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

妖狐艹你老母 2022-02-18 09:34 426阅读 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][https_stackoverflow.com_questions_18054224_python-sqlalchemy-mysql-server-has-gone-away]  
           （2）[https://discorporate.us/jek/talks/SQLAlchemy-EuroPython2010.pdf][https_discorporate.us_jek_talks_SQLAlchemy-EuroPython2010.pdf]  
           （3）[https://docs.sqlalchemy.org/en/13/core/event.html][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()

[https_stackoverflow.com_questions_18054224_python-sqlalchemy-mysql-server-has-gone-away]: https://stackoverflow.com/questions/18054224/python-sqlalchemy-mysql-server-has-gone-away
[https_discorporate.us_jek_talks_SQLAlchemy-EuroPython2010.pdf]: https://discorporate.us/jek/talks/SQLAlchemy-EuroPython2010.pdf
[https_docs.sqlalchemy.org_en_13_core_event.html]: https://docs.sqlalchemy.org/en/13/core/event.html