diff options
Diffstat (limited to 'lib/sqlalchemy/dialects/postgresql')
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/__init__.py | 17 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/array.py | 306 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 519 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/ext.py (renamed from lib/sqlalchemy/dialects/postgresql/constraints.py) | 76 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/hstore.py | 278 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/json.py | 400 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/psycopg2.py | 2 |
7 files changed, 850 insertions, 748 deletions
diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py index 98fe6f085..d67f2a07e 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 -from .constraints import ExcludeConstraint + DATE, BYTEA, BOOLEAN, INTERVAL, ENUM, dialect, TSVECTOR, DropEnumType, \ + CreateEnumType from .hstore import HSTORE, hstore -from .json import JSON, JSONElement, JSONB +from .json import JSON, JSONB +from .array import array, ARRAY, Any, All +from .ext import aggregate_order_by, ExcludeConstraint, array_agg + from .ranges import INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, \ TSTZRANGE @@ -24,8 +26,9 @@ __all__ = ( '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', 'Any', 'All', 'array', 'HSTORE', + 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'array', 'HSTORE', 'hstore', 'INT4RANGE', 'INT8RANGE', 'NUMRANGE', 'DATERANGE', - 'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', 'JSONElement', - 'DropEnumType' + 'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', 'Any', 'All', + 'DropEnumType', 'CreateEnumType', 'ExcludeConstraint', + 'aggregate_order_by', 'array_agg' ) diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py new file mode 100644 index 000000000..b88f139de --- /dev/null +++ b/lib/sqlalchemy/dialects/postgresql/array.py @@ -0,0 +1,306 @@ +# 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 ...sql.base import SchemaEventTarget +from ... import types as sqltypes + +try: + from uuid import UUID as _python_UUID +except ImportError: + _python_UUID = None + + +def Any(other, arrexpr, operator=operators.eq): + """A synonym for the :meth:`.ARRAY.Comparator.any` method. + + This method is legacy and is here for backwards-compatiblity. + + .. seealso:: + + :func:`.expression.any_` + + """ + + return arrexpr.any(other, operator) + + +def All(other, arrexpr, operator=operators.eq): + """A synonym for the :meth:`.ARRAY.Comparator.all` method. + + This method is legacy and is here for backwards-compatiblity. + + .. seealso:: + + :func:`.expression.all_` + + """ + + return arrexpr.all(other, 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): + if (against in ( + operators.any_op, operators.all_op, operators.getitem)): + return expression.Grouping(self) + else: + return self + + +CONTAINS = operators.custom_op("@>", precedence=5) + +CONTAINED_BY = operators.custom_op("<@", precedence=5) + +OVERLAP = operators.custom_op("&&", precedence=5) + + +class ARRAY(SchemaEventTarget, sqltypes.Array): + + """Postgresql ARRAY type. + + .. versionchanged:: 1.1 The :class:`.postgresql.ARRAY` type is now + a subclass of the core :class:`.Array` type. + + The :class:`.postgresql.ARRAY` type is constructed in the same way + as the core :class:`.Array` type; a member type is required, and a + number of dimensions is recommended if the type is to be used for more + than one dimension:: + + from sqlalchemy.dialects import postgresql + + mytable = Table("mytable", metadata, + Column("data", postgresql.ARRAY(Integer, dimensions=2)) + ) + + The :class:`.postgresql.ARRAY` type provides all operations defined on the + core :class:`.Array` type, including support for "dimensions", indexed + access, and simple matching such as :meth:`.Array.Comparator.any` + and :meth:`.Array.Comparator.all`. :class:`.postgresql.ARRAY` class also + provides PostgreSQL-specific methods for containment operations, including + :meth:`.postgresql.ARRAY.Comparator.contains` + :meth:`.postgresql.ARRAY.Comparator.contained_by`, + and :meth:`.postgresql.ARRAY.Comparator.overlap`, e.g.:: + + mytable.c.data.contains([1, 2]) + + The :class:`.postgresql.ARRAY` type may not be supported on all + PostgreSQL DBAPIs; it is currently known to work on psycopg2 only. + + Additionally, the :class:`.postgresql.ARRAY` type does not work directly in + conjunction with the :class:`.ENUM` type. For a workaround, see the + special type at :ref:`postgresql_array_of_enum`. + + .. seealso:: + + :class:`.types.Array` - base array type + + :class:`.postgresql.array` - produces a literal array value. + + """ + + class Comparator(sqltypes.Array.Comparator): + + """Define comparison operations for :class:`.ARRAY`. + + Note that these operations are in addition to those provided + by the base :class:`.types.Array.Comparator` class, including + :meth:`.types.Array.Comparator.any` and + :meth:`.types.Array.Comparator.all`. + + """ + + 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 _set_parent(self, column): + """Support SchemaEentTarget""" + + if isinstance(self.item_type, SchemaEventTarget): + self.item_type._set_parent(column) + + def _set_parent_with_dispatch(self, parent): + """Support SchemaEentTarget""" + + if isinstance(self.item_type, SchemaEventTarget): + self.item_type._set_parent_with_dispatch(parent) + + 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 dc7987d74..ec12e1145 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -508,6 +508,41 @@ 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 + +* :func:`.postgresql.array_agg` - ARRAY_AGG SQL function + +* :class:`.postgresql.aggregate_order_by` - helper for PG's ORDER BY aggregate + function syntax. + +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 ---------- @@ -524,13 +559,54 @@ entity. The following sections should be consulted: * :meth:`.postgresql.ENUM.create` , :meth:`.postgresql.ENUM.drop` - individual CREATE and DROP commands for ENUM. +.. _postgresql_array_of_enum: + +Using ENUM with ARRAY +^^^^^^^^^^^^^^^^^^^^^ + +The combination of ENUM and ARRAY is not directly supported by backend +DBAPIs at this time. In order to send and receive an ARRAY of ENUM, +use the following workaround type:: + + class ArrayOfEnum(ARRAY): + + def bind_expression(self, bindvalue): + return sa.cast(bindvalue, self) + + def result_processor(self, dialect, coltype): + super_rp = super(ArrayOfEnum, self).result_processor( + dialect, coltype) + + def handle_raw_string(value): + inner = re.match(r"^{(.*)}$", value).group(1) + return inner.split(",") + + def process(value): + if value is None: + return None + return super_rp(handle_raw_string(value)) + return process + +E.g.:: + + Table( + 'mydata', metadata, + Column('id', Integer, primary_key=True), + Column('data', ArrayOfEnum(ENUM('a', 'b, 'c', name='myenum'))) + + ) + +This type is not included as a built-in type as it would be incompatible +with a DBAPI that suddenly decides to support ARRAY of ENUM directly in +a new version. + """ from collections import defaultdict 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 +798,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. @@ -1385,26 +1050,18 @@ class PGCompiler(compiler.SQLCompiler): self.process(element.stop, **kw), ) - def visit_any(self, element, **kw): - return "%s%sANY (%s)" % ( - self.process(element.left, **kw), - compiler.OPERATORS[element.operator], - self.process(element.right, **kw) - ) - - def visit_all(self, element, **kw): - return "%s%sALL (%s)" % ( - self.process(element.left, **kw), - compiler.OPERATORS[element.operator], - self.process(element.right, **kw) - ) - def visit_getitem_binary(self, binary, operator, **kw): return "%s[%s]" % ( self.process(binary.left, **kw), self.process(binary.right, **kw) ) + def visit_aggregate_order_by(self, element, **kw): + return "%s ORDER BY %s" % ( + self.process(element.target, **kw), + self.process(element.order_by, **kw) + ) + def visit_match_op_binary(self, binary, operator, **kw): if "postgresql_regconfig" in binary.modifiers: regconfig = self.render_literal_value( @@ -1547,8 +1204,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 +2105,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)) @@ -2641,7 +2298,7 @@ class PGDialect(default.DefaultDialect): i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, a.attname, a.attnum, NULL, ix.indkey%s, - i.reloptions, am.amname + %s, am.amname FROM pg_class t join pg_index ix on t.oid = ix.indrelid @@ -2664,6 +2321,8 @@ class PGDialect(default.DefaultDialect): # cast does not work in PG 8.2.4, does work in 8.3.0. # nothing in PG changelogs regarding this. "::varchar" if self.server_version_info >= (8, 3) else "", + "i.reloptions" if self.server_version_info >= (8, 2) + else "NULL", self._pg_index_any("a.attnum", "ix.indkey") ) else: diff --git a/lib/sqlalchemy/dialects/postgresql/constraints.py b/lib/sqlalchemy/dialects/postgresql/ext.py index 4cfc050de..9b2e3fd73 100644 --- a/lib/sqlalchemy/dialects/postgresql/constraints.py +++ b/lib/sqlalchemy/dialects/postgresql/ext.py @@ -1,11 +1,69 @@ -# Copyright (C) 2013-2015 the SQLAlchemy authors and contributors +# postgresql/ext.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 ...sql.schema import ColumnCollectionConstraint + from ...sql import expression -from ... import util +from ...sql import elements +from ...sql import functions +from ...sql.schema import ColumnCollectionConstraint +from .array import ARRAY + + +class aggregate_order_by(expression.ColumnElement): + """Represent a Postgresql aggregate order by expression. + + E.g.:: + + from sqlalchemy.dialects.postgresql import aggregate_order_by + expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc())) + stmt = select([expr]) + + would represent the expression:: + + SELECT array_agg(a ORDER BY b DESC) FROM table; + + Similarly:: + + expr = func.string_agg( + table.c.a, + aggregate_order_by(literal_column("','"), table.c.a) + ) + stmt = select([expr]) + + Would represent:: + + SELECT string_agg(a, ',' ORDER BY a) FROM table; + + .. versionadded:: 1.1 + + .. seealso:: + + :class:`.array_agg` + + """ + + __visit_name__ = 'aggregate_order_by' + + def __init__(self, target, order_by): + self.target = elements._literal_as_binds(target) + self.order_by = elements._literal_as_binds(order_by) + + def self_group(self, against=None): + return self + + def get_children(self, **kwargs): + return self.target, self.order_by + + def _copy_internals(self, clone=elements._clone, **kw): + self.target = clone(self.target, **kw) + self.order_by = clone(self.order_by, **kw) + + @property + def _from_objects(self): + return self.target._from_objects + self.order_by._from_objects class ExcludeConstraint(ColumnCollectionConstraint): @@ -96,3 +154,15 @@ static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE initially=self.initially) c.dispatch._update(self.dispatch) return c + + +def array_agg(*arg, **kw): + """Postgresql-specific form of :class:`.array_agg`, ensures + return type is :class:`.postgresql.ARRAY` and not + the plain :class:`.types.Array`. + + .. versionadded:: 1.1 + + """ + kw['type_'] = ARRAY(functions._type_from_args(arg)) + return functions.func.array_agg(*arg, **kw) 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..8a50270f5 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` - - """ - 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. - - E.g.:: +HAS_KEY = operators.custom_op( + "?", precedence=5, natural_self_precedent=True +) - select([data_table.c.data['some key'].cast(Integer)]) +HAS_ALL = operators.custom_op( + "?&", precedence=5, natural_self_precedent=True +) - .. seealso:: +HAS_ANY = operators.custom_op( + "?|", precedence=5, natural_self_precedent=True +) - :attr:`.JSONElement.astext` +CONTAINS = operators.custom_op( + "@>", precedence=5, natural_self_precedent=True +) - """ - if not self._astext: - return self.astext.cast(type_) - else: - return sql.cast(self, type_) +CONTAINED_BY = operators.custom_op( + "<@", precedence=5, natural_self_precedent=True +) -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 @@ -146,6 +115,29 @@ class JSON(sqltypes.TypeEngine): will be detected by the unit of work. See the example at :class:`.HSTORE` for a simple example involving a dictionary. + When working with NULL values, the :class:`.JSON` type recommends the + use of two specific constants in order to differentiate between a column + that evaluates to SQL NULL, e.g. no value, vs. the JSON-encoded string + of ``"null"``. To insert or select against a value that is SQL NULL, + use the constant :func:`.null`:: + + conn.execute(table.insert(), json_value=null()) + + To insert or select against a value that is JSON ``"null"``, use the + constant :attr:`.JSON.NULL`:: + + conn.execute(table.insert(), json_value=JSON.NULL) + + The :class:`.JSON` type supports a flag + :paramref:`.JSON.none_as_null` which when set to True will result + in the Python constant ``None`` evaluating to the value of SQL + NULL, and when set to False results in the Python constant + ``None`` evaluating to the value of JSON ``"null"``. The Python + value ``None`` may be used in conjunction with either + :attr:`.JSON.NULL` and :func:`.null` in order to indicate NULL + values, but care must be taken as to the value of the + :paramref:`.JSON.none_as_null` in these cases. + 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 @@ -161,11 +153,42 @@ 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() + + NULL = util.symbol('JSON_NULL') + """Describe the json value of NULL. + + This value is used to force the JSON value of ``"null"`` to be + used as the value. A value of Python ``None`` will be recognized + either as SQL NULL or JSON ``"null"``, based on the setting + of the :paramref:`.JSON.none_as_null` flag; the :attr:`.JSON.NULL` + constant can be used to always resolve to JSON ``"null"`` regardless + of this setting. This is in contrast to the :func:`.sql.null` construct, + which always resolves to SQL NULL. E.g.:: + + from sqlalchemy import null + from sqlalchemy.dialects.postgresql import JSON + + obj1 = MyObject(json_value=null()) # will *always* insert SQL NULL + obj2 = MyObject(json_value=JSON.NULL) # will *always* insert JSON string "null" + + session.add_all([obj1, obj2]) + session.commit() + + .. versionadded:: 1.1 + + """ + + 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,58 +202,99 @@ 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. + .. seealso:: + + :attr:`.JSON.NULL` + + :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 + + @property + def should_evaluate_none(self): + return not self.none_as_null def bind_processor(self, dialect): json_serializer = dialect._json_serializer or json.dumps if util.py2k: encoding = dialect.encoding - - def process(value): - if isinstance(value, elements.Null) or ( - value is None and self.none_as_null - ): - return None - return json_serializer(value).encode(encoding) else: - def process(value): - if isinstance(value, elements.Null) or ( - value is None and self.none_as_null - ): - return None + encoding = None + + def process(value): + if value is self.NULL: + value = None + elif isinstance(value, elements.Null) or ( + value is None and self.none_as_null + ): + return None + if encoding: + return json_serializer(value).encode(encoding) + else: return json_serializer(value) + return process def result_processor(self, dialect, coltype): json_deserializer = dialect._json_deserializer or json.loads if util.py2k: encoding = dialect.encoding - - def process(value): - if value is None: - return None - return json_deserializer(value.decode(encoding)) else: - def process(value): - if value is None: - return None - return json_deserializer(value) + encoding = None + + def process(value): + if value is None: + return None + if encoding: + value = value.decode(encoding) + return json_deserializer(value) return process @@ -253,106 +317,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.:: - - engine = create_engine("postgresql://scott:tiger@localhost/test", - json_serializer=my_serialize_fn, - json_deserializer=my_deserialize_fn - ) + .. versionadded:: 0.9.7 - When using the psycopg2 dialect, the json_deserializer is registered - against the database using ``psycopg2.extras.register_default_json``. + .. seealso:: - .. 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 |