In search of elegant code: Combining two statements into one (DBI)

Discussion in 'Perl Misc' started by David Filmer, Sep 22, 2004.

  1. David Filmer

    David Filmer Guest

    (I'm gonna use a DBI query as an example, but this question is really
    a Perl syntax inquiry (how to add elegance to a bit of code), not a
    database question).

    Suppose I have a database table "department" with a simple structure
    such as:

    id integer primary key #might not be sequential because of
    deletes
    name varchar

    I want to build a hash (%dept) from the database such that (for
    example) $dept{2} eq "Accounting".

    I access the database:

    $sth = $dbh->prepare("SELECT id, name FROM department");
    $sth->execute;

    Now, I COULD do this:

    $dept_ref = %{$sth->fetchall_hashref('id')};

    Which gives me a situation where $dept_ref->{2}->{'name'} eq
    "Accounting" - not really very close to the data structure I want (a
    simple hash). So I COULD, instead, populate a hash like this:

    %dept = %{$sth->fetchall_hashref('id')};

    That gets me closer - now $dept{2}{'name'} eq "Accounting." DBI does
    this, of course, because I might be selecting multiple columns. But,
    in this case, I'm only selecting a key/value pair from the database
    and would like to go directly to a simple hash. But I need an EXTRA
    STEP to get to my goal - something like this:

    $dept{$_} = $dept{$_}{'name'} for keys %dept; #now $dept{2} eq
    'Accounting'

    That got me what I wanted, but it's not elegant - I had to populate
    the hash and then manipulate it. Is there an elegant way I can express
    the subroutine call (in this case to fetchall_hashref) so that it does
    this in just one step?
     
    David Filmer, Sep 22, 2004
    #1
    1. Advertising

  2. David Filmer wrote:

    > (I'm gonna use a DBI query as an example, but this question is really
    > a Perl syntax inquiry (how to add elegance to a bit of code), not a
    > database question).


    Yes ... but it does have a bearing on the solution.

    > Suppose I have a database table "department" with a simple structure
    > such as:
    >
    > id integer primary key #might not be sequential because of
    > deletes
    > name varchar
    >
    > I want to build a hash (%dept) from the database such that (for
    > example) $dept{2} eq "Accounting".


    Hopefully, this table stays relatively small. ;-)

    > I access the database:
    >
    > $sth = $dbh->prepare("SELECT id, name FROM department");
    > $sth->execute;
    >
    > Now, I COULD do this:
    >
    > $dept_ref = %{$sth->fetchall_hashref('id')};


    I doubt it. This would return a hash in scalar context - just remove
    the %{}, and you'd be right.

    > Which gives me a situation where $dept_ref->{2}->{'name'} eq
    > "Accounting" - not really very close to the data structure I want (a
    > simple hash). So I COULD, instead, populate a hash like this:


    Well, actually pretty close. You could write that as
    $dept_ref->{2}{'name'} - same thing, really. So, the only difference
    is that you've got a ref to the hash of hashes, rather than a hash of
    hashes.

    > %dept = %{$sth->fetchall_hashref('id')};
    >
    > That gets me closer - now $dept{2}{'name'} eq "Accounting." DBI does
    > this, of course, because I might be selecting multiple columns. But,
    > in this case, I'm only selecting a key/value pair from the database
    > and would like to go directly to a simple hash. But I need an EXTRA
    > STEP to get to my goal - something like this:


    Extra steps are not necessarily non-elegant. Intermediary variables
    often are elegant in that they make the code easier to read. What you
    want is to merely make compact code - not always the same thing as
    elegant.

    > $dept{$_} = $dept{$_}{'name'} for keys %dept; #now $dept{2} eq
    > 'Accounting'
    >
    > That got me what I wanted, but it's not elegant - I had to populate
    > the hash and then manipulate it. Is there an elegant way I can express
    > the subroutine call (in this case to fetchall_hashref) so that it does
    > this in just one step?


    %dept = map { $_->[0] => $_->[1] } @{$sth->fetchall_arrayref()};

    I don't have time right now to check the exact syntax of the arrayref
    function, but that should be pretty close.
     
    Darin McBride, Sep 22, 2004
    #2
    1. Advertising

  3. David Filmer

    Tore Aursand Guest

    On Tue, 21 Sep 2004 16:44:37 -0700, David Filmer wrote:
    > Now, I COULD do this:
    >
    > $dept_ref = %{$sth->fetchall_hashref('id')};
    >
    > Which gives me a situation where $dept_ref->{2}->{'name'} eq
    > "Accounting" - not really very close to the data structure I want (a
    > simple hash). So I COULD, instead, populate a hash like this:
    >
    > %dept = %{$sth->fetchall_hashref('id')};
    >
    > That gets me closer - now $dept{2}{'name'} eq "Accounting." DBI does
    > this, of course, because I might be selecting multiple columns. But, in
    > this case, I'm only selecting a key/value pair from the database and
    > would like to go directly to a simple hash. But I need an EXTRA STEP to
    > get to my goal - something like this:
    >
    > $dept{$_} = $dept{$_}{'name'} for keys %dept; #now $dept{2} eq
    > 'Accounting'
    >
    > That got me what I wanted, but it's not elegant - I had to populate the
    > hash and then manipulate it. Is there an elegant way I can express the
    > subroutine call (in this case to fetchall_hashref) so that it does this
    > in just one step?


    You want to play with the 'map' function. You also want to use
    'fetchall_arrayref' instead;

    my $stDepts = $dbh->prepare( 'SELECT id, name FROM dept );
    $stDepts->execute();
    my %dept = map { $_->[0] => $_->[1] } @{$stDepts->fetchall_arrayref()};
    $stDepts->finish();


    --
    Tore Aursand <>
    "There are three kinds of lies: lies, damn lies, and statistics."
    (Benjamin Disraeli)
     
    Tore Aursand, Sep 22, 2004
    #3
  4. In article <>, says...
    >
    >
    >(I'm gonna use a DBI query as an example, but this question is really
    >a Perl syntax inquiry (how to add elegance to a bit of code), not a
    >database question).
    >
    >Suppose I have a database table "department" with a simple structure
    >such as:
    >
    > id integer primary key #might not be sequential because of
    >deletes
    > name varchar
    >
    >I want to build a hash (%dept) from the database such that (for
    >example) $dept{2} eq "Accounting".


    This is form perldoc DBI and should do what you want:

    # get array of id and name pairs:
    my $ary_ref = $dbh->selectcol_arrayref("select id, name from table", { Columns=>[1,2] });
    my %hash = @$ary_ref; # build hash from key-value pairs so $hash{$id} => name

    cheers

    --
    Heinrich Mislik
    Zentraler Informatikdienst der Universitaet Wien
    A-1010 Wien, Universitaetsstrasse 7
    Tel.: (+43 1) 4277-14056, Fax: (+43 1) 4277-9140
     
    Heinrich Mislik, Sep 22, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chuck
    Replies:
    0
    Views:
    384
    Chuck
    Aug 28, 2003
  2. Dave Blackington

    Combining colon statements on one line?

    Dave Blackington, Jul 16, 2004, in forum: Python
    Replies:
    3
    Views:
    323
    Alex Hunsley
    Jul 20, 2004
  3. Phil Frost
    Replies:
    6
    Views:
    323
    Peter Hansen
    Jul 21, 2004
  4. Replies:
    5
    Views:
    320
    EricF
    Oct 9, 2007
  5. Replies:
    2
    Views:
    119
    Anno Siegel
    Aug 1, 2005
Loading...

Share This Page