Scalar Defaults¶

The simplest kind of default is a scalar value used as the default value of a column:

Table("mytable", meta,
    Column("somecolumn", Integer, default=12)
)

Above, the value “12” will be bound as the column value during an INSERT if no other value is supplied.

A scalar value may also be associated with an UPDATE statement, though this is not very common (as UPDATE statements are usually looking for dynamic defaults):

Table("mytable", meta,
    Column("somecolumn", Integer, onupdate=25)
)

Python-Executed Functions¶

The Column.default and Column.onupdate keyword arguments also accept Python functions. These functions are invoked at the time of insert or update if no other value for that column is supplied, and the value returned is used for the column’s value. Below illustrates a crude “sequence” that assigns an incrementing counter to a primary key column:

# a function which counts upwards
i = 0
def mydefault():
    global i
    i += 1
    return i

t = Table("mytable", meta,
    Column('id', Integer, primary_key=True, default=mydefault),
)

It should be noted that for real “incrementing sequence” behavior, the built-in capabilities of the database should normally be used, which may include sequence objects or other autoincrementing capabilities. For primary key columns, SQLAlchemy will in most cases use these capabilities automatically. See the API documentation for Column including the Column.autoincrement flag, as well as the section on Sequence later in this chapter for background on standard primary key generation techniques.

To illustrate onupdate, we assign the Python datetime function now to the Column.onupdate attribute:

import datetime

t = Table("mytable", meta,
    Column('id', Integer, primary_key=True),

    # define 'last_updated' to be populated with datetime.now()
    Column('last_updated', DateTime, onupdate=datetime.datetime.now),
)

When an update statement executes and no value is passed for last_updated, the datetime.datetime.now() Python function is executed and its return value used as the value for last_updated. Notice that we provide now as the function itself without calling it (i.e. there are no parenthesis following) - SQLAlchemy will execute the function at the time the statement executes.

def mydefault(context):
    return context.current_parameters['counter'] + 12

t = Table('mytable', meta,
    Column('counter', Integer),
    Column('counter_plus_twelve', Integer, default=mydefault, onupdate=mydefault)
)

SQL Expressions¶

The “default” and “onupdate” keywords may also be passed SQL expressions, including select statements or direct function calls:

t = Table("mytable", meta,
    Column('id', Integer, primary_key=True),

    # define 'create_date' to default to now()
    Column('create_date', DateTime, default=func.now()),

    # define 'key' to pull its default from the 'keyvalues' table
    Column('key', String(20), default=keyvalues.select(keyvalues.c.type='type1', limit=1)),

    # define 'last_modified' to use the current_timestamp SQL function on update
    Column('last_modified', DateTime, onupdate=func.utc_timestamp())
    )

Above, the create_date column will be populated with the result of the now() SQL function (which, depending on backend, compiles into NOW() or CURRENT_TIMESTAMP in most cases) during an INSERT statement, and the key column with the result of a SELECT subquery from another table. The last_modified column will be populated with the value of UTC_TIMESTAMP(), a function specific to MySQL, when an UPDATE statement is emitted for this table.

Note that when using func functions, unlike when using Python datetime functions we do call the function, i.e. with parenthesis “()” - this is because what we want in this case is the return value of the function, which is the SQL expression construct that will be rendered into the INSERT or UPDATE statement.

The above SQL functions are usually executed “inline” with the INSERT or UPDATE statement being executed, meaning, a single statement is executed which embeds the given expressions or subqueries within the VALUES or SET clause of the statement. Although in some cases, the function is “pre-executed” in a SELECT statement of its own beforehand. This happens when all of the following is true:

• the column is a primary key column
• the database dialect does not support a usable cursor.lastrowid accessor (or equivalent); this currently includes PostgreSQL, Oracle, and Firebird, as well as some MySQL dialects.
• the dialect does not support the “RETURNING” clause or similar, or the implicit_returning flag is set to False for the dialect. Dialects which support RETURNING currently include Postgresql, Oracle, Firebird, and MS-SQL.
• the statement is a single execution, i.e. only supplies one set of parameters and doesn’t use “executemany” behavior
• the inline=True flag is not set on the Insert() or Update() construct, and the statement has not defined an explicit returning() clause.

Whether or not the default generation clause “pre-executes” is not something that normally needs to be considered, unless it is being addressed for performance reasons.

When the statement is executed with a single set of parameters (that is, it is not an “executemany” style execution), the returned ResultProxy will contain a collection accessible via ResultProxy.postfetch_cols() which contains a list of all Column objects which had an inline-executed default. Similarly, all parameters which were bound to the statement, including all Python and SQL expressions which were pre-executed, are present in the ResultProxy.last_inserted_params() or ResultProxy.last_updated_params() collections on ResultProxy. The ResultProxy.inserted_primary_key collection contains a list of primary key values for the row inserted (a list so that single-column and composite-column primary keys are represented in the same format).

Server Side Defaults¶

