首页 > 数据库技术 > 详细

SQLAlchemy 在查询期间丢失与MySQL服务器的连接

时间:2019-05-14 21:13:18      阅读:324      评论:0      收藏:0      [点我收藏+]

遇到问题

pymysql.err.OperationalError: (2013, ‘Lost connection to MySQL server during query‘)

建立的 pymysql 的连接

# mysql connect
engine = create_engine("mysql+{driver}://{username}:{password}@{server}/{database}?charset={charset}"
                       .format(driver=MYSQL_DRIVER,
                               username=MYSQL_USERNAME,
                               password=MYSQL_PASSWORD,
                               server=MYSQL_SERVER,
                               database=DB_NAME,
                               charset=DB_CHARSET),
                       pool_size=100,
                       max_overflow=100,
                       # pool_recycle=7200,
                       pool_recycle=10,
                       echo=False)
engine.execute("SET NAMES {charset};".format(charset=DB_CHARSET))
MapBase = declarative_base(bind=engine)
DBSession = sessionmaker(bind=engine)

 

运行具体报错

Connected to pydev debugger (build 191.6183.50)
init mysql_db success
ai access running...
{recordId: e2d432da51214c54aa2bb4d43b513835, carImg1Data: /9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAI
{recordId: e2d432da51214c54aa2bb4d43b513835, carImg1Data: /9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAI
exception occurs when get data! Traceback (most recent call last):
  File "/home/ubuntu/.virtualenvs/access_py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/home/ubuntu/.virtualenvs/access_py3/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/connections.py", line 657, in _read_packet
    packet_header = self._read_bytes(4)
  File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/connections.py", line 707, in _read_bytes
    CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, Lost connection to MySQL server during query)

 

分析

配置了 pool_recycle = 10,导致池在给定的秒数过后再循环连接,连接池 100 连接不够用了,导致上述错误

 

解决方案

配置 pool_recycle = 1,或者自动回收(设置为 True 是可以的,貌似与设置为 1 一样),而默认等于 -1,表示不回收

 

实践例子

设置 pool_recycle=3,而我们程序执行了两次,那么就会触发异常

import time

from sqlalchemy.engine import create_engine

url = mysql+pymysql://user:pass@127.0.0.1:3306/db
engine = create_engine(url, pool_recycle=3).connect()

query = SELECT NOW();

while True:
    print(Q1, engine.execute(query).fetchall())
    # time.sleep(2)
    print(Q2, engine.execute(query).fetchall())

 

好了,更多 create_engine() 函数的连接参数说明,可以去官网查看,以便更好的理解


 

SQLAlchemy 在查询期间丢失与MySQL服务器的连接

原文:https://www.cnblogs.com/kaichenkai/p/10864576.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!