файла Сохранить вывод PL/pgSQL из PostgreSQL в файл CSV




psql copy (12)

Каков самый простой способ сохранить вывод PL / pgSQL из базы данных PostgreSQL в файл CSV?

Я использую PostgreSQL 8.4 с плагинами pgAdmin III и PSQL, из которых я запускаю запросы.


Вам нужен результирующий файл на сервере или на клиенте?

Серверная сторона

Если вы хотите что-то простое для повторного использования или автоматизации, вы можете использовать команду Postgresql, созданную в COPY . например

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

Этот подход полностью работает на удаленном сервере - он не может писать на ваш локальный ПК. Он также должен запускаться как «суперпользователь Postgres» (обычно называемый «root»), потому что Postgres не может остановить его, делая неприятные вещи с локальной файловой системой этой машины.

Это на самом деле не означает, что вы должны быть подключены как суперпользователь (автоматизация, которая будет представлять угрозу безопасности другого типа), потому что вы можете использовать опцию SECURITY DEFINER для CREATE FUNCTION чтобы создать функцию, которая работает так, как если бы вы были суперпользователь .

Важнейшая часть заключается в том, что ваша функция состоит в том, чтобы выполнять дополнительные проверки, а не просто обходить безопасность - чтобы вы могли написать функцию, которая экспортирует точные данные, которые вам нужны, или вы могли бы написать что-то, что может принимать различные варианты, если они встретить строгий белый список. Вам нужно проверить две вещи:

  1. Какие файлы следует разрешить пользователю читать или записывать на диск? Например, это может быть конкретный каталог, и имя файла должно иметь подходящий префикс или расширение.
  2. Какие таблицы должны иметь пользовательский интерфейс для чтения / записи в базе данных? Обычно это определяется GRANT s в базе данных, но теперь функция работает как суперпользователь, поэтому таблицы, которые обычно будут «за пределами границ», будут полностью доступны. Вероятно, вы не хотите, чтобы кто-то вызывал вашу функцию и добавлял строки в конец вашей таблицы «users» ...

Я написал сообщение в блоге, расширяющее этот подход , включая некоторые примеры функций, которые экспортируют (или импортируют) файлы и таблицы, отвечающие строгим условиям.

Сторона клиента

Другой подход заключается в том , чтобы обрабатывать файлы на стороне клиента , то есть в вашем приложении или скрипте. Сервер Postgres не должен знать, к какому файлу вы копируете, он просто выплевывает данные, и клиент помещает его где-то.

Основным синтаксисом для этого является команда COPY TO STDOUT , а графические инструменты, такие как pgAdmin, будут переносить их для вас в приятном диалоговом окне.

Клиент командной строки psql имеет специальную «мета-команду» под названием \copy , которая принимает все те же опции, что и «настоящая» COPY , но выполняется внутри клиента:

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

Обратите внимание, что завершение отсутствует ; , потому что мета-команды завершаются новой строкой, в отличие от команд SQL.

Из документов :

Не путайте COPY с инструкцией psql \ copy. \ copy вызывает COPY FROM STDIN или COPY TO STDOUT, а затем извлекает / сохраняет данные в файле, доступном для клиента psql. Таким образом, доступ к файлам и права доступа зависят от клиента, а не от сервера, когда используется \ copy.

Ваш язык программирования приложений может также поддерживать тонирование или выборку данных, но вы не можете использовать COPY FROM STDIN / TO STDOUT в стандартном SQL-заявлении, потому что нет способа подключения потока ввода-вывода. Обработчик PHP PostgreSQL (а не PDO) включает в себя очень простые функции pg_copy_from и pg_copy_to которые копируются в / из массива PHP, что может быть неэффективным для больших наборов данных.


Если вас интересуют все столбцы конкретной таблицы вместе с заголовками, вы можете использовать

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

Это немного проще, чем

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

которые, насколько мне известно, эквивалентны.


Я написал небольшой инструмент под названием psql2csv который инкапсулирует COPY query TO STDOUT , что приводит к правильному CSV. Интерфейс подобен psql .

psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

Предполагается, что запрос представляет собой содержимое STDIN, если оно присутствует, или последний аргумент. Все остальные аргументы передаются в psql, за исключением следующих:

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

