Comment afficher les résultats de recherche MySQL au format CSV?
y a-t-il un moyen facile d'exécuter une requête MySQL depuis la ligne de commande Linux et d'afficher les résultats dans le format CSV ?
voilà ce que je fais maintenant:
mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | tee list.csv
select id, concat(""",name,""") as name
from students
EOQ
c'est compliqué quand il y a beaucoup de colonnes qui doivent être entourées de guillemets, ou s'il y a des guillemets dans les résultats qui doivent être échappés.
28 réponses
de http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file /
SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
en utilisant cette commande les noms de colonnes ne seront pas exportés.
aussi noter que /var/lib/mysql-files/orders.csv
sera sur le serveur qui exécute MySQL. L'utilisateur sous lequel le processus MySQL est exécuté doit avoir les permissions d'écrire dans le répertoire choisi, ou la commande échouera.
si vous voulez écrire sur votre machine locale à partir d'un serveur distant (en particulier une machine hébergée ou virtualisée comme Heroku ou Amazon RDS), cette solution n'est pas appropriée.
$ mysql your_database --password=foo < my_requests.sql > out.csv
qui est séparé par un onglet. Piper comme ça pour obtenir un vrai CSV (merci @therromhere):
... .sql | sed 's/\t/,/g' > out.csv
mysql -- batch, -B
imprimer les résultats en utilisant tab comme séparateur de colonne, avec chaque ligne sur un nouvelle ligne. Avec cette option, mysql n'utilise pas le fichier d'historique. Le mode Batch donne des résultats dans un format de sortie non-tabulaire et l'échappement de caractères spéciaux. L'échappement peut être désactivé en utilisant le mode brut; voir la description de la --raw option.
cela vous donnera un fichier séparé tab. Depuis des virgules (ou des chaînes contenant la virgule) ne sont pas échappés, il n'est pas simple de changer le délimiteur en virgule.
Voici une façon assez grotesque de le faire. Trouvé quelque part, ne peut pas prendre tout crédit
mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv
marche plutôt bien. Encore une fois, bien qu'un regex prouve écrire seulement.
Regex Explication:
- s/// des moyens de substitution ce qui se trouve entre la première // avec ce qui se trouve entre la deuxième //
- le "g" à la fin est un modificateur qui signifie "tous instance, pas seulement la première"
- ^ (dans ce contexte) signifie début de ligne
- $ (dans ce contexte) signifie fin de ligne
donc, tout mettre ensemble:
s/'/\'/ replace ' with \'
s/\t/\",\"/g replace all \t (tab) with ","
s/^/\"/ at the beginning of the line place a "
s/$/\"/ at the end of the line place a "
s/\n//g replace all \n (newline) with nothing
la solution OUTFILE donnée par Paul Tomblin provoque un fichier à être écrit sur le serveur MySQL lui-même, de sorte que cela ne fonctionnera que si vous avez fichier accès, ainsi que l'accès login ou d'autres moyens pour extraire le fichier de cette boîte.
si vous n'avez pas un tel accès, et la sortie délimitée par des onglets est un substitut raisonnable pour CSV (par exemple, si votre objectif final est d'importer vers Excel), puis la solution de Serbaut (en utilisant mysql --batch
et optionnellement --raw
) est la voie à suivre.
Que Diriez-vous de:
mysql your_database -p < my_requests.sql | awk '{print ","}' > out.csv
MySQL Workbench peut exporter des recordsets vers CSV, et il semble très bien traiter les virgules dans les champs. Le CSV s'ouvre à OpenOffice fine.
toutes les solutions ici à ce jour, sauf celle de Mysql workbench, sont incorrectes et très probablement dangereuses (c.-à-d. des problèmes de sécurité) pour au moins certains contenus possibles dans la base de données mysql.
Mysql Workbench (et de la même manière PHPMyAdmin) fournissent une solution formellement correcte, mais sont conçus pour télécharger la sortie à l'emplacement d'un utilisateur. Ils ne sont pas très utiles pour des choses comme automatiser l'exportation de données.
Il n'est pas possible de générer de manière fiable corriger csv de la sortie de mysql -B -e 'SELECT ...'
parce que cela ne peut pas coder retours chariot et espace blanc dans les champs. Le drapeau' - s ' à mysql ne antislash échapper, et pourrait conduire à une solution correcte. Cependant, l'utilisation d'un langage de script (un avec des structures de données internes décentes qui est, pas bash), et les bibliothèques où les problèmes d'encodage ont déjà été soigneusement élaborés est beaucoup plus sûr.
j'ai pensé à écrire un script pour ceci, mais dès que j'ai pensé à ce que j'appellerais ça, ça m'est venu à l'esprit de chercher du travail préexistant sous le même nom. Bien que je ne l'ai pas examiné à fond, la solution à https://github.com/robmiller/mysql2csv semble prometteur. En fonction de votre application, l'approche yaml pour spécifier les commandes SQL pourrait ne pas plaire. Je ne suis pas non plus ravi de l'exigence d'une version plus récente de ruby que celle qui est fournie en standard avec mon ordinateur portable Ubuntu 12.04 ou les serveurs Debian Squeeze. Oui, je sais que je pourrais utiliser RVM, mais je préfère ne pas maintenir que pour un but aussi simple.
si tout va bien quelqu'un fera remarquer un outil approprié, qui a eu un peu de tests. Sinon, je le mettrai à jour quand j'en trouverai ou en écrirai un.
de votre ligne de commande, vous pouvez le faire:
mysql -h *hostname* -P *port number* --database=*database_name* -u *username* -p -e *your SQL query* | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > *output_file_name.csv*
crédits: exportation de la table D'Amazon RDS dans un fichier csv
- la logique :
CREATE TABLE () (SELECT data FROM other_table ) ENGINE=CSV ;
lorsque vous créez une table CSV, le serveur crée un fichier de format de table en le répertoire de base de données. Le fichier commence par le nom de la table et a une .frm extension. Le moteur de stockage crée également un fichier de données. Son nom commence par le nom de la table et a un .CSV extension. Le fichier de données est un fichier de texte brut. Lorsque vous stockez des données dans la table, le stockage moteur l'enregistre dans le fichier de données en valeurs séparées par des virgules format.
beaucoup de réponses sur cette page sont faibles parce qu'elles ne traitent pas le cas général de ce qui peut se produire dans le format CSV. par exemple, des virgules et des guillemets intégrés dans des champs et d'autres conditions qui apparaissent toujours en fin de compte. Nous avons besoin d'une solution générale qui fonctionne pour toutes les données D'entrée CSV valides.
Voici une solution simple et forte en Python:
#!/usr/bin/env python
import csv
import sys
tab_in = csv.reader(sys.stdin, dialect=csv.excel_tab)
comma_out = csv.writer(sys.stdout, dialect=csv.excel)
for row in tab_in:
comma_out.writerow(row)
nommez ce fichier tab2csv
, mettez - le sur votre chemin, donnez-lui les autorisations d'exécution, puis utilisation de la liste:
mysql OTHER_OPTIONS --batch --execute='select * from whatever;' | tab2csv >outfile.csv
les fonctions de manipulation CSV de Python couvrent les cases de coin pour le(S) format (s) D'entrée CSV.
cela pourrait être amélioré pour traiter de très gros fichiers via une approche de streaming.
pour développer sur les réponses précédentes, le suivant one-liner exporte une table simple comme un dossier tab-séparé. Il est adapté pour l'automatisation, l'exportation de la base de données tous les jours environ.
mysql -B -D mydatabase -e 'select * from mytable'
commodément, nous pouvons utiliser la même technique pour lister les tables de MySQL, et pour décrire les champs sur une seule table:
mysql -B -D mydatabase -e 'show tables'
mysql -B -D mydatabase -e 'desc users'
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
email varchar(128) NO UNI NULL
lastName varchar(100) YES NULL
title varchar(128) YES UNI NULL
userName varchar(128) YES UNI NULL
firstName varchar(100) YES NULL
c'est simple, et il fonctionne sur n'importe quoi sans avoir besoin du mode de fournée ou des fichiers de sortie:
select concat_ws(',',
concat('"', replace(field1, '"', '""'), '"'),
concat('"', replace(field2, '"', '""'), '"'),
concat('"', replace(field3, '"', '""'), '"'))
from your_table where etc;
explication:
- remplacer" par "" dans chaque zone -- > Remplacer (Zone 1, '"', '""')
- Surround chaque résultat entre guillemets --> concat('"', result1, '"')
- placez une virgule entre chaque résultat Cité --> concat_ws(',', quoted1, quoted2, ...)
C'est ça!
aussi, si vous effectuez la requête sur la ligne de commande Bash, je crois que la commande tr
peut être utilisée pour substituer les onglets par défaut aux délimiteurs arbitraires.
$ echo "SELECT * FROM Table123" | mysql Database456 | tr "\t" ,
alternativement à la réponse ci-dessus, vous pouvez avoir une table MySQL qui utilise le moteur CSV.
Alors vous aurez un fichier sur votre disque dur qui sera toujours dans un format CSV qui vous pouvez simplement copier sans traitement.
Voici ce que je fais:
echo $QUERY | \
mysql -B $MYSQL_OPTS | \
perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' | \
mail -s 'report' person@address
le script perl (sniped from elsewhere) fait un bon travail de convertir les champs espacés de l'onglet en CSV.
pas exactement comme un format CSV, mais tee
commande de MySQL client peut être utilisé pour enregistrer la sortie dans un local fichier:
tee foobar.txt
SELECT foo FROM bar;
vous pouvez le désactiver en utilisant notee
.
le problème avec SELECT … INTO OUTFILE …;
est qu'il nécessite la permission d'écrire des fichiers sur le serveur.
construire sur user7610, voici la meilleure façon de le faire. Avec mysql outfile
, il y a eu 60 minutes de problèmes de propriété des fichiers et de réécriture.
ce n'est pas cool, mais ça a marché en 5 minutes.
php csvdump.php localhost root password database tablename > whatever-you-like.csv
<?php
$server = $argv[1];
$user = $argv[2];
$password = $argv[3];
$db = $argv[4];
$table = $argv[5];
mysql_connect($server, $user, $password) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
// fetch the data
$rows = mysql_query('SELECT * FROM ' . $table);
$rows || die(mysql_error());
// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');
// output the column headings
$fields = [];
for($i = 0; $i < mysql_num_fields($rows); $i++) {
$field_info = mysql_fetch_field($rows, $i);
$fields[] = $field_info->name;
}
fputcsv($output, $fields);
// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);
?>
cette réponse utilise Python et une bibliothèque de tiers populaire, PyMySQL . Je l'ajoute parce que la bibliothèque csv de Python est assez puissante pour correctement gérer de nombreuses saveurs différentes de .csv
et aucune autre réponse n'utilise le code Python pour interagir avec la base de données.
import contextlib
import csv
import datetime
import os
# https://github.com/PyMySQL/PyMySQL
import pymysql
SQL_QUERY = """\
SELECT * FROM my_table WHERE my_attribute = 'my_attribute';
"""
# embedding passwords in code gets nasty when you use version control
# the environment isn't much better, but this is an example
# http://stackoverflow.com/questions/12461484/is-it-secure-to-store-passwords-as-environment-variables-rather-than-as-plain-t
SQL_USER = os.environ['SQL_USER']
SQL_PASS = os.environ['SQL_PASS']
connection = pymysql.connect(host='localhost',
user=SQL_USER,
password=SQL_PASS,
db='dbname')
with contextlib.closing(connection):
with connection.cursor() as cursor:
cursor.execute(SQL_QUERY)
# Hope you have enough memory :)
results = cursor.fetchall()
output_file = 'my_query-{}.csv'.format(datetime.datetime.today().strftime('%Y-%m-%d'))
with open(output_file, 'w', newline='') as csvfile:
# http://stackoverflow.com/a/17725590/2958070 about lineterminator
csv_writer = csv.writer(csvfile, lineterminator='\n')
csv_writer.writerows(results)
en utilisant la solution Postée par Tim, j'ai créé ce script bash pour faciliter le processus (mot de passe root est demandé, mais vous pouvez modifier le script facilement pour demander à n'importe quel autre utilisateur):
#!/bin/bash
if [ "" == "" ];then
echo "Usage: "151900920" DATABASE TABLE [MYSQL EXTRA COMMANDS]"
exit
fi
DBNAME=
TABLE=
FNAME=..csv
MCOMM=
echo "MySQL password:"
stty -echo
read PASS
stty echo
mysql -uroot -p$PASS $MCOMM $DBNAME -B -e "SELECT * FROM $TABLE;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $FNAME
il va créer un fichier nommé: base de données.table.csv
essayez ce code:
SELECT 'Column1', 'Column2', 'Column3', 'Column4', 'Column5'
UNION ALL
SELECT column1, column2,
column3 , column4, column5 FROM demo
INTO OUTFILE '/tmp/demo.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
pour plus d'informations: http://dev.mysql.com/doc/refman/5.1/en/select-into.html
si vous avez PHP configuré sur le serveur, vous pouvez utiliser mysql2csv pour exporter un fichier CSV (réellement valide) pour une requête mysql abitrary. Voir ma réponse à MySQL-SELECT * dans OUTFILE LOCAL ? pour un peu plus de contexte/info.
j'ai essayé de maintenir les noms des options de mysql
donc il devrait être suffisant de fournir les options --file
et --query
:
./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"
"Installer mysql2csv
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
(télécharger le contenu du gist, vérifier checksum et le rendre exécutable).
S'il y a PHP installé sur la machine que vous utilisez, vous pouvez écrire un script PHP pour le faire. Il nécessite L'installation de PHP avec L'extension MySQL installée.
vous pouvez appeler l'interpréteur PHP depuis la ligne de commande comme suit:
php --php-ini path/to/php.ini your-script.php
j'inclus le commutateur --php-ini
, car vous devrez peut-être utiliser votre propre configuration PHP pour activer L'extension MySQL. Sur PHP 5.3.0+ cette extension est activée par défaut, donc ce n'est plus nécessaire d'utiliser la configuration pour l'activer.
alors vous pouvez écrire votre script d'exportation comme n'importe quel script PHP normal:
<?php
#mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("mydb") or die(mysql_error());
$result = mysql_query("SELECT * FROM table_with_the_data p WHERE p.type = $typeiwant");
$result || die(mysql_error());
while($row = mysql_fetch_row($result)) {
$comma = false;
foreach ($row as $item) {
# Make it comma separated
if ($comma) {
echo ',';
} else {
$comma = true;
}
# Quote the quotes
$quoted = str_replace("\"", "\"\"", $item);
# Quote the string
echo "\"$quoted\"";
}
echo "\n";
}
?>
l'avantage de cette méthode est, qu'elle n'a pas de problèmes avec varchar et les champs de texte, qui ont du texte contenant des lignes nouvelles. Ces champs sont correctement cités et les nouvelles lignes qu'ils contiennent seront interprétées par le lecteur CSV comme faisant partie du texte, pas comme des séparateurs d'enregistrements. C'est quelque chose qui est difficile de corriger par la suite avec sed ou ainsi.
minuscule script bash pour faire simple requête à CSV dumps, inspiré par https://stackoverflow.com/a/5395421/2841607 .
#!/bin/bash
# = query to execute
# = outfile
# = mysql database name
# = mysql username
if [ -z "" ]; then
echo "Query not given"
exit 1
fi
if [ -z "" ]; then
echo "Outfile not given"
exit 1
fi
MYSQL_DB=""
MYSQL_USER="root"
if [ ! -z "" ]; then
MYSQL_DB=
fi
if [ ! -z "" ]; then
MYSQL_USER=
fi
if [ -z "$MYSQL_DB" ]; then
echo "Database name not given"
exit 1
fi
if [ -z "$MYSQL_USER" ]; then
echo "Database user not given"
exit 1
fi
mysql -u $MYSQL_USER -p -D $MYSQL_DB -B -s -e "" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >
echo "Written to "
le script bash suivant fonctionne pour moi. Il obtient également le schéma pour les tables demandées.
#!/bin/bash
#
# export mysql data to CSV
#/q/how-to-output-mysql-query-results-in-csv-format-51776/""
local l_msg=""
echo -e "${l_color}$l_msg${endColor}"
}
#
# error
#
# show the given error message on stderr and exit
#
# params:
# 1: l_msg - the error message to display
#
error() {
local l_msg=""
# use ansi red for error
color_msg $red "Error:" 1>&2
color_msg $red "\t$l_msg" 1>&2
usage
}
#
# display usage
#
usage() {
echo "usage: "151900920" [-h|--help]" 1>&2
echo " -o | --output csvdirectory" 1>&2
echo " -d | --database database" 1>&2
echo " -t | --tables tables" 1>&2
echo " -p | --password password" 1>&2
echo " -u | --user user" 1>&2
echo " -hs | --host host" 1>&2
echo " -gs | --get-schema" 1>&2
echo "" 1>&2
echo " output: output csv directory to export mysql data into" 1>&2
echo "" 1>&2
echo " user: mysql user" 1>&2
echo " password: mysql password" 1>&2
echo "" 1>&2
echo " database: target database" 1>&2
echo " tables: tables to export" 1>&2
echo " host: host of target database" 1>&2
echo "" 1>&2
echo " -h|--help: show help" 1>&2
exit 1
}
#
# show help
#
help() {
echo ""151900920" Help" 1>&2
echo "===========" 1>&2
echo ""151900920" exports a csv file from a mysql database optionally limiting to a list of tables" 1>&2
echo " example: "151900920" --database=cms --user=scott --password=tiger --tables=person --output person.csv" 1>&2
echo "" 1>&2
usage
}
domysql() {
mysql --host $host -u$user --password=$password $database
}
getcolumns() {
local l_table=""
echo "describe $l_table" | domysql | cut -f1 | grep -v "Field" | grep -v "Warning" | paste -sd "," - 2>/dev/null
}
host="localhost"
mysqlfiles="/var/lib/mysql-files/"
# parse command line options
while true; do
#echo "option "
case "" in
# options without arguments
-h|--help) usage;;
-d|--database) database="" ; shift ;;
-t|--tables) tables="" ; shift ;;
-o|--output) csvoutput="" ; shift ;;
-u|--user) user="" ; shift ;;
-hs|--host) host="" ; shift ;;
-p|--password) password="" ; shift ;;
-gs|--get-schema) option="getschema";;
(--) shift; break;;
(-*) echo ""151900920": error - unrecognized option " 1>&2; usage;;
(*) break;;
esac
shift
done
# checks
if [ "$csvoutput" == "" ]
then
error "ouput csv directory not set"
fi
if [ "$database" == "" ]
then
error "mysql database not set"
fi
if [ "$user" == "" ]
then
error "mysql user not set"
fi
if [ "$password" == "" ]
then
error "mysql password not set"
fi
color_msg $blue "exporting tables of database $database"
if [ "$tables" = "" ]
then
tables=$(echo "show tables" | domysql)
fi
case $option in
getschema)
rm $csvoutput$database.schema
for table in $tables
do
color_msg $blue "getting schema for $table"
echo -n "$table:" >> $csvoutput$database.schema
getcolumns $table >> $csvoutput$database.schema
done
;;
*)
for table in $tables
do
color_msg $blue "exporting table $table"
cols=$(grep "$table:" $csvoutput$database.schema | cut -f2 -d:)
if [ "$cols" = "" ]
then
cols=$(getcolumns $table)
fi
ssh $host rm $mysqlfiles/$table.csv
cat <<EOF | mysql --host $host -u$user --password=$password $database
SELECT $cols FROM $table INTO OUTFILE '$mysqlfiles$table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
EOF
scp $host:$mysqlfiles/$table.csv $csvoutput$table.csv.raw
(echo "$cols"; cat $csvoutput$table.csv.raw) > $csvoutput$table.csv
rm $csvoutput$table.csv.raw
done
;;
esac
vous pouvez utiliser la commande ci-dessous de votre emplacement SQL:
mysql-h(nom d'hôte/IP>) -u(nom d'utilisateur) -p(mot de passe) databasename <(query.sql) > outputFILE(.txt/.xls) "
E. g hostname-X. x.x.x
uname - nom d'utilisateur
mot de passe-Mot de passe
DBName-employeeDB
queryFile-employee.sql
outputFile - outputFile.xls
mysql-hx.x.x.x -uusername -pmotdepasse employeeDB< employé.sql > outputFile.xls
assurez-vous que vous exécutez la commande à partir du répertoire où SQL est situé ou mentionnez le chemin complet de l'emplacement sql dans la commande ci-dessus.