SQLAlchemy 1.3 Documentation
SQLAlchemy Core
- SQL Expression Language Tutorial
- SQL Statements and Expressions API
- Schema Definition Language
- Describing Databases with MetaData
- Reflecting Database Objects
- Column INSERT/UPDATE Defaults¶
- Defining Constraints and Indexes
- Customizing DDL
- Column and Data Types
- Engine and Connection Use
- Core API Basics
Project Versions
- Previous: Reflecting Database Objects
- Next: Defining Constraints and Indexes
- Up: Home
- On this page:
Column INSERT/UPDATE Defaults¶
Column INSERT and UPDATE defaults refer to functions that create a default value for a particular column in a row as an INSERT or UPDATE statement is proceeding against that row, in the case where no value was provided to the INSERT or UPDATE statement for that column. That is, if a table has a column called “timestamp”, and an INSERT statement proceeds which does not include a value for this column, an INSERT default would create a new value, such as the current time, that is used as the value to be INSERTed into the “timestamp” column. If the statement does include a value for this column, then the default does not take place.
Column defaults can be server-side functions or constant values which are defined in the database along with the schema in DDL, or as SQL expressions which are rendered directly within an INSERT or UPDATE statement emitted by SQLAlchemy; they may also be client-side Python functions or constant values which are invoked by SQLAlchemy before data is passed to the database.
Note
A column default handler should not be confused with a construct that intercepts and modifies incoming values for INSERT and UPDATE statements which are provided to the statement as it is invoked. This is known as data marshalling, where a column value is modified in some way by the application before being sent to the database. SQLAlchemy provides a few means of achieving this which include using custom datatypes, SQL execution events and in the ORM custom validators as well as attribute events. Column defaults are only invoked when there is no value present for a column in a SQL DML statement.
SQLAlchemy provides an array of features regarding default generation functions which take place for non-present values during INSERT and UPDATE statements. Options include:
Scalar values used as defaults during INSERT and UPDATE operations
Python functions which execute upon INSERT and UPDATE operations
SQL expressions which are embedded in INSERT statements (or in some cases execute beforehand)
SQL expressions which are embedded in UPDATE statements
Server side default values used during INSERT
Markers for server-side triggers used during UPDATE
The general rule for all insert/update defaults is that they only take effect
if no value for a particular column is passed as an execute()
parameter;
otherwise, the given value is used.
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.
Context-Sensitive Default Functions¶
The Python functions used by Column.default
and
Column.onupdate
may also make use of the current statement’s
context in order to determine a value. The context of a statement is an
internal SQLAlchemy object which contains all information about the statement
being executed, including its source expression, the parameters associated with
it and the cursor. The typical use case for this context with regards to
default generation is to have access to the other values being inserted or
updated on the row. To access the context, provide a function that accepts a
single context
argument:
def mydefault(context):
return context.get_current_parameters()['counter'] + 12
t = Table('mytable', meta,
Column('counter', Integer),
Column('counter_plus_twelve', Integer, default=mydefault, onupdate=mydefault)
)
The above default generation function is applied so that it will execute for
all INSERT and UPDATE statements where a value for counter_plus_twelve
was
otherwise not provided, and the value will be that of whatever value is present
in the execution for the counter
column, plus the number 12.
For a single statement that is being executed using “executemany” style, e.g.
with multiple parameter sets passed to Connection.execute()
, the
user-defined function is called once for each set of parameters. For the use case of
a multi-valued Insert
construct (e.g. with more than one VALUES
clause set up via the Insert.values()
method), the user-defined function
is also called once for each set of parameters.
When the function is invoked, the special method
DefaultExecutionContext.get_current_parameters()
is available from
the context object (an subclass of DefaultExecutionContext
). This
method returns a dictionary of column-key to values that represents the
full set of values for the INSERT or UPDATE statement. In the case of a
multi-valued INSERT construct, the subset of parameters that corresponds to
the individual VALUES clause is isolated from the full parameter dictionary
and returned alone.
New in version 1.2: Added DefaultExecutionContext.get_current_parameters()
method,
which improves upon the still-present
DefaultExecutionContext.current_parameters
attribute
by offering the service of organizing multiple VALUES clauses
into individual parameter dictionaries.
Client-Invoked SQL Expressions¶
The Column.default
and Column.onupdate
keywords may
also be passed SQL expressions, which are in most cases rendered inline within the
INSERT or UPDATE statement:
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=select([keyvalues.c.key]).where(keyvalues.c.type='type1')),
# 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
the SQL UTC_TIMESTAMP()
MySQL function when an UPDATE statement is
emitted for this table.
Note
When using SQL functions with the func
construct, we “call” the
named function, e.g. with parenthesis as in func.now()
. This differs
from when we specify a Python callable as a default such as
datetime.datetime
, where we pass the function itself, but we don’t
invoke it ourselves. In the case of a SQL function, invoking
func.now()
returns the SQL expression object that will render the
“NOW” function into the SQL being emitted.
Default and update SQL expressions specified by Column.default
and
Column.onupdate
are invoked explicitly by SQLAlchemy when an
INSERT or UPDATE statement occurs, typically rendered inline within the DML
statement except in certain cases listed below. This is different than a
“server side” default, which is part of the table’s DDL definition, e.g. as
part of the “CREATE TABLE” statement, which are likely more common. For
server side defaults, see the next section Server-invoked DDL-Explicit Default Expressions.
When a SQL expression indicated by Column.default
is used with
primary key columns, there are some cases where SQLAlchemy must “pre-execute”
the default generation SQL function, meaning it is invoked in a separate SELECT
statement, and the resulting value is passed as a parameter to the INSERT.
This only occurs for primary key columns for an INSERT statement that is being
asked to return this primary key value, where RETURNING or cursor.lastrowid
may not be used. An Insert
construct that specifies the
insert.inline
flag will always render default expressions
inline.
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-invoked DDL-Explicit Default Expressions¶
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=func.sysdate()),
Column('index_value', Integer, server_default=text("0"))
)
A create call for the above table will produce:
CREATE TABLE test (
abc varchar(20) default 'abc',
created_at datetime default sysdate,
index_value integer default 0
)
The above example illustrates the two typical use cases for Column.server_default
,
that of the SQL function (SYSDATE in the above example) as well as a server-side constant
value (the integer “0” in the above example). It is advisable to use the
text()
construct for any literal SQL values as opposed to passing the
raw value, as SQLAlchemy does not typically perform any quoting or escaping on
these values.
Like client-generated expressions, Column.server_default
can accommodate
SQL expressions in general, however it is expected that these will usually be simple
functions and expressions, and not the more complex cases like an embedded SELECT.
Marking Implicitly Generated Values, timestamps, and Triggered Columns¶
Columns which generate a new value on INSERT or UPDATE based on other
server-side database mechanisms, such as database-specific auto-generating
behaviors such as seen with TIMESTAMP columns on some platforms, as well as
custom triggers that invoke upon INSERT or UPDATE to generate a new value,
may be called out using FetchedValue
as a marker:
from sqlalchemy.schema import FetchedValue
t = Table('test', meta,
Column('id', Integer, primary_key=True),
Column('abc', TIMESTAMP, server_default=FetchedValue()),
Column('def', String(20), server_onupdate=FetchedValue())
)
The FetchedValue
indicator does not affect the rendered DDL for the
CREATE TABLE. Instead, it marks the column as one that will have a new value
populated by the database during the process of an INSERT or UPDATE statement,
and for supporting databases may be used to indicate that the column should be
part of a RETURNING or OUTPUT clause for the statement. Tools such as the
SQLAlchemy ORM then make use of this marker in order to know how to get at the
value of the column after such an operation. In particular, the
ValuesBase.return_defaults()
method can be used with an Insert
or Update
construct to indicate that these values should be
returned.
For details on using FetchedValue
with the ORM, see
Fetching Server-Generated Defaults.
Warning
The Column.server_onupdate
directive
does not currently produce MySQL’s
“ON UPDATE CURRENT_TIMESTAMP()” clause. See
Rendering ON UPDATE CURRENT TIMESTAMP for MySQL’s explicit_defaults_for_timestamp for background on how to produce
this clause.
See also
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,
MariaDB 10.3 or greater, 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', metadata=meta), 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. Typically, the sequence function is embedded in the INSERT statement, which is combined with RETURNING so that the newly generated value can be returned to the Python code:
INSERT INTO cartitems (cart_id, description, createdate)
VALUES (next_val(cart_id_seq), 'some description', '2015-10-15 12:00:15')
RETURNING cart_id
When the Sequence
is associated with a
Column
as its Python-side default generator, the
Sequence
will also be subject to “CREATE SEQUENCE” and “DROP
SEQUENCE” DDL when similar DDL is emitted for the owning Table
.
This is a limited scope convenience feature that does not accommodate for
inheritance of other aspects of the MetaData
, such as the default
schema. Therefore, it is best practice that for a Sequence
which
is local to a certain Column
/ Table
, that it be
explicitly associated with the MetaData
using the
Sequence.metadata
parameter. See the section
Associating a Sequence with the MetaData for more background on this.
Associating a Sequence on a SERIAL column¶
PostgreSQL’s SERIAL datatype is an auto-incrementing type that implies
the implicit creation of a PostgreSQL sequence when CREATE TABLE is emitted.
If a Column
specifies an explicit Sequence
object
which also specifies a True
value for the Sequence.optional
boolean flag, the Sequence
will not take effect under PostgreSQL,
and the SERIAL datatype will proceed normally. Instead, the Sequence
will only take effect when used against other sequence-supporting
databases, currently Oracle and Firebird.
Executing a Sequence Standalone¶
A SEQUENCE is a first class schema object in SQL and can be used to generate
values independently in the database. If you have a Sequence
object, it can be invoked with its “next value” instruction by
passing it directly to a SQL execution method:
with my_engine.connect() as conn:
seq = Sequence('some_sequence')
nextid = conn.execute(seq)
In order to embed the “next value” function of a Sequence
inside of a SQL statement like a SELECT or INSERT, use the Sequence.next_value()
method, which will render at statement compilation time a SQL function that is
appropriate for the target backend:
>>> my_seq = Sequence('some_sequence')
>>> stmt = select([my_seq.next_value()])
>>> print(stmt.compile(dialect=postgresql.dialect()))
SELECT nextval('some_sequence') AS next_value_1
Associating a Sequence with the MetaData¶
For many years, the SQLAlchemy documentation referred to the
example of associating a Sequence
with a table as follows:
table = Table("cartitems", meta,
Column("cart_id", Integer, Sequence('cart_id_seq'),
primary_key=True),
Column("description", String(40)),
Column("createdate", DateTime())
)
While the above is a prominent idiomatic pattern, it is recommended that
the Sequence
in most cases be explicitly associated with the
MetaData
, using the Sequence.metadata
parameter:
table = Table("cartitems", meta,
Column(
"cart_id",
Integer,
Sequence('cart_id_seq', metadata=meta), primary_key=True),
Column("description", String(40)),
Column("createdate", DateTime())
)
The Sequence
object is a first class
schema construct that can exist independently of any table in a database, and
can also be shared among tables. Therefore SQLAlchemy does not implicitly
modify the Sequence
when it is associated with a Column
object as either the Python-side or server-side default generator. While the
CREATE SEQUENCE / DROP SEQUENCE DDL is emitted for a Sequence
defined as a Python side generator at the same time the table itself is subject
to CREATE or DROP, this is a convenience feature that does not imply that the
Sequence
is fully associated with the MetaData
object.
Explicitly associating the Sequence
with MetaData
allows for the following behaviors:
The
Sequence
will inherit theMetaData.schema
parameter specified to the targetMetaData
, which affects the production of CREATE / DROP DDL, if any.The
Sequence.create()
andSequence.drop()
methods automatically use the engine bound to theMetaData
object, if any.The
MetaData.create_all()
andMetaData.drop_all()
methods will emit CREATE / DROP for thisSequence
, even if theSequence
is not associated with anyTable
/Column
that’s a member of thisMetaData
.
Since the vast majority of cases that deal with Sequence
expect
that Sequence
to be fully “owned” by the associated Table
and that options like default schema are propagated, setting the
Sequence.metadata
parameter should be considered a best practice.
Associating a Sequence as the Server Side Default¶
Note
The following technique is known to work only with the PostgreSQL database. It does not work with Oracle.
The preceding sections illustrate how to associate a Sequence
with a
Column
as the Python side default generator:
Column(
"cart_id", Integer, Sequence('cart_id_seq', metadata=meta),
primary_key=True)
In the above case, the Sequence
will automatically be subject
to CREATE SEQUENCE / DROP SEQUENCE DDL when the related Table
is subject to CREATE / DROP. However, the sequence will not be present
as the server-side default for the column when CREATE TABLE is emitted.
If we want the sequence to be used as a server-side default,
meaning it takes place even if we emit INSERT commands to the table from
the SQL command line, we can use the Column.server_default
parameter in conjunction with the value-generation function of the
sequence, available from the Sequence.next_value()
method. Below
we illustrate the same Sequence
being associated with the
Column
both as the Python-side default generator as well as
the server-side default generator:
cart_id_seq = Sequence('cart_id_seq', metadata=meta)
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())
)
or with the ORM:
class CartItem(Base):
__tablename__ = 'cartitems'
cart_id_seq = Sequence('cart_id_seq', metadata=Base.metadata)
cart_id = Column(
Integer, cart_id_seq,
server_default=cart_id_seq.next_value(), primary_key=True)
description = Column(String(40))
createdate = Column(DateTime)
When the “CREATE TABLE” statement is emitted, on PostgreSQL it would be emitted as:
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)
)
Placement of the Sequence
in both the Python-side and server-side
default generation contexts ensures that the “primary key fetch” logic
works in all cases. Typically, sequence-enabled databases also support
RETURNING for INSERT statements, which is used automatically by SQLAlchemy
when emitting this statement. However if RETURNING is not used for a particular
insert, then SQLAlchemy would prefer to “pre-execute” the sequence outside
of the INSERT statement itself, which only works if the sequence is
included as the Python-side default generator function.
The example also associates the Sequence
with the enclosing
MetaData
directly, which again ensures that the Sequence
is fully associated with the parameters of the MetaData
collection
including the default schema, if any.
See also
Sequences/SERIAL/IDENTITY - in the PostgreSQL dialect documentation
RETURNING Support - in the Oracle dialect documentation
Computed (GENERATED ALWAYS AS) Columns¶
New in version 1.3.11.
The Computed
construct allows a Column
to be declared in
DDL as a “GENERATED ALWAYS AS” column, that is, one which has a value that is
computed by the database server. The construct accepts a SQL expression
typically declared textually using a string or the text()
construct, in
a similar manner as that of CheckConstraint
. The SQL expression is
then interpreted by the database server in order to determine the value for the
column within a row.
Example:
from sqlalchemy import Table, Column, MetaData, Integer, Computed
metadata = MetaData()
square = Table(
"square",
metadata,
Column("id", Integer, primary_key=True),
Column("side", Integer),
Column("area", Integer, Computed("side * side")),
Column("perimeter", Integer, Computed("4 * side")),
)
The DDL for the square
table when run on a PostgreSQL 12 backend will look
like:
CREATE TABLE square (
id SERIAL NOT NULL,
side INTEGER,
area INTEGER GENERATED ALWAYS AS (side * side) STORED,
perimeter INTEGER GENERATED ALWAYS AS (4 * side) STORED,
PRIMARY KEY (id)
)
Whether the value is persisted upon INSERT and UPDATE, or if it is calculated
on fetch, is an implementation detail of the database; the former is known as
“stored” and the latter is known as “virtual”. Some database implementations
support both, but some only support one or the other. The optional
Computed.persisted
flag may be specified as True
or False
to indicate if the “STORED” or “VIRTUAL” keyword should be rendered in DDL,
however this will raise an error if the keyword is not supported by the target
backend; leaving it unset will use a working default for the target backend.
The Computed
construct is a subclass of the FetchedValue
object, and will set itself up as both the “server default” and “server
onupdate” generator for the target Column
, meaning it will be treated
as a default generating column when INSERT and UPDATE statements are generated,
as well as that it will be fetched as a generating column when using the ORM.
This includes that it will be part of the RETURNING clause of the database
for databases which support RETURNING and the generated values are to be
eagerly fetched.
Note
A Column
that is defined with the Computed
construct may not store any value outside of that which the server applies
to it; SQLAlchemy’s behavior when a value is passed for such a column
to be written in INSERT or UPDATE is currently that the value will be
ignored.
“GENERATED ALWAYS AS” is currently known to be supported by:
MySQL version 5.7 and onwards
MariaDB 10.x series and onwards
PostgreSQL as of version 12
Oracle - with the caveat that RETURNING does not work correctly with UPDATE (a warning will be emitted to this effect when the UPDATE..RETURNING that includes a computed column is rendered)
Microsoft SQL Server
Firebird
When Computed
is used with an unsupported backend, if the target
dialect does not support it, a CompileError
is raised when attempting
to render the construct. Otherwise, if the dialect supports it but the
particular database server version in use does not, then a subclass of
DBAPIError
, usually OperationalError
, is raised when the
DDL is emitted to the database.
See also
Default Objects API¶
Object Name | Description |
---|---|
A plain default value on a column. |
|
Defines a generated column, i.e. “GENERATED ALWAYS AS” syntax. |
|
A DDL-specified DEFAULT column value. |
|
Base class for column default values. |
|
A marker for a transparent database-side default. |
|
Defines options for a named database sequence or an identity column. |
|
A DDL-specified DEFAULT column value. |
|
Represents a named database sequence. |
- class sqlalchemy.schema.Computed(sqltext, persisted=None)¶
Defines a generated column, i.e. “GENERATED ALWAYS AS” syntax.
The
Computed
construct is an inline construct added to the argument list of aColumn
object:from sqlalchemy import Computed Table('square', meta, Column('side', Float, nullable=False), Column('area', Float, Computed('side * side')) )
See the linked documentation below for complete details.
New in version 1.3.11.
Members
Class signature
class
sqlalchemy.schema.Computed
(sqlalchemy.schema.FetchedValue
,sqlalchemy.schema.SchemaItem
)-
method
sqlalchemy.schema.Computed.
__init__(sqltext, persisted=None)¶ Construct a GENERATED ALWAYS AS DDL construct to accompany a
Column
.- Parameters:
sqltext¶ –
A string containing the column generation expression, which will be used verbatim, or a SQL expression construct, such as a
text()
object. If given as a string, the object is converted to atext()
object.Warning
The
Computed.sqltext
argument toComputed
can be passed as a Python string argument, which will be treated as trusted SQL text and rendered as given. DO NOT PASS UNTRUSTED INPUT TO THIS PARAMETER.persisted¶ –
Optional, controls how this column should be persisted by the database. Possible values are:
None
, the default, it will use the default persistence defined by the database.True
, will renderGENERATED ALWAYS AS ... STORED
, or the equivalent for the target database if supported.False
, will renderGENERATED ALWAYS AS ... VIRTUAL
, or the equivalent for the target database if supported.
Specifying
True
orFalse
may raise an error when the DDL is emitted to the target database if the database does not support that persistence option. Leaving this parameter at its default ofNone
is guaranteed to succeed for all databases that supportGENERATED ALWAYS AS
.
-
method
- class sqlalchemy.schema.ColumnDefault(arg, **kwargs)¶
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 thedefault
,onupdate
arguments ofColumn
are used. AColumnDefault
can be passed positionally as well.For example, the following:
Column('foo', Integer, default=50)
Is equivalent to:
Column('foo', Integer, ColumnDefault(50))
Class signature
class
sqlalchemy.schema.ColumnDefault
(sqlalchemy.schema.DefaultGenerator
)
- class sqlalchemy.schema.DefaultClause(arg, for_update=False, _reflected=False)¶
A DDL-specified DEFAULT column value.
DefaultClause
is aFetchedValue
that also generates a “DEFAULT” clause when “CREATE TABLE” is emitted.DefaultClause
is generated automatically whenever theserver_default
,server_onupdate
arguments ofColumn
are used. ADefaultClause
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 signature
class
sqlalchemy.schema.DefaultClause
(sqlalchemy.schema.FetchedValue
)
- class sqlalchemy.schema.DefaultGenerator(for_update=False)¶
Base class for column default values.
Class signature
class
sqlalchemy.schema.DefaultGenerator
(sqlalchemy.schema._NotAColumnExpr
,sqlalchemy.schema.SchemaItem
)
- class sqlalchemy.schema.FetchedValue(for_update=False)¶
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.Class signature
class
sqlalchemy.schema.FetchedValue
(sqlalchemy.schema._NotAColumnExpr
,sqlalchemy.sql.expression.SchemaEventTarget
)
- class sqlalchemy.schema.PassiveDefault(*arg, **kw)¶
A DDL-specified DEFAULT column value.
Deprecated since version 0.6:
PassiveDefault
is deprecated and will be removed in a future release. Please refer toDefaultClause
.Class signature
class
sqlalchemy.schema.PassiveDefault
(sqlalchemy.schema.DefaultClause
)
- class sqlalchemy.schema.Sequence(name, start=None, increment=None, minvalue=None, maxvalue=None, nominvalue=None, nomaxvalue=None, cycle=None, schema=None, cache=None, order=None, optional=False, quote=None, metadata=None, quote_schema=None, for_update=False)¶
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 SQLAlchemyEngine
orConnection
, 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, theSequence
construct is ignored.Members
Class signature
class
sqlalchemy.schema.Sequence
(sqlalchemy.schema.IdentityOptions
,sqlalchemy.schema.DefaultGenerator
)-
method
sqlalchemy.schema.Sequence.
__init__(name, start=None, increment=None, minvalue=None, maxvalue=None, nominvalue=None, nomaxvalue=None, cycle=None, schema=None, cache=None, order=None, optional=False, quote=None, metadata=None, quote_schema=None, for_update=False)¶ Construct a
Sequence
object.- Parameters:
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.New in version 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.New in version 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.New in version 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.New in version 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.
New in version 1.0.7.
schema¶ – optional schema name for the sequence, if located in a schema other than the default. The rules for selecting the schema name when a
MetaData
is also present are the same as that ofTable.schema
.cache¶ –
optional integer value; number of future values in the sequence which are calculated in advance. Renders the CACHE keyword understood by Oracle and PostgreSQL.
New in version 1.1.12.
order¶ –
optional boolean value; if
True
, renders the ORDER keyword, understood by Oracle, indicating the sequence is definitively ordered. May be necessary to provide deterministic ordering using Oracle RAC.New in version 1.1.12.
optional¶ – boolean value, when
True
, indicates that thisSequence
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
orFalse
, explicitly forces quoting of theSequence.name
on or off. When left at its default ofNone
, 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 thisSequence
will be associated with. ASequence
that is associated with aMetaData
gains the following capabilities:The
Sequence
will inherit theMetaData.schema
parameter specified to the targetMetaData
, which affects the production of CREATE / DROP DDL, if any.The
Sequence.create()
andSequence.drop()
methods automatically use the engine bound to theMetaData
object, if any.The
MetaData.create_all()
andMetaData.drop_all()
methods will emit CREATE / DROP for thisSequence
, even if theSequence
is not associated with anyTable
/Column
that’s a member of thisMetaData
.
The above behaviors can only occur if the
Sequence
is explicitly associated with theMetaData
via this parameter.See also
Associating a Sequence with the MetaData - full discussion of the
Sequence.metadata
parameter.for_update¶ – Indicates this
Sequence
, when associated with aColumn
, 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.
-
attribute
sqlalchemy.schema.Sequence.
bind¶
-
method
sqlalchemy.schema.Sequence.
create(bind=None, checkfirst=True)¶ Creates this sequence in the database.
-
method
sqlalchemy.schema.Sequence.
drop(bind=None, checkfirst=True)¶ Drops this sequence from the database.
-
method
sqlalchemy.schema.Sequence.
next_value(func)¶ Return a
next_value
function element which will render the appropriate increment function for thisSequence
within any SQL expression.
-
method
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Sat 06 Jan 2024 12:15:52 PM