table - postgresql export in csv




Salvar a saída PL/pgSQL do PostgreSQL para um arquivo CSV (11)

Qual é a maneira mais fácil de salvar a saída do PL / pgSQL de um banco de dados PostgreSQL em um arquivo CSV?

Eu estou usando o PostgreSQL 8.4 com o pgAdmin III e o plugin PSQL onde eu executo as consultas.


Unificação de Exportação CSV

Esta informação não está bem representada. Como esta é a segunda vez que preciso obter isso, vou colocar isso aqui para me lembrar de mais nada.

Realmente, a melhor maneira de fazer isso (obter CSV de postgres) é usar o comando COPY ... TO STDOUT . Embora você não queira fazer da maneira mostrada nas respostas aqui. A maneira correta de usar o comando é:

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

Lembre-se de apenas um comando!

É ótimo para uso em ssh:

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

É ótimo para uso dentro do docker sobre o ssh:

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

É ótimo mesmo na máquina local:

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

Ou dentro de uma janela de encaixe na máquina local ?:

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

Ou em um cluster do kubernetes, no docker, em HTTPS ??:

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

Tão versátil, muita vírgula!

Você mesmo?

Sim eu fiz, aqui estão minhas anotações:

Os COPYses

Usar /copy efetivamente executa operações de arquivo em qualquer sistema em que o comando psql está sendo executado, como o usuário que está executando o mesmo. Se você se conectar a um servidor remoto, é simples copiar arquivos de dados no sistema que executa o psql para / do servidor remoto.

COPY executa operações de arquivo no servidor, pois a conta de usuário do processo de backend ( postgres padrão), caminhos de arquivo e permissões são verificados e aplicados de acordo. Se estiver usando TO STDOUT , as verificações de permissões de arquivo serão ignoradas.

Ambas as opções requerem movimentação de arquivo subsequente se o psql não estiver sendo executado no sistema no qual você deseja que o CSV resultante resida. Este é o caso mais provável, na minha experiência, quando você trabalha principalmente com servidores remotos.

É mais complexo configurar algo como um túnel TCP / IP sobre ssh para um sistema remoto para saída CSV simples, mas para outros formatos de saída (binário) pode ser melhor /copy através de uma conexão em túnel, executando um psql local. Na mesma linha, para importações grandes, mover o arquivo de origem para o servidor e usar COPY é provavelmente a opção de maior desempenho.

Parâmetros PSQL

Com parâmetros psql você pode formatar a saída como CSV, mas há desvantagens como ter que lembrar de desativar o pager e não obter cabeçalhos:

$ 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,,

Outras ferramentas

Não, só quero tirar o CSV do meu servidor sem compilar e / ou instalar uma ferramenta.


Estou trabalhando no AWS Redshift, que não suporta o recurso COPY TO .

Minha ferramenta de BI suporta CSVs delimitados por tabulação, por isso usei o seguinte:

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

Eu tive que usar o \ COPY porque recebi a mensagem de erro:

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

Então eu usei:

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

e está funcionando


Existem várias soluções:

Comando 1 psql

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

Isso tem a grande vantagem de você poder usá-lo via SSH, como o ssh [email protected] command - permitindo que você obtenha

2 comando postgres copy

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

3 psql interativo (ou não)

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

Todos eles podem ser usados ​​em scripts, mas eu prefiro # 1.

4 pgadmin mas isso não é scriptável.


No terminal (enquanto conectado ao banco de dados) defina a saída para o arquivo cvs

1) Defina o separador de campo como ',' :

\f ','

2) Defina o formato de saída desalinhado:

\a

3) Mostrar apenas tuplas:

\t

4) Definir saída:

\o '/tmp/yourOutputFile.csv'

5) Execute sua consulta:

:select * from YOUR_TABLE

6) Saída:

\o

Você então poderá encontrar seu arquivo csv neste local:

cd /tmp

Copie-o usando o comando scp ou edite usando nano:

nano /tmp/yourOutputFile.csv

Nova versão - psql 12 - suportará --csv .

psql - devel

--csv

Alterna para o modo de saída CSV (Comma-Separated Values). Isso é equivalente ao csv do formato \ pset .

csv_fieldsep

Especifica o separador de campo a ser usado no formato de saída do CSV. Se o caractere separador aparecer no valor de um campo, esse campo será enviado entre aspas duplas, seguindo as regras CSV padrão. O padrão é uma vírgula.

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

