Export MySQL table/query into CSV

mysql-export-csv.pl

#!/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;
}