python读取Excel插入oracle库；批量插入

调包说明

读取：pandas

插入：oracledb

插入一行数据 

def insert_table1():
    path = r'D:\sheet1.xlsx'
    data = pd.read_excel(path)
    list_1 = [data.iloc[0,0],data.iloc[0,1],data.iloc[0,2],data.iloc[0,3],data.iloc[0,4],str(data.iloc[0,5])]
    # sql_1 = 'insert into table1 values(%s,%s,%s,%s,%s,%s)'
    sql_1 = 'insert into table1 values(:1,:2,:3,:4,:5,:6)'
    cursor.execute(sql_1, list_1)
    # cursor.executemany(sql_1, list_1)
    db.commit()
    print('table1完成')

iloc函数自动跳过column行，因此data.iloc[0,0]读取的是第二行第一个数

注释的是错误示范，后面细说

插入多行数据

def insert_table2():
    path = r'D:\sheet2.xlsx'
    data = pd.read_excel(path)
    list_2 = []
    for i in range(data.shape[0]):
        # list_2.append((data.iloc[i,0],data.iloc[i,1],str(data.iloc[i,2]),str(data.iloc[i,3])))
        list_2.append((data.iloc[i,0],data.iloc[i,1],data.iloc[i,2],str(data.iloc[i,3])))
    # sql_2 = 'insert into table2 values(%s,%s,%s,%s)'
    sql_2 = 'insert into table2 values (:1,:2,:3,:4)'
    cursor.executemany(sql_2, list_2)
    db.commit()
    print('table2完成')

for循环读取行数据封装到元组，在追加到列表。使用元组是因为更安全

executemany()函数可以完成批量插入

插入大量数据（指有很多列）

def insert_table3():
    path = r'D:\sheet3.xlsx'
    data = pd.read_excel(path)
    list_3 = []
    for i in range(data.shape[0]):
        insert_list3 = []
        for j in range(data.shape[1]):
            insert_list3.append(str(data.iloc[i, j]))
        list_3.append(insert_list3)
    # sql_3 = 'insert into table3 values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    sql_3 = 'insert into table3 values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33)'
    cursor.executemany(sql_3, list_3)
    db.commit()
    print('table3完成')

使用嵌套循环完成

调试过程

这个小需求也是花费了我很多精力，因为python做这个的很少，网上的资源不多。

oracledb包

选到这个包也是浪费了我很多时间，因为之前用的大多是cx_oracle，不过oracledb确实好用，没有版本的要求。

sql语句占位符设置

因为上述原因，搜到的sql是用的%s进行占位，但是oracledb改用

:1,:2,:3,:4,:5,:6

进行占位，报错信息如下：

oracledb.exceptions.DatabaseError: DPY-4009: 0 positional bind values are required but 6 were provided

sql语句结束符

不需要；结束，不然会报错：

oracledb.exceptions.DatabaseError: ORA-00933: SQL 命令未正确结束

我搜索之后还以为是需要commit进行提交，SQL语句就写成了

insert into table1 values(:1,:2,:3,:4,:5,:6);commit;

其实并不需要，使用

db.commit()

就行了

数据类型不适用

从Excel中读取的数值型数据，插入到oracle中前要先转换成string型，插入时会根据字段类型转换成number型，使用str（），不然报错如下：

oracledb.exceptions.NotSupportedError: DPY-3002: Python value of type "int64" is not supported

 总结

程序虽然简单，但是网上关于oracledb库的教程少，因此仅仅3个问题就让我晕头转向。让我以为是不是数据库没连接上？是不是sql语句只能用单引号引起来？是不是execute函数传参传错了？好在最后还是成功排查出这三个问题，记录下来也是希望能够帮到其他人。

欢迎评论区探讨