Как скопировать файл CSV в таблицу PostgreSQL с заголовками в файле CSV?
Я хочу скопировать файл CSV в таблицу Postgres. В этой таблице около 100 столбцов, поэтому я не хочу переписывать их, если мне не нужно.
Я использую \copy table from 'table.csv' delimiter ',' csv;
команда, но без созданной таблицы я получаю ERROR: relation "table" does not exist
, Если я добавляю пустую таблицу, я не получаю ошибки, но ничего не происходит. Я пробовал эту команду два или три раза, и не было ни вывода, ни сообщений, но таблица не обновлялась, когда я проверял ее через PGAdmin.
Есть ли способ импортировать таблицу с включенными заголовками, как я пытаюсь сделать?
6 ответов
Это сработало. В первой строке были имена столбцов.
COPY wheat FROM 'wheat_crop_data.csv' DELIMITER ';' CSV HEADER
С библиотекой Python pandas
, вы можете легко создавать имена столбцов и выводить типы данных из файла CSV.
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine('postgresql://user:pass@localhost/db_name')
df = pd.read_csv('/path/to/csv_file')
df.to_sql('pandas_db', engine)
if_exists
Параметр может быть установлен для замены или добавления к существующей таблице, например df.to_sql('pandas_db', engine, if_exists='replace')
, Это работает и для дополнительных типов входных файлов, документы здесь и здесь.
Альтернатива терминалом без разрешения
Документация pg в NOTES говорит
Путь будет интерпретироваться относительно рабочего каталога серверного процесса (обычно каталога данных кластера), а не рабочего каталога клиента.
Итак, в общем, используя psql
или любой клиент, даже на локальном сервере, у вас есть проблемы... И, если вы выражаете команду COPY для других пользователей, например. на Github README у читателя будут проблемы...
Единственный способ выразить относительный путь с разрешениями клиента является использование STDIN,
Когда указано STDIN или STDOUT, данные передаются через соединение между клиентом и сервером.
как вспомнили здесь:
psql -h remotehost -d remote_mydb -U myuser -c \
"copy mytable (column1, column2) from STDIN with delimiter as ','" \
< ./relative_path/file.csv
Я использовал эту функцию некоторое время без проблем. Вам просто нужно указать числовые столбцы, которые есть в файле csv, и он возьмет имена заголовков из первой строки и создаст таблицу для вас:
create or replace function data.load_csv_file
(
target_table text, -- name of the table that will be created
csv_file_path text,
col_count integer
)
returns void
as $$
declare
iter integer; -- dummy integer to iterate columns with
col text; -- to keep column names in each iteration
col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet
begin
set schema 'data';
create table temp_table ();
-- add just enough number of columns
for iter in 1..col_count
loop
execute format ('alter table temp_table add column col_%s text;', iter);
end loop;
-- copy the data from csv file
execute format ('copy temp_table from %L with delimiter '','' quote ''"'' csv ', csv_file_path);
iter := 1;
col_first := (select col_1
from temp_table
limit 1);
-- update the column names based on the first row which has the column names
for col in execute format ('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)
loop
execute format ('alter table temp_table rename column col_%s to %s', iter, col);
iter := iter + 1;
end loop;
-- delete the columns row // using quote_ident or %I does not work here!?
execute format ('delete from temp_table where %s = %L', col_first, col_first);
-- change the temp table name to the name given as parameter, if not blank
if length (target_table) > 0 then
execute format ('alter table temp_table rename to %I', target_table);
end if;
end;
$$ language plpgsql;
## csv with header
$ psql -U$db_user -h$db_host -p$db_port -d DB_NAME \
-c "\COPY TB_NAME FROM 'data_sample.csv' WITH (FORMAT CSV, header);"
## csv without header
$ psql -U$db_user -h$db_host -p$db_port -d DB_NAME \
-c "\COPY TB_NAME FROM 'data_sample.csv' WITH (FORMAT CSV);"
## csv without header, specify column
$ psql -U$db_user -h$db_host -p$db_port -d DB_NAME \
-c "\COPY TB_NAME(COL1,COL2) FROM 'data_sample.csv' WITH (FORMAT CSV);"
все столбцы в csv должны быть такими же, как таблица (или такими же, как указанный столбец)
Вы можете использовать d6tstack, который создает таблицу для вас и работает быстрее, чем pd.to_sql(), потому что он использует собственные команды импорта БД. Он поддерживает Postgres, а также MYSQL и MS SQL.
import pandas as pd
df = pd.read_csv('table.csv')
uri_psql = 'postgresql+psycopg2://usr:pwd@localhost/db'
d6tstack.utils.pd_to_psql(df, uri_psql, 'table')
Это также полезно для импорта нескольких CSV, решения изменений схемы данных и / или предварительной обработки с помощью панд (например, для дат) перед записью в базу данных, см. Далее в блокноте примеров
d6tstack.combine_csv.CombinerCSV(glob.glob('*.csv'),
apply_after_read=apply_fun).to_psql_combine(uri_psql, 'table')