Импорт и экспорт данных CSV в PostgreSQL

Как импортировать и экспортировать данные CSV в PostgreSQL

Резервное копирование данных Postgres, перенос их в другое место и импорт при необходимости – все это с помощью CSV.

Файлы CSV упрощают процесс хранения данных. Поскольку формат CSV основан на строках и столбцах, он хорошо переводится в реляционную базу данных.

Легко импортировать CSV-файл в базу данных и экспортировать данные из базы данных в CSV-файл. В PostgreSQL для этого можно использовать несколько различных методов.

Создание файла CSV

id,firstname,lastname,email
1,Riannon,Pulsifer,Riannon.Pulsifer@example.com
2,Moyna,Palocz,Moyna.Palocz@example.com
3,Roslyn,Bearnard,Roslyn.Bearnard@example.com
4,Gloria,Aldric,Gloria.Aldric@example.com
5,Felice,Greenwald,Felice.Greenwald@example.com

Перед импортом файла CSV в базу данных PostgreSQL необходимо создать таблицу, соответствующую формату файла CSV. Используйте следующий код для создания таблицы Postgres.

CREATE TABLE employees(id int NOT NULL,
                       firstname char(20), 
                       lastname char(20),
                       email char(50));

Импорт CSV-файлов в PostgreSQL

Вы можете импортировать файлы CSV в таблицу Postgres на сервере или на клиентской машине.

Импорт CSV-файлов на стороне сервера

Чтобы импортировать CSV-файл на сервер PostgreSQL, используйте команду PostgreSQL COPY и ключевое слово FROM. Это позволит вам скопировать данные из CSV-файла в таблицу Postgres при условии совпадения структуры. Используйте следующий код для копирования примера CSV-файла в таблицу employees:

COPY employees(id,firstname,lastname,email)
FROM '/tmp/sample.csv'
DELIMITER ','
CSV HEADER;

Путь в приведенном выше примере является абсолютным. При желании вы можете использовать путь относительно каталога данных Postgres.

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

Импорт CSV-файлов на стороне клиента

На клиентской машине используйте команду psql \copy для импорта CSV-файла. Выполните следующий код в командной строке psql, чтобы скопировать образец CSV-файла в таблицу employees.

\copy employees FROM '/tmp/sample.csv' DELIMITER ',' CSV HEADER;

Экспорт CSV-файлов из PostgreSQL

Как и в случае с импортом, вы также можете экспортировать CSV-файл на стороне сервера или клиента.

Экспорт CSV-файлов на стороне сервера

Используйте команду COPY и ключевое слово TO для экспорта данных в CSV-файл. Вам необходимо указать исходную таблицу и путь к файлу назначения. На этот раз путь должен быть абсолютным:

COPY employees TO '/tmp/employees.csv' CSV HEADER;

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

COPY (SELECT * FROM employees where firstname='Moyna') TO '/tmp/employees-moyna.csv' CSV HEADER;

Экспорт CSV-файлов на стороне клиента

Чтобы экспортировать данные на стороне клиента, используйте команду \copy и ключевое слово TO в командной строке psql:

\copy employees TO 'path-to-file.csv' CSV header;

Использование адаптера базы данных

Для импорта файла CSV необязательно использовать терминал. Вы можете импортировать данные через объект, используя предпочитаемый вами язык программирования. Например, если вы работаете с Python, вы можете подключиться к серверу PostgreSQL и выполнить команду COPY.

Для этого необходимо подключить Python к Postgres через адаптер базы данных, например psycopg2.

pip install psycopg2

Подключитесь к базе данных, используя следующий код:

import psycopg2;
 
connection = psycopg.connect(
    database="employee_db",
    host="localhost"
    user="postgres",
    password="password", 
)

Затем создайте объект и используйте его для выполнения команды COPY:

cursor = connection.cursor()
 
cursor.execute(
 "COPY employees(id,firstname,lastname,email)
 FROM '/tmp/sample.csv'
 DELIMITER ','
 CSV HEADER;"
)
 
connection.close()

Управление данными PostgreSQL с помощью CSV

Это простой процесс импорта и экспорта данных с использованием файлов PostgreSQL и CSV. Вы можете использовать команды psql на клиенте или сервере или программно использовать адаптер базы данных.

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