On Tue, Aug 26, 2014 at 5:49 PM, Anthony Le <[email protected]> wrote:
> Sorry for the multiple attempts at posting. I am not sure if posts are time
> delayed but I am not seeing what I thought I posted. So I am trying one more
> time. 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 just like above. So the
> attbribute error is caused by trying to access this column. 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.)?
>
I'm not certain, but you might be able to use:
func.max(Run.start_time).label('max_start_time')
and refer to it as:
sub_query.c.max_start_time
Hope that helps,
Simon
--
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.
