Hi,
I've encountered a strange behaviour when mapping a table which
hasn't got a PrimaryKeyConstraint declared but a primary_key is
declared when building the mapper (in order to be able to map it).
Queries through the mapper work fine until the "limit" keyword is
used. Am I doing something wrong?
Attached is a simple script that reproduces the problem on SA 0.3.10
Thanks,
Alberto
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
"""The following query fails when using "limit" and no primary key is declared
at the table level but is declared on the mapper. Exception raised:
exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (OperationalError) no such column: tbl_row_count.hub_time u'SELECT hub.rel2_id AS hub_rel2_id, hub.rel1_id AS hub_rel1_id, anon_9feb.data AS anon_9feb_data, anon_9feb.id AS anon_9feb_id, anon_d6ec.data AS anon_d6ec_data, anon_d6ec.id AS anon_d6ec_id, hub.time AS hub_time, hub.data AS hub_data \nFROM hub LEFT OUTER JOIN rel2 AS anon_9feb ON anon_9feb.id = hub.rel2_id LEFT OUTER JOIN rel1 AS anon_d6ec ON anon_d6ec.id = hub.rel1_id ORDER BY tbl_row_count.hub_time DESC, anon_9feb.oid, anon_d6ec.oid'
Run script for full traceback.
"""
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.time, hub.c.rel1_id, hub.c.rel2_id],
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()
