dataframe 拼接_DataFrame写入PG数据库效率对比

在做算法开发的时候，通常都会遇到读取或者写入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.使用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))

可见，用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，但是影响时间大大缩短，对于追求效率和优化的脚本可以优先考虑这种方式写入数据。