-
-
Save paslandau/37bf787eab1b84fc7ae679d1823cf401 to your computer and use it in GitHub Desktop.
#!/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); | |
} |
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
@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.
PHP 7 is required because of line 81:
$actualOption = $actualOptions[$option] ?? $actualOptions[$longopt] ?? $default;
PHP 7 is required because of line 81:
$actualOption = $actualOptions[$option] ?? $actualOptions[$longopt] ?? $default;
You can recode this statement old-school for PHP <7
$actualOption = ( isset($actualOptions[$option]) ? $actualOptions[$option] : ( isset($actualOptions[$longopt]) ? $actualOptions[$longopt] : $default ) );
In the case of an exception, returning a non-zero error code would be very helpful. For example, using echo()
with exit(1)
instead of die()
when catching PDO exceptions, like
echo("Error: ".$e->getMessage()."\n");
exit(1);
would allow error checking in a script context like,
if [[ $(mysql2csv ...) ]]
then
<ring the bell>
fi
Thanks @paslandau for sharing.
Watch out when pulling large datasets. If the script is running out of memory, set the following flag in the PDO constructor, after line 140:
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
Install via
(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 contentPrint help via
./mysql2csv --help