Describing Databases with MetaData

A collection of metadata entities is stored in an object aptly named MetaData:

is a container object that keeps together many different features of a database (or multiple databases) being described.

To represent a table, use the Table class. Its two primary arguments are the table name, then the object which it will be associated with. The remaining positional arguments are mostly Column objects describing each column:

user = Table('user', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60)),
    Column('nickname', String(50), nullable=False)
)

Above, a table called user is described, which contains four columns. The primary key of the table consists of the user_id column. Multiple columns may be assigned the primary_key=True flag which denotes a multi-column primary key, known as a composite primary key.

Note also that each column describes its datatype using objects corresponding to genericized types, such as and String. SQLAlchemy features dozens of types of varying levels of specificity as well as the ability to create custom types. Documentation on the type system can be found at .

The MetaData object contains all of the schema constructs we’ve associated with it. It supports a few methods of accessing these table objects, such as the sorted_tables accessor which returns a list of each object in order of foreign key dependency (that is, each table is preceded by all tables which it references):

>>> for t in metadata.sorted_tables:
...    print(t.name)
user
user_preference
invoice
invoice_item

In most cases, individual Table objects have been explicitly declared, and these objects are typically accessed directly as module-level variables in an application. Once a has been defined, it has a full set of accessors which allow inspection of its properties. Given the following Table definition:

employees = Table('employees', metadata,
    Column('employee_id', Integer, primary_key=True),
    Column('employee_name', String(60), nullable=False),
    Column('employee_dept', Integer, ForeignKey("departments.department_id"))
)

Note the object used in this table - this construct defines a reference to a remote table, and is fully described in Defining Foreign Keys. Methods of accessing information about this table include:

# access the column "EMPLOYEE_ID":
employees.columns.employee_id
# or just
employees.c.employee_id
# via string
employees.c['employee_id']
# iterate through all columns
for c in employees.c:
    print(c)
# get the table's primary key columns
for primary_key in employees.primary_key:
    print(primary_key)
# get the table's foreign key objects:
for fkey in employees.foreign_keys:
    print(fkey)
# access the table's MetaData:
employees.metadata
# access the table's bound Engine or Connection, if its MetaData is bound:
employees.bind
# access a column's name, type, nullable, primary key, foreign key
employees.c.employee_id.name
employees.c.employee_id.type
employees.c.employee_id.nullable
employees.c.employee_id.primary_key
employees.c.employee_dept.foreign_keys
# get the "key" of a column, which defaults to its name, but can
# be any user-defined string:
employees.c.employee_name.key
# access a column's table:
employees.c.employee_id.table is employees
# get the table related by a foreign key
list(employees.c.employee_dept.foreign_keys)[0].column.table

Creating and Dropping Database Tables

Once you’ve defined some Table objects, assuming you’re working with a brand new database one thing you might want to do is issue CREATE statements for those tables and their related constructs (as an aside, it’s also quite possible that you don’t want to do this, if you already have some preferred methodology such as tools included with your database or an existing scripting system - if that’s the case, feel free to skip this section - SQLAlchemy has no requirement that it be used to create your tables).

The usual way to issue CREATE is to use on the MetaData object. This method will issue queries that first check for the existence of each individual table, and if not found will issue the CREATE statements:

creates foreign key constraints between tables usually inline with the table definition itself, and for this reason it also generates the tables in order of their dependency. There are options to change this behavior such that ALTER TABLE is used instead.

Dropping all tables is similarly achieved using the drop_all() method. This method does the exact opposite of - the presence of each table is checked first, and tables are dropped in reverse order of dependency.

Creating and dropping individual tables can be done via the create() and drop() methods of Table. These methods by default issue the CREATE or DROP regardless of the table being present:

engine = create_engine('sqlite:///:memory:')
meta = MetaData()
employees = Table('employees', meta,
    Column('employee_id', Integer, primary_key=True),
    Column('employee_name', String(60), nullable=False, key='name'),
    Column('employee_dept', Integer, ForeignKey("departments.department_id"))
)
sqlemployees.create(engine)
CREATE TABLE employees(
employee_id SERIAL NOT NULL PRIMARY KEY,
employee_name VARCHAR(60) NOT NULL,
employee_dept INTEGER REFERENCES departments(department_id)
)
{}

drop() method:

sqlemployees.drop(engine)
DROP TABLE employees
{}

To enable the “check first for the table existing” logic, add the checkfirst=True argument to create() or drop():

employees.create(engine, checkfirst=True)
employees.drop(engine, checkfirst=False)

While SQLAlchemy directly supports emitting CREATE and DROP statements for schema constructs, the ability to alter those constructs, usually via the ALTER statement as well as other database-specific constructs, is outside of the scope of SQLAlchemy itself. While it’s easy enough to emit ALTER statements and similar by hand, such as by passing a construct to Connection.execute() or by using the construct, it’s a common practice to automate the maintenance of database schemas in relation to application code using schema migration tools.

The SQLAlchemy project offers the Alembic migration tool for this purpose. Alembic features a highly customizable environment and a minimalistic usage pattern, supporting such features as transactional DDL, automatic generation of “candidate” migrations, an “offline” mode which generates SQL scripts, and support for branch resolution.

Alembic supersedes the project, which is the original migration tool for SQLAlchemy and is now considered legacy.

Specifying the Schema Name

Most databases support the concept of multiple “schemas” - namespaces that refer to alternate sets of tables and other constructs. The server-side geometry of a “schema” takes many forms, including names of “schemas” under the scope of a particular database (e.g. PostgreSQL schemas), named sibling databases (e.g. MySQL / MariaDB access to other databases on the same server), as well as other concepts like tables owned by other usernames (Oracle, SQL Server) or even names that refer to alternate database files (SQLite ATTACH) or remote servers (Oracle DBLINK with synonyms).

What all of the above approaches have (mostly) in common is that there’s a way of referring to this alternate set of tables using a string name. SQLAlchemy refers to this name as the schema name. Within SQLAlchemy, this is nothing more than a string name which is associated with a object, and is then rendered into SQL statements in a manner appropriate to the target database such that the table is referred towards in its remote “schema”, whatever mechanism that is on the target database.

The “schema” name may be associated directly with a Table using the argument; when using the ORM with declarative table configuration, the parameter is passed using the __table_args__ parameter dictionary.

The “schema” name may also be associated with the object where it will take effect automatically for all Table objects associated with that that don’t otherwise specify their own name. Finally, SQLAlchemy also supports a “dynamic” schema name system that is often used for multi-tenant applications such that a single set of Table metadata may refer to a dynamically configured set of schema names on a per-connection or per-statement basis.

See also

- schema name specification when using the ORM declarative table configuration

The most basic example is that of the argument using a Core Table object as follows:

metadata = MetaData()
financial_info = Table(
    'financial_info',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('value', String(100), nullable=False),
    schema='remote_banks'
)

SQL that is rendered using this , such as the SELECT statement below, will explicitly qualify the table name financial_info with the remote_banks schema name:

>>> print(select(financial_info))
SELECT remote_banks.financial_info.id, remote_banks.financial_info.value
FROM remote_banks.financial_info

When a Table object is declared with an explicit schema name, it is stored in the internal namespace using the combination of the schema and table name. We can view this in the MetaData.tables collection by searching for the key 'remote_banks.financial_info':

>>> metadata.tables['remote_banks.financial_info']
Table('financial_info', MetaData(),
Column('id', Integer(), table=<financial_info>, primary_key=True, nullable=False),
Column('value', String(length=100), table=<financial_info>, nullable=False),
schema='remote_banks')

This dotted name is also what must be used when referring to the table for use with the or ForeignKeyConstraint objects, even if the referring table is also in that same schema:

customer = Table(
    "customer",
    metadata,
    Column('id', Integer, primary_key=True),
    Column('financial_info_id', ForeignKey("remote_banks.financial_info.id")),
    schema='remote_banks'
)

The argument may also be used with certain dialects to indicate a multiple-token (e.g. dotted) path to a particular table. This is particularly important on a database such as Microsoft SQL Server where there are often dotted “database/owner” tokens. The tokens may be placed directly in the name at once, such as:

schema="dbo.scott"

See also

Multipart Schema Names - describes use of dotted schema names with the SQL Server dialect.

The object may also set up an explicit default option for all Table.schema parameters by passing the argument to the top level MetaData construct:

metadata = MetaData(schema="remote_banks")
financial_info = Table(
    'financial_info',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('value', String(100), nullable=False),
)

Above, for any object (or Sequence object directly associated with the ) which leaves the Table.schema parameter at its default of None will instead act as though the parameter were set to the value "remote_banks". This includes that the is cataloged in the MetaData using the schema-qualified name, that is:

metadata.tables['remote_banks.financial_info']

When using the or ForeignKeyConstraint objects to refer to this table, either the schema-qualified name or the non-schema-qualified name may be used to refer to the remote_banks.financial_info table:

# either will work:
refers_to_financial_info = Table(
    'refers_to_financial_info',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('fiid', ForeignKey('financial_info.id')),
)
# or
refers_to_financial_info = Table(
    'refers_to_financial_info',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('fiid', ForeignKey('remote_banks.financial_info.id')),
)

When using a object that sets MetaData.schema, a that wishes to specify that it should not be schema qualified may use the special symbol BLANK_SCHEMA:

from sqlalchemy import BLANK_SCHEMA
metadata = MetaData(schema="remote_banks")
financial_info = Table(
    'financial_info',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('value', String(100), nullable=False),
    schema=BLANK_SCHEMA  # will not use "remote_banks"
)

See also

The names used by the Table.schema parameter may also be applied against a lookup that is dynamic on a per-connection or per-execution basis, so that for example in multi-tenant situations, each transaction or statement may be targeted at a specific set of schema names that change. The section describes how this feature is used.

See also

Translation of Schema Names

The above approaches all refer to methods of including an explicit schema-name within SQL statements. Database connections in fact feature the concept of a “default” schema, which is the name of the “schema” (or database, owner, etc.) that takes place if a table name is not explicitly schema-qualified. These names are usually configured at the login level, such as when connecting to a PostgreSQL database, the default “schema” is called “public”.

There are often cases where the default “schema” cannot be set via the login itself and instead would usefully be configured each time a connection is made, using a statement such as “SET SEARCH_PATH” on PostgreSQL or “ALTER SESSION” on Oracle. These approaches may be achieved by using the PoolEvents.connect() event, which allows access to the DBAPI connection when it is first created. For example, to set the Oracle CURRENT_SCHEMA variable to an alternate name:

from sqlalchemy import event
from sqlalchemy import create_engine
engine = create_engine("oracle+cx_oracle://scott:tiger@tsn_name")
@event.listens_for(engine, "connect", insert=True)
def set_current_schema(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("ALTER SESSION SET CURRENT_SCHEMA=%s" % schema_name)
    cursor.close()

Above, the set_current_schema() event handler will take place immediately when the above first connects; as the event is “inserted” into the beginning of the handler list, it will also take place before the dialect’s own event handlers are run, in particular including the one that will determine the “default schema” for the connection.

For other databases, consult the database and/or dialect documentation for specific information regarding how default schemas are configured.

Changed in version 1.4.0b2: The above recipe now works without the need to establish additional event handlers.

See also

Setting Alternate Search Paths on Connect - in the dialect documentation.

Table supports database-specific options. For example, MySQL has different table backend types, including “MyISAM” and “InnoDB”. This can be expressed with using mysql_engine:

addresses = Table('engine_email_addresses', meta,
    Column('address_id', Integer, primary_key=True),
    Column('remote_user_id', Integer, ForeignKey(users.c.user_id)),
    Column('email_address', String(20)),
    mysql_engine='InnoDB'
)

Other backends may support table-level options as well - these would be described in the individual documentation sections for each dialect.

Column, Table, MetaData API

attribute sqlalchemy.schema.``BLANK_SCHEMA

Symbol indicating that a Table or should have ‘None’ for its schema, even if the parent MetaData has specified a schema.

See also

Table.schema

New in version 1.0.14.

class sqlalchemy.schema.``Column(\args, **kwargs*)

Represents a column in a database table.

Class signature

class sqlalchemy.schema.Column (, sqlalchemy.schema.SchemaItem, )

method sqlalchemy.schema.Column.__eq__(other)

inherited from the sqlalchemy.sql.expression.ColumnOperators.__eq__ method of

Implement the == operator.

In a column context, produces the clause a = b. If the target is None, produces a IS NULL.
method sqlalchemy.schema.Column.__init__(\args, **kwargs*)

Construct a new Column object.
- Parameters
  - name –
    
    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 . This is to support convenient usage within the declarative extension.
  - type_ –
    
    The column’s type, indicated using an instance which subclasses . 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 is made to refer to another column using ForeignKey and/or , 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 Column object.
    
    Changed in version 0.9.0: Support for propagation of type to a from its ForeignKey object has been improved and should be more reliable and timely.
  - *args – Additional positional arguments include various derived constructs which will be applied as options to the column. These include instances of Constraint, , ColumnDefault, , Computed . In some cases an equivalent keyword argument is available such as server_default, default and unique.
  - autoincrement –
    
    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 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 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.
    
    Changed in version 1.1: The autoincrement flag now defaults to "auto" which indicates autoincrement semantics by default for single-column integer primary keys only; for composite (multi-column) primary keys, autoincrement is never implicitly enabled; as always, autoincrement=True will allow for at most one of those columns to be an “autoincrement” column. autoincrement=True may also be set on a Column that has an explicit client-side or server-side default, subject to limitations of the backend database and dialect.
    
    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 , 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
      
      SQLite Auto Incrementing Behavior
    - 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.
  - default –
    
    A scalar, Python callable, or 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 which creates a default generator on the database side.
    
    See also
    
    Column INSERT/UPDATE Defaults
  - doc – optional String that can be used by the ORM or similar to document attributes on the Python side. This attribute does not render SQL comments; use the parameter for this purpose.
  - key – An optional string identifier which will identify this Column object on the Table. When a key is provided, this is the only identifier referencing the Column within the application, including ORM attribute mapping; the name field is used only when rendering SQL.
  - index –
    
    When True, indicates that a construct will be automatically generated for this Column, which will result in a “CREATE INDEX” statement being emitted for the when the DDL create operation is invoked.
    
    Using this flag is equivalent to making use of the Index construct explicitly at the level of the construct itself:
```
Table(
    "some_table",
    metadata,
    Column("x", Integer),
    Index("ix_some_table_x", "x")
)
```
    To add the Index.unique flag to the , set both the Column.unique and 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 default naming convention which for the 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 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.
    
    Note
    
    the attribute on Column does not indicate if this column is indexed or not, only if this flag was explicitly set here. To view indexes on a column, view the collection or use Inspector.get_indexes().
    
    See also
    
    Configuring Constraint Naming Conventions
  - info – Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.
  - nullable –
    
    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 is also True or the column specifies a Identity, in which case it defaults to False. This parameter is only used when issuing CREATE TABLE statements.
    
    Note
    
    When the column specifies a Identity this parameter is in general ignored by the DDL compiler. The PostgreSQL database allows nullable identity column by setting this parameter to True explicitly.
  - onupdate –
    
    A scalar, Python callable, or 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 as a positional argument with for_update=True.
    
    See also
    
    Column INSERT/UPDATE Defaults - complete discussion of onupdate
  - primary_key – If True, marks this column as a primary key column. Multiple columns can have this flag set to specify composite primary keys. As an alternative, the primary key of a can be specified via an explicit PrimaryKeyConstraint object.
  - server_default –
    
    A instance, str, Unicode or 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 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.
    
    Use 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.
    
    See also
    
    Server-invoked DDL-Explicit Default Expressions - complete discussion of server side defaults
  - server_onupdate –
    
    A 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.
    
    Warning
    
    This directive does not currently produce MySQL’s “ON UPDATE CURRENT_TIMESTAMP()” clause. See Rendering ON UPDATE CURRENT TIMESTAMP for MySQL / MariaDB’s explicit_defaults_for_timestamp for background on how to produce this clause.
    
    See also
  - quote – Force quoting of this column’s name on or off, corresponding to True or False. When left at its default of None, the column identifier will be quoted according to whether the name is case sensitive (identifiers with at least one upper case character are treated as case sensitive), or if it’s a reserved word. This flag is only needed to force quoting of a reserved word which is not known by the SQLAlchemy dialect.
  - unique –
    
    When True, and the Column.index parameter is left at its default value of False, indicates that a construct will be automatically generated for this 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 object is invoked.
    
    When this flag is True while the Column.index parameter is simultaneously set to True, the effect instead is that a construct which includes the Index.unique parameter set to True is generated. See the documentation for for additional detail.
    
    Using this flag is equivalent to making use of the UniqueConstraint construct explicitly at the level of the construct itself:
```
Table(
    "some_table",
    metadata,
    Column("x", Integer),
    UniqueConstraint("x")
```
    The UniqueConstraint.name parameter of the unique constraint object is left at its default value of None; in the absence of a for the enclosing 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 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.
    
    Note
    
    the Column.unique attribute on does not indicate if this column has a unique constraint or not, only if this flag was explicitly set here. To view indexes and unique constraints that may involve this column, view the Table.indexes and/or collections or use Inspector.get_indexes() and/or
    
    See also
    
    UNIQUE Constraint
    
    Column.index
  - system –
    
    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 .
  - comment –
    
    Optional string that will render an SQL comment on table creation.
    
    New in version 1.2: Added the Column.comment parameter to .

method sqlalchemy.schema.Column.__le__(other)

inherited from the sqlalchemy.sql.expression.ColumnOperators.__le__ method of

Implement the <= operator.

In a column context, produces the clause a <= b.
method sqlalchemy.schema.Column.__lt__(other)

inherited from the sqlalchemy.sql.expression.ColumnOperators.__lt__ method of

Implement the < operator.

In a column context, produces the clause a < b.
method sqlalchemy.schema.Column.__ne__(other)

inherited from the sqlalchemy.sql.expression.ColumnOperators.__ne__ method of

Implement the != operator.

In a column context, produces the clause a != b. If the target is None, produces a IS NOT NULL.
method sqlalchemy.schema.Column.all_()

inherited from the method of ColumnOperators

Produce a clause against the parent object.

This operator is only appropriate against a scalar subquery object, or for some backends an column expression that is against the ARRAY type, e.g.:
```
# postgresql '5 = ALL (somearray)'
expr = 5 == mytable.c.somearray.all_()
# mysql '5 = ALL (SELECT value FROM table)'
expr = 5 == select(table.c.value).scalar_subquery().all_()
```
See also

all_() - standalone version

- ANY operator

New in version 1.1.
attribute sqlalchemy.schema.Column.anon_key_label

inherited from the attribute of ColumnElement

Provides a constant ‘anonymous key label’ for this ColumnElement.

Compare to anon_label, except that the “key” of the column, if available, is used to generate the label.

This is used when a deduplicating key is placed into the columns collection of a selectable.
attribute anon_label

inherited from the ColumnElement.anon_label attribute of

Provides a constant ‘anonymous label’ for this ColumnElement.

This is a label() expression which will be named at compile time. The same label() is returned each time anon_label is called so that expressions can reference anon_label multiple times, producing the same label name at compile time.

The compiler uses this function automatically at compile time for expressions that are known to be ‘unnamed’ like binary expressions and function calls.
method sqlalchemy.schema.Column.any_()

inherited from the method of ColumnOperators

Produce a clause against the parent object.

This operator is only appropriate against a scalar subquery object, or for some backends an column expression that is against the ARRAY type, e.g.:
```
# postgresql '5 = ANY (somearray)'
expr = 5 == mytable.c.somearray.any_()
# mysql '5 = ANY (SELECT value FROM table)'
expr = 5 == select(table.c.value).scalar_subquery().any_()
```
See also

any_() - standalone version

- ALL operator

New in version 1.1.
method sqlalchemy.schema.Column.classmethod argument_for(dialect_name, argument_name, default)

inherited from the method of DialectKWArgs

Add a new kind of dialect-specific keyword argument for this class.

E.g.:
```
Index.argument_for("mydialect", "length", None)
some_index = Index('a', 'b', mydialect_length=5)
```
The method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.
- Parameters
  - dialect_name – name of a dialect. The dialect must be locatable, else a is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.
  - argument_name – name of the parameter.
  - default – default value of the parameter.

New in version 0.9.4.

method sqlalchemy.schema.Column.asc()

inherited from the method of ColumnOperators

Produce a clause against the parent object.
method sqlalchemy.schema.Column.between(cleft, cright, symmetric=False)

inherited from the method of ColumnOperators

Produce a clause against the parent object, given the lower and upper range.
method sqlalchemy.schema.Column.bool_op(opstring, precedence=0)

inherited from the method of Operators

Return a custom boolean operator.

This method is shorthand for calling and passing the Operators.op.is_comparison flag with True.

See also
method sqlalchemy.schema.Column.cast(type_)

inherited from the method of ColumnElement

Produce a type cast, i.e. CAST(<expression> AS <type>).

This is a shortcut to the function.

See also

Data Casts and Type Coercion

type_coerce()

New in version 1.0.7.
method collate(collation)

inherited from the ColumnOperators.collate() method of

Produce a collate() clause against the parent object, given the collation string.

See also
method sqlalchemy.schema.Column.compare(other, \*kw*)

inherited from the method of ClauseElement

Compare this to the given ClauseElement.

Subclasses should override the default behavior, which is a straight identity comparison.

**kw are arguments consumed by subclass compare() methods and may be used to modify the criteria for comparison (see ).
method sqlalchemy.schema.Column.compile(bind=None, dialect=None, \*kw*)

inherited from the method of ClauseElement

Compile this SQL expression.

The return value is a object. Calling str() or unicode() on the returned value will yield a string representation of the result. The Compiled object also can return a dictionary of bind parameter names and values using the params accessor.
- Parameters
  - bind – An Engine or Connection from which a Compiled will be acquired. This argument takes precedence over this ’s bound engine, if any.
  - column_keys – Used for INSERT and UPDATE statements, a list of column names which should be present in the VALUES clause of the compiled statement. If None, all columns from the target table object are rendered.
  - dialect – A Dialect instance from which a Compiled will be acquired. This argument takes precedence over the bind argument as well as this ClauseElement ‘s bound engine, if any.
  - compile_kwargs –
    
    optional dictionary of additional parameters that will be passed through to the compiler within all “visit” methods. This allows any custom flag to be passed through to a custom compilation construct, for example. It is also used for the case of passing the literal_binds flag through:
```
from sqlalchemy.sql import table, column, select
t = table('t', column('x'))
s = select(t).where(t.c.x == 5)
print(s.compile(compile_kwargs={"literal_binds": True}))
```
    New in version 0.9.0.

See also
[How do I render SQL expressions as strings, possibly with bound parameters inlined?]($23f306fd0cdd485d.md#faq-sql-expression-string)

method concat(other)

inherited from the ColumnOperators.concat() method of

Implement the ‘concat’ operator.

In a column context, produces the clause a || b, or uses the concat() operator on MySQL.
method sqlalchemy.schema.Column.contains(other, \*kwargs*)

inherited from the method of ColumnOperators

Implement the ‘contains’ operator.

Produces a LIKE expression that tests against a match for the middle of a string value:
```
column LIKE '%' || <other> || '%'
```
E.g.:
```
stmt = select(sometable).\
    where(sometable.c.column.contains("foobar"))
```
Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.contains.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.
- Parameters
  - other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the flag is set to True.
  - autoescape –
    
    boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.
    
    An expression such as:
```
somecolumn.contains("foo%bar", autoescape=True)
```
    Will render as:
```
somecolumn LIKE '%' || :param || '%' ESCAPE '/'
```
    With the value of :param as "foo/%bar".
  - escape –
    
    a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.
    
    An expression such as:
```
somecolumn.contains("foo/%bar", escape="^")
```
    Will render as:
```
somecolumn LIKE '%' || :param || '%' ESCAPE '^'
```
    The parameter may also be combined with ColumnOperators.contains.autoescape:
    
    Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.


[`ColumnOperators.startswith()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.startswith "sqlalchemy.sql.expression.ColumnOperators.startswith")
[`ColumnOperators.endswith()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.endswith "sqlalchemy.sql.expression.ColumnOperators.endswith")
[`ColumnOperators.like()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.like "sqlalchemy.sql.expression.ColumnOperators.like")

method copy(\*kw*)

Deprecated since version 1.4: The Column.copy() method is deprecated and will be removed in a future release.
method desc()

inherited from the ColumnOperators.desc() method of

Produce a desc() clause against the parent object.
attribute dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the DialectKWArgs.dialect_options collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

New in version 0.9.2.

Changed in version 0.9.4: The collection is now writable.

See also

DialectKWArgs.dialect_options - nested dictionary form
attribute dialect_options

inherited from the DialectKWArgs.dialect_options attribute of

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:
```
arg = my_object.dialect_options['postgresql']['where']
```
New in version 0.9.2.

See also

DialectKWArgs.dialect_kwargs - flat dictionary form
method distinct()

inherited from the ColumnOperators.distinct() method of

Produce a distinct() clause against the parent object.
method endswith(other, \*kwargs*)

inherited from the ColumnOperators.endswith() method of

Implement the ‘endswith’ operator.

Produces a LIKE expression that tests against a match for the end of a string value:
```
column LIKE '%' || <other>
```
E.g.:
```
stmt = select(sometable).\
    where(sometable.c.column.endswith("foobar"))
```
Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.endswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.
- Parameters
  - other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.endswith.autoescape flag is set to True.
  - autoescape –
    
    boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.
    
    An expression such as:
```
somecolumn.endswith("foo%bar", autoescape=True)
```
    Will render as:
```
somecolumn LIKE '%' || :param ESCAPE '/'
```
    With the value of :param as "foo/%bar".
  - escape –
    
    a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.
    
    An expression such as:
```
somecolumn.endswith("foo/%bar", escape="^")
```
    Will render as:
```
somecolumn LIKE '%' || :param ESCAPE '^'
```
    The parameter may also be combined with :
```
somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
```
    Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also
[`ColumnOperators.startswith()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.startswith "sqlalchemy.sql.expression.ColumnOperators.startswith")
[`ColumnOperators.contains()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.contains "sqlalchemy.sql.expression.ColumnOperators.contains")
[`ColumnOperators.like()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.like "sqlalchemy.sql.expression.ColumnOperators.like")

attribute sqlalchemy.schema.Column.expression

inherited from the attribute of ColumnElement

Return a column expression.

Part of the inspection interface; returns self.
attribute foreign_keys = None

A collection of all ForeignKey marker objects associated with this .

Each object is a member of a Table-wide .

See also

Table.foreign_keys
method get_children(column_tables=False, \*kw*)

inherited from the ColumnClause.get_children() method of

Return immediate child Traversible elements of this .

This is used for visit traversal.

**kw may contain flags that change the collection that is returned, for example to return a subset of items in order to cut down on larger traversals, or to return child items from a different context (such as schema-level collections instead of clause-level).
method sqlalchemy.schema.Column.ilike(other, escape=None)

inherited from the method of ColumnOperators

Implement the ilike operator, e.g. case insensitive LIKE.

In a column context, produces an expression either of the form:
```
lower(a) LIKE lower(other)
```
Or on backends that support the ILIKE operator:
```
a ILIKE other
```
E.g.:
```
stmt = select(sometable).\
    where(sometable.c.column.ilike("%foobar%"))
```
- Parameters
  - other – expression to be compared
  - escape –
    
    optional escape character, renders the ESCAPE keyword, e.g.:
```
somecolumn.ilike("foo/%bar", escape="/")
```

See also
[`ColumnOperators.like()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.like "sqlalchemy.sql.expression.ColumnOperators.like")

method in_(other)

inherited from the ColumnOperators.in_() method of

Implement the in operator.

In a column context, produces the clause column IN <other>.

The given parameter other may be:
- A list of literal values, e.g.:
```
stmt.where(column.in_([1, 2, 3]))
```
  In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:
```
WHERE COL IN (?, ?, ?)
```
- A list of tuples may be provided if the comparison is against a tuple_() containing multiple expressions:
```
from sqlalchemy import tuple_
stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))
```
- An empty list, e.g.:
```
stmt.where(column.in_([]))
```
  In this calling form, the expression renders an “empty set” expression. These expressions are tailored to individual backends and are generally trying to get an empty SELECT statement as a subquery. Such as on SQLite, the expression is:
```
WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
```
  Changed in version 1.4: empty IN expressions now use an execution-time generated SELECT subquery in all cases.
- A bound parameter, e.g. , may be used if it includes the bindparam.expanding flag:
```
stmt.where(column.in_(bindparam('value', expanding=True)))
```
  In this calling form, the expression renders a special non-SQL placeholder expression that looks like:
```
WHERE COL IN ([EXPANDING_value])
```
```
connection.execute(stmt, {"value": [1, 2, 3]})
```
  The database would be passed a bound parameter for each value:
```
WHERE COL IN (?, ?, ?)
```
  New in version 1.2: added “expanding” bound parameters
  
  If an empty list is passed, a special “empty list” expression, which is specific to the database in use, is rendered. On SQLite this would be:
```
WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
```
  New in version 1.3: “expanding” bound parameters now support empty lists
- a construct, which is usually a correlated scalar select:
```
stmt.where(
    column.in_(
        select(othertable.c.y).
        where(table.c.x == othertable.c.x)
    )
)
```
  In this calling form, ColumnOperators.in_() renders as given:
```
WHERE COL IN (SELECT othertable.y
FROM othertable WHERE othertable.x = table.x)
```
- Parameters
  
  other – a list of literals, a construct, or a bindparam() construct that includes the flag set to True.
attribute sqlalchemy.schema.Column.index = None

The value of the parameter.

Does not indicate if this Column is actually indexed or not; use .

See also

Table.indexes
attribute info

inherited from the SchemaItem.info attribute of

Info dictionary associated with the object, allowing user-defined data to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as and Column.
method is_(other)

inherited from the ColumnOperators.is_() method of

Implement the IS operator.

Normally, IS is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS may be desirable if comparing to boolean values on certain platforms.

See also

ColumnOperators.is_not()
method is_distinct_from(other)

inherited from the ColumnOperators.is_distinct_from() method of

Implement the IS DISTINCT FROM operator.

Renders “a IS DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS NOT b”.

New in version 1.1.
method sqlalchemy.schema.Column.is_not(other)

inherited from the method of ColumnOperators

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS NOT may be desirable if comparing to boolean values on certain platforms.

Changed in version 1.4: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

See also
method sqlalchemy.schema.Column.is_not_distinct_from(other)

inherited from the method of ColumnOperators

Implement the IS NOT DISTINCT FROM operator.

Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.

Changed in version 1.4: The is_not_distinct_from() operator is renamed from isnot_distinct_from() in previous releases. The previous name remains available for backwards compatibility.

New in version 1.1.
method isnot(other)

inherited from the ColumnOperators.isnot() method of

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS NOT may be desirable if comparing to boolean values on certain platforms.

Changed in version 1.4: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.is_()
method isnot_distinct_from(other)

inherited from the ColumnOperators.isnot_distinct_from() method of

Implement the IS NOT DISTINCT FROM operator.

Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.

Changed in version 1.4: The is_not_distinct_from() operator is renamed from isnot_distinct_from() in previous releases. The previous name remains available for backwards compatibility.

New in version 1.1.
attribute sqlalchemy.schema.Column.kwargs

inherited from the attribute of DialectKWArgs

A synonym for .
method sqlalchemy.schema.Column.label(name)

inherited from the method of ColumnElement

Produce a column label, i.e. <columnname> AS <name>.

This is a shortcut to the function.

If ‘name’ is None, an anonymous label name will be generated.
method sqlalchemy.schema.Column.like(other, escape=None)

inherited from the method of ColumnOperators

Implement the like operator.

In a column context, produces the expression:
```
a LIKE other
```
E.g.:
```
stmt = select(sometable).\
    where(sometable.c.column.like("%foobar%"))
```
- Parameters
  - other – expression to be compared
  - escape –
    
    optional escape character, renders the ESCAPE keyword, e.g.:
```
somecolumn.like("foo/%bar", escape="/")
```

See also
[`ColumnOperators.ilike()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.ilike "sqlalchemy.sql.expression.ColumnOperators.ilike")

