diff options
Diffstat (limited to 'lib/sqlalchemy/dialects/postgresql')
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/__init__.py | 12 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/array.py | 419 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 449 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/hstore.py | 278 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/json.py | 303 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/psycopg2.py | 2 |
6 files changed, 756 insertions, 707 deletions
diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py index 98fe6f085..46f45a340 100644 --- a/lib/sqlalchemy/dialects/postgresql/__init__.py +++ b/lib/sqlalchemy/dialects/postgresql/__init__.py @@ -12,11 +12,13 @@ base.dialect = psycopg2.dialect from .base import \ INTEGER, BIGINT, SMALLINT, VARCHAR, CHAR, TEXT, NUMERIC, FLOAT, REAL, \ INET, CIDR, UUID, BIT, MACADDR, OID, DOUBLE_PRECISION, TIMESTAMP, TIME, \ - DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect, array, Any, All, \ - TSVECTOR, DropEnumType + DATE, BYTEA, BOOLEAN, INTERVAL, ENUM, dialect, TSVECTOR, DropEnumType, \ + CreateEnumType from .constraints import ExcludeConstraint from .hstore import HSTORE, hstore -from .json import JSON, JSONElement, JSONB +from .json import JSON, JSONB +from .array import array, ARRAY, Any, All + from .ranges import INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, \ TSTZRANGE @@ -26,6 +28,6 @@ __all__ = ( 'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME', 'DATE', 'BYTEA', 'BOOLEAN', 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'Any', 'All', 'array', 'HSTORE', 'hstore', 'INT4RANGE', 'INT8RANGE', 'NUMRANGE', 'DATERANGE', - 'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', 'JSONElement', - 'DropEnumType' + 'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', + 'DropEnumType', 'CreateEnumType', 'ExcludeConstraint' ) diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py new file mode 100644 index 000000000..8c63b43ce --- /dev/null +++ b/lib/sqlalchemy/dialects/postgresql/array.py @@ -0,0 +1,419 @@ +# postgresql/array.py +# Copyright (C) 2005-2015 the SQLAlchemy authors and contributors +# <see AUTHORS file> +# +# This module is part of SQLAlchemy and is released under +# the MIT License: http://www.opensource.org/licenses/mit-license.php + +from .base import ischema_names +from ...sql import expression, operators +from ... import types as sqltypes + +try: + from uuid import UUID as _python_UUID +except ImportError: + _python_UUID = None + + +class Any(expression.ColumnElement): + + """Represent the clause ``left operator ANY (right)``. ``right`` must be + an array expression. + + .. seealso:: + + :class:`.postgresql.ARRAY` + + :meth:`.postgresql.ARRAY.Comparator.any` - ARRAY-bound method + + """ + __visit_name__ = 'any' + + def __init__(self, left, right, operator=operators.eq): + self.type = sqltypes.Boolean() + self.left = expression._literal_as_binds(left) + self.right = right + self.operator = operator + + +class All(expression.ColumnElement): + + """Represent the clause ``left operator ALL (right)``. ``right`` must be + an array expression. + + .. seealso:: + + :class:`.postgresql.ARRAY` + + :meth:`.postgresql.ARRAY.Comparator.all` - ARRAY-bound method + + """ + __visit_name__ = 'all' + + def __init__(self, left, right, operator=operators.eq): + self.type = sqltypes.Boolean() + self.left = expression._literal_as_binds(left) + self.right = right + self.operator = operator + + +class array(expression.Tuple): + + """A Postgresql ARRAY literal. + + This is used to produce ARRAY literals in SQL expressions, e.g.:: + + from sqlalchemy.dialects.postgresql import array + from sqlalchemy.dialects import postgresql + from sqlalchemy import select, func + + stmt = select([ + array([1,2]) + array([3,4,5]) + ]) + + print stmt.compile(dialect=postgresql.dialect()) + + Produces the SQL:: + + SELECT ARRAY[%(param_1)s, %(param_2)s] || + ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1 + + An instance of :class:`.array` will always have the datatype + :class:`.ARRAY`. The "inner" type of the array is inferred from + the values present, unless the ``type_`` keyword argument is passed:: + + array(['foo', 'bar'], type_=CHAR) + + .. versionadded:: 0.8 Added the :class:`~.postgresql.array` literal type. + + See also: + + :class:`.postgresql.ARRAY` + + """ + __visit_name__ = 'array' + + def __init__(self, clauses, **kw): + super(array, self).__init__(*clauses, **kw) + self.type = ARRAY(self.type) + + def _bind_param(self, operator, obj): + return array([ + expression.BindParameter(None, o, _compared_to_operator=operator, + _compared_to_type=self.type, unique=True) + for o in obj + ]) + + def self_group(self, against=None): + return self + + +CONTAINS = operators.custom_op("@>", precedence=5) + +CONTAINED_BY = operators.custom_op("<@", precedence=5) + +OVERLAP = operators.custom_op("&&", precedence=5) + + +class ARRAY(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine): + + """Postgresql ARRAY type. + + Represents values as Python lists. + + An :class:`.ARRAY` type is constructed given the "type" + of element:: + + mytable = Table("mytable", metadata, + Column("data", ARRAY(Integer)) + ) + + The above type represents an N-dimensional array, + meaning Postgresql will interpret values with any number + of dimensions automatically. To produce an INSERT + construct that passes in a 1-dimensional array of integers:: + + connection.execute( + mytable.insert(), + data=[1,2,3] + ) + + The :class:`.ARRAY` type can be constructed given a fixed number + of dimensions:: + + mytable = Table("mytable", metadata, + Column("data", ARRAY(Integer, dimensions=2)) + ) + + This has the effect of the :class:`.ARRAY` type + specifying that number of bracketed blocks when a :class:`.Table` + is used in a CREATE TABLE statement, or when the type is used + within a :func:`.expression.cast` construct; it also causes + the bind parameter and result set processing of the type + to optimize itself to expect exactly that number of dimensions. + Note that Postgresql itself still allows N dimensions with such a type. + + SQL expressions of type :class:`.ARRAY` have support for "index" and + "slice" behavior. The Python ``[]`` operator works normally here, given + integer indexes or slices. Note that Postgresql arrays default + to 1-based indexing. The operator produces binary expression + constructs which will produce the appropriate SQL, both for + SELECT statements:: + + select([mytable.c.data[5], mytable.c.data[2:7]]) + + as well as UPDATE statements when the :meth:`.Update.values` method + is used:: + + mytable.update().values({ + mytable.c.data[5]: 7, + mytable.c.data[2:7]: [1, 2, 3] + }) + + Multi-dimensional array index support is provided automatically based on + either the value specified for the :paramref:`.ARRAY.dimensions` parameter. + E.g. an :class:`.ARRAY` with dimensions set to 2 would return an expression + of type :class:`.ARRAY` for a single index operation:: + + type = ARRAY(Integer, dimensions=2) + + expr = column('x', type) # expr is of type ARRAY(Integer, dimensions=2) + + expr = column('x', type)[5] # expr is of type ARRAY(Integer, dimensions=1) + + An index expression from ``expr`` above would then return an expression + of type Integer:: + + sub_expr = expr[10] # expr is of type Integer + + .. versionadded:: 1.1 support for index operations on multi-dimensional + :class:`.postgresql.ARRAY` objects is added. + + :class:`.ARRAY` provides special methods for containment operations, + e.g.:: + + mytable.c.data.contains([1, 2]) + + For a full list of special methods see :class:`.ARRAY.Comparator`. + + .. versionadded:: 0.8 Added support for index and slice operations + to the :class:`.ARRAY` type, including support for UPDATE + statements, and special array containment operations. + + The :class:`.ARRAY` type may not be supported on all DBAPIs. + It is known to work on psycopg2 and not pg8000. + + See also: + + :class:`.postgresql.array` - produce a literal array value. + + """ + __visit_name__ = 'ARRAY' + + class Comparator( + sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator): + + """Define comparison operations for :class:`.ARRAY`.""" + + def _setup_getitem(self, index): + if isinstance(index, slice): + return_type = self.type + elif self.type.dimensions is None or self.type.dimensions == 1: + return_type = self.type.item_type + else: + adapt_kw = {'dimensions': self.type.dimensions - 1} + return_type = self.type.adapt(self.type.__class__, **adapt_kw) + + return operators.getitem, index, return_type + + def any(self, other, operator=operators.eq): + """Return ``other operator ANY (array)`` clause. + + Argument places are switched, because ANY requires array + expression to be on the right hand-side. + + E.g.:: + + from sqlalchemy.sql import operators + + conn.execute( + select([table.c.data]).where( + table.c.data.any(7, operator=operators.lt) + ) + ) + + :param other: expression to be compared + :param operator: an operator object from the + :mod:`sqlalchemy.sql.operators` + package, defaults to :func:`.operators.eq`. + + .. seealso:: + + :class:`.postgresql.Any` + + :meth:`.postgresql.ARRAY.Comparator.all` + + """ + return Any(other, self.expr, operator=operator) + + def all(self, other, operator=operators.eq): + """Return ``other operator ALL (array)`` clause. + + Argument places are switched, because ALL requires array + expression to be on the right hand-side. + + E.g.:: + + from sqlalchemy.sql import operators + + conn.execute( + select([table.c.data]).where( + table.c.data.all(7, operator=operators.lt) + ) + ) + + :param other: expression to be compared + :param operator: an operator object from the + :mod:`sqlalchemy.sql.operators` + package, defaults to :func:`.operators.eq`. + + .. seealso:: + + :class:`.postgresql.All` + + :meth:`.postgresql.ARRAY.Comparator.any` + + """ + return All(other, self.expr, operator=operator) + + def contains(self, other, **kwargs): + """Boolean expression. Test if elements are a superset of the + elements of the argument array expression. + """ + return self.operate(CONTAINS, other, result_type=sqltypes.Boolean) + + def contained_by(self, other): + """Boolean expression. Test if elements are a proper subset of the + elements of the argument array expression. + """ + return self.operate( + CONTAINED_BY, other, result_type=sqltypes.Boolean) + + def overlap(self, other): + """Boolean expression. Test if array has elements in common with + an argument array expression. + """ + return self.operate(OVERLAP, other, result_type=sqltypes.Boolean) + + comparator_factory = Comparator + + def __init__(self, item_type, as_tuple=False, dimensions=None, + zero_indexes=False): + """Construct an ARRAY. + + E.g.:: + + Column('myarray', ARRAY(Integer)) + + Arguments are: + + :param item_type: The data type of items of this array. Note that + dimensionality is irrelevant here, so multi-dimensional arrays like + ``INTEGER[][]``, are constructed as ``ARRAY(Integer)``, not as + ``ARRAY(ARRAY(Integer))`` or such. + + :param as_tuple=False: Specify whether return results + should be converted to tuples from lists. DBAPIs such + as psycopg2 return lists by default. When tuples are + returned, the results are hashable. + + :param dimensions: if non-None, the ARRAY will assume a fixed + number of dimensions. This will cause the DDL emitted for this + ARRAY to include the exact number of bracket clauses ``[]``, + and will also optimize the performance of the type overall. + Note that PG arrays are always implicitly "non-dimensioned", + meaning they can store any number of dimensions no matter how + they were declared. + + :param zero_indexes=False: when True, index values will be converted + between Python zero-based and Postgresql one-based indexes, e.g. + a value of one will be added to all index values before passing + to the database. + + .. versionadded:: 0.9.5 + + + """ + if isinstance(item_type, ARRAY): + raise ValueError("Do not nest ARRAY types; ARRAY(basetype) " + "handles multi-dimensional arrays of basetype") + if isinstance(item_type, type): + item_type = item_type() + self.item_type = item_type + self.as_tuple = as_tuple + self.dimensions = dimensions + self.zero_indexes = zero_indexes + + @property + def hashable(self): + return self.as_tuple + + @property + def python_type(self): + return list + + def compare_values(self, x, y): + return x == y + + def _proc_array(self, arr, itemproc, dim, collection): + if dim is None: + arr = list(arr) + if dim == 1 or dim is None and ( + # this has to be (list, tuple), or at least + # not hasattr('__iter__'), since Py3K strings + # etc. have __iter__ + not arr or not isinstance(arr[0], (list, tuple))): + if itemproc: + return collection(itemproc(x) for x in arr) + else: + return collection(arr) + else: + return collection( + self._proc_array( + x, itemproc, + dim - 1 if dim is not None else None, + collection) + for x in arr + ) + + def bind_processor(self, dialect): + item_proc = self.item_type.dialect_impl(dialect).\ + bind_processor(dialect) + + def process(value): + if value is None: + return value + else: + return self._proc_array( + value, + item_proc, + self.dimensions, + list) + return process + + def result_processor(self, dialect, coltype): + item_proc = self.item_type.dialect_impl(dialect).\ + result_processor(dialect, coltype) + + def process(value): + if value is None: + return value + else: + return self._proc_array( + value, + item_proc, + self.dimensions, + tuple if self.as_tuple else list) + return process + +ischema_names['_array'] = ARRAY diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 64d19eda1..d175819c8 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -508,6 +508,36 @@ dialect in conjunction with the :class:`.Table` construct: `Postgresql CREATE TABLE options <http://www.postgresql.org/docs/9.3/static/sql-createtable.html>`_ +ARRAY Types +----------- + +The Postgresql dialect supports arrays, both as multidimensional column types +as well as array literals: + +* :class:`.postgresql.ARRAY` - ARRAY datatype + +* :class:`.postgresql.array` - array literal + +JSON Types +---------- + +The Postgresql dialect supports both JSON and JSONB datatypes, including +psycopg2's native support and support for all of Postgresql's special +operators: + +* :class:`.postgresql.JSON` + +* :class:`.postgresql.JSONB` + +HSTORE Type +----------- + +The Postgresql HSTORE type as well as hstore literals are supported: + +* :class:`.postgresql.HSTORE` - HSTORE datatype + +* :class:`.postgresql.hstore` - hstore literal + ENUM Types ---------- @@ -530,7 +560,7 @@ import re from ... import sql, schema, exc, util from ...engine import default, reflection -from ...sql import compiler, expression, operators, default_comparator +from ...sql import compiler, expression from ... import types as sqltypes try: @@ -722,417 +752,6 @@ class TSVECTOR(sqltypes.TypeEngine): __visit_name__ = 'TSVECTOR' -class _Slice(expression.ColumnElement): - __visit_name__ = 'slice' - type = sqltypes.NULLTYPE - - def __init__(self, slice_, source_comparator): - self.start = default_comparator._check_literal( - source_comparator.expr, - operators.getitem, slice_.start) - self.stop = default_comparator._check_literal( - source_comparator.expr, - operators.getitem, slice_.stop) - - -class Any(expression.ColumnElement): - - """Represent the clause ``left operator ANY (right)``. ``right`` must be - an array expression. - - .. seealso:: - - :class:`.postgresql.ARRAY` - - :meth:`.postgresql.ARRAY.Comparator.any` - ARRAY-bound method - - """ - __visit_name__ = 'any' - - def __init__(self, left, right, operator=operators.eq): - self.type = sqltypes.Boolean() - self.left = expression._literal_as_binds(left) - self.right = right - self.operator = operator - - -class All(expression.ColumnElement): - - """Represent the clause ``left operator ALL (right)``. ``right`` must be - an array expression. - - .. seealso:: - - :class:`.postgresql.ARRAY` - - :meth:`.postgresql.ARRAY.Comparator.all` - ARRAY-bound method - - """ - __visit_name__ = 'all' - - def __init__(self, left, right, operator=operators.eq): - self.type = sqltypes.Boolean() - self.left = expression._literal_as_binds(left) - self.right = right - self.operator = operator - - -class array(expression.Tuple): - - """A Postgresql ARRAY literal. - - This is used to produce ARRAY literals in SQL expressions, e.g.:: - - from sqlalchemy.dialects.postgresql import array - from sqlalchemy.dialects import postgresql - from sqlalchemy import select, func - - stmt = select([ - array([1,2]) + array([3,4,5]) - ]) - - print stmt.compile(dialect=postgresql.dialect()) - - Produces the SQL:: - - SELECT ARRAY[%(param_1)s, %(param_2)s] || - ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1 - - An instance of :class:`.array` will always have the datatype - :class:`.ARRAY`. The "inner" type of the array is inferred from - the values present, unless the ``type_`` keyword argument is passed:: - - array(['foo', 'bar'], type_=CHAR) - - .. versionadded:: 0.8 Added the :class:`~.postgresql.array` literal type. - - See also: - - :class:`.postgresql.ARRAY` - - """ - __visit_name__ = 'array' - - def __init__(self, clauses, **kw): - super(array, self).__init__(*clauses, **kw) - self.type = ARRAY(self.type) - - def _bind_param(self, operator, obj): - return array([ - expression.BindParameter(None, o, _compared_to_operator=operator, - _compared_to_type=self.type, unique=True) - for o in obj - ]) - - def self_group(self, against=None): - return self - - -class ARRAY(sqltypes.Concatenable, sqltypes.TypeEngine): - - """Postgresql ARRAY type. - - Represents values as Python lists. - - An :class:`.ARRAY` type is constructed given the "type" - of element:: - - mytable = Table("mytable", metadata, - Column("data", ARRAY(Integer)) - ) - - The above type represents an N-dimensional array, - meaning Postgresql will interpret values with any number - of dimensions automatically. To produce an INSERT - construct that passes in a 1-dimensional array of integers:: - - connection.execute( - mytable.insert(), - data=[1,2,3] - ) - - The :class:`.ARRAY` type can be constructed given a fixed number - of dimensions:: - - mytable = Table("mytable", metadata, - Column("data", ARRAY(Integer, dimensions=2)) - ) - - This has the effect of the :class:`.ARRAY` type - specifying that number of bracketed blocks when a :class:`.Table` - is used in a CREATE TABLE statement, or when the type is used - within a :func:`.expression.cast` construct; it also causes - the bind parameter and result set processing of the type - to optimize itself to expect exactly that number of dimensions. - Note that Postgresql itself still allows N dimensions with such a type. - - SQL expressions of type :class:`.ARRAY` have support for "index" and - "slice" behavior. The Python ``[]`` operator works normally here, given - integer indexes or slices. Note that Postgresql arrays default - to 1-based indexing. The operator produces binary expression - constructs which will produce the appropriate SQL, both for - SELECT statements:: - - select([mytable.c.data[5], mytable.c.data[2:7]]) - - as well as UPDATE statements when the :meth:`.Update.values` method - is used:: - - mytable.update().values({ - mytable.c.data[5]: 7, - mytable.c.data[2:7]: [1, 2, 3] - }) - - .. note:: - - Multi-dimensional support for the ``[]`` operator is not supported - in SQLAlchemy 1.0. Please use the :func:`.type_coerce` function - to cast an intermediary expression to ARRAY again as a workaround:: - - expr = type_coerce(my_array_column[5], ARRAY(Integer))[6] - - Multi-dimensional support will be provided in a future release. - - :class:`.ARRAY` provides special methods for containment operations, - e.g.:: - - mytable.c.data.contains([1, 2]) - - For a full list of special methods see :class:`.ARRAY.Comparator`. - - .. versionadded:: 0.8 Added support for index and slice operations - to the :class:`.ARRAY` type, including support for UPDATE - statements, and special array containment operations. - - The :class:`.ARRAY` type may not be supported on all DBAPIs. - It is known to work on psycopg2 and not pg8000. - - See also: - - :class:`.postgresql.array` - produce a literal array value. - - """ - __visit_name__ = 'ARRAY' - - class Comparator(sqltypes.Concatenable.Comparator): - - """Define comparison operations for :class:`.ARRAY`.""" - - def __getitem__(self, index): - shift_indexes = 1 if self.expr.type.zero_indexes else 0 - if isinstance(index, slice): - if shift_indexes: - index = slice( - index.start + shift_indexes, - index.stop + shift_indexes, - index.step - ) - index = _Slice(index, self) - return_type = self.type - else: - index += shift_indexes - return_type = self.type.item_type - - return default_comparator._binary_operate( - self.expr, operators.getitem, index, - result_type=return_type) - - def any(self, other, operator=operators.eq): - """Return ``other operator ANY (array)`` clause. - - Argument places are switched, because ANY requires array - expression to be on the right hand-side. - - E.g.:: - - from sqlalchemy.sql import operators - - conn.execute( - select([table.c.data]).where( - table.c.data.any(7, operator=operators.lt) - ) - ) - - :param other: expression to be compared - :param operator: an operator object from the - :mod:`sqlalchemy.sql.operators` - package, defaults to :func:`.operators.eq`. - - .. seealso:: - - :class:`.postgresql.Any` - - :meth:`.postgresql.ARRAY.Comparator.all` - - """ - return Any(other, self.expr, operator=operator) - - def all(self, other, operator=operators.eq): - """Return ``other operator ALL (array)`` clause. - - Argument places are switched, because ALL requires array - expression to be on the right hand-side. - - E.g.:: - - from sqlalchemy.sql import operators - - conn.execute( - select([table.c.data]).where( - table.c.data.all(7, operator=operators.lt) - ) - ) - - :param other: expression to be compared - :param operator: an operator object from the - :mod:`sqlalchemy.sql.operators` - package, defaults to :func:`.operators.eq`. - - .. seealso:: - - :class:`.postgresql.All` - - :meth:`.postgresql.ARRAY.Comparator.any` - - """ - return All(other, self.expr, operator=operator) - - def contains(self, other, **kwargs): - """Boolean expression. Test if elements are a superset of the - elements of the argument array expression. - """ - return self.expr.op('@>')(other) - - def contained_by(self, other): - """Boolean expression. Test if elements are a proper subset of the - elements of the argument array expression. - """ - return self.expr.op('<@')(other) - - def overlap(self, other): - """Boolean expression. Test if array has elements in common with - an argument array expression. - """ - return self.expr.op('&&')(other) - - def _adapt_expression(self, op, other_comparator): - if isinstance(op, operators.custom_op): - if op.opstring in ['@>', '<@', '&&']: - return op, sqltypes.Boolean - return sqltypes.Concatenable.Comparator.\ - _adapt_expression(self, op, other_comparator) - - comparator_factory = Comparator - - def __init__(self, item_type, as_tuple=False, dimensions=None, - zero_indexes=False): - """Construct an ARRAY. - - E.g.:: - - Column('myarray', ARRAY(Integer)) - - Arguments are: - - :param item_type: The data type of items of this array. Note that - dimensionality is irrelevant here, so multi-dimensional arrays like - ``INTEGER[][]``, are constructed as ``ARRAY(Integer)``, not as - ``ARRAY(ARRAY(Integer))`` or such. - - :param as_tuple=False: Specify whether return results - should be converted to tuples from lists. DBAPIs such - as psycopg2 return lists by default. When tuples are - returned, the results are hashable. - - :param dimensions: if non-None, the ARRAY will assume a fixed - number of dimensions. This will cause the DDL emitted for this - ARRAY to include the exact number of bracket clauses ``[]``, - and will also optimize the performance of the type overall. - Note that PG arrays are always implicitly "non-dimensioned", - meaning they can store any number of dimensions no matter how - they were declared. - - :param zero_indexes=False: when True, index values will be converted - between Python zero-based and Postgresql one-based indexes, e.g. - a value of one will be added to all index values before passing - to the database. - - .. versionadded:: 0.9.5 - - """ - if isinstance(item_type, ARRAY): - raise ValueError("Do not nest ARRAY types; ARRAY(basetype) " - "handles multi-dimensional arrays of basetype") - if isinstance(item_type, type): - item_type = item_type() - self.item_type = item_type - self.as_tuple = as_tuple - self.dimensions = dimensions - self.zero_indexes = zero_indexes - - @property - def python_type(self): - return list - - def compare_values(self, x, y): - return x == y - - def _proc_array(self, arr, itemproc, dim, collection): - if dim is None: - arr = list(arr) - if dim == 1 or dim is None and ( - # this has to be (list, tuple), or at least - # not hasattr('__iter__'), since Py3K strings - # etc. have __iter__ - not arr or not isinstance(arr[0], (list, tuple))): - if itemproc: - return collection(itemproc(x) for x in arr) - else: - return collection(arr) - else: - return collection( - self._proc_array( - x, itemproc, - dim - 1 if dim is not None else None, - collection) - for x in arr - ) - - def bind_processor(self, dialect): - item_proc = self.item_type.\ - dialect_impl(dialect).\ - bind_processor(dialect) - - def process(value): - if value is None: - return value - else: - return self._proc_array( - value, - item_proc, - self.dimensions, - list) - return process - - def result_processor(self, dialect, coltype): - item_proc = self.item_type.\ - dialect_impl(dialect).\ - result_processor(dialect, coltype) - - def process(value): - if value is None: - return value - else: - return self._proc_array( - value, - item_proc, - self.dimensions, - tuple if self.as_tuple else list) - return process - -PGArray = ARRAY - - class ENUM(sqltypes.Enum): """Postgresql ENUM type. @@ -1547,8 +1166,8 @@ class PGDDLCompiler(compiler.DDLCompiler): else: colspec += " SERIAL" else: - colspec += " " + self.dialect.type_compiler.process(column.type, - type_expression=column) + colspec += " " + self.dialect.type_compiler.process( + column.type, type_expression=column) default = self.get_column_default_string(column) if default is not None: colspec += " DEFAULT " + default @@ -2448,7 +2067,7 @@ class PGDialect(default.DefaultDialect): if coltype: coltype = coltype(*args, **kwargs) if is_array: - coltype = ARRAY(coltype) + coltype = self.ischema_names['_array'](coltype) else: util.warn("Did not recognize type '%s' of column '%s'" % (attype, name)) diff --git a/lib/sqlalchemy/dialects/postgresql/hstore.py b/lib/sqlalchemy/dialects/postgresql/hstore.py index 9f369cb5b..b7b0fc007 100644 --- a/lib/sqlalchemy/dialects/postgresql/hstore.py +++ b/lib/sqlalchemy/dialects/postgresql/hstore.py @@ -7,110 +7,43 @@ import re -from .base import ARRAY, ischema_names +from .base import ischema_names +from .array import ARRAY from ... import types as sqltypes from ...sql import functions as sqlfunc +from ...sql import operators from ...sql.operators import custom_op from ... import util __all__ = ('HSTORE', 'hstore') -# My best guess at the parsing rules of hstore literals, since no formal -# grammar is given. This is mostly reverse engineered from PG's input parser -# behavior. -HSTORE_PAIR_RE = re.compile(r""" -( - "(?P<key> (\\ . | [^"])* )" # Quoted key -) -[ ]* => [ ]* # Pair operator, optional adjoining whitespace -( - (?P<value_null> NULL ) # NULL value - | "(?P<value> (\\ . | [^"])* )" # Quoted value -) -""", re.VERBOSE) - -HSTORE_DELIMITER_RE = re.compile(r""" -[ ]* , [ ]* -""", re.VERBOSE) - - -def _parse_error(hstore_str, pos): - """format an unmarshalling error.""" - - ctx = 20 - hslen = len(hstore_str) - - parsed_tail = hstore_str[max(pos - ctx - 1, 0):min(pos, hslen)] - residual = hstore_str[min(pos, hslen):min(pos + ctx + 1, hslen)] - if len(parsed_tail) > ctx: - parsed_tail = '[...]' + parsed_tail[1:] - if len(residual) > ctx: - residual = residual[:-1] + '[...]' - - return "After %r, could not parse residual at position %d: %r" % ( - parsed_tail, pos, residual) - - -def _parse_hstore(hstore_str): - """Parse an hstore from its literal string representation. - - Attempts to approximate PG's hstore input parsing rules as closely as - possible. Although currently this is not strictly necessary, since the - current implementation of hstore's output syntax is stricter than what it - accepts as input, the documentation makes no guarantees that will always - be the case. - - - - """ - result = {} - pos = 0 - pair_match = HSTORE_PAIR_RE.match(hstore_str) - - while pair_match is not None: - key = pair_match.group('key').replace(r'\"', '"').replace( - "\\\\", "\\") - if pair_match.group('value_null'): - value = None - else: - value = pair_match.group('value').replace( - r'\"', '"').replace("\\\\", "\\") - result[key] = value - - pos += pair_match.end() - - delim_match = HSTORE_DELIMITER_RE.match(hstore_str[pos:]) - if delim_match is not None: - pos += delim_match.end() - - pair_match = HSTORE_PAIR_RE.match(hstore_str[pos:]) - - if pos != len(hstore_str): - raise ValueError(_parse_error(hstore_str, pos)) +INDEX = custom_op( + "->", precedence=5, natural_self_precedent=True +) - return result +HAS_KEY = operators.custom_op( + "?", precedence=5, natural_self_precedent=True +) +HAS_ALL = operators.custom_op( + "?&", precedence=5, natural_self_precedent=True +) -def _serialize_hstore(val): - """Serialize a dictionary into an hstore literal. Keys and values must - both be strings (except None for values). +HAS_ANY = operators.custom_op( + "?|", precedence=5, natural_self_precedent=True +) - """ - def esc(s, position): - if position == 'value' and s is None: - return 'NULL' - elif isinstance(s, util.string_types): - return '"%s"' % s.replace("\\", "\\\\").replace('"', r'\"') - else: - raise ValueError("%r in %s position is not a string." % - (s, position)) +CONTAINS = operators.custom_op( + "@>", precedence=5, natural_self_precedent=True +) - return ', '.join('%s=>%s' % (esc(k, 'key'), esc(v, 'value')) - for k, v in val.items()) +CONTAINED_BY = operators.custom_op( + "<@", precedence=5, natural_self_precedent=True +) -class HSTORE(sqltypes.Concatenable, sqltypes.TypeEngine): +class HSTORE(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine): """Represent the Postgresql HSTORE type. The :class:`.HSTORE` type stores dictionaries containing strings, e.g.:: @@ -185,51 +118,61 @@ class HSTORE(sqltypes.Concatenable, sqltypes.TypeEngine): __visit_name__ = 'HSTORE' hashable = False + text_type = sqltypes.Text() + + def __init__(self, text_type=None): + """Construct a new :class:`.HSTORE`. + + :param text_type: the type that should be used for indexed values. + Defaults to :class:`.types.Text`. + + .. versionadded:: 1.1.0 - class comparator_factory(sqltypes.Concatenable.Comparator): + """ + if text_type is not None: + self.text_type = text_type + + class Comparator( + sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator): """Define comparison operations for :class:`.HSTORE`.""" def has_key(self, other): """Boolean expression. Test for presence of a key. Note that the key may be a SQLA expression. """ - return self.expr.op('?')(other) + return self.operate(HAS_KEY, other, result_type=sqltypes.Boolean) def has_all(self, other): - """Boolean expression. Test for presence of all keys in the PG - array. + """Boolean expression. Test for presence of all keys in jsonb """ - return self.expr.op('?&')(other) + return self.operate(HAS_ALL, other, result_type=sqltypes.Boolean) def has_any(self, other): - """Boolean expression. Test for presence of any key in the PG - array. + """Boolean expression. Test for presence of any key in jsonb """ - return self.expr.op('?|')(other) - - def defined(self, key): - """Boolean expression. Test for presence of a non-NULL value for - the key. Note that the key may be a SQLA expression. - """ - return _HStoreDefinedFunction(self.expr, key) + return self.operate(HAS_ANY, other, result_type=sqltypes.Boolean) def contains(self, other, **kwargs): - """Boolean expression. Test if keys are a superset of the keys of - the argument hstore expression. + """Boolean expression. Test if keys (or array) are a superset + of/contained the keys of the argument jsonb expression. """ - return self.expr.op('@>')(other) + return self.operate(CONTAINS, other, result_type=sqltypes.Boolean) def contained_by(self, other): """Boolean expression. Test if keys are a proper subset of the - keys of the argument hstore expression. + keys of the argument jsonb expression. """ - return self.expr.op('<@')(other) + return self.operate( + CONTAINED_BY, other, result_type=sqltypes.Boolean) - def __getitem__(self, other): - """Text expression. Get the value at a given key. Note that the - key may be a SQLA expression. + def _setup_getitem(self, index): + return INDEX, index, self.type.text_type + + def defined(self, key): + """Boolean expression. Test for presence of a non-NULL value for + the key. Note that the key may be a SQLA expression. """ - return self.expr.op('->', precedence=5)(other) + return _HStoreDefinedFunction(self.expr, key) def delete(self, key): """HStore expression. Returns the contents of this hstore with the @@ -263,14 +206,7 @@ class HSTORE(sqltypes.Concatenable, sqltypes.TypeEngine): """Text array expression. Returns array of [key, value] pairs.""" return _HStoreMatrixFunction(self.expr) - def _adapt_expression(self, op, other_comparator): - if isinstance(op, custom_op): - if op.opstring in ['?', '?&', '?|', '@>', '<@']: - return op, sqltypes.Boolean - elif op.opstring == '->': - return op, sqltypes.Text - return sqltypes.Concatenable.Comparator.\ - _adapt_expression(self, op, other_comparator) + comparator_factory = Comparator def bind_processor(self, dialect): if util.py2k: @@ -374,3 +310,105 @@ class _HStoreArrayFunction(sqlfunc.GenericFunction): class _HStoreMatrixFunction(sqlfunc.GenericFunction): type = ARRAY(sqltypes.Text) name = 'hstore_to_matrix' + + +# +# parsing. note that none of this is used with the psycopg2 backend, +# which provides its own native extensions. +# + +# My best guess at the parsing rules of hstore literals, since no formal +# grammar is given. This is mostly reverse engineered from PG's input parser +# behavior. +HSTORE_PAIR_RE = re.compile(r""" +( + "(?P<key> (\\ . | [^"])* )" # Quoted key +) +[ ]* => [ ]* # Pair operator, optional adjoining whitespace +( + (?P<value_null> NULL ) # NULL value + | "(?P<value> (\\ . | [^"])* )" # Quoted value +) +""", re.VERBOSE) + +HSTORE_DELIMITER_RE = re.compile(r""" +[ ]* , [ ]* +""", re.VERBOSE) + + +def _parse_error(hstore_str, pos): + """format an unmarshalling error.""" + + ctx = 20 + hslen = len(hstore_str) + + parsed_tail = hstore_str[max(pos - ctx - 1, 0):min(pos, hslen)] + residual = hstore_str[min(pos, hslen):min(pos + ctx + 1, hslen)] + + if len(parsed_tail) > ctx: + parsed_tail = '[...]' + parsed_tail[1:] + if len(residual) > ctx: + residual = residual[:-1] + '[...]' + + return "After %r, could not parse residual at position %d: %r" % ( + parsed_tail, pos, residual) + + +def _parse_hstore(hstore_str): + """Parse an hstore from its literal string representation. + + Attempts to approximate PG's hstore input parsing rules as closely as + possible. Although currently this is not strictly necessary, since the + current implementation of hstore's output syntax is stricter than what it + accepts as input, the documentation makes no guarantees that will always + be the case. + + + + """ + result = {} + pos = 0 + pair_match = HSTORE_PAIR_RE.match(hstore_str) + + while pair_match is not None: + key = pair_match.group('key').replace(r'\"', '"').replace( + "\\\\", "\\") + if pair_match.group('value_null'): + value = None + else: + value = pair_match.group('value').replace( + r'\"', '"').replace("\\\\", "\\") + result[key] = value + + pos += pair_match.end() + + delim_match = HSTORE_DELIMITER_RE.match(hstore_str[pos:]) + if delim_match is not None: + pos += delim_match.end() + + pair_match = HSTORE_PAIR_RE.match(hstore_str[pos:]) + + if pos != len(hstore_str): + raise ValueError(_parse_error(hstore_str, pos)) + + return result + + +def _serialize_hstore(val): + """Serialize a dictionary into an hstore literal. Keys and values must + both be strings (except None for values). + + """ + def esc(s, position): + if position == 'value' and s is None: + return 'NULL' + elif isinstance(s, util.string_types): + return '"%s"' % s.replace("\\", "\\\\").replace('"', r'\"') + else: + raise ValueError("%r in %s position is not a string." % + (s, position)) + + return ', '.join('%s=>%s' % (esc(k, 'key'), esc(v, 'value')) + for k, v in val.items()) + + diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 13ebc4afe..4716ca970 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -6,96 +6,60 @@ # the MIT License: http://www.opensource.org/licenses/mit-license.php from __future__ import absolute_import +import collections import json from .base import ischema_names from ... import types as sqltypes -from ...sql.operators import custom_op -from ... import sql -from ...sql import elements, default_comparator +from ...sql import operators +from ...sql import elements from ... import util -__all__ = ('JSON', 'JSONElement', 'JSONB') +__all__ = ('JSON', 'JSONB') -class JSONElement(elements.BinaryExpression): - """Represents accessing an element of a :class:`.JSON` value. +# json : returns json +INDEX = operators.custom_op( + "->", precedence=5, natural_self_precedent=True +) - The :class:`.JSONElement` is produced whenever using the Python index - operator on an expression that has the type :class:`.JSON`:: +# path operator: returns json +PATHIDX = operators.custom_op( + "#>", precedence=5, natural_self_precedent=True +) - expr = mytable.c.json_data['some_key'] +# json + astext: returns text +ASTEXT = operators.custom_op( + "->>", precedence=5, natural_self_precedent=True +) - The expression typically compiles to a JSON access such as ``col -> key``. - Modifiers are then available for typing behavior, including - :meth:`.JSONElement.cast` and :attr:`.JSONElement.astext`. +# path operator + astext: returns text +ASTEXT_PATHIDX = operators.custom_op( + "#>>", precedence=5, natural_self_precedent=True +) - """ - - def __init__(self, left, right, astext=False, - opstring=None, result_type=None): - self._astext = astext - if opstring is None: - if hasattr(right, '__iter__') and \ - not isinstance(right, util.string_types): - opstring = "#>" - right = "{%s}" % ( - ", ".join(util.text_type(elem) for elem in right)) - else: - opstring = "->" - - self._json_opstring = opstring - operator = custom_op(opstring, precedence=5) - right = default_comparator._check_literal( - left, operator, right) - super(JSONElement, self).__init__( - left, right, operator, type_=result_type) - - @property - def astext(self): - """Convert this :class:`.JSONElement` to use the 'astext' operator - when evaluated. - - E.g.:: - - select([data_table.c.data['some key'].astext]) - - .. seealso:: - - :meth:`.JSONElement.cast` +HAS_KEY = operators.custom_op( + "?", precedence=5, natural_self_precedent=True +) - """ - if self._astext: - return self - else: - return JSONElement( - self.left, - self.right, - astext=True, - opstring=self._json_opstring + ">", - result_type=sqltypes.String(convert_unicode=True) - ) - - def cast(self, type_): - """Convert this :class:`.JSONElement` to apply both the 'astext' operator - as well as an explicit type cast when evaluated. +HAS_ALL = operators.custom_op( + "?&", precedence=5, natural_self_precedent=True +) - E.g.:: +HAS_ANY = operators.custom_op( + "?|", precedence=5, natural_self_precedent=True +) - select([data_table.c.data['some key'].cast(Integer)]) +CONTAINS = operators.custom_op( + "@>", precedence=5, natural_self_precedent=True +) - .. seealso:: +CONTAINED_BY = operators.custom_op( + "<@", precedence=5, natural_self_precedent=True +) - :attr:`.JSONElement.astext` - - """ - if not self._astext: - return self.astext.cast(type_) - else: - return sql.cast(self, type_) - -class JSON(sqltypes.TypeEngine): +class JSON(sqltypes.Indexable, sqltypes.TypeEngine): """Represent the Postgresql JSON type. The :class:`.JSON` type stores arbitrary JSON format data, e.g.:: @@ -113,31 +77,36 @@ class JSON(sqltypes.TypeEngine): :class:`.JSON` provides several operations: - * Index operations:: + * Index operations (the ``->`` operator):: data_table.c.data['some key'] - * Index operations returning text (required for text comparison):: + * Index operations returning text (the ``->>`` operator):: data_table.c.data['some key'].astext == 'some value' - * Index operations with a built-in CAST call:: + * Index operations with CAST + (equivalent to ``CAST(col ->> ['some key'] AS <type>)``):: - data_table.c.data['some key'].cast(Integer) == 5 + data_table.c.data['some key'].astext.cast(Integer) == 5 - * Path index operations:: + * Path index operations (the ``#>`` operator):: data_table.c.data[('key_1', 'key_2', ..., 'key_n')] - * Path index operations returning text (required for text comparison):: + * Path index operations returning text (the ``#>>`` operator):: + + data_table.c.data[('key_1', 'key_2', ..., 'key_n')].astext == \ +'some value' - data_table.c.data[('key_1', 'key_2', ..., 'key_n')].astext == \\ - 'some value' + .. versionchanged:: 1.1 The :meth:`.ColumnElement.cast` operator on + JSON objects now requires that the :attr:`.JSON.Comparator.astext` + modifier be called explicitly, if the cast works only from a textual + string. - Index operations return an instance of :class:`.JSONElement`, which - represents an expression such as ``column -> index``. This element then - defines methods such as :attr:`.JSONElement.astext` and - :meth:`.JSONElement.cast` for setting up type behavior. + Index operations return an expression object whose type defaults to + :class:`.JSON` by default, so that further JSON-oriented instructions + may be called upon the result type. The :class:`.JSON` type, when used with the SQLAlchemy ORM, does not detect in-place mutations to the structure. In order to detect these, the @@ -161,11 +130,18 @@ class JSON(sqltypes.TypeEngine): .. versionadded:: 0.9 + .. seealso:: + + :class:`.JSONB` + """ __visit_name__ = 'JSON' - def __init__(self, none_as_null=False): + hashable = False + astext_type = sqltypes.Text() + + def __init__(self, none_as_null=False, astext_type=None): """Construct a :class:`.JSON` type. :param none_as_null: if True, persist the value ``None`` as a @@ -179,23 +155,56 @@ class JSON(sqltypes.TypeEngine): .. versionchanged:: 0.9.8 - Added ``none_as_null``, and :func:`.null` is now supported in order to persist a NULL value. + :param astext_type: the type to use for the + :attr:`.JSON.Comparator.astext` + accessor on indexed attributes. Defaults to :class:`.types.Text`. + + .. versionadded:: 1.1.0 + """ self.none_as_null = none_as_null + if astext_type is not None: + self.astext_type = astext_type - class comparator_factory(sqltypes.Concatenable.Comparator): + class Comparator( + sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator): """Define comparison operations for :class:`.JSON`.""" - def __getitem__(self, other): - """Get the value at a given key.""" + @property + def astext(self): + """On an indexed expression, use the "astext" (e.g. "->>") + conversion when rendered in SQL. + + E.g.:: + + select([data_table.c.data['some key'].astext]) + + .. seealso:: + + :meth:`.ColumnElement.cast` + + """ + against = self.expr.operator + if against is PATHIDX: + against = ASTEXT_PATHIDX + else: + against = ASTEXT + + return self.expr.left.operate( + against, self.expr.right, result_type=self.type.astext_type) + + def _setup_getitem(self, index): + if not isinstance(index, util.string_types): + assert isinstance(index, collections.Sequence) + tokens = [util.text_type(elem) for elem in index] + index = "{%s}" % (", ".join(tokens)) + operator = PATHIDX + else: + operator = INDEX - return JSONElement(self.expr, other) + return operator, index, self.type - def _adapt_expression(self, op, other_comparator): - if isinstance(op, custom_op): - if op.opstring == '->': - return op, sqltypes.Text - return sqltypes.Concatenable.Comparator.\ - _adapt_expression(self, op, other_comparator) + comparator_factory = Comparator def bind_processor(self, dialect): json_serializer = dialect._json_serializer or json.dumps @@ -253,106 +262,68 @@ class JSONB(JSON): data = {"key1": "value1", "key2": "value2"} ) - :class:`.JSONB` provides several operations: - - * Index operations:: - - data_table.c.data['some key'] - - * Index operations returning text (required for text comparison):: + The :class:`.JSONB` type includes all operations provided by + :class:`.JSON`, including the same behaviors for indexing operations. + It also adds additional operators specific to JSONB, including + :meth:`.JSONB.Comparator.has_key`, :meth:`.JSONB.Comparator.has_all`, + :meth:`.JSONB.Comparator.has_any`, :meth:`.JSONB.Comparator.contains`, + and :meth:`.JSONB.Comparator.contained_by`. + + Like the :class:`.JSON` type, the :class:`.JSONB` type does not detect + in-place changes when used with the ORM, unless the + :mod:`sqlalchemy.ext.mutable` extension is used. + + Custom serializers and deserializers + are shared with the :class:`.JSON` class, using the ``json_serializer`` + and ``json_deserializer`` keyword arguments. These must be specified + at the dialect level using :func:`.create_engine`. When using + psycopg2, the serializers are associated with the jsonb type using + ``psycopg2.extras.register_default_jsonb`` on a per-connection basis, + in the same way that ``psycopg2.extras.register_default_json`` is used + to register these handlers with the json type. - data_table.c.data['some key'].astext == 'some value' - - * Index operations with a built-in CAST call:: - - data_table.c.data['some key'].cast(Integer) == 5 - - * Path index operations:: - - data_table.c.data[('key_1', 'key_2', ..., 'key_n')] - - * Path index operations returning text (required for text comparison):: - - data_table.c.data[('key_1', 'key_2', ..., 'key_n')].astext == \\ - 'some value' - - Index operations return an instance of :class:`.JSONElement`, which - represents an expression such as ``column -> index``. This element then - defines methods such as :attr:`.JSONElement.astext` and - :meth:`.JSONElement.cast` for setting up type behavior. - - The :class:`.JSON` type, when used with the SQLAlchemy ORM, does not - detect in-place mutations to the structure. In order to detect these, the - :mod:`sqlalchemy.ext.mutable` extension must be used. This extension will - allow "in-place" changes to the datastructure to produce events which - will be detected by the unit of work. See the example at :class:`.HSTORE` - for a simple example involving a dictionary. - - Custom serializers and deserializers are specified at the dialect level, - that is using :func:`.create_engine`. The reason for this is that when - using psycopg2, the DBAPI only allows serializers at the per-cursor - or per-connection level. E.g.:: + .. versionadded:: 0.9.7 - engine = create_engine("postgresql://scott:tiger@localhost/test", - json_serializer=my_serialize_fn, - json_deserializer=my_deserialize_fn - ) + .. seealso:: - When using the psycopg2 dialect, the json_deserializer is registered - against the database using ``psycopg2.extras.register_default_json``. - - .. versionadded:: 0.9.7 + :class:`.JSON` """ __visit_name__ = 'JSONB' - hashable = False - class comparator_factory(sqltypes.Concatenable.Comparator): + class Comparator(JSON.Comparator): """Define comparison operations for :class:`.JSON`.""" - def __getitem__(self, other): - """Get the value at a given key.""" - - return JSONElement(self.expr, other) - - def _adapt_expression(self, op, other_comparator): - # How does one do equality?? jsonb also has "=" eg. - # '[1,2,3]'::jsonb = '[1,2,3]'::jsonb - if isinstance(op, custom_op): - if op.opstring in ['?', '?&', '?|', '@>', '<@']: - return op, sqltypes.Boolean - if op.opstring == '->': - return op, sqltypes.Text - return sqltypes.Concatenable.Comparator.\ - _adapt_expression(self, op, other_comparator) - def has_key(self, other): """Boolean expression. Test for presence of a key. Note that the key may be a SQLA expression. """ - return self.expr.op('?')(other) + return self.operate(HAS_KEY, other, result_type=sqltypes.Boolean) def has_all(self, other): """Boolean expression. Test for presence of all keys in jsonb """ - return self.expr.op('?&')(other) + return self.operate(HAS_ALL, other, result_type=sqltypes.Boolean) def has_any(self, other): """Boolean expression. Test for presence of any key in jsonb """ - return self.expr.op('?|')(other) + return self.operate(HAS_ANY, other, result_type=sqltypes.Boolean) def contains(self, other, **kwargs): - """Boolean expression. Test if keys (or array) are a superset of/contained - the keys of the argument jsonb expression. + """Boolean expression. Test if keys (or array) are a superset + of/contained the keys of the argument jsonb expression. """ - return self.expr.op('@>')(other) + return self.operate(CONTAINS, other, result_type=sqltypes.Boolean) def contained_by(self, other): """Boolean expression. Test if keys are a proper subset of the keys of the argument jsonb expression. """ - return self.expr.op('<@')(other) + return self.operate( + CONTAINED_BY, other, result_type=sqltypes.Boolean) + + comparator_factory = Comparator ischema_names['jsonb'] = JSONB diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index 36a9d7bf7..d33554922 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -320,7 +320,7 @@ from ...sql import expression from ... import types as sqltypes from .base import PGDialect, PGCompiler, \ PGIdentifierPreparer, PGExecutionContext, \ - ENUM, ARRAY, _DECIMAL_TYPES, _FLOAT_TYPES,\ + ENUM, _DECIMAL_TYPES, _FLOAT_TYPES,\ _INT_TYPES, UUID from .hstore import HSTORE from .json import JSON, JSONB |