Python读取Excel大数据量写入Oracle表

使用openpyxl读取xlsx文件（不支持xls格式文件），并将数据保存到oracle数据库中，环境win10 64位。

支持大数据量，测试5万条数据，10秒左右写入完成。

支持只导入指定的excel的列，即进行列的裁剪。

Anaconda3，Python 3.8

#encoding = gbk

import cx_Oracle
from openpyxl import Workbook
from openpyxl import load_workbook

cx_Oracle.init_oracle_client(config_dir='D:\\Software\\OracleClient&PLSQL Developer\\instantclient_11_2')

connectionStr = 'dw/dw@10.3.3.111:1521/orcl'


#读取excel的WorkSheet的数据，获取指定的列，返回的是一维数组
#begin是开始的行，columns是列标名称，第1-2列就写AB
def read_column(ws, begin, columns):
    return [ws["{}{}".format(column, row)].value for row in range(begin, ws.max_row + 1) for column in columns]

wb = load_workbook('E:\\WorkDocument\\temp_file_2022_02\\ImportData.xlsx')
#猜测格式类型
wb.guess_types = True
#读取默认激活的表页
#ws1 = wb.active
#读取指定名称的表页（当xlsx文档有多个表页时，推荐用这个）
ws1 = wb.get_sheet_by_name('Sheet1')

print('总行数', ws1.max_row)
print('总列数', ws1.max_column)

#行从第2行开始，列只取J列和K列，列不需要连续，例如取AIR三列
column_ab_values = read_column(ws1, 2, 'JK')

#将裁剪的数据，一维数组格式的，转为二维数组（两列）
#若是多列，则此代码需要调整(2就表示2列，若有三列，则将下面循环的两个2改为3
arr2=[]
for index in range(0, len(column_ab_values), 2):
	arr2.append(column_ab_values[index:index + 2])
#以下这行打印，会把整个数据作为一行输出，非必须，可注释掉
#print(arr2)

#print('column_ab_values', column_ab_values)



v_sql = "insert into rpt.sales_2023453(IDD,SHOP_ID) values(:1, :2)"

#data = [jg, ts_hc, ts_cbze]
#print(data)
#insert_sql(sql, column_ab_values)

conn = cx_Oracle.connect(connectionStr)  # 连接数据库
c = conn.cursor()  # 获取cursor
try:
    # 解析sql语句
    c.parse(v_sql)
# 捕获SQL异常
except cx_Oracle.DatabaseError as e:
    print(e)
c.executemany(v_sql, arr2)
conn.commit()

c.close()  # 关闭cursor
conn.close()  # 关闭连接

python连接oracle的时候报错

DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: “The specified module could not be found”. See https://oracle.github.io/odpi/doc/installation.html#windows for help

解决方案

作者：COLOR_KU
链接：https://www.jianshu.com/p/f3cfd6fb3f36
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

oracle的python驱动，连接问题：

1、配置path环境变量，把oracle的client的根目录（oci.dll文件所在的目录），添加到Path环境变量中Path = C:\Instant Client\bin
2、配置Oracle相关的两个环境变量NLS_LANG = AMERICAN_AMERICA.ZHS16GBK。配置instantclient_11_2的环境变量到ADMIN目录下。TNS_ADMIN = D:\instantclient_11_2\NETWORK\ADMIN

4、配置tnsnames.ora文件（自行百度），完成后使用navicat验证链接，成功后继续。

5、将instantclient_11_2目录下的oci.dll oraocci11.dll oraociei11.dll 放到python的Lib下的site-packages下。我的环境是Anaconda3的环境，python是自己创建的虚拟环境，PythonExcelDemo ，目录是D:\ProgramFiles\Anaconda3\envs\PythonExcelDemo，就把oci.dll oraocci11.dll oraociei11.dll 放到该目录下。

6、代码最开始加上  cx_Oracle.init_oracle_client(config_dir='D:\\Software\\OracleClient&PLSQL Developer\\instantclient_11_2')

我把网上找到的相关的配置，都配置了一遍，可能有些不是必须的，这个没空去检查了。最主要的是拷贝三个dll文件到虚拟环境目录，这一笔做完了，才没有再报错。

oracle的相关配置，参考链接：https://www.jianshu.com/p/a66afc1f5082

大数据量oracle写入，批处理提交：

https://blogs.oracle.com/opal/post/efficient-and-scalable-batch-statement-execution-in-python-cx_oracle