17 Pregunta: Guarde la salida PL / pgSQL de PostgreSQL en un archivo CSV

pregunta creada en Sun, Apr 9, 2017 12:00 AM

¿Cuál es la forma más fácil de guardar la salida PL /pgSQL de una base de datos PostgreSQL en un archivo CSV?

Estoy usando PostgreSQL 8.4 con pgAdmin III y PSQL desde donde ejecuto las consultas.

    
802
  1. 2015-03-29 10: 34: 41Z
17 Respuestas                              17                         

¿Desea que el archivo resultante esté en el servidor o en el cliente?

lado del servidor

Si desea algo fácil de reutilizar o automatizar, puede usar el postgresql integrado en COPY comando. por ejemplo

 
Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';

Este enfoque se ejecuta completamente en el servidor remoto , no puede escribir en su PC local. También debe ejecutarse como un "superusuario" de Postgres (normalmente llamado "root") porque Postgres no puede evitar que haga cosas desagradables con el sistema de archivos local de esa máquina.

Eso no significa que tengas que estar conectado como superusuario (automatizar eso sería un riesgo de seguridad de otro tipo), porque puedes usar la opción SECURITY DEFINER a CREATE FUNCTION para crear una función que se ejecute como si fuera un superusuario .

La parte crucial es que su función está ahí para realizar verificaciones adicionales, no solo eludir la seguridad, por lo que podría escribir una función que exporte los datos exactos que necesita, o podría escribir algo que pueda aceptar varias opciones como siempre y cuando cumplan con una estricta lista blanca. Necesitas verificar dos cosas:

  1. ¿Qué archivos debería permitir al usuario leer /escribir en el disco? Esto podría ser un directorio particular, por ejemplo, y el nombre del archivo debe tener un prefijo o extensión adecuados.
  2. ¿Qué tablas debería el usuario poder leer /escribir en la base de datos? Normalmente, esto sería definido por GRANT s en la base de datos, pero la función ahora se ejecuta como un superusuario, por lo que las tablas que normalmente estarían "fuera de los límites" serán completamente accesibles. Probablemente no quieras permitir que alguien invoque tu función y agregue filas al final de tu tabla de "usuarios" ...

He escrito una publicación del blog que amplía este enfoque , incluidos algunos ejemplos de Funciones que exportan (o importan) archivos y tablas que cumplen condiciones estrictas.


lado del cliente

El otro enfoque es hacer el manejo del archivo en el lado del cliente , es decir, en su aplicación o script. El servidor de Postgres no necesita saber en qué archivo está copiando, simplemente escupe los datos y el cliente los coloca en algún lugar.

La sintaxis subyacente para esto es el comando COPY TO STDOUT, y las herramientas gráficas como pgAdmin lo incluirán en un bonito diálogo.

El psql cliente de línea de comandos tiene un "meta-comando" especial llamado \copy , que toma todas las mismas opciones que el "real" COPY, pero se ejecuta Dentro del cliente:

 
\copy (Select * From foo) To '/tmp/test.csv' With CSV

Tenga en cuenta que no hay una terminación ;, porque los meta-comandos son terminados por una nueva línea, a diferencia de los comandos SQL.

De the docs :

  

No confunda COPY con la instrucción psql \copy. \copy invoca COPY FROM STDIN o COPY TO STDOUT, y luego busca /almacena los datos en un archivo accesible para el cliente psql. Por lo tanto, la accesibilidad de los archivos y los derechos de acceso dependen del cliente en lugar del servidor cuando se usa \copy.

Su lenguaje de programación de aplicaciones puede también tiene soporte para enviar o recuperar datos, pero generalmente no puede usar COPY FROM STDIN/TO STDOUT dentro de una declaración SQL estándar, porque no hay manera de conectar la entrada /salida corriente. El controlador de PostgreSQL de PHP ( no DOP) incluye pg_copy_from y pg_copy_to functicomplementos que se copian en /desde una matriz de PHP, que puede no ser eficiente para grandes conjuntos de datos.

    
1209
2016-05-14 12: 49: 26Z
  1. Obviamente, el ejemplo anterior requiere que el usuario sea un superusuario, aquí hay una versión para personas comunes;) echo “COPY (SELECT * from foo) TO STDOUT with CSV HEADER” | psql -o '/tmp/test.csv' nombre_base_datos
    2012-04-17 17: 26: 47Z
  2. @ Drachenfels: \copy también funciona; allí, las rutas son relativas al cliente, y no se necesita /se permite un punto y coma. Ver mi edición.
    2013-02-13 10: 12: 11Z
  3. @ IMSoP: ¿Cómo agregaría una instrucción COPY a una función sql (en postgres 9.3)? ¿Entonces la consulta se guarda en un archivo .csv?
    2013-11-12 21: 24: 21Z
  4. Parece que \copy necesita ser de una sola línea. Por lo tanto, no obtiene la belleza de formatear el sql de la manera que desea, y solo tiene que poner una copia /función a su alrededor.
    2014-01-17 13: 49: 50Z
  5. @ AndreSilva Como indica la respuesta, \copy es un meta-comando especial en el cliente de la línea de comandos psql . No funcionará en otros clientes, como pgAdmin; probablemente tendrán sus propias herramientas, como asistentes gráficos, para realizar este trabajo.
    2018-05-02 17: 49: 54Z

