如何使用SQLAlchemy连接PostgreSQL指定Schema并操作数据？

一、连接阶段指定默认Schema #

PostgreSQL的search_path参数决定了默认查找的Schema，你可以在连接URL中直接指定，后续操作无需重复添加Schema前缀：

from sqlalchemy import create_engine

# 替换成你的数据库用户名、密码、主机、数据库名
engine = create_engine('postgresql://postgres:your_password@localhost:5432/your_db?options=-csearch_path=rezerwacja_domkow')

或者在连接后手动设置搜索路径：

with engine.connect() as conn:
    conn.execute("SET search_path TO rezerwacja_domkow")
    conn.commit()

二、用SQLAlchemy Inspector查看Schema内的表 #

Inspector默认只扫描publicSchema，需要显式指定目标Schema名称才能获取表列表：

from sqlalchemy import create_engine, inspect

engine = create_engine('postgresql://postgres:your_password@localhost:5432/your_db')
inspector = inspect(engine)

# 获取rezerwacja_domkow下的所有表
tables = inspector.get_table_names(schema='rezerwacja_domkow')
print(tables)

三、数据增改操作 #

1. ORM模型方式 #

定义模型时通过__table_args__绑定目标Schema，后续操作和使用SQLite时一致：

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

Base = declarative_base()

# 示例模型，替换成你的表结构
class CabinReservation(Base):
    __tablename__ = 'cabin_reservations'  # 你的表名
    __table_args__ = {'schema': 'rezerwacja_domkow'}  # 指定Schema
    
    id = Column(Integer, primary_key=True)
    customer_name = Column(String(50))
    cabin_id = Column(Integer)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 新增数据
new_reservation = CabinReservation(customer_name="John Doe", cabin_id=3)
session.add(new_reservation)
session.commit()

# 修改数据
target_reservation = session.query(CabinReservation).filter_by(id=1).first()
if target_reservation:
    target_reservation.customer_name = "Jane Doe"
    session.commit()

2. 原生SQL方式 #

如果不使用ORM，执行SQL时可以直接带上Schema前缀（或已设置search_path则无需前缀）：

with engine.connect() as conn:
    # 新增数据
    conn.execute("INSERT INTO rezerwacja_domkow.cabin_reservations (customer_name, cabin_id) VALUES ('Alice', 5)")
    # 修改数据
    conn.execute("UPDATE rezerwacja_domkow.cabin_reservations SET cabin_id = 4 WHERE id = 1")
    conn.commit()

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