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)

　　

转载于:https://www.cnblogs.com/52shaidan/p/10106508.html