[sqlalchemy] Getting Subquery with labels in SQLAlchemy 0.7.4 to Work

[Anthony Phan Le]

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
pha...@amyris.com<mailto:pha...@amyris.com>
1-510-450-0761 Tel (ext 701)
1-510-225-2645 Fax

[cid:image001.png@01CFC076.54560070]<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 pha...@amyris.com<mailto:pha...@amyris.com> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.