#!/usr/bin/env python # encoding: utf-8 #@author: 東哥加油! #@file: del_tb_bigtable_statistic.py #@time: 2018/11/21 15:39 import pymysql import datetime import math import time #獲取連線 def get_conn(): conn = None try: conn = pymysql.connect( host="192.168.1.2", port=3306, user="root", passwd="mysqlpassword", charset="utf8", ) except Exception as err: print(err) return conn #查詢語句執行 def get_data(sql): conn = get_conn() cur = conn.cursor() cur.execute(sql) data = cur.fetchall() conn.close() return data #93天前的時間戳 # 2018-07-24 00:00:00 轉成毫秒時間戳 def get_pdate_begin(xday): now_time = datetime.datetime.now() step_time = datetime.timedelta(days=xday) yes_time = now_time - step_time pdate = yes_time.strftime(`%Y%m%d`) print(pdate) return pdate #資料備份,放到tb_bigtable_statistic_hist表中 def data_bak(xday): print("開始時間:",time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())) conn = get_conn() cur = conn.cursor() cidlist = data_zk(xday) if cidlist == 0: print(`當天無資料`) else: for cids in cidlist: try: sql = ```insert into db_order.tb_bigtable_statistic_hist select * from db_order.tb_bigtable_statistic where cid in( %s )``` % cids cur.execute(sql) conn.commit() except: print(`備份失敗!!!`) conn.rollback() conn.close() exit(99) conn.close() print("結束時間:", time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())) #組裝cid成in的條件(....),5000個cid為一組 def data_zk(xday): conn = get_conn() cur = conn.cursor() cid = get_cid(xday) var1 = "-999" i = 0 list = [] if cid.__len__() > 0: for one in cid: var1=var1+","+str(one[0]) i=i+1 if(i==2000): list.append(var1) var1 = "-999" i=0 list.append(var1) return list else: return 0 #獲取該條件所有的cid def get_cid(xday): pdate = get_pdate_begin(xday) sql = ```SELECT cid FROM db_order.tb_bigtable_statistic WHERE pdate = %s limit 20000``` % (pdate) cid = get_data(sql) return cid #刪除資料 def del_data(xday): print("刪除開始時間:", time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())) conn = get_conn() cur = conn.cursor() cidlist = data_zk(xday) if cidlist == 0: print(`當天無資料`) else: for cids in cidlist: try: sql = ```delete from db_order.tb_bigtable_statistic where cid in( %s )``` % cids cur.execute(sql) conn.commit() except: print(`備份失敗!!!`) conn.rollback() conn.close() exit(99) conn.close() print("刪除結束時間:", time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())) def move_data(xday): data_bak(xday) del_data(xday) if __name__ == `__main__`: move_data(93)