regression w/ sqlite join rewriting

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import *


engine = create_engine('sqlite://', echo=True)
#engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

Base = declarative_base(bind=engine)
metadata = Base.metadata


account = Table('account', metadata,
    Column('account_id', Integer, key='id', primary_key=True),
    Column('name', String(255), nullable=False),
)

account_address_map = Table('account_address_map', metadata,
    Column('account_id', Integer, ForeignKey('account.id'), nullable=False),
    Column('address_id', Integer, ForeignKey('address.id'), nullable=False),
    Column('role_id', Integer, nullable=False)
)

address = Table('address', metadata,
    Column('address_id', Integer, key='id', primary_key=True),
    Column('address1', String(255), nullable=False),
)


mapping = account_address_map.select().where(account_address_map.c.role_id == 1)


class Account(Base):
    __table__ = account

    service_address = relationship('Address',
        secondary=mapping,
        uselist=False,
        viewonly=True)


class Address(Base):
    __table__ = address


metadata.drop_all()
metadata.create_all()
session = Session()
session.query(Account).options(joinedload_all('service_address')).all()
Comments (2)
