I've been milling over your comment regarding the func.array vs array approaches, and that makes a lot of sense. Currently any array(iterable) statement gets turned into an ARRAY[] literal in Postgres. Extending this to emit an ARRAY() constructor when array(query) is called seems like a reasonable approach. Turning this expression into a scalar one where necessary could be something that is done as part of that process. Going by Postgres documentation, this query should return a single column, but it seems reasonable to have the user be responsible for honoring that limitation and not SQLAlchemy.
Having array(expression) also result in working contains/contained_by methods would be a nice bonus, as these don't work for the func.array() approach. I took a brief look at the code for array, but I'm not really sure how the whole flow from Python statement to emitted SQL goes. It does seems fairly wedded to the concept of being a literal array (looking at the superclass) for now, so my suggested extension might be a bit easier said than implemented. On Thu, Aug 8, 2019 at 7:05 PM Mike Bayer <mike...@zzzcomputing.com> wrote: > > > On Thu, Aug 8, 2019, at 11:56 AM, Elmer de Looff wrote: > > Thanks for the quick response and solution! > > Having the answer, it seems obvious enough, but getting to the solution > from the problem was hard, despite excellent documentation, Not sure it > deserves an FAQ entry as it might be a bit of an infrequent problem, but > maybe that'll help the next person. I'd be happy to take a stab at an entry > if you're interested. > > > PostgreSQL's special syntaxes are a much bigger issue than just this. > Many syntaxes are not yet supported and require recipes as listed at > https://github.com/sqlalchemy/sqlalchemy/issues/3566 . I would think > the Postgresql dialect documentation needs an entire section dedicated to > special PG patterns, preferably in tandem with the structures in issue 3566 > being implemented as features. > > also, the approach I just gave you with as_scalar() might not be > generalizable as of yet , in that it's not clear what the approach is for > multidimensional arrays - as_scalar() really means a SELECT that returns a > single column / single row, and already we are somewhat repurposing it here > to represent a SELECT that represents multiple rows. This usage should > be clarified. > > Additionally, the use of func.array() works in this case but ideally you'd > be using the sqlalchemy.dialects.postgresql.array() construct since this is > not as much a SQL function as it is a first class datastructure in > Postgresql. Multidimensional support was added to this construct in > https://github.com/sqlalchemy/sqlalchemy/issues/4756 but I don't think it > as of yet supports arbitrary SQL expressions, which it should. > > Basically, this whole area of SQLAlchemy is raw and under-developed. If > we document approaches that just happen to work right now, but aren't > tested or supported or part of an overall strategy, then we are creating > poor assumptions. So I'd prefer we build out real patterns and have > them tested before we document them. > > > > > > On Thu, Aug 8, 2019 at 5:17 PM Mike Bayer <mike...@zzzcomputing.com> > wrote: > > > > > On Thu, Aug 8, 2019, at 9:48 AM, Elmer de Looff wrote: > > Hi, > > I'm trying to create a query to check whether a small number of given keys > are all present within a selection of a table. Postgres provides array > types/functions for this to check sub/superset properties, which seem to do > what I want. The query I'm trying to create is one of the following form: > > > > thanks for the clear test case, which allows me to just make your code > work. > > The ARRAY(select) is against a scalar list, so use as_scalar() so that the > SELECT becomes a self-contained subquery: > > city_ids = sa.select([City.id]).where(City.size_code == > "M").as_scalar() > check = sa.select( > [array([3, 10, 18]).contained_by(sa.func.array(city_ids))] > ) > > > > > > SELECT :selection <@ ARRAY( > SELECT id > FROM city > WHERE size_code = :size_code) > > Wrapping this in a text clause, adding parameters and executing it works > without a hitch: > > raw_select = sa.text(""" > SELECT :selection <@ ARRAY( > SELECT id > FROM city > WHERE size_code = :size_code)""") > parameterized = raw_select.params( > selection=[3, 10, 18], > size_code='M') > result = engine.execute(parameterized).scalar() > > However, I'd like to avoid having textual SQL in my codebase as it's more > sensitive to changes in names and generally more error-prone. I'm > struggling converting this to a working Core expression, a spurious > FROM-clause keeps being generated: > > city_ids = sa.select([City.id]).where(City.size_code == 'M') > check = sa.select([ > array([3, 10, 18]).contained_by(sa.func.array(city_ids))]) > engine.execute(check).scalar() > > This results in a Syntax Error being thrown by Postgres: > > sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) subquery in > FROM must have an alias > LINE 4: FROM (SELECT city.id AS id > ^ > HINT: For example, FROM (SELECT ...) [AS] foo. > > [SQL: SELECT ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] <@ array((SELECT > city.id > FROM city > WHERE city.size_code = %(size_code_1)s)) AS anon_1 > FROM (SELECT city.id AS id > FROM city > WHERE city.size_code = %(size_code_1)s)] > [parameters: {'param_1': 3, 'param_2': 10, 'param_3': 18, 'size_code_1': > 'M'}] > (Background on this error at: http://sqlalche.me/e/f405) > > The problem appears to be in the "_froms" list that is non-empty on the > "check" query, but I can't seem to find a way of coercing SQLAlchemy into > not generating that. > > I've attached a minimal script to reproduce the problem. The table is > described though will have to be created still; it need not contain any > data, the problem is one of SQL syntax alone. > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/8437d2b0-7189-4842-b029-e56056a9246a%40googlegroups.com > <https://groups.google.com/d/msgid/sqlalchemy/8437d2b0-7189-4842-b029-e56056a9246a%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > *Attachments:* > > - sqla_select_contained_by.py > > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/127f2b38-0340-490d-8edf-ddb5de0cc20d%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/127f2b38-0340-490d-8edf-ddb5de0cc20d%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > > > > -- > > Elmer > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAA7D1eHmQ2uUrREU25%3DHJrSsO4P1Gy3Lj%3DkrsWE5183X-R%2BYkQ%40mail.gmail.com > <https://groups.google.com/d/msgid/sqlalchemy/CAA7D1eHmQ2uUrREU25%3DHJrSsO4P1Gy3Lj%3DkrsWE5183X-R%2BYkQ%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/60c4dd99-3649-4d0d-ba26-809647cf7de2%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/60c4dd99-3649-4d0d-ba26-809647cf7de2%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > -- Elmer -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAA7D1eEMhS%2BXr7_U1zWK%3D75w_8B%3D%2BCCQCgcbCmzZVjKq65f6-g%40mail.gmail.com.
