#!/usr/bin/env python# encoding: utf-8#@author: 东哥加油!#@file: del_tb_bigtable_statistic.py#@time: 2018/11/21 15:39import pymysqlimport datetimeimport mathimport 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#获取该条件所有的ciddef 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)