有近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
。