sqlalchemy.sql.schema.Column

The name of this column as represented in the database. This argument may be the first positional argument, or specified via keyword.

Names which contain no upper case characters will be treated as case insensitive names, and will not be quoted unless they are a reserved word. Names with any number of upper case characters will be quoted and sent exactly. Note that this behavior applies even for databases which standardize upper case names as case insensitive such as Oracle.

The name field may be omitted at construction time and applied later, at any time before the Column is associated with a _schema.Table. This is to support convenient usage within the ~sqlalchemy.ext.declarative extension.

The column's type, indicated using an instance which subclasses ~sqlalchemy.types.TypeEngine. If no arguments are required for the type, the class of the type can be sent as well, e.g.:

# use a type with arguments
Column('data', String(50))

# use no arguments
Column('level', Integer)

The type argument may be the second positional argument or specified by keyword.

If the type is None or is omitted, it will first default to the special type .NullType. If and when this _schema.Column is made to refer to another column using _schema.ForeignKey and/or _schema.ForeignKeyConstraint, the type of the remote-referenced column will be copied to this column as well, at the moment that the foreign key is resolved against that remote _schema.Column object.

Set up "auto increment" semantics for an integer primary key column. The default value is the string "auto" which indicates that a single-column primary key that is of an INTEGER type with no stated client-side or python-side defaults should receive auto increment semantics automatically; all other varieties of primary key columns will not. This includes that :term:`DDL` such as PostgreSQL SERIAL or MySQL AUTO_INCREMENT will be emitted for this column during a table create, as well as that the column is assumed to generate new integer primary key values when an INSERT statement invokes which will be retrieved by the dialect. When used in conjunction with .Identity on a dialect that supports it, this parameter has no effect.

The flag may be set to True to indicate that a column which is part of a composite (e.g. multi-column) primary key should have autoincrement semantics, though note that only one column within a primary key may have this setting. It can also be set to True to indicate autoincrement semantics on a column that has a client-side or server-side default configured, however note that not all dialects can accommodate all styles of default as an "autoincrement". It can also be set to False on a single-column primary key that has a datatype of INTEGER in order to disable auto increment semantics for that column.

The setting only has an effect for columns which are:

• Integer derived (i.e. INT, SMALLINT, BIGINT).

• Part of the primary key

• Not referring to another column via _schema.ForeignKey, unless the value is specified as 'ignore_fk':

  # turn on autoincrement for this column despite
  # the ForeignKey()
  Column('id', ForeignKey('other.id'),
              primary_key=True, autoincrement='ignore_fk')
  

  It is typically not desirable to have "autoincrement" enabled on a column that refers to another via foreign key, as such a column is required to refer to a value that originates from elsewhere.

The setting has these two effects on columns that meet the above criteria:

• DDL issued for the column will include database-specific keywords intended to signify this column as an "autoincrement" column, such as AUTO INCREMENT on MySQL, SERIAL on PostgreSQL, and IDENTITY on MS-SQL. It does not issue AUTOINCREMENT for SQLite since this is a special SQLite flag that is not required for autoincrementing behavior.

  See Also

  :ref:`sqlite_autoincrement`

• The column will be considered to be available using an "autoincrement" method specific to the backend database, such as calling upon cursor.lastrowid, using RETURNING in an INSERT statement to get at a sequence-generated value, or using special functions such as "SELECT scope_identity()". These methods are highly specific to the DBAPIs and databases in use and vary greatly, so care should be taken when associating autoincrement=True with a custom default generation function.

A scalar, Python callable, or _expression.ColumnElement expression representing the default value for this column, which will be invoked upon insert if this column is otherwise not specified in the VALUES clause of the insert. This is a shortcut to using .ColumnDefault as a positional argument; see that class for full detail on the structure of the argument.

Contrast this argument to :paramref:`_schema.Column.server_default` which creates a default generator on the database side.

When True, indicates that a _schema.Index construct will be automatically generated for this _schema.Column, which will result in a "CREATE INDEX" statement being emitted for the _schema.Table when the DDL create operation is invoked.

Using this flag is equivalent to making use of the _schema.Index construct explicitly at the level of the _schema.Table construct itself:

Table(
    "some_table",
    metadata,
    Column("x", Integer),
    Index("ix_some_table_x", "x")
)

To add the :paramref:`_schema.Index.unique` flag to the _schema.Index, set both the :paramref:`_schema.Column.unique` and :paramref:`_schema.Column.index` flags to True simultaneously, which will have the effect of rendering the "CREATE UNIQUE INDEX" DDL instruction instead of "CREATE INDEX".

