Jaki jest najłatwiejszy sposób zapisania danych wyjściowych PL / pgSQL z bazy danych PostgreSQL w pliku CSV?

Używam PostgreSQL 8.4 z wtyczką pgAdmin III i PSQL, z której uruchamiam zapytania.

Czy chcesz wynikowy plik na serwerze, czy na kliencie?

Po stronie serwera

Jeśli chcesz czegoś łatwego do ponownego użycia lub zautomatyzowania, możesz użyć wbudowanego polecenia Postgresql COPY . na przykład

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

To podejście działa całkowicie na zdalnym serwerze - nie można zapisywać na lokalnym komputerze. Musi także działać jako „superużytkownik” Postgresa (zwykle nazywany „rootem”), ponieważ Postgres nie może przestać robić nieprzyjemnych rzeczy z lokalnym systemem plików tego komputera.

To tak naprawdę nie oznacza, że ​​musisz być podłączony jako superużytkownik (automatyzacja byłaby rodzajem ryzyka bezpieczeństwa innego rodzaju), ponieważ możesz użyć tej SECURITY DEFINERopcji,CREATE FUNCTION aby stworzyć funkcję działającą tak, jakbyś był superużytkownikiem .

Najważniejsze jest to, że twoja funkcja wykonuje dodatkowe kontrole, nie tylko omijając zabezpieczenia - więc możesz napisać funkcję, która eksportuje dokładnie potrzebne dane, lub możesz napisać coś, co zaakceptuje różne opcje, o ile tylko poznać ścisłą białą listę. Musisz sprawdzić dwie rzeczy:

1. Które pliki powinien mieć możliwość odczytu / zapisu na dysku? Może to być na przykład określony katalog, a nazwa pliku może wymagać odpowiedniego prefiksu lub rozszerzenia.
2. Które tabele powinien mieć możliwość odczytu / zapisu w bazie danych? Normalnie byłoby to zdefiniowane przez GRANTs w bazie danych, ale funkcja działa teraz jako superużytkownik, więc tabele, które normalnie byłyby poza zakresem, będą w pełni dostępne. Prawdopodobnie nie chcesz, aby ktoś wywoływał twoją funkcję i dodawał wiersze na końcu tabeli „użytkowników”…

Napisałem post na blogu, w którym omawiam to podejście , w tym kilka przykładów funkcji eksportujących (lub importujących) pliki i tabele spełniające surowe warunki.

Strona klienta

Drugim podejściem jest obsługa plików po stronie klienta , tj. W aplikacji lub skrypcie. Serwer Postgres nie musi wiedzieć, do którego pliku kopiujesz, po prostu wyrzuca dane, a klient gdzieś je umieszcza.

Podstawową składnią tego COPY TO STDOUTpolecenia jest polecenie, a narzędzia graficzne, takie jak pgAdmin, zawiną go w miłym oknie dialogowym.

psqlKlient wiersza polecenia ma specjalny „meta-polecenia” o nazwie \copy, która przyjmuje wszystkie te same opcje jak „prawdziwe” COPY, ale jest prowadzony wewnątrz klienta:

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

Zauważ, że nie ma zakończenia ;, ponieważ meta-polecenia są kończone znakiem nowej linii, w przeciwieństwie do poleceń SQL.

Z dokumentów :

Nie myl KOPIOWANIA z instrukcją \ kopia psql. \ copy wywołuje funkcję COPY FROM STDIN lub COPY TO STDOUT, a następnie pobiera / przechowuje dane w pliku dostępnym dla klienta psql. Tak więc dostępność plików i prawa dostępu zależą od klienta, a nie od serwera, gdy używana jest opcja \ copy.

Język programowania aplikacji może również obsługiwać wypychanie lub pobieranie danych, ale ogólnie nie można używać COPY FROM STDIN/ TO STDOUTw standardowej instrukcji SQL, ponieważ nie ma możliwości podłączenia strumienia wejściowego / wyjściowego. Wózek PostgreSQL PHP ( nie PDO) zawiera bardzo podstawowe pg_copy_fromi pg_copy_tofunkcje, które kopiują do / z tablicy PHP, który nie może być skuteczny w przypadku dużych zbiorów danych.

Istnieje kilka rozwiązań:

1 psqlpolecenie

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

Ma to tę wielką zaletę, że można go używać za pośrednictwem SSH, na przykład ssh postgres@host command- umożliwiając uzyskanie

2 copypolecenia postgres

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

3 psql interaktywne (lub nie)

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

Wszystkie z nich mogą być używane w skryptach, ale wolę numer 1.

4 pgadmin, ale nie jest to możliwe do skryptu.

W terminalu (po podłączeniu do bazy danych) ustaw dane wyjściowe w pliku cvs

