python百万级数据批量插入postgreSQL数据库 copy_from()执行效率完胜executemany()和to_sql()方法

104 阅读 0 评论 69 点赞

我是靠谱客的博主伶俐心锁，这篇文章主要介绍python百万级数据批量插入postgreSQL数据库 copy_from()执行效率完胜executemany()和to_sql()方法，现在分享给大家，希望可以做个参考。

1.查询数据库中数据表是否存在，不存在则创建

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import psycopg2
conn = psycopg2.connect(host=***, port=***, database=***, user=***, password=***)
cur = conn.cursor()
try:
cur.execute("select to_regclass(" + "'" +
table_name + "'" + ") is not null")
rows = cur.fetchall()
except Exception as e:
rows = []
conn.close()
if rows:
data = rows
flag = data[0][0]
print(flag)

flag的值即为查询出来的表是否存在的状态，存在则为True，不存在则为False
利用python在数据库创建表的例子网上很多，在此就不进行赘述了。

executemany()方法批量输入数据到数据库
data是需要插入的数据，为list类型，3列，数据条数有70多万条

复制代码

1
2
3
4
5
6
7
8
9
import psycopg2
conn = psycopg2.connect(host=***, port=***, database=***, user=***, password=***)
cur = conn.cursor()
sql =
"insert into " + table_name + " values(%s, %s, %s)
cur.executemany(sql, data)
conn.commit()
conn.close()

该方法下，70多万条数据插入到数据库需要3.88分钟

datafame的to_sql()插入数据到数据库

复制代码

1
2
3
4
5
from sqlalchemy import create_engine
result = pd.DataFrame(data)
engine = create_engine('postgresql://user:password@host:port/database')
pd.io.sql.to_sql(result, table_name, engine, index = False, if_exists='append')

该方法下，70多万条数据插入到数据库需要4.42分钟

强大的copy_from()，是postgresSQ的内置函数

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import psycopg2
from sqlalchemy import create_engine
import pandas as pd
from io import StringIO
data1 = pd.DataFrame(data)
# dataframe类型转换为IO缓冲区中的str类型
output = StringIO()
data1.to_csv(output, sep='t', index=False, header=False)
output1 = output.getvalue()
conn = psycopg2.connect(host=***, user=***, password=***, database=***)
cur = conn.cursor()
cur.copy_from(StringIO(output1), table_name1)
conn.commit()
cur.close()
conn.close()
print('done')