method match(other, \*kwargs*)

inherited from the ColumnOperators.match() method of

Implements a database-specific ‘match’ operator.

ColumnOperators.match() attempts to resolve to a MATCH-like function or operator provided by the backend. Examples include:
- PostgreSQL - renders x @@ to_tsquery(y)
- MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)
- Oracle - renders CONTAINS(x, y)
- other backends may provide special implementations.
- Backends without any special implementation will emit the operator as “MATCH”. This is compatible with SQLite, for example.
class memoized_attribute(fget, doc=None)

A read-only @property that is only evaluated once.
method classmethod memoized_instancemethod(fn)

inherited from the HasMemoized.memoized_instancemethod() method of HasMemoized

Decorate a method memoize its return value.
method sqlalchemy.schema.Column.not_ilike(other, escape=None)

inherited from the method of ColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation with , i.e. ~x.ilike(y).

Changed in version 1.4: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.ilike()
method not_in(other)

inherited from the ColumnOperators.not_in() method of

implement the NOT IN operator.

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

In the case that other is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. The may be used to alter this behavior.

Changed in version 1.4: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

Changed in version 1.2: The ColumnOperators.in_() and operators now produce a “static” expression for an empty IN sequence by default.

See also

ColumnOperators.in_()
method not_like(other, escape=None)

