Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (5)
As of 2022-01-28 13:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    In 2022, my preferred method to securely store passwords is:












    Results (73 votes). Check out past polls.

    Notices?