python xlrd写入数据_将Python xlrd数据写入Oracle,pythonxlrd,oracle

import xlrd as xr

import os,pickle

import cx_Oracle

import sys,re,datetime,csv

from xlrd import xldate_as_datetime, xldate_as_tuple

import time

def trim(s):

r = re.findall('[\S]+', s)

print ("替换:"+s)

return " ".join(r)

def query_sql(v_sql):

conn = cx_Oracle.connect('user/pass@host:ip/sid')

c = conn.cursor() # 获取cursor

try:

# 解析sql语句

c.parse(v_sql)

# 捕获SQL异常

except cx_Oracle.DatabaseError as e:

print(e)

c.execute(v_sql) # 使用cursor进行各种操作

row = c.fetchall() # 可以调用cursor.fetchall()一次取完所有结果，或者cursor.fetchone()一次取一行结果

c.close() # 关闭cursor

conn.close() # 关闭连接

return row

def insert_batch_sql(v_sql, data):

conn = cx_Oracle.connect('user/pass@host:ip/sid')

c = conn.cursor()

try:

c.parse(v_sql)

except cx_Oracle.DatabaseError as e:

print(e)

c.executemany(v_sql,data) #批量写入

conn.commit()

c.close()

conn.close()

def insert_log(log_msg):

conn = cx_Oracle.connect('user/pass@host:ip/sid')

c = conn.cursor()

dt =datetime.datetime.now()

param = {'rzxx': log_msg,'rzsj':dt}

c.execute('insert into t_log values(seq_t.nextval,:rzxx,:rzsj)', param);

conn.commit()

c.close()

conn.close()

#excel写入数据,检查数据库表的列数和excel是否一致，不一致的跳过

def excel_check_and_imp(filename,tablename,col_nums):

data = xr.open_workbook(filename)

sheets = data.sheet_names()

for sheet in sheets:

table = data.sheet_by_name(sheet)

ncols = table.ncols

if ncols != col_nums:

log_msg = "[跳过原因2]文件:["+filename+"]，sheet["+sheet+"]和表["+tablename+"]列数不一致"

print("文件:[%s],sheet[%s]和表[%s]列数不一致，跳过"%(filename,sheet,tablename))

print(table.row_values(1))

insert_log(log_msg)

return -1

else:

batch_to_ora(filename,tablename)

return 1

#检查路径下是否有excel文件

def fold_files_check(path):

if os.path.exists(path):

filenames = os.listdir(path)

for i,filename in enumerate(filenames):

if i == 0:

iSpecialFile = i + 1

sFileName = filename

print('==================正在检查第%s个文件=========================' % (i + 1))

if sFileName.find(".xls") <0 :

log_msg="[跳过原因1]文件：[" + filename + "]为非excel文件"

print('文件：[%s]为非excel文件，跳过' % (filename))

insert_log(log_msg)

continue

else:

log_msg = "文件：[" + (path+"\\"+filename) + "]文件格式检查通过,开始导入"

print('文件：[%s]文件格式检查通过' % (path+"\\"+filename))

insert_log(log_msg)

#通过filename确定表名

list_table=['T1','T2','T3','T4','T5','T6','T7','T8','T9','T10','T11','T12','T13','T14','T15','T16','T17','T18','T19','T20','T21','T22','T23']

tablename=(list_table[int(filename[0:2])-1])

# 获取表的列数

v_sys_sql = "select count(1) from sys.dba_tab_cols a where a.owner='RAM' AND A.TABLE_NAME='" + tablename.upper() + "'"

cols_name = query_sql(v_sys_sql)

for col in cols_name:

col_nums = col[0]

#检查通过的进行导入

if excel_check_and_imp(path+"\\"+filename,tablename,col_nums) <0 :

continue

else:

print('文件夹:(%s)不存在！' % (path))

def batch_to_ora(filename,tablename):

#读excel

data = xr.open_workbook(filename)

sheets = data.sheet_names()

for sheet in sheets:

table = data.sheet_by_name(sheet)

#table = data.sheets()[0]

nrows = table.nrows

ncols = table.ncols

#准备变量

list = [] #批量写入

num = 0

#插入表的SQL

sql = "INSERT INTO :tab :cols VALUES :vals "

#查询系统视图，获取列名，并组装SQL

v_sys_sql = "select listagg(column_name,',') WITHIN GROUP (order by column_id),listagg(':'||column_id,',') WITHIN GROUP (order by column_id) from sys.dba_tab_cols a where a.owner='RAM' AND A.TABLE_NAME='"+tablename.upper()+"'"

