mappers with primary_key but tables have no primary key whatsoever

from sqlalchemy import *

meta = MetaData()
engine = create_engine('sqlite:///:memory:', echo=True)
meta.bind = engine

# Tables
rel1 = Table('rel1', meta,
    Column('id', Integer, primary_key=True),
    Column('data', Unicode),
    )

rel2 = Table('rel2', meta,
    Column('id', Integer, primary_key=True),
    Column('data', Unicode),
    )


hub = Table('hub', meta,
    Column('time', DateTime, nullable=False),
    Column('rel1_id', Integer, ForeignKey('rel1.id'), nullable=False),
    Column('rel2_id', Integer, ForeignKey('rel2.id'), nullable=False),

    # Mapped schema doesn't use a primary key since it's a "hub" table
    # in a fact system and "time" doesn't have enough resolution to guarantee
    # uniqueness. 
    #XXX Uncommenting the following line fixes it.
    ##PrimaryKeyConstraint('time', 'rel1_id', 'rel2_id'),

    Column('data', Unicode),
    )

# Mapped classes
class Hub(object): pass
class Rel1(object): pass
class Rel2(object): pass

# mappers
mapper(Rel1, rel1)
mapper(Rel2, rel2)
mapper(Hub, hub, 
    # A PK must be "faked" in the mapper to be able to map it (some rows are 
    # missed due to duped pk when retrieving with the mapper but it's not much
    # of a problem since the schema is mostly queried without the ORM for data
    # analysisi).
    primary_key = [hub.c.rel1_id, hub.c.rel2_id](hub.c.time,),
    properties = dict(
        #XXX: Making the relations lazy fixes it too.
        rel1 = relation(Rel1, lazy=False),
        rel2 = relation(Rel2, lazy=False),
        )
    )

def run_test():
    meta.create_all()
    sess = create_session(bind_to=engine)
    # No limit, no problem
    sess.query(Hub).select()
    # Bang!
    sess.query(Hub).select(limit=100)

if __name__ == '__main__':
    run_test()
Comments (2)