1) Ustaw separator pola na ',':

\f ','

2) Ustaw format wyjściowy bez wyrównania:

\a

3) Pokaż tylko krotki:

\t

4) Ustaw moc wyjściową:

\o '/tmp/yourOutputFile.csv'

5) Wykonaj zapytanie:

:select * from YOUR_TABLE

6) Wyjście:

\o

Będziesz wtedy mógł znaleźć swój plik csv w tej lokalizacji:

cd /tmp

Skopiuj go za pomocą scppolecenia lub edytuj za pomocą nano:

nano /tmp/yourOutputFile.csv

Jeśli interesują Cię wszystkie kolumny określonej tabeli wraz z nagłówkami, możesz użyć

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

To jest trochę prostsze niż

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

które, zgodnie z moją najlepszą wiedzą, są równoważne.

Ujednolicenie eksportu CSV

Ta informacja nie jest właściwie dobrze reprezentowana. Ponieważ po raz drugi muszę to wyciągnąć, umieszczę to tutaj, aby przypomnieć sobie, czy nic więcej.

Naprawdę najlepszym sposobem na to (pobranie CSV z postgres) jest użycie COPY ... TO STDOUTpolecenia. Chociaż nie chcesz tego robić w sposób pokazany w odpowiedziach tutaj. Prawidłowy sposób użycia polecenia to:

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

Zapamiętaj tylko jedno polecenie!

Jest świetny do użycia przez ssh:

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

Jest świetny do użycia wewnątrz dokera nad ssh:

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

Jest nawet świetny na lokalnej maszynie:

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

Lub wewnątrz dokera na komputerze lokalnym ?:

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

Lub w klastrze kubernetes, w oknie dokowanym, przez HTTPS?

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

Tak wszechstronny, dużo przecinków!

Czy ty w ogóle?

Tak, zrobiłem, oto moje notatki:

KOPIE

Używanie /copyskutecznie wykonuje operacje na plikach w dowolnym systemie, na którym psqluruchomiona jest komenda, tak jak użytkownik, który ją wykonuje 1 . Jeśli łączysz się ze zdalnym serwerem, łatwo jest skopiować pliki danych w systemie wykonującym się psqldo / ze zdalnego serwera.

COPYwykonuje operacje na plikach na serwerze jako konto użytkownika procesu zaplecza (domyślnie postgres), ścieżki plików i uprawnienia są sprawdzane i odpowiednio stosowane. Jeśli używasz, TO STDOUTwówczas sprawdzanie uprawnień do plików jest pomijane.

Obie te opcje wymagają kolejnego przenoszenia pliku, jeśli psqlnie jest wykonywany w systemie, w którym docelowy CSV ma się ostatecznie znajdować. Z mojego doświadczenia wynika, że ​​jest to najbardziej prawdopodobny przypadek, gdy pracujesz głównie ze zdalnymi serwerami.

Bardziej skomplikowane jest skonfigurowanie czegoś takiego jak tunel TCP / IP przez ssh do zdalnego systemu w celu uzyskania prostego wyjścia CSV, ale w przypadku innych formatów wyjściowych (binarnych) lepiej może być /copyw przypadku połączenia tunelowanego, wykonując lokalnie psql. Podobnie w przypadku dużych importów przenoszenie pliku źródłowego na serwer i używanie COPYjest prawdopodobnie opcją o najwyższej wydajności.

Parametry PSQL

Za pomocą parametrów psql możesz sformatować dane wyjściowe, takie jak CSV, ale są też wady, takie jak konieczność pamiętania o wyłączeniu pagera i braku pobierania nagłówków:

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

Inne narzędzia

Nie, chcę tylko usunąć CSV z mojego serwera bez kompilacji i / lub instalacji narzędzia.

Musiałem użyć \ COPY, ponieważ otrzymałem komunikat o błędzie:

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

Więc użyłem:

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

i to działa

psql mogę to dla ciebie zrobić:

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:~$

Aby man psqluzyskać pomoc na temat opcji tutaj użytych.

Pracuję nad AWS Redshift, który nie obsługuje tej COPY TOfunkcji.

Moje narzędzie BI obsługuje jednak pliki CSV rozdzielane tabulatorami, więc użyłem następujących opcji:

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

Nowa wersja - psql 12 - będzie obsługiwać --csv.

psql - devel

--csv

Przełącza do trybu wyjściowego CSV (wartości rozdzielane przecinkami). Jest to równoważne z formatem \ pset csv .

---

csv_fieldsep

Określa separator pól używany w formacie wyjściowym CSV. Jeśli znak separatora pojawia się w wartości pola, pole to jest wyprowadzane w podwójnych cudzysłowach, zgodnie ze standardowymi regułami CSV. Domyślnie jest to przecinek.