Hay varias soluciones:

1 psql comando

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

Esto tiene la gran ventaja de que puedes usarlo a través de SSH, como ssh postgres@host command, lo que te permite obtener

2 postgres copy comando

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

3 psql interactivo (o no)

 
>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q

Todos ellos pueden usarse en scripts, pero prefiero el # 1.

4 pgadmin pero eso no es programable.

    
462
2015-05-19 04: 39: 52Z
  1. IMHO, la primera opción es propensa a errores, porque no incluye el escape adecuado de la coma en los datos exportados.
    2013-05-06 21: 07: 52Z
  2. @ Piohen por lo que recuerdo, ya que citará cadenas, pero no estoy 100% seguro, es mejor probarlo.
    2013-05-07 09: 09: 24Z
  3. Además, psql no cita los valores de las celdas, por lo que si ANY de sus datos usa el delimitador, su archivo se corromperá.
    2014-04-08 21: 39: 09Z
  4. @ Cerin -t es un sinónimo de --tuples-only (desactivar la impresión de los nombres de columna y los pies de página de la fila de resultados, etc.) - omítalo para obtener la columna encabezados
    2014-06-05 21: 40: 20Z
  5. Recientemente probé la afirmación de escape de coma: es cierto, el método # 1 no > elimina las comas en los valores.
    2014-09-17 21: 07: 46Z

En el terminal (mientras está conectado a la base de datos) establezca la salida al archivo cvs

1) Establezca el separador de campo en ',':

 
\f ','

2) Establecer formato de salida sin alinear:

 
\a

3) Mostrar solo tuplas:

 
\t

4) Establecer salida:

 
\o '/tmp/yourOutputFile.csv'

5) Ejecute su consulta:

 
:select * from YOUR_TABLE

6) Salida:

 
\o

Entonces podrá encontrar su archivo csv en esta ubicación:

 
cd /tmp

Cópielo con el comando scp o edítelo usando nano:

 
nano /tmp/yourOutputFile.csv
    
85
2017-03-16 12: 49: 50Z
  1. y \o para imprimir la consola de nuevo
    2012-08-06 14: 57: 01Z
  2. Esto no producirá un archivo CSV, solo registrará la salida del comando en el archivo de texto (que no hace que esté separado por comas).
    2012-11-29 16: 39: 52Z
  3. @ RuslanKabalin sí, lo noté y enmendé las instrucciones para crear una salida separada por comas (cvs)
    2012-11-30 11: 01: 16Z
  4. Mejoré esta respuesta al señalar que la salida "csv" no se eliminará correctamente y cada vez que se ejecuta un comando sql, los resultados se concatenan al archivo de salida .
    2014-02-06 23: 50: 02Z
  5. ¿Qué pasa con las nuevas líneas en los valores de campo? Los enfoques COPY o \copy se manejan correctamente (se convierten al formato estándar CSV); hace esto?
    2017-01-07 04: 19: 35Z

Si está interesado en todas las columnas de una tabla en particular junto con los encabezados, puede usar

 
COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

Esto es un poquito más simple que

 
COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

que, a mi entender, son equivalentes.

    
34
2013-01-11 20: 34: 59Z
  1. Si la consulta es personalizada (IE tiene alias de columna o une diferentes tablas), el encabezado imprimirá los alias de columna tal como se muestran en la pantalla.
    2013-11-13 21: 58: 29Z

Unificación de exportación CSV

Esta información no está muy bien representada. Como esta es la segunda vez que necesito derivar esto, lo pondré aquí para recordarme si no hay nada más.

Realmente la mejor manera de hacer esto (obtener CSV de postgres) es usar el comando COPY ... TO STDOUT. Aunque no quieras hacerlo como se muestra en las respuestas aquí. La forma correcta de usar el comando es:

 
COPY (select id, name from groups) TO STDOUT WITH CSV HEADER

¡Recuerda solo un comando!

Es genial para usar sobre ssh:

 
$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv

Es ideal para usar dentro de la ventana acoplable sobre ssh:

 
$ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

Es incluso genial en la máquina local:

 
$ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

¿O dentro de la ventana acoplable de la máquina local ?:

 
docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

O en un clúster de kubernetes, en la ventana acoplable, a través de HTTPS ??:

 
kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

