方式一:
import pymysql
'''
【数据清理】执行时间超过当前24小时,设置执行状态为失败
流程执行记录表-auto_workflow_exec_records
'''
# 连接数据库
connection = pymysql.connect(host="127.0.0.1", user="root", password="123456",
database="merak", port=3306, charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
# 更新
def update():
try:
'''with语法:
会依次执行当前connection类 __enter__(self)、connect(self,...)、
__exit__(self, *exc_info),而__exit__方法包括self.close()方法
'''
with connection:
with connection.cursor() as cursor:
sql = """
update auto_workflow_exec_records set execute_state = 0
where `execute_state` = 2
and HOUR(TIMEDIFF(NOW(),`create_time`)) > 24
ORDER BY `create_time` desc;
"""
# 执行创建sql语句
cursor.execute(sql)
# 提交数据
connection.commit()
'''with语法会关闭connection,或再执行connection.close(),会报:
pymysql.err.Error: Already closed
'''
#connection.close()
except pymysql.err.MySQLError as _error:
print(_error)
raise _error
if __name__ == "__main__":
update()
方式二:
import time
import pymysql
'''
【数据清理】执行时间超过当前24小时,设置执行状态为失败
流程执行记录表-auto_workflow_exec_records
'''
# 连接数据库
def get_conn():
_connection = pymysql.connect(host="127.0.0.1", user="root", password="1q2w3e4r",
database="merak", port=3306, charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
_cursor = _connection.cursor()
return _connection, _cursor
# 更新
def update(connection, cursor):
try:
sql = """
update auto_workflow_exec_records set execute_state = 2
where `execute_state` = 0 and HOUR(TIMEDIFF(NOW(),`create_time`)) > 24
ORDER BY `create_time` desc;
"""
# 执行创建sql语句
cursor.execute(sql)
# 提交数据
connection.commit()
except pymysql.err.MySQLError as _error:
print(f'_error:{_error}')
raise _error
def close_conn(connection, cursor):
if cursor:
cursor.close()
if connection:
connection.close()
if __name__ == "__main__":
connection, cursor = get_conn()
try:
update(connection, cursor)
except pymysql.err.MySQLError:
print('catch MySQLError')
close_conn(connection, cursor)