python导出oracle数据_基于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

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