如何在Perl中按DBI顺序获取列名和行数据？

本文介绍了如何在Perl中按DBI顺序获取列名和行数据？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用DBI查询SQLite3数据库。我所拥有的有效，但无法按顺序返回各列。示例：

I'm using DBI to query a SQLite3 database. What I have works, but it doesn't return the columns in order. Example:

Query: select col1, col2, col3, col4 from some_view; Output: col3, col2, col1, col4 3, 2, 1, 4 3, 2, 1, 4 3, 2, 1, 4 3, 2, 1, 4 ... (values and columns are just for illustration)

我知道这种情况的发生是因为我使用的是哈希值，但是如果我重新获得列名，该怎么办？只使用数组？我想做的就是为任何任意查询提供类似的信息：

I know this is happening because I'm using a hash, but how else do I get the column names back if I only use an array? All I want to do is get something like this for any arbitrary query:

col1, col2, col3, col4 1, 2, 3, 4 1, 2, 3, 4 1, 2, 3, 4 1, 2, 3, 4 ...

（也就是说，我需要以正确的顺序输出该列

(That is, I need the output is in the right order and with the column names.)

我是Perl的新手，但是我真的认为这将是一个简单的问题。 （我之前在Ruby和PHP中已经做到了，但是在跟踪Perl文档中要查找的内容时遇到了麻烦。）

I'm very much a Perl novice, but I really thought this would be a simple problem. (I've done this before in Ruby and PHP, but I'm having trouble tracking down what I'm looking for in the Perl documentation.)

我现在所拥有的简写版本：

Here's a pared down version of what I have at the moment:

use Data::Dumper; use DBI; my $database_path = '~/path/to/db.sqlite3'; $database = DBI->connect( "dbi:SQLite:dbname=$database_path", "", "", { RaiseError => 1, AutoCommit => 0, } ) or die "Couldn't connect to database: " . DBI->errstr; my $result = $database->prepare('select col1, col2, col3, col4 from some_view;') or die "Couldn't prepare query: " . $database->errstr; $result->execute or die "Couldn't execute query: " . $result->errstr; ########################################################################################### # What goes here to print the fields that I requested in the query? # It can be totally arbitrary or '*' -- "col1, col2, col3, col4" is just for illustration. # I would expect it to be called something like $result->fields ########################################################################################### while (my $row = $result->fetchrow_hashref) { my $csv = join(',', values %$row); print "$csv\n"; } $result->finish; $database->disconnect;

推荐答案

替换此处内容注释和以下内容循环：

Replace the "what goes here" comment and the following loop with:

my $fields = join(',', @{ $result->{NAME_lc} }); print "$fields\n"; while (my $row = $result->fetchrow_arrayref) { my $csv = join(',', @$row); print "$csv\n"; }

NAME_lc 字段名称小写。您也可以使用 NAME_uc 表示大写，或者使用 NAME 表示数据库决定将其返回的大小写。

NAME_lc gives the field names in lowercase. You can also use NAME_uc for uppercase, or NAME for whatever case the database decides to return them in.

您可能还应该使用 Text :: CSV 或 Text :: CSV_XS 而不是尝试滚动自己的CSV文件，但这是另一个问题。

You should also probably be using Text::CSV or Text::CSV_XS instead of trying to roll your own CSV file, but that's another question.