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 [email protected]. To post to this group, send email to [email protected]. 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/28e00515-510b-48b7-9cbf-f35db45f74f7%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
