python 删除大表数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142

#!/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)