SQLModel/SQLAlchemy空表查询遇UnboundExecutionError问题求助

问题背景 #

使用SQLite数据库，定义configs表关联server、user、business三个子配置表，执行查询时触发数据库绑定错误。

模型定义 #

from typing import Optional, List, Dict
from uuid import UUID
from sqlmodel import SQLModel, Field, Relationship
from sqlalchemy import Column, JSON

class ServerConfigBase(SQLModel):
    """Base Server Configuration."""

    address: str
    port: int
    app_configs: List["Config"] = Relationship(back_populates="server")

class ServerConfig(ServerConfigBase, table=True):
    """Configuration model describing network communication fields"""

    id: Optional[UUID] = Field(default=None, primary_key=True)

class BusinessConfigBase(SQLModel):
    """Base Business Configuration."""
    ...
    app_configs: List["Config"] = Relationship(back_populates="business")

class BusinessConfig(BusinessConfigBase, table=True):
    """Configuration model describing Business related fields"""

    id: Optional[UUID] = Field(default=None, primary_key=True)

class UserConfigBase(SQLModel):
    """Base User Configuration."""

    name: str
    app_configs: List["Config"] = Relationship(back_populates="user")

class UserConfig(UserConfigBase, table=True):
    """Configuration model describing user settable fields"""

    id: Optional[UUID] = Field(default=None, primary_key=True)

class ConfigBase(SQLModel):
    """Base Configuration."""

    server: ServerConfig = Relationship(back_populates="app_configs")
    server_conf_id: UUID = Field(foreign_key=ServerConfig.id)
    business: BusinessConfig = Relationship(back_populates="app_configs")
    business_conf_id: UUID = Field(foreign_key=BusinessConfig.id)
    user: UserConfig = Relationship(back_populates="app_configs")
    user_conf_id: UUID = Field(foreign_key=UserConfig.id)
    random_list_of_string_field: List[Dict] = Field(sa_column=Column(JSON[List[Dict]]))

class Config(ConfigBase, table=True):
    """Table configuration."""

    id: Optional[UUID] = Field(default=None, primary_key=True)
    active: bool = False

错误触发场景 #

执行以下查询语句时均报错：

# 检查激活配置
session.exec(select(Config).where(Config.active == True)).first()

# 查询全表
session.exec(select(Config)).all()

错误信息 #

sqlalchemy.exc.UnboundExecutionError: Could not locate a bind configured on mapper mapped class Config->config, SQL expression or this Session.

堆栈跟踪 #

File "/Users/somaf/repos/api/src/api/controllers/config.py", line 91, in read_configs
    return session.exec(select(Config)).all()
  File "/Users/somaf/repos/api/.venv/lib/python3.9/site-packages/sqlmodel/orm/session.py", line 60, in exec
    results = super().execute(
  File "/Users/somaf/repos/api/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1705, in execute
    bind = self.get_bind(**bind_arguments)
  File "/Users/somaf/repos/api/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2154, in get_bind
    raise sa_exc.UnboundExecutionError(
sqlalchemy.exc.UnboundExecutionError: Could not locate a bind configured on mapper mapped class Config->config, SQL expression or this Session.

补充：create_engine代码 #

代码位于main.py的lifespan初始化函数中：

engine = create_engine(
            "sqlite://",
            connect_args={"check_same_thread": False},
            poolclass=StaticPool
        )
SQLModel.metadata.create_all(engine)

解答说明 #

此问题与SQLModel的Relationship无关！

问题根源 #

1. lifespan函数中创建的engine是局部变量，后续创建Session时未引用该engine，导致Session无法找到数据库绑定。
2. 使用内存SQLite（sqlite://）会导致每次重启丢失数据，但核心问题是Session与engine的绑定缺失。

解决步骤 #

1. 确保engine全局可访问：将engine定义为全局变量，或通过依赖注入等方式让Session能获取到该engine。
2. 创建Session时绑定engine：实例化Session时明确传入engine，而非使用无参构造。
3. 改用持久化数据库（可选）：将sqlite://改为具体文件路径，比如sqlite:///./config.db，避免重启丢失数据。

修正示例 #

# 全局定义engine
engine = create_engine(
    "sqlite:///./config.db",
    connect_args={"check_same_thread": False},
    poolclass=StaticPool
)
SQLModel.metadata.create_all(engine)

# 创建Session时绑定engine
session = Session(engine=engine)

同时确认SQLModel.metadata.create_all(engine)执行成功，确保所有表已正确创建。

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