postgresql批量导入的效率优化

  有近3万条数据需要从excel中导入postgresql中。之前使用to_sql方法实现，耗时2min上下，需要做一下简单的优化。

  如果使用的mysql或者时SQL Server数据库可以在配置数据连接时，添加参数fast_executemany=True。但是postgresql数据库常用的psycopg2不支持这种配置。

  推荐使用psycopg2的copy_from()函数，先看代码：

# -*- coding:UTF-8 -*-
"""
 @ProjectName  :
 @FileName     :read_excel  
 @Description: 读取excel数据
 @Time         :2021/3/19 15:05
 @Author       :Qredsun
 @Author_email :1410672725@qq.com  
 """
 
import time
import psycopg2
import pandas as pd
from io import StringIO
from sqlalchemy import create_engine

def psycopg2_function(df, table_name, host='123.11.140.42', port='5432', user='CRM', passwd='123456', db='GS'):

	output = StringIO()
	df.to_csv(output, sep='\t', index=False, header=False)
	output1 = output.getvalue()

	conn = psycopg2.connect(host=host, port=port, user=user, password=passwd, dbname=db)
	cur = conn.cursor()

	# 判断表格是否存在，不存在则创建
	try:
		cur.execute("select to_regclass(" + "\'" + table_name + "\'" + ") is not null")
		rows = cur.fetchall()
	except Exception as e:
		rows = []
	if rows:
		data = rows
		flag = data[0][0]
		print(flag)

	if flag != True :
		sql = f'''CREATE TABLE "public"."{table_name}" ( \
              "id" int8,\
              "center_x" float8,\
              "center_y" float8,\
              "center_z" float8,\
              "local_timestamp" timestamp(6),\
              "timestamp" float8,\
              "track_id" int8,\
              "lane_ids" int8,\
              "connection_ids" int8,\
              "det_confidence" int8,\
              "obs_drsuids" int8,\
              "is_valid" text COLLATE "pg_catalog"."default",\
              "frame_number" int8\
            )
            ;'''
		cur.execute(sql)
		conn.commit()

	# 获取列名
	columns = list(df)
	cur.copy_from(StringIO(output1), table_name, null='', columns=columns)
	conn.commit()
	cur.close()
	conn.close()

path_excel = 'acu.xlsx' # 'acu_status_info_history_2_parsed.xlsx' #


def excel_to_DB(host='123.11.140.42', port='5432', user='CRM', passwd='123456', db='GS', path_excel = 'acu.xlsx', table_name = 'traffic'):

	data_excel = pd.read_excel(path_excel,engine='openpyxl')

	ticks_min = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
	print(f'read excel at : {ticks_min}')

	data_dataframe = pd.DataFrame(data_excel)

	ticks_min = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
	print(f'write db start at : {ticks_min}')

	# DataFrame.to_sql方法
	# engine =create_engine(f'postgresql+psycopg2://{user}:{passwd}@{host}:{port}/{db}',encoding='utf8')  
	# data_dataframe.to_sql(table_name,con=engine,if_exists='replace',index=False, method="multi") # 没有表会自动创建


	# copy_from 方法
	psycopg2_function(data_dataframe, table_name, host, port, user, passwd, db)

	ticks_min = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
	print(f'write db end at : {ticks_min}')

ps：copy_from默认将\N作为NULL值，但是to_csv会将None值变''字符串，因此需要在copy_from中说明null=''，即空字符串就是代表的NULL。