¿Cómo puedo generar resultados de consultas de MySQL en formato CSV?

1315

¿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 vuelve complicado cuando hay muchas columnas que deben estar rodeadas de comillas, o si hay comillas en los resultados que deben escaparse.

9
1902

Desde Guardar los resultados de la consulta MySQL en un archivo de texto o CSV :

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';

Nota: Es posible que sea necesario reordenar esa sintaxis para

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

en versiones más recientes de MySQL.

Con este comando, los nombres de las columnas no se exportarán.

También tenga en cuenta que /var/lib/mysql-files/orders.csvestará en el servidor que ejecuta MySQL. El usuario bajo el que se ejecuta 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.

2
  • 2
    Puede que necesite ser root. "El archivo se crea en el host del servidor, por lo que debe tener el privilegio FILE para usar esta sintaxis". - dev.mysql.com/doc/refman/8.0/en/select-into.html 23 de junio a las 21:10
  • Sí, depende tanto de los privilegios de MySQL como de los privilegios de escritura en algún lugar del sistema de archivos local.
    cazort
    3 de julio a las 17:12
510
mysql your_database --password=foo < my_requests.sql > out.csv

Que está separado por tabuladores. Póngalo así para obtener un verdadero CSV (gracias al usuario John Carter ):

... .sql | sed 's/\t/,/g' > out.csv
3
  • 32
    Está separado por tabulaciones, no por comas.
    Flimm
    30/08/11 a las 15:13
  • 14
    @Flimm, asumiendo que no tiene comas / pestañas incrustadas en los campos, puede convertirlo canalizando el resultado en | sed 's/\t/,/g' 10/11/11 a las 4:42
  • 120
    el sed 'fix' no compensa las comas que pueden aparecer en cualquiera de los datos seleccionados y sesgará las columnas generadas en consecuencia
    Joey T
    11/12/12 a las 1:17
224

mysql --batch, -B

Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file. Batch mode results in non-tabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

Esto le dará un archivo separado por tabulaciones. Dado que las comas (o cadenas que contienen comas) no se escapan, no es sencillo cambiar el delimitador a coma.

0
164

Aquí hay una forma bastante retorcida de hacerlo [1] :

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, sin embargo, una expresión regular demuestra ser de solo escritura.


Explicación de expresiones regulares:

  • s /// significa sustituir lo que está entre el primero // con lo que está entre el segundo //
  • la "g" al final es un modificador que significa "todas las instancias, no solo la primera"
  • ^ (en este contexto) significa comienzo de línea
  • $ (en este contexto) significa fin de línea

Entonces, poniéndolo todo junto:

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

[1] Lo encontré en alguna parte y no puedo atribuirme ningún crédito.

0
109

Canalícelo a través de 'tr' (solo Unix / Cygwin ):

mysql <database> -e "<query here>" | tr '\t' ',' > data.csv

NB: Esto no maneja comas incrustadas ni pestañas incrustadas.

2
  • Me sorprende lo menos votada que es esta solución. Actualmente, la mejor solución requiere un privilegio que muchos usuarios de la base de datos no tienen (y por una buena razón; es un riesgo de seguridad para los administradores darlo). Su solución aquí funciona sin ningún privilegio especial, y también probablemente podría mejorarse para abordar las deficiencias de las comas o tabulaciones, posiblemente con una sustitución en la propia consulta.
    cazort
    3 de julio a las 17:09
  • Esta es una solución brillante, funcionó a la perfección.
    J86
    16 de agosto a las 11:44
67

Esto me salvó un par de veces. ¡Es rápido y funciona!

--batch Print results using tab as the column separator, with each row on a new line.

--raw disables character escaping (\n, \t, \0, and \)

Ejemplo:

mysql -udemo_user -p -h127.0.0.1 --port=3306 \
   --default-character-set=utf8mb4 --database=demo_database \
   --batch --raw < /tmp/demo_sql_query.sql > /tmp/demo_csv_export.tsv

Para completar, puede convertir a CSV (pero tenga cuidado porque las pestañas podrían estar dentro de los valores de campo, por ejemplo, campos de texto)

tr '\t' ',' < file.tsv > file.csv

