How can I insert array data into MySQL in Perl?

Question

I parse a text file with the script below.

How to insert the array data to MySQL table?

I already learned Perl MySQL DBI connect method. And I can connect to local MySQL DB successfully. I can create the table with MySQL command line.

#!C:\Perl\bin\perl.exe
use strict;
use warnings;

while ( <DATA> ) { 
    my @rocks = split(/\s+/, $_);

    foreach my $rock (@rocks) {  

    $rock = "\t$rock "; # put a tab in front of each element of @rocks 

    $rock .= "\n"; # put a newline on the end of each  

    print $rock ;
    } 
} 

__DATA__ 
A B C D
E F G H

I want the table browse result.

        Item1  Item2  Itme3 Item4

        A       B      C       D

        E       F      G       H

Your comments are not in sync with the code: Where is @rocks? Your variable names lie to the reader: $array is not an array ref as one might expect. Plus: s/talbe/table/ — Sinan Ünür
– Sinan Ünür, Commented Jan 25, 2010 at 15:11

FMc · Accepted Answer · 2010-01-25 13:36:42Z

9

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect(
    'DBI:mysql:database=test;host=localhost',
    'root',
    'YOUR_PASSWORD',
    { RaiseError => 1, AutoCommit => 1 },
);

my $sql = 'INSERT INTO foo (Item1,Item2,Item3,Item4) VALUES (?,?,?,?)';
my $sth = $dbh->prepare($sql);

while (<DATA>){
    chomp;
    my @vals = split /\s+/, $_;
    $sth->execute(@vals);
}

__END__
A B C D
E F G H

answered Jan 25, 2010 at 13:36

FMc

42.5k13 gold badges81 silver badges135 bronze badges

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

2 Comments

Nano HE Over a year ago

Hi FM, it works very well. Could you please give me more dissecion about RaiseError => 1, AutoCommit => 1 ?, I can't understand it. Thank you.

Ivan Nevostruev Over a year ago

@Nano HE: Take a look at manuals search.cpan.org/perldoc?DBI. Everything is described there

Collectives™ on Stack Overflow

How can I insert array data into MySQL in Perl?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related