Excel数据上传数据库（Oracle）

Excel数据上传数据库（Oracle）

使用Python 将Excel数据批量上传至数据库
1.Excel 路径：C:/Excel/
ps：Excel Title应一致，所处sheet名应一致
2.代码：

import sys
import os
import cx_Oracle
import xlrd
from xlrd import xldate_as_tuple
from datetime import datetime as datetime1
import threading
import time
import datetime
import PyLog
import logging
import codecs
import xlsxwriter
import openpyxl
import pandas
import shutil

#获取文件夹下Excel
for f in os.listdir("C:/Excel/"):
    print(f)
    try:
    	#打开Excel
        data = xlrd.open_workbook('C:/Excel/'+ f)
        #获取Excel数据 
        excel = data.sheet_by_name('Sheet1')
        #连接Oracle 数据库
        host = "**.**.***.***"
        port = "****"
        sid = "******"
        oracleStr = cx_Oracle.makedsn(host, port, sid)

        if (excel.ncols) < 1:
            raise Exception("Excel has no data")
        logging.debug("[READ] OPEN EXCEL start time:%s " % time.ctime(time.time()))

        row = []

        row = [('' for i in range(excel.ncols)) for j in range(excel.nrows-1)]

        for i in range(excel.nrows):
            if (i==0):
                row[0] = tuple(excel.row_values(i), )
            if not (i==0):
                row[i-1] = tuple(excel.row_values(i),)

        print("[READ] OPEN EXCEL End time:%s " % time.ctime(time.time()))
        logging.debug("[READ] OPEN EXCEL End time:%s " % time.ctime(time.time()))
        print("="*10,"SHEET1共計",excel.nrows-1,"筆","="*10)


        #======================================

        oracleConn = cx_Oracle.connect("账号", "密码", oracleStr)
        cur = oracleConn.cursor()

        if [row]:
            for i in range(excel.nrows-1):
                tmp = (datetime.datetime.now(),)
                date = (datetime1(*xldate_as_tuple(float(row[i][7]), 0)),)
                row[i] = row[i] + tmp + date
            logging.debug("START insert DB TIME:%s" % time.ctime(time.time()))
            cur.executemany("INSERT INTO TableName(栏位1,栏位2,栏位3,栏位4,栏位5) VALUES (:1,:2,:3,:4,:5)",row[0:])
            logging.debug("END insert DB TIME:%s" % time.ctime(time.time()))
            print("END insert DB TIME:%s" % time.ctime(time.time()))
            #存入后可移动或删除
            shutil.move("C:/Excel/" + f, "C:/Excel/历史上传/")
            print("MOVE OK!")


        if not row:
            print(" no data")
            logging.debug(" no data")
        oracleConn.commit()
        cur.close()
        oracleConn.close()
        print("[INSERT_WO]  END ")

        logging.debug("END :%s time: %s " % (time.ctime(time.time())))
        print("END :%s time: %s " % (time.ctime(time.time())))
    #如果某个Excel 数据格式错误则跳过
    except (Exception) as errorHandle:
        print("ERROR :", errorHandle)
        logging.debug("ERROR :", errorHandle)
        pass
    else:
        print("no error")

以上，不懂可留言