Python MySQL连接超时

Question

在使用两个docker容器时遇到问题，写入MySQL DB时一切正常，但MySQL日志中偶尔会出现错误：

2020-09-18 17:03:02 21 [Warning] Aborted connection 21 to db: 'database' user: 'dbuser' host: '172.18.0.5' (Got an error reading communication packets)
2020-09-18 17:05:02 47 [Warning] Aborted connection 47 to db: 'database' user: 'dbuser' host: '172.18.0.5' (Got an error reading communication packets)
2020-09-18 17:08:02 49 [Warning] Aborted connection 49 to db: 'database' user: 'dbuser' host: '172.18.0.5' (Got an error reading communication packets)
2020-09-18 17:08:02 48 [Warning] Aborted connection 48 to db: 'database' user: 'dbuser' host: '172.18.0.5' (Got an error reading communication packets)
2020-09-18 17:08:02 50 [Warning] Aborted connection 50 to db: 'database' user: 'dbuser' host: '172.18.0.5' (Got an error reading communication packets)
2020-09-18 17:10:03 52 [Warning] Aborted connection 52 to db: 'database' user: 'dbuser' host: '172.18.0.5' (Got an error reading communication packets)
2020-09-18 17:10:03 51 [Warning] Aborted connection 51 to db: 'database' user: 'dbuser' host: '172.18.0.5' (Got an error reading communication packets)

我编写到DB的Python代码是：

    mydb = mysql.connector.connect(
    host="mysqlprd",
    user="dbuser",
    passwd="password",
    database="database"
    )

    mycursor = mydb.cursor()

    sql = "INSERT INTO filldbstats VALUES .....
    
        mycursor.executemany(sql,val)

    mydb.commit()
    pass

在python脚本的MySQL部分是否有方法传递超时，或者是否需要在DB端设置超时

谢谢

Answer 1

据我所知，您无法通过python脚本在MySQL中传递超时，但在这里，您可以使用decorator找到一个好的解决方案：

注：部分代码取自here

import multiprocessing.pool
import functools

# Define a decorator for timeout

def timeout(max_timeout):
    """Timeout decorator, parameter in seconds."""
    def timeout_decorator(item):
        """Wrap the original function."""
        @functools.wraps(item)
        def func_wrapper(*args, **kwargs):
            """Closure for function."""
            pool = multiprocessing.pool.ThreadPool(processes=1)
            async_result = pool.apply_async(item, args, kwargs)
            # raises a TimeoutError if execution exceeds max_timeout
            return async_result.get(max_timeout)
        return func_wrapper
    return timeout_decorator

@timeout(3) #kills execution if it takes more than 3 seconds
def make_consult_aux(query):
"""
send your query to db
""" 
return res # return your query operation

def make_consult(query):
    try:
        res = make_consult_aux(query)
        return res
    except TimeoutError:
        return "" #empty answer for control timeout

此外，您可能希望使用pysycopg2或pandas进行带有pandas.read_sql(query,connection_string)的sql查询

psycopg2