oracle sqlload python程序

#数据放在data目录下，列和数据库对应即可

import os
import time

def load_data(file_path, voice_srcFilename):
voice_srcfile = file_path+'data/' + voice_srcFilename
content_list = [
'load data\n',
'CHARACTERSET AL32UTF8\n',
'infile \'%s\'\n'%(voice_srcfile),
'append into table sss_t3\n',
'fields terminated by \'\x01\'\n',
'TRAILING NULLCOLS\n',
'(KEY_ID char(2000) "strim(:KEY_ID)",SIGNIN_ID char(2000) "strim(:SIGNIN_ID)",LOGIN_ID char(2000) "strim(:LOGIN_ID)",SIGNIN_TIME char(2000) "strim(:SIGNIN_TIME)",SIGNOUT_TIME char(2000) "strim(:SIGNOUT_TIME)",USER_CODE char(2000) "strim(:USER_CODE)",USER_NAME char(2000) "strim(:USER_NAME)",AGENT_CODE char(2000) "strim(:AGENT_CODE)",COMPUTER_IP char(2000) "strim(:COMPUTER_IP)",EXTENSION char(2000) "strim(:EXTENSION)",QUEUE_ID char(2000) "strim(:QUEUE_ID)",QUEUE_NAME char(1000000),CHANGED_QUEUE_ID char(2000) "strim(:CHANGED_QUEUE_ID)",CHANGED_QUEUE_NAME char(2000) "strim(:CHANGED_QUEUE_NAME)",CREATE_USER char(2000) "strim(:CREATE_USER)",CREATE_NAME char(2000) "strim(:CREATE_NAME)",CREATE_TIME char(2000) "strim(:CREATE_TIME)",UPDATE_USER char(2000) "strim(:UPDATE_USER)",UPDATE_NAME char(2000) "strim(:UPDATE_NAME)",UPDATE_TIME char(2000) "strim(:UPDATE_TIME)",CODE_LOGIN_STATUS char(2000) "strim(:CODE_LOGIN_STATUS)",ORG_ID char(2000) "strim(:ORG_ID)",PRO_ID char(2000) "strim(:PRO_ID)",TENANT_ID char(2000) "strim(:TENANT_ID)",MONTH_ID char(2000) "strim(:MONTH_ID)",DAY_ID char(2000) "strim(:DAY_ID)")']
with open("%ssqlldr_t1_d.ctl"%(file_path), "w") as sqlldr_ctl:
sqlldr_ctl.writelines(content_list)

sqlldr_cmd = '/usr/lib/oracle/12.1/client64/bin/sqlldr \'csp/"CSP!123"@kefu_39\' control=%ssqlldr_t1_d.ctl rows=20000 log=%ssqlldr_t1_d.log bad=%ssqlldr_t1_d.bad direct=true streamsize=1048576'%(file_path, file_path, file_path)

flag_load = os.system(sqlldr_cmd)
if flag_load == 0:
print('数据进库完成')
else:
print('数据进库失败')

file_path = '/home/etl/script/kafka_test/sss_t3/'
print('执行时间 %s'%(time.strftime('%Y-%m-%d %H%M%S',time.localtime())))

for item in os.listdir(file_path+'data/'):
if len(item) > 0:
voice_srcFilename = item
print('file_name:%s'%voice_srcFilename)
load_data(file_path, voice_srcFilename)