After some experimenting I am able to generate the correct query
by two methods
(1) build query from ORM classes with ORM session.query()
(2) build query from underlying tables with sql expressions
I like the ORM based method better, because the code does not need
to know which columns are involved in the join, and does seem to be
a little cleaner.
I do have a couple of questions about this though:
(1) I can't figure out how to access columns from the RowTuple result
by name. I can only get values by absolute index row[0],row[1], etc.
(2) Are subquery aliases always anonymous or can I control the name?
The table based query satisfies all requirements for getting data. The
only issue is that the code now has more embedded information about
the
relations between tables.
Generated SQL here
http://dpaste.com/hold/116982/
Here is a complete test program; I am using version 0.5.2
http://dpaste.com/hold/116995/
######## Mapped classes based query
# set up subqueries for outer joins
S1 = session.query(SRC1).subquery()
S2 = session.query(SRC2).subquery()
S3 = session.query(SRC3).subquery()
# build up the query
query = session.query(Job.title, Step.name).join(Step)
query = query.outerjoin([S1,S2,S3])
query = query.add_column(S1.c.value)
query = query.add_column(S2.c.value)
query = query.add_column(S3.c.value)
query = query.with_labels()
# select relevant job
query = query.filter(Job.id==2)
for r in query:
print r # how to get named access to columns? r[0],r[1],... works
# r.job_title, etc. doesn't
######## Table based query
# get access to underlying tables
J = class_mapper(Job).mapped_table
S = class_mapper(Step).mapped_table
I = class_mapper(Input).mapped_table
# set up some alias names for outerjoins
S1 = select([I], I.c.kind=='SRC1')\
.with_only_columns([I.c.step_id,I.c.value]).alias('S1')
S2 = select([I], I.c.kind=='SRC2')\
.with_only_columns([I.c.step_id,I.c.value]).alias('S2')
S3 = select([I], I.c.kind=='SRC3')\
.with_only_columns([I.c.step_id,I.c.value]).alias('S3')
# build a query
query = J.join(S)
query = query.outerjoin(S1, S.c.id==S1.c.step_id)
query = query.outerjoin(S2, S.c.id==S2.c.step_id)
query = query.outerjoin(S3, S.c.id==S3.c.step_id)
query = query.select(use_labels=True)
query = query.with_only_columns([J.c.title,S.c.name,
S1.c.value,S2.c.value,S3.c.value])
# select rows of interest
query = query.where(J.c.id==1)
for r in conn.execute(query):
print r.job_title, r.step_name, r.S1_value, r.S2_value, r.S3_value
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
