python连接oracle导出数据文件

python连接oracle，感觉table_list文件内的表名，来卸载数据文件

主脚本：

import os
import logging
import sys
import configparser
import subprocess
import cx_Oracle

#判断输入参数个数
class param():
    def check_para(self):
        if len(sys.argv) != 1:
             print("请输入正确的参数：yyyymmdd")
             exit(1)
        else:
            print("继续执行")

#根据配置文件获取登录信息
class get_dbini():
    def get_db(self):
        config=configparser.ConfigParser()
        filepath="db.ini"
        if os.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)
        #声明使用全局变量
        global username,password,ip,dbsid
        username=dbinfo[0]
        password=dbinfo[1]
        ip=dbinfo[2]
        dbsid=dbinfo[3]
        loginfo.info(username+password+ip+dbsid)
                    
#导出表数据
class exp_date():
   def exp_table(self):
     with open('table_list','r') as f:
        list = f.readlines()
     for i in list:
       tablename = i.rstrip('\n')
       exportquery='sqluldr2 user='+username+'/'+password+'@'+ip+':1521/'+dbsid+' query="select * from '+tablename+';" head=no file='+tablename+'.dat field=0x03 record=0x030x0a safe=yes'
       loginfo.info("开始导出数据: exportquery= "+exportquery)
       flag= subprocess.check_call(exportquery,shell=True)
       loginfo.info(flag)
        
#打印日志
class log_set():
   def logger_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)
     return logger
     
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@master2 tmp]$ python3 c.py 
继续执行
           0 rows exported at 2019-01-22 17:51:51, size 0 MB.
         output file BONUS.dat closed at 0 rows, size 0 MB.
           0 rows exported at 2019-01-22 17:51:52, size 0 MB.
           4 rows exported at 2019-01-22 17:51:52, size 0 MB.
         output file DEPT.dat closed at 4 rows, size 0 MB.
           0 rows exported at 2019-01-22 17:51:52, size 0 MB.
          12 rows exported at 2019-01-22 17:51:52, size 0 MB.
         output file EMP.dat closed at 12 rows, size 0 MB.
           0 rows exported at 2019-01-22 17:51:52, size 0 MB.
          10 rows exported at 2019-01-22 17:51:52, size 0 MB.
         output file LEAD_TABLE.dat closed at 10 rows, size 0 MB.
           0 rows exported at 2019-01-22 17:51:52, size 0 MB.
           5 rows exported at 2019-01-22 17:51:52, size 0 MB.
         output file SALGRADE.dat closed at 5 rows, size 0 MB.
           0 rows exported at 2019-01-22 17:51:52, size 0 MB.
           5 rows exported at 2019-01-22 17:51:52, size 0 MB.
         output file T1.dat closed at 5 rows, size 0 MB.
           0 rows exported at 2019-01-22 17:51:52, size 0 MB.
          10 rows exported at 2019-01-22 17:51:52, size 0 MB.
         output file TB_USER.dat closed at 10 rows, size 0 MB.
           0 rows exported at 2019-01-22 17:51:52, size 0 MB.
           8 rows exported at 2019-01-22 17:51:52, size 0 MB.
         output file TEST.dat closed at 8 rows, size 0 MB.
           0 rows exported at 2019-01-22 17:51:52, size 0 MB.
           9 rows exported at 2019-01-22 17:51:52, size 0 MB.
         output file XGJ.dat closed at 9 rows, size 0 MB.
           0 rows exported at 2019-01-22 17:51:52, size 0 MB.
           8 rows exported at 2019-01-22 17:51:52, size 0 MB.
         output file XGJ_2.dat closed at 8 rows, size 0 MB.

查看日志：

[oracle@master2 tmp]$ more a.log
2019-01-22 17:51:51,858 -mylogger -INFO - c##scotttiger192.168.1.250orcl
2019-01-22 17:51:51,858 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from BONUS;" head=no file=BON
US.dat field=0x03 record=0x030x0a safe=yes
2019-01-22 17:51:51,949 -mylogger -INFO - 0
2019-01-22 17:51:51,949 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from DEPT;" head=no file=DEPT
.dat field=0x03 record=0x030x0a safe=yes
2019-01-22 17:51:52,038 -mylogger -INFO - 0
2019-01-22 17:51:52,038 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from EMP;" head=no file=EMP.d
at field=0x03 record=0x030x0a safe=yes
2019-01-22 17:51:52,129 -mylogger -INFO - 0
2019-01-22 17:51:52,129 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from LEAD_TABLE;" head=no fil
e=LEAD_TABLE.dat field=0x03 record=0x030x0a safe=yes
2019-01-22 17:51:52,299 -mylogger -INFO - 0
2019-01-22 17:51:52,300 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from SALGRADE;" head=no file=
SALGRADE.dat field=0x03 record=0x030x0a safe=yes
2019-01-22 17:51:52,401 -mylogger -INFO - 0
2019-01-22 17:51:52,402 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from T1;" head=no file=T1.dat
 field=0x03 record=0x030x0a safe=yes
2019-01-22 17:51:52,490 -mylogger -INFO - 0
2019-01-22 17:51:52,490 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from TB_USER;" head=no file=T
B_USER.dat field=0x03 record=0x030x0a safe=yes
2019-01-22 17:51:52,578 -mylogger -INFO - 0
2019-01-22 17:51:52,578 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from TEST;" head=no file=TEST
.dat field=0x03 record=0x030x0a safe=yes
2019-01-22 17:51:52,665 -mylogger -INFO - 0
2019-01-22 17:51:52,665 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from XGJ;" head=no file=XGJ.d
at field=0x03 record=0x030x0a safe=yes
2019-01-22 17:51:52,771 -mylogger -INFO - 0
2019-01-22 17:51:52,771 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from XGJ_2;" head=no file=XGJ
_2.dat field=0x03 record=0x030x0a safe=yes
2019-01-22 17:51:52,856 -mylogger -INFO - 0

完。