¡Muy versátil, muchas comas!

¿Incluso?

Sí, lo hice, aquí están mis notas:

Las copias COPY

El uso de /copy ejecuta efectivamente las operaciones de archivo en cualquier sistema en el que se ejecute el comando psql, ya que el usuario que lo está ejecutando 1 . Si se conecta a un servidor remoto, es sencillo copiar archivos de datos en el sistema ejecutando psql a /desde el servidor remoto.

COPY ejecuta las operaciones de archivos en el servidor como la cuenta de usuario del proceso back-end (valor predeterminado postgres), las rutas de los archivos y los permisos se verifican y se aplican en consecuencia. Si se utiliza TO STDOUT, se omiten las comprobaciones de permisos de archivos.

Ambas opciones requieren un movimiento de archivo posterior si psql no se está ejecutando en el sistema en el que desea que resulte finalmente el CSV resultante. Este es el caso más probable, según mi experiencia, cuando trabaja principalmente con servidores remotos.

Es más complejo configurar algo como un túnel TCP /IP a través de ssh a un sistema remoto para una salida CSV simple, pero para otros formatos de salida (binarios) puede ser mejor que /copy a través de una conexión de túnel, ejecutando un psql local . De manera similar, para grandes importaciones, mover la fuente al servidor y usar COPY es probablemente la opción de mayor rendimiento.

Parámetros PSQL

Con los parámetros psql puede formatear la salida como CSV, pero hay desventajas como tener que recordar desactivar el buscapersonas y no obtenerg encabezados:

 
$ psql -P pager=off -d mydb -t -A -F',' -c 'select * from groups;'
2,Technician,Test 2,,,t,,0,,                                                                                                                                                                   
3,Truck,1,2017-10-02,,t,,0,,                                                                                                                                                                   
4,Truck,2,2017-10-02,,t,,0,,

Otras herramientas

No, solo quiero eliminar CSV de mi servidor sin compilar y /o instalar una herramienta.

    
23
2018-10-30 20: 29: 37Z
  1. ¿Dónde se guardan los resultados? Mi consulta se ejecuta pero el archivo no aparece en ninguna parte de mi computadora. Esto es lo que estoy haciendo: COPIAR (seleccione a, b de c donde d = '1') A STDOUT WITH CSVHEADER > abcd.csv
    2018-04-25 17: 00: 31Z
  2. @ kRazzyR La salida va a la salida estándar del comando psql, así que en última instancia, cualquier cosa que haga con stdout es donde van los datos. En mis ejemplos utilizo '> file.csv 'para redirigir a un archivo. Desea asegurarse de que está fuera del comando que se envía al servidor a través del parámetro psql -c. Vea el ejemplo de 'máquina local'.
    2018-04-26 02: 02: 59Z

Tuve que usar \COPY porque recibí el mensaje de error:

 
ERROR:  could not open file "/filepath/places.csv" for writing: Permission denied

Así que utilicé:

 
\Copy (Select address, zip  From manjadata) To '/filepath/places.csv' With CSV;

y está funcionando

    
22
2015-05-26 13: 19: 12Z

psql puede hacer esto por ti:

 
edd@ron:~$ psql -d beancounter -t -A -F"," \
                -c "select date, symbol, day_close " \
                   "from stockprices where symbol like 'I%' " \
                   "and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
edd@ron:~$

Consulte man psql para obtener ayuda sobre las opciones utilizadas aquí.

    
16
2009-10-04 23: 12: 13Z
  1. Este no es un verdadero archivo CSV: mire cómo se quema si hay comas en los datos, por lo que es preferible utilizar el soporte COPY integrado. Pero esta técnica general es útil como un truco rápido para exportar desde Postgres en otros formatos delimitados además de CSV.
    2009-10-06 05: 19: 40Z

Estoy trabajando en AWS Redshift, que no es compatible con la función COPY TO.

Sin embargo, mi herramienta de BI admite CSV delimitados por tabuladores, así que usé lo siguiente:

 
 psql -h dblocation -p port -U user -d dbname -F $'\t' --no-align -c "SELECT * FROM TABLE" > outfile.csv
    
12
2019-03-17 22: 32: 31Z

En pgAdmin III hay una opción para exportar a un archivo desde la ventana de consulta. En el menú principal es Consulta - > Ejecute en archivo o hay un botón que hace lo mismo (es un triángulo verde con un disquete azul en lugar del triángulo verde liso que simplemente ejecuta la consulta). Si no está ejecutando la consulta desde la ventana de consulta, haría lo que IMSoP sugirió y usaría el comando de copia.

    
11
2009-11-04 16: 58: 59Z
  1. La respuesta de IMSoP no me funcionó porque necesitaba ser un superadministrador. Esto funcionó de maravilla. ¡Gracias!
    2012-01-31 22: 08: 45Z

