Python删除oracle数据库中的表，并且删除前备份数据到本地

1.用到cx_Oracle模块，用来连接数据库

  用到游标cursor，用来执行sql语句

2.备份的话就是获取每条数据记录，依次写入备份文件

3.定义了一个配置文件，存放要删的表信息，读取配置文件，遍历删除

---具体代码如下----------------------------------

配置文件：test.xml

<?xml version="1.0" encoding="utf-8"?>
<rmfile>
        <rmdays>3 </rmdays>
        <database>
                <test>end_time</test>
                <testA>end_time</testA>
                <testB>end_time</testB>
        </database>

</rmfile>

py执行文件：

#-*-coding:utf-8 -*-
import cx_Oracle
from time import strftime
from datetime import timedelta,date,datetime
from xml.etree import ElementTree as ET

def readcfg():
tree=ET.parse('test.xml')
dnode=tree.findall('rmdays')
d=dnode[0].text

tables=[]
dbNode=tree.findall('database')
for db in dbNode:
print 'db=',db
for t in db.getchildren():
print 'db=',db,t.text
tables.append(t.tag+'.'+t.text)
for table in tables:
l=table.split('.')
print l[0],l[1]
query(str(l[0]),str(l[1]),str(d))

def query(tableName,dateField,d):#表名，日期字段，删除天数参数
#建立连接
conn=cx_Oracle.connect('scott/Oracle2018@localhost:1521/orcl')
#建立光标
cursor=conn.cursor()
#查出语句
queryTable='select * from '+tableName +'  where '+dateField+'<sysdate-'+d+'  order by   '+dateField
print queryTable
#执行查询
cursor.execute( queryTable)
#遍历每一条数据
newName=tableName+date.today().strftime('%Y-%m-%d')
tableFile='/home/oracle/'+newName+'.dat'
outFile=open(tableFile,'w')

for row_data in cursor:
print row_data
outFile.write(str(row_data)+'\n')
outFile.close()

#delete
delsql='delete from  '+tableName +'  where '+dateField+'<sysdate-10'
print delsql
cursor=conn.cursor()
cursor.execute(delsql)
conn.commit()
conn.close()

if __name__=='__main__':
readcfg()