A variant on the SQL expression default is the Column.server_default, which gets placed in the CREATE TABLE statement during a Table.create() operation:

t = Table('test', meta,
    Column('abc', String(20), server_default='abc'),
    Column('created_at', DateTime, server_default=text("sysdate"))
)

A create call for the above table will produce:

CREATE TABLE test (
    abc varchar(20) default 'abc',
    created_at datetime default sysdate
)

The behavior of Column.server_default is similar to that of a regular SQL default; if it’s placed on a primary key column for a database which doesn’t have a way to “postfetch” the ID, and the statement is not “inlined”, the SQL expression is pre-executed; otherwise, SQLAlchemy lets the default fire off on the database side normally.

Triggered Columns¶

Columns with values set by a database trigger or other external process may be called out using FetchedValue as a marker:

t = Table('test', meta,
    Column('abc', String(20), server_default=FetchedValue()),
    Column('def', String(20), server_onupdate=FetchedValue())
)

These markers do not emit a “default” clause when the table is created, however they do set the same internal flags as a static server_default clause, providing hints to higher-level tools that a “post-fetch” of these rows should be performed after an insert or update.

t = Table('test', meta,
        Column('abc', MyType, default=func.generate_new_value(), primary_key=True)
)

Defining Sequences¶

SQLAlchemy represents database sequences using the Sequence object, which is considered to be a special case of “column default”. It only has an effect on databases which have explicit support for sequences, which currently includes Postgresql, Oracle, and Firebird. The Sequence object is otherwise ignored.

The Sequence may be placed on any column as a “default” generator to be used during INSERT operations, and can also be configured to fire off during UPDATE operations if desired. It is most commonly used in conjunction with a single integer primary key column:

table = Table("cartitems", meta,
    Column("cart_id", Integer, Sequence('cart_id_seq'), primary_key=True),
    Column("description", String(40)),
    Column("createdate", DateTime())
)

Where above, the table “cartitems” is associated with a sequence named “cart_id_seq”. When INSERT statements take place for “cartitems”, and no value is passed for the “cart_id” column, the “cart_id_seq” sequence will be used to generate a value.

When the Sequence is associated with a table, CREATE and DROP statements issued for that table will also issue CREATE/DROP for the sequence object as well, thus “bundling” the sequence object with its parent table.

The Sequence object also implements special functionality to accommodate Postgresql’s SERIAL datatype. The SERIAL type in PG automatically generates a sequence that is used implicitly during inserts. This means that if a Table object defines a Sequence on its primary key column so that it works with Oracle and Firebird, the Sequence would get in the way of the “implicit” sequence that PG would normally use. For this use case, add the flag optional=True to the Sequence object - this indicates that the Sequence should only be used if the database provides no other option for generating primary key identifiers.

The Sequence object also has the ability to be executed standalone like a SQL expression, which has the effect of calling its “next value” function:

seq = Sequence('some_sequence')
nextid = connection.execute(seq)

cart_id_seq = Sequence('cart_id_seq')
table = Table("cartitems", meta,
    Column(
        "cart_id", Integer, cart_id_seq,
        server_default=cart_id_seq.next_value(), primary_key=True),
    Column("description", String(40)),
    Column("createdate", DateTime())
)

CREATE TABLE cartitems (
    cart_id INTEGER DEFAULT nextval('cart_id_seq') NOT NULL,
    description VARCHAR(40),
    createdate TIMESTAMP WITHOUT TIME ZONE,
    PRIMARY KEY (cart_id)
)

Default Objects API¶

class sqlalchemy.schema.ColumnDefault(arg, **kwargs)¶

Bases: sqlalchemy.schema.DefaultGenerator

A plain default value on a column.

This could correspond to a constant, a callable function, or a SQL clause.

ColumnDefault is generated automatically whenever the default, onupdate arguments of Column are used. A ColumnDefault can be passed positionally as well.

For example, the following:

Column('foo', Integer, default=50)

Is equivalent to:

Column('foo', Integer, ColumnDefault(50))

class sqlalchemy.schema.DefaultClause(arg, for_update=False, _reflected=False)¶

Bases: sqlalchemy.schema.FetchedValue

A DDL-specified DEFAULT column value.

DefaultClause is a FetchedValue that also generates a “DEFAULT” clause when “CREATE TABLE” is emitted.

DefaultClause is generated automatically whenever the server_default, server_onupdate arguments of Column are used. A DefaultClause can be passed positionally as well.

For example, the following:

Column('foo', Integer, server_default="50")

Is equivalent to:

Column('foo', Integer, DefaultClause("50"))

class sqlalchemy.schema.DefaultGenerator(for_update=False)¶

Bases: sqlalchemy.schema._NotAColumnExpr, sqlalchemy.schema.SchemaItem

Base class for column default values.

class sqlalchemy.schema.FetchedValue(for_update=False)¶

Bases: sqlalchemy.schema._NotAColumnExpr, sqlalchemy.sql.expression.SchemaEventTarget

A marker for a transparent database-side default.

