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.
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.
