python出入库_python出入库_基于Python对Excel数据入库的处理

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([email protected]: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!")