pymysql长时间连接自动断开解决方案

公司有一个demo的后端是我用python flask框架写的，出现过几次后端崩了的情况，一开始我以为是参数的问题，写了个异常抛出，没有去查看日志。后面继续崩，我发现事情并不简单，然后就留意了一下日志，发现报错是pymysql.err.InterfaceError。好家伙，就是数据库连接的问题啊，后面就百度了一下，发现是数据库长时间连接会自动断开，问题找到了，接下来就是解决问题了。

coon.ping()

报错详情

117.171.190.84 - - [22/May/ 23:02:01] "[35m[1mPOST /vs HTTP/1.1[0m" 500 -Traceback (most recent call last):File "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 2464, in __call__return self.wsgi_app(environ, start_response)File "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 2450, in wsgi_appresponse = self.handle_exception(e)File "/home/admin/python3/lib/python3.6/site-packages/flask_cors/extension.py", line 165, in wrapped_functionreturn cors_after_request(app.make_response(f(*args, **kwargs)))File "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 1867, in handle_exceptionreraise(exc_type, exc_value, tb)File "/home/admin/python3/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraiseraise valueFile "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 2447, in wsgi_appresponse = self.full_dispatch_request()File "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 1952, in full_dispatch_requestrv = self.handle_user_exception(e)File "/home/admin/python3/lib/python3.6/site-packages/flask_cors/extension.py", line 165, in wrapped_functionreturn cors_after_request(app.make_response(f(*args, **kwargs)))File "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 1821, in handle_user_exceptionreraise(exc_type, exc_value, tb)File "/home/admin/python3/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraiseraise valueFile "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 1950, in full_dispatch_requestrv = self.dispatch_request()File "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 1936, in dispatch_requestreturn self.view_functions[rule.endpoint](**req.view_args)File "/home/admin/demo.py", line 72, in json_vscursor.execute("select * from t_vs")File "/home/admin/python3/lib/python3.6/site-packages/pymysql/cursors.py", line 148, in executeresult = self._query(query)File "/home/admin/python3/lib/python3.6/site-packages/pymysql/cursors.py", line 310, in _queryconn.query(q)File "/home/admin/python3/lib/python3.6/site-packages/pymysql/connections.py", line 547, in queryself._execute_command(_QUERY, sql)File "/home/admin/python3/lib/python3.6/site-packages/pymysql/connections.py", line 793, in _execute_commandraise err.InterfaceError(0, "")pymysql.err.InterfaceError: (0, '')

问题找到了，该解决了

def json_vs():# 访问量接口global cooncoon.ping()cursor = coon.cursor()cursor.execute("select * from t_vs")data = cursor.fetchall()cursor = coon.cursor()sql = "update t_vs set vs=%s where id=1"cursor.execute(sql,data[0][1]+1)data1 = cursor.fetchall()return jsonify(data)

coon = pymysql.connect(host) ## host是你连接数据库的语句 coon 需要替换成你定义的变量coon.ping() # 在执行sql语句之前加上这个就可以了

这个只是临时解决一下，确保项目能够正常运行。这种方式消耗性能，可以写一个try抛出异常，当有问题的时候再执行coon.ping 或者自己写一个连接池，连接池相对这个方式而已是不会有这个消耗性能的，这个只是临时解决的方案。后面回更新连接池的代码。