如何编写从CSV文件导入数据并填充表的存储过程?
当前回答
这些都是很好的答案,但对我来说太复杂了。我只需要在postgreSQL中加载一个CSV文件,而不需要先创建一个表。
这是我的方法:
库
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")
发动机需求分解:
Engine = create_engine(dialect+驱动程序://用户名:password@host:端口/数据库)
分解
Postgresql +psycopg2 =方言+驱动程序 Postgres =用户名 Password =来自环境变量的密码。如果需要,可以输入密码,但不建议输入 Localhost = host 5432 = port Postgres =数据库
获取您的CSV文件路径,我不得不使用编码方面。原因可以在这里找到
data = pd.read_csv(r"path, encoding= 'unicode_escape')
发送数据到Postgress SQL:
data.to_sql('test', engine, if_exists='replace')
分解
Test =你想要的表名 引擎=上面创建的引擎。也就是我们的联系 if_exists =将替换旧表。请谨慎使用。
在一起:
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')
其他回答
在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多万行。
如果文件不是很大,可以使用Pandas库。
在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.')
您可以创建一个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)
然后运行这个脚本。
您还可以使用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
)
如果你没有权限使用COPY(在db服务器上工作),你可以使用\ COPY(在db客户端上工作)。以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 FROM命令将始终写入输入数据中提供的列值,就像INSERT选项覆盖SYSTEM VALUE一样。
推荐文章
- 将varchar字段的类型更改为整数:"不能自动转换为整数类型"
- PostgreSQL可以索引数组列吗?
- PostgreSQL:角色不允许登录
- 用csv模块从csv文件中读取特定的列?
- 如何查找Postgres / PostgreSQL表及其索引的磁盘大小
- 是使用各有一个模式的多个数据库更好,还是使用一个数据库有多个模式更好?
- 如果字符串包含
- 对于PostgreSQL表来说,多大才算太大?
- 将一列的多个结果行连接为一列,按另一列分组
- 使用pgadmin连接到heroku数据库
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 如何首次配置postgresql ?
- 数据库性能调优有哪些资源?
- 如何在PostgreSQL中自动更新时间戳