inherited from the ColumnOperators.not_like() method of

implement the NOT LIKE operator.

This is equivalent to using negation with ColumnOperators.like(), i.e. ~x.like(y).

Changed in version 1.4: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

See also
method sqlalchemy.schema.Column.notilike(other, escape=None)

inherited from the method of ColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation with , i.e. ~x.ilike(y).

Changed in version 1.4: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.ilike()
method notin_(other)

inherited from the ColumnOperators.notin_() method of

implement the NOT IN operator.

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

In the case that other is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. The may be used to alter this behavior.

Changed in version 1.4: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

Changed in version 1.2: The ColumnOperators.in_() and operators now produce a “static” expression for an empty IN sequence by default.

See also

ColumnOperators.in_()
method notlike(other, escape=None)

inherited from the ColumnOperators.notlike() method of

implement the NOT LIKE operator.

This is equivalent to using negation with ColumnOperators.like(), i.e. ~x.like(y).

Changed in version 1.4: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

See also
method sqlalchemy.schema.Column.nulls_first()

inherited from the method of ColumnOperators

Produce a clause against the parent object.

Changed in version 1.4: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.
method sqlalchemy.schema.Column.nulls_last()

inherited from the method of ColumnOperators

Produce a clause against the parent object.

Changed in version 1.4: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.
method sqlalchemy.schema.Column.nullsfirst()

inherited from the method of ColumnOperators

Produce a clause against the parent object.

Changed in version 1.4: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.
method sqlalchemy.schema.Column.nullslast()

inherited from the method of ColumnOperators

Produce a clause against the parent object.

Changed in version 1.4: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.
method sqlalchemy.schema.Column.op(opstring, precedence=0, is_comparison=False, return_type=None)

inherited from the method of Operators

Produce a generic operator function.

e.g.:
```
somecolumn.op("*")(5)
```
produces:
```
somecolumn * 5
```
This function can also be used to make bitwise operators explicit. For example:
```
somecolumn.op('&')(0xff)
```
is a bitwise AND of the value in somecolumn.
- Parameters
  - operator – a string which will be output as the infix operator between this element and the expression passed to the generated function.
  - precedence – precedence to apply to the operator, when parenthesizing expressions. A lower number will cause the expression to be parenthesized when applied against another operator with higher precedence. The default value of 0 is lower than all operators except for the comma (,) and AS operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.
  - is_comparison –
    
    if True, the operator will be considered as a “comparison” operator, that is which evaluates to a boolean true/false value, like ==, >, etc. This flag should be set so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition.
    
    New in version 0.9.2: - added the flag.
  - return_type – a TypeEngine class or object that will force the return type of an expression produced by this operator to be of that type. By default, operators that specify will resolve to Boolean, and those that do not will be of the same type as the left-hand operand.