0
42

La solución OUTFILE proporcionada por Paul Tomblin hace que se escriba un archivo en el servidor MySQL, por lo que esto funcionará solo si tiene acceso a ARCHIVO , así como acceso de inicio de sesión u otros medios para recuperar el archivo de ese cuadro.

Si no tiene dicho acceso, y la salida delimitada por tabulaciones es un sustituto razonable de CSV (por ejemplo, si su objetivo final es importar a Excel), entonces la solución de serbaut (usando mysql --batchy opcionalmente --raw) es el camino a seguir.

0
41

MySQL Workbench puede exportar conjuntos de registros a CSV y parece manejar muy bien las comas en los campos. El CSV se abre en OpenOffice Calc bien.

5
  • 3
    Un millón de gracias David. Después de pasar 3 horas logrando que las nuevas líneas salieran correctamente para el contenido HTML en los datos, usé MySQL Workbench y en 2 minutos tenía mi archivo CSV listo.
    mr-euro
    11/07/12 a las 17:02
  • Acabo de descubrir que también se puede guardar como XML, lo cual es genial. Espero migrar de una aplicación a otra utilizando XSLT para transformar este XML en un archivo CSV adecuado para importar a la aplicación de destino. 5/08/12 a las 21:27
  • mysql workbench es la mejor opción para la función de importación y exportación. 5 de agosto de 2015 a las 3:20
  • Bueno, pero cada vez que el banco de trabajo limita los registros seleccionados hasta 1000 y cuando se trata de muchos más registros, no funciona tan bien, la misma condición para la importación a menudo se bloquea si trato de importar un archivo csv relativamente grande en el base de datos mysql por workbench. 29/10/2016 a las 6:42
  • 1
    Acabo de exportar con éxito más de medio millón de filas usando mysql workbench, por lo que los archivos grandes no parecen ser un problema. Solo debe asegurarse de eliminar el límite de selección antes de ejecutar su consulta y es posible que también deba aumentar los siguientes valores en su archivo my.ini: max_allowed_packet = 500M, net_read_timeout = 600, net_write_timeout = 600
    Vincent
    22/04/18 a las 15:47
33

Usar:

mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv
2
  • 2
    Realmente me gusta esta. Es mucho más limpio y me gusta el uso de awk. Sin embargo, probablemente me hubiera ido con esto: mysql -uUser -pPassword your_database < my_requests.sql | awk 'BEGIN{OFS="=";} {print $1,$2}' > out.csv
    Josh
    11/04/12 a las 0:00
  • 8
    Esto falla para los valores de campo con espacios. 10/04/2015 a las 6:17
31

Todas las soluciones aquí hasta la fecha, excepto la de MySQL Workbench , son incorrectas y posiblemente inseguras (es decir, problemas de seguridad) para al menos parte del contenido posible en la base de datos MySQL.

MySQL Workbench (y de manera similar phpMyAdmin ) proporcionan una solución formalmente correcta, pero están diseñados 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 contenido CSV correcto y confiable a partir de la salida de mysql -B -e 'SELECT ...'porque no puede codificar retornos de carro y espacios en blanco en los campos. La bandera '-s' mysqlhace un escape de barra invertida y podría conducir a una solución correcta. Sin embargo, es mucho más seguro usar un lenguaje de secuencias de comandos (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. Si bien no lo he repasado a fondo, mysql2csv parece prometedor. Sin embargo, dependiendo de su aplicación, el enfoque YAML para especificar los comandos SQL puede resultar atractivo o no. Tampoco estoy encantado con el requisito de una versión más reciente de Ruby que la que viene de serie con mi computadora portátil Ubuntu 12.04 (Precise Pangolin) o servidores Debian 6.0 (Squeeze). Sí, sé que podría usar RVM, pero prefiero no mantener eso para un propósito tan simple.

6
  • 1
    Tiene razón, para cadenas complejas en la tabla debe usar una biblioteca decente, no solo bash. Creo que nodejs tiene mejores soluciones para este tipo de acciones. como este ejemplo
    yeya
    28/07/2016 a las 15:28
  • 1
    Hola, buena respuesta, agregaría un enlace a la solución de Python propuesta a continuación stackoverflow.com/a/35123787/1504300 , que funciona bien y es muy simple. Intenté editar tu publicación, pero la edición fue rechazada. 2 de junio de 2017 a las 12:44
  • El script mysql2csv de Rob Miller insiste en conectarse a la base de datos en sí, ya sea a través de una red o un socket, y no se puede usar como una tubería de estilo Unix. Tal vez sea necesario, pero realmente limita el uso. 9/09/20 a las 19:21
  • @chrisinmtown, ¿qué le gustaría agregar? ¿Salida de myslqldump tal vez? Como he señalado, la salida de mysql -Bno se puede arreglar.
    mc0e
    4 oct.20 a las 14:03
  • 1
    @ mc0e Quiero tubería en la salida de mysqldump, leyó que a medida que la entrada estándar. 5 oct.20 a las 13:20
30

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 eventualmente. Necesitamos una solución general que funcione para todos los datos de entrada CSV válidos.

Aquí hay una solución simple y sólida 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)

