diff options
Diffstat (limited to 'lib/sqlalchemy/sql/dml.py')
| -rw-r--r-- | lib/sqlalchemy/sql/dml.py | 202 |
1 files changed, 61 insertions, 141 deletions
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): |