Hi Michael et al,
I can not find a way to express my query in SQLAlchemy. What I want to
do is to load all ORM-mapped objects for which another query can not
yield any result (not exists):
select * from entry where not exists (select 1 from lookup where
lookup.skipped_id = entry.id)
For my application, the subquery is a bit more complicated (it's a join
over two tables). In any case, I can not correlate the ORM query with
the sql query.
The attached example prints the following query (SQLAlchemy 0.6.8):
SELECT entry.id AS entry_id, entry.content AS entry_content
FROM entry
WHERE NOT (EXISTS (SELECT 1
FROM lookup))
How can I correlate the subquery in this context? There is a bunch of
correlate methods (Query.correlate, Select.correlate) but I do not
really understand how to make use of it here.
Do you have a hint for me?
Greetings, Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:[email protected]
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
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.
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
entry_table = Table("entry", metadata,
Column("id", Integer, primary_key=True),
Column("content", String)
)
lookup_table = Table("lookup", metadata,
Column("skipped_id", Integer, ForeignKey(entry_table.c.id)))
class Entry(object): pass
mapper(Entry, entry_table)
engine = create_engine("sqlite:///", echo=True)
metadata.create_all(engine)
Session = sessionmaker(engine)
session = Session()
for v in (1, 3, 7, 9):
session.execute(lookup_table.insert().values(skipped_id=v))
session.query(Entry).filter(
not_(exists("1", from_obj=lookup_table)
.correlate(entry_table))).all()
