dynamicfail.py

from sqlalchemy import Column, ForeignKey, Integer, and_, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
    backref, foreign, relationship, scoped_session, sessionmaker
)


Base = declarative_base()


class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)


class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_1_id = Column(Integer, ForeignKey(A.id))

    a_2_id = Column(Integer, ForeignKey(A.id))

    a_1 = relationship(
        A,
        primaryjoin=and_(a_1_id == foreign(A.id)),
        backref=backref(
            name='a_1s', lazy='dynamic', uselist=True
        ),
        uselist=False,
    )
    a_2 = relationship(
        A,
        primaryjoin=and_(a_2_id == foreign(A.id)),
        backref=backref(
            name='a_2s', lazy='dynamic', uselist=True
        ),
        uselist=False,
    )


FooSession = scoped_session(sessionmaker())

engine = create_engine('sqlite:///dynamicfail.sqlite')
FooSession.configure(bind=engine)
engine.bind = Base.metadata

FooSession.query(A)