Nueva versión - psql 12 - soportará --csv.

  

psql - devel

     

--csv

     

Cambia al modo de salida CSV (valores separados por comas). Esto es equivalente a \pset format csv .

     

csv_fieldsep

     

Especifica el separador de campo que se utilizará en el formato de salida CSV. Si el carácter separador aparece en el valor de un campo, ese campo se muestra entre comillas dobles,siguiendo las reglas estándar de CSV. El valor predeterminado es una coma.

Uso:

 
psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv -P csv_fieldsep='^'  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres > output.csv
    
9
2019-03-17 22: 36: 58Z

Probé varias cosas, pero pocas de ellas me dieron el CSV deseado con los detalles del encabezado.

Esto es lo que funcionó para mí.

 
psql -d dbame -U username \
  -c "COPY ( SELECT * FROM TABLE ) TO STDOUT WITH CSV HEADER " > \
  OUTPUT_CSV_FILE.csv
    
7
2018-11-12 00: 40: 22Z

He escrito una pequeña herramienta llamada psql2csv que encapsula el patrón COPY query TO STDOUT, lo que resulta en un CSV adecuado . Su interfaz es similar a la psql.

 
psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

Se supone que la consulta es el contenido de STDIN, si está presente, o el último argumento. Todos los demás argumentos se reenvían a psql, excepto estos:

 
-h, --help           show help, then exit
--encoding=ENCODING  use a different encoding than UTF8 (Excel likes LATIN1)
--no-header          do not output a header
    
7
2019-03-17 22: 30: 27Z
  1. Funciona muy bien. Gracias.
    2017-11-03 06: 52: 31Z

Si tiene una consulta más larga y le gusta usar psql, coloque su consulta en un archivo y use el siguiente comando:

 
psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv
    
5
2014-09-18 19: 52: 49Z
  1. FWIW, tuve que usar -F"," en lugar de -F";" para generar un archivo CSV que se abriera correctamente en MS Excel
    2018-05-31 19: 44: 26Z

Para descargar un archivo CSV con nombres de columna como HEADER use este comando:

 
Copy (Select * From tableName) To '/tmp/fileName.csv' With CSV HEADER;
    
3
2018-11-30 08: 25: 24Z

JackDB , un cliente de base de datos en su navegador web, lo hace realmente fácil. Especialmente si estás en Heroku.

Le permite conectarse a bases de datos remotas y ejecutar consultas SQL en ellas.

Fuente jackdb-heroku http://static.jackdb.com/assets/img/blog/jackdb-heroku-oauth-connect. gif


Una vez que su base de datos está conectada, puede ejecutar una consulta y exportar a CSV o TXT (ver abajo a la derecha).


jackdb-export

Nota: no estoy afiliado de ninguna manera con JackDB. Actualmente utilizo sus servicios gratuitos y creo que es un gran producto.

    
1
2017-05-23 12: 18: 25Z

Recomiendo altamente DataGrip , una base de datos IDE de JetBrains. Puede exportar una consulta SQL a un archivo CSV , y puede configurar ssh tunneling con facilidad. Cuando la documentación se refiere al "conjunto de resultados", significa el resultado devuelto por una consulta SQL en la consola.

No estoy asociado conDataGrip, me encanta el producto!

    
- 1
2019-04-08 21: 28: 45Z
  1. Supongo que el voto negativo se debió a la falta de contexto /explicación, por lo que me he vinculado a la documentación de DataGrip. Si hay una razón diferente para el voto negativo, por favor hágamelo saber. He utilizado las soluciones CLI anteriores y DataGrip es mucho más fácil para consultas más pequeñas.
    2019-04-08 21: 30: 01Z
 
import json
cursor = conn.cursor()
qry = """ SELECT details FROM test_csvfile """ 
cursor.execute(qry)
rows = cursor.fetchall()

value = json.dumps(rows)

with open("/home/asha/Desktop/Income_output.json","w+") as f:
    f.write(value)
print 'Saved to File Successfully'
    
- 3
2018-02-27 10: 56: 14Z
  1. Por favor, explique lo que hizo editando la respuesta, evite responder solo el código
    2018-02-27 12: 09: 04Z
  2. Gracias por este fragmento de código, que podría proporcionar alguna ayuda limitada a corto plazo. Una explicación adecuada mejoraría enormemente su valor a largo plazo al mostrar por qué esta es una buena solución para el problema, y ​​lo haría más útil para futuros lectores con otras preguntas similares. edita tu respuesta para agregar alguna explicación, incluidas las suposiciones que hayas hecho.
    2018-02-27 12: 48: 22Z
  3. Esto producirá un archivo json, no un archivo csv.
    2018-02-27 13: 23: 43Z
fuente colocada aquí