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.
Thanks
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 ## -----------------------------------------------------------------
In reply to load data to MySQL database using Perl by xieyulong
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |