python sqlalchemy默认2小时后出现MySQL Server gone away问题

最新推荐文章于 2024-06-17 17:12:17 发布

sunct

最新推荐文章于 2024-06-17 17:12:17 发布

阅读量818

点赞数 1

分类专栏： Python 文章标签： sqlalchemy python

本文链接：https://blog.csdn.net/sunct/article/details/89350356

版权

Python 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

python sqlalchemy在2小时后会报以下错：

OperationalError('(pymysql.err.OperationalError) (2006, "MySQLserver has gone away (BrokenPipeError(32, \'Broken pipe\'))")',)

原因是，sqlalchemy数据库连接的默认2小时超时断开，导致MySQL Server gone away。

参考：（1）https://stackoverflow.com/questions/18054224/python-sqlalchemy-mysql-server-has-gone-away
（2）https://discorporate.us/jek/talks/SQLAlchemy-EuroPython2010.pdf
（3）https://docs.sqlalchemy.org/en/13/core/event.html

以下修改后的代码：

from sqlalchemy import Column, String, Integer, DateTime, BLOB, create_engine, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.exc import DisconnectionError


def checkout_listener(dbapi_con, con_record, con_proxy):
    '''
    如果数据库链接超时，则重新连接，因为一般sqlalchemy默认2小时，就会断开
    参考：https://stackoverflow.com/questions/18054224/python-sqlalchemy-mysql-server-has-gone-away
    https://discorporate.us/jek/talks/SQLAlchemy-EuroPython2010.pdf
    :param dbapi_con:
    :param con_record:
    :param con_proxy:
    :return:
    '''
    try:
        try:
            dbapi_con.ping(False)
        except TypeError:
            dbapi_con.ping()
    except dbapi_con.OperationalError as exc:
        if exc.args[0] in (2006, 2013, 2014, 2045, 2055):
            raise DisconnectionError()
        else:
            raise

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8",
                        pool_size=10,
                        pool_recycle=3600,
                        encoding='utf-8')
event.listen(engine, 'checkout', checkout_listener)

Base = declarative_base()