Python 3.6.2 Script for Generating Directories and Files Used by Oracle SQL*Loader @ DanBrother的部落格

Python 3.6.2 Script for Generating Directories and Files Used by Oracle SQL*Loader

E:\pytest>type GEN_FILES.py
###############################################################################
# Python 3.6.2 Script for Generating Directories and Files Used by Oracle SQL*Loader
# Syntax: GEN_FILES.py {LOTTERY_TYPE} {DATE}
# e.g. python GEN_FILES.py BIG_Lottery 20170908
# Programmed by DanBrother
###############################################################################

import os,sys
lottery=sys.argv[1]
date=sys.argv[2]
curr_dir=os.getcwd() # get current working directory
working_dir=f'{curr_dir}\{lottery}\{date}'
new_dir=f'{working_dir}\SN'

# make new directories (new_dir)
if not os.path.exists(new_dir):
os.makedirs(new_dir)

#open and write to load.bat file
f = open(f'{working_dir}\load.bat',"w")
load_content = 'sqlldr testuser@TESTDB errors=0 control='+working_dir+'\\'+lottery+'_SN.ctl log='+working_dir+'\\'+lottery+'_SN.log bad='+working_dir+'\\'+lottery+'_SN.bad'
f.write(load_content)
f.close()

#open and write to the control file (.ctl)
f = open(f'{working_dir}\{lottery}_SN.ctl',"w")
load_content=f'''Load Data
infile '{working_dir}\{lottery}_SN.txt'
APPEND into table lottery_sn
fields terminated by \',\'
TRAILING NULLCOLS(LOTTERY_TYPE,SN,PUBLISH)'''
f.write(load_content)
f.close()

#open and write to the sn text file (.txt)
f = open(f'{working_dir}\{lottery}_SN.txt',"w")
load_content=f'''{lottery},TEST-1000000001,n
{lottery},TEST-1000000002,n
{lottery},TEST-1000000003,n
{lottery},TEST-1000000004,n
{lottery},TEST-1000000005,n
{lottery},TEST-1000000006,n
{lottery},TEST-1000000007,n
{lottery},TEST-1000000008,n
{lottery},TEST-1000000009,n
{lottery},TEST-1000000010,n'''
f.write(load_content)
f.close()

E:\pytest>type GEN_FILES.bat
python GEN_FILES.py BIG_Lottery 20170908

Double click on GEN_FILES.bat file to generate directories and files as shown bellow in red color:
>>
-pytest
-BIG_Lottery
   -20170908
               SN
                       BIG_Lottery_SN.ctl
                           BIG_Lottery_SN.txt
                           load.bat
GEN_FILES.bat
GEN_FILES.py

--@testdb
create table LOTTERY_SN
(LOTTERY_TYPE VARCHAR2(20) not null,
SN VARCHAR2(20) not null,
PUBLISH char(1) not null,
CONSTRAINT PK_LOTTERY_SN PRIMARY KEY(SN)
);

To load the data, double click on load.bat file under 20170908 directory. (Enter the password for the designate user when prompt.)

SQL> select * from LOTTERY_SN;

LOTTERY_TYPE SN PUBLISH
-------------------- -------------------- -------
BIG_Lottery TEST-1000000001 n
BIG_Lottery TEST-1000000002 n
BIG_Lottery TEST-1000000003 n
BIG_Lottery TEST-1000000004 n
BIG_Lottery TEST-1000000005 n
BIG_Lottery TEST-1000000006 n
BIG_Lottery TEST-1000000007 n
BIG_Lottery TEST-1000000008 n
BIG_Lottery TEST-1000000009 n
BIG_Lottery TEST-1000000010 n

-- drop table test table
drop table LOTTERY_SN purge;