1.项目背景
将Excel表格中的数据源，加载到Oracle数据库中
2.技术方案
Python、Cx_Oracle、xlrd、datetime，批量插入
3.具体方案
# -*- coding: utf-8 -*-
import cx_Oracle
import xlrd
import datetime
#connect the oracle
conn = cx_Oracle.connect('username/password@ip:port/instance')
cursor = conn.cursor()
count = 0
#the path of the excel
file_name = "E:\import2.xlsx"
#open the excel
file = xlrd.open_workbook(file_name)
sheets = file.sheet_by_index(0)
#the number of row
nrows = sheets.nrows
#the number of the cloumn
ncols = sheets.ncols
#Record start time
begin_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print("The begin time:" + begin_time)
for i in range(0, nrows):
row_data = sheets.row_values(i)
if row_data:
try:
#Here,Two columns of data are inserted
sql_insert = "insert into test(name, address) values('%s', '%s')" %(row_data[0], row_data[1])
cursor.execute(sql_insert)
count += 1
#Bulk insert
while count==5000:
conn.commit()
count = 0
except:
print("Load Error1")
try:
conn.commit()
except:
print("Load Error2")
cursor.close()
conn.close()
#Recore end time
end_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print("The end time:" + end_time)
print("Load Success!")
