SQLAlchemy更新非默认模式表时多次迭代后报relation不存在问题

你遇到的这个“relation不存在”的异常，根本原因大概率和SQLAlchemy的连接池机制以及PostgreSQL的search_path作用范围有关，下面帮你拆解原因并给出针对性的解决办法：

核心原因：连接池复用导致search_path丢失 #

PostgreSQL里的SET search_path TO client1是连接级别的设置，而SQLAlchemy默认会使用连接池来复用数据库连接。当你在某个会话中设置了search_path，会话结束后这个连接会被放回连接池，后续新的会话复用这个连接时，如果没有重新设置search_path，一旦有其他操作修改了该连接的search_path，或者连接池回收连接后重置了状态，就会出现找不到目标schema下表的情况。

你的分批处理能临时生效，是因为每批处理后新建会话，相当于重新获取连接并设置search_path，但次数多了连接池还是会复用旧连接，所以问题会复现。

根本解决方案 #

方案1：在表模型中直接指定schema（最可靠） #

不要依赖search_path的设置，直接在定义表的时候明确指定所属schema，这样SQLAlchemy生成的SQL会自动带上schema前缀，彻底避免路径问题：

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class YourTargetTable(Base):
    __tablename__ = 'your_table'
    __table_args__ = {'schema': 'client1'}  # 这里指定目标schema
    
    # 定义你的字段
    id = Column(Integer, primary_key=True)
    attr1 = Column(String)
    attr2 = Column(String)

之后不管连接的search_path是什么，查询和更新都会直接指向client1.your_table，不会出现找不到表的问题。

方案2：监听连接事件，自动设置search_path #

如果不能修改表模型，可以通过SQLAlchemy的事件监听机制，在每次新连接创建时自动执行SET search_path命令，确保所有连接都带有正确的路径设置：

from sqlalchemy import event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "connect")
def setup_search_path(dbapi_connection, connection_record):
    # 在连接建立时立即设置search_path
    cursor = dbapi_connection.cursor()
    cursor.execute("SET search_path TO client1")
    cursor.close()

这样不管连接池怎么复用连接，每个新连接都会自动配置好search_path，从根源解决连接状态不一致的问题。

方案3：每次会话初始化时都设置search_path #

如果以上两种方法都不适用，可以修改你的代码，确保每次创建会话后都执行SET search_path，而不是只在开头执行一次：

# 每次创建会话后都设置search_path
session = DBSession()
try:
    session.execute("SET search_path TO client1")
    total_rows = session.query(table).all()
    for row in total_rows:
        try:
            row.attr1 = getAttr1()
            row.attr2 = getAttr2()
            session.commit()
        except Exception as inst:
            print(inst)
            session.rollback()
finally:
    session.close()

不过这种方法不如前两种可靠，因为如果连接被复用且中间被修改了search_path，还是可能出问题。

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