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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
