python连接池interfaceerror_解决pymysql.err.InterfaceError: (0, '')报错的办法

发现问题

最近使用 Flask+MySQL 写了些简单的接口，部署到Linux之后，发现过了一段时间，再次访问接口就不能用了，报错如下：

Traceback (most recent call last):

File "/root/python36/lib/python3.6/site-packages/flask/app.py", line 2328, in __call__

return self.wsgi_app(environ, start_response)

File "/root/python36/lib/python3.6/site-packages/flask/app.py", line 2314, in wsgi_app

response = self.handle_exception(e)

File "/root/python36/lib/python3.6/site-packages/flask/app.py", line 1760, in handle_exception

reraise(exc_type, exc_value, tb)

File "/root/python36/lib/python3.6/site-packages/flask/_compat.py", line 36, in reraise

raise value

File "/root/python36/lib/python3.6/site-packages/flask/app.py", line 2311, in wsgi_app

response = self.full_dispatch_request()

File "/root/python36/lib/python3.6/site-packages/flask/app.py", line 1834, in full_dispatch_request

rv = self.handle_user_exception(e)

File "/root/python36/lib/python3.6/site-packages/flask/app.py", line 1737, in handle_user_exception

reraise(exc_type, exc_value, tb)

File "/root/python36/lib/python3.6/site-packages/flask/_compat.py", line 36, in reraise

raise value

File "/root/python36/lib/python3.6/site-packages/flask/app.py", line 1832, in full_dispatch_request

rv = self.dispatch_request()

File "/root/python36/lib/python3.6/site-packages/flask/app.py", line 1818, in dispatch_request

return self.view_functions[rule.endpoint](**req.view_args)

File "/root/flaskDemo/api/user.py", line 18, in get_all_users

data = db.select_db(sql)

File "/root/flaskDemo/common/mysql_operate.py", line 27, in select_db

self.cur.execute(sql)

File "/root/python36/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute

result = self._query(query)

File "/root/python36/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query

conn.query(q)

File "/root/python36/lib/python3.6/site-packages/pymysql/connections.py", line 516, in query

self._execute_command(COMMAND.COM_QUERY, sql)

File "/root/python36/lib/python3.6/site-packages/pymysql/connections.py", line 750, in _execute_command

raise err.InterfaceError("(0, '')")

pymysql.err.InterfaceError: (0, '')

在网上查了下，是因为这个数据库的连接建立太久了，会自动断开，这个时候我们需要重新建立连接，否则访问接口就会出现异常报错了。

解决办法

在知道了问题原因后，我们就需要进行处理了。上面说到MySQL连接时间太长了会断开连接，那么我们可以在每次操作SQL之前对连接进行检查，如果发现连接已经断开，则进行重连。

在 pymysql 创建的数据库连接对象下，有这么一个方法：ping()，直接进去查看源码。

def ping(self, reconnect=True):

"""

Check if the server is alive.

:param reconnect: If the connection is closed, reconnect.

:raise Error: If the connection is closed and reconnect=False.

"""

if self._sock is None:

if reconnect:

self.connect()

reconnect = False

else:

raise err.Error("Already closed")

try:

self._execute_command(COMMAND.COM_PING, "")

self._read_ok_packet()

except Exception:

if reconnect:

self.connect()

self.ping(False)

else:

raise

在源码中，如果使用了该方法 ping(reconnect=True) ，那么可以在每次连接之前，会检查当前连接是否已关闭，如果连接关闭则会重新进行连接，于是我们可以将其用于处理目前报错的问题，改动后的代码如下：

def select_db(self, sql):

"""查询"""

# 检查连接是否断开，如果断开就进行重连

self.conn.ping(reconnect=True)

# 使用 execute() 执行sql

self.cur.execute(sql)

# 使用 fetchall() 获取查询结果

data = self.cur.fetchall()

return data

def execute_db(self, sql):

"""更新/新增/删除"""

try:

# 检查连接是否断开，如果断开就进行重连

self.conn.ping(reconnect=True)

# 使用 execute() 执行sql

self.cur.execute(sql)

# 提交事务

self.conn.commit()

except Exception as e:

print("操作出现错误：{}".format(e))

# 回滚所有更改

self.conn.rollback()

上面的处理，其实只是在 select_db() 和 execute_db() 中使用 execute() 方法执行sql前，加了一行代码：self.conn.ping(reconnect=True) ，而 self.conn 则是代码里创建的 pymysql 数据库连接对象，我按上述方法处理之后问题得以解决。