ok solved it :) it seems postgresql needs alias everywhere !
from sqlalchemy import Table, Column, Integer, String, MetaData, Date, Text,
Float, alias
from sqlalchemy.dialects import postgresql
from sqlalchemy.dialects.postgresql import array
from sqlalchemy.sql import column
from sqlalchemy.sql import select
from sqlalchemy import create_engine
from utils import unnest_func
engine = create_engine('postgresql://postgres:postgres@localhost:5433/sfj',
echo=True)
metadata = MetaData()
lake = Table('lake', metadata,
Column('time', Date, primary_key=True),
Column('ticker', Text, primary_key=True),
Column('field1', Integer),
Column('field2', String),
Column('field3', Float),
Column('field4', String),
)
conn = engine.connect()
s = """
select l.field3, l.field2, l.field4, l.field1
from lake l
join (
select x.id, x.ordering
from unnest(array ['ticker3','ticker1', 'ticker2']) with ordinality
as x (id, ordering)) as r on l.ticker = r.id
where time = '2019-06-28'
order by r.ordering;
"""
result = conn.execute(s)
print('s')
print(result.fetchall())
l = alias(lake)
fields_asked = ['field3', 'field2', 'field4', 'field1']
columns_asked = [column(fa) for fa in fields_asked]
ticker_arr = array(['ticker3', 'ticker1', 'ticker2'])
x = unnest_func(ticker_arr).alias('x')
r = select([x.c.unnest, x.c.ordinality]).select_from(x).alias('r')
stmt = select(columns_asked).select_from(l.join(r, l.c.ticker ==
r.c.unnest)).where(
l.c.time == '2019-06-28').order_by(r.c.ordinality)
print('stmt')
print(str(stmt.compile(dialect=postgresql.dialect())))
result = conn.execute(stmt)
print(result.fetchall())
conn.close()
On Saturday, June 29, 2019 at 3:23:43 PM UTC+2, Benoit Barthelet wrote:
>
> I'm trying to "translate" this query in sqlalchemy core.
>
> s="""
> select l.field3, l.field2, l.field4, l.field1
> from lake l
> join (
> select *
> from unnest(array['ticker3','ticker1', 'ticker2']) with ordinality
> ) as x (id, ordering) on l.ticker = x.id
> where time = '2019-06-28'
> order by x.ordering;
> """
>
>
> I managed to get quite close thanks to recipes from
> https://github.com/sqlalchemy/sqlalchemy/issues/3566
>
> However I fail at getting a proper alias for the left part of the join.
> The issue mentions:
>
> To get the form with "AS x(unnest, ordinality)" you'd need to add another
>> construct like a subclass of Alias or something, however it seems like the
>> "unnest" construct can be aliased in the tradtional way and the column
>> names become "(function name, ordinality)". Again the amount of special
>> functions and behaviors that would be part of a standard feature keep on
>> growing, making this a difficult feature to add as "done" without missing
>> one of PG's weird non-SQL things.
>
>
> I've not been able to write the *AS* part of the query as described above.
>
> Here's where I'm at (see below for full code)
>
> I chncked the query in several parts and ultimately I'd like to join s1
> with s3, s1 works fine, s3 too, but s3 miss the alias to be able to perform
> the join
>
> - s1 (SELECT field3, field2, field4, field1 FROM lake)
> - s3 (SELECT * FROM unnest(ARRAY[%(param_1)s, %(param_2)s,
> %(param_3)s]) WITH ORDINALITY AS anon_1),
>
>
> from sqlalchemy import Table, Column, Integer, String, MetaData, Date, Text,
> Float, func, alias
> from sqlalchemy.dialects.postgresql import array
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.sql import column
> from sqlalchemy.sql import select
>
> from sqlalchemy import create_engine
>
> from utils import ColumnFunction
>
> engine = create_engine('postgresql://postgres:postgres@localhost:5433/sfj',
> echo=True)
>
> metadata = MetaData()
> lake = Table('lake', metadata,
> Column('time', Date, primary_key=True),
> Column('ticker', Text, primary_key=True),
> Column('field1', Integer),
> Column('field2', String),
> Column('field3', Float),
> Column('field4', String),
> )
> conn = engine.connect()
>
>
> s="""
> select l.field3, l.field2, l.field4, l.field1
> from lake l
> join (
> select *
> from unnest(array['ticker3','ticker1', 'ticker2']) with ordinality
> ) as x (id, ordering) on l.ticker = x.id
> where time = '2019-06-28'
> order by x.ordering;
> """
>
> fields_asked = ['field3', 'field2', 'field4', 'field1']
> columns_asked = [column(fa) for fa in fields_asked]
> s1 = select(columns_asked, from_obj=lake)
> result = conn.execute(s1)
> print('s1')
> # seems ok
> # SELECT field3, field2, field4, field1 FROM lake
> print(result.fetchall())
>
> ticker_arr= array(['ticker3', 'ticker1', 'ticker2'])
> s2 = select(['*']).select_from(alias(func.unnest(ticker_arr)))
> result = conn.execute(s2)
> print('s2')
> print(result.fetchall())
>
>
> class unnest_func(ColumnFunction):
> name = 'unnest'
> column_names = ['unnest', 'ordinality']
>
>
> @compiles(unnest_func)
> def _compile_unnest_func(element, compiler, **kw):
> return compiler.visit_function(element, **kw) + " WITH ORDINALITY"
>
>
> s3 = select(['*']).select_from(alias(unnest_func(ticker_arr)))
> result = conn.execute(s3)
> print('s3')
> # seems ok
> # SELECT * FROM unnest(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) WITH
> ORDINALITY AS anon_1
> print(result.fetchall())
>
>
> # s4 = s1.join(s3)
> # result = conn.execute(s4)
> # print('s4')
> # print(result.fetchall())
>
>
> conn.close()
>
>
>
>
>
>
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/65a1b067-d388-4823-9203-77dae332751e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
