如何将CSV文件数据导入PostgreSQL表

如何编写从CSV文件导入数据并填充表的存储过程？

1056315 次浏览
小开

看看这个短文。

解决方案在这里解释：

创建您的表：

CREATE TABLE zip_codes
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision,
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);

将数据从CSV文件复制到表中：

COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' WITH (FORMAT csv);
小开

您还可以使用pgAdmin，它提供了一个GUI来进行导入。这在so线程中显示。使用pgAdmin的优点是它也适用于远程数据库。

每个人都有自己的解决方案，但我通常在Excel中打开CSV文件，复制标题，将特殊的换位粘贴到不同的工作表上，将相应的数据类型放在下一列，然后将其复制并粘贴到文本编辑器中，并与适当的SQL表创建查询一起，如下所示：

CREATE TABLE my_table (
/* Paste data from Excel here for example ... */
col_1 bigint,
col_2 bigint,
/* ... */
col_n bigint
)
小开

一种快速方法是使用Python熊猫库（0.15或更高版本最有效）。这将为您处理创建列-尽管显然它为数据类型做出的选择可能不是您想要的。如果它不能完全满足您的需求，您可以始终使用作为模板生成的“创建表”代码。

这里有一个简单的例子：

import pandas as pd
df = pd.read_csv('mypath.csv')
df.columns = [c.lower() for c in df.columns] # PostgreSQL doesn't like capitals or spaces


from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')


df.to_sql("my_table_name", engine)

下面是一些代码，向您展示如何设置各种选项：

# Set it so the raw SQL output is logged
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)


df.to_sql("my_table_name2",
engine,
if_exists="append",  # Options are ‘fail’, ‘replace’, ‘append’, default ‘fail’
index = False, # Do not output the index of the dataframe
dtype = {'col1': sqlalchemy.types.NUMERIC,
'col2': sqlalchemy.types.String}) # Datatypes should be SQLAlchemy types
小开

这里的大多数其他解决方案都需要您提前/手动创建表。这在某些情况下可能不切实际（例如，如果您在目标表中有很多列）。所以，下面的方法可能会派上用场。

提供CSV文件的路径和列计数，您可以使用以下函数将表加载到将命名为target_table的临时表：

假定最上面的行具有列名。

create or replace function data.load_csv_file
(
target_table text,
csv_path text,
col_count integer
)


returns void as $$


declare


iter integer; -- dummy integer to iterate columns with
col text; -- variable to keep the column name at each iteration
col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet


begin
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_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
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;
小开

如果您没有权限使用COPY（在数据库服务器上工作），您可以使用\copy代替（在数据库客户端中工作）。使用相同的示例如Bozhidar Batsov：

创建您的表：

CREATE TABLE zip_codes
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision,
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);

将数据从CSV文件复制到表中：

\copy zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV

注意\复制…必须写在一行中，结尾没有；！

您还可以指定要读取的列：

\copy zip_codes(ZIP,CITY,STATE) FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV

请参阅留档以获取COPY

不要将COPY与psql指令\Copy混淆。\Copy调用COPY from STDIN或COPY TO STDOUT，然后将数据获取/存储在psql客户端可访问的文件中。因此，使用\Copy时，文件可访问性和访问权限取决于客户端而不是服务器。

注意：

对于标识列，COPY OF命令将始终写入输入数据中提供的列值，例如INSERT选项OVERRIDING SYSTEM VALUE。

小开

作为保罗提到，导入在pgAdmin中工作：

右键单击表格→导入

选择本地文件、格式和编码。

这是一个德语pgAdmin GUI截图：

pgAdmin导入GUI

您可以使用DbVisualizer做类似的事情（我有许可证，但不确定免费版本）。

右键单击表格→导入表数据…

DbVisualizer导入GUI

小开

恕我直言，最方便的方法是遵循“将CSV数据导入postgresql，舒适的方式；-）”，使用csvkit中的csvsql，这是一个可通过pip安装的Python包。

小开 
COPY table_name FROM 'path/to/data.csv' DELIMITER ',' CSV HEADER;
小开

使用此SQL代码：

copy table_name(atribute1,attribute2,attribute3...)
from 'E:\test.csv' delimiter ',' csv header

head关键字让DBMS知道CSV文件有一个带有属性的头。

有关更多信息，请访问将CSV文件导入PostgreSQL表。

小开

创建一个表并拥有用于在CSV文件中创建表的所需列。

  1. 打开postgres并右键单击要加载的目标表。在文件选项部分中选择导入并更新以下步骤

  2. 现在浏览您的文件以获取文件名

  3. 在格式中选择CSV

  4. 编码为ISO_8859_5

现在转到杂项选择。选中报头并单击导入。

小开

这是使用PostgreSQL的个人经验，我仍在等待更快的方法。

  1. 如果文件存储在本地，则首先创建一个表骨架：

     drop table if exists ur_table;