Nombra ese archivo tab2csv, ponlo en tu ruta, dale permisos de ejecución, luego úsalo así:

mysql OTHER_OPTIONS --batch --execute='select * from whatever;' | tab2csv > outfile.csv

Las funciones de manejo de CSV de Python cubren casos de esquina para los formatos de entrada CSV.

Esto podría mejorarse para manejar archivos muy grandes a través de un enfoque de transmisión.

5
  • 10
    Una solución aún más confiable sería conectarse realmente a la base de datos con Python, entonces debería ser más fácil hacer lo que necesita hacer para lidiar con conjuntos de datos más grandes (fragmentación de resultados, transmisión, etc.). 4 de febrero de 2016 a las 15:52
  • @JoshRumbut, muy tarde, pero hice stackoverflow.com/a/41840534/2958070 para complementar tu comentario
    Ben
    30/08/19 a las 16:28
  • Consulte stackoverflow.com/questions/356578/… para obtener una versión extendida de este script con un dialecto de entrada que maneja comas incrustadas y comillas dobles. 10/09/20 a las 13:51
  • ¿Cuál es la esencia de esto? ¿Estás usando una biblioteca? ¿Una biblioteca incorporada? ¿Puede vincular a la documentación , etc.? (Pero sin "Editar:", "Actualizar:" o similar; la respuesta debería aparecer como si estuviera escrita hoy). 6 de agosto a las 11:34
  • ¿Qué se necesitaría para un enfoque de transmisión? ¿Puedes desarrollar un poco tu respuesta? (Pero sin "Editar:", "Actualizar:" o similar; la respuesta debería aparecer como si estuviera escrita hoy). 12 de agosto a las 9:30
27

Desde su línea de comando, puede 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

1
  • ese es un delineador loco. felicitaciones 7 de nov. De 2017 a las 2:47
17

Esta respuesta usa Python y una popular biblioteca de terceros, PyMySQL . Lo estoy agregando porque la biblioteca csv de Python es lo suficientemente poderosa como para manejar correctamente muchos sabores diferentes .csvy ninguna otra respuesta usa código 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
# https://stackoverflow.com/questions/12461484
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)
4
  • Ya se proporcionó una respuesta usando Python. stackoverflow.com/a/35123787/5470883 25 de enero de 2017 a las 9:39
  • 5
    @AlexanderBaltasar, correcto, y parece útil, pero no usa código Python para interactuar con la base de datos. Vea el comentario sobre eso en esa pregunta.
    Ben
    26 de enero de 2017 a las 2:29
  • Re "ninguna otra respuesta utiliza código Python para interactuar con la base de datos". : No, pero la respuesta de Chris Johnson también usa la biblioteca 'csv'. 6 de agosto a las 11:40
  • @PeterMortensen, los comentarios sobre esa respuesta me motivaron a escribir esta respuesta :)
    Ben
    6 de agosto a las 17:36
14

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:

  1. Reemplazar "con ""en cada campo ->replace(field1, '"', '""')
  2. Rodee cada resultado entre comillas -> concat('"', result1, '"')
  3. Coloque una coma entre cada resultado citado -> concat_ws(',', quoted1, quoted2, ...)

