SQLAlchemy 1.3.23经典映射(imperative mapping)实现relationship关联问题

问题核心原因 #

• relationship()第一个参数要求传入已完成映射的实体类，你当前传入的是markets_table表对象，属于参数类型错误
• users_table.c.xxx是SQLAlchemy Table对象的列集合，仅能查询表结构中定义的物理字段，markets是映射后实体类的关联属性，不存在于表的列集合中，无法通过xxx_table.c.xxx获取
• 你的实体类__init__方法中所有赋值语句末尾多了逗号，会导致属性被封装为元组，后续操作会出现类型错误

修正后的完整实现代码 #

from sqlalchemy import Table, Column, Integer, String, Boolean, ForeignKey, Enum
from sqlalchemy.orm import mapper, relationship
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

# 此处替换为你自己定义的MarketTypes枚举类即可
class MarketTypes:
    TYPE1 = 1
    TYPE2 = 2

Base = declarative_base()
metadata = Base.metadata

# 表定义保持不变
users_table = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("username", String, nullable=False, unique=True),
)

markets_table = Table(
    "markets",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("type", Enum(MarketTypes), nullable=False),
    Column("status", Boolean, server_default="True"),
    Column("user_id", ForeignKey("users.id"), nullable=False),
)

# 实体类修正：去掉赋值末尾的多余逗号
class User(object):
    def __init__(self, id, username):
        self.id = id
        self.username = username

class Market(object):
    def __init__(self, id, type, status, user_id):
        self.id = id
        self.type = type
        self.status = status
        self.user_id = user_id

# 映射逻辑修正：relationship第一个参数传入已映射的Market类
mapper(Market, markets_table)

mapper(User, users_table, properties={
    "markets": relationship(
        Market, 
        primaryjoin=users_table.c.id == markets_table.c.user_id,
        backref="user" # 可选配置，添加后可直接通过Market实例.user获取关联的User对象
    )
})

# 数据库初始化示例
engine = create_engine("sqlite:///test.db")
metadata.create_all(engine)

关联属性使用示例 #

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

# 插入测试数据
user = User(id=1, username="test_user")
session.add(user)
market = Market(id=1, type=MarketTypes.TYPE1, status=True, user_id=1)
session.add(market)
session.commit()

# 获取关联属性：从User实例获取关联的市场列表
user_obj = session.query(User).filter_by(id=1).first()
print(user_obj.markets) # 输出关联的Market对象列表
# 反向关联获取：从Market实例获取所属用户
market_obj = session.query(Market).filter_by(id=1).first()
print(market_obj.user) # 输出关联的User对象

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