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