python 调用sqldr,Use Python script to comply sqlldr’s tool auto

The CSV file that use python script was output.The CSV file was loaded the oracle database.

we will use the sqlldr tools comply the function.

NOTE:

1.csv file format ,for example:xxxx-yyyymmdd.csv

2.os env:linux,don't fit the windows

3.the python script at the oracle database server machine.

4.configure the python on the oracle database server machine

# -*- coding: utf-8 -*-

"""

Created on Thu Nov 24 17:04:34 2016

@author: trsenzhang

"""

import os

import sys

import time

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

#python script directory

CUR_PATH = os.path.dirname(os.path.realpath(__file__))

def sed(type,filename="",s_str="",d_str=""):

'''

a behind the "s_str" line append "d_str". if "s_str" is null, then append in the end of the file;

i in front of "s_str" line append "d_str". if "s_str" is null,then append in the header of the;

d delete the "s_str" line

s replace string of "s_str" to "d_str"

'''

fp = open(filename)

cont = fp.read()

fp.close()

content = cont.split("\n")

content2 = cont.split("\n")

cnt = 0

idx = 0

if type == 'a':

if not s_str:

content.append(d_str)

else:

for i in content2:

if i.find(s_str) != -1:

x = idx + 1 + cnt

content.insert(x,d_str)

cnt += 1

idx += 1

elif type == 'i':

if not s_str:

content.insert(0,d_str)

else:

for i in content2:

if i.find(s_str) != -1:

x = idx + cnt

content.insert(x,d_str)

cnt += 1

idx += 1

elif type == 'd':

for i in content2:

if i.find(s_str) != -1:

idx = content.remove(i)

elif type == 's':

cont=str.replace(cont,s_str,d_str)

content=cont.split("\n")

fp = open(filename, "w")

fp.write("\n".join(content))

fp.close()

def getSqlldir():

ctlfile=CUR_PATH+'/sqlldir/orders.ctl'

csvfile1=CUR_PATH+'/report/Orders-'+str(int(time.strftime("%Y%m%d",time.localtime()))-2)+'.csv'

csvfile2=CUR_PATH+'/report/Orders-'+str(int(time.strftime("%Y%m%d",time.localtime()))-1)+'.csv'

sqllogfile=CUR_PATH+'/sqlldir/log/Orders_sqlldir_'+str(int(time.strftime("%Y%m%d",time.localtime()))-1)+'.log'

badfile=CUR_PATH+'/sqlldir/log/Orders_sqlldir_'+str(int(time.strftime("%Y%m%d",time.localtime()))-1)+'.bad'

#modify the sqlldr controlfile

sed('s',ctlfile,csvfile1,csvfile2)

#load the csv to oracle database.

os.system('/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlldr system/oracle control='+ctlfile+' log='+sqllogfile+' bad='+badfile+' skip=1')

if __name__ == '__main__':

if not os.path.exists('%s/sqlldir' % CUR_PATH):

os.mkdir('%s/sqlldir' % CUR_PATH)

if not os.path.exists('%s/sqlldir/log' % CUR_PATH):

os.mkdir('%s/sqlldir/log' %s CUR_PATH):

getSqlldir()