Python中如何解决不同服务器数据库连接长时间闲置后断开的问题？
服务器 A 是 tornado，去连服务器 B 上的 PostgreSQL 数据库，刚开始没啥问题。
可是时间久了服务器 A 和服务器 B 的数据库连接就断了。
大佬们有遇到过这种情况吗？
附个错误提示：
Traceback (most recent call last):
File "/home/xxx/.local/lib/python3.5/site-packages/peewee.py", line 3756, in execute_sql
cursor = self.get_cursor()
File "/home/xxx/.local/lib/python3.5/site-packages/peewee.py", line 3704, in get_cursor
return self.get_conn().cursor()
psycopg2.InterfaceError: connection already closed
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File “/home/xxx/.local/lib/python3.5/site-packages/tornado/web.py”, line 1488, in _execute
result = self.prepare()
File “/home/xxx/cc_backend/view/init.py”, line 217, in prepare
if not self.current_user():
File “/home/xxx/cc_backend/view/init.py”, line 192, in current_user
return User.get_by_key(key)
File “/home/xxx/cc_backend/model/user.py”, line 102, in get_by_key
return cls.get(cls.key == key)
File “/home/xxx/.local/lib/python3.5/site-packages/peewee.py”, line 4916, in get
return sq.get()
File “/home/xxx/.local/lib/python3.5/site-packages/peewee.py”, line 3171, in get
return next(clone.execute())
File “/home/xxx/.local/lib/python3.5/site-packages/peewee.py”, line 3223, in execute
self._qr = ResultWrapper(model_class, self._execute(), query_meta)
File “/home/xxx/.local/lib/python3.5/site-packages/peewee.py”, line 2902, in _execute
return self.database.execute_sql(sql, params, self.require_commit)
File “/home/xxx/.local/lib/python3.5/site-packages/peewee.py”, line 3765, in execute_sql
self.commit()
File “/home/xxx/.local/lib/python3.5/site-packages/peewee.py”, line 3588, in exit
reraise(new_type, new_type(*exc_args), traceback)
File “/home/xxx/.local/lib/python3.5/site-packages/peewee.py”, line 135, in reraise
raise value.with_traceback(tb)
File “/home/xxx/.local/lib/python3.5/site-packages/peewee.py”, line 3756, in execute_sql
cursor = self.get_cursor()
File “/home/xxx/.local/lib/python3.5/site-packages/peewee.py”, line 3704, in get_cursor
return self.get_conn().cursor()
peewee.InterfaceError: connection already closed
Python中如何解决不同服务器数据库连接长时间闲置后断开的问题？
import pymysql
from dbutils.persistent_db import PersistentDB
import threading
import time
# 创建持久化数据库连接池
POOL = PersistentDB(
creator=pymysql,
maxusage=1000, # 单个连接最大复用次数
setsession=[], # 可选的会话命令列表
ping=1, # 每次借出连接时ping检查 (0=从不, 1=默认, 2=创建时, 4=执行查询时, 7=总是)
closeable=False,
threadlocal=None, # 线程局部存储
host='localhost',
user='test',
password='test',
database='test',
charset='utf8mb4'
)
def get_connection():
"""获取数据库连接"""
return POOL.connection()
# 使用示例
def query_data():
conn = get_connection()
try:
with conn.cursor() as cursor:
cursor.execute("SELECT NOW()")
result = cursor.fetchone()
print(f"当前时间: {result[0]}")
finally:
conn.close() # 实际是归还到连接池
# 测试连接保持
def test_connection():
print("初始查询...")
query_data()
print("等待35秒模拟闲置...")
time.sleep(35)
print("闲置后查询...")
query_data() # 连接池会自动处理重连
# 多线程测试
threads = []
for i in range(3):
t = threading.Thread(target=test_connection)
threads.append(t)
t.start()
for t in threads:
t.join()
核心方案：
- 连接池：使用
DBUtils.PersistentDB创建持久化连接池，连接复用避免频繁创建 - ping参数：设置
ping=1，每次从池中取连接时自动检查，失效则重建 - 连接验证：在执行查询前通过简单SQL（如
SELECT 1）验证连接状态 - 重连机制：在数据库操作外层包裹重试逻辑，捕获断开异常后重新连接
替代方案：
- SQLAlchemy：内置连接池和预ping功能
- 设置MySQL的
wait_timeout和interactive_timeout参数增加超时时间 - 使用
mysql.connector的pool_reset_session=False减少连接重置
一句话建议：用连接池+ping检测最省心。
太正常了，数据库会把一些闲置的连接主动杀掉（多长时间没有活动），可以通过 Keepalive 来连接，linux 可以通过一下参数来进行配置：
net.ipv4.tcp_keepalive_time=90
net.ipv4.tcp_keepalive_intvl=15
net.ipv4.tcp_keepalive_probes=2
但是依然会断开,postgresql 可以定时发送 NOTIFY 来保持连接
原来如此，谢谢
增加连接时常的配置有点治标不治本的意思，我先谷歌看看 NOTIFY，谢谢
想说有没有一种断了再重连的方法，或者使用前尝试重连就可以解决了
好主意，我去试试，谢谢
