需求场景：
有一业务数据库，使用mysql 5.5版本，每天会写入大量数据，需要不定期将多表中“指定时期前“的数据进行删除，在sql server中很容易实现，写几个while循环就搞定，虽然mysql中也存在类似功能，怎奈自己不精通，于是采用python来实现
话不多少，上脚本：
# coding: utf-8
import mysqldb
import time
# delete config
delete_datetime = ‘2016-08-31 23:59:59’
delete_rows = 10000
exec_detail_file = ‘exec_detail.txt’
sleep_second_per_batch = 0.5
datetime_format = ‘%y-%m-%d %x’
# mysql connection config
default_mysql_host = ‘localhost’
default_mysql_port = 3358
default_mysql_user = “root”
default_mysql_password = ‘roo@01239876’
default_mysql_charset = “utf8”
default_mysql_connect_timeout = 120
default_database_name = ‘testdb001’
def get_time_string(dt_time):
“””
获取指定格式的时间字符串
:param dt_time: 要转换成字符串的时间
:return: 返回指定格式的字符串
“””
global datetime_format
return time.strftime(datetime_format, dt_time)
def print_info(message):
“””
将message输出到控制台，并将message写入到日志文件
:param message: 要输出的字符串
:return: 无返回
“””
print(message)
global exec_detail_file
new_message = get_time_string(time.localtime()) + chr(13) + str(message)
write_file(exec_detail_file, new_message)
def write_file(file_path, message):
“””
将传入的message追加写入到file_path指定的文件中
请先创建文件所在的目录
:param file_path: 要写入的文件路径
:param message: 要写入的信息
:return:
“””
file_handle = open(file_path, ‘a’)
file_handle.writelines(message)
# 追加一个换行以方便浏览
file_handle.writelines(chr(13))
file_handle.close()
def get_mysql_connection():
“””
根据默认配置返回数据库连接
:return: 数据库连接
“””
conn = mysqldb.connect(
host=default_mysql_host,
port=default_mysql_port,
user=default_mysql_user,
passwd=default_mysql_password,
connect_timeout=default_mysql_connect_timeout,
charset=default_mysql_charset,
db=default_database_name
)
return conn
def mysql_exec(sql_script, sql_param=none):
“””
执行传入的脚本，返回影响行数
:param sql_script:
:param sql_param:
:return: 脚本最后一条语句执行影响行数
“””
try:
conn = get_mysql_connection()
print_info(“在服务器{0}上执行脚本:{1}”.format(
conn.get_host_info(), sql_script))
cursor = conn.cursor()
if sql_param is not none:
cursor.execute(sql_script, sql_param)
row_count = cursor.rowcount
else:
cursor.execute(sql_script)
row_count = cursor.rowcount
conn.commit()
cursor.close()
conn.close()
except exception, e:
print_info(“execute exception:” + str(e))
row_count = 0
return row_count
def mysql_query(sql_script, sql_param=none):
“””
执行传入的sql脚本，并返回查询结果
:param sql_script:
:param sql_param:
:return: 返回sql查询结果
“””
try:
conn = get_mysql_connection()
print_info(“在服务器{0}上执行脚本:{1}”.format(
conn.get_host_info(), sql_script))
cursor = conn.cursor()
if sql_param != ”:
cursor.execute(sql_script, sql_param)
else:
cursor.execute(sql_script)
exec_result = cursor.fetchall()
cursor.close()
conn.close()
return exec_result
except exception, e:
print_info(“execute exception:” + str(e))
def get_id_range(table_name):
“””
按照传入的表获取要删除数据最大id、最小id、删除总行数
:param table_name: 要删除的表
:return: 返回要删除数据最大id、最小id、删除总行数
“””
global delete_datetime
sql_script = “””
select
max(id) as max_id,
min(id) as min_id,
count(1) as total_count
from {0}
where create_time