Hi there,
I have been using sqlalchemy for the past month with great success.
However, recently I came across an unexpected situation. Essentially
the results I was retrieving from the database via an ORM query were
not including all the related attributes despite indicating eager
loading on the mapper using lazy=False.
I have created an example script (below) to highlight the results I
was getting. Note that after a bit of reading I discovered I could use
contains_eager() on the query to obtain the correct result, but I am
not sure if indeed this is the correct solution or if there is a flaw
in my setup/understanding of the system.
As I see it from the SQL the first query is placing a limit 1 at the
end of the full statement rather than as part of a subselect causing
only 1 Tag to be retrieved with the Employee rather than the expected
2.
ta,
Martin
p.s. Apologies if the code displays badly - is there a format
indicator?
===================================================================
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:////tmp/example.db', echo=True)
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True,
transactional=True, bind=engine))
mapper = Session.mapper
# Person
#----------------------------------------------
class Person(object):
def __init__(self):
self.id = None
class Employee(Person):
def __init__(self, name='bob'):
self.name = name
class Tag(object):
def __init__(self, label):
self.id = None
self.label = label
# Setup tables
#----------------------------------------------
people = Table('people', metadata,
Column('id', Integer, primary_key=True),
Column('_type', String(30), nullable=False),
)
employees = Table('employees', metadata,
Column('id', Integer, ForeignKey('people.id'),
primary_key=True),
)
tags = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('label', String, nullable=False),
)
peopleTags = Table('peopleTags', metadata,
Column('person_id', Integer,
ForeignKey('people.id')),
Column('tag_id', Integer,
ForeignKey('tags.id')),
)
# Mappers
#----------------------------------------------
mapper(Person, people, polymorphic_on=people.c._type,
polymorphic_identity='person')
mapper(Employee, employees, inherits=Person,
polymorphic_identity='employee')
mapper(Tag, tags)
_mapper = class_mapper(Person)
_table = _mapper.local_table
_mapper.add_property('tags', relation(Tag, secondary=peopleTags,
backref='people', lazy=False))
metadata.create_all(engine)
# Test
#----------------------------------------------
session = Session()
bob = Employee()
session.save(bob)
tag = Tag('crazy')
session.save(tag)
bob.tags.append(tag)
tag = Tag('funny')
session.save(tag)
bob.tags.append(tag)
session.commit()
session.clear()
instance = session.query(Employee).filter_by(id=1).first()
print instance, instance.tags
session.clear()
instance =
session.query(Employee).options(contains_eager('tags')).filter_by(id=1).first()
print instance, instance.tags
Session.remove()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