Use FetchedValue when the database is configured to provide some automatic default for a column.

E.g.:

Column('foo', Integer, FetchedValue())

Would indicate that some trigger or default generator will create a new value for the foo column during an INSERT.

더 보기

Triggered Columns

class sqlalchemy.schema.PassiveDefault(*arg, **kw)¶

Bases: sqlalchemy.schema.DefaultClause

A DDL-specified DEFAULT column value.

버전 0.6 폐지: PassiveDefault is deprecated. Use DefaultClause.

class sqlalchemy.schema.Sequence(name, start=None, increment=None, minvalue=None, maxvalue=None, nominvalue=None, nomaxvalue=None, cycle=None, schema=None, optional=False, quote=None, metadata=None, quote_schema=None, for_update=False)¶

Bases: sqlalchemy.schema.DefaultGenerator

Represents a named database sequence.

The Sequence object represents the name and configurational parameters of a database sequence. It also represents a construct that can be “executed” by a SQLAlchemy Engine or Connection, rendering the appropriate “next value” function for the target database and returning a result.

The Sequence is typically associated with a primary key column:

some_table = Table(
    'some_table', metadata,
    Column('id', Integer, Sequence('some_table_seq'),
    primary_key=True)
)

When CREATE TABLE is emitted for the above Table, if the target platform supports sequences, a CREATE SEQUENCE statement will be emitted as well. For platforms that don’t support sequences, the Sequence construct is ignored.

더 보기

CreateSequence

DropSequence

__init__(name, start=None, increment=None, minvalue=None, maxvalue=None, nominvalue=None, nomaxvalue=None, cycle=None, schema=None, optional=False, quote=None, metadata=None, quote_schema=None, for_update=False)¶

Construct a Sequence object.

매개 변수:

name¶ – The name of the sequence. start¶ – the starting index of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “START WITH” clause. If None, the clause is omitted, which on most platforms indicates a starting value of 1. increment¶ – the increment value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “INCREMENT BY” clause. If None, the clause is omitted, which on most platforms indicates an increment of 1. minvalue¶ – the minimum value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “MINVALUE” clause. If None, the clause is omitted, which on most platforms indicates a minvalue of 1 and -2^63-1 for ascending and descending sequences, respectively. 버전 1.0.7에 추가. maxvalue¶ – the maximum value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “MAXVALUE” clause. If None, the clause is omitted, which on most platforms indicates a maxvalue of 2^63-1 and -1 for ascending and descending sequences, respectively. 버전 1.0.7에 추가. nominvalue¶ – no minimum value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “NO MINVALUE” clause. If None, the clause is omitted, which on most platforms indicates a minvalue of 1 and -2^63-1 for ascending and descending sequences, respectively. 버전 1.0.7에 추가. nomaxvalue¶ – no maximum value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “NO MAXVALUE” clause. If None, the clause is omitted, which on most platforms indicates a maxvalue of 2^63-1 and -1 for ascending and descending sequences, respectively. 버전 1.0.7에 추가. cycle¶ – allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. This value is used when the CREATE SEQUENCE command is emitted to the database as the “CYCLE” clause. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively. If cycle=False (the default) any calls to nextval after the sequence has reached its maximum value will return an error. 버전 1.0.7에 추가. schema¶ – Optional schema name for the sequence, if located in a schema other than the default. optional¶ – boolean value, when True, indicates that this Sequence object only needs to be explicitly generated on backends that don’t provide another way to generate primary key identifiers. Currently, it essentially means, “don’t create this sequence on the Postgresql backend, where the SERIAL keyword creates a sequence for us automatically”. quote¶ – boolean value, when True or False, explicitly forces quoting of the schema name on or off. When left at its default of None, normal quoting rules based on casing and reserved words take place. quote_schema¶ – set the quoting preferences for the schema name. metadata¶ – optional MetaData object which will be associated with this Sequence. A Sequence that is associated with a MetaData gains access to the bind of that MetaData, meaning the Sequence.create() and Sequence.drop() methods will make usage of that engine automatically. 버전 0.7으로 변경: Additionally, the appropriate CREATE SEQUENCE/ DROP SEQUENCE DDL commands will be emitted corresponding to this Sequence when MetaData.create_all() and MetaData.drop_all() are invoked. Note that when a Sequence is applied to a Column, the Sequence is automatically associated with the MetaData object of that column’s parent Table, when that association is made. The Sequence will then be subject to automatic CREATE SEQUENCE/DROP SEQUENCE corresponding to when the Table object itself is created or dropped, rather than that of the MetaData object overall. for_update¶ – Indicates this Sequence, when associated with a Column, should be invoked for UPDATE statements on that column’s table, rather than for INSERT statements, when no value is otherwise present for that column in the statement.

create(bind=None, checkfirst=True)¶

Creates this sequence in the database.

drop(bind=None, checkfirst=True)¶

Drops this sequence from the database.

next_value()¶

Return a next_value function element which will render the appropriate increment function for this Sequence within any SQL expression.