CREATE TABLE ur_table
(
id serial NOT NULL,
log_id numeric,
proc_code numeric,
date timestamp,
qty    int,
name varchar,
price money
);
COPY
ur_table(id, log_id, proc_code, date, qty, name, price)
FROM '\path\xxx.csv' DELIMITER ',' CSV HEADER;

  2. 当路径xxx.csv文件在服务器上时，PostgreSQL没有 访问服务器的权限。您必须通过pgAdmin内置功能导入. csv文件。

    右键单击表名称并选择导入。

    在此输入图片描述

如果您仍然有问题，请参考此教程：将CSV文件导入PostgreSQL表

小开

  1. 首先创建一个表

  2. 然后使用复制命令复制表详细信息：

     copy table_name (C1,C2,C3....)
from 'path to your CSV file' delimiter ',' csv header;

注：

  • 列和顺序由SQL中的C1,C2,C3..指定
  • header选项只是从输入中跳过一行，而不是根据列的名称。
小开

如果您需要从文本/解析多行CSV内容导入的简单机制，您可以使用：

CREATE TABLE t   -- OR INSERT INTO tab(col_names)
AS
SELECT
t.f[1] AS col1
,t.f[2]::int AS col2
,t.f[3]::date AS col3
,t.f[4] AS col4
FROM (
SELECT regexp_split_to_array(l, ',') AS f
FROM regexp_split_to_table(
$$a,1,2016-01-01,bbb
c,2,2018-01-01,ddd
e,3,2019-01-01,eee$$, '\n') AS l) t;

DBFiddle Demo

小开

在Python中，您可以使用此代码自动创建带有列名的PostgreSQL表：

import pandas, csv


from io import StringIO
from sqlalchemy import create_engine


def psql_insert_copy(table, conn, keys, data_iter):
dbapi_conn = conn.connection
with dbapi_conn.cursor() as cur:
s_buf = StringIO()
writer = csv.writer(s_buf)
writer.writerows(data_iter)
s_buf.seek(0)
columns = ', '.join('"{}"'.format(k) for k in keys)
if table.schema:
table_name = '{}.{}'.format(table.schema, table.name)
else:
table_name = table.name
sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(table_name, columns)
cur.copy_expert(sql=sql, file=s_buf)


engine = create_engine('postgresql://user:password@localhost:5432/my_db')


df = pandas.read_csv("my.csv")
df.to_sql('my_table', engine, schema='my_schema', method=psql_insert_copy)

它也相对较快。我可以在大约4分钟内导入超过330万行。

小开

如何将CSV文件数据导入PostgreSQL表

步骤：

  1. 需要在终端中连接PostgreSQL数据库

     psql -U postgres -h localhost

  2. 需要创建一个数据库

     create database mydb;

  3. 需要创建一个用户

     create user siva with password 'mypass';

  4. 连接数据库

     \c mydb;

  5. 需要创建一个架构

     create schema trip;

  6. 需要创建一个表

     create table trip.test(VendorID int,passenger_count int,trip_distance decimal,RatecodeID int,store_and_fwd_flag varchar,PULocationID int,DOLocationID int,payment_type decimal,fare_amount decimal,extra decimal,mta_tax decimal,tip_amount decimal,tolls_amount int,improvement_surcharge decimal,total_amount
);

  7. 将csv文件数据导入postgresql

     COPY trip.test(VendorID int,passenger_count int,trip_distance decimal,RatecodeID int,store_and_fwd_flag varchar,PULocationID int,DOLocationID int,payment_type decimal,fare_amount decimal,extra decimal,mta_tax decimal,tip_amount decimal,tolls_amount int,improvement_surcharge decimal,total_amount) FROM '/home/Documents/trip.csv' DELIMITER ',' CSV HEADER;

  8. 查找给定的表数据

     select * from trip.test;
小开

我创建了一个小工具，可以非常轻松地将csv文件导入PostgreSQL。它只是一个命令，它将创建和填充表，但不幸的是，目前，自动创建的所有字段都使用TEXT类型：

csv2pg users.csv -d ";" -H 192.168.99.100 -U postgres -B mydatabase

该工具可以在https://github.com/eduardonunesp/csv2pg上找到

小开

您也可以使用pgFutter，或者更好的是pgcsv。

这些工具根据CSV标头从您创建表列。

pgFutter是相当错误的，我推荐pgcsv。

以下是如何使用pgcsv：

sudo pip install pgcsv
pgcsv --db 'postgresql://localhost/postgres?user=postgres&password=...' my_table my_file.csv
小开

DBeaver Community Edition（dbeaver.io）使连接到数据库，然后导入CSV文件以上传到PostgreSQL数据库变得简单。它还可以轻松地发出查询、检索数据和将结果集下载为CSV、JSON、SQL或其他常见数据格式。

它是SQL程序员，DBA和分析师的FOSS多平台数据库工具，支持所有流行的数据库：MySQL，PostgreSQL，SQLite，Oracle，DB2，SQLServer，赛贝斯，MS Access，Teradata，Firebird，Hive，Presto等。

