How do I insert values from a hash into a database using Perl's DBI module?

Question

I need to insert values from a hash into a database. Following is the code template I have to insert values in table1 column key and value:

use DBI;
use strict;

%hash; #assuming it already contains desired values
my $dbh = DBI->connect(
      "dbi:Sybase:server=$Srv;database=$Db", 
      "$user", "$passwd"
) or die sprintf 'could not connect to database %s', DBI->errstr;
my $query= "Insert INTO table1(key, values) VALUES (?,?) ";
my $sth = $dbh->prepare($query) 
    or die "could not prepare statement\n", $dbh->errstr;
$sth-> execute or die "could not execute", $sth->errstr;

I know how to insert values using array i.e use execute_array(), but do not know how to insert values present in %hash in table1.

Any suggestions?

Answer 1

Maybe you could try using

for my $key (keys %hash) {
  $sth->execute($key, $hash{$key}) or die $sth->errstr;
}

Is this what you're trying to achieve?

If I understand the manual correctly ("Execute the prepared statement once for each parameter tuple (group of values) [...] via a reference passed ...") it should also be possible to simply to

($tuples, $rows) = $sth->execute_array(\%hash) or die $sth->errstr;

Answer 2

Here's a mostly easy way to build the query. I will typically do something like this because I haven't found another workaround yet.

use strict;
use DBI;

my $dbh = Custom::Module::Make::DBH->connect('$db');

my %hash = (
    apple  => 'red',
    grape  => 'purple',
    banana => 'yellow',
);

my $keystr = (join ",\n        ", (keys %hash));
my $valstr = join ', ', (split(/ /, "? " x (scalar(values %hash))));
my @values = values %hash;

my $query = qq`
    INSERT INTO table1 (
        $keystr
    )
    VALUES (
        $valstr
    )
`;

my $sth = $dbh->prepare($query) 
    or die "Can't prepare insert: ".$dbh->errstr()."\n";

$sth->execute(@values)
    or die "Can't execute insert: ".$dbh->errstr()."\n";

But it's possible I also didn't understand the question correctly :P

Answer 3

The following uses the execute_array function as mentioned in your question. I tested it.

my $dbh = DBI->connect("DBI:mysql:database=$DB;host=$host;port=$port", $user, $password);

my %hash = (
            1   =>  'A',
            2   =>  'B',
            0   =>  'C',
            );

my @keys = keys %hash;

my @values = values %hash;

my $sth = $dbh->prepare("INSERT INTO table1(id, value) VALUES (?,?);");

$sth->execute_array({},\@keys, \@values);

(Sorry, I don't have a Sybase database to work with, or I'd use it as an example.)

Answer 4

There's an example in the docs