Skip to content

Instantly share code, notes, and snippets.

@toniher
Created December 1, 2014 13:28
Show Gist options
  • Save toniher/42422764e1656e68d879 to your computer and use it in GitHub Desktop.
Save toniher/42422764e1656e68d879 to your computer and use it in GitHub Desktop.
Export MySQL table/query into CSV
#!/usr/bin/env perl
# Script for exporting MySQL tables into a CSV.
use strict;
use warnings;
use DBI;
use Text::CSV;
use JSON qw( decode_json );
binmode STDOUT, ":utf8";
use utf8;
my $conffile = shift;
# Default config file
if ( ! defined( $conffile ) ) {
$conffile = "conf.json";
}
=begin comment
conf.json example file
{
"query" : "select * from stats",
"mysql": {
"user" : "user",
"password" : "xxx",
"db" : "test",
"host" : "localhost"
},
"csv": {
"sep_char" : "\t",
"binary" : 1
},
"cols": true
}
=end comment
=cut
my $json;
if (-f $conffile ) {
open(FILE, "<", $conffile) || die "Cannot open $conffile";
while (<FILE>) {
$json.= $_;
}
close(FILE);
} else {
die "No conf file. Stop here.";
}
my $config = decode_json($json);
my $user = $config->{"mysql"}->{"user"};
my $password = $config->{"mysql"}->{"password"};
my $db = $config->{"mysql"}->{"db"};
my $host = $config->{"mysql"}->{"host"};
my $query = $config->{"query"};
my $csvconf = {};
# CSV parameters from https://metacpan.org/pod/Text::CSV
if ( defined ( $config->{"csv"} ) ) {
$csvconf = $config->{"csv"};
}
my $csv = Text::CSV->new ( $csvconf );
# Let's assume we need a query and a $db
if ( defined ( $query ) && defined( $db ) ) {
if ( ! defined( $host ) ) {
$host = "localhost";
}
if ( ! defined( $user ) ) {
$user = "";
}
if ( ! defined( $password ) ) {
$password = "";
}
my $dbh = DBI->connect('DBI:mysql:database='.$db.';host='.$host, $user, $password) || die "Could not connect to database: $DBI::errstr";
my $sth = $dbh->prepare( $query );
$sth->execute();
# We print columns of table if set true in conf.json
if ( $config->{"cols"} ) {
$csv->print( *STDOUT, $sth->{NAME_lc} );
print "\n";
}
while (my $row = $sth->fetchrow_arrayref()) {
$csv->print( *STDOUT, $row );
print "\n";
}
$sth->finish;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment