the - select export mysql
¿Cómo generar resultados de consulta MySQL en formato CSV? (29)
¿Existe una manera fácil de ejecutar una consulta MySQL desde la línea de comandos de Linux y generar los resultados en formato CSV ?
Esto es lo que estoy haciendo ahora:
mysql -u uid -ppwd -D dbname << EOQ | sed -e ''s/ /,/g'' | tee list.csv
select id, concat("/"",name,"/"") as name
from students
EOQ
Se ensucia cuando hay muchas columnas que deben estar entre comillas, o si hay comillas en los resultados que deben escaparse.
mysql - lote, -B
Imprima los resultados utilizando la pestaña como separador de columna, con cada fila en una nueva línea. Con esta opción, mysql no usa el archivo histórico. El modo por lotes da como resultado un formato de salida no tabular y el escape de caracteres especiales. El escape se puede deshabilitar usando el modo sin procesar; vea la descripción para la opción --raw.
Esto le dará un archivo separado por pestañas. Dado que las comas (o cadenas que contienen comas) no se escapan, no es sencillo cambiar el delimitador a comas.
- lógica:
CREATE TABLE () (SELECT data FROM other_table ) ENGINE=CSV ;
Cuando crea una tabla CSV, el servidor crea un archivo de formato de tabla en el directorio de la base de datos. El archivo comienza con el nombre de la tabla y tiene una extensión .frm. El motor de almacenamiento también crea un archivo de datos. Su nombre comienza con el nombre de la tabla y tiene una extensión .CSV. El archivo de datos es un archivo de texto plano. Cuando almacena datos en la tabla, el motor de almacenamiento los guarda en el archivo de datos en formato de valores separados por comas.
Además, si está realizando la consulta en la línea de comando Bash, creo que el comando tr se puede usar para sustituir las pestañas predeterminadas por delimitadores arbitrarios.
$ echo "SELECT * FROM Table123" | mysql Database456 | tr "/t" ,
Aquí hay una manera bastante retorcida de hacerlo. Lo encontré en algún lugar, no puedo tomar ningún crédito
mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/''//'/;s//t//",/"/g;s/^//"/;s/$//"/;s//n//g" > vehicle_categories.csv
Funciona bastante bien Una vez más, aunque una expresión regular prueba solo escribe.
Explicación Regex:
- s /// significa sustituir lo que está entre el primer // con lo que hay entre el segundo //
- la "g" al final es un modificador que significa "todas las instancias, no solo las primeras"
- ^ (en este contexto) significa principio de línea
- $ (en este contexto) significa fin de línea
Entonces, juntándolo todo:
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
Como alternativa a la respuesta anterior, puede tener una tabla MySQL que utiliza el motor CSV.
Luego, tendrá un archivo en su disco duro que siempre estará en un formato CSV que podría copiar sin procesarlo.
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'';
Usando este comando, los nombres de columnas no serán exportados.
También tenga en cuenta que /var/lib/mysql-files/orders.csv estará en el servidor que ejecuta MySQL. El usuario con el que se está ejecutando el proceso MySQL debe tener permisos para escribir en el directorio elegido, o el comando fallará.
Si desea escribir la salida en su máquina local desde un servidor remoto (especialmente una máquina alojada o virtualizada como Heroku o Amazon RDS), esta solución no es adecuada.
Desde tu línea de comando, puedes hacer esto:
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éditos: Exportación de tablas de Amazon RDS a un archivo csv
El siguiente guión de bash funciona para mí. Opcionalmente también obtiene el esquema para las tablas solicitadas.
#!/bin/bash
#
# export mysql data to CSV
#https://.com/questions/356578/how-to-output-mysql-query-results-in-csv-format
#
#ansi colors
#http://www.csc.uvic.ca/~sae/seng265/fall04/tips/s265s047-tips/bash-using-colors.html
blue=''/033[0;34m''
red=''/033[0;31m''
green=''/033[0;32m'' # ''/e[1;32m'' is too bright for white bg.
endColor=''/033[0m''
#
# a colored message
# params:
# 1: l_color - the color of the message
# 2: l_msg - the message to display
#
color_msg() {
local l_color="$1"
local l_msg="$2"
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="$1"
# 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: $0 [-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 "$0 Help" 1>&2
echo "===========" 1>&2
echo "$0 exports a csv file from a mysql database optionally limiting to a list of tables" 1>&2
echo " example: $0 --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="$1"
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 $1"
case "$1" in
# options without arguments
-h|--help) usage;;
-d|--database) database="$2" ; shift ;;
-t|--tables) tables="$2" ; shift ;;
-o|--output) csvoutput="$2" ; shift ;;
-u|--user) user="$2" ; shift ;;
-hs|--host) host="$2" ; shift ;;
-p|--password) password="$2" ; shift ;;
-gs|--get-schema) option="getschema";;
(--) shift; break;;
(-*) echo "$0: error - unrecognized option $1" 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
Esta respuesta utiliza Python y una biblioteca popular de terceros, PyMySQL . Lo estoy agregando porque la biblioteca csv de Python es lo suficientemente potente como para manejar correctamente muchos tipos diferentes de .csv y ninguna otra respuesta está utilizando el código de Python para interactuar con la base de datos.
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 is not much better, but this is an example
# http://.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://.com/a/17725590/2958070 about lineterminator
csv_writer = csv.writer(csvfile, lineterminator=''/n'')
csv_writer.writerows(results)
Esto es lo que hago:
echo $QUERY | /
mysql -B $MYSQL_OPTS | /
perl -F"/t" -lane ''print join ",", map {s/"/""/g; /^[/d.]+$/ ? $_ : qq("$_")} @F '' | /
mail -s ''report'' person@address
El script de Perl (cortado desde otro lugar) hace un buen trabajo al convertir los campos espaciados por tabuladores a CSV.
Esto es simple, y funciona en cualquier cosa sin necesidad de modo por lotes o archivos de salida:
select concat_ws('','',
concat(''"'', replace(field1, ''"'', ''""''), ''"''),
concat(''"'', replace(field2, ''"'', ''""''), ''"''),
concat(''"'', replace(field3, ''"'', ''""''), ''"''))
from your_table where etc;
Explicación:
- Reemplace "con" en cada campo -> reemplazar (campo1, ''"'', ''" "'')
- Rodee cada resultado entre comillas -> concat (''"'', resultado1, ''"'')
- Coloque una coma entre cada resultado citado -> concat_ws ('','', quoted1, quoted2, ...)
¡Eso es!
La solución OUTFILE proporcionada por Paul Tomblin hace que se escriba un archivo en el propio servidor MySQL, por lo que esto funcionará solo si tiene acceso a FILE , así como acceso de inicio de sesión u otros medios para recuperar el archivo desde ese cuadro.
Si no tiene dicho acceso, y la salida delimitada por tabuladores es un sustituto razonable de CSV (por ejemplo, si su objetivo final es importar a Excel), entonces la solución de Serbaut (con mysql --batch y opcionalmente --raw ) es el camino a seguir.
Muchas de las respuestas en esta página son débiles porque no manejan el caso general de lo que puede ocurrir en formato CSV. por ejemplo, comas y comillas incrustadas en campos y otras condiciones que siempre surgen con el tiempo. Necesitamos una solución general que funcione para todos los datos de entrada CSV válidos.
Aquí hay una solución simple y fuerte 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)
Asigne un nombre a ese archivo tab2csv , póngalo en su ruta, déle permisos de ejecución y, a continuación, tab2csv este listado
mysql OTHER_OPTIONS --batch --execute=''select * from whatever;'' | tab2csv >outfile.csv
Las funciones de manejo de CSV de Python cubren casos de esquina para formatos de entrada de CSV.
Esto podría mejorarse para manejar archivos muy grandes a través de un enfoque de transmisión.
No exactamente como un formato CSV, pero se puede usar el comando tee del cliente MySQL para guardar la salida en un archivo local :
tee foobar.txt
SELECT foo FROM bar;
Puedes desactivarlo usando notee .
El problema con SELECT … INTO OUTFILE …; Es que requiere permiso para escribir archivos en el servidor.
Para ampliar las respuestas anteriores, la siguiente línea de una línea exporta una sola tabla como un archivo separado por tabulaciones. Es adecuado para la automatización, exportando la base de datos todos los días más o menos.
mysql -B -D mydatabase -e ''select * from mytable''
Convenientemente, podemos usar la misma técnica para enumerar las tablas de MySQL y para describir los campos en una sola tabla:
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
Prueba este código:
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'';
Para obtener más información: http://dev.mysql.com/doc/refman/5.1/en/select-into.html
Qué tal si:
mysql your_database -p < my_requests.sql | awk ''{print $1","$2}'' > out.csv
Si hay PHP instalado en la máquina que está utilizando, puede escribir un script PHP para hacerlo. Requiere que la instalación de PHP tenga instalada la extensión MySQL.
Puede llamar al intérprete de PHP desde la línea de comando así:
php --php-ini path/to/php.ini your-script.php
Estoy incluyendo el --php-ini , porque es posible que necesites usar tu propia configuración de PHP que habilita la extensión MySQL. En PHP 5.3.0+, esa extensión está habilitada de forma predeterminada, por lo que ya no es necesario usar la configuración para habilitarla.
Luego puedes escribir tu script de exportación como cualquier 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";
}
?>
La ventaja de este método es que no tiene problemas con los campos de texto y varchar, que tienen texto que contiene nuevas líneas. Esos campos se citan correctamente y esas líneas nuevas en ellos serán interpretadas por el lector CSV como parte del texto, no como separadores de registros. Eso es algo que es difícil de corregir después con sed o algo así.
Si tiene PHP configurado en el servidor, puede usar mysql2csv para exportar un archivo CSV (realmente válido) para una consulta mysql absoluta. Vea mi respuesta en MySQL: ¿SELECCIONAR * EN PERFIL LOCAL? Para un poco más de contexto / información.
Traté de mantener los nombres de las opciones desde mysql por lo que debería ser suficiente para proporcionar las opciones --file y --query :
./mysql2csv --file="/tmp/result.csv" --query=''SELECT 1 as foo, 2 as bar;'' --user="username" --password="password"
"Instalar" mysql2csv través de
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
(descargue el contenido de la esencia, verifique la suma de comprobación y hágalo ejecutable).
Sobre la base de user7610, esta es la mejor manera de hacerlo. Con mysql outfile hubo 60 minutos de propiedad de archivos y problemas de sobrescritura.
No está bien, pero funcionó en 5 minutos.
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);
?>
Solo Unix / Cygwin , canalícelo a través de ''tr'':
mysql <database> -e "<query here>" | tr ''/t'' '','' > data.csv
NB: Esto no maneja ni las comas incrustadas, ni las pestañas incrustadas.
Tiny bash script para realizar consultas simples a volcados CSV, inspirado en https://.com/a/5395421/2841607 .
#!/bin/bash
# $1 = query to execute
# $2 = outfile
# $3 = mysql database name
# $4 = mysql username
if [ -z "$1" ]; then
echo "Query not given"
exit 1
fi
if [ -z "$2" ]; then
echo "Outfile not given"
exit 1
fi
MYSQL_DB=""
MYSQL_USER="root"
if [ ! -z "$3" ]; then
MYSQL_DB=$3
fi
if [ ! -z "$4" ]; then
MYSQL_USER=$4
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 "$1" | sed "s/''//'/;s//t//",/"/g;s/^//"/;s/$//"/;s//n//g" > $2
echo "Written to $2"
Todas las soluciones aquí hasta la fecha, excepto la del banco de trabajo Mysql, son incorrectas y posiblemente muy inseguras (es decir, problemas de seguridad) para al menos algún contenido posible en la base de datos de mysql.
Mysql Workbench (y de manera similar, PHPMyAdmin) proporciona una solución formalmente correcta, pero está diseñado para descargar la salida a la ubicación de un usuario. No son tan útiles para cosas como la automatización de la exportación de datos.
No es posible generar csv correctamente correctos desde la salida de mysql -B -e ''SELECT ...'' porque eso no puede codificar retornos de carro ni espacios en blanco en los campos. La bandera ''-s'' para mysql hace un escape de barra invertida, y puede llevar a una solución correcta. Sin embargo, es mucho más seguro utilizar un lenguaje de scripting (uno con estructuras de datos internas decentes, no bash) y bibliotecas donde los problemas de codificación ya se han resuelto cuidadosamente.
Pensé en escribir un guión para esto, pero tan pronto como pensé en cómo lo llamaría, se me ocurrió buscar un trabajo preexistente con el mismo nombre. Aunque no lo he analizado a fondo, la solución en https://github.com/robmiller/mysql2csv parece prometedora. Dependiendo de su aplicación, el enfoque de yaml para especificar los comandos SQL puede o no ser atractivo. Tampoco estoy entusiasmado con el requisito de una versión más reciente de ruby que viene de serie con mi portátil Ubuntu 12.04 o los servidores Debian Squeeze. Sí, sé que podría usar RVM, pero preferiría no mantenerlo con un propósito tan simple.
Esperemos que alguien señale una herramienta adecuada, que haya tenido un poco de prueba. De lo contrario probablemente actualizaré esto cuando encuentre o escriba uno.
Usando la solución publicada por Tim, creé este script bash para facilitar el proceso (se solicita la contraseña de root, pero puede modificar el script fácilmente para pedir a cualquier otro usuario):
#!/bin/bash
if [ "$1" == "" ];then
echo "Usage: $0 DATABASE TABLE [MYSQL EXTRA COMMANDS]"
exit
fi
DBNAME=$1
TABLE=$2
FNAME=$1.$2.csv
MCOMM=$3
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
Creará un archivo llamado: database.table.csv
MySQL Workbench puede exportar conjuntos de registros a CSV, y parece manejar muy bien las comas en los campos. El CSV se abre en multa de OpenOffice.
Lo que funcionó para mí:
SELECT *
FROM students
WHERE foo = ''bar''
LIMIT 0,1200000
INTO OUTFILE ''./students-1200000.csv''
FIELDS TERMINATED BY '','' ESCAPED BY ''"''
ENCLOSED BY ''"''
LINES TERMINATED BY ''/r/n'';
Ninguna de las soluciones en este hilo funcionó para mi caso particular, tenía datos bastante json dentro de una de las columnas, que se desordenarían en mi salida csv. Para aquellos con un problema similar, intente líneas terminadas por / r / n en su lugar.
También otro problema para aquellos que intentan abrir el csv con Microsoft Excel, tenga en cuenta que hay un límite de 32.767 caracteres que puede contener una sola celda, por encima de eso se desborda a las filas de abajo. Para identificar qué registros en una columna tienen el problema, use la consulta a continuación. Luego puede truncar esos registros o manejarlos como desee.
SELECT id,name,CHAR_LENGTH(json_student_description) AS ''character length''
FROM students
WHERE CHAR_LENGTH(json_student_description)>32767;
Puedes usar el siguiente comando desde tu ubicación SQL:
" mysql -h (nombre de host / IP>) -u (nombre de usuario) -p (contraseña) databasename <(query.sql)> outputFILE (.txt / .xls) "
ej. nombre de host -xxxx
uname - nombre de usuario
contraseña - contraseña
DBName - employeeDB
queryFile - employee.sql
outputFile - outputFile.xls
mysql -hx.xxx -uusername -ppassword employeeDB <employee.sql> outputFile.xls
Asegúrese de que está ejecutando el comando desde el directorio donde se encuentra SQL o mencione la ruta completa de la ubicación sql en el comando anterior.
$ mysql your_database --password=foo < my_requests.sql > out.csv
Que es pestaña separada. Pipelo así para obtener un verdadero CSV (gracias @therefromhere):
... .sql | sed ''s//t/,/g'' > out.csv