如何在SQL Alchemy中为已存在的表添加关联关系？

问题描述 #

我有一个包含多列的表，想要使用SQLAlchemy为其添加新的关联关系。由于列数量较多，我无法重新创建映射所有列的对象再添加关联，需要一种能自动加载所有列并添加新关联的方法。

示例代码：

import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.engine.url import URL

DATABASE = {
    'drivername': 'sqlite',
    'database': 'test.db'
}

engine = sa.create_engine(URL.create(**DATABASE))
Base = orm.declarative_base()

class Parent(Base):
    __tablename__ = 'parent'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    # ...假设这里还有很多其他列

class Child(Base):
    __tablename__ = 'child'
    id = sa.Column(sa.Integer, primary_key=True)
    # ...假设这里还有很多其他列

Base.metadata.create_all(engine)
session = orm.Session(engine)

user1 = Parent(id=1, name='Jorge')
user2 = Parent(id=2, name='Lucas')
user3 = Parent(id=3, name='Amanda')

child1 = Child(id=1)
session.add_all([user1, user2, user3, child1])
session.commit()    

现在，在另一个会话中，我想要加载Child表并添加关联关系，该如何操作？

我尝试过但无效的示例：

示例一

import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.engine.url import URL

DATABASE = {
    'drivername': 'sqlite',
    'database': 'test.db'
}

engine = sa.create_engine(URL.create(**DATABASE))
Base = orm.declarative_base()

class Child(Base) :
    __tablename__ = 'child'
    __table_args__ = {'extend_existing': True}
    
    parent_id = sa.Column(sa.Integer, sa.ForeignKey('parent.id'))
    parent = orm.relationship("Parent", uselist=False, backref="child")

报错：

ArgumentError: Mapper mapped class Child->child could not assemble any primary key columns for mapped table 'child'

示例二

class Child(Base) :
    __table__ = sa.Table('child', Base.metadata, autoload_with=engine)
        
    parent_id = sa.Column(sa.Integer, sa.ForeignKey('parent.id'))
    parent = orm.relationship("Parent", uselist=False, backref="child")

报错：

ArgumentError: Can't add additional column 'parent_id' when specifying table

解决方法 #

要实现自动加载现有表的所有列，同时添加新的关联字段和关系，需要分两步操作：先反射加载表结构并添加外键列，再创建映射类绑定关系。

步骤1：反射加载表并添加外键列

先把Child表的结构从数据库中反射出来，检查并添加需要的外键列（如果数据库中还没有该列）：

import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.engine.url import URL

DATABASE = {
    'drivername': 'sqlite',
    'database': 'test.db'
}

engine = sa.create_engine(URL.create(**DATABASE))
Base = orm.declarative_base()

# 反射加载Child表的完整结构
child_table = sa.Table('child', Base.metadata, autoload_with=engine)

# 检查parent_id列是否存在，不存在则添加到数据库
if 'parent_id' not in child_table.columns:
    parent_id_col = sa.Column('parent_id', sa.Integer, sa.ForeignKey('parent.id'))
    parent_id_col.create(child_table, bind=engine)

步骤2：创建映射类并添加关联关系

绑定反射好的表到Child类，直接添加关联关系即可，无需重复定义原有列：

class Child(Base):
    __table__ = child_table
    # 添加与Parent的关联关系
    parent = orm.relationship("Parent", uselist=False, backref="child")

# 加载Parent类（如果Parent列也很多，同样可以用反射方式加载）
class Parent(Base):
    __tablename__ = 'parent'
    __table_args__ = {'extend_existing': True}
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    # 若Parent列多，替换为：__table__ = sa.Table('parent', Base.metadata, autoload_with=engine)

# 测试关联功能
session = orm.Session(engine)
child = session.query(Child).get(1)
child.parent = session.query(Parent).get(1)
session.commit()

# 验证关联是否生效
child = session.query(Child).get(1)
print(child.parent.name)  # 输出Jorge

报错原因说明 #

• 示例一：仅指定__tablename__和extend_existing，但未定义表的主键列，SQLAlchemy无法识别表的主键信息，因此报错。
• 示例二：直接绑定反射好的__table__后，不能在类中直接新增列定义，因为__table__已经完整描述了表结构，新增列需先修改数据库中的表结构，再绑定映射类。

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