diff options
Diffstat (limited to 'lib/sqlalchemy/sql')
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 59 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/dml.py | 202 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 3 |
3 files changed, 113 insertions, 151 deletions
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 6370b1227..5d05cbc29 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1761,11 +1761,12 @@ class SQLCompiler(Compiled): '=' + c[1] for c in colparams ) - if update_stmt._returning: - self.returning = update_stmt._returning + if self.returning or update_stmt._returning: + if not self.returning: + self.returning = update_stmt._returning if self.returning_precedes_values: text += " " + self.returning_clause( - update_stmt, update_stmt._returning) + update_stmt, self.returning) if extra_froms: extra_from_text = self.update_from_clause( @@ -1785,7 +1786,7 @@ class SQLCompiler(Compiled): if self.returning and not self.returning_precedes_values: text += " " + self.returning_clause( - update_stmt, update_stmt._returning) + update_stmt, self.returning) self.stack.pop(-1) @@ -1866,6 +1867,19 @@ class SQLCompiler(Compiled): self.dialect.implicit_returning and \ stmt.table.implicit_returning + if self.isinsert: + implicit_return_defaults = implicit_returning and stmt._return_defaults + elif self.isupdate: + implicit_return_defaults = self.dialect.implicit_returning and \ + stmt.table.implicit_returning and \ + stmt._return_defaults + + if implicit_return_defaults: + if stmt._return_defaults is True: + implicit_return_defaults = set(stmt.table.c) + else: + implicit_return_defaults = set(stmt._return_defaults) + postfetch_lastrowid = need_pks and self.dialect.postfetch_lastrowid check_columns = {} @@ -1928,6 +1942,10 @@ class SQLCompiler(Compiled): elif c.primary_key and implicit_returning: self.returning.append(c) value = self.process(value.self_group()) + elif implicit_return_defaults and \ + c in implicit_return_defaults: + self.returning.append(c) + value = self.process(value.self_group()) else: self.postfetch.append(c) value = self.process(value.self_group()) @@ -1984,14 +2002,20 @@ class SQLCompiler(Compiled): not self.dialect.sequences_optional): proc = self.process(c.default) values.append((c, proc)) - if not c.primary_key: + if implicit_return_defaults and \ + c in implicit_return_defaults: + self.returning.append(c) + elif not c.primary_key: self.postfetch.append(c) elif c.default.is_clause_element: values.append( (c, self.process(c.default.arg.self_group())) ) - if not c.primary_key: + if implicit_return_defaults and \ + c in implicit_return_defaults: + self.returning.append(c) + elif not c.primary_key: # dont add primary key column to postfetch self.postfetch.append(c) else: @@ -2000,8 +2024,14 @@ class SQLCompiler(Compiled): ) self.prefetch.append(c) elif c.server_default is not None: - if not c.primary_key: + if implicit_return_defaults and \ + c in implicit_return_defaults: + self.returning.append(c) + elif not c.primary_key: self.postfetch.append(c) + elif implicit_return_defaults and \ + c in implicit_return_defaults: + self.returning.append(c) elif self.isupdate: if c.onupdate is not None and not c.onupdate.is_sequence: @@ -2009,14 +2039,25 @@ class SQLCompiler(Compiled): values.append( (c, self.process(c.onupdate.arg.self_group())) ) - self.postfetch.append(c) + if implicit_return_defaults and \ + c in implicit_return_defaults: + self.returning.append(c) + else: + self.postfetch.append(c) else: values.append( (c, self._create_crud_bind_param(c, None)) ) self.prefetch.append(c) elif c.server_onupdate is not None: - self.postfetch.append(c) + if implicit_return_defaults and \ + c in implicit_return_defaults: + self.returning.append(c) + else: + self.postfetch.append(c) + elif implicit_return_defaults and \ + c in implicit_return_defaults: + self.returning.append(c) if parameters and stmt_parameters: check = set(parameters).intersection( diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py index cbebf7d55..abbd05efe 100644 --- a/lib/sqlalchemy/sql/dml.py +++ b/lib/sqlalchemy/sql/dml.py @@ -104,9 +104,14 @@ class UpdateBase(HasPrefixes, Executable, ClauseElement): read the documentation notes for the database in use in order to determine the availability of RETURNING. + .. seealso:: + + :meth:`.ValuesBase.return_defaults` + """ self._returning = cols + @_generative def with_hint(self, text, selectable=None, dialect_name="*"): """Add a table hint for a single table to this @@ -303,6 +308,58 @@ class ValuesBase(UpdateBase): else: self.parameters.update(kwargs) + @_generative + def return_defaults(self, *cols): + """If available, make use of a RETURNING clause for the purpose + of fetching server-side expressions and defaults. + + When used against a backend that supports RETURNING, all column + values generated by SQL expression or server-side-default will be added + to any existing RETURNING clause, excluding one that is specified + by the :meth:`.UpdateBase.returning` method. The column values + will then be available on the result using the + :meth:`.ResultProxy.server_returned_defaults` method as a + dictionary, referring to values keyed to the :meth:`.Column` object + as well as its ``.key``. + + This method differs from :meth:`.UpdateBase.returning` in these ways: + + 1. It is compatible with any backend. Backends that don't support + RETURNING will skip the usage of the feature, rather than raising + an exception. The return value of :attr:`.ResultProxy.returned_defaults` + will be ``None`` + + 2. It is compatible with the existing logic to fetch auto-generated + primary key values, also known as "implicit returning". Backends that + support RETURNING will automatically make use of RETURNING in order + to fetch the value of newly generated primary keys; while the + :meth:`.UpdateBase.returning` method circumvents this behavior, + :meth:`.UpdateBase.return_defaults` leaves it intact. + + 3. :meth:`.UpdateBase.returning` leaves the cursor's rows ready for + fetching using methods like :meth:`.ResultProxy.fetchone`, whereas + :meth:`.ValuesBase.return_defaults` fetches the row internally. + While all DBAPI backends observed so far seem to only support + RETURNING with single-row executions, + technically :meth:`.UpdateBase.returning` would support a backend + that can deliver multiple RETURNING rows as well. However + :meth:`.ValuesBase.return_defaults` is single-row by definition. + + :param cols: optional list of column key names or :class:`.Column` + objects. If omitted, all column expressions evaulated on the server + are added to the returning list. + + .. versionadded:: 0.9.0 + + .. seealso:: + + :meth:`.UpdateBase.returning` + + :meth:`.ResultProxy.returned_defaults` + + """ + self._return_defaults = cols or True + class Insert(ValuesBase): """Represent an INSERT construct. @@ -326,52 +383,15 @@ class Insert(ValuesBase): bind=None, prefixes=None, returning=None, + return_defaults=False, **kwargs): - """Construct an :class:`.Insert` object. - - Similar functionality is available via the - :meth:`~.TableClause.insert` method on - :class:`~.schema.Table`. - - :param table: :class:`.TableClause` which is the subject of the insert. - - :param values: collection of values to be inserted; see - :meth:`.Insert.values` for a description of allowed formats here. - Can be omitted entirely; a :class:`.Insert` construct will also - dynamically render the VALUES clause at execution time based on - the parameters passed to :meth:`.Connection.execute`. - - :param inline: if True, SQL defaults will be compiled 'inline' into the - statement and not pre-executed. - - If both `values` and compile-time bind parameters are present, the - compile-time bind parameters override the information specified - within `values` on a per-key basis. - - The keys within `values` can be either :class:`~sqlalchemy.schema.Column` - objects or their string identifiers. Each key may reference one of: - - * a literal data value (i.e. string, number, etc.); - * a Column object; - * a SELECT statement. - - If a ``SELECT`` statement is specified which references this - ``INSERT`` statement's table, the statement will be correlated - against the ``INSERT`` statement. - - .. seealso:: - - :ref:`coretutorial_insert_expressions` - SQL Expression Tutorial - - :ref:`inserts_and_updates` - SQL Expression Tutorial - - """ ValuesBase.__init__(self, table, values, prefixes) self._bind = bind self.select = None self.inline = inline self._returning = returning self.kwargs = kwargs + self._return_defaults = return_defaults def get_children(self, **kwargs): if self.select is not None: @@ -446,109 +466,8 @@ class Update(ValuesBase): bind=None, prefixes=None, returning=None, + return_defaults=False, **kwargs): - """Construct an :class:`.Update` object. - - E.g.:: - - from sqlalchemy import update - - stmt = update(users).where(users.c.id==5).\\ - values(name='user #5') - - Similar functionality is available via the - :meth:`~.TableClause.update` method on - :class:`.Table`:: - - stmt = users.update().\\ - where(users.c.id==5).\\ - values(name='user #5') - - :param table: A :class:`.Table` object representing the database - table to be updated. - - :param whereclause: Optional SQL expression describing the ``WHERE`` - condition of the ``UPDATE`` statement. Modern applications - may prefer to use the generative :meth:`~Update.where()` - method to specify the ``WHERE`` clause. - - The WHERE clause can refer to multiple tables. - For databases which support this, an ``UPDATE FROM`` clause will - be generated, or on MySQL, a multi-table update. The statement - will fail on databases that don't have support for multi-table - update statements. A SQL-standard method of referring to - additional tables in the WHERE clause is to use a correlated - subquery:: - - users.update().values(name='ed').where( - users.c.name==select([addresses.c.email_address]).\\ - where(addresses.c.user_id==users.c.id).\\ - as_scalar() - ) - - .. versionchanged:: 0.7.4 - The WHERE clause can refer to multiple tables. - - :param values: - Optional dictionary which specifies the ``SET`` conditions of the - ``UPDATE``. If left as ``None``, the ``SET`` - conditions are determined from those parameters passed to the - statement during the execution and/or compilation of the - statement. When compiled standalone without any parameters, - the ``SET`` clause generates for all columns. - - Modern applications may prefer to use the generative - :meth:`.Update.values` method to set the values of the - UPDATE statement. - - :param inline: - if True, SQL defaults present on :class:`.Column` objects via - the ``default`` keyword will be compiled 'inline' into the statement - and not pre-executed. This means that their values will not - be available in the dictionary returned from - :meth:`.ResultProxy.last_updated_params`. - - If both ``values`` and compile-time bind parameters are present, the - compile-time bind parameters override the information specified - within ``values`` on a per-key basis. - - The keys within ``values`` can be either :class:`.Column` - objects or their string identifiers (specifically the "key" of the - :class:`.Column`, normally but not necessarily equivalent to - its "name"). Normally, the - :class:`.Column` objects used here are expected to be - part of the target :class:`.Table` that is the table - to be updated. However when using MySQL, a multiple-table - UPDATE statement can refer to columns from any of - the tables referred to in the WHERE clause. - - The values referred to in ``values`` are typically: - - * a literal data value (i.e. string, number, etc.) - * a SQL expression, such as a related :class:`.Column`, - a scalar-returning :func:`.select` construct, - etc. - - When combining :func:`.select` constructs within the values - clause of an :func:`.update` construct, - the subquery represented by the :func:`.select` should be - *correlated* to the parent table, that is, providing criterion - which links the table inside the subquery to the outer table - being updated:: - - users.update().values( - name=select([addresses.c.email_address]).\\ - where(addresses.c.user_id==users.c.id).\\ - as_scalar() - ) - - .. seealso:: - - :ref:`inserts_and_updates` - SQL Expression - Language Tutorial - - - """ ValuesBase.__init__(self, table, values, prefixes) self._bind = bind self._returning = returning @@ -558,6 +477,7 @@ class Update(ValuesBase): self._whereclause = None self.inline = inline self.kwargs = kwargs + self._return_defaults = return_defaults def get_children(self, **kwargs): diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index bbbe0b235..01091bc0a 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -49,7 +49,7 @@ from .selectable import Alias, Join, Select, Selectable, TableClause, \ subquery, HasPrefixes, Exists, ScalarSelect -from .dml import Insert, Update, Delete +from .dml import Insert, Update, Delete, UpdateBase, ValuesBase # factory functions - these pull class-bound constructors and classmethods # from SQL elements and selectables into public functions. This allows @@ -101,6 +101,7 @@ from .elements import _literal_as_text, _clause_element_as_expr,\ from .selectable import _interpret_as_from + # old names for compatibility _Executable = Executable _BindParamClause = BindParameter |