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 <[email protected]> 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 [email protected]. >>> 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 [email protected]. >> 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 [email protected]. > 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/60c4dd99-3649-4d0d-ba26-809647cf7de2%40www.fastmail.com.
