Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/array.py : 42%

Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# postgresql/array.py
2# Copyright (C) 2005-2020 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: http://www.opensource.org/licenses/mit-license.php
8import re
10from ... import types as sqltypes
11from ... import util
12from ...sql import expression
13from ...sql import operators
16def Any(other, arrexpr, operator=operators.eq):
17 """A synonym for the :meth:`.ARRAY.Comparator.any` method.
19 This method is legacy and is here for backwards-compatibility.
21 .. seealso::
23 :func:`_expression.any_`
25 """
27 return arrexpr.any(other, operator)
30def All(other, arrexpr, operator=operators.eq):
31 """A synonym for the :meth:`.ARRAY.Comparator.all` method.
33 This method is legacy and is here for backwards-compatibility.
35 .. seealso::
37 :func:`_expression.all_`
39 """
41 return arrexpr.all(other, operator)
44class array(expression.Tuple):
46 """A PostgreSQL ARRAY literal.
48 This is used to produce ARRAY literals in SQL expressions, e.g.::
50 from sqlalchemy.dialects.postgresql import array
51 from sqlalchemy.dialects import postgresql
52 from sqlalchemy import select, func
54 stmt = select([
55 array([1,2]) + array([3,4,5])
56 ])
58 print(stmt.compile(dialect=postgresql.dialect()))
60 Produces the SQL::
62 SELECT ARRAY[%(param_1)s, %(param_2)s] ||
63 ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1
65 An instance of :class:`.array` will always have the datatype
66 :class:`_types.ARRAY`. The "inner" type of the array is inferred from
67 the values present, unless the ``type_`` keyword argument is passed::
69 array(['foo', 'bar'], type_=CHAR)
71 Multidimensional arrays are produced by nesting :class:`.array` constructs.
72 The dimensionality of the final :class:`_types.ARRAY`
73 type is calculated by
74 recursively adding the dimensions of the inner :class:`_types.ARRAY`
75 type::
77 stmt = select([
78 array([
79 array([1, 2]), array([3, 4]), array([column('q'), column('x')])
80 ])
81 ])
82 print(stmt.compile(dialect=postgresql.dialect()))
84 Produces::
86 SELECT ARRAY[ARRAY[%(param_1)s, %(param_2)s],
87 ARRAY[%(param_3)s, %(param_4)s], ARRAY[q, x]] AS anon_1
89 .. versionadded:: 1.3.6 added support for multidimensional array literals
91 .. seealso::
93 :class:`_postgresql.ARRAY`
95 """
97 __visit_name__ = "array"
99 def __init__(self, clauses, **kw):
100 super(array, self).__init__(*clauses, **kw)
101 if isinstance(self.type, ARRAY):
102 self.type = ARRAY(
103 self.type.item_type,
104 dimensions=self.type.dimensions + 1
105 if self.type.dimensions is not None
106 else 2,
107 )
108 else:
109 self.type = ARRAY(self.type)
111 def _bind_param(self, operator, obj, _assume_scalar=False, type_=None):
112 if _assume_scalar or operator is operators.getitem:
113 return expression.BindParameter(
114 None,
115 obj,
116 _compared_to_operator=operator,
117 type_=type_,
118 _compared_to_type=self.type,
119 unique=True,
120 )
122 else:
123 return array(
124 [
125 self._bind_param(
126 operator, o, _assume_scalar=True, type_=type_
127 )
128 for o in obj
129 ]
130 )
132 def self_group(self, against=None):
133 if against in (operators.any_op, operators.all_op, operators.getitem):
134 return expression.Grouping(self)
135 else:
136 return self
139CONTAINS = operators.custom_op("@>", precedence=5)
141CONTAINED_BY = operators.custom_op("<@", precedence=5)
143OVERLAP = operators.custom_op("&&", precedence=5)
146class ARRAY(sqltypes.ARRAY):
148 """PostgreSQL ARRAY type.
150 .. versionchanged:: 1.1 The :class:`_postgresql.ARRAY` type is now
151 a subclass of the core :class:`_types.ARRAY` type.
153 The :class:`_postgresql.ARRAY` type is constructed in the same way
154 as the core :class:`_types.ARRAY` type; a member type is required, and a
155 number of dimensions is recommended if the type is to be used for more
156 than one dimension::
158 from sqlalchemy.dialects import postgresql
160 mytable = Table("mytable", metadata,
161 Column("data", postgresql.ARRAY(Integer, dimensions=2))
162 )
164 The :class:`_postgresql.ARRAY` type provides all operations defined on the
165 core :class:`_types.ARRAY` type, including support for "dimensions",
166 indexed access, and simple matching such as
167 :meth:`.types.ARRAY.Comparator.any` and
168 :meth:`.types.ARRAY.Comparator.all`. :class:`_postgresql.ARRAY`
169 class also
170 provides PostgreSQL-specific methods for containment operations, including
171 :meth:`.postgresql.ARRAY.Comparator.contains`
172 :meth:`.postgresql.ARRAY.Comparator.contained_by`, and
173 :meth:`.postgresql.ARRAY.Comparator.overlap`, e.g.::
175 mytable.c.data.contains([1, 2])
177 The :class:`_postgresql.ARRAY` type may not be supported on all
178 PostgreSQL DBAPIs; it is currently known to work on psycopg2 only.
180 Additionally, the :class:`_postgresql.ARRAY`
181 type does not work directly in
182 conjunction with the :class:`.ENUM` type. For a workaround, see the
183 special type at :ref:`postgresql_array_of_enum`.
185 .. seealso::
187 :class:`_types.ARRAY` - base array type
189 :class:`_postgresql.array` - produces a literal array value.
191 """
193 class Comparator(sqltypes.ARRAY.Comparator):
195 """Define comparison operations for :class:`_types.ARRAY`.
197 Note that these operations are in addition to those provided
198 by the base :class:`.types.ARRAY.Comparator` class, including
199 :meth:`.types.ARRAY.Comparator.any` and
200 :meth:`.types.ARRAY.Comparator.all`.
202 """
204 def contains(self, other, **kwargs):
205 """Boolean expression. Test if elements are a superset of the
206 elements of the argument array expression.
207 """
208 return self.operate(CONTAINS, other, result_type=sqltypes.Boolean)
210 def contained_by(self, other):
211 """Boolean expression. Test if elements are a proper subset of the
212 elements of the argument array expression.
213 """
214 return self.operate(
215 CONTAINED_BY, other, result_type=sqltypes.Boolean
216 )
218 def overlap(self, other):
219 """Boolean expression. Test if array has elements in common with
220 an argument array expression.
221 """
222 return self.operate(OVERLAP, other, result_type=sqltypes.Boolean)
224 comparator_factory = Comparator
226 def __init__(
227 self, item_type, as_tuple=False, dimensions=None, zero_indexes=False
228 ):
229 """Construct an ARRAY.
231 E.g.::
233 Column('myarray', ARRAY(Integer))
235 Arguments are:
237 :param item_type: The data type of items of this array. Note that
238 dimensionality is irrelevant here, so multi-dimensional arrays like
239 ``INTEGER[][]``, are constructed as ``ARRAY(Integer)``, not as
240 ``ARRAY(ARRAY(Integer))`` or such.
242 :param as_tuple=False: Specify whether return results
243 should be converted to tuples from lists. DBAPIs such
244 as psycopg2 return lists by default. When tuples are
245 returned, the results are hashable.
247 :param dimensions: if non-None, the ARRAY will assume a fixed
248 number of dimensions. This will cause the DDL emitted for this
249 ARRAY to include the exact number of bracket clauses ``[]``,
250 and will also optimize the performance of the type overall.
251 Note that PG arrays are always implicitly "non-dimensioned",
252 meaning they can store any number of dimensions no matter how
253 they were declared.
255 :param zero_indexes=False: when True, index values will be converted
256 between Python zero-based and PostgreSQL one-based indexes, e.g.
257 a value of one will be added to all index values before passing
258 to the database.
260 .. versionadded:: 0.9.5
263 """
264 if isinstance(item_type, ARRAY):
265 raise ValueError(
266 "Do not nest ARRAY types; ARRAY(basetype) "
267 "handles multi-dimensional arrays of basetype"
268 )
269 if isinstance(item_type, type):
270 item_type = item_type()
271 self.item_type = item_type
272 self.as_tuple = as_tuple
273 self.dimensions = dimensions
274 self.zero_indexes = zero_indexes
276 @property
277 def hashable(self):
278 return self.as_tuple
280 @property
281 def python_type(self):
282 return list
284 def compare_values(self, x, y):
285 return x == y
287 def _proc_array(self, arr, itemproc, dim, collection):
288 if dim is None:
289 arr = list(arr)
290 if (
291 dim == 1
292 or dim is None
293 and (
294 # this has to be (list, tuple), or at least
295 # not hasattr('__iter__'), since Py3K strings
296 # etc. have __iter__
297 not arr
298 or not isinstance(arr[0], (list, tuple))
299 )
300 ):
301 if itemproc:
302 return collection(itemproc(x) for x in arr)
303 else:
304 return collection(arr)
305 else:
306 return collection(
307 self._proc_array(
308 x,
309 itemproc,
310 dim - 1 if dim is not None else None,
311 collection,
312 )
313 for x in arr
314 )
316 @util.memoized_property
317 def _require_cast(self):
318 return self._against_native_enum or isinstance(
319 self.item_type, sqltypes.JSON
320 )
322 @util.memoized_property
323 def _against_native_enum(self):
324 return (
325 isinstance(self.item_type, sqltypes.Enum)
326 and self.item_type.native_enum
327 )
329 def bind_expression(self, bindvalue):
330 if self._require_cast:
331 return expression.cast(bindvalue, self)
332 else:
333 return bindvalue
335 def bind_processor(self, dialect):
336 item_proc = self.item_type.dialect_impl(dialect).bind_processor(
337 dialect
338 )
340 def process(value):
341 if value is None:
342 return value
343 else:
344 return self._proc_array(
345 value, item_proc, self.dimensions, list
346 )
348 return process
350 def result_processor(self, dialect, coltype):
351 item_proc = self.item_type.dialect_impl(dialect).result_processor(
352 dialect, coltype
353 )
355 def process(value):
356 if value is None:
357 return value
358 else:
359 return self._proc_array(
360 value,
361 item_proc,
362 self.dimensions,
363 tuple if self.as_tuple else list,
364 )
366 if self._against_native_enum:
367 super_rp = process
369 def handle_raw_string(value):
370 inner = re.match(r"^{(.*)}$", value).group(1)
371 return inner.split(",") if inner else []
373 def process(value):
374 if value is None:
375 return value
376 # isinstance(value, util.string_types) is required to handle
377 # the # case where a TypeDecorator for and Array of Enum is
378 # used like was required in sa < 1.3.17
379 return super_rp(
380 handle_raw_string(value)
381 if isinstance(value, util.string_types)
382 else value
383 )
385 return process