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!
Anthony Phan Le | Software Developer 2
Amyris Inc. | http://www.amyris.com/
5885 Hollis St. Suite 100
Emeryville, CA 94608
[email protected]<mailto:[email protected]>
1-510-450-0761 Tel (ext 701)
1-510-225-2645 Fax
[cid:[email protected]]<http://www.amyris.com/>
>>>>> Notice of Confidentiality <<<<<
The information contained in this e-mail message or any attachment(s) may be
confidential and/or privileged and is intended for use only by the
individual(s) to whom this message is addressed. If you are not the intended
recipient, any dissemination, distribution, copying, or use is strictly
prohibited. If you receive this e-mail message in error, please e-mail the
sender at [email protected]<mailto:[email protected]> and destroy this message
and remove the transmission from all computer directories (including e-mail
servers).
--
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.
