使用python将大量XLS数据加载到Oracle中

Question

我有一个3+百万记录XLS文件，需要使用python2.7在Oracle12CDB(直接转储)中转储该文件。

我正在使用Cx_Oracle python包来建立到Oracle的连接，但是读取和转储XLS (使用openpyxl pckg)非常慢，而且性能下降了数千/百万记录。

从脚本站的角度来看，使用了两种方法-

1. 我尝试过大容量加载，方法是读取数组中的所有值，然后使用游标准备(使用绑定变量)将其转储，而游标fetchmany.This不能很好地处理海量数据。
2. 数据的迭代加载是fetched.Even的，这种方式存在性能问题。

作为最佳实践，我可以部署哪些选项和技术/包来将这卷数据从XLS加载到Oracle DB ?通过脚本加载这卷数据是明智的，还是应该使用ETL工具？到目前为止，我只能通过python脚本来选择，所以请回答前者。

Answer 1

如果可以将excel文件导出为CSV，则只需使用sqlldr将文件加载到db中即可。

Answer 2

基本上，对于大容量的数据，除了C之外，任何语言都会强调I/O、。最好的方法是使用DB供应商提供的本机工具/实用程序。对于甲骨文来说，最合适的是SQL。

有关快速教程http://www.thegeekstuff.com/2012/06/oracle-sqlldr/，请参阅此链接

给你，…运行并返回代码、输出和错误的示例代码

sql_ld_command = ['sqlldr ',  'uid/passwd', 'CONTROL=', 
'your_ctrl_file_path', 'DATA=', 'your_data_file_path']   

sql_ldr_proc  = subprocess.Popen(sql_ld_command, stdin=subprocess.PIPE,stdout=subprocess.PIPE, stderr=subprocess.PIPE)   

out, err  = sql_ldr_proc.communicate()  
retn_code = sql_ldr_proc.wait()

Answer 3

以下是所有步骤: load xlsx，生成csv (制表符分隔)和ctrl文件，使用sqlldr加载。

# %%
import sys
import pandas as pd
import subprocess
# %%
user = 'in_user_name'
password = 'in_password'
host = 'in_host'
database = 'in_service_name'
in_file = r"in_file.xlsx"
in_sheet_name = 'in_sheet'
tablename = 'in_table'

# %%
df = pd.read_excel(in_file, sheet_name=in_sheet_name)
print(f"Loaded {df.shape[0]} records from {in_file}")
# %%
inflie = f'{tablename}.csv'
controlfile = f'{tablename}.ctrl'
# %%,
df.to_csv(inflie, index=False, sep='\t',)
# %%
columns = df.columns.tolist()
with open(controlfile, 'w') as file:
    header = f"""OPTIONS (SKIP=1, DIRECT=TRUE ) 
LOAD DATA
INFILE '{inflie}' 
BADFILE '{tablename}.bad'
DISCARDFILE '{tablename}.dsc'
TRUNCATE
INTO TABLE {tablename}
FIELDS TERMINATED BY X'9'  
TRAILING NULLCOLS
( """
    file.write(header)
    for c in columns[:-1]:
        file.write(f'{c},\n')
    file.write(f'{columns[-1]})')
# %%
sqlldr_command = f"""sqlldr USERID='{user}/{password}@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={host})(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME ={database}) ))'  control={controlfile}"""
print(f"Running sqlldr. Log file: {tablename}.log")
subprocess.call(sqlldr_command, shell=True)