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)