批量删除MySQL数据

 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

import mysql.connector
import time
import notify  # 通知
from datetime import date, timedelta

begin_time = int(time.time())

before_serven_day = (date.today() - timedelta(8)).strftime('%Y-%m-%d')

sql = '''delete from `schema`.`table` where trigger_time < '{} 00:00:00' limit 50;'''.format(before_serven_day)

cnx = mysql.connector.connect(user='', database='', password='',
                              host='', port=3306)
cursor = cnx.cursor()
count = 0

while True:
    print(sql)
    cursor.execute(sql)
    cnx.commit()

    if cursor.rowcount == 0 or count > 130000:
        print(cursor.rowcount)
        cursor.close()
        cnx.close()
        exe_time = int(time.time()) - begin_time
        notify.notify(count, exe_time, sql, 'token')
        break
    else:
        time.sleep(1)
        count += cursor.rowcount
        print(count)