Create a perl hash from a db select

Question

Having some trouble understanding how to create a Perl hash from a DB select statement.

$sth=$dbh->prepare(qq{select authorid,titleid,title,pubyear from books});
$sth->execute()  or die DBI->errstr;
while(@records=$sth->fetchrow_array()) {
    %Books = (%Books,AuthorID=> $records[0]);
    %Books = (%Books,TitleID=> $records[1]);
    %Books = (%Books,Title=> $records[2]);
    %Books = (%Books,PubYear=> $records[3]);
    print qq{$records[0]\n}
    print qq{\t$records[1]\n};
    print qq{\t$records[2]\n};
    print qq{\t$records[3]\n};
}
$sth->finish();
while(($key,$value) = each(%Books)) {
    print qq{$key --> $value\n};
}

The print statements work in the first while loop, but I only get the last result in the second key,value loop.

What am I doing wrong here. I'm sure it's something simple. Many thanks.

Your hash only ever has 4 keys ("AuthorID" etc) with 4 corresponding values. You keep overwriting the current 4 values with 4 new values. You need to decide what data structure you want to achieve - for example a hash of arrays - where each hash value is a reference to an array which contains all AuthorID values etc. — Dave Mitchell
– Dave Mitchell, Commented Apr 11, 2022 at 21:25
To assign a key-value pair to a hash, you normally do $Books{AuthorID} = $records[0]; (etc). Here can use a "slice" as well: @Books{qw(AuthorID TitleID Title PubYear)} = @recors;. But with this it's hopefully clear that the next row of values overwrites the previous, etc, and only the last row remains. (Unless you do have only one row in the table?) Another option for accumulating results is an array of hashes -- an array which has hash-references like above. So choose your data structure :) — zdim
– zdim, Commented Apr 11, 2022 at 21:30
Comments are not for extended discussion; this conversation has been moved to chat. — Ryan M
– Ryan M ♦, Commented Apr 13, 2022 at 2:50

Polar Bear · Accepted Answer · 2022-04-12 00:27:52Z

3

OP needs better specify the question and do some reading on DBI module.

DBI module has a call for fetchall_hashref perhaps OP could put it to some use.

answered Apr 12, 2022 at 0:27

Polar Bear

6,8061 gold badge8 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zdim · Accepted Answer · 2022-04-12 07:25:44Z

2

In the shown code an assignment of a record to a hash with the same keys overwrites the previous one, row after row, and the last one remains. Instead, they should be accumulated in a suitable data structure.

Since there are a fair number of rows (351 we are told) one option is a top-level array, with hashrefs for each book

my @all_books;

while (my @records = $sth->fetchrow_array()) {
    my %book;
    @book{qw(AuthorID TitleID Title PubYear)} = @records;
    push @all_books, \%book;
}

Now we have an array of books, each indexed by the four parameters. This uses a hash slice to assign multiple key-value pairs to a hash.

Another option is a top-level hash with keys for the four book-related parameters, each having for a value an arrayref with entries from all records

my %books;

while (my @records = $sth->fetchrow_array()) {
    push @{$books{AuthorID}}, $records[0];
    push @{$books{TitleID}}, $records[1];
    ...
}

Now one can go through authors/titles/etc, and readily recover the other parameters for each.

Adding some checks is always a good idea when reading from a database.

edited Apr 12, 2022 at 7:25

answered Apr 11, 2022 at 23:24

zdim

67.2k5 gold badges59 silver badges87 bronze badges

5 Comments

simbabque Over a year ago

Keep in mind that fetchrow_array is slow. It copies values around.

zdim Over a year ago

@simbabque That was not the question, about what (of many DBI ways) to use. There are a number of ways to retrieve data from a database. The question -- that I am answering anyway -- was about how to organize data traversal so to run correctly and not lose data. Btw, it seems like you are saying that fetchrow_array should not be used?

zdim Over a year ago

@simbabque Perhaps I should say: I find fetchrow_array pretty reasonable for the question at hand, that they asked. (But yes one can discuss what way to adopt to retrieving data, if they asked that and thus described the context of their problem. But they didn't ask that...)

zdim Over a year ago

@simbabque "...fetchrow_array is slow. It copies values around" --- but w/ fetchrow_arrayref that has to be dereferenced to get to the data. So we need to copy values. (If they were passing data elsewhere then yes, better get a ref. But the question has nothing to do with that.)

simbabque Over a year ago

I didn't mean to say your answer is incorrect. I agree it illustrates the problem well. Just wanted to point that out for future readers. :)

Collectives™ on Stack Overflow

Create a perl hash from a db select

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related