Excel表要写入数据库,毕竟通过pandas来操作复杂的数据还是很有难度的,有多少种数据库,就有多少种数据库引擎,要下载多少python数据库包,同样对Excel写入数据库的操作,可以基于excel按行写入,也可以通过pandas处理,但pandas处理效率不高。
基于openpyxl写入数据的操作包括,以下几步
1、基于表名构造一个绑定变量的SQL
2、读取excel表,转换为list
3、逐行写入,最后提交
踩坑如下:
cx_Oracle.DatabaseError: DPI-1043:invalid number一般是excel表出现空值
还有就是一些生僻的汉字写入数据库的时候,报汉字编码错误,没找到合适的解决办法。
import cx_Oracle
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import types
import openpyxl
sheetname='Sheet0'
resourcefilenames=[r'C:\Users\baoqi\Documents\文件1.xlsx',
r'C:\Users\baoqi\Documents\文件2.xlsx',
r'C:\Users\baoqi\Documents\文件3.xlsx',
r'C:\Users\baoqi\Documents\文件4.xlsx'desttablename=['table1','table2','table3','table4']
def get_sql(cursor,tablename):
cursor.execute("select column_name from user_tab_columns where table_name = upper('{}')".format(tablename))
res=c.fetchall()
res = [a[0] for a in res]
fieldname=','.join(res)
colnum=len(res)
zwf=[':'+str(i+1) for i in range(colnum)]
zwf=','.join(zwf)
SQL = "INSERT INTO {} ({}) VALUES({})".format(tablename, fieldname, zwf)
print(SQL)
return SQL
conn_str = cx_Oracle.makedsn('127.0.0.1', '1521', service_name='ORCL2') # 数据库信息,注意是service_name,非SID
conn = cx_Oracle.connect(user='username', password='password', dsn=conn_str) # 用户信息
c = conn.cursor() #创建连接
for i, filename in enumerate(resourcefilenames):
print('处理第{}表,库表为{},excel名称为{}'.format(i,desttablename[i],filename))
sql=get_sql(c,desttablename[i])
# 解决 cx_Oracle.DatabaseError: DPI-1043:invalid number 问题
pddata = pd.read_excel(filename, header=0, engine="openpyxl").fillna('-')
datalist = pddata.values.tolist()
for data in datalist:
c.execute(sql,tuple(data))
conn.commit()
c.close()
基于pandas写入数据库,一定要使用sqlalchemy,操作比较简单,但效率较低。
import cx_Oracle
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import types
import openpyxl
filename = r'C:\Users\baoqi\Documents\test.xlsx'
conn_string='oracle+cx_oracle://user:pass@host:port/dbname'
engine = create_engine(conn_string, echo=False)
pddata = pd.read_excel(filename, header=0, engine="openpyxl")
pddata.to_sql('testtest', con=engine, if_exists='append', index=False,dtype={})
本文分享自 python与大数据分析 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!