![37a82edf75d45689da716845f27c2dc3.png](https://i-blog.csdnimg.cn/blog_migrate/503c5133b5dab2a3d1acd0a8be81a40e.jpeg)
在做算法开发的时候,通常都会遇到读取或者写入PG数据库的情况。
一般对于读取PG数据库来说,尽可能将复杂的SQL命令拆分成短小的多条SQL命令并在Python中进行merge会大大的降低脚本运行时间。
同样,对于写入PG数据库来说,不同写入方式也会存在明显的效率差异。这里,我以自己在写入数据库时的三种方式(拼接成insert长字符串,pandas自带的tosql,及psycopg2的copy_from命令)做了一个对比,数据源为1个含有13642条数据的csv
1.insert拼接字符串写入
写入时间
![4d3e2af3ebcd56e5f0301bb44c1140a5.png](https://i-blog.csdnimg.cn/blog_migrate/09926e5e8ab6e4f50d9ea697dd7c3e46.png)
可见,写入13642条数据用时约5s。
但是这种方式是用了一种拼接字符串的方式,一次性写入1w条数据,想想这个字符串的长度都可怕,我保存了一下发现这条写入1w条数据的SQl文本大小约为386M。可见,这种方式对于数据库内存影响很大。
#
2.使用pandas的to_sql导入
这种方式要用到两个模块pandas和sqlalchemy
import
将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))
![c8c1b475052e74fdc546c2a178786067.png](https://i-blog.csdnimg.cn/blog_migrate/29b51b2e9155f041510ee92b3d394e4c.png)
可见,用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方法直接将文件复制到目标表中的操作,
![5c9469c3ad2b302631fa3d82793608b8.png](https://i-blog.csdnimg.cn/blog_migrate/50728ea8726c2486d74596e3d472a1c7.png)
这种操作的用时令我有点震精,写入13642条数据用时不到1s。
psycopg2官方文档:The cursor class - Psycopg 2.8.4 documentation
PS:后面测试发现,copy_from的参数columns=['label1', 'label2'],最好要写上,否则会出现运行成功但是数据没有写入的情况。
综上,可见copy_from()的效果明显优于上面两种,短时间内虽然会带来造成较高的IO,但是影响时间大大缩短,对于追求效率和优化的脚本可以优先考虑这种方式写入数据。