`quoted_name` was what was throwing me off in the end. Thanks for the
super-fast response (as usual). If you happen to find the recipe, I'd like
to take a look at it (if not, I stick with your current solution which
seems to work fine).
Thanks
On Wednesday, March 5, 2014 3:14:56 AM UTC+11, Michael Bayer wrote:
>
> if you want to select columns from a function you select from it:
>
> from sqlalchemy import func, select, create_engine, literal_column
> s = select([literal_column("*")]).select_from(func.generate_series(4, 5,
> 6))
>
> the thing with PG’s non-standard SQL syntax in order to give it an alias,
> we have to hack a bit, we can use quoted_name() to create names that
> absolutely will never be quoted, even as the name of an alias:
>
> from sqlalchemy import func, select, create_engine
> from sqlalchemy.sql.elements import quoted_name
>
> s = select([quoted_name("a.s",
> False)]).select_from(func.generate_series(4, 5,
> 6).alias(quoted_name("a(s)", False)))
>
> e = create_engine("postgresql://scott@localhost/test", echo=True)
> print e.execute(s).fetchall()
>
> that’s probably all you need. if you need more, like in-Python SQL
> arithmetic, (e.g. the select object doesn’t have a “s.c.s” attribute), you
> can use a proper column to get you more of that:
>
> s =
> select([literal_column("a.s").label('s')]).select_from(func.generate_series(4,
>
> 5, 6).alias(quoted_name("a(s)", False)))
>
> still further would be creating a subclass of Alias that publishes the
> given column names fully and uses @compiles in order to render. I might
> have given someone that recipe at some point.
>
>
>
>
> On Mar 4, 2014, at 1:39 AM, gbr <doub...@directbox.com <javascript:>>
> wrote:
>
> I know this is an old thread, but there isn't much on the web around
> generate_series and SQLA, so I thought I might revive it.
>
> One of the suggestion was to use:
>
> s = func.generate_series(4,5,6).alias(cols=['a'])
>
> select([func.current_date() + s.c.a])
>
> Unfortunately, alias doesn't take a `cols` argument. What's the correct
> syntax with a more contemporary version of SQLA (>= 0.9)?
>
> On Sunday, April 27, 2008 2:40:35 AM UTC+10, Michael Bayer wrote:
>>
>> OK r4566 of trunk also allows text() within select_from(), so you can
>> use textual bind params (denoted by a colon ':'):
>>
>> generate_series = text("generate_series(:x, :y, :z) as s(a)")
>>
>> s = select([(func.current_date() +
>> literal_column("s.a")).label("dates")]).select_from(generate_series)
>>
>> # load up some parameters:
>>
>> s = s.params(x=5, y=6, z=7)
>>
>>
>>
>>
>>
>>
>>
> --
> 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 tosqlalchemy+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com<javascript:>
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
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/groups/opt_out.
