diff options
Diffstat (limited to 'lib/sqlalchemy')
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/__init__.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 160 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 39 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 21 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/operators.py | 20 | ||||
| -rw-r--r-- | lib/sqlalchemy/types.py | 3 | ||||
| -rw-r--r-- | lib/sqlalchemy/util/langhelpers.py | 1 |
7 files changed, 217 insertions, 31 deletions
diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py index f36d71b2b..3c273bd56 100644 --- a/lib/sqlalchemy/dialects/postgresql/__init__.py +++ b/lib/sqlalchemy/dialects/postgresql/__init__.py @@ -11,10 +11,10 @@ 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 + DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect, array __all__ = ( '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' +'DATE', 'BYTEA', 'BOOLEAN', 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'array' ) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index d159649e0..3e2e1eb10 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -192,7 +192,7 @@ import re from ... import sql, schema, exc, util from ...engine import default, reflection -from ...sql import compiler, expression, util as sql_util +from ...sql import compiler, expression, util as sql_util, operators from ... import types as sqltypes try: @@ -339,18 +339,144 @@ class UUID(sqltypes.TypeEngine): PGUuid = UUID +class _Slice(expression.ColumnElement): + __visit_name__ = 'slice' + type = sqltypes.NULLTYPE + def __init__(self, slice_, source_comparator): + self.start = source_comparator._check_literal( + source_comparator.expr, + operators.getitem, slice_.start) + self.stop = source_comparator._check_literal( + source_comparator.expr, + operators.getitem, slice_.stop) + +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): + return self + class ARRAY(sqltypes.Concatenable, sqltypes.TypeEngine): """Postgresql ARRAY type. Represents values as Python lists. - The ARRAY type may not be supported on all DBAPIs. + 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]}) + + .. versionadded:: 0.8 Added support for index and slice operations + to the :class:`.ARRAY` type, including support for UPDATE + statements. + + 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): + def __getitem__(self, index): + if isinstance(index, slice): + index = _Slice(index, self) + return_type = self.type + else: + return_type = self.type.item_type + return self._binary_operate(self.expr, operators.getitem, index, + result_type=return_type) + + comparator_factory = Comparator + def __init__(self, item_type, as_tuple=False, dimensions=None): """Construct an ARRAY. @@ -363,8 +489,7 @@ class ARRAY(sqltypes.Concatenable, sqltypes.TypeEngine): :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. The type mapping figures out on - the fly + ``ARRAY(ARRAY(Integer))`` or such. :param as_tuple=False: Specify whether return results should be converted to tuples from lists. DBAPIs such @@ -619,15 +744,31 @@ ischema_names = { class PGCompiler(compiler.SQLCompiler): + def visit_array(self, element, **kw): + return "ARRAY[%s]" % self.visit_clauselist(element, **kw) + + def visit_slice(self, element, **kw): + return "%s:%s" % ( + self.process(element.start, **kw), + self.process(element.stop, **kw), + ) + + def visit_getitem_binary(self, binary, operator, **kw): + return "%s[%s]" % ( + self.process(binary.left, **kw), + self.process(binary.right, **kw) + ) + def visit_match_op_binary(self, binary, operator, **kw): return "%s @@ to_tsquery(%s)" % ( - self.process(binary.left), - self.process(binary.right)) + self.process(binary.left, **kw), + self.process(binary.right, **kw)) def visit_ilike_op_binary(self, binary, operator, **kw): escape = binary.modifiers.get("escape", None) return '%s ILIKE %s' % \ - (self.process(binary.left), self.process(binary.right)) \ + (self.process(binary.left, **kw), + self.process(binary.right, **kw)) \ + (escape and (' ESCAPE ' + self.render_literal_value(escape, None)) or '') @@ -635,7 +776,8 @@ class PGCompiler(compiler.SQLCompiler): def visit_notilike_op_binary(self, binary, operator, **kw): escape = binary.modifiers.get("escape", None) return '%s NOT ILIKE %s' % \ - (self.process(binary.left), self.process(binary.right)) \ + (self.process(binary.left, **kw), + self.process(binary.right, **kw)) \ + (escape and (' ESCAPE ' + self.render_literal_value(escape, None)) or '') @@ -653,7 +795,7 @@ class PGCompiler(compiler.SQLCompiler): def limit_clause(self, select): text = "" if select._limit is not None: - text += " \n LIMIT " + self.process(sql.literal(select._limit)) + text += " \n LIMIT " + self.process(sql.literal(select._limit)) if select._offset is not None: if select._limit is None: text += " \n LIMIT ALL" diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 5d842f3d6..c56b7fc37 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -411,7 +411,8 @@ class SQLCompiler(engine.Compiled): within_columns_clause=False, **kw) - def visit_column(self, column, add_to_result_map=None, **kwargs): + def visit_column(self, column, add_to_result_map=None, + include_table=True, **kwargs): name = orig_name = column.name if name is None: raise exc.CompileError("Cannot compile Column object until " @@ -438,7 +439,7 @@ class SQLCompiler(engine.Compiled): name = self.preparer.quote(name, column.quote) table = column.table - if table is None or not table.named_with_column: + if table is None or not include_table or not table.named_with_column: return name else: if table.schema: @@ -1329,16 +1330,13 @@ class SQLCompiler(engine.Compiled): text += table_text text += ' SET ' - if extra_froms and self.render_table_with_column_in_update_from: - text += ', '.join( - self.visit_column(c[0]) + - '=' + c[1] for c in colparams - ) - else: - text += ', '.join( - self.preparer.quote(c[0].name, c[0].quote) + + include_table = extra_froms and \ + self.render_table_with_column_in_update_from + text += ', '.join( + c[0]._compiler_dispatch(self, + include_table=include_table) + '=' + c[1] for c in colparams - ) + ) if update_stmt._returning: self.returning = update_stmt._returning @@ -1414,12 +1412,25 @@ class SQLCompiler(engine.Compiled): if not stmt.parameters or key not in stmt.parameters) + # create a list of column assignment clauses as tuples + values = [] + if stmt.parameters is not None: for k, v in stmt.parameters.iteritems(): - parameters.setdefault(sql._column_as_key(k), v) + colkey = sql._column_as_key(k) + if colkey is not None: + parameters.setdefault(colkey, v) + else: + # a non-Column expression on the left side; + # add it to values() in an "as-is" state, + # coercing right side to bound param + if sql._is_literal(v): + v = self.process(sql.bindparam(None, v, type_=k.type)) + else: + v = self.process(v.self_group()) + + values.append((k, v)) - # create a list of column assignment clauses as tuples - values = [] need_pks = self.isinsert and \ not self.inline and \ diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index d02710d9f..41f4910a7 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -1404,7 +1404,10 @@ def _column_as_key(element): return element if hasattr(element, '__clause_element__'): element = element.__clause_element__() - return element.key + try: + return element.key + except AttributeError: + return None def _clause_element_as_expr(element): if hasattr(element, '__clause_element__'): @@ -1940,7 +1943,7 @@ class _DefaultColumnComparator(operators.ColumnOperators): type_=sqltypes.BOOLEANTYPE, negate=negate, modifiers=kwargs) - def _binary_operate(self, expr, op, obj, reverse=False): + def _binary_operate(self, expr, op, obj, reverse=False, result_type=None): obj = self._check_literal(expr, op, obj) if reverse: @@ -1948,7 +1951,9 @@ class _DefaultColumnComparator(operators.ColumnOperators): else: left, right = expr, obj - op, result_type = left.comparator._adapt_expression(op, right.comparator) + if result_type is None: + op, result_type = left.comparator._adapt_expression( + op, right.comparator) return BinaryExpression(left, right, op, type_=result_type) @@ -2005,6 +2010,11 @@ class _DefaultColumnComparator(operators.ColumnOperators): return self._boolean_compare(expr, op, ClauseList(*args).self_group(against=op), negate=negate_op) + + def _unsupported_impl(self, expr, op, *arg, **kw): + raise NotImplementedError("Operator '%s' is not supported on " + "this expression" % op.__name__) + def _neg_impl(self, expr, op, **kw): """See :meth:`.ColumnOperators.__neg__`.""" return UnaryExpression(expr, operator=operators.neg) @@ -2097,6 +2107,7 @@ class _DefaultColumnComparator(operators.ColumnOperators): "startswith_op": (_startswith_impl,), "endswith_op": (_endswith_impl,), "neg": (_neg_impl,), + "getitem": (_unsupported_impl,), } @@ -3260,8 +3271,10 @@ class Tuple(ClauseList, ColumnElement): def __init__(self, *clauses, **kw): clauses = [_literal_as_binds(c) for c in clauses] + self.type = kw.pop('type_', None) + if self.type is None: + self.type = _type_from_args(clauses) super(Tuple, self).__init__(*clauses, **kw) - self.type = _type_from_args(clauses) @property def _select_iterable(self): diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index 07fb417fb..f1607c884 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -10,7 +10,8 @@ """Defines operators used in SQL expressions.""" from operator import ( - and_, or_, inv, add, mul, sub, mod, truediv, lt, le, ne, gt, ge, eq, neg + and_, or_, inv, add, mul, sub, mod, truediv, lt, le, ne, gt, ge, eq, neg, + getitem ) # Py2K @@ -305,6 +306,15 @@ class ColumnOperators(Operators): """ return self.operate(neg) + def __getitem__(self, index): + """Implement the [] operator. + + This can be used by some database-specific types + such as Postgresql ARRAY and HSTORE. + + """ + return self.operate(getitem, index) + def concat(self, other): """Implement the 'concat' operator. @@ -591,12 +601,18 @@ def is_ordering_modifier(op): _associative = _commutative.union([concat_op, and_, or_]) +_natural_self_precedent = _associative.union([getitem]) +"""Operators where if we have (a op b) op c, we don't want to +parenthesize (a op b). + +""" _smallest = symbol('_smallest', canonical=-100) _largest = symbol('_largest', canonical=100) _PRECEDENCE = { from_: 15, + getitem: 15, mul: 7, truediv: 7, # Py2K @@ -637,7 +653,7 @@ _PRECEDENCE = { def is_precedent(operator, against): - if operator is against and operator in _associative: + if operator is against and operator in _natural_self_precedent: return False else: return (_PRECEDENCE.get(operator, diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py index a082daf63..eeb19496b 100644 --- a/lib/sqlalchemy/types.py +++ b/lib/sqlalchemy/types.py @@ -46,6 +46,9 @@ class TypeEngine(AbstractType): """Base class for custom comparison operations defined at the type level. See :attr:`.TypeEngine.comparator_factory`. + The public base class for :class:`.TypeEngine.Comparator` + is :class:`.ColumnOperators`. + """ def __init__(self, expr): diff --git a/lib/sqlalchemy/util/langhelpers.py b/lib/sqlalchemy/util/langhelpers.py index 9258619ec..ac4b5527a 100644 --- a/lib/sqlalchemy/util/langhelpers.py +++ b/lib/sqlalchemy/util/langhelpers.py @@ -404,6 +404,7 @@ def monkeypatch_proxied_specials(into_cls, from_cls, skip=None, only=None, dunders = [m for m in dir(from_cls) if (m.startswith('__') and m.endswith('__') and not hasattr(into_cls, m) and m not in skip)] + for method in dunders: try: fn = getattr(from_cls, method) |