pymysql.err.InterfaceError: (0, ”)

最近新上线了一个报表展示功能,供内部使用。使用Python + Flask,数据库使用mysql。业务也很简单,从mysql数据库中拿出数据并展示。上线之初,一切正常。在过了两个小时之后,刷新页面,报错提示pymysql.err.InterfaceError: (0, ”)

  • 报错日志
2019-09-23 14:55:44,944 inner_report_controller.py[line:25] [get_adjust_daily_report] INFO adjust report info:{'cur_hour_start': '2019-09-23 14:00:00', 'yesterday_start': '2019-09-22 00:00:00', 'yesterday_end': '2019-09-22 23:59:59'}
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 2463, in __call__
    return self.wsgi_app(environ, start_response)
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 2449, in wsgi_app
    response = self.handle_exception(e)
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1866, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/work/ai_ads_task/edison/application/controller/inner_report_controller.py", line 26, in get_adjust_daily_report
    adjust_report_data = adjust_report.generate_daily_report()
  File "/usr/local/work/ai_ads_task/edison/application/service/inner_report/adjust_price_report.py", line 62, in generate_daily_report
    data_result = self.get_daily_data()
  File "/usr/local/work/ai_ads_task/edison/application/service/inner_report/adjust_price_report.py", line 54, in get_daily_data
    app_db_cursor.execute(sql)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 515, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 745, in _execute_command
    raise err.InterfaceError("(0, '')")
pymysql.err.InterfaceError: (0, '')
  • 经过排查,报错的原因是因为领导将mysql数据库连接写死了。所以当到达mysql设置的超时时间后,连接已经断开
# 此代码属于公共方法,只执行一次
app_db_conn = pymysql.connect("127.0.0.1", "root", "1103", "xxxx", charset='utf8',
                              cursorclass=pymysql.cursors.DictCursor)
app_db_cursor = app_db_conn.cursor()
def get_daily_data(self):
    """获取天级执行日志"""
    logger.info('get_daily_data: %s, %s' % (self.yesterday_start, self.yesterday_end))
    sql = "select * from adv_task_log " 
          "where exe_time between '%s' and '%s' " % (self.yesterday_start, self.yesterday_end)

    result = app_db_cursor.fetchall()

当程序执行到这里时,连接已经断开。所以出现错误

解决办法:

  • 涉及到MySQL数据库操作最好使用数据库连接池,但在这里为了不改动太多代码且对性能没有太高的要求。所以只需要在执行SQL语句前加一个判断是否超时的代码,如果超时,自动重连即可。
  • 改动后代码
def get_daily_data(self):
    """获取天级执行日志"""
    logger.info('get_daily_data: %s, %s' % (self.yesterday_start, self.yesterday_end))
    sql = "select * from adv_task_log " 
          "where exe_time between '%s' and '%s' " % (self.yesterday_start, self.yesterday_end)
    try:
        db_cursor = app_db_conn.cursor()
        db_cursor.execute(sql)
    except:
        app_db_conn.ping()
        db_cursor = app_db_conn.cursor()
        db_cursor.execute(sql)

文章来源于互联网,如有雷同请联系站长删除:MySQL数据库报错pymysql.err.InterfaceError: (0, ”)

发表评论