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

最近新上線了一個(gè)報(bào)表展示功能，供內(nèi)部使用。使用Python + Flask，數(shù)據(jù)庫(kù)使用mysql。業(yè)務(wù)也很簡(jiǎn)單，從mysql數(shù)據(jù)庫(kù)中拿出數(shù)據(jù)并展示。上線之初，一切正常。在過了兩個(gè)小時(shí)之后，刷新頁面，報(bào)錯(cuò)提示pymysql.err.InterfaceError: (0, '')

• 報(bào)錯(cuò)日志

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, '')

• 經(jīng)過排查，報(bào)錯(cuò)的原因是因?yàn)轭I(lǐng)導(dǎo)將mysql數(shù)據(jù)庫(kù)連接寫死了。所以當(dāng)?shù)竭_(dá)mysql設(shè)置的超時(shí)時(shí)間后，連接已經(jīng)斷開

# 此代碼屬于公共方法，只執(zhí)行一次
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):
    """獲取天級(jí)執(zhí)行日志"""
    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()

當(dāng)程序執(zhí)行到這里時(shí)，連接已經(jīng)斷開。所以出現(xiàn)錯(cuò)誤

解決辦法：

• 涉及到MySQL數(shù)據(jù)庫(kù)操作最好使用數(shù)據(jù)庫(kù)連接池，但在這里為了不改動(dòng)太多代碼且對(duì)性能沒有太高的要求。所以只需要在執(zhí)行SQL語句前加一個(gè)判斷是否超時(shí)的代碼，如果超時(shí)，自動(dòng)重連即可。
• 改動(dòng)后代碼

def get_daily_data(self):
    """獲取天級(jí)執(zhí)行日志"""
    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)