python 使用sqlalchemy报错（pymysql.err.OperationalError）2006 和 2013

目录

(2006, "MySQL server has gone away (ConnectionResetError(104, 'Connection reset by peer'))")

(2013, 'Lost connection to MySQL server during query')

---

(2006, "MySQL server has gone away (ConnectionResetError(104, 'Connection reset by peer'))")

一开始报错

(pymysql.err.OperationalError) (2006, "MySQL server has gone away (ConnectionResetError(104, 'Connection reset by peer'))")

 MySQL默认的wait_timeout时间28800秒，即8小时，超过8小时，MySQL就会放弃连接。可以看一下自己的MySQL设置的时间是多少，运行show variables like '%timeout%';

show variables like '%wait_timeout%';

修改my.cnf中并重启数据库

root@Com01:/opt# vim my.cnf 
[mysqld]
server_id=1
log_bin=mysql-bin
innodb_flush_log_at_trx_commit=1
binlog_format=mixed
lower_case_table_names=1
max_connections=1000
wait_timeout=300
interactive_timeout=500

(2013, 'Lost connection to MySQL server during query')
 

改完之后有出现新的报错

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

解决方案
create_engine中添加参数：pool_pre_ping=True,
每次从池中签出连接时，“pre-ping”功能通常会发出相当于“select 1”的SQL；
如果出现检测为“disconnect”情况的错误，则该连接将立即被回收，并且所有其他比当前时间早的池连接都将失效，以便下次它们处于丢弃后，也会在使用前回收利用。

self.engine = create_engine(database_path, max_overflow=10, pool_recycle=7200, pool_pre_ping=True)

 

相关文档-SQLAlchemy 1.4 Documentation