在做算法开发的时候,通常都会遇到读取或者写入PG数据库的情况。
一般对于读取PG数据库来说,尽可能将复杂的SQL命令拆分成短小的多条SQL命令并在Python中进行merge会大大的降低脚本运行时间。
同样,对于写入PG数据库来说,不同写入方式也会存在明显的效率差异。这里,我以自己在写入数据库时的三种方式(拼接成insert长字符串,pandas自带的tosql,及psycopg2的copy_from命令)做了一个对比,数据源为1个含有13642条数据的csv
1.insert拼接字符串写入
写入时间
可见,写入13642条数据用时约5s。
但是这种方式是用了一种拼接字符串的方式,一次性写入1w条数据,想想这个字符串的长度都可怕,我保存了一下发现这条写入1w条数据的SQl文本大小约为386M。可见,这种方式对于数据库内存影响很大。
#一条插入2行数据的命令长度
"insert into table_name
(label1,label2,label3,label4,label5,label6,label7)
values
('val1','val2','val3','val4','val5','val6','val7'),
('val8','val9','val10','val11','val12','val13','val14')
...
2.使用pandas的to_sql导入
这种方式要用到两个模块pandas和sqlalchemy
import pandas as pd
from sqlalchemy import create_engine
将csv中的数据先读取成dataframe
engine = create_engine('postgresql://user:password@host:port/database')
start = datetime.datetime.now()
pd.io.sql.to_sql(df, 'table_name', engine,index= False, schema='your_schema',if_exists='append')
end = datetime.datetime.now()
print('time cost:',(end - start))
可见,用to_sql写入13642条数据用时约8s。
虽然比insert要慢了一些,但是这种方式的数据库内存占用小,不会对数据库造成压力。
另外要注意其中的to_sql参数说明情况:
index = False,不写入索引列,否则必须在目标表中构建一列index。
chunksize,一次性写入的数据量,默认为全部数据一次性写入,可以根据性能进行修改。
to_sql官方文档:pandas.DataFrame.to_sql - pandas 0.25.2 documentation
3.使用copy_from导入
这种方式直接用了psycopg2模块中的copy_from方法,写入速度最快。
代码如下:
# dataframe类型转换为IO缓冲区中的str类型
output = StringIO()
df.to_csv(output, sep='t', index=False, header=False)
output1 = output.getvalue()
conn = pgconnection()
cur = conn.cursor()
cur.copy_from(StringIO(output1),'table_name',columns=['label1', 'label2'])
conn.commit()
cur.close()
conn.close()
相比于前面两种传统的都是利用insert命令的插入方式,这种方式其实是先将结果写入缓存文件中,然后利用copy_from方法直接将文件复制到目标表中的操作,
这种操作的用时令我有点震精,写入13642条数据用时不到1s。
psycopg2官方文档:The cursor class - Psycopg 2.8.4 documentation
PS:后面测试发现,copy_from的参数columns=['label1', 'label2'],最好要写上,否则会出现运行成功但是数据没有写入的情况。
综上,可见copy_from()的效果明显优于上面两种,短时间内虽然会带来造成较高的IO,但是影响时间大大缩短,对于追求效率和优化的脚本可以优先考虑这种方式写入数据。