基于python连接oracle导并出数据文件
python连接oracle,感觉table_list文件内的表名,来卸载数据文件
主脚本:
importos importlogging importsys importconfigparser importsubprocess importcx_Oracle #判断输入参数个数 classparam(): defcheck_para(self): iflen(sys.argv)!=1: print("请输入正确的参数:yyyymmdd") exit(1) else: print("继续执行") #根据配置文件获取登录信息 classget_dbini(): defget_db(self): config=configparser.ConfigParser() filepath="db.ini" ifos.path.exists(filepath): config.read_file(open(filepath)) dbinfo=[config.get("db_oracle","username"),\ config.get("db_oracle","password"),\ config.get("db_oracle","ip"),\ config.get("db_oracle","dbsid")] else: loginfo.info("没有那个配置文件") sys.exit(4) #声明使用全局变量 globalusername,password,ip,dbsid username=dbinfo[0] password=dbinfo[1] ip=dbinfo[2] dbsid=dbinfo[3] loginfo.info(username+password+ip+dbsid) #导出表数据 classexp_date(): defexp_table(self): withopen('table_list','r')asf: list=f.readlines() foriinlist: tablename=i.rstrip('\n') exportquery='sqluldr2user='+username+'/'+password+'@'+ip+':1521/'+dbsid+'query="select*from'+tablename+';"head=nofile='+tablename+'.datfield=0x03record=0x030x0asafe=yes' loginfo.info("开始导出数据:exportquery="+exportquery) flag=subprocess.check_call(exportquery,shell=True) loginfo.info(flag) #打印日志 classlog_set(): deflogger_set(self): logger=logging.getLogger('mylogger') logger.setLevel(logging.DEBUG) fh=logging.FileHandler('a.log','w') fh.setLevel(logging.INFO) ch=logging.StreamHandler() ch.setLevel(logging.ERROR) formatter=logging.Formatter('%(asctime)s-%(name)s-%(levelname)s-%(message)s') fh.setFormatter(formatter) ch.setFormatter(formatter) logger.addHandler(fh) logger.addHandler(ch) returnlogger if__name__=='__main__': loginfo=log_set().logger_set() param().check_para() get_dbini().get_db() exp_date().exp_table()
DB配置文件内容:
db.ini
[db_oracle]
username=c##scott
password=tiger
ip=192.168.1.250
dbsid=orcl
表名字的配置文件:
table_list
BONUS
DEPT
EMP
LEAD_TABLE
SALGRADE
T1
TB_USER
TEST
XGJ
XGJ_2
运行结果:
[oracle@master2tmp]$python3c.py
继续执行
0rowsexportedat2019-01-2217:51:51,size0MB.
outputfileBONUS.datclosedat0rows,size0MB.
0rowsexportedat2019-01-2217:51:52,size0MB.
4rowsexportedat2019-01-2217:51:52,size0MB.
outputfileDEPT.datclosedat4rows,size0MB.
0rowsexportedat2019-01-2217:51:52,size0MB.
12rowsexportedat2019-01-2217:51:52,size0MB.
outputfileEMP.datclosedat12rows,size0MB.
0rowsexportedat2019-01-2217:51:52,size0MB.
10rowsexportedat2019-01-2217:51:52,size0MB.
outputfileLEAD_TABLE.datclosedat10rows,size0MB.
0rowsexportedat2019-01-2217:51:52,size0MB.
5rowsexportedat2019-01-2217:51:52,size0MB.
outputfileSALGRADE.datclosedat5rows,size0MB.
0rowsexportedat2019-01-2217:51:52,size0MB.
5rowsexportedat2019-01-2217:51:52,size0MB.
outputfileT1.datclosedat5rows,size0MB.
0rowsexportedat2019-01-2217:51:52,size0MB.
10rowsexportedat2019-01-2217:51:52,size0MB.
outputfileTB_USER.datclosedat10rows,size0MB.
0rowsexportedat2019-01-2217:51:52,size0MB.
8rowsexportedat2019-01-2217:51:52,size0MB.
outputfileTEST.datclosedat8rows,size0MB.
0rowsexportedat2019-01-2217:51:52,size0MB.
9rowsexportedat2019-01-2217:51:52,size0MB.
outputfileXGJ.datclosedat9rows,size0MB.
0rowsexportedat2019-01-2217:51:52,size0MB.
8rowsexportedat2019-01-2217:51:52,size0MB.
outputfileXGJ_2.datclosedat8rows,size0MB.
查看日志:
[oracle@master2tmp]$morea.log
2019-01-2217:51:51,858-mylogger-INFO-c##scotttiger192.168.1.250orcl
2019-01-2217:51:51,858-mylogger-INFO-开始导出数据:exportquery=sqluldr2user=c##scott/tiger@192.168.1.250:1521/orclquery="select*fromBONUS;"head=nofile=BON
US.datfield=0x03record=0x030x0asafe=yes
2019-01-2217:51:51,949-mylogger-INFO-0
2019-01-2217:51:51,949-mylogger-INFO-开始导出数据:exportquery=sqluldr2user=c##scott/tiger@192.168.1.250:1521/orclquery="select*fromDEPT;"head=nofile=DEPT
.datfield=0x03record=0x030x0asafe=yes
2019-01-2217:51:52,038-mylogger-INFO-0
2019-01-2217:51:52,038-mylogger-INFO-开始导出数据:exportquery=sqluldr2user=c##scott/tiger@192.168.1.250:1521/orclquery="select*fromEMP;"head=nofile=EMP.d
atfield=0x03record=0x030x0asafe=yes
2019-01-2217:51:52,129-mylogger-INFO-0
2019-01-2217:51:52,129-mylogger-INFO-开始导出数据:exportquery=sqluldr2user=c##scott/tiger@192.168.1.250:1521/orclquery="select*fromLEAD_TABLE;"head=nofil
e=LEAD_TABLE.datfield=0x03record=0x030x0asafe=yes
2019-01-2217:51:52,299-mylogger-INFO-0
2019-01-2217:51:52,300-mylogger-INFO-开始导出数据:exportquery=sqluldr2user=c##scott/tiger@192.168.1.250:1521/orclquery="select*fromSALGRADE;"head=nofile=
SALGRADE.datfield=0x03record=0x030x0asafe=yes
2019-01-2217:51:52,401-mylogger-INFO-0
2019-01-2217:51:52,402-mylogger-INFO-开始导出数据:exportquery=sqluldr2user=c##scott/tiger@192.168.1.250:1521/orclquery="select*fromT1;"head=nofile=T1.dat
field=0x03record=0x030x0asafe=yes
2019-01-2217:51:52,490-mylogger-INFO-0
2019-01-2217:51:52,490-mylogger-INFO-开始导出数据:exportquery=sqluldr2user=c##scott/tiger@192.168.1.250:1521/orclquery="select*fromTB_USER;"head=nofile=T
B_USER.datfield=0x03record=0x030x0asafe=yes
2019-01-2217:51:52,578-mylogger-INFO-0
2019-01-2217:51:52,578-mylogger-INFO-开始导出数据:exportquery=sqluldr2user=c##scott/tiger@192.168.1.250:1521/orclquery="select*fromTEST;"head=nofile=TEST
.datfield=0x03record=0x030x0asafe=yes
2019-01-2217:51:52,665-mylogger-INFO-0
2019-01-2217:51:52,665-mylogger-INFO-开始导出数据:exportquery=sqluldr2user=c##scott/tiger@192.168.1.250:1521/orclquery="select*fromXGJ;"head=nofile=XGJ.d
atfield=0x03record=0x030x0asafe=yes
2019-01-2217:51:52,771-mylogger-INFO-0
2019-01-2217:51:52,771-mylogger-INFO-开始导出数据:exportquery=sqluldr2user=c##scott/tiger@192.168.1.250:1521/orclquery="select*fromXGJ_2;"head=nofile=XGJ
_2.datfield=0x03record=0x030x0asafe=yes
2019-01-2217:51:52,856-mylogger-INFO-0
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。