基于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

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。