SQLAlchemy 1.4 Documentation
SQLAlchemy Core
- SQL Expression Language Tutorial (1.x API)
- SQL Statements and Expressions API
- Schema Definition Language
- Describing Databases with MetaData¶
- Accessing Tables and Columns
- Creating and Dropping Database Tables
- Altering Database Objects through Migrations
- Specifying the Schema Name
- Backend-Specific Options
- Column, Table, MetaData API
Column
Column.__eq__()
Column.__init__()
Column.__le__()
Column.__lt__()
Column.__ne__()
Column.all_()
Column.anon_key_label
Column.anon_label
Column.any_()
Column.argument_for()
Column.asc()
Column.between()
Column.bool_op()
Column.cast()
Column.collate()
Column.compare()
Column.compile()
Column.concat()
Column.contains()
Column.copy()
Column.desc()
Column.dialect_kwargs
Column.dialect_options
Column.distinct()
Column.endswith()
Column.expression
Column.foreign_keys
Column.get_children()
Column.ilike()
Column.in_()
Column.index
Column.info
Column.inherit_cache
Column.is_()
Column.is_distinct_from()
Column.is_not()
Column.is_not_distinct_from()
Column.isnot()
Column.isnot_distinct_from()
Column.key
Column.kwargs
Column.label()
Column.like()
Column.match()
Column.memoized_instancemethod()
Column.not_ilike()
Column.not_in()
Column.not_like()
Column.notilike()
Column.notin_()
Column.notlike()
Column.nulls_first()
Column.nulls_last()
Column.nullsfirst()
Column.nullslast()
Column.op()
Column.operate()
Column.params()
Column.references()
Column.regexp_match()
Column.regexp_replace()
Column.reverse_operate()
Column.self_group()
Column.shares_lineage()
Column.startswith()
Column.timetuple
Column.unique
Column.unique_params()
MetaData
SchemaItem
Table
Table.__init__()
Table.add_is_dependent_on()
Table.alias()
Table.append_column()
Table.append_constraint()
Table.argument_for()
Table.bind
Table.c
Table.columns
Table.compare()
Table.compile()
Table.constraints
Table.corresponding_column()
Table.create()
Table.delete()
Table.description
Table.dialect_kwargs
Table.dialect_options
Table.drop()
Table.entity_namespace
Table.exists()
Table.exported_columns
Table.foreign_key_constraints
Table.foreign_keys
Table.get_children()
Table.implicit_returning
Table.indexes
Table.info
Table.inherit_cache
Table.insert()
Table.is_derived_from()
Table.join()
Table.key
Table.kwargs
Table.lateral()
Table.memoized_instancemethod()
Table.outerjoin()
Table.params()
Table.primary_key
Table.replace_selectable()
Table.schema
Table.select()
Table.self_group()
Table.table_valued()
Table.tablesample()
Table.to_metadata()
Table.tometadata()
Table.unique_params()
Table.update()
ThreadLocalMetaData
- Reflecting Database Objects
- Column INSERT/UPDATE Defaults
- Defining Constraints and Indexes
- Customizing DDL
- Describing Databases with MetaData¶
- SQL Datatype Objects
- Engine and Connection Use
- Core API Basics
- SQLAlchemy 2.0 Future (Core)
Project Versions
- Previous: Schema Definition Language
- Next: Reflecting Database Objects
- Up: Home
- On this page:
- Describing Databases with MetaData
- Accessing Tables and Columns
- Creating and Dropping Database Tables
- Altering Database Objects through Migrations
- Specifying the Schema Name
- Backend-Specific Options
- Column, Table, MetaData API
Column
Column.__eq__()
Column.__init__()
Column.__le__()
Column.__lt__()
Column.__ne__()
Column.all_()
Column.anon_key_label
Column.anon_label
Column.any_()
Column.argument_for()
Column.asc()
Column.between()
Column.bool_op()
Column.cast()
Column.collate()
Column.compare()
Column.compile()
Column.concat()
Column.contains()
Column.copy()
Column.desc()
Column.dialect_kwargs
Column.dialect_options
Column.distinct()
Column.endswith()
Column.expression
Column.foreign_keys
Column.get_children()
Column.ilike()
Column.in_()
Column.index
Column.info
Column.inherit_cache
Column.is_()
Column.is_distinct_from()
Column.is_not()
Column.is_not_distinct_from()
Column.isnot()
Column.isnot_distinct_from()
Column.key
Column.kwargs
Column.label()
Column.like()
Column.match()
Column.memoized_instancemethod()
Column.not_ilike()
Column.not_in()
Column.not_like()
Column.notilike()
Column.notin_()
Column.notlike()
Column.nulls_first()
Column.nulls_last()
Column.nullsfirst()
Column.nullslast()
Column.op()
Column.operate()
Column.params()
Column.references()
Column.regexp_match()
Column.regexp_replace()
Column.reverse_operate()
Column.self_group()
Column.shares_lineage()
Column.startswith()
Column.timetuple
Column.unique
Column.unique_params()
MetaData
SchemaItem
Table
Table.__init__()
Table.add_is_dependent_on()
Table.alias()
Table.append_column()
Table.append_constraint()
Table.argument_for()
Table.bind
Table.c
Table.columns
Table.compare()
Table.compile()
Table.constraints
Table.corresponding_column()
Table.create()
Table.delete()
Table.description
Table.dialect_kwargs
Table.dialect_options
Table.drop()
Table.entity_namespace
Table.exists()
Table.exported_columns
Table.foreign_key_constraints
Table.foreign_keys
Table.get_children()
Table.implicit_returning
Table.indexes
Table.info
Table.inherit_cache
Table.insert()
Table.is_derived_from()
Table.join()
Table.key
Table.kwargs
Table.lateral()
Table.memoized_instancemethod()
Table.outerjoin()
Table.params()
Table.primary_key
Table.replace_selectable()
Table.schema
Table.select()
Table.self_group()
Table.table_valued()
Table.tablesample()
Table.to_metadata()
Table.tometadata()
Table.unique_params()
Table.update()
ThreadLocalMetaData
Describing Databases with MetaData¶
This section discusses the fundamental Table
, Column
and MetaData
objects.
See also
Working with Database Metadata - tutorial introduction to SQLAlchemy’s database metadata concept in the SQLAlchemy 1.4 / 2.0 Tutorial
A collection of metadata entities is stored in an object aptly named
MetaData
:
from sqlalchemy import MetaData
metadata_obj = MetaData()
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
MetaData
object which it will be associated with.
The remaining positional arguments are mostly
Column
objects describing each column:
from sqlalchemy import Table, Column, Integer, String
user = Table(
"user",
metadata_obj,
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 Integer
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 SQL Datatype Objects.
Accessing Tables and Columns¶
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 Table
object in order of foreign key
dependency (that is, each table is preceded by all tables which it
references):
>>> for t in metadata_obj.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
Table
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_obj,
Column("employee_id", Integer, primary_key=True),
Column("employee_name", String(60), nullable=False),
Column("employee_dept", Integer, ForeignKey("departments.department_id")),
)
Note the ForeignKey
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
Tip
The FromClause.c
collection, synonymous with the
FromClause.columns
collection, is an instance of
ColumnCollection
, which provides a dictionary-like interface
to the collection of columns. Names are ordinarily accessed like
attribute names, e.g. employees.c.employee_name
. However for special names
with spaces or those that match the names of dictionary methods such as
ColumnCollection.keys()
or ColumnCollection.values()
,
indexed access must be used, such as employees.c['values']
or
employees.c["some column"]
. See ColumnCollection
for
further information.
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
create_all()
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:
engine = create_engine("sqlite:///:memory:")
metadata_obj = MetaData()
user = Table(
"user",
metadata_obj,
Column("user_id", Integer, primary_key=True),
Column("user_name", String(16), nullable=False),
Column("email_address", String(60), key="email"),
Column("nickname", String(50), nullable=False),
)
user_prefs = Table(
"user_prefs",
metadata_obj,
Column("pref_id", Integer, primary_key=True),
Column("user_id", Integer, ForeignKey("user.user_id"), nullable=False),
Column("pref_name", String(40), nullable=False),
Column("pref_value", String(100)),
)
sqlmetadata_obj.create_all(engine)
PRAGMA table_info(user){}
CREATE TABLE user(
user_id INTEGER NOT NULL PRIMARY KEY,
user_name VARCHAR(16) NOT NULL,
email_address VARCHAR(60),
nickname VARCHAR(50) NOT NULL
)
PRAGMA table_info(user_prefs){}
CREATE TABLE user_prefs(
pref_id INTEGER NOT NULL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES user(user_id),
pref_name VARCHAR(40) NOT NULL,
pref_value VARCHAR(100)
)
create_all()
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 create_all()
- 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:")
metadata_obj = MetaData()
employees = Table(
"employees",
metadata_obj,
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)
Altering Database Objects through Migrations¶
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 text()
construct to
Connection.execute()
or by using the DDL
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 SQLAlchemy-Migrate 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 Table
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 Table.schema
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 MetaData
object where it will take effect automatically for all Table
objects associated with that MetaData
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
Explicit Schema Name with Declarative Table - schema name specification when using the ORM declarative table configuration
The most basic example is that of the Table.schema
argument
using a Core Table
object as follows:
metadata_obj = MetaData()
financial_info = Table(
"financial_info",
metadata_obj,
Column("id", Integer, primary_key=True),
Column("value", String(100), nullable=False),
schema="remote_banks",
)
SQL that is rendered using this Table
, 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 MetaData
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_obj.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 ForeignKey
or ForeignKeyConstraint
objects, even if the referring table is also in that same schema:
customer = Table(
"customer",
metadata_obj,
Column("id", Integer, primary_key=True),
Column("financial_info_id", ForeignKey("remote_banks.financial_info.id")),
schema="remote_banks",
)
The Table.schema
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.
Specifying a Default Schema Name with MetaData¶
The MetaData
object may also set up an explicit default
option for all Table.schema
parameters by passing the
MetaData.schema
argument to the top level MetaData
construct:
metadata_obj = MetaData(schema="remote_banks")
financial_info = Table(
"financial_info",
metadata_obj,
Column("id", Integer, primary_key=True),
Column("value", String(100), nullable=False),
)
Above, for any Table
object (or Sequence
object
directly associated with the MetaData
) 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 Table
is cataloged in the MetaData
using the schema-qualified name, that is:
metadata_obj.tables["remote_banks.financial_info"]
When using the ForeignKey
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_obj,
Column("id", Integer, primary_key=True),
Column("fiid", ForeignKey("financial_info.id")),
)
# or
refers_to_financial_info = Table(
"refers_to_financial_info",
metadata_obj,
Column("id", Integer, primary_key=True),
Column("fiid", ForeignKey("remote_banks.financial_info.id")),
)
When using a MetaData
object that sets
MetaData.schema
, a Table
that wishes
to specify that it should not be schema qualified may use the special symbol
BLANK_SCHEMA
:
from sqlalchemy import BLANK_SCHEMA
metadata_obj = MetaData(schema="remote_banks")
financial_info = Table(
"financial_info",
metadata_obj,
Column("id", Integer, primary_key=True),
Column("value", String(100), nullable=False),
schema=BLANK_SCHEMA, # will not use "remote_banks"
)
See also
Applying Dynamic Schema Naming Conventions¶
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 Translation of Schema Names describes how this feature is used.
See also
Setting a Default Schema for New Connections¶
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_obj = dbapi_connection.cursor()
cursor_obj.execute("ALTER SESSION SET CURRENT_SCHEMA=%s" % schema_name)
cursor_obj.close()
Above, the set_current_schema()
event handler will take place immediately
when the above Engine
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 PostgreSQL dialect documentation.
Schemas and Reflection¶
The schema feature of SQLAlchemy interacts with the table reflection feature introduced at Reflecting Database Objects. See the section Reflecting Tables from Other Schemas for additional details on how this works.
Backend-Specific Options¶
Table
supports database-specific options. For
example, MySQL has different table backend types, including “MyISAM” and
“InnoDB”. This can be expressed with Table
using
mysql_engine
:
addresses = Table(
"engine_email_addresses",
metadata_obj,
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¶
Object Name | Description |
---|---|
Represents a column in a database table. |
|
A collection of |
|
Base class for items that define a database schema. |
|
Represent a table in a database. |
|
A MetaData variant that presents a different |
-
attribute
sqlalchemy.schema.sqlalchemy.schema.
sqlalchemy.schema.BLANK_SCHEMA Symbol indicating that a
Table
orSequence
should have ‘None’ for its schema, even if the parentMetaData
has specified a schema.New in version 1.0.14.
-
attribute
sqlalchemy.schema.sqlalchemy.schema.
sqlalchemy.schema.RETAIN_SCHEMA Symbol indicating that a
Table
,Sequence
or in some cases aForeignKey
object, in situations where the object is being copied for aTable.to_metadata()
operation, should retain the schema name that it already has.
- class sqlalchemy.schema.Column(*args, **kwargs)¶
Represents a column in a database table.
Members
__eq__(), __init__(), __le__(), __lt__(), __ne__(), all_(), anon_key_label, anon_label, any_(), argument_for(), asc(), between(), bool_op(), cast(), collate(), compare(), compile(), concat(), contains(), copy(), desc(), dialect_kwargs, dialect_options, distinct(), endswith(), expression, foreign_keys, get_children(), ilike(), in_(), index, info, inherit_cache, is_(), is_distinct_from(), is_not(), is_not_distinct_from(), isnot(), isnot_distinct_from(), key, kwargs, label(), like(), match(), memoized_instancemethod(), not_ilike(), not_in(), not_like(), notilike(), notin_(), notlike(), nulls_first(), nulls_last(), nullsfirst(), nullslast(), op(), operate(), params(), references(), regexp_match(), regexp_replace(), reverse_operate(), self_group(), shares_lineage(), startswith(), timetuple, unique, unique_params()
Class signature
class
sqlalchemy.schema.Column
(sqlalchemy.sql.base.DialectKWArgs
,sqlalchemy.schema.SchemaItem
,sqlalchemy.sql.expression.ColumnClause
)-
method
sqlalchemy.schema.Column.
__eq__(other)¶ inherited from the
sqlalchemy.sql.expression.ColumnOperators.__eq__
method ofColumnOperators
Implement the
==
operator.In a column context, produces the clause
a = b
. If the target isNone
, producesa 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
Table
. This is to support convenient usage within thedeclarative
extension.type_¶ –
The column’s type, indicated using an instance which subclasses
TypeEngine
. If no arguments are required for the type, the class of the type can be sent as well, e.g.:# use a type with arguments Column('data', String(50)) # use no arguments Column('level', Integer)
The
type
argument may be the second positional argument or specified by keyword.If the
type
isNone
or is omitted, it will first default to the special typeNullType
. If and when thisColumn
is made to refer to another column usingForeignKey
and/orForeignKeyConstraint
, 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 remoteColumn
object.Changed in version 0.9.0: Support for propagation of type to a
Column
from itsForeignKey
object has been improved and should be more reliable and timely.*args¶ – Additional positional arguments include various
SchemaItem
derived constructs which will be applied as options to the column. These include instances ofConstraint
,ForeignKey
,ColumnDefault
,Sequence
,Computed
Identity
. In some cases an equivalent keyword argument is available such asserver_default
,default
andunique
.autoincrement¶ –
Set up “auto increment” semantics for an integer primary key column with no foreign key dependencies (see later in this docstring for a more specific definition). This may influence the DDL that will be emitted for this column during a table create, as well as how the column will be considered when INSERT statements are compiled and executed.
The default value is the string
"auto"
, which indicates that a single-column (i.e. non-composite) primary key that is of an INTEGER type with no other client-side or server-side default constructs indicated should receive auto increment semantics automatically. Other values includeTrue
(force this column to have auto-increment semantics for a composite primary key as well),False
(this column should never have auto-increment semantics), and the string"ignore_fk"
(special-case for foreign key columns, see below).The term “auto increment semantics” refers both to the kind of DDL that will be emitted for the column within a CREATE TABLE statement, when methods such as
MetaData.create_all()
andTable.create()
are invoked, as well as how the column will be considered when an INSERT statement is compiled and emitted to the database:DDL rendering (i.e.
MetaData.create_all()
,Table.create()
): When used on aColumn
that has no other default-generating construct associated with it (such as aSequence
orIdentity
construct), the parameter will imply that database-specific keywords such as PostgreSQLSERIAL
, MySQLAUTO_INCREMENT
, orIDENTITY
on SQL Server should also be rendered. Not every database backend has an “implied” default generator available; for example the Oracle backend always needs an explicit construct such asIdentity
to be included with aColumn
in order for the DDL rendered to include auto-generating constructs to also be produced in the database.INSERT semantics (i.e. when a
insert()
construct is compiled into a SQL string and is then executed on a database usingConnection.execute()
or equivalent): A single-row INSERT statement will be known to produce a new integer primary key value automatically for this column, which will be accessible after the statement is invoked via theCursorResult.inserted_primary_key
attribute upon theResult
object. This also applies towards use of the ORM when ORM-mapped objects are persisted to the database, indicating that a new integer primary key will be available to become part of the identity key for that object. This behavior takes place regardless of what DDL constructs are associated with theColumn
and is independent of the “DDL Rendering” behavior discussed in the previous note above.
The parameter may be set to
True
to indicate that a column which is part of a composite (i.e. 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 toTrue
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 toFalse
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 aColumn
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
ForeignKey
, unless the value is specified as'ignore_fk'
:# turn on autoincrement for this column despite # the ForeignKey() Column('id', ForeignKey('other.id'), primary_key=True, autoincrement='ignore_fk')
It is typically not desirable to have “autoincrement” enabled on a column that refers to another via foreign key, as such a column is required to refer to a value that originates from elsewhere.
The setting has these effects on columns that meet the above criteria:
DDL issued for the column, if the column does not already include a default generating construct supported by the backend such as
Identity
, will include database-specific keywords intended to signify this column as an “autoincrement” column for specific backends. Behavior for primary SQLAlchemy dialects includes:AUTO INCREMENT on MySQL and MariaDB
SERIAL on PostgreSQL
IDENTITY on MS-SQL - this occurs even without the
Identity
construct as theColumn.autoincrement
parameter pre-dates this construct.SQLite - SQLite integer primary key columns are implicitly “auto incrementing” and no additional keywords are rendered; to render the special SQLite keyword
AUTOINCREMENT
is not included as this is unnecessary and not recommended by the database vendor. See the section SQLite Auto Incrementing Behavior for more background.Oracle - The Oracle dialect has no default “autoincrement” feature available at this time, instead the
Identity
construct is recommended to achieve this (theSequence
construct may also be used).Third-party dialects - consult those dialects’ documentation for details on their specific behaviors.
When a single-row
insert()
construct is compiled and executed, which does not set theInsert.inline()
modifier, newly generated primary key values for this column will be automatically retrieved upon statement execution using a method specific to the database driver in use:MySQL, SQLite - calling upon
cursor.lastrowid()
(see https://www.python.org/dev/peps/pep-0249/#lastrowid)PostgreSQL, SQL Server, Oracle - use RETURNING or an equivalent construct when rendering an INSERT statement, and then retrieving the newly generated primary key values after execution
PostgreSQL, Oracle for
Table
objects that setTable.implicit_returning
to False - for aSequence
only, theSequence
is invoked explicitly before the INSERT statement takes place so that the newly generated primary key value is available to the clientSQL Server for
Table
objects that setTable.implicit_returning
to False - theSELECT scope_identity()
construct is used after the INSERT statement is invoked to retrieve the newly generated primary key value.Third-party dialects - consult those dialects’ documentation for details on their specific behaviors.
For multiple-row
insert()
constructs invoked with a list of parameters (i.e. “executemany” semantics), primary-key retrieving behaviors are generally disabled, however there may be special APIs that may be used to retrieve lists of new primary key values for an “executemany”, such as the psycopg2 “fast insertmany” feature. Such features are very new and may not yet be well covered in documentation.
default¶ –
A scalar, Python callable, or
ColumnElement
expression representing the default value for this column, which will be invoked upon insert if this column is otherwise not specified in the VALUES clause of the insert. This is a shortcut to usingColumnDefault
as a positional argument; see that class for full detail on the structure of the argument.Contrast this argument to
Column.server_default
which creates a default generator on the database side.See also
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
Column.comment
parameter for this purpose.key¶ – An optional string identifier which will identify this
Column
object on theTable
. When a key is provided, this is the only identifier referencing theColumn
within the application, including ORM attribute mapping; thename
field is used only when rendering SQL.index¶ –
When
True
, indicates that aIndex
construct will be automatically generated for thisColumn
, which will result in a “CREATE INDEX” statement being emitted for theTable
when the DDL create operation is invoked.Using this flag is equivalent to making use of the
Index
construct explicitly at the level of theTable
construct itself:Table( "some_table", metadata, Column("x", Integer), Index("ix_some_table_x", "x") )
To add the
Index.unique
flag to theIndex
, set both theColumn.unique
andColumn.index
flags to True simultaneously, which will have the effect of rendering the “CREATE UNIQUE INDEX” DDL instruction instead of “CREATE INDEX”.The name of the index is generated using the default naming convention which for the
Index
construct is of the formix_<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
Column.index
attribute onColumn
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 theTable.indexes
collection or useInspector.get_indexes()
.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. WhenTrue
, 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 toTrue
unlessColumn.primary_key
is alsoTrue
or the column specifies aIdentity
, in which case it defaults toFalse
. 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 toTrue
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 usingColumnDefault
as a positional argument withfor_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 aTable
can be specified via an explicitPrimaryKeyConstraint
object.server_default¶ –
A
FetchedValue
instance, str, Unicode ortext()
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
text()
expression will be rendered as-is, without quotes:Column('y', DateTime, server_default=text('NOW()')) y DATETIME DEFAULT NOW()
Strings and text() will be converted into a
DefaultClause
object upon initialization.This parameter can also accept complex combinations of contextually valid SQLAlchemy expressions or constructs:
from sqlalchemy import create_engine from sqlalchemy import Table, Column, MetaData, ARRAY, Text from sqlalchemy.dialects.postgresql import array engine = create_engine( 'postgresql://scott:tiger@localhost/mydatabase' ) metadata_obj = MetaData() tbl = Table( "foo", metadata_obj, Column("bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"]) ) ) metadata_obj.create_all(engine)
The above results in a table created with the following SQL:
CREATE TABLE foo ( bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash'] )
Use
FetchedValue
to indicate that an already-existing column will generate a default value on the database side which will be available to SQLAlchemy for post-fetch after inserts. This construct does not specify any DDL and the implementation is left to the database, such as via a trigger.See also
Server-invoked DDL-Explicit Default Expressions - complete discussion of server side defaults
server_onupdate¶ –
A
FetchedValue
instance representing a database-side default generation function, such as a trigger. This indicates to SQLAlchemy that a newly generated value will be available after updates. This construct does not actually implement any kind of generation function within the database, which instead must be specified separately.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.
quote¶ – Force quoting of this column’s name on or off, corresponding to
True
orFalse
. When left at its default ofNone
, 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 theColumn.index
parameter is left at its default value ofFalse
, indicates that aUniqueConstraint
construct will be automatically generated for thisColumn
, which will result in a “UNIQUE CONSTRAINT” clause referring to this column being included in theCREATE TABLE
statement emitted, when the DDL create operation for theTable
object is invoked.When this flag is
True
while theColumn.index
parameter is simultaneously set toTrue
, the effect instead is that aIndex
construct which includes theIndex.unique
parameter set toTrue
is generated. See the documentation forColumn.index
for additional detail.Using this flag is equivalent to making use of the
UniqueConstraint
construct explicitly at the level of theTable
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 ofNone
; in the absence of a naming convention for the enclosingMetaData
, 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
UniqueConstraint
construct should be preferred for most use cases, including composite constraints that encompass more than one column, backend-specific index configuration options, and constraints that use a specific name.Note
the
Column.unique
attribute onColumn
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 theTable.indexes
and/orTable.constraints
collections or useInspector.get_indexes()
and/orInspector.get_unique_constraints()
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 aCREATE TABLE
statement.For more elaborate scenarios where columns should be conditionally rendered differently on different backends, consider custom compilation rules for
CreateColumn
.comment¶ –
Optional string that will render an SQL comment on table creation.
New in version 1.2: Added the
Column.comment
parameter toColumn
.
-
method
sqlalchemy.schema.Column.
__le__(other)¶ inherited from the
sqlalchemy.sql.expression.ColumnOperators.__le__
method ofColumnOperators
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 ofColumnOperators
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 ofColumnOperators
Implement the
!=
operator.In a column context, produces the clause
a != b
. If the target isNone
, producesa IS NOT NULL
.
-
method
sqlalchemy.schema.Column.
all_()¶ inherited from the
ColumnOperators.all_()
method ofColumnOperators
Produce an
all_()
clause against the parent object.See the documentation for
all_()
for examples.Note
be sure to not confuse the newer
ColumnOperators.all_()
method with its olderARRAY
-specific counterpart, theComparator.all()
method, which a different calling syntax and usage pattern.New in version 1.1.
-
attribute
sqlalchemy.schema.Column.
anon_key_label¶ inherited from the
ColumnElement.anon_key_label
attribute ofColumnElement
Deprecated since version 1.4: The
ColumnElement.anon_key_label
attribute is now private, and the public accessor is deprecated.
-
attribute
sqlalchemy.schema.Column.
anon_label¶ inherited from the
ColumnElement.anon_label
attribute ofColumnElement
Deprecated since version 1.4: The
ColumnElement.anon_label
attribute is now private, and the public accessor is deprecated.
-
method
sqlalchemy.schema.Column.
any_()¶ inherited from the
ColumnOperators.any_()
method ofColumnOperators
Produce an
any_()
clause against the parent object.See the documentation for
any_()
for examples.Note
be sure to not confuse the newer
ColumnOperators.any_()
method with its olderARRAY
-specific counterpart, theComparator.any()
method, which a different calling syntax and usage pattern.New in version 1.1.
-
classmethod
sqlalchemy.schema.Column.
argument_for(dialect_name, argument_name, default)¶ inherited from the
DialectKWArgs.argument_for()
method ofDialectKWArgs
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 theDefaultDialect.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
NoSuchModuleError
is raised. The dialect must also include an existingDefaultDialect.construct_arguments
collection, indicating that it participates in the keyword-argument validation and default system, elseArgumentError
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
ColumnOperators.asc()
method ofColumnOperators
Produce a
asc()
clause against the parent object.
-
method
sqlalchemy.schema.Column.
between(cleft, cright, symmetric=False)¶ inherited from the
ColumnOperators.between()
method ofColumnOperators
Produce a
between()
clause against the parent object, given the lower and upper range.
-
method
sqlalchemy.schema.Column.
bool_op(opstring, precedence=0)¶ inherited from the
Operators.bool_op()
method ofOperators
Return a custom boolean operator.
This method is shorthand for calling
Operators.op()
and passing theOperators.op.is_comparison
flag with True. A key advantage to usingOperators.bool_op()
is that when using column constructs, the “boolean” nature of the returned expression will be present for PEP 484 purposes.See also
-
method
sqlalchemy.schema.Column.
cast(type_)¶ inherited from the
ColumnElement.cast()
method ofColumnElement
Produce a type cast, i.e.
CAST(<expression> AS <type>)
.This is a shortcut to the
cast()
function.New in version 1.0.7.
-
method
sqlalchemy.schema.Column.
collate(collation)¶ inherited from the
ColumnOperators.collate()
method ofColumnOperators
Produce a
collate()
clause against the parent object, given the collation string.See also
-
method
sqlalchemy.schema.Column.
compare(other, **kw)¶ inherited from the
ClauseElement.compare()
method ofClauseElement
Compare this
ClauseElement
to the givenClauseElement
.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 (seeColumnElement
).
-
method
sqlalchemy.schema.Column.
compile(bind=None, dialect=None, **kw)¶ inherited from the
ClauseElement.compile()
method ofClauseElement
Compile this SQL expression.
The return value is a
Compiled
object. Callingstr()
orunicode()
on the returned value will yield a string representation of the result. TheCompiled
object also can return a dictionary of bind parameter names and values using theparams
accessor.- Parameters:
bind¶ – An
Engine
orConnection
from which aCompiled
will be acquired. This argument takes precedence over thisClauseElement
’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 aCompiled
will be acquired. This argument takes precedence over the bind argument as well as thisClauseElement
‘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.
-
method
sqlalchemy.schema.Column.
concat(other)¶ inherited from the
ColumnOperators.concat()
method ofColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause
a || b
, or uses theconcat()
operator on MySQL.
-
method
sqlalchemy.schema.Column.
contains(other, **kwargs)¶ inherited from the
ColumnOperators.contains()
method ofColumnOperators
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, theColumnOperators.contains.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.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 theColumnOperators.contains.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.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
:somecolumn.contains("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.
-
method
sqlalchemy.schema.Column.
copy(**kw)¶ Deprecated since version 1.4: The
Column.copy()
method is deprecated and will be removed in a future release.
-
method
sqlalchemy.schema.Column.
desc()¶ inherited from the
ColumnOperators.desc()
method ofColumnOperators
Produce a
desc()
clause against the parent object.
-
attribute
sqlalchemy.schema.Column.
dialect_kwargs¶ inherited from the
DialectKWArgs.dialect_kwargs
attribute ofDialectKWArgs
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 theDialectKWArgs.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
DialectKWArgs.dialect_kwargs
collection is now writable.See also
DialectKWArgs.dialect_options
- nested dictionary form
-
attribute
sqlalchemy.schema.Column.
dialect_options¶ inherited from the
DialectKWArgs.dialect_options
attribute ofDialectKWArgs
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, thepostgresql_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
sqlalchemy.schema.Column.
distinct()¶ inherited from the
ColumnOperators.distinct()
method ofColumnOperators
Produce a
distinct()
clause against the parent object.
-
method
sqlalchemy.schema.Column.
endswith(other, **kwargs)¶ inherited from the
ColumnOperators.endswith()
method ofColumnOperators
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, theColumnOperators.endswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.endswith.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 theColumnOperators.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
ColumnOperators.endswith.autoescape
: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.
-
attribute
sqlalchemy.schema.Column.
expression¶ inherited from the
ColumnElement.expression
attribute ofColumnElement
Return a column expression.
Part of the inspection interface; returns self.
-
attribute
sqlalchemy.schema.Column.
foreign_keys = None¶ A collection of all
ForeignKey
marker objects associated with thisColumn
.Each object is a member of a
Table
-wideForeignKeyConstraint
.See also
-
method
sqlalchemy.schema.Column.
get_children(column_tables=False, **kw)¶ inherited from the
ColumnClause.get_children()
method ofColumnClause
Return immediate child
Traversible
elements of thisTraversible
.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
ColumnOperators.ilike()
method ofColumnOperators
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:
See also
-
method
sqlalchemy.schema.Column.
in_(other)¶ inherited from the
ColumnOperators.in_()
method ofColumnOperators
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.
bindparam()
, may be used if it includes thebindparam.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])
This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:
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
select()
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
select()
construct, or abindparam()
construct that includes thebindparam.expanding
flag set to True.
-
attribute
sqlalchemy.schema.Column.
index = None¶ The value of the
Column.index
parameter.Does not indicate if this
Column
is actually indexed or not; useTable.indexes
.See also
-
attribute
sqlalchemy.schema.Column.
info¶ inherited from the
SchemaItem.info
attribute ofSchemaItem
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
Table
andColumn
.
-
attribute
sqlalchemy.schema.Column.
inherit_cache = True¶ Indicate if this
HasCacheKey
instance should make use of the cache key generation scheme used by its immediate superclass.The attribute defaults to
None
, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value toFalse
, except that a warning is also emitted.This flag can be set to
True
on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.See also
Enabling Caching Support for Custom Constructs - General guideslines for setting the
HasCacheKey.inherit_cache
attribute for third-party or user defined SQL constructs.
-
method
sqlalchemy.schema.Column.
is_(other)¶ inherited from the
ColumnOperators.is_()
method ofColumnOperators
Implement the
IS
operator.Normally,
IS
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS
may be desirable if comparing to boolean values on certain platforms.See also
-
method
sqlalchemy.schema.Column.
is_distinct_from(other)¶ inherited from the
ColumnOperators.is_distinct_from()
method ofColumnOperators
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
ColumnOperators.is_not()
method ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.Changed in version 1.4: The
is_not()
operator is renamed fromisnot()
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
ColumnOperators.is_not_distinct_from()
method ofColumnOperators
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 fromisnot_distinct_from()
in previous releases. The previous name remains available for backwards compatibility.New in version 1.1.
-
method
sqlalchemy.schema.Column.
isnot(other)¶ inherited from the
ColumnOperators.isnot()
method ofColumnOperators
Implement the
IS NOT
operator.Normally,
IS NOT
is generated automatically when comparing to a value ofNone
, which resolves toNULL
. However, explicit usage ofIS NOT
may be desirable if comparing to boolean values on certain platforms.Changed in version 1.4: The
is_not()
operator is renamed fromisnot()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.schema.Column.
isnot_distinct_from(other)¶ inherited from the
ColumnOperators.isnot_distinct_from()
method ofColumnOperators
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 fromisnot_distinct_from()
in previous releases. The previous name remains available for backwards compatibility.New in version 1.1.
-
attribute
sqlalchemy.schema.Column.
key = None¶ inherited from the
ColumnElement.key
attribute ofColumnElement
The ‘key’ that in some circumstances refers to this object in a Python namespace.
This typically refers to the “key” of the column as present in the
.c
collection of a selectable, e.g.sometable.c["somekey"]
would return aColumn
with a.key
of “somekey”.
-
attribute
sqlalchemy.schema.Column.
kwargs¶ inherited from the
DialectKWArgs.kwargs
attribute ofDialectKWArgs
A synonym for
DialectKWArgs.dialect_kwargs
.
-
method
sqlalchemy.schema.Column.
label(name)¶ inherited from the
ColumnElement.label()
method ofColumnElement
Produce a column label, i.e.
<columnname> AS <name>
.This is a shortcut to the
label()
function.If ‘name’ is
None
, an anonymous label name will be generated.
-
method
sqlalchemy.schema.Column.
like(other, escape=None)¶ inherited from the
ColumnOperators.like()
method ofColumnOperators
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:
See also
-
method
sqlalchemy.schema.Column.
match(other, **kwargs)¶ inherited from the
ColumnOperators.match()
method ofColumnOperators
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)
See also
match
- MySQL specific construct with additional features.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.
-
classmethod
sqlalchemy.schema.Column.
memoized_instancemethod(fn)¶ inherited from the
HasMemoized.memoized_instancemethod()
method ofHasMemoized
Decorate a method memoize its return value.
-
method
sqlalchemy.schema.Column.
not_ilike(other, escape=None)¶ inherited from the
ColumnOperators.not_ilike()
method ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.Changed in version 1.4: The
not_ilike()
operator is renamed fromnotilike()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.schema.Column.
not_in(other)¶ inherited from the
ColumnOperators.not_in()
method ofColumnOperators
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. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.Changed in version 1.4: The
not_in()
operator is renamed fromnotin_()
in previous releases. The previous name remains available for backwards compatibility.Changed in version 1.2: The
ColumnOperators.in_()
andColumnOperators.not_in()
operators now produce a “static” expression for an empty IN sequence by default.See also
-
method
sqlalchemy.schema.Column.
not_like(other, escape=None)¶ inherited from the
ColumnOperators.not_like()
method ofColumnOperators
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 fromnotlike()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.schema.Column.
notilike(other, escape=None)¶ inherited from the
ColumnOperators.notilike()
method ofColumnOperators
implement the
NOT ILIKE
operator.This is equivalent to using negation with
ColumnOperators.ilike()
, i.e.~x.ilike(y)
.Changed in version 1.4: The
not_ilike()
operator is renamed fromnotilike()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.schema.Column.
notin_(other)¶ inherited from the
ColumnOperators.notin_()
method ofColumnOperators
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. Thecreate_engine.empty_in_strategy
may be used to alter this behavior.Changed in version 1.4: The
not_in()
operator is renamed fromnotin_()
in previous releases. The previous name remains available for backwards compatibility.Changed in version 1.2: The
ColumnOperators.in_()
andColumnOperators.not_in()
operators now produce a “static” expression for an empty IN sequence by default.See also
-
method
sqlalchemy.schema.Column.
notlike(other, escape=None)¶ inherited from the
ColumnOperators.notlike()
method ofColumnOperators
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 fromnotlike()
in previous releases. The previous name remains available for backwards compatibility.See also
-
method
sqlalchemy.schema.Column.
nulls_first()¶ inherited from the
ColumnOperators.nulls_first()
method ofColumnOperators
Produce a
nulls_first()
clause against the parent object.Changed in version 1.4: The
nulls_first()
operator is renamed fromnullsfirst()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.schema.Column.
nulls_last()¶ inherited from the
ColumnOperators.nulls_last()
method ofColumnOperators
Produce a
nulls_last()
clause against the parent object.Changed in version 1.4: The
nulls_last()
operator is renamed fromnullslast()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.schema.Column.
nullsfirst()¶ inherited from the
ColumnOperators.nullsfirst()
method ofColumnOperators
Produce a
nulls_first()
clause against the parent object.Changed in version 1.4: The
nulls_first()
operator is renamed fromnullsfirst()
in previous releases. The previous name remains available for backwards compatibility.
-
method
sqlalchemy.schema.Column.
nullslast()¶ inherited from the
ColumnOperators.nullslast()
method ofColumnOperators
Produce a
nulls_last()
clause against the parent object.Changed in version 1.4: The
nulls_last()
operator is renamed fromnullslast()
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
Operators.op()
method ofOperators
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 which the database is expected to apply to the operator in SQL expressions. This integer value acts as a hint for the SQL compiler to know when explicit parenthesis should be rendered around a particular operation. 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 (,
) andAS
operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.See also
I’m using op() to generate a custom operator and my parenthesis are not coming out correctly - detailed description of how the SQLAlchemy SQL compiler renders parenthesis
is_comparison¶ –
legacy; 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 is provided so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition.Using the
is_comparison
parameter is superseded by using theOperators.bool_op()
method instead; this more succinct operator sets this parameter automatically. In SQLAlchemy 2.0 it will also provide for improved typing support.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 specifyOperators.op.is_comparison
will resolve toBoolean
, and those that do not will be of the same type as the left-hand operand.
-
method
sqlalchemy.schema.Column.
operate(op, *other, **kwargs)¶ inherited from the
ColumnElement.operate()
method ofColumnElement
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 applyfunc.lower()
to the left and right side:class MyComparator(ColumnOperators): def operate(self, op, other, **kwargs): return op(func.lower(self), func.lower(other), **kwargs)
-
method
sqlalchemy.schema.Column.
params(*optionaldict, **kwargs)¶ inherited from the
Immutable.params()
method ofImmutable
Return a copy with
bindparam()
elements replaced.Returns a copy of this ClauseElement with
bindparam()
elements replaced with values taken from the given dictionary:>>> clause = column('x') + bindparam('foo') >>> print(clause.compile().params) {'foo':None} >>> print(clause.params({'foo':7}).compile().params) {'foo':7}
-
method
sqlalchemy.schema.Column.
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
ColumnOperators.regexp_match()
method ofColumnOperators
Implements a database-specific ‘regexp match’ operator.
E.g.:
stmt = select(table.c.some_column).where( table.c.some_column.regexp_match('^(b|c)') )
ColumnOperators.regexp_match()
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
orx !~ y
when negated.Oracle - renders
REGEXP_LIKE(x, y)
SQLite - uses SQLite’s
REGEXP
placeholder operator and calls into the Pythonre.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, passed as plain Python string only. These flags are backend specific. 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.
New in version 1.4.
Changed in version 1.4.48,: 2.0.18 Note that due to an implementation error, the “flags” parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the “flags” parameter, as these flags are rendered as literal inline values within SQL expressions.
See also
-
method
sqlalchemy.schema.Column.
regexp_replace(pattern, replacement, flags=None)¶ inherited from the
ColumnOperators.regexp_replace()
method ofColumnOperators
Implements a database-specific ‘regexp replace’ operator.
E.g.:
stmt = select( table.c.some_column.regexp_replace( 'b(..)', 'XY', flags='g' ) )
ColumnOperators.regexp_replace()
attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the functionREGEXP_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, passed as plain Python string only. These flags are backend specific. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern.
New in version 1.4.
Changed in version 1.4.48,: 2.0.18 Note that due to an implementation error, the “flags” parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the “flags” parameter, as these flags are rendered as literal inline values within SQL expressions.
See also
-
method
sqlalchemy.schema.Column.
reverse_operate(op, other, **kwargs)¶ inherited from the
ColumnElement.reverse_operate()
method ofColumnElement
Reverse operate on an argument.
Usage is the same as
operate()
.
-
method
sqlalchemy.schema.Column.
self_group(against=None)¶ inherited from the
ColumnElement.self_group()
method ofColumnElement
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
select()
constructs when placed into the FROM clause of anotherselect()
. (Note that subqueries should be normally created using theSelect.alias()
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 likex OR (y AND z)
- AND takes precedence over OR.The base
self_group()
method ofClauseElement
just returns self.
-
method
sqlalchemy.schema.Column.
shares_lineage(othercolumn)¶ inherited from the
ColumnElement.shares_lineage()
method ofColumnElement
Return True if the given
ColumnElement
has a common ancestor to thisColumnElement
.
-
method
sqlalchemy.schema.Column.
startswith(other, **kwargs)¶ inherited from the
ColumnOperators.startswith()
method ofColumnOperators
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, theColumnOperators.startswith.autoescape
flag may be set toTrue
to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, theColumnOperators.startswith.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 theColumnOperators.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
ColumnOperators.startswith.autoescape
: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.
-
attribute
sqlalchemy.schema.Column.
timetuple = None¶ inherited from the
ColumnOperators.timetuple
attribute ofColumnOperators
Hack, allows datetime objects to be compared on the LHS.
-
attribute
sqlalchemy.schema.Column.
unique = None¶ The value of the
Column.unique
parameter.Does not indicate if this
Column
is actually subject to a unique constraint or not; useTable.indexes
andTable.constraints
.
-
method
sqlalchemy.schema.Column.
unique_params(*optionaldict, **kwargs)¶ inherited from the
Immutable.unique_params()
method ofImmutable
Return a copy with
bindparam()
elements replaced.Same functionality as
ClauseElement.params()
, except adds unique=True to affected bind parameters so that multiple statements can be used.
-
method
- class sqlalchemy.schema.MetaData(bind=None, schema=None, quote_schema=None, naming_convention=None, info=None)¶
A collection of
Table
objects and their associated schema constructs.Holds a collection of
Table
objects as well as an optional binding to anEngine
orConnection
. If bound, theTable
objects in the collection and their columns may participate in implicit SQL execution.The
Table
objects themselves are stored in theMetaData.tables
dictionary.MetaData
is a thread-safe object for read operations. Construction of new tables within a singleMetaData
object, either explicitly or via reflection, may not be completely thread-safe.See also
Describing Databases with MetaData - Introduction to database metadata
Members
__init__(), bind, clear(), create_all(), drop_all(), is_bound(), reflect(), remove(), sorted_tables, tables
Class signature
class
sqlalchemy.schema.MetaData
(sqlalchemy.schema.SchemaItem
)-
method
sqlalchemy.schema.MetaData.
__init__(bind=None, schema=None, quote_schema=None, naming_convention=None, info=None)¶ Create a new MetaData object.
- Parameters:
bind¶ –
An Engine or Connection to bind to. May also be a string or URL instance, these are passed to
create_engine()
and thisMetaData
will be bound to the resulting engine.Deprecated since version 1.4: The
MetaData.bind
argument is deprecated and will be removed in SQLAlchemy 2.0.schema¶ –
The default schema to use for the
Table
,Sequence
, and potentially other objects associated with thisMetaData
. Defaults toNone
.See also
Specifying a Default Schema Name with MetaData - details on how the
MetaData.schema
parameter is used.quote_schema¶ – Sets the
quote_schema
flag for thoseTable
,Sequence
, and other objects which make usage of the localschema
name.info¶ –
Optional data dictionary which will be populated into the
SchemaItem.info
attribute of this object.New in version 1.0.0.
naming_convention¶ –
a dictionary referring to values which will establish default naming conventions for
Constraint
andIndex
objects, for those objects which are not given a name explicitly.The keys of this dictionary may be:
a constraint or Index class, e.g. the
UniqueConstraint
,ForeignKeyConstraint
class, theIndex
classa string mnemonic for one of the known constraint classes;
"fk"
,"pk"
,"ix"
,"ck"
,"uq"
for foreign key, primary key, index, check, and unique constraint, respectively.the string name of a user-defined “token” that can be used to define new naming tokens.
The values associated with each “constraint class” or “constraint mnemonic” key are string naming templates, such as
"uq_%(table_name)s_%(column_0_name)s"
, which describe how the name should be composed. The values associated with user-defined “token” keys should be callables of the formfn(constraint, table)
, which accepts the constraint/index object andTable
as arguments, returning a string result.The built-in names are as follows, some of which may only be available for certain types of constraint:
%(table_name)s
- the name of theTable
object associated with the constraint.%(referred_table_name)s
- the name of theTable
object associated with the referencing target of aForeignKeyConstraint
.%(column_0_name)s
- the name of theColumn
at index position “0” within the constraint.%(column_0N_name)s
- the name of allColumn
objects in order within the constraint, joined without a separator.%(column_0_N_name)s
- the name of allColumn
objects in order within the constraint, joined with an underscore as a separator.%(column_0_label)s
,%(column_0N_label)s
,%(column_0_N_label)s
- the label of either the zerothColumn
or allColumns
, separated with or without an underscore%(column_0_key)s
,%(column_0N_key)s
,%(column_0_N_key)s
- the key of either the zerothColumn
or allColumns
, separated with or without an underscore%(referred_column_0_name)s
,%(referred_column_0N_name)s
%(referred_column_0_N_name)s
,%(referred_column_0_key)s
,%(referred_column_0N_key)s
, … column tokens which render the names/keys/labels of columns that are referenced by aForeignKeyConstraint
.%(constraint_name)s
- a special key that refers to the existing name given to the constraint. When this key is present, theConstraint
object’s existing name will be replaced with one that is composed from template string that uses this token. When this token is present, it is required that theConstraint
is given an explicit name ahead of time.user-defined: any additional token may be implemented by passing it along with a
fn(constraint, table)
callable to the naming_convention dictionary.
New in version 1.3.0: - added new
%(column_0N_name)s
,%(column_0_N_name)s
, and related tokens that produce concatenations of names, keys, or labels for all columns referred to by a given constraint.See also
Configuring Constraint Naming Conventions - for detailed usage examples.
-
attribute
sqlalchemy.schema.MetaData.
bind¶ An
Engine
orConnection
to which thisMetaData
is bound.Typically, a
Engine
is assigned to this attribute so that “implicit execution” may be used, or alternatively as a means of providing engine binding information to an ORMSession
object:engine = create_engine("someurl://") metadata.bind = engine
Deprecated since version 1.4: The metadata.bind attribute, as part of the deprecated system of “implicit execution”, is itself deprecated and will be removed in SQLAlchemy 2.0.
See also
Connectionless Execution, Implicit Execution - background on “bound metadata”
-
method
sqlalchemy.schema.MetaData.
clear()¶ Clear all Table objects from this MetaData.
-
method
sqlalchemy.schema.MetaData.
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 thisMetaData
, 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 theMetaData
(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 thisMetaData
, 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 theMetaData
(others are ignored).checkfirst¶ – Defaults to True, only issue DROPs for tables confirmed to be present in the target database.
-
method
sqlalchemy.schema.MetaData.
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 thisMetaData
for any table available in the database but not yet present in theMetaData
. May be called multiple times to pick up tables recently added to the database, however no special action is taken if a table in thisMetaData
no longer exists in the database.- Parameters:
bind¶ –
A
Connectable
used to access the database; if None, uses the existing bind on thisMetaData
, 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
MetaData
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
asTable.extend_existing
.New in version 0.9.1.
autoload_replace¶ –
Passed along to each
Table
asTable.autoload_replace
.New in version 0.9.1.
resolve_fks¶ –
if True, reflect
Table
objects linked toForeignKey
objects located in eachTable
. ForMetaData.reflect()
, 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 theMetaData.reflect.only
parameter. When False,ForeignKey
objects are not followed to theTable
in which they link, however if the related table is also part of the list of tables that would be reflected in any case, theForeignKey
object will still resolve to its relatedTable
after theMetaData.reflect()
operation is complete. Defaults to True.New in version 1.3.0.
See also
**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 Dialects for detail on documented arguments.New in version 0.9.2: - Added
MetaData.reflect.**dialect_kwargs
to support dialect-level reflection options for allTable
objects reflected.
-
method
sqlalchemy.schema.MetaData.
remove(table)¶ Remove the given Table object from this MetaData.
-
attribute
sqlalchemy.schema.MetaData.
sorted_tables¶ Returns a list of
Table
objects sorted in order of foreign key dependency.The sorting will place
Table
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 thereversed()
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
ForeignKeyConstraint.use_alter
parameter may be applied to those constraints which create a cycle. Alternatively, thesort_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
MetaData.sorted_tables
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.
-
attribute
sqlalchemy.schema.MetaData.
tables = None¶ A dictionary of
Table
objects keyed to their name or “table key”.The exact key is that determined by the
Table.key
attribute; for a table with noTable.schema
attribute, this is the same asTable.name
. For a table with a schema, it is typically of the formschemaname.tablename
.See also
-
method
- class sqlalchemy.schema.SchemaItem¶
Base class for items that define a database schema.
Members
Class signature
class
sqlalchemy.schema.SchemaItem
(sqlalchemy.sql.expression.SchemaEventTarget
,sqlalchemy.sql.visitors.Traversible
)-
attribute
sqlalchemy.schema.SchemaItem.
info¶ 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
Table
andColumn
.
-
attribute
- 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 givenMetaData
object. Calling theTable
constructor with the same name and sameMetaData
argument a second time will return the sameTable
object - in this way theTable
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 thisTable
within the owningMetaData
collection. Additional calls toTable
with the same name, metadata, and schema name will return the sameTable
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 thequoted_name
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 particularConnectable
.*args¶ – Additional positional arguments are used primarily to add the list of
Column
objects contained within this table. Similar to the style of a CREATE TABLE statement, otherSchemaItem
constructs may be added here, includingPrimaryKeyConstraint
, andForeignKeyConstraint
.autoload¶ –
Defaults to
False
, unlessTable.autoload_with
is set in which case it defaults toTrue
;Column
objects for this table should be reflected from the database, possibly augmenting objects that were explicitly specified.Column
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 usingTable.autoload
in conjunction withTable.extend_existing
, indicates thatColumn
objects present in the already-existingTable
object should be replaced with columns of the same name retrieved from the autoload process. WhenFalse
, 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 toTable
that also is autoloading; thoseColumn
objects will always replace existing columns of the same name whenTable.extend_existing
isTrue
.autoload_with¶ – An
Engine
orConnection
object, or aInspector
object as returned byinspect()
against one, with which thisTable
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 thisTable
is already present in the givenMetaData
, apply further arguments within the constructor to the existingTable
.If
Table.extend_existing
orTable.keep_existing
are not set, and the given name of the newTable
refers to aTable
that is already present in the targetMetaData
collection, and thisTable
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 aTable
is specified that matches an existingTable
, yet specifies additional constructs.Table.extend_existing
will also work in conjunction withTable.autoload
to run a new reflection operation against the database, even if aTable
of the same name is already present in the targetMetaData
; newly reflectedColumn
objects and other options will be added into the state of theTable
, potentially overwriting existing columns and options of the same name.As is always the case with
Table.autoload
,Column
objects can be specified in the sameTable
constructor, which will take precedence. Below, the existing tablemytable
will be augmented withColumn
objects both reflected from the database, as well as the givenColumn
named “y”:Table("mytable", metadata, Column('y', Integer), extend_existing=True, autoload_with=engine )
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 animplicit_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 resultingTable
object. Defaults toNone
which indicates all columns should be reflected.resolve_fks¶ –
Whether or not to reflect
Table
objects related to this one viaForeignKey
objects, whenTable.autoload
orTable.autoload_with
is specified. Defaults to True. Set to False to disable reflection of related tables asForeignKey
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 theMetaData
collection, or becomes present later, aForeignKey
object associated with thisTable
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 givenMetaData
, ignore further arguments within the constructor to the existingTable
, and return theTable
object as originally created. This is to allow a function that wishes to define a newTable
on first call, but on subsequent calls will return the sameTable
, without any of the declarations (particularly constraints) being applied a second time.If
Table.extend_existing
orTable.keep_existing
are not set, and the given name of the newTable
refers to aTable
that is already present in the targetMetaData
collection, and thisTable
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 aTable
is specified that matches an existingTable
, yet specifies additional constructs.See also
listeners¶ –
A list of tuples of the form
(<eventname>, <fn>)
which will be passed tolisten()
upon construction. This alternate hook tolisten()
allows the establishment of a listener function specific to thisTable
before the “autoload” process begins. Historically this has been intended for use with theDDLEvents.column_reflect()
event, however note that this event hook may now be associated with theMetaData
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
must_exist¶ – When
True
, indicates that this Table must already be present in the givenMetaData
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
orFalse
. When left at its default ofNone
, 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.Note
setting this flag to
False
will not provide case-insensitive behavior for table reflection; table reflection will always search for a mixed-case name in a case sensitive fashion. Case insensitive names are specified in SQLAlchemy only by stating the name with all lower case characters.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 thisTable
specifies its ownMetaData.schema
parameter, then that schema name will be applied to thisTable
if the schema parameter here is set toNone
. To set a blank schema name on aTable
that would otherwise use the schema set on the owningMetaData
, specify the special symbolBLANK_SCHEMA
.New in version 1.0.14: Added the
BLANK_SCHEMA
symbol to allow aTable
to have a blank schema name even when the parentMetaData
specifiesMetaData.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 flagquote_schema=True
to the constructor, or use thequoted_name
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 toTable
.**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.
Members
__init__(), add_is_dependent_on(), alias(), append_column(), append_constraint(), argument_for(), bind, c, columns, compare(), compile(), constraints, corresponding_column(), create(), delete(), description, dialect_kwargs, dialect_options, drop(), entity_namespace, exists(), exported_columns, foreign_key_constraints, foreign_keys, get_children(), implicit_returning, indexes, info, inherit_cache, insert(), is_derived_from(), join(), key, kwargs, lateral(), memoized_instancemethod(), outerjoin(), params(), primary_key, replace_selectable(), schema, select(), self_group(), table_valued(), tablesample(), to_metadata(), tometadata(), unique_params(), update()
Class signature
class
sqlalchemy.schema.Table
(sqlalchemy.sql.base.DialectKWArgs
,sqlalchemy.schema.SchemaItem
,sqlalchemy.sql.expression.TableClause
)-
method
sqlalchemy.schema.Table.
__init__(*args, **kw)¶ Constructor for
Table
.This method is a no-op. See the top-level documentation for
Table
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
sqlalchemy.schema.Table.
alias(name=None, flat=False)¶ inherited from the
FromClause.alias()
method ofFromClause
Return an alias of this
FromClause
.E.g.:
a2 = some_table.alias('a2')
The above code creates an
Alias
object which can be used as a FROM clause in any SELECT statement.
-
method
sqlalchemy.schema.Table.
append_column(column, replace_existing=False)¶ Append a
Column
to thisTable
.The “key” of the newly added
Column
, i.e. the value of its.key
attribute, will then be available in the.c
collection of thisTable
, and the column definition will be included in any CREATE TABLE, SELECT, UPDATE, etc. statements generated from thisTable
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. WhenFalse
, 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
Constraint
to thisTable
.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
Constraint
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.
-
classmethod
sqlalchemy.schema.Table.
argument_for(dialect_name, argument_name, default)¶ inherited from the
DialectKWArgs.argument_for()
method ofDialectKWArgs
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 theDefaultDialect.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
NoSuchModuleError
is raised. The dialect must also include an existingDefaultDialect.construct_arguments
collection, indicating that it participates in the keyword-argument validation and default system, elseArgumentError
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
sqlalchemy.schema.Table.
bind¶ Return the connectable associated with this Table.
-
attribute
sqlalchemy.schema.Table.
c¶ inherited from the
FromClause.c
attribute ofFromClause
A named-based collection of
ColumnElement
objects maintained by thisFromClause
.The
FromClause.c
attribute is an alias for theFromClause.columns
attribute.- Returns:
-
attribute
sqlalchemy.schema.Table.
columns¶ inherited from the
FromClause.columns
attribute ofFromClause
A named-based collection of
ColumnElement
objects maintained by thisFromClause
.The
columns
, orc
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
ColumnCollection
object.
-
method
sqlalchemy.schema.Table.
compare(other, **kw)¶ inherited from the
ClauseElement.compare()
method ofClauseElement
Compare this
ClauseElement
to the givenClauseElement
.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 (seeColumnElement
).
-
method
sqlalchemy.schema.Table.
compile(bind=None, dialect=None, **kw)¶ inherited from the
ClauseElement.compile()
method ofClauseElement
Compile this SQL expression.
The return value is a
Compiled
object. Callingstr()
orunicode()
on the returned value will yield a string representation of the result. TheCompiled
object also can return a dictionary of bind parameter names and values using theparams
accessor.- Parameters:
bind¶ – An
Engine
orConnection
from which aCompiled
will be acquired. This argument takes precedence over thisClauseElement
’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 aCompiled
will be acquired. This argument takes precedence over the bind argument as well as thisClauseElement
‘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.
-
attribute
sqlalchemy.schema.Table.
constraints = None¶ A collection of all
Constraint
objects associated with thisTable
.Includes
PrimaryKeyConstraint
,ForeignKeyConstraint
,UniqueConstraint
,CheckConstraint
. A separate collectionTable.foreign_key_constraints
refers to the collection of allForeignKeyConstraint
objects, and theTable.primary_key
attribute refers to the singlePrimaryKeyConstraint
associated with theTable
.
-
method
sqlalchemy.schema.Table.
corresponding_column(column, require_embedded=False)¶ inherited from the
Selectable.corresponding_column()
method ofSelectable
Given a
ColumnElement
, return the exportedColumnElement
object from theSelectable.exported_columns
collection of thisSelectable
which corresponds to that originalColumnElement
via a common ancestor column.- Parameters:
column¶ – the target
ColumnElement
to be matched.require_embedded¶ – only return corresponding columns for the given
ColumnElement
, if the givenColumnElement
is actually present within a sub-element of thisSelectable
. Normally the column will match if it merely shares a common ancestor with one of the exported columns of thisSelectable
.
See also
Selectable.exported_columns
- theColumnCollection
that is used for the operation.ColumnCollection.corresponding_column()
- implementation method.
-
method
sqlalchemy.schema.Table.
create(bind=None, checkfirst=False)¶ Issue a
CREATE
statement for thisTable
, using the givenConnectable
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
TableClause.delete()
method ofTableClause
Generate a
delete()
construct against thisTableClause
.E.g.:
table.delete().where(table.c.id==7)
See
delete()
for argument and usage information.
-
attribute
sqlalchemy.schema.Table.
description¶ inherited from the
TableClause.description
attribute ofTableClause
-
attribute
sqlalchemy.schema.Table.
dialect_kwargs¶ inherited from the
DialectKWArgs.dialect_kwargs
attribute ofDialectKWArgs
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 theDialectKWArgs.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
DialectKWArgs.dialect_kwargs
collection is now writable.See also
DialectKWArgs.dialect_options
- nested dictionary form
-
attribute
sqlalchemy.schema.Table.
dialect_options¶ inherited from the
DialectKWArgs.dialect_options
attribute ofDialectKWArgs
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, thepostgresql_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
sqlalchemy.schema.Table.
drop(bind=None, checkfirst=False)¶ Issue a
DROP
statement for thisTable
, using the givenConnectable
for connectivity.Note
the “bind” argument will be required in SQLAlchemy 2.0.
See also
-
attribute
sqlalchemy.schema.Table.
entity_namespace¶ inherited from the
FromClause.entity_namespace
attribute ofFromClause
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
Table.exists()
method is deprecated and will be removed in a future release. Please refer toInspector.has_table()
.
-
attribute
sqlalchemy.schema.Table.
exported_columns¶ inherited from the
FromClause.exported_columns
attribute ofFromClause
A
ColumnCollection
that represents the “exported” columns of thisSelectable
.The “exported” columns for a
FromClause
object are synonymous with theFromClause.columns
collection.New in version 1.4.
-
attribute
sqlalchemy.schema.Table.
foreign_key_constraints¶ ForeignKeyConstraint
objects referred to by thisTable
.This list is produced from the collection of
ForeignKey
objects currently associated.
-
attribute
sqlalchemy.schema.Table.
foreign_keys¶ inherited from the
FromClause.foreign_keys
attribute ofFromClause
Return the collection of
ForeignKey
marker objects which this FromClause references.Each
ForeignKey
is a member of aTable
-wideForeignKeyConstraint
.See also
-
method
sqlalchemy.schema.Table.
get_children(omit_attrs=(), **kw)¶ inherited from the
Traversible.get_children()
method ofTraversible
Return immediate child
Traversible
elements of thisTraversible
.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.
implicit_returning = False¶ inherited from the
TableClause.implicit_returning
attribute ofTableClause
TableClause
doesn’t support having a primary key or column -level defaults, so implicit returning doesn’t apply.
-
attribute
sqlalchemy.schema.Table.
indexes = None¶ A collection of all
Index
objects associated with thisTable
.See also
-
attribute
sqlalchemy.schema.Table.
info¶ inherited from the
SchemaItem.info
attribute ofSchemaItem
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
Table
andColumn
.
-
attribute
sqlalchemy.schema.Table.
inherit_cache = None¶ inherited from the
HasCacheKey.inherit_cache
attribute ofHasCacheKey
Indicate if this
HasCacheKey
instance should make use of the cache key generation scheme used by its immediate superclass.The attribute defaults to
None
, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value toFalse
, except that a warning is also emitted.This flag can be set to
True
on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.See also
Enabling Caching Support for Custom Constructs - General guideslines for setting the
HasCacheKey.inherit_cache
attribute for third-party or user defined SQL constructs.
-
method
sqlalchemy.schema.Table.
insert(values=None, inline=False, **kwargs)¶ inherited from the
TableClause.insert()
method ofTableClause
Generate an
insert()
construct against thisTableClause
.E.g.:
table.insert().values(name='foo')
See
insert()
for argument and usage information.
-
method
sqlalchemy.schema.Table.
is_derived_from(fromclause)¶ inherited from the
FromClause.is_derived_from()
method ofFromClause
Return
True
if thisFromClause
is ‘derived’ from the givenFromClause
.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
FromClause.join()
method ofFromClause
Return a
Join
from thisFromClause
to anotherFromClause
.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
FromClause
object such as aTable
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
,FromClause.join()
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.
-
attribute
sqlalchemy.schema.Table.
key¶ Return the ‘key’ for this
Table
.This value is used as the dictionary key within the
MetaData.tables
collection. It is typically the same as that ofTable.name
for a table with noTable.schema
set; otherwise it is typically of the formschemaname.tablename
.
-
attribute
sqlalchemy.schema.Table.
kwargs¶ inherited from the
DialectKWArgs.kwargs
attribute ofDialectKWArgs
A synonym for
DialectKWArgs.dialect_kwargs
.
-
method
sqlalchemy.schema.Table.
lateral(name=None)¶ inherited from the
Selectable.lateral()
method ofSelectable
Return a LATERAL alias of this
Selectable
.The return value is the
Lateral
construct also provided by the top-levellateral()
function.New in version 1.1.
See also
LATERAL correlation - overview of usage.
-
classmethod
sqlalchemy.schema.Table.
memoized_instancemethod(fn)¶ inherited from the
HasMemoized.memoized_instancemethod()
method ofHasMemoized
Decorate a method memoize its return value.
-
method
sqlalchemy.schema.Table.
outerjoin(right, onclause=None, full=False)¶ inherited from the
FromClause.outerjoin()
method ofFromClause
Return a
Join
from thisFromClause
to anotherFromClause
, 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
FromClause
object such as aTable
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
,FromClause.join()
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.
-
method
sqlalchemy.schema.Table.
params(*optionaldict, **kwargs)¶ inherited from the
Immutable.params()
method ofImmutable
Return a copy with
bindparam()
elements replaced.Returns a copy of this ClauseElement with
bindparam()
elements replaced with values taken from the given dictionary:>>> clause = column('x') + bindparam('foo') >>> print(clause.compile().params) {'foo':None} >>> print(clause.params({'foo':7}).compile().params) {'foo':7}
-
attribute
sqlalchemy.schema.Table.
primary_key¶ inherited from the
FromClause.primary_key
attribute ofFromClause
Return the iterable collection of
Column
objects which comprise the primary key of this_selectable.FromClause
.For a
Table
object, this collection is represented by thePrimaryKeyConstraint
which itself is an iterable collection ofColumn
objects.
-
method
sqlalchemy.schema.Table.
replace_selectable(old, alias)¶ inherited from the
Selectable.replace_selectable()
method ofSelectable
Replace all occurrences of
FromClause
‘old’ with the givenAlias
object, returning a copy of thisFromClause
.Deprecated since version 1.4: The
Selectable.replace_selectable()
method is deprecated, and will be removed in a future release. Similar functionality is available via the sqlalchemy.sql.visitors module.
-
attribute
sqlalchemy.schema.Table.
schema = None¶ inherited from the
FromClause.schema
attribute ofFromClause
Define the ‘schema’ attribute for this
FromClause
.This is typically
None
for most objects except that ofTable
, where it is taken as the value of theTable.schema
argument.
-
method
sqlalchemy.schema.Table.
select(whereclause=None, **kwargs)¶ inherited from the
FromClause.select()
method ofFromClause
Return a SELECT of this
FromClause
.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
FromClause.select().whereclause
parameter is deprecated and will be removed in version 2.0. Please make use of theSelect.where()
method to add WHERE criteria to the SELECT statement.**kwargs¶ – additional keyword arguments are passed to the legacy constructor for
Select
described atSelect.create_legacy_select()
.
See also
select()
- general purpose method which allows for arbitrary column lists.
-
method
sqlalchemy.schema.Table.
self_group(against=None)¶ inherited from the
ClauseElement.self_group()
method ofClauseElement
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
select()
constructs when placed into the FROM clause of anotherselect()
. (Note that subqueries should be normally created using theSelect.alias()
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 likex OR (y AND z)
- AND takes precedence over OR.The base
self_group()
method ofClauseElement
just returns self.
-
method
sqlalchemy.schema.Table.
table_valued()¶ inherited from the
FromClause.table_valued()
method ofFromClause
Return a
TableValuedColumn
object for thisFromClause
.A
TableValuedColumn
is aColumnElement
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 SQLAlchemy 1.4 / 2.0 Tutorial
-
method
sqlalchemy.schema.Table.
tablesample(sampling, name=None, seed=None)¶ inherited from the
FromClause.tablesample()
method ofFromClause
Return a TABLESAMPLE alias of this
FromClause
.The return value is the
TableSample
construct also provided by the top-leveltablesample()
function.New in version 1.1.
See also
tablesample()
- usage guidelines and parameters
-
method
sqlalchemy.schema.Table.
to_metadata(metadata, schema=symbol('retain_schemaSymbol indicating that a :class:`_schema.Table`, :class:`.Sequence`\n or in some cases a :class:`_schema.ForeignKey` object, in situations\n where the object is being copied for a :meth:`.Table.to_metadata`\n operation, should retain the schema name that it already has.\n\n '), referred_schema_fn=None, name=None)¶ Return a copy of this
Table
associated with a differentMetaData
.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 fromTable.tometadata()
.- Parameters:
metadata¶ – Target
MetaData
object, into which the newTable
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 newTable
. If set to a string name, the newTable
will have this new name as the.schema
. If set toNone
, the schema will be set to that of the schema set on the targetMetaData
, which is typicallyNone
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
ForeignKeyConstraint
. The callable accepts this parentTable
, the target schema that we are changing to, theForeignKeyConstraint
object, and the existing “target schema” of that constraint. The function should return the string schema name that should be applied. To reset the schema to “none”, return the symbolBLANK_SCHEMA
. To effect no change, returnNone
orRETAIN_SCHEMA
.Changed in version 1.4.33: The
referred_schema_fn
function may return theBLANK_SCHEMA
orRETAIN_SCHEMA
symbols.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 sameMetaData
target with a new name.New in version 1.0.0.
-
method
sqlalchemy.schema.Table.
tometadata(metadata, schema=symbol('retain_schemaSymbol indicating that a :class:`_schema.Table`, :class:`.Sequence`\n or in some cases a :class:`_schema.ForeignKey` object, in situations\n where the object is being copied for a :meth:`.Table.to_metadata`\n operation, should retain the schema name that it already has.\n\n '), referred_schema_fn=None, name=None)¶ Return a copy of this
Table
associated with a differentMetaData
.Deprecated since version 1.4:
Table.tometadata()
is renamed toTable.to_metadata()
See
Table.to_metadata()
for a full description.
-
method
sqlalchemy.schema.Table.
unique_params(*optionaldict, **kwargs)¶ inherited from the
Immutable.unique_params()
method ofImmutable
Return a copy with
bindparam()
elements replaced.Same functionality as
ClauseElement.params()
, except adds unique=True to affected bind parameters so that multiple statements can be used.
-
method
sqlalchemy.schema.Table.
update(whereclause=None, values=None, inline=False, **kwargs)¶ inherited from the
TableClause.update()
method ofTableClause
Generate an
update()
construct against thisTableClause
.E.g.:
table.update().where(table.c.id==7).values(name='foo')
See
update()
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 differentEngine
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 usingconnect()
. You can also re-bind dynamically multiple times per thread, just like a regularMetaData
.Members
Class signature
class
sqlalchemy.schema.ThreadLocalMetaData
(sqlalchemy.schema.MetaData
)-
method
sqlalchemy.schema.ThreadLocalMetaData.
__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
sqlalchemy.schema.ThreadLocalMetaData.
dispose()¶ Dispose all bound engines, in all thread contexts.
-
method
sqlalchemy.schema.ThreadLocalMetaData.
is_bound()¶ True if there is a bind for this thread.
-
method
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Wed 30 Oct 2024 02:18:58 PM EDT