¡Eso es todo!

1
  • 3
    Tenga en cuenta que los NULLvalores se omitirán concat_ws(), lo que provocará una discrepancia en las columnas. Para evitar esto, simplemente use una cadena vacía en su lugar: IFNULL(field, '')(o cualquier otra cosa que use para representar NULL) 21 de mayo de 2019 a las 23:11
14

Además, si está realizando la consulta en la línea de comando de Bash, creo que el trcomando se puede usar para sustituir las pestañas predeterminadas por delimitadores arbitrarios.

$ echo "SELECT * FROM Table123" | mysql Database456 | tr "\t" ,
2
  • Esta es una buena manera de hacerlo si tiene problemas con los permisos de archivo, pero no citará sus campos, por lo que otras herramientas pueden malinterpretar el CSV si sus datos incluyen comas o comillas. 8 de abril a las 15:29
  • Requiere la opción -s (silenciosa) para producir una salida con pestañas
    JamesP
    19 jul a las 15:03
12

Encontré el mismo problema y Paul's Answer no era una opción ya que era Amazon RDS . Reemplazar la pestaña con las comas no funcionó porque los datos tenían comas y pestañas incrustadas. Descubrí que mycli , que es una alternativa para el cliente mysql, admite la salida CSV lista para usar con la --csvbandera:

mycli db_name --csv -e "select * from flowers" > flowers.csv
4
  • Funciona como un encanto, se puede instalar en mycli macOS a través de: brew update && brew install mycli. Nunca volveré a usar el cliente stock mysql, ¡mycli es tan legítimo! 24 jul.20 a las 2:40
  • 1
    Mycli y su primo pgcli son fantásticos. Vine aquí para agregar este consejo mysql porque no estaba llegando a ninguna parte con las otras soluciones. La salida limpia a un .csv local es sorprendentemente difícil con mysql. 4 sep.20 a las 19:17
  • Impresionante, es muy triste que esta respuesta tenga muy pocos "me gusta". Es la única solución que se adapta a mis necesidades. Todos los demás no funcionaron perfectamente para mí, excepto esto. Tenía saltos de línea en las columnas, y eso condujo a nuevas líneas en el programa de Excel al abrir el archivo.
    Skiff
    20/11/20 a las 23:57
  • Esta respuesta no funcionará si está atascado con la versión 1.8.1 (tal vez porque está usando un sistema operativo más antiguo que todavía tiene Python 2.x), ya que '--csv' no estaba disponible en esa versión. 14/12/20 a las 9:35
11

Puede tener una tabla MySQL que utilice el motor CSV.

Luego, tendrá un archivo en su disco duro que siempre estará en formato CSV y podrá copiarlo sin procesarlo.

1
  • 3
    ¿Cuáles son los límites de esto en términos de tamaño de archivo / escrituras / lecturas / etc.? 10 oct 2018 a las 8:21
10

Para ampliar las respuestas anteriores, la siguiente línea única 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 aproximadamente.

mysql -B -D mydatabase -e 'select * from mytable'

Convenientemente, podemos usar la misma técnica para enumerar las tablas de MySQL y 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    
2
  • 4
    Para convertir a CSV: mysql -B -D mydatabase -e 'select * from mytable' | sed -e 's/\t/,/g'
    DSimon
    12/01/15 a las 21:15
  • 7
    El uso sed -e 's/\t/,/g'solo es seguro si está seguro de que sus datos no contienen comas ni pestañas.
    awatts
    20/10/15 a las 16:23
8

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' [email protected]

El script de Perl (recortado de otro lugar) hace un buen trabajo al convertir los campos espaciados por tabulaciones a CSV.

2
  • 2
    Esto es genial. Una ligera mejora podría ser citar todo excepto los números perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^\d+(?:\.\d+)?$/ ? $_ : qq("$_")} @F '; el tuyo no cotizaría1.2.3 15 de marzo de 2017 a las 16:00
  • 1
    Esta debería ser la solución aceptada en mi humilde opinión, con la mejora de @ artfulrobot, por supuesto. 25 sep.20 a las 14:55
