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

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

coon.ping()

报错详情

117.171.190.84 - - [22/May/2021 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_app
    response = self.handle_exception(e)
  File "/home/admin/python3/lib/python3.6/site-packages/flask_cors/extension.py", line 165, in wrapped_function
    return 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_exception
    reraise(exc_type, exc_value, tb)
  File "/home/admin/python3/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/admin/python3/lib/python3.6/site-packages/flask_cors/extension.py", line 165, in wrapped_function
    return 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_exception
    reraise(exc_type, exc_value, tb)
  File "/home/admin/python3/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/admin/python3/lib/python3.6/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/admin/demo.py", line 72, in json_vs
    cursor.execute("select * from t_vs")
  File "/home/admin/python3/lib/python3.6/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/home/admin/python3/lib/python3.6/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/home/admin/python3/lib/python3.6/site-packages/pymysql/connections.py", line 547, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/home/admin/python3/lib/python3.6/site-packages/pymysql/connections.py", line 793, in _execute_command
    raise err.InterfaceError(0, "")
pymysql.err.InterfaceError: (0, '')

问题找到了，该解决了

def json_vs():
	# 访问量接口
	global coon
	coon.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 或者自己写一个连接池，连接池相对这个方式而已是不会有这个消耗性能的，这个只是临时解决的方案。后面回更新连接池的代码。