您现在的位置：程式師世界 >> 編程語言 > >> 更多編程語言 >> Python

Python | peewee.InterfaceError

編輯：Python

Python | peewee.InterfaceError

1. 簡介

服務器上運行的Python後台，每次到第二天就會出現後台管理賬號無法登錄情況。查看後台日志發現是數據庫方面出現了問題，最先以為是數據庫無法訪問。因為後台有正在運行的任務，會經常訪問數據庫發現並沒有報數據庫方面的錯，只有HTTP請求的線程無法訪問。

(0, '') <class 'peewee.InterfaceError'>, (0, ''), [<FrameSummary file /flask/router/user.py, line 29 in login>, <FrameSummary file /flask/service/user.py, line 19 in login_user>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 6522 in get>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 6970 in get>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 1918 in inner>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 1989 in execute>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 2162 in _execute>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 3190 in execute>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 3174 in execute_sql>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 2950 in __exit__>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 191 in reraise>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 3177 in execute_sql>, <FrameSummary file /usr/local/lib/python3.10/site-packages/pymysql/cursors.py, line 148 in execute>, <FrameSummary file /usr/local/lib/python3.10/site-packages/pymysql/cursors.py, line 310 in _query>, <FrameSummary file /usr/local/lib/python3.10/site-packages/pymysql/connections.py, line 547 in query>, <FrameSummary file /usr/local/lib/python3.10/site-packages/pymysql/connections.py, line 793 in _execute_command>]

2. 原因

peewee會為每個線程分配一條數據庫鏈接，當頁面長時間沒有收到HTTP請求數據操作數據庫時，處理HTTP請求的線程的數據庫連接就會失效，所有會出現頁面無法登錄，而後台運行的任務會經常訪問數據庫，所有連接不會失效。

3. 問題復現

第1步修改數據庫wait_timeout時間

讓連接更快失效，這裡將超時時間設置為3秒。

# 查看數據庫wait_timeout
show global variables like 'wait_timeout';
# 設置數據庫wait_time
set global wait_timeout=3;

第2步執行測試代碼

import time
from peewee import *
db = MySQLDatabase(
'test',
user='root',
password='12345678',
host='127.0.0.1',
port=3306,
)
class User(Model):
username = CharField()
password = CharField()
salt = CharField()
state = SmallIntegerField()
class Meta:
database = db
db.create_tables([User])
query = User.select()
ls = [user.username for user in query]
time.sleep(5)
query = User.select()
ls = [user.username for user in query]

執行結果

Traceback (most recent call last):
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 3177, in execute_sql
cursor.execute(sql, params or ())
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute
result = self._query(query)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query
conn.query(q)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result
result.read()
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read
first_packet = self.connection._read_packet()
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 701, in _read_packet
raise err.OperationalError(
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/Users/yimt/Code/PycharmProjects/hello-python/work.py", line 31, in <module>
ls = [user.username for user in query]
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 6960, in __iter__
self.execute()
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 1918, in inner
return method(self, database, *args, **kwargs)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 1989, in execute
return self._execute(database)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 2162, in _execute
cursor = database.execute(self)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 3190, in execute
return self.execute_sql(sql, params, commit=commit)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 3174, in execute_sql
with __exception_wrapper__:
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 2950, in __exit__
reraise(new_type, new_type(exc_value, *exc_args), traceback)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 191, in reraise
raise value.with_traceback(tb)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 3177, in execute_sql
cursor.execute(sql, params or ())
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute
result = self._query(query)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query
conn.query(q)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result
result.read()
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read
first_packet = self.connection._read_packet()
File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 701, in _read_packet
raise err.OperationalError(
peewee.OperationalError: (2013, 'Lost connection to MySQL server during query')

4. 解決方法

使用ReconnectMixin，當發現數據庫連接失效時會自動重新連接。

import time
from playhouse.shortcuts import ReconnectMixin
from peewee import *
class ReconnectMySQLDatabase(ReconnectMixin, MySQLDatabase):
pass
db = ReconnectMySQLDatabase(
'test',
user='root',
password='12345678',
host='127.0.0.1',
port=3306,
)
class User(Model):
username = CharField()
password = CharField()
salt = CharField()
state = SmallIntegerField()
class Meta:
database = db
db.create_tables([User])
query = User.select()
ls = [user.username for user in query]
time.sleep(5)
query = User.select()
ls = [user.username for user in query]