Stosowanie:

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

W pgAdmin III istnieje opcja eksportu do pliku z okna zapytania. W menu głównym jest to Zapytanie -> Wykonaj do pliku lub przycisk, który robi to samo (jest to zielony trójkąt z niebieską dyskietką w przeciwieństwie do zwykłego zielonego trójkąta, który po prostu uruchamia zapytanie). Jeśli nie uruchamiasz zapytania z okna zapytania, zrobiłbym to, co sugerował IMSoP, i użyłem polecenia kopiowania.

Próbowałem kilku rzeczy, ale niewiele z nich dało mi pożądany plik CSV ze szczegółami nagłówka.

Oto, co zadziałało dla mnie.

psql -d dbame -U username \
  -c "COPY ( SELECT * FROM TABLE ) TO STDOUT WITH CSV HEADER " > \
  OUTPUT_CSV_FILE.csv

Napisałem małe narzędzie o nazwie, psql2csvktóre zamyka COPY query TO STDOUTwzorzec, co skutkuje poprawnym CSV. Jego interfejs jest podobny do psql.

psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

Przyjmuje się, że zapytanie jest zawartością STDIN, jeśli jest obecny, lub ostatnim argumentem. Wszystkie inne argumenty są przekazywane do psql, z wyjątkiem tych:

-h, --help           show help, then exit
--encoding=ENCODING  use a different encoding than UTF8 (Excel likes LATIN1)
--no-header          do not output a header

Jeśli masz dłuższe zapytanie i chcesz użyć psql, umieść zapytanie w pliku i użyj następującego polecenia:

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

Aby pobrać plik CSV z nazwami kolumn jako HEADER, użyj tego polecenia:

Copy (Select * From tableName) To '/tmp/fileName.csv' With CSV HEADER;

Gorąco polecam DataGrip , bazę danych IDE firmy JetBrains. Możesz wyeksportować zapytanie SQL do pliku CSV i łatwo skonfigurować tunelowanie ssh. Kiedy dokumentacja odnosi się do „zestawu wyników”, oznaczają one wynik zwracany przez zapytanie SQL w konsoli.

Nie jestem związany z DataGrip, po prostu uwielbiam ten produkt!

JackDB , klient bazy danych w przeglądarce internetowej, sprawia, że ​​jest to naprawdę łatwe. Zwłaszcza jeśli jesteś na Heroku.

Pozwala łączyć się ze zdalnymi bazami danych i uruchamiać na nich zapytania SQL.

                                                                                                                                                       ^Źródło _{(źródło: jackdb.com )}

Po podłączeniu bazy danych możesz uruchomić zapytanie i wyeksportować do pliku CSV lub TXT (patrz dolny prawy róg).

^{Uwaga: Nie jestem w żaden sposób związany z JackDB. Obecnie korzystam z ich bezpłatnych usług i uważam, że to świetny produkt.}

Na prośbę @ skeller88 ponownie zamieszczam mój komentarz jako odpowiedź, aby nie gubił się u osób, które nie czytają każdej odpowiedzi ...

Problem z DataGrip polega na tym, że chwyta on twój portfel. To nie jest za darmo. Wypróbuj wersję społecznościową DBeaver ze strony dbeaver.io. To wieloplatformowe narzędzie FOSS dla programistów SQL, DBA i analityków, które obsługuje wszystkie popularne bazy danych: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Hive, Presto itp.

DBeaver Community Edition sprawia, że ​​łączenie się z bazą danych jest proste, zadawanie zapytań w celu pobrania danych, a następnie pobieranie zestawu wyników w celu zapisania go w CSV, JSON, SQL lub innych popularnych formatach danych. Jest realnym konkurentem FOSS dla TOAD dla Postgres, TOAD dla SQL Server lub Toad dla Oracle.

Nie mam powiązań z DBeaver. Uwielbiam cenę i funkcjonalność, ale chciałbym, żeby bardziej otworzyły aplikację DBeaver / Eclipse i ułatwiły dodawanie widżetów analitycznych do DBeaver / Eclipse, zamiast wymagać od użytkowników płacenia za roczną subskrypcję do tworzenia wykresów i wykresów bezpośrednio w obrębie Aplikacja. Moje umiejętności kodowania Java są zardzewiałe i nie mam ochoty poświęcić tygodni na ponowne nauczenie się, jak budować widżety Eclipse, ale okazało się, że DBeaver wyłączył możliwość dodawania widgetów innych firm do DBeaver Community Edition.

Czy użytkownicy DBeaver mają wgląd w etapy tworzenia widgetów analitycznych, które można dodać do Community Edition DBeaver?

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'