python - SQLAlchemy 多对多无外键

Question

有人可以帮我弄清楚我应该如何编写 PrimaryJoin/SecondaryJoin在缺少一个外键定义的辅助表上。我无法修改数据库本身，因为它被不同的应用程序使用。

from sqlalchemy import schema, types, func, orm
from sqlalchemy.dialects.postgresql import ARRAY

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'atab'
    id = schema.Column(types.SmallInteger, primary_key=True)

class B(Base):
    __tablename__ = 'btab'
    id = schema.Column(types.SmallInteger, primary_key=True)

    a = orm.relationship(
        'A', secondary='abtab', backref=orm.backref('b')
    ) 

class AB(Base):
    __tablename__ = 'abtab'
    id = schema.Column(types.SmallInteger, primary_key=True)
    a_id = schema.Column(types.SmallInteger, schema.ForeignKey('atab.id'))
    b_id = schema.Column(types.SmallInteger)

我尝试在连接条件上指定外部:

a = orm.relationship(
    'A', secondary='abtab', backref=orm.backref('b'),
    primaryjoin=(id==orm.foreign(AB.b_id))
) 

但收到以下错误:

ArgumentError: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition '"atab".id = "abtab"."a_id"' on relationship Category.projects.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True.

Answer 1

您可以将 foreign_keys 添加到您的关系配置中。他们在 mailing list post 中提到了这一点:

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    logon = Column(String(10), primary_key=True)
    group_id = Column(Integer)

class Group(Base):
    __tablename__ = 'groups'
    group_id = Column(Integer, primary_key=True)
    users = relationship('User', backref='group',
        primaryjoin='User.group_id==Group.group_id',
        foreign_keys='User.group_id')

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

u1 = User(logon='foo')
u2 = User(logon='bar')
g = Group()
g.users = [u1, u2]
session.add(g)
session.commit()
g = session.query(Group).first()
print([user.logon for user in g.users])

输出:

['foo', 'bar']