Hey,
I've got some interesting behaviour on a slightly obtuse schema: I map a
Person onto two tables, 'account' and 'person', which store login details
and personal details, respectively. I then have a many-many join onto a
submission table, which contains details about papers.
Attached is a the simplified version, which has 4 tables, and two mappers on
two domain objects.
The assertion that fails shows that the query returning the list of
submissions assigned to the third person is incorrect. You get the correct
behaviour if you change the Person mapper to not join on account and person,
getting a query that looks like:
SELECT submission.title AS submission_title, submission.id AS submission_id
FROM person_submission_map, submission
WHERE ? = person_submission_map.person_id AND submission.id =
person_submission_map.submission_id ORDER BY person_submission_map.oid
which is doing the right thing, asking for a person_id.
The failing query looks like:
SELECT submission.title AS submission_title, submission.id AS submission_id
FROM person, person_submission_map, submission
WHERE person.id = person_submission_map.person_id AND submission.id =
person_submission_map.submission_id ORDER BY person_submission_map.oid
which isn't looking for a specific person when it runs the query, and hence
returns an invalid set of data.
So, am I doing something wrong? Does the Person mapper need extra
information to perform many-many joins? Or is this a bug? :)
If it's a bug, ideas for workaround would be welcome too.
from sqlalchemy import *
account = Table('account',
Column('id', Integer, primary_key=True),
Column('email_address', String)
)
person = Table('person',
Column('id', Integer, primary_key=True),
Column('account_id', Integer, ForeignKey('account.id')),
Column('name', String)
)
submission = Table('submission',
Column('id', Integer, primary_key=True),
Column('title', String)
)
p_s_map = Table('person_submission_map',
Column('person_id', Integer, ForeignKey('person.id'),
nullable=False),
Column('submission_id', Integer, ForeignKey('submission.id'),
nullable=False),
)
engine = create_engine('sqlite:///', echo=True)
global_connect(engine)
default_metadata.create_all()
class Person(object):
def __init__(self, name=None, email=None):
self.name = name
self.email_address = email
def __repr__(self):
return '<Person id="%s" name="%s">' % (self.id, self.name)
mapper(Person, join(account, person),
properties = {
'account_id': [account.c.id, person.c.account_id]
}
)
# alternate mapper, without joining on account
#mapper(Person, person)
class Submission(object):
def __init__(self, title=None):
self.title = title
def __repr__(self):
return '<Submission id="%s" title="%s">' % (self.id, self.title)
mapper(Submission, submission,
properties = {
'people': relation(Person, secondary=p_s_map,
backref='submissions'),
}
)
session = create_session()
p1 = Person('a')
session.save(p1)
session.flush()
s1 = Submission('one')
p1.submissions.append(s1)
session.save(s1)
session.flush()
p2 = Person('b')
session.save(p2)
session.flush()
s2 = Submission('two')
p2.submissions.append(s2)
session.save(s2)
session.flush()
p3 = Person('c')
session.save(p3)
session.flush()
p4 = Person('d')
s1.people.append(p4)
session.save(p4)
session.flush()
print "p1", p1, p1.submissions
print "p2", p2, p2.submissions
print "p3", p3, p3.submissions
print "p4", p4, p4.submissions
# assert positives
assert s1 in p1.submissions
assert s2 in p2.submissions
assert p3.submissions == []
assert s1 in p4.submissions
# now assert negatives
assert s1 not in p2.submissions
assert s2 not in p1.submissions
assert s1 not in p3.submissions
assert s2 not in p3.submissions
assert s2 not in p4.submissions
assert p1 in s1.people
assert p2 in s2.people
assert p3 not in s1.people
assert p4 in s1.people
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
