Tobias Bell wrote: > > Hello > > after falling in love with SQLAlchemy I'm trying to transform most of > my raw SQL-Statements into ORM-Queries. > I'm using cx_Oracle 4.3.3, SQLAlchemy 0.5.4p2 and Oracle 10.2.0.4.0 > under Windows XP with Python 2.5.2. > At the moment I'm stuck with this statement (most columns are replaced > with `*') > > -- D) stack_id select > SELECT * > FROM pe_statushistory > WHERE stack_id = :stack_id > UNION ALL > -- C) IN-Clause with subselect > SELECT * > FROM pe_statushistory > WHERE msg_detail2 IN ( > -- A) Document subselect > SELECT fndoc_id > FROM pe_process p > WHERE p.stack_id = :stack_id AND fndoc_id IS > NOT NULL > UNION ALL > -- B) Process subselect > SELECT fndoc_id > FROM pe_document d > WHERE d.stack_id = :stack_id AND fndoc_id IS > NOT NULL) > ORDER BY dbtime DESC, logtime DESC > > The transformation looks like this > > # A) > fdoc_id_from_doc = self.session.query > (pe_model.Document.fndoc_id)\ > .filter(pe_model.Document.stack_id == stack_id)\ > .filter(pe_model.Document.fndoc_id != None) > # B) > fdoc_id_from_proc = self.session.query > (pe_model.Process.fndoc_id)\ > .filter(pe_model.Process.stack_id == stack_id)\ > .filter(pe_model.Process.fndoc_id != None) > > doc_id_sub = fdoc_id_from_doc.union_all(fdoc_id_from_proc) > # C) > history_from_fdoc_id = self.session.query > (pe_model.StatusHistory)\ > .filter(pe_model.StatusHistory.msg_detail2.in_ > (doc_id_sub)) > # D) > history = self.session.query(pe_model.StatusHistory)\ > .filter(pe_model.StatusHistory.stack_id == stack_id)\ > .union_all(history_from_fdoc_id) > > The generated SQL-Statement looks good >>>> print history > SELECT anon1.* > FROM (SELECT * > FROM "POSTEINGANG_ADM".pe_statushistory > WHERE "POSTEINGANG_ADM".pe_statushistory.stack_id > = :stack_id_1 > UNION ALL > SELECT * > FROM "POSTEINGANG_ADM".pe_statushistory > WHERE "POSTEINGANG_ADM".pe_statushistory.msg_detail2 IN ( > SELECT anon_2.fndoc_id > FROM (SELECT > "POSTEINGANG_ADM".pe_document.fndoc_id > AS > fndoc_id > FROM "POSTEINGANG_ADM".pe_document > WHERE > "POSTEINGANG_ADM".pe_document.stack_id = > > :stack_id_2 > AND > "POSTEINGANG_ADM".pe_document.fndoc_id IS NOT NULL > UNION ALL > SELECT > "POSTEINGANG_ADM".pe_process.fndoc_id > AS > fndoc_id > FROM "POSTEINGANG_ADM".pe_process > WHERE > "POSTEINGANG_ADM".pe_process.stack_id = > > :stack_id_3 > AND > "POSTEINGANG_ADM".pe_process.fndoc_id IS NOT NULL) anon_2)) anon_1 > ORDER BY anon_1.dbtime, anon_1.logtime > > When pasted into Toad or SQLPlus the result are expected and match. > But >>>> history.all() > [None] > > How can I help SQLAlchemy to map the results into instances of the > mapped class pe_model.StatusHistory? > > Regards > Tobias Bell
When doing a straight session.query() for full mapped instances (i.e. not individual columns), the mapper only generates instances for rows which have a full non-null primary key value represented in its columns, otherwise the row is skipped. You can make this more lenient by setting "allow_null_pks=True" on your mapper(), which will allow partially null primary keys to still be recognized as PKs. In version 6 this flag will be on by default. --~--~---------~--~----~------------~-------~--~----~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
