generate - Guarde la salida PL/pgSQL de PostgreSQL en un archivo CSV

postgresql generate csv

Question

¿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 el complemento pgAdmin III y PSQL desde donde ejecuto las consultas.

Answer 1

Unificación de Exportaciones 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 genial para usar dentro de docker sobre ssh:

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

Incluso es genial en la máquina local:

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

¿O dentro de la ventana 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 docker, sobre HTTPS ??:

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

Tan versátil, muchas comas!

¿Por lo menos?

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

Las copias

Utilizando /copy efectivamente ejecuta las operaciones de archivo en cualquier sistema en el que se psql comando psql , como 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 ( postgres predeterminados), las rutas de los archivos y los permisos se verifican y aplican en consecuencia. Si se usa TO STDOUT se TO STDOUT comprobaciones de permisos de archivos.

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

Es más complejo configurar algo así 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 /copy sobre una conexión tunelizada, ejecutando un psql local. En una línea similar, para importaciones grandes, mover la fuente al servidor y usar COPY es probablemente la opción de mayor rendimiento.

Parámetros de PSQL

Con los parámetros psql puede formatear la salida como CSV pero hay desventajas como tener que recordar desactivar el buscapersonas y no obtener 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 sacar CSV de mi servidor sin compilar y / o instalar una herramienta.

Answer 2

¿Desea el archivo resultante en el servidor o en el cliente?

Lado del servidor

Si desea algo fácil de reutilizar o automatizar, puede usar el comando COPY incorporado en Postgresql. p.ej

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), ya que puedes usar la opción SECURITY DEFINER para CREATE FUNCTION para hacer una función que funcione como si fueras 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 siempre que Cumplir con una estricta lista blanca. Es necesario comprobar dos cosas:

1. ¿Qué archivos deberían permitir al usuario leer / escribir en el disco? Este puede ser un directorio particular, por ejemplo, y el nombre de 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, GRANT s definirá esto 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 quiera permitir que alguien invoque su función y agregue filas al final de su tabla de "usuarios" ...

He escrito una publicación de blog sobre este enfoque , que incluye 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 envolverán en un bonito diálogo.

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

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

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

De la documentación :

No confunda COPY con la instrucción psql / copy. / copy invoca COPY FROM STDIN o COPY TO STDOUT, y luego recupera / 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.

El lenguaje de programación de su aplicación también puede tener soporte para enviar o recuperar datos, pero generalmente no puede usar COPY FROM STDIN / TO STDOUT dentro de una instrucción SQL estándar, porque no hay manera de conectar la secuencia de entrada / salida. El controlador PostgreSQL de PHP ( no PDO) incluye las funciones pg_copy_from y pg_copy_to muy básicas que se copian en / desde una matriz de PHP, lo que puede no ser eficiente para grandes conjuntos de datos.

Answer 3

En la 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 las tuplas:

/t

4) Ajuste de 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 usando el comando scp o edite usando nano:

nano /tmp/yourOutputFile.csv

Answer 4

En pgAdmin III hay una opción para exportar a un archivo desde la ventana de consulta. En el menú principal es Consulta -> Ejecutar a 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, yo haría lo que IMSoP sugirió y usaría el comando de copia.

Answer 5

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 tabulaciones, así que usé lo siguiente:

 psql -h dblocation -p port -U user -d dbname -F $''/t'' --no-align -c " SELECT * FROM TABLE" > outfile.csv

Answer 6

Hay varias soluciones:

psql 1 psql

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

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

2 postgres comando de copy

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.

Answer 7

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 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 envían a psql, excepto por 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

Answer 8

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

AQUI ES LO QUE FUNCIONA PARA MI

psql -d dbame -U nombre de usuario -c "COPIAR (SELECCIONAR * DE LA TABLA) A ENCENDIDO CON CSV HEADER"> OUTPUT_CSV_FILE.csv

Answer 9

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.

Answer 10

Si tiene una consulta más larga y le gusta usar psql, ponga 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

Answer 11

Tuve que usar el / 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 esta funcionando

Answer 12

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

Te permite conectarte a bases de datos remotas y ejecutar consultas SQL en ellas.

^Source 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).

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

Answer 13

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í.

Answer 14

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