Я попробовал несколько вещей, но меньше их было в состоянии дать мне желаемый csv с деталями заголовка.

ЗДЕСЬ, ЧТО РАБОТАЕТ ДЛЯ МЕНЯ.

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


В pgAdmin III есть опция экспорта в файл из окна запроса. В главном меню Query -> Execute to file или есть кнопка, которая делает то же самое (это зеленый треугольник с голубой дискеткой, а не простой зеленый треугольник, который просто запускает запрос). Если вы не выполняете запрос из окна запроса, я бы сделал то, что предложил IMSoP, и использовал команду копирования.


Существует несколько решений:

Команда 1 psql

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

Это имеет большое преимущество, что вы можете использовать его через SSH, например, ssh [email protected] command - позволяя вам получить

2 команда copy postgres

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

3 psql интерактивный (или нет)

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

Все они могут использоваться в скриптах, но я предпочитаю # 1.

4 pgadmin, но это невозможно для сценариев.


Если у вас есть более длинный запрос, и вы хотите использовать psql, тогда поместите свой запрос в файл и используйте следующую команду:

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

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'

В терминале (при подключении к db) установите вывод в файл cvs

1) Установите разделитель поля на ',' :

\f ','

2) Установите выходной формат неравнозначным:

\a

3) Показывать только кортежи:

\t

4) Установите выход:

\o '/tmp/yourOutputFile.csv'

5) Выполните свой запрос:

:select * from YOUR_TABLE

6) Выход:

\o

Затем вы сможете найти свой CSV-файл в этом месте:

cd /tmp

Скопируйте его с помощью команды scp или отредактируйте с помощью nano:

nano /tmp/yourOutputFile.csv

Объединение экспорта CSV

Эта информация не очень хорошо представлена. Поскольку это второй раз, когда мне нужно было это сделать, я поставил это здесь, чтобы напомнить себе, если ничего другого.

На самом деле лучший способ сделать это (получить CSV из postgres) - использовать команду COPY ... TO STDOUT . Хотя вы не хотите делать это, как показано в ответах здесь. Правильный способ использования команды:

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

Помните только одну команду!

Это отлично подходит для использования через ssh:

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

Это отлично подходит для использования внутри докеров над ssh:

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

Это даже отлично на локальной машине:

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

Или внутри докера на локальной машине ?:

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

Или на кластере кубернетов, в докере, над HTTPS ??:

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

Настолько универсальный, много запятых!

Ты когда-нибудь?

Да, вот мои заметки:

Копирование

Использование /copy эффективно выполняет операции с файлами в любой системе, на которой запущена команда psql , как пользователь, выполняющий ее 1 . Если вы подключаетесь к удаленному серверу, просто скопировать файлы данных в систему, выполняющую psql на / с удаленного сервера.

COPY выполняет операции с файлами на сервере, поскольку учетная запись пользовательского интерфейса (по умолчанию postgres ), пути к файлам и разрешения проверяются и применяются соответственно. Если вы используете TO STDOUT проверки файлов разрешены.

Обе эти опции требуют последующего перемещения файлов, если psql не выполняется в системе, где вы хотите, чтобы итоговый CSV находился в конечном итоге. Это самый вероятный случай, по моему опыту, когда вы в основном работаете с удаленными серверами.

Сложнее настроить что-то вроде туннеля TCP / IP через ssh для удаленной системы для простого вывода CSV, но для других выходных форматов (двоичных) может быть лучше /copy по туннелированному соединению, выполняя локальный psql . В аналогичном ключе, для крупных импорта, перемещение исходного файла на сервер и использование COPY , вероятно, является самым высокопроизводительным вариантом.

Параметры PSQL

С параметрами psql вы можете форматировать вывод, например CSV, но есть недостатки, такие как необходимость запоминать пейджер и не получать заголовки:

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

Другие инструменты

Нет, я просто хочу получить CSV из моего сервера без компиляции и / или установки инструмента.


Мне пришлось использовать \ COPY, потому что я получил сообщение об ошибке:

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

Поэтому я использовал:

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

и он функционирует


psql может сделать это за вас:

[email protected]:~$ 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
[email protected]:~$

См. man psql для справки о параметрах, используемых здесь.





postgresql-copy