How can I insert data from three Perl arrays into a single MySQL table?

Question

I have 3 different arrays in Perl (namely A, B and C). Now, I have a table in mysql which also has 3 fields.

What I am trying to do is get all the contents of array A in first field of mysql table, contents of array B into second field and so on. I tried doing this using foreach loops but it works fine for the first array but does not insert anything for the second and third array.

The code is used is as below:

foreach my $a (@a) {

    my $sql = "insert into es(a) VALUES(\"$a\")";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die "SQL Error: $DBI::errstr\n";
}


foreach my $b (@b) {

    my $sql = "insert into es(b) VALUES(\"$b\")";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die "SQL Error: $DBI::errstr\n";

}

and similarly for third. The column a of the table gets populated correctly but there is no data for column b and c in the table. What am I doing wrong.

You should use placeholders, not try to interpolate variables in the query. It may be causing your problem. — TLP
– TLP, Commented Sep 21, 2012 at 10:46

simbabque · Accepted Answer · 2012-09-21 11:21:45Z

6

I guess you're not very familiar with relational databases. What you've done is:

iterate over @a
prepare a new statement for each item in @a
add one line per insert with only the a value in it; that means:
- you create a dataset for each item of @a
- the columns b and c are NULL.

Now you do that for @b and @c likewise. That is not very efficient.

DBI is designed to help you out here. You should consider the following rules (guidelines):

Always use DBI's quote method or better placeholders. That saves you the hassle of addind quotes yourself.
If there's a loop and several INSERTs or UPDATEs, always prepare your query outside of the loop and just execute it in the loop.

Let's take a look at your problem then. I assume @a, @b and @c all have the same number of items, and you want one line per index of @a, @b and @c. So if you have this data:

my @a = (1, 2, 3);
my @b = qw(foo bar baz)
my @c = (999, 998, 997);

My bet is that you want it to look like this in the db:

a b   c
1 foo 999
2 bar 998
3 baz 997

We therefore need to combine your three INSERTs into one statement. That can be done by iterating over all of them at once. We can use the each_array function from List::MoreUtils to handle the iteration for us. We'll also add the guidelines from above to the code.

use List::MoreUtils qw(each_array);
my $dbh = DBI->connect(); # connect to db here

# prepare the INSERT statement once
my $sth_insert = $dbh->prepare('INSERT INTO es SET a=?, b=?, c=?')
  or die $dbh->errstr;

# create the array iterator
my $ea = each_array(@a, @b, @c);
# iterate over all three arrays step by step
while ( my ($val_a, $val_b, $val_c) = $ea->() ) {
  $sth_insert->execute($val_a, $val_b, $val_c) or die $dbh->errstr;
}

edited Sep 21, 2012 at 11:21

answered Sep 21, 2012 at 10:47

simbabque

54.4k8 gold badges77 silver badges141 bronze badges

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

6 Comments

Deepak Over a year ago

Thanks for your detailed and well explained answer simbabque. You guessed correctly both, about the same number of items in arrays and that I am not very familiar with relational databases. Your way is absolutely correct and you saved my day!! Thanks a lot. Really appreciate it :)

simbabque Over a year ago

@Deepak: I'm glad it's right. Did another edit right now to clarify.

simbabque Over a year ago

You should also read a bit more about DBI in the doc. It's very powerful and you can do a lot of stuff with it once you know about it.

Deepak Over a year ago

Thanks again simbabque. I will definitely read about DBI in the doc. Just getting started with it and I am very much excited. It indeed is very powerful and provides a lot of functionality. Was also unaware of List::MoreUtils, will read about it too :)

Zaid Over a year ago

@simbabque : Excellent answer. My only suggestion would be to avoid using $a and $b as lexicals

|

Collectives™ on Stack Overflow

How can I insert data from three Perl arrays into a single MySQL table?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related