Hi Mike,
I've been playing around with the Postgresql dialect array() type a bit
more and have something that appears to work, at least for the small case
that I'm trying to solve for myself. I'd like to check whether I'm on the
right track with how I'm approaching this though so please find a patch
(against the current master) attached, as well as a small update to the
example script to use the array(selectable).contained_by(array(literal))
syntax I described in my previous message.
One thing that surprised me when doing this against the master branch was
an error that Subquery no longer has an .as_scalar() method; but then
removing as_scalar() altogether from that query yielded a correct query, so
something (excellent) has changed there in 1.4
On Fri, Aug 9, 2019 at 11:29 AM Elmer de Looff <elmer.delo...@gmail.com>
wrote:
> 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
>
--
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/CAA7D1eG%3DbW3zg8sHw%3DkSsE6g9T%3DvqArVWBLYCM8WvdsNKs3g2Q%40mail.gmail.com.
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql.array import (
array,
CONTAINS)
from sqlalchemy.ext.declarative import declarative_base
SELECTION = [3, 10, 18]
SIZE_CODE = 'M'
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)
session = sa.orm.sessionmaker(bind=engine)()
# Text-based selection example
raw_select = sa.text("""
SELECT :selection <@ ARRAY(
SELECT id
FROM city
WHERE size_code = :size_code)""")
parameterized = raw_select.params(
selection=SELECTION,
size_code=SIZE_CODE)
print('*** [RAW] selection contained by query result: {}\n'.format(
engine.execute(parameterized).scalar()))
orm_city_ids_array = array(
session.query(City.id).filter_by(size_code=SIZE_CODE).subquery())
print('*** [ORM] Selection contained by query result: {}\n'.format(
session.query(array(SELECTION).contained_by(orm_city_ids_array))
.scalar()))
print('*** [ORM] Query result contains selection: {}\n'.format(
session.query(orm_city_ids_array.contains(SELECTION)).scalar()))
if __name__ == '__main__':
main()
From 211e56e0e836ae1889440b2387cc80b091d1e056 Mon Sep 17 00:00:00 2001
From: Elmer de Looff <elmer.delo...@gmail.com>
Date: Mon, 12 Aug 2019 11:29:45 +0200
Subject: [PATCH] Adds support for ARRAY() construction from a selectable,
rather than literal-only.
- Adds a new visitor for 'array_from_selectable';
- Alters __visitor_name__ of Postgresql 'array' type to be dependent on whether
the given value was an expression.Selectable instance or an (assumed) iterable.
---
lib/sqlalchemy/dialects/postgresql/array.py | 18 +++++++++++++++---
lib/sqlalchemy/dialects/postgresql/base.py | 3 +++
2 files changed, 18 insertions(+), 3 deletions(-)
diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py
index 81bde2a02..a394c1122 100644
--- a/lib/sqlalchemy/dialects/postgresql/array.py
+++ b/lib/sqlalchemy/dialects/postgresql/array.py
@@ -97,10 +97,14 @@ class array(expression.Tuple):
"""
- __visit_name__ = "array"
-
def __init__(self, clauses, **kw):
- super(array, self).__init__(*clauses, **kw)
+ if isinstance(clauses, expression.Selectable):
+ self.from_select = True
+ super(array, self).__init__(clauses, **kw)
+ else:
+ self.from_select = False
+ super(array, self).__init__(*clauses, **kw)
+
if isinstance(self.type, ARRAY):
self.type = ARRAY(
self.type.item_type,
@@ -141,6 +145,14 @@ class array(expression.Tuple):
else:
return self
+ def __visit_name__(self):
+ if self.from_select:
+ return "array_from_select"
+ else:
+ return "array"
+
+ __visit_name__ = property(__visit_name__)
+
CONTAINS = operators.custom_op("@>", precedence=5)
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 8fbd97ebe..ab2a6a2f0 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1553,6 +1553,9 @@ class PGCompiler(compiler.SQLCompiler):
def visit_array(self, element, **kw):
return "ARRAY[%s]" % self.visit_clauselist(element, **kw)
+ def visit_array_from_select(self, element, **kw):
+ return "ARRAY(%s)" % self.visit_clauselist(element, **kw)
+
def visit_slice(self, element, **kw):
return "%s:%s" % (
self.process(element.start, **kw),
--
2.20.1
