How can I get column names and row data in order with DBI in Perl?

Question

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

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;

Teevens

Answer 1

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

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.

Marquesan

Great, I'll try that. I would use Text::CSV, but I'm only printing out the output for testing purposes at the moment. – Teevens

Worked great. Thanks again. No wonder it didn't come up when I searched -- Google chokes on things like @{ $result->{NAME} }. – Teevens

Well, the place to search is search.cpan.org/perldoc?DBI (or your local copy). – Marquesan

I came across that, but never found the solution there. I see it now that I know what to look for, however. – Teevens

Answer 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

Cypsela

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

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

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.

Lamaism

Answer 4

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

Robrobaina

Answer 5

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

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.

Industrialist

From the question: "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?" It's the last part I'm having a problem with. – Teevens

See "Statement Handle Attributes" on the DBI doc. – Industrialist

From what I've read in Perl the 'keys' and 'values' functions acting on the same hash is guaranteed to give the same ordering of elements in the corresponding arrays they return. So one trick would be to fetch the first row, grab the field names, then continue processing the rest of the rows. – Bedeck

@jerseyboy, that only works if the hash isn't modified between the calls to keys and values. There's no guarantee that the results would be consistent between calls to fetchrow_hashref. – Marquesan

Answer 6

#!/usr/bin/perl                                                                                                                                                                        
use strict;
use warnings;
use DBI;
my $database = "your_mysqldb";
my $username = "your_dbusername";
my $password = "your_dbpassword";
my $tblename = "your_mysqldbtable";
my $dsn = "DBI:mysql:embedded";
#                                                                                                                                                                                      
my %attr = ( PrintError=>0,   #turn off error reporting via warn()                                                                                                                     
             RaiseError=>1);  #turn on error reporting via die()                                                                                                                       
my $dbh = DBI->connect($dsn,$username,$password,\%attr);
print "Connected to the MySQL database $database for username $username\n";
my $sql = "DESCRIBE ".$tblename;
my $sth = $dbh->prepare($sql);  
$sth->execute();   #execute the prepared query                                                                                                                                         
my @heads;
my $i=0;
while(my $r = $sth->fetchrow_arrayref()){
    $heads[$i]=$r->[0];
    $i++;
}
my $j=0;
foreach(@heads){
    print "$j $_\n";
    $j++;
}
$sth->finish();
$dbh->disconnect(); #disconnect from mysql db   

Tanganyika

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Leifleifer