python 删除大表数据

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