如何在Perl中使用DBI获取列名和行数据？

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

我正在使用DBI来查询SQLite3数据库。我的工作原理，但它没有按顺序返回列。例：

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.)

（也就是说，我需要输出的顺序和列名都是正确的。）

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.)

我非常喜欢Perl新手，但我真的认为这是一个简单的问题。 （我以前在Ruby和PHP中做过这个，但是我无法在Perl文档中找到我正在寻找的内容。）

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;

5 个解决方案

#1

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

用以下内容替换“what goes here”注释和以下循环：

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

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

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.

NAME_lc以小写字母给出字段名称。您也可以将NAME_uc用于大写，或者在数据库决定将其返回的情况下使用NAME。

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.

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

#2

If you want to preserve the order, but still use a hash to refer to fields by name use:

如果要保留订单，但仍使用哈希来按名称引用字段：

$dbh->selectall_arrayref($sql,{ Slice => {} } );

This will give you an ordered array of hashes

这将为您提供有序的哈希数组

#3

Define your column names in an ARRAY before your SELECT

Ideally you'd have a list of the columns you were SELECT'ing with DBI, and you'd use that array.

理想情况下，您有一个用DBI选择的列的列表，并且您将使用该数组。

If you need to get the column names from the hash itself, this will work, and you can sort it, but there is no indication of the original SQL SELECT order (in the hash):

如果你需要从散列本身获取列名，这将有效，你可以对它进行排序，但没有迹象表明原始的SQL SELECT顺序（在散列中）：

my %cols_hash = ("name" => "john", "age" => 2, "color" => "apalachian");
my $cols_hash_ref = \%cols;  

my @keys = (sort keys %$cols_hash_ref);  
my @vals;  
foreach (@keys){ push @vals, $$cols_hash_ref{$_} };  

Hope this helps.

希望这可以帮助。

When I searched I found a way to get the column names from the DBI:

$sth = $dbh->prepare($query) or die "Prepare exceptioin: $DBI::errstr!";  
$rv = $sth->execute() or die "Execute exception: $DBI::errstr";  
$res = $sth->fetchall_arrayref();  

# Array reference with cols captions, which were retrived.  
$col_names_array_ref = $sth->{NAME};          

That should give you the column names in the original order, but I haven't tested it.

那应该给你原始顺序的列名，但我没有测试它。

#4

You're asking for the result as a hash. A hash is inherently unordered. Perhaps you want fetchrow_arrayref instead.

你要求结果作为哈希。哈希本质上是无序的。也许你想要fetchrow_arrayref。

In fact, if you had looked at keys %$row, you would have seen the corresponding keys being out of order as well. That's the nature of a hash... each key is paired with its value, but the overall ordering of keys or values is optimized for access, not external ordering.

事实上，如果你看过键％$ row，你会看到相应的键也不正常。这是散列的本质......每个键都与其值配对，但是键或值的整体排序是针对访问而非外部排序进行优化的。

#5

Here's what I do:

这是我做的：

    use Data::Dump qw(dump);
    # get column names in array
    my @column_names_array= $sth->{NAME};  
    # print out column names in pretty format
    print "Field names: \n";
    dump(@column_names_array);