# -*- coding=utf-8 -*-
import xlrd
import string
import os
import pymysql
import time
import datetime
import cx_Oracle
def IsSubString(SubStrList,Str):
flag=True
for substr in SubStrList:
if not(substr in Str):
flag=False
return flag
def fn_get_filelist(FindPath,FlagStr=[]):
FileList=[]
FileNames=os.listdir(FindPath)
if (len(FileNames) > 0):
for fn in FileNames:
if (len(FlagStr) > 0):
#返回指定类型的文件名
if (IsSubString(FlagStr,fn)) :
fullfilename=os.path.join(FindPath,fn)
if '~$' not in fullfilename :
FileList.append(fullfilename)
else:
#默认直接返回所有文件名
fullfilename=os.path.join(FindPath,fn)
FileList.append(fullfilename)
#对文件名排序
if (len(FileList)>0):
FileList.sort()
for i in range(len(FileList)):
print (FileList[i])
return FileList
if __name__ == '__main__':
import sys
Ocon=cx_Oracle.connect('rs_guoxx_2op','password','192.168.18.103:1521/ordb')
Ocursor = Ocon.cursor()
path = "E:/csv/"
fs=fn_get_filelist(path,['csv'])
enclose = '"'
enline = '/n'
for ii in range(len(fs)):
fileName = fs[ii]
commitI = 0
tableName = fileName.replace(path,'').replace('.csv','')
# print(fileName,'fileName',tableName)
sql = " select c.column_name,data_type,column_id from user_tab_columns c where c.table_name = '{0}' order by column_id ".format(tableName.upper())
Ocursor.execute(sql)
R = Ocursor.fetchall()
ic = ''
i ,dateList ,numList= 0 ,[],[]
for r in R:
ic = ic+ r[0] + ','
if r[1] == 'DATE':
dateList.append(i)
if r[1] == 'NUMBER':
numList.append(i)
i = i + 1
ic = ic[0:-1]
print(tableName ,' write begin ' ,datetime.datetime.now())
with open(fs[ii],'r', encoding='gb18030') as f:
sql_insert,sql_values = '', ''
sql_i = 0
file_row =0
sql = ''
lineCount = 0
lineCt = 0
for line in f:
lineCt +=1
insertS = " insert into " + tableName + '(' + ic + ") \r\n values( "
ll = line.replace("'","").split(enclose+','+enclose)
# (insertS)
lli = 0
vc = ''
lineCount +=1
if lineCount ==1 :
continue
for ls in ll :
ls = ls.replace(enclose,'').replace('\n','').replace('\r','')
v = ''
if ls is None or ls == '' :
v = ' null '
else:
if lli in dateList:
v = " to_date('"+ ls + "','yyyy-MM-dd HH24:mi:ss') "
elif lli in numList:
v = str(ls)
else:
v = "'{0}'".format(ls)
lli += 1
vc = vc + v + ','
vc = vc[0:-1]
insertS = insertS + vc + ' ) '
try:
# print(tableName, lineCt)
Ocursor.execute(insertS)
except Exception as E:
print(insertS)
print(E)
# raise( E)
Ocon.commit()
print(tableName , lineCt,'rows write completed ' ,datetime.datetime.now())
