Python3 使用executemany()、extras和to_sql()三种方法分别实现批量数据写入postgressql数据库中

Python3 使用executemany()、extras和to_sql()三种方法分别实现批量数据写入postgressql数据库中

1.知识点

1）使用psycopg2模块的executemany()方法实现批量写入数据到postgresql数据库中

2）使用psycopg2模块的extras实现批量写入数据到postgresql数据库中

3）使用pandas模块的to_sql()方法实现批量写入数据到postgresql数据库中

2.各个方法的实现

1）使用psycopg2模块的executemany()方法实现批量写入数据到postgresql数据库中

import psycopg2
# 创建一个二维列表，列表中的每个列表代表数据库中的一条记录
data = [['1', '2', '3'],['1', '2', '3']]   
conn = psycopg2.connect(host=***, port=***, database=***, user=***, password=***)
cur = conn.cursor()
# values中的%s对于需要写入数据库的每天记录的值
sql =  "insert into " + table_name + " values(%s, %s, %s)
cur.executemany(sql, data)
conn.commit()
cur.close()
conn.close()

2）使用psycopg2模块的extras实现批量写入数据到postgresql数据库中

import psycopg2
from psycopg2 import extras as ex

# values 后面直接%s
sql = 'insert into tb_user(id, userame, passwd, roleid, lasttime, failnum, info) values %s'
# 创建一个元组的list
datalist = []
# 行数据是以元组的形式存放，将元组循环加入list中,例如(1, 2, 3, 4, 5, 6, 'test')是数据库中的一条记录
datalist.append((1, 2, 3, 4, 5, 6, 'test'))
conn = psycopg2.connect(host='127.0.0.1', user="postgres", password="123456", database="postgres")
cur = conn.cursor()
ex.execute_values(cur, sql, datalist, page_size=10000)
conn.commit()
cur.close()
conn.close()

3）使用pandas模块的to_sql()方法实现批量写入数据到postgresql数据库中

 to_sql官方文档：https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

from sqlalchemy import create_engine
# 创建一个二维列表，列表中的每个列表代表数据库中的一条记录
data = [['1', '2', '3'],['1', '2', '3']]
# 将list类型数据转换为DataFrame
result = pd.DataFrame(data)
engine = create_engine('postgresql://user:password@host:port/database')
# index = False，不写入索引列，否则必须在目标表中构建一列index。
pd.io.sql.to_sql(result, table_name, engine, index = False, if_exists='append')

3.性能比较

psycopg2模块的executemany()和psycopg2模块的extras性能相差不大，pandas模块的to_sql()比前两者稍弱要慢了一些，但是这种方式的数据库内存占用小，不会对数据库造成压力。

推荐批量数据写入PG数据库最强推荐使用另一种方法copy_from
详细使用方法参见博客