CODEWITHSUNDEEP
mysqlperl
Timmy
Timmy

Reputation: 12828

How do I dump contents of a MySQL table to file using Perl?

What's the best way to dump a MySQL table to a file in Perl?

I've been using:

open( FILE, ">$filename" );
my $sth=$dbh->prepare("select * from table");
$sth->execute();
while ( my $row = $sth->fetchrow_arrayref ) {
    print FILE join( "|", @$row ), "\n";
}

Upvotes: 2

Views: 1930

Answers (4)

hpavc
hpavc

Reputation: 1335

Depending on why you need it dumped and what the size and content is. Assuming what you want is not a backup, which obviously should have a different application besides perl for. I would go with something like this which will preserve your columns and make the data easier in some respects to slurp into other programs or hand off than a CSV.

use XML::Simple;
...
my @rows=();
while ( my $h = $sth->fetchrow_hashref() )
{
   $h->{_ROWNUM}=$#rows;
   push(@rows, $h);
}
print XMLout(\@rows);

Upvotes: 1

Anon
Anon

Reputation: 326

As already said, it depends on what you want to do. If the purpose is to back up the data, you should consider mysqlhotcopy (if you are using MyIsam tables) which copies the data/index files. It is much faster than manually dumping data (e.g. I get a 2.5 gb backup in approx 3 minutes).

Upvotes: 1

codehead
codehead

Reputation: 2115

It depends on what you really want. Do you want to preserve schema information and database metadata? What about column names? On the other hand your current method should work fine for data storage as long as the schema and column order don't change, buy you should consider the case of some record with the "|" character in it and escape that value appropiately, and apply the corresponding logic when you read and parse the file back. You might want to look into Text::CSV for a fast, realiable and flexible implementation that does most of the work for you in both directions, both writing an reading the file.

Upvotes: 3

Greg
Greg

Reputation: 321698

Can you shell out to mysqldump? That's what it's there for...

Upvotes: 6

Related Questions