我和DBeaver没有任何关系。我喜欢它的价格（免费！）和完整的功能，但我希望他们能更多地开放这个DBeaver/Eclipse应用程序，并使向DBeaver/Eclipse添加分析小部件变得容易，而不是要求用户每年支付199美元的订阅费，只是为了直接在应用程序中创建图形和图表。我的Java编码技能生疏了，我不想花几周时间重新学习如何构建Eclipse小部件，（结果发现DBeaver可能已经禁用了向DBeaver社区版添加第三方小部件的能力。）

小开

您可以将Bash文件创建为import.sh（您的CSV格式是制表符分隔符）：

#!/usr/bin/env bash


USER="test"
DB="postgres"
TBALE_NAME="user"
CSV_DIR="$(pwd)/csv"
FILE_NAME="user.txt"


echo $(psql -d $DB -U $USER  -c "\copy $TBALE_NAME from '$CSV_DIR/$FILE_NAME' DELIMITER E'\t' csv" 2>&1 |tee /dev/tty)

然后运行这个脚本。

小开

如果文件不是很大，您可以使用熊猫库。

在Pandas数据框上使用iter时要小心。我在这里这样做是为了演示这种可能性。当从数据框复制到SQL表时，也可以考虑pd.Dataframe.to_sql（）函数。

假设您已经创建了所需的表，您可以：

import psycopg2
import pandas as pd
data=pd.read_csv(r'path\to\file.csv', delimiter=' ')


#prepare your data and keep only relevant columns


data.drop(['col2', 'col4','col5'], axis=1, inplace=True)
data.dropna(inplace=True)
print(data.iloc[:3])




conn=psycopg2.connect("dbname=db user=postgres password=password")
cur=conn.cursor()


for index,row in data.iterrows():
cur.execute('''insert into table (col1,col3,col6)
VALUES (%s,%s,%s)''', (row['col1'], row['col3'], row['col6'])


cur.close()
conn.commit()


conn.close()
print('\n db connection closed.')
小开

通过使用任何客户端-我使用DataGrip-我创建了一个新数据库，然后在数据库的默认模式（公共）中，右键单击数据库，然后执行从文件导入数据。

从该位置选择CSV文件，然后选择导入文件→格式为TSV→确保数据CSV文件的每个列名都与表的列名有关。

导入CSV文件

小开

我的想法是将您的CSV文件转换为SQL查询：

  1. 打开工具将CSV转换为在线插入SQL
  2. 在数据源详细信息窗格中粘贴或上传您的CSV文件
  3. 滚动到表生成器面板
  4. 点击复制到剪贴板或下载地址

示例：

id,name
1,Roberta
2,Oliver

SQL查询的输出：

CREATE TABLE tableName
(
id    varchar(300),
name  varchar(300)
);


INSERT INTO tableName (id,name)
VALUES
('1', 'Roberta'),
('2', 'Oliver');
小开

您有3个选项可以将CSV文件导入PostgreSQL： 首先，通过命令行使用COPY命令。

输入图片描述

其次，使用pgAdmin工具的导入/导出。

输入图片描述

第三，使用Skyvia等云解决方案，从FTP源等在线位置或Google Drive等云存储获取CSV文件。

输入图片描述

您可以从这里中查看解释所有这些的文章。

小开

这些是一些很棒的答案，但对我来说过于复杂。我只需要将CSV文件加载到postgreSQL中，而无需先创建表。

这是我的方式：

图书馆

import pandas as pd
import os
import psycopg2 as pg
from sqlalchemy  import create_engine

使用环境变量获取密码

password = os.environ.get('PSW')

创建我们的引擎

engine = create_engine(f"postgresql+psycopg2://postgres:{password}@localhost:5432/postgres")

发动机要求的细分：

  • 引擎=create_engine（方言+驱动程序：//用户名：password@host：端口/数据库）

打破

  • postgresql+stercopg2=方言+驱动
  • postgres=用户名
  • 密码=环境变量中的密码。如果需要，您可以输入密码，但不推荐
  • localhost=主机
  • 5432=端口
  • postgres=数据库

获取你的CSV文件路径，我不得不使用编码方面。原因可以找到这里

data = pd.read_csv(r"path, encoding= 'unicode_escape')

将数据发送到SQL：

data.to_sql('test', engine, if_exists='replace')

打破

  • test=您希望表成为的表名
  • Engine=上面创建的引擎。AKA我们的连接
  • if_exsists=将替换旧表，如果有的话。

大家一起：

import pandas as pd
import os
import psycopg2 as pg
from sqlalchemy  import create_engine


password = os.environ.get('PSW')


engine = create_engine(f"postgresql+psycopg2://postgres:{password}@localhost:5432/postgres")


data = pd.read_csv(r"path, encoding= 'unicode_escape')
data.to_sql('test', engine, if_exists='replace')

提交答案