8

Sobre la base de user7610, esta es la mejor manera de hacerlo. Con mysql outfilehabía 60 minutos de propiedad de los archivos y los problemas de sobreescritura.

No es genial, 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);

?>
1
  • 1
    Agradable, limpio y funciona rápidamente: ¡esta es una gran solución si puede ejecutar PHP en ese entorno! 5/04/18 a las 22:01
5

No exactamente como formato CSV, pero el teecomando del cliente MySQL se puede usar para guardar la salida en un archivo local :

tee foobar.txt
SELECT foo FROM bar;

Puede deshabilitarlo usando notee.

El problema SELECT … INTO OUTFILE …;es que requiere permiso para escribir archivos en el servidor.

2
  • Si se usa la extensión .csv en lugar de .txt, ¿hay algún problema de formato que deba tener en cuenta? 8 de mayo de 2018 a las 21:53
  • @myidealab Los problemas de formato surgen porque las comas, etc., no se escapan. CSV es un formato de texto sin formato, por lo que no hay problemas de formato solo por cambiar la extensión. 20/06/19 a las 17:24
5

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 bastante datos JSON dentro de una de las columnas, que se estropeaban en mi salida CSV. Para aquellos con un problema similar, pruebe con líneas terminadas por \ r \ n.

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 del cual se desborda a las filas de abajo. Para identificar qué registros de una columna tienen el problema, utilice 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;
1
  • Re "... datos JSON bonitos ..." : ¿Bonitos de qué manera? 6 de agosto a las 11:57
3

Utilizando la solución publicada por Tim Harding , creé este script Bash para facilitar el proceso (se solicita la contraseña de root, pero puede modificar el script fácilmente para solicitar 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

1
  • ¿Qué pasa con los caracteres de comillas dobles incrustados? También necesitan escapar, pero no veo un patrón en el encantamiento sed para ellos. 25 sep.20 a las 14:50
3

Si tiene PHP configurado en el servidor, puede usar mysql2csv para exportar un archivo CSV (realmente válido) para una consulta MySQL arbitraria. Vea mi respuesta en MySQL - SELECT * INTO OUTFILE LOCAL? para un poco más de contexto / información.

Intenté mantener los nombres de las opciones de, por mysqllo que debería ser suficiente para proporcionar las opciones --filey --query:

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

"Instalar" a mysql2csvtravé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).

3

Lo siguiente produce una salida CSV válida y delimitada por tabulaciones . A diferencia de la mayoría de las otras respuestas, esta técnica maneja correctamente el escape de pestañas, comas, comillas y nuevas líneas sin ningún filtro de flujo como sed , AWK o tr .

El ejemplo muestra cómo canalizar una tabla MySQL remota directamente a una base de datos SQLite local utilizando flujos. Esto funciona sin el permiso FILE o SELECT INTO OUTFILE. He agregado nuevas líneas para facilitar la lectura.

mysql -B -C --raw -u 'username' --password='password' --host='hostname' 'databasename'
-e 'SELECT
    CONCAT('\''"'\'',REPLACE(`id`,'\''"'\'', '\''""'\''),'\''"'\'') AS '\''id'\'',
    CONCAT('\''"'\'',REPLACE(`value`,'\''"'\'', '\''""'\''),'\''"'\'') AS '\''value'\''
    FROM sampledata'
2>/dev/null | sqlite3 -csv -separator $'\t' mydb.db '.import /dev/stdin mycsvtable'

Se 2>/dev/nullnecesita para suprimir la advertencia sobre la contraseña en la línea de comando.

Si sus datos tienen NULL, puede usar la función IFNULL () en la consulta.

3

En mi caso from table_name ..... antes INTO OUTFILE ..... da un error:

Unexpected ordering of clauses. (near "FROM" at position 10)

Que funciona para mi:

SELECT *
INTO OUTFILE '/Volumes/Development/sql/sql/enabled_contacts.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name
WHERE column_name = 'value'
2

Si recibe un error de secure-file-priventonces, también después de cambiar la ubicación del archivo de destino dentro C:\ProgramData\MySQL\MySQL Server 8.0\Uploadsy también después de la consulta:

SELECT * FROM attendance INTO OUTFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\FileName.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

no funciona, solo tiene que cambiar \(backsplash) de la consulta a /(forwardsplash)

¡¡Y eso funciona !!

Ejemplo:

SELECT * FROM attendance INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/FileName.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

¡Cada vez que ejecute la consulta correcta, generará el nuevo archivo CSV cada vez! ¿Guay, verdad?

1
  • ¿En Windows, presumiblemente? En qué se probó, incl. versiones (de Windows, MySQL, etc.)? 6 de agosto a las 12:06
1

Script Tiny Bash para realizar consultas simples en volcados CSV, inspirado en la respuesta de Tim Harding .

#!/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"
1
  • 1
    Esto usa el encantamiento sed que ignora los caracteres de comillas dobles incrustados. Sugiera usar la solución de Perl en su lugar. 25 sep.20 a las 14:52
1

El siguiente script de Bash me funciona. Opcionalmente, también obtiene el esquema de las tablas solicitadas.

#!/bin/bash
#
# Export MySQL data to CSV
#https://stackoverflow.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 standard error and exit
#
#   Parameters:
#     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 "output CSV directory is not set"
fi
if [ "$database" == "" ]
then
  error "MySQL database is not set"
fi
if [ "$user" == "" ]
then
  error "MySQL user is not set"
fi
if [ "$password" == "" ]
then
  error "MySQL password is 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
1

De pie sobre los hombros de Chris Johnson , extendí la respuesta de febrero de 2016 con un dialecto personalizado para leer.

Esta herramienta de canalización de shell no necesita conectarse a su base de datos, maneja comas y comillas aleatorias en la entrada, ¡y funciona bien en Python 2 y Python 3!

#!/usr/bin/env python
import csv
import sys

# fields are separated by tabs; double-quotes may occur anywhere
csv.register_dialect("mysql", delimiter="\t", quoting=csv.QUOTE_NONE)
tab_in = csv.reader(sys.stdin, dialect="mysql")
comma_out = csv.writer(sys.stdout, dialect=csv.excel)
for row in tab_in:
    # print("row: {}".format(row))
    comma_out.writerow(row)

Use esa declaración impresa para convencerse de que está analizando su entrada correctamente :)

Una advertencia importante: tratamiento de los caracteres de retorno de carro, ^ M también conocido como control-M, \ r en términos de Linux. Aunque la salida de MySQL en modo por lotes escapa correctamente a los caracteres de nueva línea incrustados, por lo que realmente hay una fila por línea (definida por el carácter de nueva línea de Linux \ n), MySQL no pone comillas alrededor de los datos de la columna. Si un elemento de datos tiene un carácter de retorno de carro incrustado, csv.reader rechaza esa entrada con esta excepción:

new-line character seen in unquoted field -
do you need to open the file in universal-newline mode?

Por favor, no @ yo diga que debería usar el modo de archivo universal volviendo a abrir sys.stdin.fileno con el modo 'rU'. Lo intenté y hace que los caracteres \ r incrustados se traten como marcadores de fin de registro, por lo que un solo registro de entrada se transforma incorrectamente en muchos registros de salida incompletos.

No he encontrado una solución de Python para esta limitación del módulo csv.reader de Python. Creo que la causa raíz es la implementación / limitación de csv.reader que se indica en su documentación, csv.reader :

The reader is hard-coded to recognise either '\r' or '\n' as end-of-line,
and ignores lineterminator.

La solución débil e insatisfactoria que puedo ofrecer es cambiar cada carácter \ r a la secuencia de dos caracteres '\ n' antes de que el csv.reader de Python vea los datos. Usé el sedcomando. Aquí hay un ejemplo de una canalización con una selección de MySQL y el script de Python de arriba:

mysql -u user db --execute="select * from table where id=12345" \
  | sed -e 's/\r/\\n/g' \
  | mysqlTsvToCsv.py

Después de luchar contra esto durante algún tiempo, creo que Python no es la solución correcta. Si puede vivir con Perl , creo que el script de una sola línea que ofrece artfulrobot puede ser la solución más eficaz y sencilla.