admin管理员组

文章数量:1565790

问题

当sqlalchemy的engine等待一定时间后，再次执行sql，会报Lost connection to MySQL server during query的错误。

准备环境

python3 -m venv venv
source venv/bin/activate
pip install sqlalchemy pymysql

查看本机wait_timeout

mysql> show global variables like 'wait_timeout%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 120   |
+---------------+-------+
1 row in set, 1 warning (0.05 sec)

复现错误

import time

from sqlalchemy.engine import create_engine


url = 'mysql+pymysql://root:root@127.0.0.1:3306/test'
engine = create_engine(url)

query = 'SELECT now();'

while True:
    print('Query-01', engine.execute(query).fetchall())
    engine.execute('SET wait_timeout=3')  # set session variable `wait_timeout`
    time.sleep(5)
    print('Query-02', engine.execute(query).fetchall())

pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query 
([WinError 10053] 你的主机中的软件中止了一个已建立的连接。)')

解决方案

设置pool_recycle比wait_timeout小。

import time

from sqlalchemy.engine import create_engine


url = 'mysql+pymysql://root:root@127.0.0.1:3306/test'
engine = create_engine(url, pool_recycle=1)

query = 'SELECT now();'

while True:
    print('Query-01', engine.execute(query).fetchall())
    engine.execute('SET wait_timeout=3')  # set session variable `wait_timeout`
    time.sleep(5)
    print('Query-02', engine.execute(query).fetchall())

Query-01 [(datetime.datetime(2023, 1, 1, 16, 44, 36),)]
Query-02 [(datetime.datetime(2023, 1, 1, 16, 44, 41),)]
Query-01 [(datetime.datetime(2023, 1, 1, 16, 44, 41),)]
Query-02 [(datetime.datetime(2023, 1, 1, 16, 44, 46),)]
Query-01 [(datetime.datetime(2023, 1, 1, 16, 44, 46),)]
Query-02 [(datetime.datetime(2023, 1, 1, 16, 44, 51),)]
Query-01 [(datetime.datetime(2023, 1, 1, 16, 44, 51),)]
Query-02 [(datetime.datetime(2023, 1, 1, 16, 44, 56),)]
...

参考

http://www.ilian.io/sqlalchemy-and-lost-connection-mysql-server-during-query/
https://madmalls/blog/post/sqlalchemy-lost-connection-to-mysql-server-during-query/

本文标签： LostSQLALCHEMYConnectionQueryServer

版权声明：本文标题：sqlalchemy (2013, ‘Lost connection to MySQL server during query 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://www.elefans.com/xitong/1725955258a1050554.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。