Python从excel导入数据到oracle
#!/usr/bin/env python3# -*- coding: utf-8 -*-
' Read excel and import to Oracle database '
import os
import cx_Oracle
import openpyxl
conn = cx_Oracle.connect("sa", "sa123", "ORA_Test")
cursor = conn.cursor()
'''cursor.execute("select 'hello', 'world!' from dual")
rows = cursor.fetchall()
for row in rows:
print(row, row)
cursor.close()
conn.close()'''
excelPath = r"D:\TEST.xlsx"
class Listexcel(object):
pass
def run():
wb = openpyxl.load_workbook(excelPath)
sheet = wb["Sheet1"]
MachineName=""
DrumValue=""
lst =[]
print(wb.encoding)
sql = """insert into PyExcel2Ora values (:RowNo,:MachineName,:DrumValue,sysdate)"""
for i in range(5,sheet.max_row+1):
for j in range(1,3):
try:
if j==1:
MachineName=sheet.cell(row=i,column=j).value
#print(MachineName)
else:
DrumValue=sheet.cell(row=i,column=j).value
#print(DrumValue)
except Exception as ex:
print("Process row {0} failure！，reason：{1}".format(i,str(ex)))
if(not MachineName is None and not DrumValue is None):
#print(i)
listexcel=Listexcel()
listexcel.RowNo=i-4
listexcel.MachineName=MachineName
listexcel.DrumValue=DrumValue
lst.append(listexcel)
MachineName=""
DrumValue=""
#print(sheet.cell(row=i,column=j).value)
cursor.execute("Truncate table PyExcel2Ora")
for item in lst:
cursor.execute(sql,{
'RowNo': item.RowNo,
'MachineName': item.MachineName,
'DrumValue': item.DrumValue.encode('utf-8')
})
rowCount=cursor.rowcount
if (rowCount <=0) :
print("Import row {0} failure！Machinename：{1}".format(item.RowNo+4,item.MachineName))
conn.commit()
cursor.close()
conn.close()
run()
页:
[1]