目前有个业务需求,需要定期对数据库中的表进行删除,因此写了个小脚本来完成该需求。
#!/usr/bin/python
# -*- coding:UTF-8 -*-
# Author:LG
# 运行 python regular_drop_table.py 2019-03-22
import psycopg2
import datetime
import sys
from db_conn_config import config
op_time = sys.argv[1] # 传输时间变量 代表当前系统时间
#################变量设置################################
new_op_time = datetime.datetime.strptime(op_time, '%Y-%m-%d')
# now = datetime.datetime.now() # 获取系统当前时间
# print(now)
print(new_op_time)
last_7_date = (new_op_time + datetime.timedelta(days=-7)).strftime('%Y%m%d') # 前7天
# print(last_7_date)
last_30_date = (new_op_time + datetime.timedelta(days=-30)).strftime('%Y%m%d') # 前30天
# print(last_30_date)
last_90_date = (new_op_time + datetime.timedelta(days=-90)).strftime('%Y%m%d') # 前90天
# print(last_90_date)
############################## main_program ################
class cron_drop_table(object):
# 操作数据库
def config_db(self):
try:
params = config()
conn = psycopg2.connect(**params)
print('连接数据库成功\n')
cur = conn.cursor()
# 查询数据中所有的表
cur.execute("select tablename from pg_tables where schemaname = 'public'")
results = cur.fetchall()
for result in results:
for i in result:
if i.startswith('ods') and i.endswith(last_30_date): # 删除ods30天表
print(i)
cur = conn.cursor()
cur.execute(' drop table if exists ' + i)
conn.commit()
print('删除 %s 成功' %i)
elif i.startswith('dwd') and i.endswith(last_90_date): # 删除dwd90天表
print(i)
cur = conn.cursor()
cur.execute(' drop table if exists ' + i)
conn.commit()
print('删除 %s 成功'%i)
else:
cur.close()
# 关闭连接
conn.close()
print('断开数据库成功')
except (Exception, psycopg2.DatabaseError) as e:
print(e)
# 调用程序
cron_drop_table().config_db()
网友评论