Skip to content

Instantly share code, notes, and snippets.

@paslandau paslandau/mysql2csv
Last active Mar 7, 2019

Embed
What would you like to do?
Helper command to export data for an arbitrary mysql query into a CSV file.
#!/usr/bin/env php
<?php
$intro = <<<TEXT
Helper command to export data for an arbitrary mysql query into a CSV file.
Especially helpful if the use of "SELECT ... INTO OUTFILE" is not an option, e.g.
because the mysql server is running on a remote host.
Usage example:
./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"
cat /tmp/result.csv
TEXT;
$opts = [
"query" => ["q", "query", ":", null, "The query string to extract data from mysql.", true],
"host" => ["h", "host", ":", "127.0.0.1", "The hostname of the mysql server.", false],
"database" => ["D", "database", ":", null, "The default database.", false],
"port" => ["P", "port", ":", "3306", "The port of the mysql server.", false],
"user" => ["u", "user", ":", null, "The username to connect to the mysql server.", false],
"password" => ["p", "password", ":", null, "The password to connect to the mysql server.", false],
"file" => ["F", "file", ":", "php://stdout", "The filename to export the query result to ('php://stdout' prints to console).", false],
"delimiter" => ["L", "delimiter", ":", ",", "The CSV delimiter.", false],
"enclosure" => ["C", "enclosure", ":", "\"", "The CSV enclosure (that is used to enclose values that contain special characters).", false],
"escape" => ["E", "escape", ":", "\\", "The CSV escape character.", false],
"null" => ["N", "null", ":", "\\N", "The value that is used to replace NULL values in the CSV file.", false],
"header" => ["H", "header", ":", 1, "If '0', the resulting CSV file does not contain headers.", false],
"help" => ["", "help", "", null, "Prints the help for this command.", false],
];
$options = "";
$longopts = [];
foreach ($opts as list($option, $longopt, $modifier)) {
$options .= $option . $modifier;
$longopts[] = $longopt . $modifier;
}
// resolve command line options
$actualOptions = getopt($options, $longopts);
$printHelp = function($intro, $opts){
$str = [];
foreach ($opts as list($option, $longopt, $modifier, $default, $description,$required)){
$requiredStr = "";
if($required){
$requiredStr = "[required] ";
}
$s = [];
if(trim($option) !== ""){
$s[] = "-$option";
}
if(trim($longopt) !== ""){
$longoptStr = "--{$longopt}";
if($modifier == ":"){
$longoptStr .= "=name";
}
elseif($modifier == "::"){
$longoptStr .= "[=name]";
}
$s[] = $longoptStr;
}
$s = implode(",",$s);
$defaultStr = "";
if(trim($default) !== ""){
$defaultStr = "(Default: $default) ";
}
$str[] = "\t$s\t{$requiredStr}\n\t\t{$defaultStr}{$description}";
}
$helpString = $intro."\n\nOptions:\n".implode("\n",$str);
return $helpString;
};
/**
* Get the actual value of the given $optionKey based on the defined
* short option / long option. Short is preferred over long.
* @param $optionKey
* @return string|null
*/
$resolveOption = function ($optionKey) use ($actualOptions, $opts) {
list($option, $longopt, $_, $default) = $opts[$optionKey];
$actualOption = $actualOptions[$option] ?? $actualOptions[$longopt] ?? $default;
return $actualOption;
};
/**
* Resolve the DSN string for the mysql PDO connection
* @see http://php.net/manual/de/ref.pdo-mysql.connection.php
* @return string
*/
$resolveDsn = function () use ($resolveOption) {
$availableDsnArgs = [
"host" => "host",
"port" => "port",
"dbname" => "database",
];
$actualArgs = [];
foreach ($availableDsnArgs as $arg => $optionKey) {
$actualOption = $resolveOption($optionKey);
if ($actualOption !== null) {
$actualArgs[] = $arg . "=" . $actualOption;
}
}
$dsn = "mysql:" . implode(";", $actualArgs);
return $dsn;
};
// resolve the given arguments / options
$help = $resolveOption("help") !== null;
if($help){
die($printHelp($intro, $opts)."\n");
}
$dsn = $resolveDsn();
$user = $resolveOption("user");
$passwd = $resolveOption("password");
$query = $resolveOption("query");
$file = $resolveOption("file");
$delimiter = $resolveOption("delimiter");
$enclosure = $resolveOption("enclosure");
$escape_char = $resolveOption("escape");
$null = $resolveOption("null");
$hasHeader = $resolveOption("header") == 1;
// validate input
if (trim($query) === "") {
list($option, $longopt) = $opts["query"];
die("Query (-$option, --$longopt) must not be empty!\n");
}
if (trim($file) === "") {
list($option, $longopt) = $opts["file"];
die("File (-$option, --$longopt) must not be empty!\n");
}
try{
// get data from mysql
$db = new PDO ($dsn, $user, $passwd,[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]
);
$stmt = $db->prepare($query);
$stmt->execute();
}catch(\Exception $e){
die("Error: ".$e->getMessage()."\n");
}
// write to CSV file
// @see http://php.net/manual/de/splfileobject.fputcsv.php
$headers = null;
$file = new SplFileObject($file, 'w');
/**
* @var array|bool $row
*/
while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
if ($hasHeader && $headers === null) {
$headers = array_keys($row);
$file->fputcsv($headers, $delimiter, $enclosure, $escape_char);
}
if ($null !== null) {
// replace NULL values
foreach ($row as $k => $v) {
if ($v === null) {
$v = $null;
}
$row[$k] = $v;
}
}
$file->fputcsv($row, $delimiter, $enclosure, $escape_char);
}
@paslandau

This comment has been minimized.

Copy link
Owner Author

commented May 16, 2018

Install via

wget https://gist.githubusercontent.com/paslandau/37bf787eab1b84fc7ae679d1823cf401/raw/29a48bb0a43f6750858e1ddec054d3552f3cbc45/mysql2csv -O mysql2csv -q && (sha256sum mysql2csv | cmp <(echo "b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv") || (echo "ERROR comparing hash, Found:" ;sha256sum mysql2csv) ) && chmod +x mysql2csv

(download content of this gist, check checksum and make it executable)

Usage example
./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"

generates file /tmp/result.csv with content

foo,bar
1,2

Print help via
./mysql2csv --help

@AjaxOdessa

This comment has been minimized.

Copy link

commented Aug 16, 2018

Any specific PHP settings/requirements? Our of the box it fails on PHP 5.3.29 (cli) with PHP Parse error: syntax error, unexpected '[' in /home/xxxxx/mysql2csv on line 14

@peter279k

This comment has been minimized.

Copy link

commented Jan 8, 2019

@AjaxOdessa, the short array syntax is supported on php-5.4+. The php-5.3 will not support this syntax.

You can see more details about this comment on official PHP website.

@cronfy

This comment has been minimized.

Copy link

commented Mar 7, 2019

PHP 7 is required because of line 81:

    $actualOption = $actualOptions[$option] ?? $actualOptions[$longopt] ?? $default;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.