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.