The name of the index is generated using the :ref:`default naming convention <constraint_default_naming_convention>` which for the _schema.Index construct is of the form ix_<tablename>_<columnname>.

As this flag is intended only as a convenience for the common case of adding a single-column, default configured index to a table definition, explicit use of the _schema.Index construct should be preferred for most use cases, including composite indexes that encompass more than one column, indexes with SQL expressions or ordering, backend-specific index configuration options, and indexes that use a specific name.

When set to False, will cause the "NOT NULL" phrase to be added when generating DDL for the column. When True, will normally generate nothing (in SQL this defaults to "NULL"), except in some very specific backend-specific edge cases where "NULL" may render explicitly. Defaults to True unless :paramref:`_schema.Column.primary_key` is also True or the column specifies a _sql.Identity, in which case it defaults to False. This parameter is only used when issuing CREATE TABLE statements.

A scalar, Python callable, or ~sqlalchemy.sql.expression.ClauseElement representing a default value to be applied to the column within UPDATE statements, which will be invoked upon update if this column is not present in the SET clause of the update. This is a shortcut to using .ColumnDefault as a positional argument with for_update=True.

A .FetchedValue instance, str, Unicode or ~sqlalchemy.sql.expression.text construct representing the DDL DEFAULT value for the column.

String types will be emitted as-is, surrounded by single quotes:

Column('x', Text, server_default="val")

x TEXT DEFAULT 'val'

A ~sqlalchemy.sql.expression.text expression will be rendered as-is, without quotes:

Column('y', DateTime, server_default=text('NOW()'))

y DATETIME DEFAULT NOW()

Strings and text() will be converted into a .DefaultClause object upon initialization.

This parameter can also accept complex combinations of contextually valid SQLAlchemy expressions or constructs:

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, MetaData, ARRAY, Text
from sqlalchemy.dialects.postgresql import array

engine = create_engine(
    'postgresql://scott:tiger@localhost/mydatabase'
)
metadata_obj = MetaData()
tbl = Table(
        "foo",
        metadata_obj,
        Column("bar",
               ARRAY(Text),
               server_default=array(["biz", "bang", "bash"])
               )
)
metadata_obj.create_all(engine)

The above results in a table created with the following SQL:

CREATE TABLE foo (
    bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
)

Use .FetchedValue to indicate that an already-existing column will generate a default value on the database side which will be available to SQLAlchemy for post-fetch after inserts. This construct does not specify any DDL and the implementation is left to the database, such as via a trigger.

A .FetchedValue instance representing a database-side default generation function, such as a trigger. This indicates to SQLAlchemy that a newly generated value will be available after updates. This construct does not actually implement any kind of generation function within the database, which instead must be specified separately.

When True, and the :paramref:`_schema.Column.index` parameter is left at its default value of False, indicates that a _schema.UniqueConstraint construct will be automatically generated for this _schema.Column, which will result in a "UNIQUE CONSTRAINT" clause referring to this column being included in the CREATE TABLE statement emitted, when the DDL create operation for the _schema.Table object is invoked.

When this flag is True while the :paramref:`_schema.Column.index` parameter is simultaneously set to True, the effect instead is that a _schema.Index construct which includes the :paramref:`_schema.Index.unique` parameter set to True is generated. See the documentation for :paramref:`_schema.Column.index` for additional detail.

Using this flag is equivalent to making use of the _schema.UniqueConstraint construct explicitly at the level of the _schema.Table construct itself:

Table(
    "some_table",
    metadata,
    Column("x", Integer),
    UniqueConstraint("x")
)

The :paramref:`_schema.UniqueConstraint.name` parameter of the unique constraint object is left at its default value of None; in the absence of a :ref:`naming convention <constraint_naming_conventions>` for the enclosing _schema.MetaData, the UNIQUE CONSTRAINT construct will be emitted as unnamed, which typically invokes a database-specific naming convention to take place.

As this flag is intended only as a convenience for the common case of adding a single-column, default configured unique constraint to a table definition, explicit use of the _schema.UniqueConstraint construct should be preferred for most use cases, including composite constraints that encompass more than one column, backend-specific index configuration options, and constraints that use a specific name.

When True, indicates this is a "system" column, that is a column which is automatically made available by the database, and should not be included in the columns list for a CREATE TABLE statement.

For more elaborate scenarios where columns should be conditionally rendered differently on different backends, consider custom compilation rules for .CreateColumn.

Optional string that will render an SQL comment on table creation.