欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

基于python连接oracle导并出数据文件

程序员文章站 2023-10-31 15:42:52
python连接oracle,感觉table_list文件内的表名,来卸载数据文件主脚本:import osimport loggingimport sysimport configparserimp...

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

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