generate_series?

[Luke Iannini]

Hi all,
Is there a way to use generate_series with SQLAlchemy?

http://www.postgresql.org/docs/8.2/interactive/functions-srf.html

generate_series = select([column('i')],
from_obj=[func.generate_series(bindparam('start'),
bindparam('end'))])

zrows = select([generate_series.params(start=1,
end=20).c.i]).label('series')

is the best stab I've made at it, but I don't actually understand the
syntax in the PGSQL docs:

select current_date + s.a as dates from generate_series(0,14,7) as
s(a);

That is how I'd like to use it, but I don't know how to translate
"s(a)/s.a" into SQLAlchemy.

As a side question, how would I add static columns to a select
statement? e.g. based on the pseudocode above:
zrows = select([generate_series.params(start=1, end=20).c.i], 0, 0,
0).label('series')
to add 3 columns of 0 value to each row generated.

Cheers
Luke

[Michael Bayer]

On Apr 25, 2008, at 9:04 AM, Luke Iannini wrote:

>
> Hi all,
> Is there a way to use generate_series with SQLAlchemy?
>
> http://www.postgresql.org/docs/8.2/interactive/functions-srf.html
>
> generate_series = select([column('i')],
> from_obj=[func.generate_series(bindparam('start'),
> bindparam('end'))])
>
> zrows = select([generate_series.params(start=1,
> end=20).c.i]).label('series')
>
> is the best stab I've made at it, but I don't actually understand the
> syntax in the PGSQL docs:
>
> select current_date + s.a as dates from generate_series(0,14,7) as
> s(a);
>

that particular syntax is an aliasing syntax that we plan on
supporting in the near future, so the above would look possibly like

s = func.generate_series(4,5,6).alias(cols=['a'])

select([func.current_date() + s.c.a])

you can hardwire this stuff using text right now:

select([(func.current_date() +
literal_column
("s.a")).label("dates")]).select_from("generate_series(0, 14, 7) as
s(a)")

I just noticed that the text() construct, which would allow the
bindparams to happen, is not being accepted into select_from() so i've
added ticket #1014 for that.

> As a side question, how would I add static columns to a select
> statement? e.g. based on the pseudocode above:
> zrows = select([generate_series.params(start=1, end=20).c.i], 0, 0,
> 0).label('series')
> to add 3 columns of 0 value to each row generated.

you should be able to put plain strings in the columns clause:

select(["foo", "bar", "bat"])

this is shorthand for using the "literal_column()" construct which you
can use anywhere in a column expression to produce textual SQL
expressions.

[Luke Iannini]

On Apr 25, 8:46 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Apr 25, 2008, at 9:04 AM, Luke Iannini wrote:
>
>
>
>
>
> > Hi all,
> > Is there a way to use generate_series with SQLAlchemy?
>
> >http://www.postgresql.org/docs/8.2/interactive/functions-srf.html
>
> > generate_series = select([column('i')],
> >    from_obj=[func.generate_series(bindparam('start'),
> > bindparam('end'))])
>
> > zrows = select([generate_series.params(start=1,
> > end=20).c.i]).label('series')
>
> > is the best stab I've made at it, but I don't actually understand the
> > syntax in the PGSQL docs:
>
> > select current_date + s.a as dates from generate_series(0,14,7) as
> > s(a);
>
> that particular syntax is an aliasing syntax that we plan on  
> supporting in the near future, so the above would look possibly like
>
> s = func.generate_series(4,5,6).alias(cols=['a'])
>
> select([func.current_date() + s.c.a])
>
> you can hardwire this stuff using text right now:
>
> select([(func.current_date() +  
> literal_column
> ("s.a")).label("dates")]).select_from("generate_series(0, 14, 7) as  
> s(a)")

Thanks so much Michael! That worked perfectly.

> I just noticed that the text() construct, which would allow the  
> bindparams to happen, is not being accepted into select_from() so i've  
> added ticket  #1014 for that.
>
> > As a side question, how would I add static columns to a select
> > statement? e.g. based on the pseudocode above:
> > zrows = select([generate_series.params(start=1, end=20).c.i], 0, 0,
> > 0).label('series')
> > to add 3 columns of 0 value to each row generated.
>
> you should be able to put plain strings in the columns clause:
>
> select(["foo", "bar", "bat"])
>
> this is shorthand for using the "literal_column()" construct which you  
> can use anywhere in a column expression to produce textual SQL  
> expressions.

Got it. Thanks again

Cheers
Luke

[Luke Iannini]

Hm, yes, so is:

Traceback (most recent call last):
File "/Users/LukeIannini/Checkout/trunk/adpinion_web/
HistoryAlchemy.py", line 46, in <module>
allstats = union_all(stats, zeros)
File "/Library/Python/2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/sql/expression.py", line 498, in union_all
return _compound_select('UNION ALL', *selects, **kwargs)
File "/Library/Python/2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/sql/expression.py", line 780, in _compound_select
return CompoundSelect(keyword, *selects, **kwargs)
File "/Library/Python/2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/sql/expression.py", line 2895, in __init__
self.oid_column = self._proxy_column(s.oid_column)
File "/Library/Python/2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/sql/expression.py", line 3323, in oid_column
oid = f.oid_column
AttributeError: '_TextFromClause' object has no attribute 'oid_column'

when trying to union_all the result of a (on its own, seemingly
working) generate_series select a manifestation of the bug you
mentioned or am I doing something else wrong?

Cheers
Luke

[Michael Bayer]

On Apr 26, 2008, at 4:01 AM, Luke Iannini wrote:

>
> Hm, yes, so is:
>
> Traceback (most recent call last):
> File "/Users/LukeIannini/Checkout/trunk/adpinion_web/
> HistoryAlchemy.py", line 46, in <module>
> allstats = union_all(stats, zeros)
> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
> sqlalchemy/sql/expression.py", line 498, in union_all
> return _compound_select('UNION ALL', *selects, **kwargs)
> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
> sqlalchemy/sql/expression.py", line 780, in _compound_select
> return CompoundSelect(keyword, *selects, **kwargs)
> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
> sqlalchemy/sql/expression.py", line 2895, in __init__
> self.oid_column = self._proxy_column(s.oid_column)
> File "/Library/Python/2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
> sqlalchemy/sql/expression.py", line 3323, in oid_column
> oid = f.oid_column
> AttributeError: '_TextFromClause' object has no attribute 'oid_column'
>
> when trying to union_all the result of a (on its own, seemingly
> working) generate_series select a manifestation of the bug you
> mentioned or am I doing something else wrong?

no, thats an entirely different bug :) thats been fixed as of
recent releases so upgrade to 0.4.5.

[Michael Bayer]

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)

[gbr]

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)?

[Michael Bayer]

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.

-- 
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.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[gbr]

`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

[Massimiliano della Rovere]

Years later...

please can you give me some hints on how to write the Alias subclass and the function to decorate with @complile?

I'm not expert with SQLAlchemy internals.

To unsubscribe from this group and stop receiving emails from it, send an email tosqlal...@googlegroups.com.

[Mike Bayer]

On Tue, Jul 30, 2019, at 1:46 PM, Massimiliano della Rovere wrote:

Years later...

please can you give me some hints on how to write the Alias subclass and the function to decorate with @complile?

I'm not expert with SQLAlchemy internals.

no idea, can you please type out the SQL you wish to generate and the table metadata you are starting with, thanks.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/182b4066-9f7c-4dfa-ae0d-f001af4e686f%40googlegroups.com.