1
.. _metadata_defaults_toplevel:
3
.. module:: sqlalchemy.schema
5
Column Insert/Update Defaults
6
==============================
8
SQLAlchemy provides a very rich featureset regarding column level events which
9
take place during INSERT and UPDATE statements. Options include:
11
* Scalar values used as defaults during INSERT and UPDATE operations
12
* Python functions which execute upon INSERT and UPDATE operations
13
* SQL expressions which are embedded in INSERT statements (or in some cases execute beforehand)
14
* SQL expressions which are embedded in UPDATE statements
15
* Server side default values used during INSERT
16
* Markers for server-side triggers used during UPDATE
18
The general rule for all insert/update defaults is that they only take effect
19
if no value for a particular column is passed as an ``execute()`` parameter;
20
otherwise, the given value is used.
25
The simplest kind of default is a scalar value used as the default value of a column::
27
Table("mytable", meta,
28
Column("somecolumn", Integer, default=12)
31
Above, the value "12" will be bound as the column value during an INSERT if no
32
other value is supplied.
34
A scalar value may also be associated with an UPDATE statement, though this is
35
not very common (as UPDATE statements are usually looking for dynamic
38
Table("mytable", meta,
39
Column("somecolumn", Integer, onupdate=25)
43
Python-Executed Functions
44
-------------------------
46
The ``default`` and ``onupdate`` keyword arguments also accept Python
47
functions. These functions are invoked at the time of insert or update if no
48
other value for that column is supplied, and the value returned is used for
49
the column's value. Below illustrates a crude "sequence" that assigns an
50
incrementing counter to a primary key column::
52
# a function which counts upwards
59
t = Table("mytable", meta,
60
Column('id', Integer, primary_key=True, default=mydefault),
63
It should be noted that for real "incrementing sequence" behavior, the
64
built-in capabilities of the database should normally be used, which may
65
include sequence objects or other autoincrementing capabilities. For primary
66
key columns, SQLAlchemy will in most cases use these capabilities
67
automatically. See the API documentation for
68
:class:`~sqlalchemy.schema.Column` including the ``autoincrement`` flag, as
69
well as the section on :class:`~sqlalchemy.schema.Sequence` later in this
70
chapter for background on standard primary key generation techniques.
72
To illustrate onupdate, we assign the Python ``datetime`` function ``now`` to
73
the ``onupdate`` attribute::
77
t = Table("mytable", meta,
78
Column('id', Integer, primary_key=True),
80
# define 'last_updated' to be populated with datetime.now()
81
Column('last_updated', DateTime, onupdate=datetime.datetime.now),
84
When an update statement executes and no value is passed for ``last_updated``,
85
the ``datetime.datetime.now()`` Python function is executed and its return
86
value used as the value for ``last_updated``. Notice that we provide ``now``
87
as the function itself without calling it (i.e. there are no parenthesis
88
following) - SQLAlchemy will execute the function at the time the statement
91
Context-Sensitive Default Functions
92
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
94
The Python functions used by ``default`` and ``onupdate`` may also make use of
95
the current statement's context in order to determine a value. The `context`
96
of a statement is an internal SQLAlchemy object which contains all information
97
about the statement being executed, including its source expression, the
98
parameters associated with it and the cursor. The typical use case for this
99
context with regards to default generation is to have access to the other
100
values being inserted or updated on the row. To access the context, provide a
101
function that accepts a single ``context`` argument::
103
def mydefault(context):
104
return context.current_parameters['counter'] + 12
106
t = Table('mytable', meta,
107
Column('counter', Integer),
108
Column('counter_plus_twelve', Integer, default=mydefault, onupdate=mydefault)
111
Above we illustrate a default function which will execute for all INSERT and
112
UPDATE statements where a value for ``counter_plus_twelve`` was otherwise not
113
provided, and the value will be that of whatever value is present in the
114
execution for the ``counter`` column, plus the number 12.
116
While the context object passed to the default function has many attributes,
117
the ``current_parameters`` member is a special member provided only during the
118
execution of a default function for the purposes of deriving defaults from its
119
existing values. For a single statement that is executing many sets of bind
120
parameters, the user-defined function is called for each set of parameters,
121
and ``current_parameters`` will be provided with each individual parameter set
127
The "default" and "onupdate" keywords may also be passed SQL expressions,
128
including select statements or direct function calls::
130
t = Table("mytable", meta,
131
Column('id', Integer, primary_key=True),
133
# define 'create_date' to default to now()
134
Column('create_date', DateTime, default=func.now()),
136
# define 'key' to pull its default from the 'keyvalues' table
137
Column('key', String(20), default=keyvalues.select(keyvalues.c.type='type1', limit=1)),
139
# define 'last_modified' to use the current_timestamp SQL function on update
140
Column('last_modified', DateTime, onupdate=func.utc_timestamp())
143
Above, the ``create_date`` column will be populated with the result of the
144
``now()`` SQL function (which, depending on backend, compiles into ``NOW()``
145
or ``CURRENT_TIMESTAMP`` in most cases) during an INSERT statement, and the
146
``key`` column with the result of a SELECT subquery from another table. The
147
``last_modified`` column will be populated with the value of
148
``UTC_TIMESTAMP()``, a function specific to MySQL, when an UPDATE statement is
149
emitted for this table.
151
Note that when using ``func`` functions, unlike when using Python `datetime`
152
functions we *do* call the function, i.e. with parenthesis "()" - this is
153
because what we want in this case is the return value of the function, which
154
is the SQL expression construct that will be rendered into the INSERT or
157
The above SQL functions are usually executed "inline" with the INSERT or
158
UPDATE statement being executed, meaning, a single statement is executed which
159
embeds the given expressions or subqueries within the VALUES or SET clause of
160
the statement. Although in some cases, the function is "pre-executed" in a
161
SELECT statement of its own beforehand. This happens when all of the following
164
* the column is a primary key column
165
* the database dialect does not support a usable ``cursor.lastrowid`` accessor
166
(or equivalent); this currently includes PostgreSQL, Oracle, and Firebird, as
167
well as some MySQL dialects.
168
* the dialect does not support the "RETURNING" clause or similar, or the
169
``implicit_returning`` flag is set to ``False`` for the dialect. Dialects
170
which support RETURNING currently include Postgresql, Oracle, Firebird, and
172
* the statement is a single execution, i.e. only supplies one set of
173
parameters and doesn't use "executemany" behavior
174
* the ``inline=True`` flag is not set on the
175
:class:`~sqlalchemy.sql.expression.Insert()` or
176
:class:`~sqlalchemy.sql.expression.Update()` construct, and the statement has
177
not defined an explicit `returning()` clause.
179
Whether or not the default generation clause "pre-executes" is not something
180
that normally needs to be considered, unless it is being addressed for
183
When the statement is executed with a single set of parameters (that is, it is
184
not an "executemany" style execution), the returned
185
:class:`~sqlalchemy.engine.ResultProxy` will contain a collection
186
accessible via ``result.postfetch_cols()`` which contains a list of all
187
:class:`~sqlalchemy.schema.Column` objects which had an inline-executed
188
default. Similarly, all parameters which were bound to the statement,
189
including all Python and SQL expressions which were pre-executed, are present
190
in the ``last_inserted_params()`` or ``last_updated_params()`` collections on
191
:class:`~sqlalchemy.engine.ResultProxy`. The ``inserted_primary_key``
192
collection contains a list of primary key values for the row inserted (a list
193
so that single-column and composite-column primary keys are represented in the
199
A variant on the SQL expression default is the ``server_default``, which gets
200
placed in the CREATE TABLE statement during a ``create()`` operation:
202
.. sourcecode:: python+sql
204
t = Table('test', meta,
205
Column('abc', String(20), server_default='abc'),
206
Column('created_at', DateTime, server_default=text("sysdate"))
209
A create call for the above table will produce::
212
abc varchar(20) default 'abc',
213
created_at datetime default sysdate
216
The behavior of ``server_default`` is similar to that of a regular SQL
217
default; if it's placed on a primary key column for a database which doesn't
218
have a way to "postfetch" the ID, and the statement is not "inlined", the SQL
219
expression is pre-executed; otherwise, SQLAlchemy lets the default fire off on
220
the database side normally.
223
.. _triggered_columns:
228
Columns with values set by a database trigger or other external process may be
229
called out using :class:`.FetchedValue` as a marker::
231
t = Table('test', meta,
232
Column('abc', String(20), server_default=FetchedValue()),
233
Column('def', String(20), server_onupdate=FetchedValue())
236
.. versionchanged:: 0.8.0b2,0.7.10
237
The ``for_update`` argument on :class:`.FetchedValue` is set automatically
238
when specified as the ``server_onupdate`` argument. If using an older version,
239
specify the onupdate above as ``server_onupdate=FetchedValue(for_update=True)``.
241
These markers do not emit a "default" clause when the table is created,
242
however they do set the same internal flags as a static ``server_default``
243
clause, providing hints to higher-level tools that a "post-fetch" of these
244
rows should be performed after an insert or update.
248
It's generally not appropriate to use :class:`.FetchedValue` in
249
conjunction with a primary key column, particularly when using the
250
ORM or any other scenario where the :attr:`.ResultProxy.inserted_primary_key`
251
attribute is required. This is becaue the "post-fetch" operation requires
252
that the primary key value already be available, so that the
253
row can be selected on its primary key.
255
For a server-generated primary key value, all databases provide special
256
accessors or other techniques in order to acquire the "last inserted
257
primary key" column of a table. These mechanisms aren't affected by the presence
258
of :class:`.FetchedValue`. For special situations where triggers are
259
used to generate primary key values, and the database in use does not
260
support the ``RETURNING`` clause, it may be necessary to forego the usage
261
of the trigger and instead apply the SQL expression or function as a
262
"pre execute" expression::
264
t = Table('test', meta,
265
Column('abc', MyType, default=func.generate_new_value(), primary_key=True)
268
Where above, when :meth:`.Table.insert` is used,
269
the ``func.generate_new_value()`` expression will be pre-executed
270
in the context of a scalar ``SELECT`` statement, and the new value will
271
be applied to the subsequent ``INSERT``, while at the same time being
272
made available to the :attr:`.ResultProxy.inserted_primary_key`
279
SQLAlchemy represents database sequences using the
280
:class:`~sqlalchemy.schema.Sequence` object, which is considered to be a
281
special case of "column default". It only has an effect on databases which
282
have explicit support for sequences, which currently includes Postgresql,
283
Oracle, and Firebird. The :class:`~sqlalchemy.schema.Sequence` object is
286
The :class:`~sqlalchemy.schema.Sequence` may be placed on any column as a
287
"default" generator to be used during INSERT operations, and can also be
288
configured to fire off during UPDATE operations if desired. It is most
289
commonly used in conjunction with a single integer primary key column::
291
table = Table("cartitems", meta,
292
Column("cart_id", Integer, Sequence('cart_id_seq'), primary_key=True),
293
Column("description", String(40)),
294
Column("createdate", DateTime())
297
Where above, the table "cartitems" is associated with a sequence named
298
"cart_id_seq". When INSERT statements take place for "cartitems", and no value
299
is passed for the "cart_id" column, the "cart_id_seq" sequence will be used to
302
When the :class:`~sqlalchemy.schema.Sequence` is associated with a table,
303
CREATE and DROP statements issued for that table will also issue CREATE/DROP
304
for the sequence object as well, thus "bundling" the sequence object with its
307
The :class:`~sqlalchemy.schema.Sequence` object also implements special
308
functionality to accommodate Postgresql's SERIAL datatype. The SERIAL type in
309
PG automatically generates a sequence that is used implicitly during inserts.
310
This means that if a :class:`~sqlalchemy.schema.Table` object defines a
311
:class:`~sqlalchemy.schema.Sequence` on its primary key column so that it
312
works with Oracle and Firebird, the :class:`~sqlalchemy.schema.Sequence` would
313
get in the way of the "implicit" sequence that PG would normally use. For this
314
use case, add the flag ``optional=True`` to the
315
:class:`~sqlalchemy.schema.Sequence` object - this indicates that the
316
:class:`~sqlalchemy.schema.Sequence` should only be used if the database
317
provides no other option for generating primary key identifiers.
319
The :class:`~sqlalchemy.schema.Sequence` object also has the ability to be
320
executed standalone like a SQL expression, which has the effect of calling its
321
"next value" function::
323
seq = Sequence('some_sequence')
324
nextid = connection.execute(seq)
329
.. autoclass:: ColumnDefault
332
.. autoclass:: DefaultClause
335
.. autoclass:: DefaultGenerator
338
.. autoclass:: FetchedValue
341
.. autoclass:: PassiveDefault
344
.. autoclass:: Sequence
added
removed
doc/_sources/core/defaults.txt
