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 <doubl...@directbox.com> 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+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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
