Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

load data to MySQL database using Perl

by xieyulong (Initiate)
on Oct 01, 2009 at 05:44 UTC ( #798525=perlquestion: print w/replies, xml ) Need Help??

xieyulong has asked for the wisdom of the Perl Monks concerning the following question:

Dear Perl Monks:

I have a problem in loading a data file to a MySQL database using Perl. I created a MySQL database MySQL_test and then created an empty table test_pet. I have data to be loaded in a file called pet.txt.

The following Perl code is supposed to first load the data records in pet.txt into table test_pet of database MySQL_test, then insert one additional record into the table of test_pet, then do a query to select all the records and output them to a csv file called test.csv.

The Perl code run without problems and the output file test.csv contains the data records in the data file pet.txt plus the additional one inserted. However when I checked the table test_pet in MySQL database MySQL_test, it is still empty.

What did I do wrong? Your help will be highly appreciated.


The following is the Perl code

use DBI; use strict; my $dsn = "dbi:mysql:mysql_test"; my $user = "root"; my $password = "password"; my $dbh = DBI -> connect($dsn,$user,$password,{RaiseError=>1,AutoCommi +t => 0}); my ($i,@row,$sth); my $out_file = "test.csv"; open(Handle_OUT,">$out_file") || die "Could not open $out_file $!\n"; # load data [pet.txt] into TABLE [test_pet] of DATABASE [MySQL_test] $sth = $dbh -> prepare("LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE te +st_pet LINES TERMINATED BY '\r\n';"); $sth -> execute; # insert one more record into TABLE [test_pet] $sth = $dbh -> prepare("INSERT INTO test_pet VALUES ('Puffball','Dian +e','hamster','f','1999-03-30',NULL);"); $sth -> execute; # select all records from the [test_pet] TABLE of DATABASE [MySQL_test +] and output to [test.csv] $sth = $dbh -> prepare("SELECT * FROM test_pet"); $sth -> execute; while (@row = $sth->fetchrow_array()) { for ($i=0;$i<@row;$i++) { print Handle_OUT "$row[$i]\t"; } print Handle_OUT "\n"; } # disconnet $dbh->disconnect; # close the output file close(Handle_OUT); ### ----------------------------------------------------------------- ### THE FOLLOWING ARE COMMAND USED TO CREATE THE TEST DATABASE AND THE + TABLE IN THE DATABASE ## ----------------------------------------------------------------- ### create database MySQL_test; # Create a database called [MyS +QL_test] ### use MySQL_test; # Select the created database [MySQL_te +st] ### # Create an empty TABLE called + [test_pet] ### CREATE TABLE test_pet (name VARCHAR(20),owner VARCHAR(20),species +VARCHAR(20),sex CHAR(1), birth DATE, death DATE); ### ### ### ----------------------------------------------------------------- ### Below is the data file [pet.txt] to be loaded into TABLE [test_pet +] of database [MySQL_test] ### ----------------------------------------------------------------- ### Fluffy Harold cat f 1993-02-04 ### Claws Gwen cat m 1994-03-17 ### Buffy Harold dog f 1989-05-13 ### Fang Benny dog m 1990-08-27 ### Bowser Diane dog m 1979-08-31 1995-07-29 ### Chirpy Gwen bird f 1998-09-11 ### Whistler Gwen bird 1997-12-09 ### Slim Benny snake m 1996-04-29 ## -----------------------------------------------------------------

Replies are listed 'Best First'.
Re: load data to MySQL database using Perl
by ccn (Vicar) on Oct 01, 2009 at 05:47 UTC

    Set AutoCommit => 1 or call $dbh->commit; before disconnect.

    See perldoc DBI about commiting

      Yes. Thanks a lot. When I set autocommit from "off" to "on", it worked as expected.

      I checked perldoc DBI about the setting of autocommit when establishing a connection, but did not understand well. In practice in which situation the autocommit should be set to "off" and then call commit to make the transaction take effect.

      Thanks again.

Re: load data to MySQL database using Perl
by Bloodnok (Vicar) on Oct 01, 2009 at 09:58 UTC
    I suspected I recognised the problem long before reading the code, so by way of explanation of ccns most apposite reply ...

    All database updates/changes are transient i.e. they will only last for the duration of the session unless any changes are auto-committed on, or the changes are manually committed before, session closure.

    A user level that continues to overstate my experience :-))
      You could always change your connect string to:
      my $dbh = DBI ->connect($dsn,$user,$password,{RaiseError=>1,AutoCommit => 1});

      Notice the Autocommit value...
        Hmmm, I take it that you didn't bother to read ccns reply before posting ??

        A user level that continues to overstate my experience :-))

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://798525]
Approved by ccn
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (6)
As of 2020-07-05 19:01 GMT
Find Nodes?
    Voting Booth?

    No recent polls found