Created
December 1, 2014 13:28
-
-
Save toniher/42422764e1656e68d879 to your computer and use it in GitHub Desktop.
Export MySQL table/query into CSV
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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