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

雨点打透心脏的1/2处 2022-12-31 09:25 78阅读 0赞

更多编程教程请到：[菜鸟教程][Link 1] https://www.piaodoo.com/

友情链接：

[高州阳光论坛][Link 2]https://www.hnthzk.com/

[人人影视][Link 3]http://www.sfkyty.com/

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  
>

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持菜鸟教程www.piaodoo.com。

[Link 1]: https://www.piaodoo.com/
[Link 2]: https://www.hnthzk.com/
[Link 3]: http://www.sfkyty.com/