如何在Perl中按DBI顺序获取列名和行数据? [英] How can I get column names and row data in order with DBI in Perl?
问题描述
我正在使用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.
这篇关于如何在Perl中按DBI顺序获取列名和行数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!