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:
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.
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import array
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class City(Base):
__tablename__ = 'city'
id = sa.Column(sa.Integer, primary_key=True)
size_code = sa.Column(sa.Text)
def main():
engine = sa.create_engine('postgres://elmer@/smallville', echo=True)
# Text-based selection works as intended
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()
print('\nCity codes all match: {}\n'.format(result))
# Expressing this in Core yields unexpected FROM-clause
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()
if __name__ == '__main__':
main()