Se você estiver interessado em todas as colunas de uma determinada tabela junto com cabeçalhos, você pode usar

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

Isso é um pouquinho mais simples que

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

que, tanto quanto sei, são equivalentes.


Se você tem uma consulta mais longa e gosta de usar o psql, coloque sua consulta em um arquivo e use o seguinte comando:

psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv

Você quer o arquivo resultante no servidor ou no cliente?

Lado do servidor

Se você quer algo fácil de reutilizar ou automatizar, você pode usar o comando COPY embutido no Postgresql. por exemplo

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

Essa abordagem é executada inteiramente no servidor remoto - ele não pode gravar em seu PC local. Ele também precisa ser executado como um "superusuário" do Postgres (normalmente chamado de "root") porque o Postgres não pode pará-lo fazendo coisas desagradáveis ​​com o sistema de arquivos local da máquina.

Isso não significa que você precisa estar conectado como um superusuário (automatizar isso seria um risco de segurança de um tipo diferente), porque você pode usar a opção SECURITY DEFINER para CREATE FUNCTION para fazer uma função que roda como se você fosse um superusuário .

A parte crucial é que a sua função está lá para executar verificações adicionais, não apenas passar a segurança - assim você pode escrever uma função que exporte os dados exatos que você precisa, ou você pode escrever algo que aceite várias opções, desde que conheça uma lista de permissões estrita. Você precisa verificar duas coisas:

  1. Quais arquivos o usuário deve ter permissão para ler / gravar em disco? Este pode ser um diretório específico, por exemplo, e o nome do arquivo pode ter um prefixo ou extensão adequados.
  2. Quais tabelas o usuário deve ser capaz de ler / gravar no banco de dados? Isso normalmente seria definido por GRANT s no banco de dados, mas a função agora está sendo executada como um superusuário, portanto, tabelas que normalmente seriam "fora dos limites" estarão totalmente acessíveis. Você provavelmente não quer deixar alguém invocar sua função e adicionar linhas no final da sua tabela de “usuários” ...

Eu escrevi uma postagem no blog expandindo essa abordagem , incluindo alguns exemplos de funções que exportam (ou importam) arquivos e tabelas que atendem a condições estritas.

Lado do cliente

A outra abordagem é fazer o tratamento de arquivos no lado do cliente , ou seja, no seu aplicativo ou script. O servidor do Postgres não precisa saber para qual arquivo você está copiando, apenas mostra os dados e o cliente o coloca em algum lugar.

A sintaxe básica para isso é o comando COPY TO STDOUT , e ferramentas gráficas como o pgAdmin irão envolvê-lo para você em um bom diálogo.

O cliente de linha de comando psql possui um "meta-comando" especial chamado \copy , que usa todas as mesmas opções que o COPY "real", mas é executado dentro do cliente:

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

Note que não há finalização ; , porque os meta-comandos são finalizados pela nova linha, ao contrário dos comandos SQL.

Dos docs :

Não confunda COPY com a instrução \ copy do psql. \ copy invoca COPY FROM STDIN ou COPY TO STDOUT e depois busca / armazena os dados em um arquivo acessível ao cliente psql. Assim, a acessibilidade de arquivos e os direitos de acesso dependem do cliente, e não do servidor, quando \ copy é usado.

Sua linguagem de programação de aplicativos também pode ter suporte para pressionar ou buscar os dados, mas geralmente não é possível usar COPY FROM STDIN / TO STDOUT dentro de uma instrução SQL padrão, porque não há como conectar o fluxo de entrada / saída. O manipulador PostgreSQL do PHP ( não o PDO) inclui pg_copy_from básicas pg_copy_from e pg_copy_to que copiam para / de um array PHP, o que pode não ser eficiente para grandes conjuntos de dados.


O JackDB , um cliente de banco de dados em seu navegador da Web, torna isso realmente fácil. Especialmente se você estiver no Heroku.

Ele permite que você se conecte a bancos de dados remotos e execute consultas SQL neles.

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

Uma vez que seu DB está conectado, você pode executar uma consulta e exportar para CSV ou TXT (veja no canto inferior direito).

Nota: Eu não sou de forma alguma afiliado ao JackDB. Eu atualmente uso seus serviços gratuitos e acho que é um ótimo produto.


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'




postgresql-copy