cols_name = query_sql(v_sys_sql)

for col in cols_name:

cols = "(" + col[0] + ")"

vals = "(" + col[1] + ")"

#获取所有数据类型列表

v_sys_sql2 = "select DATA_TYPE from sys.dba_tab_cols a where a.owner='RAM' AND A.TABLE_NAME='" + tablename.upper() + "'"

coltype = []

col_types = query_sql(v_sys_sql2)

for i in range(len(col_types)):

str_type=str(col_types[i])

if str_type.find("VARCHAR2")>0:

coltype.append("VARCHAR2")

elif str_type.find("NUMBER")>0:

coltype.append("NUMBER")

elif str_type.find("DATE")>0:

coltype.append("DATE")

#组装SQL

sql=sql.replace(":cols", cols).replace(":vals", vals).replace(":tab",tablename)

# for iRow in range(1, nrows):

# for iCol in range(ncols):

# sCell = table.cell_value(iRow, iCol)

# # ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

# ctype = table.cell(iRow, iCol).ctype

# if ctype == 3:

# date = xr.xldate_as_datetime(sCell,data.datemode)

#批量写入，6000一批

excel_cols_num_imp = 0

for i in range(1,nrows):

tab_row = table.row_values(i)

for j, x in enumerate(tab_row):

if table.cell(i, j).ctype==3:

tab_row[j]=xr.xldate_as_datetime(tab_row[j],data.datemode).strftime('%Y-%d-%m %H:%M:%S')

if x=='':

tab_row[j] = None

# if table.cell(i, j).ctype==1 and trim(x)== '':

# tab_row[j] = None

# elif coltype[j] == 'NUMBER':

# if trim(x) == '':

# tab_row[j]=None

#如果数据类型为varchar,并且excel为科学技术法，则进行转换

elif coltype[j] == 'VARCHAR2':

try:

v=str(tab_row[j])

#科学计数法转换成字符串

if v.find("e+") != -1:

v = "%.f" % float(tab_row[j])

tab_row[j] = str(v)

except Exception as e:

tab_row[j] = str(tab_row[j])

#如果为number,且excel为字符，则强制转换为Int类型

#TypeError: expecting string or bytes object:一般出现这个问题，是因为excel读出来为'96.99',带引号的，要使用float转一下

elif coltype[j] == 'NUMBER':

try:

v = str(tab_row[j])

if v.find(".") != -1:

v = "%.2f" % float(tab_row[j])

tab_row[j] = float(v)

else:

tab_row[j] = int(v)

except Exception as e:

tab_row[j] = str(tab_row[j])

list.append(tab_row)

print(tab_row)

num += 1

if num>=batch_limit:

print("正在导入文件:[%s],sheet:[%s]总共[%s]行,已导入[%s]行" % (filename,sheet, nrows - 1, excel_cols_num_imp))

insert_batch_sql(sql, list)

excel_cols_num_imp = excel_cols_num_imp + num

num = 0

list.clear()

log_msg = "文件：[" + filename + "],sheet["+sheet+"]总共[" + str(nrows-1)+ "]行,已导入["+str(excel_cols_num_imp)+"]行"

print("文件:[%s],sheet:[%s]总共[%s]行,已导入[%s]行"%(filename,sheet,nrows-1,excel_cols_num_imp))

insert_log(log_msg)

#剩下的小于批量数的再次插入

print("正在导入文件:[%s],sheet:[%s]总共[%s]行,已导入[%s]行，导入完成" % (filename,sheet, nrows - 1, excel_cols_num_imp))

insert_batch_sql(sql, list)

excel_cols_num_imp = excel_cols_num_imp + num

list.clear()

log_msg = "文件:[" +filename+ "总共" + str(nrows-1) + "]行,已导入[" + str(excel_cols_num_imp) + "]行，导入完成"

print("文件:[%s],sheet:[%s]总共[%s]行,已导入[%s]行，导入完成" % (filename, sheet,nrows-1, excel_cols_num_imp))

insert_log(log_msg)

batch_limit = 10000

fold_files_check("C:\\Users\\sp\\Desktop\\data\\******")

#fold_files_check("C:\\test")

# if __name__=="__main__":

# cmd=sys.argv[1]

# p1=cmd.split(",")[0]

# fold_files_check(p1)