I am trying to convert the following SQL statement into an Sqlalchemy ORM
(Version 0.7.4) statement:
SELECT [hermes_stage].[dbo].[run].[tank]
,[id]
,[experiment_id]
,[local_id]
,[start_time]
,[stop_time]
FROM [some_db].[run]
INNER JOIN (
Select max(start_time) as LatestDate, [tank]
from [hermes_stage].[dbo].[run]
Group by [tank]) submax ON
[run].start_time = submax.LatestDate
WHERE some_condition = True and start_time is not NULL and stop_time is
NULL and run.tank in ('some list of labels')
ORDER BY tank
Note the parts highlighted in yellow, labeling max(start_time), and the
part that joins the subquery with the main query.
Strategy: I was planning on using a subquery() to generate the query within
the inner join.
sub_query = model.session.query(func.max(Run.start_time), Run.tank)
.filter(Run.tank.in_(['Q2_A1', 'Q2_A2']))
.group_by(Run.tank)
.with_labels()
.subquery(name = 'sub')
Then:
Some_table.query()
.join(sub_query, Run.start_time==sub_query.c.max_1)
.filter(some_conditions are true)
.all()
This will, however, fail with an “AttributeError: max_1”. I did some more
digging and found that:
In: for item in test.c: print item
Out:
sub.max_1
sub.run_tank
I can do the following:
In: test.c.run_tank
Out: Column(u'run_tank', Unicode(length=10), table=<sub>)
Doing the same for max_1 gives me an attribute error. When I print the
types of each item in c, I get the following.
In:for item in test.c: print type(item)
Out:
<class 'sqlalchemy.sql.expression.ColumnClause'>
<class 'sqlalchemy.schema.Column'>
I think I am getting the error because it is not possible to retrieve a
column name from something of type “ColumnClause”. Two questions come to
mind:1.) How do I convert a ColumnClause into a “Column”? I think I need to
do this so that I can perform the join part correctly. 2.) What alternative
querying strategy would you recommend in the absence of a solution to 1.)?
Thank you for looking at my case!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
