Flask应用出现psycopg2.InterfaceError：cursor already closed问题求助

核心错误原因 #

1. 全局连接/游标被复用且提前关闭：你在全局作用域创建了conn和cur，第一次请求执行conn.close()后连接已关闭，但Flask在debug模式下会复用上下文或重启，后续请求再调用cur.execute()时，游标依赖的连接已失效，触发cursor already closed错误。
2. 模板语法错误：Jinja2模板不支持直接调用Python内置函数len()，需用模板过滤器|length；且循环写法冗余，没必要通过索引遍历。

修复方案 #

方案一：改用SQLAlchemy操作数据库（推荐） #

既然已引入Flask-SQLAlchemy，可直接用它替代原生psycopg2，无需手动管理连接和游标：

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
from models import People, db
from flask_migrate import Migrate

app = Flask(__name__)

# 直接配置目标数据库URI
app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://teacher_user:1234@localhost:5432/teacherdb"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db.init_app(app)
migrate = Migrate(app, db)

@app.route('/')
def index():
    # 用SQLAlchemy执行原生SQL查询
    records = db.session.execute(db.text("SELECT * FROM teachertb")).fetchall()
    print(records)
    # SQLAlchemy自动管理连接，无需手动关闭
    return render_template("index.html", records=records)

if __name__ == "__main__":
    app.run(debug=True)

方案二：坚持使用原生psycopg2 #

每次请求都重新创建连接和游标，请求结束后按顺序关闭：

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
from models import People, db
from flask_migrate import Migrate
import psycopg2

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://student_user:1234@localhost:5432/studentdb"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db.init_app(app)
migrate = Migrate(app, db)

@app.route('/')
def index():
    # 每次请求新建连接与游标
    conn = psycopg2.connect(dbname="teacherdb", host="localhost", user="teacher_user", password=1234)
    cur = conn.cursor()
    cur.execute("SELECT * FROM teachertb")
    records = cur.fetchall()
    print(records)
    # 先关闭游标再关闭连接
    cur.close()
    conn.close()
    return render_template("index.html", records=records)

if __name__ == "__main__":
    app.run(debug=True)

修复HTML模板错误 #

简化循环逻辑，适配Jinja2语法：

<!DOCTYPE html>
<html>
<head>
    <meta charset='utf-8'>
    <meta http-equiv='X-UA-Compatible' content='IE=edge'>
    <title>Page Title</title>
    <meta name='viewport' content='width=device-width, initial-scale=1'>
</head>
<body>
    {% for record in records %}
        <p>{{ record[1] }}</p>
    {% endfor %}
</body>
</html>

内容的提问来源于stack exchange，提问作者mkg