关于Excel表操作-写入数据库操作

python与大数据分析

发布于 2022-05-19 12:25:02

1.3K0

发布于 2022-05-19 12:25:02

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={})

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-04-29，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库