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()