I am having an issue at work with a query that is passing in all of our
unit tests against SQLite and PostgreSQL, but is failing in MySQL. Here
is a short test case that will show the problem. Any help would be much
appreciated!
#---------------------------------------------------------------------#
from sqlalchemy import *
metadata = MetaData()
#
# tables
#
p_table = Table('p', metadata,
Column('id', Integer, primary_key=True),
Column('foo', Unicode)
)
v_table = Table('v', metadata,
Column('id', Integer, primary_key=True),
Column('p_id', Integer, ForeignKey('p.id')),
Column('a_id', Integer, ForeignKey('a.id')),
Column('bar', Unicode)
)
a_table = Table('a', metadata,
Column('id', Integer, primary_key=True),
Column('baz', Unicode)
)
#
# test case
#
if __name__ == '__main__':
metadata.connect('mysql://user:[EMAIL PROTECTED]/some_test_database')
metadata.create_all()
p_table.insert().execute(foo='foo1')
a_table.insert().execute(baz='baz1')
v_table.insert().execute(bar='bar1', p_id=1, a_id=1)
p_table.insert().execute(foo='foo2')
a_table.insert().execute(baz='baz2')
v_table.insert().execute(bar='bar2', p_id=2, a_id=2)
p_table.insert().execute(foo='foo3')
a_table.insert().execute(baz='baz3')
v_table.insert().execute(bar='bar3', p_id=3, a_id=3)
query = select(
[p_table.c.foo, v_table.c.bar, a_table.c.baz],
and_(
p_table.c.id==v_table.c.p_id,
v_table.c.a_id==a_table.c.id,
not_(p_table.c.id.in_(
select(
[p_table.c.id],
and_(
p_table.c.id==v_table.c.p_id,
v_table.c.a_id==a_table.c.id,
or_(
p_table.c.foo=='foo2',
v_table.c.bar=='bar3'
)
)
)
))
)
)
try:
for result in query.execute():
print result
finally:
metadata.drop_all()
#---------------------------------------------------------------------#
It appears to me that MySQL doesn't like it when you don't specify a
FROM in the subselect, whereas PostgreSQL and SQLite don't care.
--
Jonathan LaCour
http://cleverdevil.org
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
