sqlalchemy.sql.dml.ValuesBase

Undocumented

Make use of a :term:`RETURNING` clause for the purpose of fetching server-side expressions and defaults.

E.g.:

stmt = table.insert().values(data='newdata').return_defaults()

result = connection.execute(stmt)

server_created_at = result.returned_defaults['created_at']

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, provided that .UpdateBase.returning is not used simultaneously. The column values will then be available on the result using the _engine.CursorResult.returned_defaults accessor as a dictionary, referring to values keyed to the _schema.Column object as well as its .key.

This method differs from .UpdateBase.returning in these ways:

1. .ValuesBase.return_defaults is only intended for use with an INSERT or an UPDATE statement that matches exactly one row per parameter set. While the RETURNING construct in the general sense supports multiple rows for a multi-row UPDATE or DELETE statement, or for special cases of INSERT that return multiple rows (e.g. INSERT from SELECT, multi-valued VALUES clause), .ValuesBase.return_defaults is intended only for an "ORM-style" single-row INSERT/UPDATE statement. The row returned by the statement is also consumed implicitly when .ValuesBase.return_defaults is used. By contrast, .UpdateBase.returning leaves the RETURNING result-set intact with a collection of any number of rows.

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 .UpdateBase.returning method circumvents this behavior, .ValuesBase.return_defaults leaves it intact.

3. It can be called against any backend. Backends that don't support RETURNING will skip the usage of the feature, rather than raising an exception. The return value of _engine.CursorResult.returned_defaults will be None

4. An INSERT statement invoked with executemany() is supported if the backend database driver supports the insert_executemany_returning feature, currently this includes PostgreSQL with psycopg2. When executemany is used, the _engine.CursorResult.returned_defaults_rows and _engine.CursorResult.inserted_primary_key_rows accessors will return the inserted defaults and primary keys.

   New in version 1.4.

.ValuesBase.return_defaults is used by the ORM to provide an efficient implementation for the eager_defaults feature of .mapper.

Specify a fixed VALUES clause for an INSERT statement, or the SET clause for an UPDATE.

Note that the _expression.Insert and _expression.Update constructs support per-execution time formatting of the VALUES and/or SET clauses, based on the arguments passed to _engine.Connection.execute. However, the .ValuesBase.values method can be used to "fix" a particular set of parameters into the statement.

Multiple calls to .ValuesBase.values will produce a new construct, each one with the parameter list modified to include the new parameters sent. In the typical case of a single dictionary of parameters, the newly passed keys will replace the same keys in the previous construct. In the case of a list-based "multiple values" construct, each new list of values is extended onto the existing list of values.

users.insert().values({"name": "some name"})

users.update().values({"name": "some new name"})

users.insert().values((5, "some name"))

users.insert().values([
                    {"name": "some name"},
                    {"name": "some other name"},
                    {"name": "yet another name"},
                ])

INSERT INTO users (name) VALUES
                (:name_1),
                (:name_2),
                (:name_3)

users.insert().values(name="some name")

users.update().where(users.c.id==5).values(name="some name")