See also
[Redefining and Creating New Operators]($993b6f7a0d78cd7b.md#types-operators)
[Using custom operators in join conditions]($e1f42b7742e49253.md#relationship-custom-operator)

method operate(op, \other, **kwargs*)

inherited from the ColumnElement.operate() method of

Operate on an argument.

This is the lowest level of operation, raises NotImplementedError by default.

Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding ColumnOperators to apply func.lower() to the left and right side:
```
class MyComparator(ColumnOperators):
    def operate(self, op, other):
        return op(func.lower(self), func.lower(other))
```
- Parameters
  - op – Operator callable.
  - *other – the ‘other’ side of the operation. Will be a single scalar for most operations.
  - **kwargs – modifiers. These may be passed by special operators such as ColumnOperators.contains().

method references(column)

Return True if this Column references the given column via foreign key.
method sqlalchemy.schema.Column.regexp_match(pattern, flags=None)

inherited from the method of ColumnOperators

Implements a database-specific ‘regexp match’ operator.

E.g.:
```
stmt = select(table.c.some_column).where(
    table.c.some_column.regexp_match('^(b|c)')
)
```
attempts to resolve to a REGEXP-like function or operator provided by the backend, however the specific regular expression syntax and flags available are not backend agnostic.

Examples include:
- PostgreSQL - renders x ~ y or x !~ y when negated.
- Oracle - renders REGEXP_LIKE(x, y)
- SQLite - uses SQLite’s REGEXP placeholder operator and calls into the Python re.match() builtin.
- other backends may provide special implementations.
- Backends without any special implementation will emit the operator as “REGEXP” or “NOT REGEXP”. This is compatible with SQLite and MySQL, for example.
Regular expression support is currently implemented for Oracle, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary.
- Parameters
  - pattern – The regular expression pattern string or column clause.
  - flags – Any regular expression string flags to apply. Flags tend to be backend specific. It can be a string or a column clause. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern. When using the ignore case flag ‘i’ in PostgreSQL, the ignore case regexp match operator ~* or !~* will be used.

method sqlalchemy.schema.Column.regexp_replace(pattern, replacement, flags=None)

inherited from the method of ColumnOperators

Implements a database-specific ‘regexp replace’ operator.

E.g.:
```
stmt = select(
    table.c.some_column.regexp_replace(
        'b(..)',
        'XY',
        flags='g'
    )
)
```
attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the function REGEXP_REPLACE(). However, the specific regular expression syntax and flags available are not backend agnostic.

Regular expression replacement support is currently implemented for Oracle, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary.
- Parameters
  - pattern – The regular expression pattern string or column clause.
  - pattern – The replacement string or column clause.
  - flags – Any regular expression string flags to apply. Flags tend to be backend specific. It can be a string or a column clause. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern.

New in version 1.4.
See also
[`ColumnOperators.regexp_match()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.regexp_match "sqlalchemy.sql.expression.ColumnOperators.regexp_match")

method sqlalchemy.schema.Column.reverse_operate(op, other, \*kwargs*)

inherited from the method of ColumnElement

Reverse operate on an argument.

Usage is the same as .
method sqlalchemy.schema.Column.self_group(against=None)

inherited from the method of ColumnElement

Apply a ‘grouping’ to this .

This method is overridden by subclasses to return a “grouping” construct, i.e. parenthesis. In particular it’s used by “binary” expressions to provide a grouping around themselves when placed into a larger expression, as well as by select() constructs when placed into the FROM clause of another . (Note that subqueries should be normally created using the Select.alias() method, as many platforms require nested SELECT statements to be named).

As expressions are composed together, the application of is automatic - end-user code should never need to use this method directly. Note that SQLAlchemy’s clause constructs take operator precedence into account - so parenthesis might not be needed, for example, in an expression like x OR (y AND z) - AND takes precedence over OR.

The base self_group() method of just returns self.
method sqlalchemy.schema.Column.shares_lineage(othercolumn)

inherited from the method of ColumnElement

Return True if the given has a common ancestor to this ColumnElement.
method startswith(other, \*kwargs*)

inherited from the ColumnOperators.startswith() method of

Implement the startswith operator.

Produces a LIKE expression that tests against a match for the start of a string value:
```
column LIKE <other> || '%'
```
E.g.:
```
stmt = select(sometable).\
    where(sometable.c.column.startswith("foobar"))
```
Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.startswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.
- Parameters
  - other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.startswith.autoescape flag is set to True.
  - autoescape –
    
    boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.
    
    An expression such as:
```
somecolumn.startswith("foo%bar", autoescape=True)
```
    Will render as:
```
somecolumn LIKE :param || '%' ESCAPE '/'
```
    With the value of :param as "foo/%bar".
  - escape –
    
    a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.
    
    An expression such as:
```
somecolumn.startswith("foo/%bar", escape="^")
```
    Will render as:
```
somecolumn LIKE :param || '%' ESCAPE '^'
```
    The parameter may also be combined with :
```
somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
```
    Where above, the given literal parameter will be converted to "foo^%bar^^bat" before being passed to the database.

See also
[`ColumnOperators.endswith()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.endswith "sqlalchemy.sql.expression.ColumnOperators.endswith")
[`ColumnOperators.contains()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.contains "sqlalchemy.sql.expression.ColumnOperators.contains")
[`ColumnOperators.like()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnOperators.like "sqlalchemy.sql.expression.ColumnOperators.like")

attribute sqlalchemy.schema.Column.unique = None

The value of the parameter.

Does not indicate if this Column is actually subject to a unique constraint or not; use and Table.constraints.

See also

Table.constraints.

class sqlalchemy.schema.``MetaData(bind=None, schema=None, quote_schema=None, naming_convention=None, info=None)

A collection of objects and their associated schema constructs.

Holds a collection of Table objects as well as an optional binding to an or Connection. If bound, the objects in the collection and their columns may participate in implicit SQL execution.

The Table objects themselves are stored in the dictionary.

MetaData is a thread-safe object for read operations. Construction of new tables within a single object, either explicitly or via reflection, may not be completely thread-safe.

See also

Describing Databases with MetaData - Introduction to database metadata

Class signature

class (sqlalchemy.schema.SchemaItem)

attribute bind

An Engine or to which this MetaData is bound.

Typically, a is assigned to this attribute so that “implicit execution” may be used, or alternatively as a means of providing engine binding information to an ORM Session object:
```
engine = create_engine("someurl://")
metadata.bind = engine
```
See also

- background on “bound metadata”
method sqlalchemy.schema.MetaData.clear()

Clear all Table objects from this MetaData.
method create_all(bind=None, tables=None, checkfirst=True)

Create all tables stored in this metadata.

Conditional by default, will not attempt to recreate tables already present in the target database.
- Parameters
  - bind –
    
    A Connectable used to access the database; if None, uses the existing bind on this MetaData, if any.
    
    Note
    
    the “bind” argument will be required in SQLAlchemy 2.0.
  - tables – Optional list of Table objects, which is a subset of the total tables in the MetaData (others are ignored).
  - checkfirst – Defaults to True, don’t issue CREATEs for tables already present in the target database.

method sqlalchemy.schema.MetaData.drop_all(bind=None, tables=None, checkfirst=True)

Drop all tables stored in this metadata.

Conditional by default, will not attempt to drop tables not present in the target database.
- Parameters
  - bind –
    
    A Connectable used to access the database; if None, uses the existing bind on this MetaData, if any.
    
    Note
    
    the “bind” argument will be required in SQLAlchemy 2.0.
  - tables – Optional list of Table objects, which is a subset of the total tables in the MetaData (others are ignored).
  - checkfirst – Defaults to True, only issue DROPs for tables confirmed to be present in the target database.

method is_bound()

True if this MetaData is bound to an Engine or Connection.
method sqlalchemy.schema.MetaData.reflect(bind=None, schema=None, views=False, only=None, extend_existing=False, autoload_replace=True, resolve_fks=True, \*dialect_kwargs*)

Load all available table definitions from the database.

Automatically creates Table entries in this MetaData for any table available in the database but not yet present in the MetaData. May be called multiple times to pick up tables recently added to the database, however no special action is taken if a table in this MetaData no longer exists in the database.
- Parameters
  - bind –
    
    A Connectable used to access the database; if None, uses the existing bind on this MetaData, if any.
    
    Note
    
    the “bind” argument will be required in SQLAlchemy 2.0.
  - schema – Optional, query and reflect tables from an alternate schema. If None, the schema associated with this is used, if any.
  - views – If True, also reflect views.
  - only –
    
    Optional. Load only a sub-set of available named tables. May be specified as a sequence of names or a callable.
    
    If a sequence of names is provided, only those tables will be reflected. An error is raised if a table is requested but not available. Named tables already present in this MetaData are ignored.
    
    If a callable is provided, it will be used as a boolean predicate to filter the list of potential table names. The callable is called with a table name and this MetaData instance as positional arguments and should return a true value for any table to reflect.
  - extend_existing –
    
    Passed along to each Table as .
    
    New in version 0.9.1.
  - autoload_replace –
    
    Passed along to each Table as .
    
    New in version 0.9.1.
  - resolve_fks –
    
    if True, reflect Table objects linked to objects located in each Table. For , this has the effect of reflecting related tables that might otherwise not be in the list of tables being reflected, for example if the referenced table is in a different schema or is omitted via the MetaData.reflect.only parameter. When False, objects are not followed to the Table in which they link, however if the related table is also part of the list of tables that would be reflected in any case, the object will still resolve to its related Table after the operation is complete. Defaults to True.
    
    New in version 1.3.0.
    
    See also
    
    Table.resolve_fks
  - **dialect_kwargs –
    
    Additional keyword arguments not mentioned above are dialect specific, and passed in the form <dialectname>_<argname>. See the documentation regarding an individual dialect at for detail on documented arguments.
    
    New in version 0.9.2: - Added MetaData.reflect.**dialect_kwargs to support dialect-level reflection options for all objects reflected.

method sqlalchemy.schema.MetaData.remove(table)

Remove the given Table object from this MetaData.
attribute sorted_tables

Returns a list of Table objects sorted in order of foreign key dependency.

The sorting will place objects that have dependencies first, before the dependencies themselves, representing the order in which they can be created. To get the order in which the tables would be dropped, use the reversed() Python built-in.

Warning

The MetaData.sorted_tables attribute cannot by itself accommodate automatic resolution of dependency cycles between tables, which are usually caused by mutually dependent foreign key constraints. When these cycles are detected, the foreign keys of these tables are omitted from consideration in the sort. A warning is emitted when this condition occurs, which will be an exception raise in a future release. Tables which are not part of the cycle will still be returned in dependency order.

To resolve these cycles, the parameter may be applied to those constraints which create a cycle. Alternatively, the sort_tables_and_constraints() function will automatically return foreign key constraints in a separate collection when cycles are detected so that they may be applied to a schema separately.

Changed in version 1.3.17: - a warning is emitted when cannot perform a proper sort due to cyclical dependencies. This will be an exception in a future release. Additionally, the sort will continue to return other tables not involved in the cycle in dependency order which was not the case previously.

See also

sort_tables()

MetaData.tables

Inspector.get_sorted_table_and_fkc_names()
attribute tables = None

A dictionary of Table objects keyed to their name or “table key”.

The exact key is that determined by the attribute; for a table with no Table.schema attribute, this is the same as . For a table with a schema, it is typically of the form schemaname.tablename.

See also

class sqlalchemy.schema.``SchemaItem

Base class for items that define a database schema.

Class signature

class sqlalchemy.schema.SchemaItem (sqlalchemy.sql.expression.SchemaEventTarget, )

attribute sqlalchemy.schema.SchemaItem.info

Info dictionary associated with the object, allowing user-defined data to be associated with this .

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as Table and .

class sqlalchemy.schema.``Table(\args, **kw*)

Represent a table in a database.

e.g.:

mytable = Table("mytable", metadata,
                Column('mytable_id', Integer, primary_key=True),
                Column('value', String(50))
           )

The Table object constructs a unique instance of itself based on its name and optional schema name within the given object. Calling the Table constructor with the same name and same argument a second time will return the same Table object - in this way the constructor acts as a registry function.

See also

Describing Databases with MetaData - Introduction to database metadata

Constructor arguments are as follows:

Parameters
- name –
  
  The name of this table as represented in the database.
  
  The table name, along with the value of the schema parameter, forms a key which uniquely identifies this within the owning MetaData collection. Additional calls to with the same name, metadata, and schema name will return the same Table object.
  
  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 or contain special characters. A name with any number of upper case characters is considered to be case sensitive, and will be sent as quoted.
  
  To enable unconditional quoting for the table name, specify the flag quote=True to the constructor, or use the construct to specify the name.
- metadata – a MetaData object which will contain this table. The metadata is used as a point of association of this table with other tables which are referenced via foreign key. It also may be used to associate this table with a particular Connectable.
- *args – Additional positional arguments are used primarily to add the list of objects contained within this table. Similar to the style of a CREATE TABLE statement, other SchemaItem constructs may be added here, including , and ForeignKeyConstraint.
- autoload –
  
  Defaults to False, unless is set in which case it defaults to True; Column objects for this table should be reflected from the database, possibly augmenting objects that were explicitly specified. and other objects explicitly set on the table will replace corresponding reflected objects.
  
  Deprecated since version 1.4: The autoload parameter is deprecated and will be removed in version 2.0. Please use the Table.autoload_with parameter, passing an engine or connection.
  
  See also
- autoload_replace –
  
  Defaults to True; when using Table.autoload in conjunction with , indicates that Column objects present in the already-existing object should be replaced with columns of the same name retrieved from the autoload process. When False, columns already present under existing names will be omitted from the reflection process.
  
  Note that this setting does not impact Column objects specified programmatically within the call to that also is autoloading; those Column objects will always replace existing columns of the same name when is True.
  
  See also
  
  Table.autoload
- autoload_with – An Engine or object, or a Inspector object as returned by against one, with which this Table object will be reflected. When set to a non-None value, the autoload process will take place for this table against the given engine or connection.
- extend_existing –
  
  When True, indicates that if this is already present in the given MetaData, apply further arguments within the constructor to the existing .
  
  If Table.extend_existing or are not set, and the given name of the new Table refers to a that is already present in the target MetaData collection, and this specifies additional columns or other constructs or flags that modify the table’s state, an error is raised. The purpose of these two mutually-exclusive flags is to specify what action should be taken when a Table is specified that matches an existing , yet specifies additional constructs.
  
  Table.extend_existing will also work in conjunction with to run a new reflection operation against the database, even if a Table of the same name is already present in the target ; newly reflected Column objects and other options will be added into the state of the , potentially overwriting existing columns and options of the same name.
  
  As is always the case with Table.autoload, objects can be specified in the same Table constructor, which will take precedence. Below, the existing table mytable will be augmented with objects both reflected from the database, as well as the given Column named “y”:
```
Table("mytable", metadata,
            Column('y', Integer),
            extend_existing=True,
            autoload_with=engine
        )
```
  See also
  
  Table.autoload_replace
- implicit_returning – True by default - indicates that RETURNING can be used by default to fetch newly inserted primary key values, for backends which support this. Note that create_engine() also provides an implicit_returning flag.
- include_columns – A list of strings indicating a subset of columns to be loaded via the autoload operation; table columns who aren’t present in this list will not be represented on the resulting Table object. Defaults to None which indicates all columns should be reflected.
- resolve_fks –
  
  Whether or not to reflect objects related to this one via ForeignKey objects, when or Table.autoload_with is specified. Defaults to True. Set to False to disable reflection of related tables as objects are encountered; may be used either to save on SQL calls or to avoid issues with related tables that can’t be accessed. Note that if a related table is already present in the MetaData collection, or becomes present later, a object associated with this Table will resolve to that table normally.
  
  New in version 1.3.
  
  See also
- info – Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.
- keep_existing –
  
  When True, indicates that if this Table is already present in the given , ignore further arguments within the constructor to the existing Table, and return the object as originally created. This is to allow a function that wishes to define a new Table on first call, but on subsequent calls will return the same , without any of the declarations (particularly constraints) being applied a second time.
  
  If Table.extend_existing or are not set, and the given name of the new Table refers to a that is already present in the target MetaData collection, and this specifies additional columns or other constructs or flags that modify the table’s state, an error is raised. The purpose of these two mutually-exclusive flags is to specify what action should be taken when a Table is specified that matches an existing , yet specifies additional constructs.
  
  See also
  
  Table.extend_existing
- listeners –
  
  A list of tuples of the form (<eventname>, <fn>) which will be passed to upon construction. This alternate hook to listen() allows the establishment of a listener function specific to this before the “autoload” process begins. Historically this has been intended for use with the DDLEvents.column_reflect() event, however note that this event hook may now be associated with the object directly:
```
def listen_for_reflect(table, column_info):
    "handle the column reflection event"
    # ...
t = Table(
    'sometable',
    autoload_with=engine,
    listeners=[
        ('column_reflect', listen_for_reflect)
    ])
```
  See also
  
  DDLEvents.column_reflect()
- must_exist – When True, indicates that this Table must already be present in the given collection, else an exception is raised.
- prefixes – A list of strings to insert after CREATE in the CREATE TABLE statement. They will be separated by spaces.
- quote – Force quoting of this table’s name on or off, corresponding to True or False. When left at its default of None, the column identifier will be quoted according to whether the name is case sensitive (identifiers with at least one upper case character are treated as case sensitive), or if it’s a reserved word. This flag is only needed to force quoting of a reserved word which is not known by the SQLAlchemy dialect.
- quote_schema – same as ‘quote’ but applies to the schema identifier.
- schema –
  
  The schema name for this table, which is required if the table resides in a schema other than the default selected schema for the engine’s database connection. Defaults to None.
  
  If the owning MetaData of this specifies its own MetaData.schema parameter, then that schema name will be applied to this if the schema parameter here is set to None. To set a blank schema name on a Table that would otherwise use the schema set on the owning , specify the special symbol BLANK_SCHEMA.
  
  New in version 1.0.14: Added the symbol to allow a Table to have a blank schema name even when the parent specifies MetaData.schema.
  
  The quoting rules for the schema name are the same as those for the name parameter, in that quoting is applied for reserved words or case-sensitive names; to enable unconditional quoting for the schema name, specify the flag quote_schema=True to the constructor, or use the construct to specify the name.
- comment –
  
  Optional string that will render an SQL comment on table creation.
  
  New in version 1.2: Added the Table.comment parameter to .
- **kw – Additional keyword arguments not mentioned above are dialect specific, and passed in the form <dialectname>_<argname>. See the documentation regarding an individual dialect at Dialects for detail on documented arguments.

Class signature

class (sqlalchemy.sql.base.DialectKWArgs, , sqlalchemy.sql.expression.TableClause)

method __init__(\args, **kw*)

Constructor for Table.

This method is a no-op. See the top-level documentation for for constructor arguments.
method sqlalchemy.schema.Table.add_is_dependent_on(table)

Add a ‘dependency’ for this Table.

This is another Table object which must be created first before this one can, or dropped after this one.

Usually, dependencies between tables are determined via ForeignKey objects. However, for other situations that create dependencies outside of foreign keys (rules, inheriting), this method can manually establish such a link.
method alias(name=None, flat=False)

inherited from the FromClause.alias() method of

Return an alias of this FromClause.

E.g.:
```
a2 = some_table.alias('a2')
```
The above code creates an object which can be used as a FROM clause in any SELECT statement.

See also

Using Aliases and Subqueries
method sqlalchemy.schema.Table.append_column(column, replace_existing=False)

Append a to this Table.

The “key” of the newly added , i.e. the value of its .key attribute, will then be available in the .c collection of this Table, and the column definition will be included in any CREATE TABLE, SELECT, UPDATE, etc. statements generated from this construct.

Note that this does not change the definition of the table as it exists within any underlying database, assuming that table has already been created in the database. Relational databases support the addition of columns to existing tables using the SQL ALTER command, which would need to be emitted for an already-existing table that doesn’t contain the newly added column.
- Parameters
  
  replace_existing –
  
  When True, allows replacing existing columns. When False, the default, an warning will be raised if a column with the same .key already exists. A future version of sqlalchemy will instead rise a warning.
  
  New in version 1.4.0.
method sqlalchemy.schema.Table.append_constraint(constraint)

Append a to this Table.

This has the effect of the constraint being included in any future CREATE TABLE statement, assuming specific DDL creation events have not been associated with the given object.

Note that this does not produce the constraint within the relational database automatically, for a table that already exists in the database. To add a constraint to an existing relational database table, the SQL ALTER command must be used. SQLAlchemy also provides the AddConstraint construct which can produce this SQL when invoked as an executable clause.
method classmethod argument_for(dialect_name, argument_name, default)

inherited from the DialectKWArgs.argument_for() method of

Add a new kind of dialect-specific keyword argument for this class.

E.g.:
```
Index.argument_for("mydialect", "length", None)
some_index = Index('a', 'b', mydialect_length=5)
```
The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.
- Parameters
  - dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.
  - argument_name – name of the parameter.
  - default – default value of the parameter.

New in version 0.9.4.

attribute bind

Return the connectable associated with this Table.
attribute sqlalchemy.schema.Table.c

inherited from the attribute of FromClause

A named-based collection of objects maintained by this FromClause.

The attribute is an alias for the FromClause.columns atttribute.
- Returns
  
  a
attribute sqlalchemy.schema.Table.columns

inherited from the attribute of FromClause

A named-based collection of objects maintained by this FromClause.

The , or c collection, is the gateway to the construction of SQL expressions using table-bound or other selectable-bound columns:
```
select(mytable).where(mytable.c.somecolumn == 5)
```
- Returns
  
  a object.
method sqlalchemy.schema.Table.compare(other, \*kw*)

inherited from the method of ClauseElement

Compare this to the given ClauseElement.

Subclasses should override the default behavior, which is a straight identity comparison.

**kw are arguments consumed by subclass compare() methods and may be used to modify the criteria for comparison (see ).
method sqlalchemy.schema.Table.compile(bind=None, dialect=None, \*kw*)

inherited from the method of ClauseElement

Compile this SQL expression.

The return value is a object. Calling str() or unicode() on the returned value will yield a string representation of the result. The Compiled object also can return a dictionary of bind parameter names and values using the params accessor.
- Parameters
  - bind – An Engine or Connection from which a Compiled will be acquired. This argument takes precedence over this ’s bound engine, if any.
  - column_keys – Used for INSERT and UPDATE statements, a list of column names which should be present in the VALUES clause of the compiled statement. If None, all columns from the target table object are rendered.
  - dialect – A Dialect instance from which a Compiled will be acquired. This argument takes precedence over the bind argument as well as this ClauseElement ‘s bound engine, if any.
  - compile_kwargs –
    
    optional dictionary of additional parameters that will be passed through to the compiler within all “visit” methods. This allows any custom flag to be passed through to a custom compilation construct, for example. It is also used for the case of passing the literal_binds flag through:
```
from sqlalchemy.sql import table, column, select
t = table('t', column('x'))
s = select(t).where(t.c.x == 5)
print(s.compile(compile_kwargs={"literal_binds": True}))
```
    New in version 0.9.0.

See also
[How do I render SQL expressions as strings, possibly with bound parameters inlined?]($23f306fd0cdd485d.md#faq-sql-expression-string)

attribute constraints = None

A collection of all Constraint objects associated with this .

Includes PrimaryKeyConstraint, , UniqueConstraint, . A separate collection Table.foreign_key_constraints refers to the collection of all objects, and the Table.primary_key attribute refers to the single associated with the Table.

See also

Table.primary_key

Table.indexes
method sqlalchemy.schema.Table.corresponding_column(column, require_embedded=False)

inherited from the method of Selectable

Given a , return the exported ColumnElement object from the collection of this Selectable which corresponds to that original via a common ancestor column.
- Parameters
  - column – the target ColumnElement to be matched.
  - require_embedded – only return corresponding columns for the given , if the given ColumnElement is actually present within a sub-element of this . Normally the column will match if it merely shares a common ancestor with one of the exported columns of this Selectable.

See also
[`Selectable.exported_columns`]($fc2d211e9d1454ca.md#sqlalchemy.sql.expression.Selectable.exported_columns "sqlalchemy.sql.expression.Selectable.exported_columns") - the [`ColumnCollection`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnCollection "sqlalchemy.sql.expression.ColumnCollection") that is used for the operation.
[`ColumnCollection.corresponding_column()`]($f62ce11674ae62ed.md#sqlalchemy.sql.expression.ColumnCollection.corresponding_column "sqlalchemy.sql.expression.ColumnCollection.corresponding_column") - implementation method.

method create(bind=None, checkfirst=False)

Issue a CREATE statement for this Table, using the given Connectable for connectivity.

Note

the “bind” argument will be required in SQLAlchemy 2.0.

See also

.
method sqlalchemy.schema.Table.delete(whereclause=None, \*kwargs*)

inherited from the method of TableClause

Generate a construct against this TableClause.

E.g.:
```
table.delete().where(table.c.id==7)
```
See for argument and usage information.
attribute sqlalchemy.schema.Table.dialect_kwargs

inherited from the attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

New in version 0.9.2.

Changed in version 0.9.4: The DialectKWArgs.dialect_kwargs collection is now writable.

See also

- nested dictionary form
attribute sqlalchemy.schema.Table.dialect_options

inherited from the attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:
```
arg = my_object.dialect_options['postgresql']['where']
```
New in version 0.9.2.

See also

- flat dictionary form
method sqlalchemy.schema.Table.drop(bind=None, checkfirst=False)

Issue a DROP statement for this , using the given Connectable for connectivity.

Note

the “bind” argument will be required in SQLAlchemy 2.0.

See also

MetaData.drop_all().
attribute entity_namespace

inherited from the FromClause.entity_namespace attribute of

Return a namespace used for name-based access in SQL expressions.

This is the namespace that is used to resolve “filter_by()” type expressions, such as:
```
stmt.filter_by(address='some address')
```
It defaults to the .c collection, however internally it can be overridden using the “entity_namespace” annotation to deliver alternative results.
method sqlalchemy.schema.Table.exists(bind=None)

Return True if this table exists.

Deprecated since version 1.4: The method is deprecated and will be removed in a future release. Please refer to Inspector.has_table().
attribute exported_columns

inherited from the FromClause.exported_columns attribute of

A ColumnCollection that represents the “exported” columns of this .

The “exported” columns for a FromClause object are synonymous with the collection.

New in version 1.4.

See also

Selectable.exported_columns
attribute sqlalchemy.schema.Table.foreign_key_constraints

objects referred to by this Table.

This list is produced from the collection of objects currently associated.

See also

Table.constraints

Table.indexes
attribute foreign_keys

inherited from the FromClause.foreign_keys attribute of

Return the collection of ForeignKey marker objects which this FromClause references.

Each is a member of a Table-wide .

See also

Table.foreign_key_constraints
method get_children(omit_attrs=(), \*kw*)

inherited from the ClauseElement.get_children() method of

Return immediate child Traversible elements of this .

This is used for visit traversal.

**kw may contain flags that change the collection that is returned, for example to return a subset of items in order to cut down on larger traversals, or to return child items from a different context (such as schema-level collections instead of clause-level).
attribute sqlalchemy.schema.Table.indexes = None

A collection of all objects associated with this Table.

See also
attribute sqlalchemy.schema.Table.info

inherited from the attribute of SchemaItem

Info dictionary associated with the object, allowing user-defined data to be associated with this .

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as Table and .
method sqlalchemy.schema.Table.insert(values=None, inline=False, \*kwargs*)

inherited from the method of TableClause

Generate an construct against this TableClause.

E.g.:
```
table.insert().values(name='foo')
```
See for argument and usage information.
method sqlalchemy.schema.Table.is_derived_from(fromclause)

inherited from the method of FromClause

Return True if this is ‘derived’ from the given FromClause.

An example would be an Alias of a Table is derived from that Table.
method sqlalchemy.schema.Table.join(right, onclause=None, isouter=False, full=False)

inherited from the method of FromClause

Return a from this FromClause to another FromClause.

E.g.:
```
from sqlalchemy import join
j = user_table.join(address_table,
                user_table.c.id == address_table.c.user_id)
stmt = select(user_table).select_from(j)
```
would emit SQL along the lines of:
```
SELECT user.id, user.name FROM user
JOIN address ON user.id = address.user_id
```
- Parameters
  - right – the right side of the join; this is any object such as a Table object, and may also be a selectable-compatible object such as an ORM-mapped class.
  - onclause – a SQL expression representing the ON clause of the join. If left at None, will attempt to join the two tables based on a foreign key relationship.
  - isouter – if True, render a LEFT OUTER JOIN, instead of JOIN.
  - full –
    
    if True, render a FULL OUTER JOIN, instead of LEFT OUTER JOIN. Implies FromClause.join.isouter.
    
    New in version 1.1.

See also
[`join()`]($fc2d211e9d1454ca.md#sqlalchemy.sql.expression.join "sqlalchemy.sql.expression.join") - standalone function
[`Join`]($fc2d211e9d1454ca.md#sqlalchemy.sql.expression.Join "sqlalchemy.sql.expression.Join") - the type of object produced

attribute key

Return the ‘key’ for this Table.

This value is used as the dictionary key within the collection. It is typically the same as that of Table.name for a table with no Table.schema set; otherwise it is typically of the form schemaname.tablename.
attribute sqlalchemy.schema.Table.kwargs

inherited from the attribute of DialectKWArgs

A synonym for .
method sqlalchemy.schema.Table.lateral(name=None)

inherited from the method of Selectable

Return a LATERAL alias of this .

The return value is the Lateral construct also provided by the top-level function.

New in version 1.1.

See also

LATERAL correlation - overview of usage.
class memoized_attribute(fget, doc=None)

A read-only @property that is only evaluated once.
method classmethod memoized_instancemethod(fn)

inherited from the HasMemoized.memoized_instancemethod() method of HasMemoized

Decorate a method memoize its return value.
method sqlalchemy.schema.Table.outerjoin(right, onclause=None, full=False)

inherited from the method of FromClause

Return a from this FromClause to another FromClause, with the “isouter” flag set to True.

E.g.:
```
from sqlalchemy import outerjoin
j = user_table.outerjoin(address_table,
                user_table.c.id == address_table.c.user_id)
```
The above is equivalent to:
```
j = user_table.join(
    address_table,
    user_table.c.id == address_table.c.user_id,
    isouter=True)
```
- Parameters
  - right – the right side of the join; this is any object such as a Table object, and may also be a selectable-compatible object such as an ORM-mapped class.
  - onclause – a SQL expression representing the ON clause of the join. If left at None, will attempt to join the two tables based on a foreign key relationship.
  - full –
    
    if True, render a FULL OUTER JOIN, instead of LEFT OUTER JOIN.
    
    New in version 1.1.

See also
[`FromClause.join()`]($fc2d211e9d1454ca.md#sqlalchemy.sql.expression.FromClause.join "sqlalchemy.sql.expression.FromClause.join")
[`Join`]($fc2d211e9d1454ca.md#sqlalchemy.sql.expression.Join "sqlalchemy.sql.expression.Join")

attribute sqlalchemy.schema.Table.primary_key

inherited from the attribute of FromClause

Return the iterable collection of objects which comprise the primary key of this _selectable.FromClause.

For a Table object, this collection is represented by the which itself is an iterable collection of Column objects.
method replace_selectable(old, alias)

inherited from the Selectable.replace_selectable() method of

Replace all occurrences of FromClause ‘old’ with the given object, returning a copy of this FromClause.

Deprecated since version 1.4: The method is deprecated, and will be removed in a future release. Similar functionality is available via the sqlalchemy.sql.visitors module.
method sqlalchemy.schema.Table.select(whereclause=None, \*kwargs*)

inherited from the method of FromClause

Return a SELECT of this .

e.g.:
```
stmt = some_table.select().where(some_table.c.id == 5)
```
- Parameters
  - whereclause –
    
    a WHERE clause, equivalent to calling the Select.where() method.
    
    Deprecated since version 1.4: The parameter is deprecated and will be removed in version 2.0. Please make use of the Select.where() method to add WHERE criteria to the SELECT statement.
  - **kwargs – additional keyword arguments are passed to the legacy constructor for described at Select.create_legacy_select().

See also
[`select()`]($fc2d211e9d1454ca.md#sqlalchemy.sql.expression.select "sqlalchemy.sql.expression.select") - general purpose method which allows for arbitrary column lists.

method self_group(against=None)

inherited from the ClauseElement.self_group() method of

Apply a ‘grouping’ to this ClauseElement.

This method is overridden by subclasses to return a “grouping” construct, i.e. parenthesis. In particular it’s used by “binary” expressions to provide a grouping around themselves when placed into a larger expression, as well as by constructs when placed into the FROM clause of another select(). (Note that subqueries should be normally created using the method, as many platforms require nested SELECT statements to be named).

As expressions are composed together, the application of self_group() is automatic - end-user code should never need to use this method directly. Note that SQLAlchemy’s clause constructs take operator precedence into account - so parenthesis might not be needed, for example, in an expression like x OR (y AND z) - AND takes precedence over OR.

The base method of ClauseElement just returns self.
method table_valued()

inherited from the FromClause.table_valued() method of

Return a TableValuedColumn object for this FromClause.

A TableValuedColumn is a that represents a complete row in a table. Support for this construct is backend dependent, and is supported in various forms by backends such as PostgreSQL, Oracle and SQL Server.

E.g.:
```
>>> from sqlalchemy import select, column, func, table
>>> a = table("a", column("id"), column("x"), column("y"))
>>> stmt = select(func.row_to_json(a.table_valued()))
>>> print(stmt)
SELECT row_to_json(a) AS row_to_json_1
FROM a
```
New in version 1.4.0b2.

See also

Working with SQL Functions - in the
method sqlalchemy.schema.Table.tablesample(sampling, name=None, seed=None)

inherited from the method of FromClause

Return a TABLESAMPLE alias of this .

The return value is the TableSample construct also provided by the top-level function.

New in version 1.1.

See also

tablesample() - usage guidelines and parameters
method to_metadata(metadata, schema=symbol(‘retain_schema’), referred_schema_fn=None, name=None)

Return a copy of this Table associated with a different .

E.g.:
```
m1 = MetaData()
user = Table('user', m1, Column('id', Integer, primary_key=True))
m2 = MetaData()
user_copy = user.to_metadata(m2)
```
Changed in version 1.4: The Table.to_metadata() function was renamed from .
- Parameters
  - metadata – Target MetaData object, into which the new object will be created.
  - schema –
    
    optional string name indicating the target schema. Defaults to the special symbol RETAIN_SCHEMA which indicates that no change to the schema name should be made in the new Table. If set to a string name, the new will have this new name as the .schema. If set to None, the schema will be set to that of the schema set on the target MetaData, which is typically None as well, unless set explicitly:
```
m2 = MetaData(schema='newschema')
# user_copy_one will have "newschema" as the schema name
user_copy_one = user.to_metadata(m2, schema=None)
m3 = MetaData()  # schema defaults to None
# user_copy_two will have None as the schema name
user_copy_two = user.to_metadata(m3, schema=None)
```
  - referred_schema_fn –
    
    optional callable which can be supplied in order to provide for the schema name that should be assigned to the referenced table of a . The callable accepts this parent Table, the target schema that we are changing to, the object, and the existing “target schema” of that constraint. The function should return the string schema name that should be applied. E.g.:
```
def referred_schema_fn(table, to_schema,
                                constraint, referred_schema):
    if referred_schema == 'base_tables':
        return referred_schema
    else:
        return to_schema
new_table = table.to_metadata(m2, schema="alt_schema",
                        referred_schema_fn=referred_schema_fn)
```
    New in version 0.9.2.
  - name –
    
    optional string name indicating the target table name. If not specified or None, the table name is retained. This allows a Table to be copied to the same target with a new name.
    
    New in version 1.0.0.

method sqlalchemy.schema.Table.tometadata(metadata, schema=symbol(‘retain_schema’), referred_schema_fn=None, name=None)

Return a copy of this associated with a different MetaData.

Deprecated since version 1.4: is renamed to Table.to_metadata()

See for a full description.
method sqlalchemy.schema.Table.update(whereclause=None, values=None, inline=False, \*kwargs*)

inherited from the method of TableClause

Generate an construct against this TableClause.

E.g.:

See for argument and usage information.

class sqlalchemy.schema.``ThreadLocalMetaData

A MetaData variant that presents a different bind in every thread.

Deprecated since version 1.4: ThreadLocalMetaData is deprecated and will be removed in a future release.

Makes the bind property of the MetaData a thread-local value, allowing this collection of tables to be bound to different Engine implementations or connections in each thread.

The ThreadLocalMetaData starts off bound to None in each thread. Binds must be made explicitly by assigning to the bind property or using connect(). You can also re-bind dynamically multiple times per thread, just like a regular MetaData.

Class signature

class (sqlalchemy.schema.MetaData)

method __init__()

Construct a ThreadLocalMetaData.
attribute sqlalchemy.schema.ThreadLocalMetaData.bind

The bound Engine or Connection for this thread.

This property may be assigned an Engine or Connection, or assigned a string or URL to automatically create a basic Engine for this bind with create_engine().
method dispose()

Dispose all bound engines, in all thread contexts.