diff options
Diffstat (limited to 'lib/sqlalchemy/dialects/postgresql')
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/__init__.py | 8 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 252 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/constraints.py | 6 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/hstore.py | 13 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/json.py | 199 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/pg8000.py | 6 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/psycopg2.py | 52 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/pypostgresql.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/ranges.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/zxjdbc.py | 2 |
10 files changed, 444 insertions, 98 deletions
diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py index 408b67846..180e9fc7e 100644 --- a/lib/sqlalchemy/dialects/postgresql/__init__.py +++ b/lib/sqlalchemy/dialects/postgresql/__init__.py @@ -1,5 +1,5 @@ # postgresql/__init__.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 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 @@ -11,9 +11,11 @@ base.dialect = psycopg2.dialect from .base import \ INTEGER, BIGINT, SMALLINT, VARCHAR, CHAR, TEXT, NUMERIC, FLOAT, REAL, \ INET, CIDR, UUID, BIT, MACADDR, DOUBLE_PRECISION, TIMESTAMP, TIME, \ - DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect, array, Any, All + DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect, array, Any, All, \ + TSVECTOR from .constraints import ExcludeConstraint from .hstore import HSTORE, hstore +from .json import JSON, JSONElement from .ranges import INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, \ TSTZRANGE @@ -23,5 +25,5 @@ __all__ = ( 'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME', 'DATE', 'BYTEA', 'BOOLEAN', 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'Any', 'All', 'array', 'HSTORE', 'hstore', 'INT4RANGE', 'INT8RANGE', 'NUMRANGE', 'DATERANGE', - 'TSRANGE', 'TSTZRANGE' + 'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONElement' ) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 6ccf7190e..11bd3830d 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1,5 +1,5 @@ # postgresql/base.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 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 @@ -131,6 +131,44 @@ use the :meth:`._UpdateBase.returning` method on a per-statement basis:: where(table.c.name=='foo') print result.fetchall() +.. _postgresql_match: + +Full Text Search +---------------- + +SQLAlchemy makes available the Postgresql ``@@`` operator via the +:meth:`.ColumnElement.match` method on any textual column expression. +On a Postgresql dialect, an expression like the following:: + + select([sometable.c.text.match("search string")]) + +will emit to the database:: + + SELECT text @@ to_tsquery('search string') FROM table + +The Postgresql text search functions such as ``to_tsquery()`` +and ``to_tsvector()`` are available +explicitly using the standard :attr:`.func` construct. For example:: + + select([ + func.to_tsvector('fat cats ate rats').match('cat & rat') + ]) + +Emits the equivalent of:: + + SELECT to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat') + +The :class:`.postgresql.TSVECTOR` type can provide for explicit CAST:: + + from sqlalchemy.dialects.postgresql import TSVECTOR + from sqlalchemy import select, cast + select([cast("some text", TSVECTOR)]) + +produces a statement equivalent to:: + + SELECT CAST('some text' AS TSVECTOR) AS anon_1 + + FROM ONLY ... ------------------------ @@ -210,7 +248,7 @@ import re from ... import sql, schema, exc, util from ...engine import default, reflection -from ...sql import compiler, expression, util as sql_util, operators +from ...sql import compiler, expression, operators from ... import types as sqltypes try: @@ -230,7 +268,7 @@ RESERVED_WORDS = set( "default", "deferrable", "desc", "distinct", "do", "else", "end", "except", "false", "fetch", "for", "foreign", "from", "grant", "group", "having", "in", "initially", "intersect", "into", "leading", "limit", - "localtime", "localtimestamp", "new", "not", "null", "off", "offset", + "localtime", "localtimestamp", "new", "not", "null", "of", "off", "offset", "old", "on", "only", "or", "order", "placing", "primary", "references", "returning", "select", "session_user", "some", "symmetric", "table", "then", "to", "trailing", "true", "union", "unique", "user", "using", @@ -368,6 +406,23 @@ class UUID(sqltypes.TypeEngine): PGUuid = UUID +class TSVECTOR(sqltypes.TypeEngine): + """The :class:`.postgresql.TSVECTOR` type implements the Postgresql + text search type TSVECTOR. + + It can be used to do full text queries on natural language + documents. + + .. versionadded:: 0.9.0 + + .. seealso:: + + :ref:`postgresql_match` + + """ + __visit_name__ = 'TSVECTOR' + + class _Slice(expression.ColumnElement): __visit_name__ = 'slice' @@ -913,6 +968,7 @@ ischema_names = { 'interval': INTERVAL, 'interval year to month': INTERVAL, 'interval day to second': INTERVAL, + 'tsvector' : TSVECTOR } @@ -954,25 +1010,30 @@ class PGCompiler(compiler.SQLCompiler): def visit_ilike_op_binary(self, binary, operator, **kw): escape = binary.modifiers.get("escape", None) + return '%s ILIKE %s' % \ (self.process(binary.left, **kw), self.process(binary.right, **kw)) \ - + (escape and - (' ESCAPE ' + self.render_literal_value(escape, None)) - or '') + + ( + ' ESCAPE ' + + self.render_literal_value(escape, sqltypes.STRINGTYPE) + if escape else '' + ) def visit_notilike_op_binary(self, binary, operator, **kw): escape = binary.modifiers.get("escape", None) return '%s NOT ILIKE %s' % \ (self.process(binary.left, **kw), self.process(binary.right, **kw)) \ - + (escape and - (' ESCAPE ' + self.render_literal_value(escape, None)) - or '') + + ( + ' ESCAPE ' + + self.render_literal_value(escape, sqltypes.STRINGTYPE) + if escape else '' + ) def render_literal_value(self, value, type_): value = super(PGCompiler, self).render_literal_value(value, type_) - # TODO: need to inspect "standard_conforming_strings" + if self.dialect._backslash_escapes: value = value.replace('\\', '\\\\') return value @@ -1009,14 +1070,25 @@ class PGCompiler(compiler.SQLCompiler): return "" def for_update_clause(self, select): - if select.for_update == 'nowait': - return " FOR UPDATE NOWAIT" - elif select.for_update == 'read': - return " FOR SHARE" - elif select.for_update == 'read_nowait': - return " FOR SHARE NOWAIT" + + if select._for_update_arg.read: + tmp = " FOR SHARE" else: - return super(PGCompiler, self).for_update_clause(select) + tmp = " FOR UPDATE" + + if select._for_update_arg.of: + tables = util.OrderedSet( + c.table if isinstance(c, expression.ColumnClause) + else c for c in select._for_update_arg.of) + tmp += " OF " + ", ".join( + self.process(table, ashint=True) + for table in tables + ) + + if select._for_update_arg.nowait: + tmp += " NOWAIT" + + return tmp def returning_clause(self, stmt, returning_cols): @@ -1039,12 +1111,15 @@ class PGCompiler(compiler.SQLCompiler): class PGDDLCompiler(compiler.DDLCompiler): def get_column_specification(self, column, **kwargs): + colspec = self.preparer.format_column(column) impl_type = column.type.dialect_impl(self.dialect) if column.primary_key and \ column is column.table._autoincrement_column and \ - not isinstance(impl_type, sqltypes.SmallInteger) and \ ( + self.dialect.supports_smallserial or + not isinstance(impl_type, sqltypes.SmallInteger) + ) and ( column.default is None or ( isinstance(column.default, schema.Sequence) and @@ -1052,6 +1127,8 @@ class PGDDLCompiler(compiler.DDLCompiler): )): if isinstance(impl_type, sqltypes.BigInteger): colspec += " BIGSERIAL" + elif isinstance(impl_type, sqltypes.SmallInteger): + colspec += " SMALLSERIAL" else: colspec += " SERIAL" else: @@ -1069,7 +1146,9 @@ class PGDDLCompiler(compiler.DDLCompiler): return "CREATE TYPE %s AS ENUM (%s)" % ( self.preparer.format_type(type_), - ",".join("'%s'" % e for e in type_.enums) + ", ".join( + self.sql_compiler.process(sql.literal(e), literal_binds=True) + for e in type_.enums) ) def visit_drop_enum_type(self, drop): @@ -1092,31 +1171,29 @@ class PGDDLCompiler(compiler.DDLCompiler): preparer.format_table(index.table) ) - if 'postgresql_using' in index.kwargs: - using = index.kwargs['postgresql_using'] - text += "USING %s " % preparer.quote(using, index.quote) + using = index.dialect_options['postgresql']['using'] + if using: + text += "USING %s " % preparer.quote(using) - ops = index.kwargs.get('postgresql_ops', {}) + ops = index.dialect_options["postgresql"]["ops"] text += "(%s)" \ % ( ', '.join([ - self.sql_compiler.process(expr, include_table=False) + - - + self.sql_compiler.process( + expr.self_group() + if not isinstance(expr, expression.ColumnClause) + else expr, + include_table=False, literal_binds=True) + (c.key in ops and (' ' + ops[c.key]) or '') - - for expr, c in zip(index.expressions, index.columns)]) ) - if 'postgresql_where' in index.kwargs: - whereclause = index.kwargs['postgresql_where'] - else: - whereclause = None + whereclause = index.dialect_options["postgresql"]["where"] if whereclause is not None: - whereclause = sql_util.expression_as_ddl(whereclause) - where_compiled = self.sql_compiler.process(whereclause) + where_compiled = self.sql_compiler.process( + whereclause, include_table=False, + literal_binds=True) text += " WHERE " + where_compiled return text @@ -1128,16 +1205,20 @@ class PGDDLCompiler(compiler.DDLCompiler): elements = [] for c in constraint.columns: op = constraint.operators[c.name] - elements.append(self.preparer.quote(c.name, c.quote)+' WITH '+op) + elements.append(self.preparer.quote(c.name) + ' WITH '+op) text += "EXCLUDE USING %s (%s)" % (constraint.using, ', '.join(elements)) if constraint.where is not None: - sqltext = sql_util.expression_as_ddl(constraint.where) - text += ' WHERE (%s)' % self.sql_compiler.process(sqltext) + text += ' WHERE (%s)' % self.sql_compiler.process( + constraint.where, + literal_binds=True) text += self.define_constraint_deferrability(constraint) return text class PGTypeCompiler(compiler.GenericTypeCompiler): + def visit_TSVECTOR(self, type): + return "TSVECTOR" + def visit_INET(self, type_): return "INET" @@ -1162,6 +1243,9 @@ class PGTypeCompiler(compiler.GenericTypeCompiler): def visit_HSTORE(self, type_): return "HSTORE" + def visit_JSON(self, type_): + return "JSON" + def visit_INT4RANGE(self, type_): return "INT4RANGE" @@ -1250,9 +1334,9 @@ class PGIdentifierPreparer(compiler.IdentifierPreparer): if not type_.name: raise exc.CompileError("Postgresql ENUM type requires a name.") - name = self.quote(type_.name, type_.quote) + name = self.quote(type_.name) if not self.omit_schema and use_schema and type_.schema is not None: - name = self.quote_schema(type_.schema, type_.quote) + "." + name + name = self.quote_schema(type_.schema) + "." + name return name @@ -1328,6 +1412,7 @@ class PGDialect(default.DefaultDialect): supports_native_enum = True supports_native_boolean = True + supports_smallserial = True supports_sequences = True sequences_optional = True @@ -1349,12 +1434,22 @@ class PGDialect(default.DefaultDialect): inspector = PGInspector isolation_level = None - # TODO: need to inspect "standard_conforming_strings" + construct_arguments = [ + (schema.Index, { + "using": False, + "where": None, + "ops": {} + }) + ] + _backslash_escapes = True - def __init__(self, isolation_level=None, **kwargs): + def __init__(self, isolation_level=None, json_serializer=None, + json_deserializer=None, **kwargs): default.DefaultDialect.__init__(self, **kwargs) self.isolation_level = isolation_level + self._json_deserializer = json_deserializer + self._json_serializer = json_serializer def initialize(self, connection): super(PGDialect, self).initialize(connection) @@ -1368,6 +1463,13 @@ class PGDialect(default.DefaultDialect): # psycopg2, others may have placed ENUM here as well self.colspecs.pop(ENUM, None) + # http://www.postgresql.org/docs/9.3/static/release-9-2.html#AEN116689 + self.supports_smallserial = self.server_version_info >= (9, 2) + + self._backslash_escapes = connection.scalar( + "show standard_conforming_strings" + ) == 'off' + def on_connect(self): if self.isolation_level is not None: def connect(conn): @@ -1515,12 +1617,6 @@ class PGDialect(default.DefaultDialect): return bool(cursor.first()) def has_type(self, connection, type_name, schema=None): - bindparams = [ - sql.bindparam('typname', - util.text_type(type_name), type_=sqltypes.Unicode), - sql.bindparam('nspname', - util.text_type(schema), type_=sqltypes.Unicode), - ] if schema is not None: query = """ SELECT EXISTS ( @@ -1530,6 +1626,7 @@ class PGDialect(default.DefaultDialect): AND n.nspname = :nspname ) """ + query = sql.text(query) else: query = """ SELECT EXISTS ( @@ -1538,13 +1635,23 @@ class PGDialect(default.DefaultDialect): AND pg_type_is_visible(t.oid) ) """ - cursor = connection.execute(sql.text(query, bindparams=bindparams)) + query = sql.text(query) + query = query.bindparams( + sql.bindparam('typname', + util.text_type(type_name), type_=sqltypes.Unicode), + ) + if schema is not None: + query = query.bindparams( + sql.bindparam('nspname', + util.text_type(schema), type_=sqltypes.Unicode), + ) + cursor = connection.execute(query) return bool(cursor.scalar()) def _get_server_version_info(self, connection): v = connection.execute("select version()").scalar() m = re.match( - '(?:PostgreSQL|EnterpriseDB) ' + '.*(?:PostgreSQL|EnterpriseDB) ' '(\d+)\.(\d+)(?:\.(\d+))?(?:\.\d+)?(?:devel)?', v) if not m: @@ -1578,12 +1685,10 @@ class PGDialect(default.DefaultDialect): table_name = util.text_type(table_name) if schema is not None: schema = util.text_type(schema) - s = sql.text(query, bindparams=[ - sql.bindparam('table_name', type_=sqltypes.Unicode), - sql.bindparam('schema', type_=sqltypes.Unicode) - ], - typemap={'oid': sqltypes.Integer} - ) + s = sql.text(query).bindparams(table_name=sqltypes.Unicode) + s = s.columns(oid=sqltypes.Integer) + if schema: + s = s.bindparams(sql.bindparam('schema', type_=sqltypes.Unicode)) c = connection.execute(s, table_name=table_name, schema=schema) table_oid = c.scalar() if table_oid is None: @@ -1675,8 +1780,7 @@ class PGDialect(default.DefaultDialect): SQL_COLS = """ SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), - (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) - for 128) + (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) @@ -1883,6 +1987,15 @@ class PGDialect(default.DefaultDialect): n.oid = c.relnamespace ORDER BY 1 """ + # http://www.postgresql.org/docs/9.0/static/sql-createtable.html + FK_REGEX = re.compile( + r'FOREIGN KEY \((.*?)\) REFERENCES (?:(.*?)\.)?(.*?)\((.*?)\)' + r'[\s]?(MATCH (FULL|PARTIAL|SIMPLE)+)?' + r'[\s]?(ON UPDATE (CASCADE|RESTRICT|NO ACTION|SET NULL|SET DEFAULT)+)?' + r'[\s]?(ON DELETE (CASCADE|RESTRICT|NO ACTION|SET NULL|SET DEFAULT)+)?' + r'[\s]?(DEFERRABLE|NOT DEFERRABLE)?' + r'[\s]?(INITIALLY (DEFERRED|IMMEDIATE)+)?' + ) t = sql.text(FK_SQL, typemap={ 'conname': sqltypes.Unicode, @@ -1890,15 +2003,18 @@ class PGDialect(default.DefaultDialect): c = connection.execute(t, table=table_oid) fkeys = [] for conname, condef, conschema in c.fetchall(): - m = re.search('FOREIGN KEY \((.*?)\) REFERENCES ' - '(?:(.*?)\.)?(.*?)\((.*?)\)', condef).groups() + m = re.search(FK_REGEX, condef).groups() constrained_columns, referred_schema, \ - referred_table, referred_columns = m + referred_table, referred_columns, \ + _, match, _, onupdate, _, ondelete, \ + deferrable, _, initially = m + if deferrable is not None: + deferrable = True if deferrable == 'DEFERRABLE' else False constrained_columns = [preparer._unquote_identifier(x) for x in re.split(r'\s*,\s*', constrained_columns)] if referred_schema: - referred_schema =\ + referred_schema = \ preparer._unquote_identifier(referred_schema) elif schema is not None and schema == conschema: # no schema was returned by pg_get_constraintdef(). This @@ -1916,7 +2032,14 @@ class PGDialect(default.DefaultDialect): 'constrained_columns': constrained_columns, 'referred_schema': referred_schema, 'referred_table': referred_table, - 'referred_columns': referred_columns + 'referred_columns': referred_columns, + 'options': { + 'onupdate': onupdate, + 'ondelete': ondelete, + 'deferrable': deferrable, + 'initially': initially, + 'match': match + } } fkeys.append(fkey_d) return fkeys @@ -1926,11 +2049,14 @@ class PGDialect(default.DefaultDialect): table_oid = self.get_table_oid(connection, table_name, schema, info_cache=kw.get('info_cache')) + # cast indkey as varchar since it's an int2vector, + # returned as a list by some drivers such as pypostgresql + IDX_SQL = """ SELECT i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, - a.attname, a.attnum, ix.indkey + a.attname, a.attnum, ix.indkey::varchar FROM pg_class t join pg_index ix on t.oid = ix.indrelid diff --git a/lib/sqlalchemy/dialects/postgresql/constraints.py b/lib/sqlalchemy/dialects/postgresql/constraints.py index 5b8bbe643..f45cef1a2 100644 --- a/lib/sqlalchemy/dialects/postgresql/constraints.py +++ b/lib/sqlalchemy/dialects/postgresql/constraints.py @@ -1,4 +1,4 @@ -# Copyright (C) 2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2013-2014 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 @@ -39,7 +39,7 @@ class ExcludeConstraint(ColumnCollectionConstraint): :param using: Optional string. If set, emit USING <index_method> when issuing DDL for this constraint. Defaults to 'gist'. - + :param where: Optional string. If set, emit WHERE <predicate> when issuing DDL for this constraint. @@ -60,7 +60,7 @@ class ExcludeConstraint(ColumnCollectionConstraint): where = kw.get('where') if where: self.where = expression._literal_as_text(where) - + def copy(self, **kw): elements = [(col, self.operators[col]) for col in self.columns.keys()] diff --git a/lib/sqlalchemy/dialects/postgresql/hstore.py b/lib/sqlalchemy/dialects/postgresql/hstore.py index c645e25d2..76562088d 100644 --- a/lib/sqlalchemy/dialects/postgresql/hstore.py +++ b/lib/sqlalchemy/dialects/postgresql/hstore.py @@ -1,5 +1,5 @@ # postgresql/hstore.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 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 @@ -144,8 +144,10 @@ class HSTORE(sqltypes.Concatenable, sqltypes.TypeEngine): For usage with the SQLAlchemy ORM, it may be desirable to combine the usage of :class:`.HSTORE` with :class:`.MutableDict` dictionary now part of the :mod:`sqlalchemy.ext.mutable` - extension. This extension will allow in-place changes to dictionary - values to be detected by the unit of work:: + extension. This extension will allow "in-place" changes to the + dictionary, e.g. addition of new keys or replacement/removal of existing + keys to/from the current dictionary, to produce events which will be detected + by the unit of work:: from sqlalchemy.ext.mutable import MutableDict @@ -163,6 +165,11 @@ class HSTORE(sqltypes.Concatenable, sqltypes.TypeEngine): session.commit() + When the :mod:`sqlalchemy.ext.mutable` extension is not used, the ORM + will not be alerted to any changes to the contents of an existing dictionary, + unless that dictionary value is re-assigned to the HSTORE-attribute itself, + thus generating a change event. + .. versionadded:: 0.8 .. seealso:: diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py new file mode 100644 index 000000000..2e29185e8 --- /dev/null +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -0,0 +1,199 @@ +# postgresql/json.py +# Copyright (C) 2005-2014 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 __future__ import absolute_import + +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 +from ... import util + +__all__ = ('JSON', 'JSONElement') + + +class JSONElement(elements.BinaryExpression): + """Represents accessing an element of a :class:`.JSON` value. + + The :class:`.JSONElement` is produced whenever using the Python index + operator on an expression that has the type :class:`.JSON`:: + + expr = mytable.c.json_data['some_key'] + + 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`. + + """ + 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 = left._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 evaulated. + + E.g.:: + + select([data_table.c.data['some key'].cast(Integer)]) + + .. seealso:: + + :attr:`.JSONElement.astext` + + """ + if not self._astext: + return self.astext.cast(type_) + else: + return sql.cast(self, type_) + + +class JSON(sqltypes.TypeEngine): + """Represent the Postgresql JSON type. + + The :class:`.JSON` type stores arbitrary JSON format data, e.g.:: + + data_table = Table('data_table', metadata, + Column('id', Integer, primary_key=True), + Column('data', JSON) + ) + + with engine.connect() as conn: + conn.execute( + data_table.insert(), + data = {"key1": "value1", "key2": "value2"} + ) + + :class:`.JSON` provides several operations: + + * Index operations:: + + data_table.c.data['some key'] + + * Index operations returning text (required for text comparison):: + + 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 + ) + + When using the psycopg2 dialect, the json_deserializer is registered + against the database using ``psycopg2.extras.register_default_json``. + + .. versionadded:: 0.9 + + """ + + __visit_name__ = 'JSON' + + class comparator_factory(sqltypes.Concatenable.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): + if isinstance(op, custom_op): + if op.opstring == '->': + return op, sqltypes.Text + return sqltypes.Concatenable.Comparator.\ + _adapt_expression(self, op, other_comparator) + + def bind_processor(self, dialect): + json_serializer = dialect._json_serializer or json.dumps + if util.py2k: + encoding = dialect.encoding + def process(value): + return json_serializer(value).encode(encoding) + else: + def process(value): + 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): + return json_deserializer(value.decode(encoding)) + else: + def process(value): + return json_deserializer(value) + return process + + +ischema_names['json'] = JSON diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py index 0e503746c..bc73f9757 100644 --- a/lib/sqlalchemy/dialects/postgresql/pg8000.py +++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py @@ -1,5 +1,5 @@ # postgresql/pg8000.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 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 @@ -39,7 +39,9 @@ class _PGNumeric(sqltypes.Numeric): def result_processor(self, dialect, coltype): if self.asdecimal: if coltype in _FLOAT_TYPES: - return processors.to_decimal_processor_factory(decimal.Decimal) + return processors.to_decimal_processor_factory( + decimal.Decimal, + self._effective_decimal_return_scale) elif coltype in _DECIMAL_TYPES or coltype in _INT_TYPES: # pg8000 returns Decimal natively for 1700 return None diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index 02eda094e..e9f64f829 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -1,5 +1,5 @@ # postgresql/psycopg2.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 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 @@ -179,6 +179,7 @@ from .base import PGDialect, PGCompiler, \ ENUM, ARRAY, _DECIMAL_TYPES, _FLOAT_TYPES,\ _INT_TYPES from .hstore import HSTORE +from .json import JSON logger = logging.getLogger('sqlalchemy.dialects.postgresql') @@ -191,7 +192,9 @@ class _PGNumeric(sqltypes.Numeric): def result_processor(self, dialect, coltype): if self.asdecimal: if coltype in _FLOAT_TYPES: - return processors.to_decimal_processor_factory(decimal.Decimal) + return processors.to_decimal_processor_factory( + decimal.Decimal, + self._effective_decimal_return_scale) elif coltype in _DECIMAL_TYPES or coltype in _INT_TYPES: # pg8000 returns Decimal natively for 1700 return None @@ -210,23 +213,13 @@ class _PGNumeric(sqltypes.Numeric): class _PGEnum(ENUM): - def __init__(self, *arg, **kw): - super(_PGEnum, self).__init__(*arg, **kw) - if util.py2k: - if self.convert_unicode: - self.convert_unicode = "force" - - -class _PGArray(ARRAY): - def __init__(self, *arg, **kw): - super(_PGArray, self).__init__(*arg, **kw) - if util.py2k: - # FIXME: this check won't work for setups that - # have convert_unicode only on their create_engine(). - if isinstance(self.item_type, sqltypes.String) and \ - self.item_type.convert_unicode: - self.item_type.convert_unicode = "force" - + def result_processor(self, dialect, coltype): + if util.py2k and self.convert_unicode is True: + # we can't easily use PG's extensions here because + # the OID is on the fly, and we need to give it a python + # function anyway - not really worth it. + self.convert_unicode = "force_nocheck" + return super(_PGEnum, self).result_processor(dialect, coltype) class _PGHStore(HSTORE): def bind_processor(self, dialect): @@ -241,6 +234,15 @@ class _PGHStore(HSTORE): else: return super(_PGHStore, self).result_processor(dialect, coltype) + +class _PGJSON(JSON): + + def result_processor(self, dialect, coltype): + if dialect._has_native_json: + return None + else: + return super(_PGJSON, self).result_processor(dialect, coltype) + # When we're handed literal SQL, ensure it's a SELECT-query. Since # 8.3, combining cursors and "FOR UPDATE" has been fine. SERVER_SIDE_CURSOR_RE = re.compile( @@ -325,6 +327,7 @@ class PGDialect_psycopg2(PGDialect): psycopg2_version = (0, 0) _has_native_hstore = False + _has_native_json = False colspecs = util.update_copy( PGDialect.colspecs, @@ -332,8 +335,8 @@ class PGDialect_psycopg2(PGDialect): sqltypes.Numeric: _PGNumeric, ENUM: _PGEnum, # needs force_unicode sqltypes.Enum: _PGEnum, # needs force_unicode - ARRAY: _PGArray, # needs force_unicode HSTORE: _PGHStore, + JSON: _PGJSON } ) @@ -361,6 +364,7 @@ class PGDialect_psycopg2(PGDialect): self._has_native_hstore = self.use_native_hstore and \ self._hstore_oids(connection.connection) \ is not None + self._has_native_json = self.psycopg2_version >= (2, 5) @classmethod def dbapi(cls): @@ -369,7 +373,7 @@ class PGDialect_psycopg2(PGDialect): @util.memoized_property def _isolation_lookup(self): - extensions = __import__('psycopg2.extensions').extensions + from psycopg2 import extensions return { 'AUTOCOMMIT': extensions.ISOLATION_LEVEL_AUTOCOMMIT, 'READ COMMITTED': extensions.ISOLATION_LEVEL_READ_COMMITTED, @@ -407,6 +411,7 @@ class PGDialect_psycopg2(PGDialect): if self.dbapi and self.use_native_unicode: def on_connect(conn): extensions.register_type(extensions.UNICODE, conn) + extensions.register_type(extensions.UNICODEARRAY, conn) fns.append(on_connect) if self.dbapi and self.use_native_hstore: @@ -423,6 +428,11 @@ class PGDialect_psycopg2(PGDialect): array_oid=array_oid) fns.append(on_connect) + if self.dbapi and self._json_deserializer: + def on_connect(conn): + extras.register_default_json(conn, loads=self._json_deserializer) + fns.append(on_connect) + if fns: def on_connect(conn): for fn in fns: diff --git a/lib/sqlalchemy/dialects/postgresql/pypostgresql.py b/lib/sqlalchemy/dialects/postgresql/pypostgresql.py index 289bef114..f030d2c1b 100644 --- a/lib/sqlalchemy/dialects/postgresql/pypostgresql.py +++ b/lib/sqlalchemy/dialects/postgresql/pypostgresql.py @@ -1,5 +1,5 @@ # postgresql/pypostgresql.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 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 diff --git a/lib/sqlalchemy/dialects/postgresql/ranges.py b/lib/sqlalchemy/dialects/postgresql/ranges.py index d03f948a7..57b0c4c30 100644 --- a/lib/sqlalchemy/dialects/postgresql/ranges.py +++ b/lib/sqlalchemy/dialects/postgresql/ranges.py @@ -1,4 +1,4 @@ -# Copyright (C) 2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2013-2014 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 diff --git a/lib/sqlalchemy/dialects/postgresql/zxjdbc.py b/lib/sqlalchemy/dialects/postgresql/zxjdbc.py index 583afc23f..67e7d53e6 100644 --- a/lib/sqlalchemy/dialects/postgresql/zxjdbc.py +++ b/lib/sqlalchemy/dialects/postgresql/zxjdbc.py @@ -1,5 +1,5 @@ # postgresql/zxjdbc.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 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 |