1.. module:: sqlalchemy.schema 2 3.. _metadata_defaults_toplevel: 4 5.. _metadata_defaults: 6 7Column Insert/Update Defaults 8============================== 9 10SQLAlchemy provides a very rich featureset regarding column level events which 11take place during INSERT and UPDATE statements. Options include: 12 13* Scalar values used as defaults during INSERT and UPDATE operations 14* Python functions which execute upon INSERT and UPDATE operations 15* SQL expressions which are embedded in INSERT statements (or in some cases execute beforehand) 16* SQL expressions which are embedded in UPDATE statements 17* Server side default values used during INSERT 18* Markers for server-side triggers used during UPDATE 19 20The general rule for all insert/update defaults is that they only take effect 21if no value for a particular column is passed as an ``execute()`` parameter; 22otherwise, the given value is used. 23 24Scalar Defaults 25--------------- 26 27The simplest kind of default is a scalar value used as the default value of a column:: 28 29 Table("mytable", meta, 30 Column("somecolumn", Integer, default=12) 31 ) 32 33Above, the value "12" will be bound as the column value during an INSERT if no 34other value is supplied. 35 36A scalar value may also be associated with an UPDATE statement, though this is 37not very common (as UPDATE statements are usually looking for dynamic 38defaults):: 39 40 Table("mytable", meta, 41 Column("somecolumn", Integer, onupdate=25) 42 ) 43 44 45Python-Executed Functions 46------------------------- 47 48The :paramref:`.Column.default` and :paramref:`.Column.onupdate` keyword arguments also accept Python 49functions. These functions are invoked at the time of insert or update if no 50other value for that column is supplied, and the value returned is used for 51the column's value. Below illustrates a crude "sequence" that assigns an 52incrementing counter to a primary key column:: 53 54 # a function which counts upwards 55 i = 0 56 def mydefault(): 57 global i 58 i += 1 59 return i 60 61 t = Table("mytable", meta, 62 Column('id', Integer, primary_key=True, default=mydefault), 63 ) 64 65It should be noted that for real "incrementing sequence" behavior, the 66built-in capabilities of the database should normally be used, which may 67include sequence objects or other autoincrementing capabilities. For primary 68key columns, SQLAlchemy will in most cases use these capabilities 69automatically. See the API documentation for 70:class:`~sqlalchemy.schema.Column` including the :paramref:`.Column.autoincrement` flag, as 71well as the section on :class:`~sqlalchemy.schema.Sequence` later in this 72chapter for background on standard primary key generation techniques. 73 74To illustrate onupdate, we assign the Python ``datetime`` function ``now`` to 75the :paramref:`.Column.onupdate` attribute:: 76 77 import datetime 78 79 t = Table("mytable", meta, 80 Column('id', Integer, primary_key=True), 81 82 # define 'last_updated' to be populated with datetime.now() 83 Column('last_updated', DateTime, onupdate=datetime.datetime.now), 84 ) 85 86When an update statement executes and no value is passed for ``last_updated``, 87the ``datetime.datetime.now()`` Python function is executed and its return 88value used as the value for ``last_updated``. Notice that we provide ``now`` 89as the function itself without calling it (i.e. there are no parenthesis 90following) - SQLAlchemy will execute the function at the time the statement 91executes. 92 93Context-Sensitive Default Functions 94~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 95 96The Python functions used by :paramref:`.Column.default` and :paramref:`.Column.onupdate` may also make use of 97the current statement's context in order to determine a value. The `context` 98of a statement is an internal SQLAlchemy object which contains all information 99about the statement being executed, including its source expression, the 100parameters associated with it and the cursor. The typical use case for this 101context with regards to default generation is to have access to the other 102values being inserted or updated on the row. To access the context, provide a 103function that accepts a single ``context`` argument:: 104 105 def mydefault(context): 106 return context.current_parameters['counter'] + 12 107 108 t = Table('mytable', meta, 109 Column('counter', Integer), 110 Column('counter_plus_twelve', Integer, default=mydefault, onupdate=mydefault) 111 ) 112 113Above we illustrate a default function which will execute for all INSERT and 114UPDATE statements where a value for ``counter_plus_twelve`` was otherwise not 115provided, and the value will be that of whatever value is present in the 116execution for the ``counter`` column, plus the number 12. 117 118While the context object passed to the default function has many attributes, 119the ``current_parameters`` member is a special member provided only during the 120execution of a default function for the purposes of deriving defaults from its 121existing values. For a single statement that is executing many sets of bind 122parameters, the user-defined function is called for each set of parameters, 123and ``current_parameters`` will be provided with each individual parameter set 124for each execution. 125 126SQL Expressions 127--------------- 128 129The "default" and "onupdate" keywords may also be passed SQL expressions, 130including select statements or direct function calls:: 131 132 t = Table("mytable", meta, 133 Column('id', Integer, primary_key=True), 134 135 # define 'create_date' to default to now() 136 Column('create_date', DateTime, default=func.now()), 137 138 # define 'key' to pull its default from the 'keyvalues' table 139 Column('key', String(20), default=keyvalues.select(keyvalues.c.type='type1', limit=1)), 140 141 # define 'last_modified' to use the current_timestamp SQL function on update 142 Column('last_modified', DateTime, onupdate=func.utc_timestamp()) 143 ) 144 145Above, the ``create_date`` column will be populated with the result of the 146``now()`` SQL function (which, depending on backend, compiles into ``NOW()`` 147or ``CURRENT_TIMESTAMP`` in most cases) during an INSERT statement, and the 148``key`` column with the result of a SELECT subquery from another table. The 149``last_modified`` column will be populated with the value of 150``UTC_TIMESTAMP()``, a function specific to MySQL, when an UPDATE statement is 151emitted for this table. 152 153Note that when using ``func`` functions, unlike when using Python `datetime` 154functions we *do* call the function, i.e. with parenthesis "()" - this is 155because what we want in this case is the return value of the function, which 156is the SQL expression construct that will be rendered into the INSERT or 157UPDATE statement. 158 159The above SQL functions are usually executed "inline" with the INSERT or 160UPDATE statement being executed, meaning, a single statement is executed which 161embeds the given expressions or subqueries within the VALUES or SET clause of 162the statement. Although in some cases, the function is "pre-executed" in a 163SELECT statement of its own beforehand. This happens when all of the following 164is true: 165 166* the column is a primary key column 167* the database dialect does not support a usable ``cursor.lastrowid`` accessor 168 (or equivalent); this currently includes PostgreSQL, Oracle, and Firebird, as 169 well as some MySQL dialects. 170* the dialect does not support the "RETURNING" clause or similar, or the 171 ``implicit_returning`` flag is set to ``False`` for the dialect. Dialects 172 which support RETURNING currently include Postgresql, Oracle, Firebird, and 173 MS-SQL. 174* the statement is a single execution, i.e. only supplies one set of 175 parameters and doesn't use "executemany" behavior 176* the ``inline=True`` flag is not set on the 177 :class:`~sqlalchemy.sql.expression.Insert()` or 178 :class:`~sqlalchemy.sql.expression.Update()` construct, and the statement has 179 not defined an explicit `returning()` clause. 180 181Whether or not the default generation clause "pre-executes" is not something 182that normally needs to be considered, unless it is being addressed for 183performance reasons. 184 185When the statement is executed with a single set of parameters (that is, it is 186not an "executemany" style execution), the returned 187:class:`~sqlalchemy.engine.ResultProxy` will contain a collection 188accessible via :meth:`.ResultProxy.postfetch_cols` which contains a list of all 189:class:`~sqlalchemy.schema.Column` objects which had an inline-executed 190default. Similarly, all parameters which were bound to the statement, 191including all Python and SQL expressions which were pre-executed, are present 192in the :meth:`.ResultProxy.last_inserted_params` or :meth:`.ResultProxy.last_updated_params` collections on 193:class:`~sqlalchemy.engine.ResultProxy`. The :attr:`.ResultProxy.inserted_primary_key` 194collection contains a list of primary key values for the row inserted (a list 195so that single-column and composite-column primary keys are represented in the 196same format). 197 198.. _server_defaults: 199 200Server Side Defaults 201-------------------- 202 203A variant on the SQL expression default is the :paramref:`.Column.server_default`, which gets 204placed in the CREATE TABLE statement during a :meth:`.Table.create` operation: 205 206.. sourcecode:: python+sql 207 208 t = Table('test', meta, 209 Column('abc', String(20), server_default='abc'), 210 Column('created_at', DateTime, server_default=text("sysdate")) 211 ) 212 213A create call for the above table will produce:: 214 215 CREATE TABLE test ( 216 abc varchar(20) default 'abc', 217 created_at datetime default sysdate 218 ) 219 220The behavior of :paramref:`.Column.server_default` is similar to that of a regular SQL 221default; if it's placed on a primary key column for a database which doesn't 222have a way to "postfetch" the ID, and the statement is not "inlined", the SQL 223expression is pre-executed; otherwise, SQLAlchemy lets the default fire off on 224the database side normally. 225 226 227.. _triggered_columns: 228 229Triggered Columns 230------------------ 231 232Columns with values set by a database trigger or other external process may be 233called out using :class:`.FetchedValue` as a marker:: 234 235 t = Table('test', meta, 236 Column('abc', String(20), server_default=FetchedValue()), 237 Column('def', String(20), server_onupdate=FetchedValue()) 238 ) 239 240.. versionchanged:: 0.8.0b2,0.7.10 241 The ``for_update`` argument on :class:`.FetchedValue` is set automatically 242 when specified as the ``server_onupdate`` argument. If using an older version, 243 specify the onupdate above as ``server_onupdate=FetchedValue(for_update=True)``. 244 245These markers do not emit a "default" clause when the table is created, 246however they do set the same internal flags as a static ``server_default`` 247clause, providing hints to higher-level tools that a "post-fetch" of these 248rows should be performed after an insert or update. 249 250.. note:: 251 252 It's generally not appropriate to use :class:`.FetchedValue` in 253 conjunction with a primary key column, particularly when using the 254 ORM or any other scenario where the :attr:`.ResultProxy.inserted_primary_key` 255 attribute is required. This is becaue the "post-fetch" operation requires 256 that the primary key value already be available, so that the 257 row can be selected on its primary key. 258 259 For a server-generated primary key value, all databases provide special 260 accessors or other techniques in order to acquire the "last inserted 261 primary key" column of a table. These mechanisms aren't affected by the presence 262 of :class:`.FetchedValue`. For special situations where triggers are 263 used to generate primary key values, and the database in use does not 264 support the ``RETURNING`` clause, it may be necessary to forego the usage 265 of the trigger and instead apply the SQL expression or function as a 266 "pre execute" expression:: 267 268 t = Table('test', meta, 269 Column('abc', MyType, default=func.generate_new_value(), primary_key=True) 270 ) 271 272 Where above, when :meth:`.Table.insert` is used, 273 the ``func.generate_new_value()`` expression will be pre-executed 274 in the context of a scalar ``SELECT`` statement, and the new value will 275 be applied to the subsequent ``INSERT``, while at the same time being 276 made available to the :attr:`.ResultProxy.inserted_primary_key` 277 attribute. 278 279 280Defining Sequences 281------------------- 282 283SQLAlchemy represents database sequences using the 284:class:`~sqlalchemy.schema.Sequence` object, which is considered to be a 285special case of "column default". It only has an effect on databases which 286have explicit support for sequences, which currently includes Postgresql, 287Oracle, and Firebird. The :class:`~sqlalchemy.schema.Sequence` object is 288otherwise ignored. 289 290The :class:`~sqlalchemy.schema.Sequence` may be placed on any column as a 291"default" generator to be used during INSERT operations, and can also be 292configured to fire off during UPDATE operations if desired. It is most 293commonly used in conjunction with a single integer primary key column:: 294 295 table = Table("cartitems", meta, 296 Column("cart_id", Integer, Sequence('cart_id_seq'), primary_key=True), 297 Column("description", String(40)), 298 Column("createdate", DateTime()) 299 ) 300 301Where above, the table "cartitems" is associated with a sequence named 302"cart_id_seq". When INSERT statements take place for "cartitems", and no value 303is passed for the "cart_id" column, the "cart_id_seq" sequence will be used to 304generate a value. 305 306When the :class:`~sqlalchemy.schema.Sequence` is associated with a table, 307CREATE and DROP statements issued for that table will also issue CREATE/DROP 308for the sequence object as well, thus "bundling" the sequence object with its 309parent table. 310 311The :class:`~sqlalchemy.schema.Sequence` object also implements special 312functionality to accommodate Postgresql's SERIAL datatype. The SERIAL type in 313PG automatically generates a sequence that is used implicitly during inserts. 314This means that if a :class:`~sqlalchemy.schema.Table` object defines a 315:class:`~sqlalchemy.schema.Sequence` on its primary key column so that it 316works with Oracle and Firebird, the :class:`~sqlalchemy.schema.Sequence` would 317get in the way of the "implicit" sequence that PG would normally use. For this 318use case, add the flag ``optional=True`` to the 319:class:`~sqlalchemy.schema.Sequence` object - this indicates that the 320:class:`~sqlalchemy.schema.Sequence` should only be used if the database 321provides no other option for generating primary key identifiers. 322 323The :class:`~sqlalchemy.schema.Sequence` object also has the ability to be 324executed standalone like a SQL expression, which has the effect of calling its 325"next value" function:: 326 327 seq = Sequence('some_sequence') 328 nextid = connection.execute(seq) 329 330Associating a Sequence as the Server Side Default 331~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 332 333When we associate a :class:`.Sequence` with a :class:`.Column` as above, 334this association is an **in-Python only** association. The CREATE TABLE 335that would be generated for our :class:`.Table` would not refer to this 336sequence. If we want the sequence to be used as a server-side default, 337meaning it takes place even if we emit INSERT commands to the table from 338the SQL commandline, we can use the :paramref:`.Column.server_default` 339parameter in conjunction with the value-generation function of the 340sequence, available from the :meth:`.Sequence.next_value` method:: 341 342 cart_id_seq = Sequence('cart_id_seq') 343 table = Table("cartitems", meta, 344 Column( 345 "cart_id", Integer, cart_id_seq, 346 server_default=cart_id_seq.next_value(), primary_key=True), 347 Column("description", String(40)), 348 Column("createdate", DateTime()) 349 ) 350 351The above metadata will generate a CREATE TABLE statement on Postgresql as:: 352 353 CREATE TABLE cartitems ( 354 cart_id INTEGER DEFAULT nextval('cart_id_seq') NOT NULL, 355 description VARCHAR(40), 356 createdate TIMESTAMP WITHOUT TIME ZONE, 357 PRIMARY KEY (cart_id) 358 ) 359 360We place the :class:`.Sequence` also as a Python-side default above, that 361is, it is mentioned twice in the :class:`.Column` definition. Depending 362on the backend in use, this may not be strictly necessary, for example 363on the Postgresql backend the Core will use ``RETURNING`` to access the 364newly generated primary key value in any case. However, for the best 365compatibility, :class:`.Sequence` was originally intended to be a Python-side 366directive first and foremost so it's probably a good idea to specify it 367in this way as well. 368 369 370Default Objects API 371------------------- 372 373.. autoclass:: ColumnDefault 374 375 376.. autoclass:: DefaultClause 377 378 379.. autoclass:: DefaultGenerator 380 381 382.. autoclass:: FetchedValue 383 384 385.. autoclass:: PassiveDefault 386 387 388.. autoclass:: Sequence 389 :members: 390