使用元数据描述数据库

Describing Databases with MetaData

本节讨论了基本的 TableColumnMetaData 对象。

参见

使用数据库元数据 - 在 SQLAlchemy 统一教程 中介绍SQLAlchemy数据库元数据概念的教程

元数据实体的集合存储在一个恰当地命名为:class:`~sqlalchemy.schema.MetaData`的对象中:

from sqlalchemy import MetaData

metadata_obj = MetaData()

MetaData 是一个容器对象,它将被描述的数据库(或多个数据库)的许多不同特性保存在一起。

要表示一个表,使用 Table 类。它的两个主要参数是表名,然后是它将关联的 MetaData 对象。其余的参数主要是描述每个列的 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),
)

上面描述了一个名为 user 的表,其中包含四个列。表的主键由 user_id 列组成。多个列可以分配 primary_key=True 标志,这表示一个多列主键,称为 复合 主键。

另请注意,每列使用与通用类型相对应的对象来描述其数据类型,例如 IntegerString。SQLAlchemy具有几十种不同层次的类型,并且可以创建自定义类型。关于类型系统的文档可以在 SQL 数据类型对象 中找到。

This section discusses the fundamental Table, Column and MetaData objects.

参见

使用数据库元数据 - tutorial introduction to SQLAlchemy’s database metadata concept in the SQLAlchemy 统一教程

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 数据类型对象.

访问表和列

Accessing Tables and Columns

MetaData 对象包含了与其关联的所有模式构造元素。它支持几种访问这些表对象的方法,例如 sorted_tables 访问器会按外键依赖顺序返回每个 Table 对象的列表(即,每个表之前都会列出它所引用的所有表):

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

在大多数情况下,单个 Table 对象会被显式声明,这些对象通常作为模块级变量直接在应用程序中访问。一旦定义了 Table,它就拥有一整套访问器,可以用来检查其属性。下面是一个 Table 的定义示例:

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")),
)

请注意该表中使用了 ForeignKey 对象 —— 该构造定义了对远程表的引用,完整说明请参见 定义外键。关于如何访问该表信息的方法包括:

# 访问 "employee_id" 列:
employees.columns.employee_id

# 或者更简洁地
employees.c.employee_id

# 通过字符串访问
employees.c["employee_id"]

# 使用多个字符串返回列元组(2.0 新增)
emp_id, name, type = employees.c["employee_id", "name", "type"]

# 遍历所有列
for c in employees.c:
    print(c)

# 获取表的主键列
for primary_key in employees.primary_key:
    print(primary_key)

# 获取表的外键对象:
for fkey in employees.foreign_keys:
    print(fkey)

# 访问表的 MetaData:
employees.metadata

# 访问列的名称、类型、是否可为空、是否为主键、外键信息
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

# 获取列的 "key",默认为列名,但可以是任何用户自定义的字符串:
employees.c.employee_name.key

# 访问列所归属的表:
employees.c.employee_id.table is employees

# 获取与外键关联的表:
list(employees.c.employee_dept.foreign_keys)[0].column.table

小技巧

FromClause.c 集合(与 FromClause.columns 同义)是 ColumnCollection 的实例,提供了 类字典接口 来访问列集合。通常我们通过属性访问,如 employees.c.employee_name。 但如果列名中包含空格,或与字典方法名称冲突(例如 ColumnCollection.keys()ColumnCollection.values()),则必须使用索引方式访问,如 employees.c['values']employees.c["some column"]。详细信息请参见 ColumnCollection

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 定义外键. 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"]

# a tuple of columns may be returned using multiple strings
# (new in 2.0)
emp_id, name, type = employees.c["employee_id", "name", "type"]

# 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 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

小技巧

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

一旦你定义了一些 Table 对象,并且假设你正在操作一个全新的数据库,那么你可能希望为这些表及其相关结构生成 CREATE 语句(顺便说一下,如果你已经有偏好的方法,例如数据库自带的工具或已有的脚本系统,也完全可以跳过这个部分 —— SQLAlchemy 并不强制必须通过它来创建表结构)。

通常使用 create_all() 方法对 MetaData 对象执行 CREATE 操作。此方法会为每个表先检查是否存在,如果不存在再发出 CREATE 语句:

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)),
)

metadata_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() 方法通常会在表定义中直接内联生成外键约束,并且按照依赖顺序生成表结构。该行为可以通过选项修改,从而改为使用 ALTER TABLE 语句。

删除所有表可使用 drop_all() 方法。该方法与 create_all() 相反 —— 它会先检查每张表是否存在,并按依赖的逆序进行删除。

也可以通过 Tablecreate()drop() 方法创建或删除单个表。默认情况下这些方法会直接执行 CREATE 或 DROP,无论表是否存在:

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")),
)
employees.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() 方法:

employees.drop(engine)
DROP TABLE employees {}

若希望在执行前先检查表是否存在,可以给 create()drop() 方法传入 checkfirst=True 参数:

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

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)),
)

metadata_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")),
)
employees.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:

employees.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

虽然 SQLAlchemy 直接支持对模式结构发出 CREATE 和 DROP 语句,但修改这些结构(通常通过 ALTER 语句以及其他特定数据库的结构)超出了 SQLAlchemy 本身的范围。尽管通过传递 text() 构造给 Connection.execute() 或使用 DDL 构造手动发出 ALTER 语句等操作很简单,但一种常见做法是使用模式迁移工具,将数据库模式的维护自动化,以配合应用程序代码的演进。

SQLAlchemy 项目提供了专门的迁移工具 Alembic 来完成这一目标。Alembic 提供了高度可定制的环境和极简的使用模式,支持如下功能:事务性 DDL、自动生成“候选”迁移、“离线”模式生成 SQL 脚本,以及分支解析支持。

Alembic 替代了原有的 SQLAlchemy-Migrate 项目,该项目是 SQLAlchemy 的第一个迁移工具,现在已被视为遗留方案。

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

大多数数据库都支持多个“模式”(schemas)的概念 —— 也就是命名空间,它们指向另一组表和其他结构。服务器端的“模式”结构形式多种多样,包括特定数据库下的“模式”名称(如 PostgreSQL 的 schema)、命名的兄弟数据库(如 MySQL / MariaDB 对同一服务器上其他数据库的访问),还有其他一些机制,如其他用户名所拥有的表(Oracle、SQL Server),甚至是指向其他数据库文件(如 SQLite 的 ATTACH)或远程服务器(如 Oracle Database 的 DBLINK 结合 synonym)。

上述各种机制的共同点(大多数情况下)是它们可以通过一个字符串名称来引用另一组表。SQLAlchemy 将这个名称称为 schema 名称。在 SQLAlchemy 中,它只是一个字符串名称,关联到某个 Table 对象,并以适合目标数据库的方式渲染到 SQL 语句中,从而使得表在其远程“schema”中被正确引用 —— 无论这个机制在目标数据库中是怎样实现的。

可以直接使用 Table.schema 参数将 “schema” 名称关联到一个 Table 对象;当使用 ORM 的 declarative table 配置时,该参数通过 __table_args__ 参数字典传递。

也可以将 “schema” 名称关联到 MetaData 对象上,那么这个 schema 将自动作用于所有没有显式指定 schema 名称的 Table 对象。此外,SQLAlchemy 还支持“动态” schema 名称系统,这在多租户(multi-tenant)应用中非常常见,可以使一组 Table 元数据在每个连接或每条语句基础上引用不同的 schema 名称。

参见

使用声明性表的显式架构名称 - 使用 ORM 时指定 schema 名称 declarative table 配置方式

最基本的例子是使用 Core 层 Table 对象时传入 Table.schema 参数,如下所示:

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",
)

通过这个 Table 生成的 SQL(如下所示的 SELECT 语句)将使用 schema 名称 remote_banks 来限定表名 financial_info

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

当使用显式 schema 名称声明 Table 对象时,它将在内部以 schema 和表名的组合形式存储在 MetaData 的命名空间中。我们可以通过在 MetaData.tables 集合中查找 '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')

当通过 ForeignKeyForeignKeyConstraint 对象引用该表时,也必须使用这种带点的名称,即使引用表也在同一 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",
)

在某些方言中,Table.schema 参数还可以表示多级标识符路径(例如点分形式的路径)。这在一些数据库中尤为重要,比如 Microsoft SQL Server,通常使用点号分隔的“数据库/所有者”标识符。此时,token 可以直接全部作为 schema 传入,例如:

schema = "dbo.scott"

参见

多部分架构名称 - 使用 SQL Server 方言时如何使用点分 schema 名称 反射来自其他架构的表

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 Database, SQL Server) or even names that refer to alternate database files (SQLite ATTACH) or remote servers (Oracle Database DBLINK with synonyms).

What all of the above approaches have (mostly) in common is that there’s a way of referencing 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 referenced 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.

参见

使用声明性表的显式架构名称 - 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"

参见

多部分架构名称 - describes use of dotted schema names with the SQL Server dialect.

反射来自其他架构的表

使用元数据指定默认架构名称

Specifying a Default Schema Name with MetaData

也可以在顶层的 MetaData 构造中传入 MetaData.schema 参数,为所有 Table.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),
)

如上所示,对于所有将 Table.schema 参数保留为默认值 NoneTable 对象(或直接与该 MetaData 关联的 Sequence 对象),将视为该参数被设置为 "remote_banks"。这包括该 TableMetaData 中是以 schema 限定名形式被收录的,也就是说:

metadata_obj.tables["remote_banks.financial_info"]

当使用 ForeignKeyForeignKeyConstraint 对象引用该表时,可以使用 schema 限定名或非限定名来引用 remote_banks.financial_info 表:

# 两种方式都可行:

refers_to_financial_info = Table(
    "refers_to_financial_info",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("fiid", ForeignKey("financial_info.id")),
)


# 或者

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")),
)

当使用带有 MetaData.schemaMetaData 对象时,如果某个 Table 明确指定它不应使用 schema 限定,可以使用特殊标识符 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,  # 不会使用 "remote_banks"
)

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"
)

应用动态架构命名约定

Applying Dynamic Schema Naming Conventions

Table.schema 参数所使用的名称也可以用于按连接或执行时动态查找的机制。例如在多租户场景中,每次事务或语句执行都可以针对一组会变化的特定 schema 名称。相关特性在 模式名称的转换 一节中有详细说明。

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

为新连接设置默认架构

Setting a Default Schema for New Connections

上述方法都涉及在 SQL 语句中显式包含 schema 名称。 实际上,数据库连接具备“默认 schema”的概念, 即当表名未显式指定 schema 时,所使用的“schema”(或数据库、所有者等)的名称。 这些名称通常在登录时进行配置,例如连接到 PostgreSQL 数据库时,默认的 “schema” 名为 “public”。

在许多情况下,默认的 “schema” 无法通过登录本身进行设置,而是需要在每次建立连接时进行配置, 例如在 PostgreSQL 中使用 “SET SEARCH_PATH” 或在 Oracle Database 中使用 “ALTER SESSION”。 可以通过 PoolEvents.connect() 事件来实现这一点, 该事件允许在首次创建 DBAPI 连接时进行操作。例如,以下代码将 Oracle Database 的 CURRENT_SCHEMA 设置为一个自定义名称:

from sqlalchemy import event
from sqlalchemy import create_engine

engine = create_engine(
    "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"
)


@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()

上述代码中,set_current_schema() 事件处理器会在上述 Engine 首次建立连接时立即执行; 由于事件被“插入”到处理器列表的开头,它还会在方言(dialect)自己的事件处理器运行之前执行, 尤其包括确定连接的“默认 schema”的那一个处理器。

对于其他数据库,请参考数据库本身和/或其 SQLAlchemy 方言文档,以了解如何设置默认 schema。

在 1.4.0b2 版本发生变更: 上述方案现在无需额外注册其它事件处理器即可工作。

参见

连接时设置备用搜索路径 - 位于 PostgreSQL 方言文档中。

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 Database. 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 Database CURRENT_SCHEMA variable to an alternate name:

from sqlalchemy import event
from sqlalchemy import create_engine

engine = create_engine(
    "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"
)


@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.

在 1.4.0b2 版本发生变更: The above recipe now works without the need to

establish additional event handlers.

参见

连接时设置备用搜索路径 - in the PostgreSQL dialect documentation.

架构和反射

Schemas and Reflection

SQLAlchemy 的 schema 功能会与在 反射数据库对象 中介绍的表反射功能交互使用。 更多细节请参考 反射来自其他架构的表

The schema feature of SQLAlchemy interacts with the table reflection feature introduced at 反射数据库对象. See the section 反射来自其他架构的表 for additional details on how this works.

后端特定选项

Backend-Specific Options

Table 支持数据库特定的选项。 例如,MySQL 支持不同的表存储引擎,包括 “MyISAM” 和 “InnoDB”。 可以通过 Tablemysql_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",
)

其他数据库后端也可能支持表级别的选项 —— 这些内容可在每种方言的独立文档章节中找到说明。

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.

列、表、元数据 API

Column, Table, MetaData API

Object Name Description

Column

Represents a column in a database table.

insert_sentinel([name, type_], *, [default, omit_from_statements])

Provides a surrogate Column that will act as a dedicated insert sentinel column, allowing efficient bulk inserts with deterministic RETURNING sorting for tables that don’t otherwise have qualifying primary key configurations.

MetaData

A collection of Table objects and their associated schema constructs.

SchemaConst

SchemaItem

Base class for items that define a database schema.

Table

Represent a table in a database.

attribute sqlalchemy.schema.sqlalchemy.schema.sqlalchemy.schema.BLANK_SCHEMA

Refers to SchemaConst.BLANK_SCHEMA.

attribute sqlalchemy.schema.sqlalchemy.schema.sqlalchemy.schema.RETAIN_SCHEMA

Refers to SchemaConst.RETAIN_SCHEMA

class sqlalchemy.schema.Column

Represents a column in a database table.

method sqlalchemy.schema.Column.__eq__(other: Any) ColumnOperators

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

Implement the == operator.

In a column context, produces the clause a = b. If the target is None, produces a IS NULL.

method sqlalchemy.schema.Column.__init__(_Column__name_pos: str | _TypeEngineArgument[_T] | SchemaEventTarget | None = None, _Column__type_pos: _TypeEngineArgument[_T] | SchemaEventTarget | None = None, /, *args: SchemaEventTarget, name: str | None = None, type_: _TypeEngineArgument[_T] | None = None, autoincrement: _AutoIncrementType = 'auto', default: Any | None = _NoArg.NO_ARG, insert_default: Any | None = _NoArg.NO_ARG, doc: str | None = None, key: str | None = None, index: bool | None = None, unique: bool | None = None, info: _InfoType | None = None, nullable: bool | Literal[SchemaConst.NULL_UNSPECIFIED] | None = SchemaConst.NULL_UNSPECIFIED, onupdate: Any | None = None, primary_key: bool = False, server_default: _ServerDefaultArgument | None = None, server_onupdate: _ServerOnUpdateArgument | None = None, quote: bool | None = None, system: bool = False, comment: str | None = None, insert_sentinel: bool = False, _omit_from_statements: bool = False, _proxies: Any | None = None, **dialect_kwargs: Any)

Construct a new Column object.

参数:
  • 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 Database.

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

  • *args – Additional positional arguments include various SchemaItem derived constructs which will be applied as options to the column. These include instances of Constraint, ForeignKey, ColumnDefault, Sequence, Computed Identity. In some cases an equivalent keyword argument is available such as server_default, default and unique.

  • autoincrement

    Set up “auto increment” semantics for an integer primary key column 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 include True (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() and Table.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 a Column that has no other default-generating construct associated with it (such as a Sequence or Identity construct), the parameter will imply that database-specific keywords such as PostgreSQL SERIAL, MySQL AUTO_INCREMENT, or IDENTITY on SQL Server should also be rendered. Not every database backend has an “implied” default generator available; for example the Oracle Database backends alway needs an explicit construct such as Identity to be included with a Column 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 using Connection.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 the CursorResult.inserted_primary_key attribute upon the Result 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 the Column 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 to True to indicate autoincrement semantics on a column that has a client-side or server-side default configured, however note that not all dialects can accommodate all styles of default as an “autoincrement”. It can also be set to False on a single-column primary key that has a datatype of INTEGER in order to disable auto increment semantics for that column.

    The setting only has an effect for columns which are:

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

    • Part of the primary key

    • Not referring to another column via 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 the Column.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 自动递增行为 for more background.

      • Oracle Database - The Oracle Database dialects have no default “autoincrement” feature available at this time, instead the Identity construct is recommended to achieve this (the Sequence 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 the Insert.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 Database - use RETURNING or an equivalent construct when rendering an INSERT statement, and then retrieving the newly generated primary key values after execution

      • PostgreSQL, Oracle Database for Table objects that set Table.implicit_returning to False - for a Sequence only, the Sequence is invoked explicitly before the INSERT statement takes place so that the newly generated primary key value is available to the client

      • SQL Server for Table objects that set Table.implicit_returning to False - the SELECT 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 using ColumnDefault 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.

  • insert_default

    An alias of Column.default for compatibility with mapped_column().

    在 2.0.31 版本加入.

  • 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 the Table. When a key is provided, this is the only identifier referencing the Column within the application, including ORM attribute mapping; the name field is used only when rendering SQL.

  • index

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

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

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

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

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

    As this flag is intended only as a convenience for the common case of adding a single-column, default configured index to a table definition, explicit use of the 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.

    备注

    the Column.index attribute on Column does not indicate if this column is indexed or not, only if this flag was explicitly set here. To view indexes on a column, view the Table.indexes collection or use Inspector.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. When True, will normally generate nothing (in SQL this defaults to “NULL”), except in some very specific backend-specific edge cases where “NULL” may render explicitly. Defaults to True unless Column.primary_key is also True or the column specifies a Identity, in which case it defaults to False. This parameter is only used when issuing CREATE TABLE statements.

    备注

    When the column specifies a Identity this parameter is in general ignored by the DDL compiler. The PostgreSQL database allows nullable identity column by setting this parameter to True explicitly.

  • onupdate

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

    参见

    列 INSERT/UPDATE 默认值 - complete discussion of onupdate

  • primary_key – If True, marks this column as a primary key column. Multiple columns can have this flag set to specify composite primary keys. As an alternative, the primary key of a Table can be specified via an explicit PrimaryKeyConstraint object.

  • server_default

    A FetchedValue instance, str, Unicode or text() construct representing the DDL DEFAULT value for the column.

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

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

    will render:

    x TEXT DEFAULT 'val'

    A text() expression will be rendered as-is, without quotes:

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

    will render:

    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+psycopg2://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.

    参见

    服务器调用的 DDL 显式默认表达式 - 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.

    警告

    This directive does not currently produce MySQL’s “ON UPDATE CURRENT_TIMESTAMP()” clause. See MySQL/MariaDB 的 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 or False. When left at its default of None, the column identifier will be quoted according to whether the name is case sensitive (identifiers with at least one upper case character are treated as case sensitive), or if it’s a reserved word. This flag is only needed to force quoting of a reserved word which is not known by the SQLAlchemy dialect.

  • unique

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

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

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

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

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

    As this flag is intended only as a convenience for the common case of adding a single-column, default configured unique constraint to a table definition, explicit use of the 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.

    备注

    the Column.unique attribute on Column does not indicate if this column has a unique constraint or not, only if this flag was explicitly set here. To view indexes and unique constraints that may involve this column, view the Table.indexes and/or Table.constraints collections or use Inspector.get_indexes() and/or Inspector.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 a CREATE 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.

  • insert_sentinel

    Marks this Column as an insert sentinel used for optimizing the performance of the insertmanyvalues feature for tables that don’t otherwise have qualifying primary key configurations.

    在 2.0.10 版本加入.

    参见

    insert_sentinel() - all in one helper for declaring sentinel columns

    INSERT 语句的“插入多个值”行为

    配置 Sentinel 列

method sqlalchemy.schema.Column.__le__(other: Any) ColumnOperators

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

Implement the <= operator.

In a column context, produces the clause a <= b.

method sqlalchemy.schema.Column.__lt__(other: Any) ColumnOperators

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

Implement the < operator.

In a column context, produces the clause a < b.

method sqlalchemy.schema.Column.__ne__(other: Any) ColumnOperators

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

Implement the != operator.

In a column context, produces the clause a != b. If the target is None, produces a IS NOT NULL.

method sqlalchemy.schema.Column.all_() ColumnOperators

inherited from the ColumnOperators.all_() method of ColumnOperators

Produce an all_() clause against the parent object.

See the documentation for all_() for examples.

备注

be sure to not confuse the newer ColumnOperators.all_() method with the legacy version of this method, the Comparator.all() method that’s specific to ARRAY, which uses a different calling style.

attribute sqlalchemy.schema.Column.anon_key_label

inherited from the ColumnElement.anon_key_label attribute of ColumnElement

自 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 of ColumnElement

自 1.4 版本弃用: The ColumnElement.anon_label attribute is now private, and the public accessor is deprecated.

method sqlalchemy.schema.Column.any_() ColumnOperators

inherited from the ColumnOperators.any_() method of ColumnOperators

Produce an any_() clause against the parent object.

See the documentation for any_() for examples.

备注

be sure to not confuse the newer ColumnOperators.any_() method with the legacy version of this method, the Comparator.any() method that’s specific to ARRAY, which uses a different calling style.

classmethod sqlalchemy.schema.Column.argument_for(dialect_name, argument_name, default)

inherited from the DialectKWArgs.argument_for() method of DialectKWArgs

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

E.g.:

Index.argument_for("mydialect", "length", None)

some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

参数:
  • dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.

  • argument_name – name of the parameter.

  • default – default value of the parameter.

method sqlalchemy.schema.Column.asc() ColumnOperators

inherited from the ColumnOperators.asc() method of ColumnOperators

Produce a asc() clause against the parent object.

method sqlalchemy.schema.Column.between(cleft: Any, cright: Any, symmetric: bool = False) ColumnOperators

inherited from the ColumnOperators.between() method of ColumnOperators

Produce a between() clause against the parent object, given the lower and upper range.

method sqlalchemy.schema.Column.bitwise_and(other: Any) ColumnOperators

inherited from the ColumnOperators.bitwise_and() method of ColumnOperators

Produce a bitwise AND operation, typically via the & operator.

在 2.0.2 版本加入.

method sqlalchemy.schema.Column.bitwise_lshift(other: Any) ColumnOperators

inherited from the ColumnOperators.bitwise_lshift() method of ColumnOperators

Produce a bitwise LSHIFT operation, typically via the << operator.

在 2.0.2 版本加入.

method sqlalchemy.schema.Column.bitwise_not() ColumnOperators

inherited from the ColumnOperators.bitwise_not() method of ColumnOperators

Produce a bitwise NOT operation, typically via the ~ operator.

在 2.0.2 版本加入.

method sqlalchemy.schema.Column.bitwise_or(other: Any) ColumnOperators

inherited from the ColumnOperators.bitwise_or() method of ColumnOperators

Produce a bitwise OR operation, typically via the | operator.

在 2.0.2 版本加入.

method sqlalchemy.schema.Column.bitwise_rshift(other: Any) ColumnOperators

inherited from the ColumnOperators.bitwise_rshift() method of ColumnOperators

Produce a bitwise RSHIFT operation, typically via the >> operator.

在 2.0.2 版本加入.

method sqlalchemy.schema.Column.bitwise_xor(other: Any) ColumnOperators

inherited from the ColumnOperators.bitwise_xor() method of ColumnOperators

Produce a bitwise XOR operation, typically via the ^ operator, or # for PostgreSQL.

在 2.0.2 版本加入.

method sqlalchemy.schema.Column.bool_op(opstring: str, precedence: int = 0, python_impl: Callable[[...], Any] | None = None) Callable[[Any], Operators]

inherited from the Operators.bool_op() method of Operators

Return a custom boolean operator.

This method is shorthand for calling Operators.op() and passing the Operators.op.is_comparison flag with True. A key advantage to using Operators.bool_op() is that when using column constructs, the “boolean” nature of the returned expression will be present for PEP 484 purposes.

method sqlalchemy.schema.Column.cast(type_: _TypeEngineArgument[_OPT]) Cast[_OPT]

inherited from the ColumnElement.cast() method of ColumnElement

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

This is a shortcut to the cast() function.

method sqlalchemy.schema.Column.collate(collation: str) ColumnOperators

inherited from the ColumnOperators.collate() method of ColumnOperators

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

参见

collate()

method sqlalchemy.schema.Column.compare(other: ClauseElement, **kw: Any) bool

inherited from the ClauseElement.compare() method of ClauseElement

Compare this ClauseElement to the given ClauseElement.

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

**kw are arguments consumed by subclass compare() methods and may be used to modify the criteria for comparison (see ColumnElement).

method sqlalchemy.schema.Column.compile(bind: _HasDialect | None = None, dialect: Dialect | None = None, **kw: Any) Compiled

inherited from the CompilerElement.compile() method of CompilerElement

Compile this SQL expression.

The return value is a Compiled object. Calling str() or unicode() on the returned value will yield a string representation of the result. The Compiled object also can return a dictionary of bind parameter names and values using the params accessor.

参数:
  • bind – An Connection or Engine which can provide a Dialect in order to generate a Compiled object. If the bind and dialect parameters are both omitted, a default SQL compiler is used.

  • 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 which can generate a Compiled object. This argument takes precedence over the bind argument.

  • 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}))

method sqlalchemy.schema.Column.concat(other: Any) ColumnOperators

inherited from the ColumnOperators.concat() method of ColumnOperators

Implement the ‘concat’ operator.

In a column context, produces the clause a || b, or uses the concat() operator on MySQL.

method sqlalchemy.schema.Column.contains(other: Any, **kw: Any) ColumnOperators

inherited from the ColumnOperators.contains() method of ColumnOperators

Implement the ‘contains’ operator.

Produces a LIKE expression that tests against a match for the middle of a string value:

column LIKE '%' || <other> || '%'

E.g.:

stmt = select(sometable).where(sometable.c.column.contains("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.contains.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.contains.escape parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

参数:
  • other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.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: Any) Column[Any]

自 1.4 版本弃用: The Column.copy() method is deprecated and will be removed in a future release.

method sqlalchemy.schema.Column.desc() ColumnOperators

inherited from the ColumnOperators.desc() method of ColumnOperators

Produce a desc() clause against the parent object.

attribute sqlalchemy.schema.Column.dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of DialectKWArgs

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

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

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

参见

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.schema.Column.dialect_options

inherited from the DialectKWArgs.dialect_options attribute of DialectKWArgs

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

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

在 0.9.2 版本加入.

参见

DialectKWArgs.dialect_kwargs - flat dictionary form

method sqlalchemy.schema.Column.distinct() ColumnOperators

inherited from the ColumnOperators.distinct() method of ColumnOperators

Produce a distinct() clause against the parent object.

method sqlalchemy.schema.Column.endswith(other: Any, escape: str | None = None, autoescape: bool = False) ColumnOperators

inherited from the ColumnOperators.endswith() method of ColumnOperators

Implement the ‘endswith’ operator.

Produces a LIKE expression that tests against a match for the end of a string value:

column LIKE '%' || <other>

E.g.:

stmt = select(sometable).where(sometable.c.column.endswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.endswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.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.

参数:
  • other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.endswith.autoescape flag is set to True.

  • autoescape

    boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

    An expression such as:

    somecolumn.endswith("foo%bar", autoescape=True)

    Will render as:

    somecolumn LIKE '%' || :param ESCAPE '/'

    With the value of :param as "foo/%bar".

  • escape

    a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

    An expression such as:

    somecolumn.endswith("foo/%bar", escape="^")

    Will render as:

    somecolumn LIKE '%' || :param ESCAPE '^'

    The parameter may also be combined with 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 of ColumnElement

Return a column expression.

Part of the inspection interface; returns self.

attribute sqlalchemy.schema.Column.foreign_keys: Set[ForeignKey] = frozenset({})

inherited from the ColumnElement.foreign_keys attribute of ColumnElement

A collection of all ForeignKey marker objects associated with this Column.

Each object is a member of a Table-wide ForeignKeyConstraint.

method sqlalchemy.schema.Column.get_children(*, column_tables=False, **kw)

inherited from the ColumnClause.get_children() method of ColumnClause

Return immediate child HasTraverseInternals elements of this HasTraverseInternals.

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.icontains(other: Any, **kw: Any) ColumnOperators

inherited from the ColumnOperators.icontains() method of ColumnOperators

Implement the icontains operator, e.g. case insensitive version of ColumnOperators.contains().

Produces a LIKE expression that tests against an insensitive match for the middle of a string value:

lower(column) LIKE '%' || lower(<other>) || '%'

E.g.:

stmt = select(sometable).where(sometable.c.column.icontains("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.icontains.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.icontains.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.

参数:
  • other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.icontains.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.icontains("foo%bar", autoescape=True)

    Will render as:

    lower(somecolumn) LIKE '%' || lower(: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.icontains("foo/%bar", escape="^")

    Will render as:

    lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^'

    The parameter may also be combined with ColumnOperators.contains.autoescape:

    somecolumn.icontains("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.iendswith(other: Any, escape: str | None = None, autoescape: bool = False) ColumnOperators

inherited from the ColumnOperators.iendswith() method of ColumnOperators

Implement the iendswith operator, e.g. case insensitive version of ColumnOperators.endswith().

Produces a LIKE expression that tests against an insensitive match for the end of a string value:

lower(column) LIKE '%' || lower(<other>)

E.g.:

stmt = select(sometable).where(sometable.c.column.iendswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.iendswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.iendswith.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.

参数:
  • other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.iendswith.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.iendswith("foo%bar", autoescape=True)

    Will render as:

    lower(somecolumn) LIKE '%' || lower(: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.iendswith("foo/%bar", escape="^")

    Will render as:

    lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^'

    The parameter may also be combined with ColumnOperators.iendswith.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.

method sqlalchemy.schema.Column.ilike(other: Any, escape: str | None = None) ColumnOperators

inherited from the ColumnOperators.ilike() method of ColumnOperators

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

In a column context, produces an expression either of the form:

lower(a) LIKE lower(other)

Or on backends that support the ILIKE operator:

a ILIKE other

E.g.:

stmt = select(sometable).where(sometable.c.column.ilike("%foobar%"))
参数:
  • other – expression to be compared

  • escape

    optional escape character, renders the ESCAPE keyword, e.g.:

    somecolumn.ilike("foo/%bar", escape="/")

method sqlalchemy.schema.Column.in_(other: Any) ColumnOperators

inherited from the ColumnOperators.in_() method of ColumnOperators

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)

    在 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 the bindparam.expanding flag:

    stmt.where(column.in_(bindparam("value", expanding=True)))

    In this calling form, the expression renders a special non-SQL placeholder expression that looks like:

    WHERE COL IN ([EXPANDING_value])

    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 (?, ?, ?)

    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)
  • 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)
参数:

other – a list of literals, a select() construct, or a bindparam() construct that includes the bindparam.expanding flag set to True.

attribute sqlalchemy.schema.Column.index: bool | None

The value of the Column.index parameter.

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

参见

Table.indexes

attribute sqlalchemy.schema.Column.info

inherited from the SchemaItem.info attribute of SchemaItem

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 and Column.

attribute sqlalchemy.schema.Column.inherit_cache: bool | None = 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 to False, 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.

参见

为自定义构造启用缓存支持 - General guideslines for setting the HasCacheKey.inherit_cache attribute for third-party or user defined SQL constructs.

method sqlalchemy.schema.Column.is_(other: Any) ColumnOperators

inherited from the ColumnOperators.is_() method of ColumnOperators

Implement the IS operator.

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

method sqlalchemy.schema.Column.is_distinct_from(other: Any) ColumnOperators

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”.

method sqlalchemy.schema.Column.is_not(other: Any) ColumnOperators

inherited from the ColumnOperators.is_not() method of ColumnOperators

Implement the IS NOT operator.

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

在 1.4 版本发生变更: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.is_not_distinct_from(other: Any) ColumnOperators

Implement the IS NOT DISTINCT FROM operator.

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

在 1.4 版本发生变更: The is_not_distinct_from() operator is renamed from isnot_distinct_from() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.isnot(other: Any) ColumnOperators

inherited from the ColumnOperators.isnot() method of ColumnOperators

Implement the IS NOT operator.

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

在 1.4 版本发生变更: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.isnot_distinct_from(other: Any) ColumnOperators

Implement the IS NOT DISTINCT FROM operator.

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

在 1.4 版本发生变更: The is_not_distinct_from() operator is renamed from isnot_distinct_from() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.istartswith(other: Any, escape: str | None = None, autoescape: bool = False) ColumnOperators

inherited from the ColumnOperators.istartswith() method of ColumnOperators

Implement the istartswith operator, e.g. case insensitive version of ColumnOperators.startswith().

Produces a LIKE expression that tests against an insensitive match for the start of a string value:

lower(column) LIKE lower(<other>) || '%'

E.g.:

stmt = select(sometable).where(sometable.c.column.istartswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.istartswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.istartswith.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.

参数:
  • other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.istartswith.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.istartswith("foo%bar", autoescape=True)

    Will render as:

    lower(somecolumn) LIKE lower(: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.istartswith("foo/%bar", escape="^")

    Will render as:

    lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^'

    The parameter may also be combined with ColumnOperators.istartswith.autoescape:

    somecolumn.istartswith("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.key: str = None

inherited from the ColumnElement.key attribute of ColumnElement

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 a Column with a .key of “somekey”.

attribute sqlalchemy.schema.Column.kwargs

inherited from the DialectKWArgs.kwargs attribute of DialectKWArgs

A synonym for DialectKWArgs.dialect_kwargs.

method sqlalchemy.schema.Column.label(name: str | None) Label[_T]

inherited from the ColumnElement.label() method of ColumnElement

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: Any, escape: str | None = None) ColumnOperators

inherited from the ColumnOperators.like() method of ColumnOperators

Implement the like operator.

In a column context, produces the expression:

a LIKE other

E.g.:

stmt = select(sometable).where(sometable.c.column.like("%foobar%"))
参数:
  • other – expression to be compared

  • escape

    optional escape character, renders the ESCAPE keyword, e.g.:

    somecolumn.like("foo/%bar", escape="/")

method sqlalchemy.schema.Column.match(other: Any, **kwargs: Any) ColumnOperators

inherited from the ColumnOperators.match() method of ColumnOperators

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 @@ plainto_tsquery(y)

    在 2.0 版本发生变更: plainto_tsquery() is used instead of to_tsquery() for PostgreSQL now; for compatibility with other forms, see 全文搜索.

  • MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

    参见

    match - MySQL specific construct with additional features.

  • Oracle Database - 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.

method sqlalchemy.schema.Column.not_ilike(other: Any, escape: str | None = None) ColumnOperators

inherited from the ColumnOperators.not_ilike() method of ColumnOperators

implement the NOT ILIKE operator.

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

在 1.4 版本发生变更: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.not_in(other: Any) ColumnOperators

inherited from the ColumnOperators.not_in() method of ColumnOperators

implement the NOT IN operator.

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

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

在 1.4 版本发生变更: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.not_like(other: Any, escape: str | None = None) ColumnOperators

inherited from the ColumnOperators.not_like() method of ColumnOperators

implement the NOT LIKE operator.

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

在 1.4 版本发生变更: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.notilike(other: Any, escape: str | None = None) ColumnOperators

inherited from the ColumnOperators.notilike() method of ColumnOperators

implement the NOT ILIKE operator.

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

在 1.4 版本发生变更: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.notin_(other: Any) ColumnOperators

inherited from the ColumnOperators.notin_() method of ColumnOperators

implement the NOT IN operator.

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

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

在 1.4 版本发生变更: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.notlike(other: Any, escape: str | None = None) ColumnOperators

inherited from the ColumnOperators.notlike() method of ColumnOperators

implement the NOT LIKE operator.

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

在 1.4 版本发生变更: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.nulls_first() ColumnOperators

inherited from the ColumnOperators.nulls_first() method of ColumnOperators

Produce a nulls_first() clause against the parent object.

在 1.4 版本发生变更: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.nulls_last() ColumnOperators

inherited from the ColumnOperators.nulls_last() method of ColumnOperators

Produce a nulls_last() clause against the parent object.

在 1.4 版本发生变更: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.nullsfirst() ColumnOperators

inherited from the ColumnOperators.nullsfirst() method of ColumnOperators

Produce a nulls_first() clause against the parent object.

在 1.4 版本发生变更: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.nullslast() ColumnOperators

inherited from the ColumnOperators.nullslast() method of ColumnOperators

Produce a nulls_last() clause against the parent object.

在 1.4 版本发生变更: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.

method sqlalchemy.schema.Column.op(opstring: str, precedence: int = 0, is_comparison: bool = False, return_type: Type[TypeEngine[Any]] | TypeEngine[Any] | None = None, python_impl: Callable[..., Any] | None = None) Callable[[Any], Operators]

inherited from the Operators.op() method of Operators

Produce a generic operator function.

e.g.:

somecolumn.op("*")(5)

produces:

somecolumn * 5

This function can also be used to make bitwise operators explicit. For example:

somecolumn.op("&")(0xFF)

is a bitwise AND of the value in somecolumn.

参数:
  • opstring – 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 (,) and AS operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators.

    参见

    我正在使用 op() 生成自定义运算符,但我的括号无法正确显示 - 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 the Operators.bool_op() method instead; this more succinct operator sets this parameter automatically, but also provides correct PEP 484 typing support as the returned object will express a “boolean” datatype, i.e. BinaryExpression[bool].

  • return_type – a TypeEngine class or object that will force the return type of an expression produced by this operator to be of that type. By default, operators that specify Operators.op.is_comparison will resolve to Boolean, and those that do not will be of the same type as the left-hand operand.

  • python_impl

    an optional Python function that can evaluate two Python values in the same way as this operator works when run on the database server. Useful for in-Python SQL expression evaluation functions, such as for ORM hybrid attributes, and the ORM “evaluator” used to match objects in a session after a multi-row update or delete.

    e.g.:

    >>> expr = column("x").op("+", python_impl=lambda a, b: a + b)("y")

    The operator for the above expression will also work for non-SQL left and right objects:

    >>> expr.operator(5, 10)
    15

    在 2.0 版本加入.

method sqlalchemy.schema.Column.operate(op: OperatorType, *other: Any, **kwargs: Any) ColumnElement[Any]

inherited from the ColumnElement.operate() method of ColumnElement

Operate on an argument.

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

Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding ColumnOperators to apply func.lower() to the left and right side:

class MyComparator(ColumnOperators):
    def operate(self, op, other, **kwargs):
        return op(func.lower(self), func.lower(other), **kwargs)
参数:
  • op – Operator callable.

  • *other – the ‘other’ side of the operation. Will be a single scalar for most operations.

  • **kwargs – modifiers. These may be passed by special operators such as ColumnOperators.contains().

method sqlalchemy.schema.Column.params(*optionaldict, **kwargs)

inherited from the Immutable.params() method of Immutable

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.Column.proxy_set: util.generic_fn_descriptor[FrozenSet[Any]]

inherited from the ColumnElement.proxy_set attribute of ColumnElement

set of all columns we are proxying

as of 2.0 this is explicitly deannotated columns. previously it was effectively deannotated columns but wasn’t enforced. annotated columns should basically not go into sets if at all possible because their hashing behavior is very non-performant.

method sqlalchemy.schema.Column.references(column: Column[Any]) bool

Return True if this Column references the given column via foreign key.

method sqlalchemy.schema.Column.regexp_match(pattern: Any, flags: str | None = None) ColumnOperators

inherited from the ColumnOperators.regexp_match() method of ColumnOperators

Implements a database-specific ‘regexp match’ operator.

E.g.:

stmt = select(table.c.some_column).where(
    table.c.some_column.regexp_match("^(b|c)")
)

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 or x !~ y when negated.

  • Oracle Database - renders REGEXP_LIKE(x, y)

  • SQLite - uses SQLite’s REGEXP placeholder operator and calls into the Python re.match() builtin.

  • other backends may provide special implementations.

  • Backends without any special implementation will emit the operator as “REGEXP” or “NOT REGEXP”. This is compatible with SQLite and MySQL, for example.

Regular expression support is currently implemented for Oracle Database, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary.

参数:
  • 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.

在 1.4 版本加入.

在 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.

method sqlalchemy.schema.Column.regexp_replace(pattern: Any, replacement: Any, flags: str | None = None) ColumnOperators

inherited from the ColumnOperators.regexp_replace() method of ColumnOperators

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 function REGEXP_REPLACE(). However, the specific regular expression syntax and flags available are not backend agnostic.

Regular expression replacement support is currently implemented for Oracle Database, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary.

参数:
  • 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.

在 1.4 版本加入.

在 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.

method sqlalchemy.schema.Column.reverse_operate(op: OperatorType, other: Any, **kwargs: Any) ColumnElement[Any]

inherited from the ColumnElement.reverse_operate() method of ColumnElement

Reverse operate on an argument.

Usage is the same as operate().

method sqlalchemy.schema.Column.self_group(against: OperatorType | None = None) ColumnElement[Any]

inherited from the ColumnElement.self_group() method of ColumnElement

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 another select(). (Note that subqueries should be normally created using the Select.alias() method, as many platforms require nested SELECT statements to be named).

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

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

method sqlalchemy.schema.Column.shares_lineage(othercolumn: ColumnElement[Any]) bool

inherited from the ColumnElement.shares_lineage() method of ColumnElement

Return True if the given ColumnElement has a common ancestor to this ColumnElement.

method sqlalchemy.schema.Column.startswith(other: Any, escape: str | None = None, autoescape: bool = False) ColumnOperators

inherited from the ColumnOperators.startswith() method of ColumnOperators

Implement the startswith operator.

Produces a LIKE expression that tests against a match for the start of a string value:

column LIKE <other> || '%'

E.g.:

stmt = select(sometable).where(sometable.c.column.startswith("foobar"))

Since the operator uses LIKE, wildcard characters "%" and "_" that are present inside the <other> expression will behave like wildcards as well. For literal string values, the ColumnOperators.startswith.autoescape flag may be set to True to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the ColumnOperators.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.

参数:
  • other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters % and _ are not escaped by default unless the ColumnOperators.startswith.autoescape flag is set to True.

  • autoescape

    boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of "%", "_" and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

    An expression such as:

    somecolumn.startswith("foo%bar", autoescape=True)

    Will render as:

    somecolumn LIKE :param || '%' ESCAPE '/'

    With the value of :param as "foo/%bar".

  • escape

    a character which when given will render with the ESCAPE keyword to establish that character as the escape character. This character can then be placed preceding occurrences of % and _ to allow them to act as themselves and not wildcard characters.

    An expression such as:

    somecolumn.startswith("foo/%bar", escape="^")

    Will render as:

    somecolumn LIKE :param || '%' ESCAPE '^'

    The parameter may also be combined with 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: Literal[None] = None

inherited from the ColumnOperators.timetuple attribute of ColumnOperators

Hack, allows datetime objects to be compared on the LHS.

attribute sqlalchemy.schema.Column.unique: bool | None

The value of the Column.unique parameter.

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

method sqlalchemy.schema.Column.unique_params(*optionaldict, **kwargs)

inherited from the Immutable.unique_params() method of Immutable

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.

class sqlalchemy.schema.MetaData

A collection of Table objects and their associated schema constructs.

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

The Table objects themselves are stored in the MetaData.tables dictionary.

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

参见

使用元数据描述数据库 - Introduction to database metadata

Class signature

class sqlalchemy.schema.MetaData (sqlalchemy.schema.HasSchemaAttr)

method sqlalchemy.schema.MetaData.__init__(schema: str | None = None, quote_schema: bool | None = None, naming_convention: _NamingSchemaParameter | None = None, info: _InfoType | None = None) None

Create a new MetaData object.

参数:
  • schema

    The default schema to use for the Table, Sequence, and potentially other objects associated with this MetaData. Defaults to None.

  • quote_schema – Sets the quote_schema flag for those Table, Sequence, and other objects which make usage of the local schema name.

  • info – Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.

  • naming_convention

    a dictionary referring to values which will establish default naming conventions for Constraint and Index 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, the Index class

    • a 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 form fn(constraint, table), which accepts the constraint/index object and Table 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 the Table object associated with the constraint.

    • %(referred_table_name)s - the name of the Table object associated with the referencing target of a ForeignKeyConstraint.

    • %(column_0_name)s - the name of the Column at index position “0” within the constraint.

    • %(column_0N_name)s - the name of all Column objects in order within the constraint, joined without a separator.

    • %(column_0_N_name)s - the name of all Column 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 zeroth Column or all Columns, separated with or without an underscore

    • %(column_0_key)s, %(column_0N_key)s, %(column_0_N_key)s - the key of either the zeroth Column or all Columns, 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 a ForeignKeyConstraint.

    • %(constraint_name)s - a special key that refers to the existing name given to the constraint. When this key is present, the Constraint 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 the Constraint 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.

    参见

    配置约束命名约定 - for detailed usage examples.

method sqlalchemy.schema.MetaData.clear() None

Clear all Table objects from this MetaData.

method sqlalchemy.schema.MetaData.create_all(bind: _CreateDropBind, tables: _typing_Sequence[Table] | None = None, checkfirst: bool = True) None

Create all tables stored in this metadata.

Conditional by default, will not attempt to recreate tables already present in the target database.

参数:
  • bind – A Connection or Engine used to access the database.

  • tables – Optional list of Table objects, which is a subset of the total tables in the MetaData (others are ignored).

  • checkfirst – Defaults to True, don’t issue CREATEs for tables already present in the target database.

method sqlalchemy.schema.MetaData.drop_all(bind: _CreateDropBind, tables: _typing_Sequence[Table] | None = None, checkfirst: bool = True) None

Drop all tables stored in this metadata.

Conditional by default, will not attempt to drop tables not present in the target database.

参数:
  • bind – A Connection or Engine used to access the database.

  • tables – Optional list of Table objects, which is a subset of the total tables in the MetaData (others are ignored).

  • checkfirst – Defaults to True, only issue DROPs for tables confirmed to be present in the target database.

method sqlalchemy.schema.MetaData.reflect(bind: Engine | Connection, schema: str | None = None, views: bool = False, only: _typing_Sequence[str] | Callable[[str, MetaData], bool] | None = None, extend_existing: bool = False, autoload_replace: bool = True, resolve_fks: bool = True, **dialect_kwargs: Any) None

Load all available table definitions from the database.

Automatically creates Table entries in this MetaData for any table available in the database but not yet present in the MetaData. May be called multiple times to pick up tables recently added to the database, however no special action is taken if a table in this MetaData no longer exists in the database.

参数:
  • bind – A Connection or Engine used to access the database.

  • 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 (materialized and plain).

  • only

    Optional. Load only a sub-set of available named tables. May be specified as a sequence of names or a callable.

    If a sequence of names is provided, only those tables will be reflected. An error is raised if a table is requested but not available. Named tables already present in this MetaData are ignored.

    If a callable is provided, it will be used as a boolean predicate to filter the list of potential table names. The callable is called with a table name and this MetaData instance as positional arguments and should return a true value for any table to reflect.

  • extend_existing – Passed along to each Table as Table.extend_existing.

  • autoload_replace – Passed along to each Table as Table.autoload_replace.

  • resolve_fks

    if True, reflect Table objects linked to ForeignKey objects located in each Table. For MetaData.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 the MetaData.reflect.only parameter. When False, ForeignKey objects are not followed to the Table in which they link, however if the related table is also part of the list of tables that would be reflected in any case, the ForeignKey object will still resolve to its related Table after the MetaData.reflect() operation is complete. Defaults to True.

  • **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.

参见

反射数据库对象

DDLEvents.column_reflect() - Event used to customize the reflected columns. Usually used to generalize the types using TypeEngine.as_generic()

使用与数据库无关的类型进行反射 - describes how to reflect tables using general types.

method sqlalchemy.schema.MetaData.remove(table: Table) None

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 the reversed() Python built-in.

警告

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, the sort_tables_and_constraints() function will automatically return foreign key constraints in a separate collection when cycles are detected so that they may be applied to a schema separately.

attribute sqlalchemy.schema.MetaData.tables: util.FacadeDict[str, Table]

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 no Table.schema attribute, this is the same as Table.name. For a table with a schema, it is typically of the form schemaname.tablename.

class sqlalchemy.schema.SchemaConst

Class signature

class sqlalchemy.schema.SchemaConst (enum.Enum)

attribute sqlalchemy.schema.SchemaConst.BLANK_SCHEMA = 2

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

attribute sqlalchemy.schema.SchemaConst.NULL_UNSPECIFIED = 3

Symbol indicating the “nullable” keyword was not passed to a Column.

This is used to distinguish between the use case of passing nullable=None to a Column, which has special meaning on some backends such as SQL Server.

attribute sqlalchemy.schema.SchemaConst.RETAIN_SCHEMA = 1

Symbol indicating that a Table, Sequence or in some cases a ForeignKey object, in situations where the object is being copied for a Table.to_metadata() operation, should retain the schema name that it already has.

class sqlalchemy.schema.SchemaItem

Base class for items that define a database schema.

Members

info

Class signature

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

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 and Column.

function sqlalchemy.schema.insert_sentinel(name: str | None = None, type_: _TypeEngineArgument[_T] | None = None, *, default: Any | None = None, omit_from_statements: bool = True) Column[Any]

Provides a surrogate Column that will act as a dedicated insert sentinel column, allowing efficient bulk inserts with deterministic RETURNING sorting for tables that don’t otherwise have qualifying primary key configurations.

Adding this column to a Table object requires that a corresponding database table actually has this column present, so if adding it to an existing model, existing database tables would need to be migrated (e.g. using ALTER TABLE or similar) to include this column.

For background on how this object is used, see the section 配置 Sentinel 列 as part of the section INSERT 语句的“插入多个值”行为.

The Column returned will be a nullable integer column by default and make use of a sentinel-specific default generator used only in “insertmanyvalues” operations.

在 2.0.10 版本加入.

class sqlalchemy.schema.Table

Represent a table in a database.

e.g.:

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

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

参见

使用元数据描述数据库 - Introduction to database metadata

Class signature

class sqlalchemy.schema.Table (sqlalchemy.sql.base.DialectKWArgs, sqlalchemy.schema.HasSchemaAttr, sqlalchemy.sql.expression.TableClause, sqlalchemy.inspection.Inspectable)

method sqlalchemy.schema.Table.__init__(name: str, metadata: MetaData, *args: SchemaItem, schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None, quote: bool | None = None, quote_schema: bool | None = None, autoload_with: Engine | Connection | None = None, autoload_replace: bool = True, keep_existing: bool = False, extend_existing: bool = False, resolve_fks: bool = True, include_columns: Collection[str] | None = None, implicit_returning: bool = True, comment: str | None = None, info: Dict[Any, Any] | None = None, listeners: _typing_Sequence[Tuple[str, Callable[..., Any]]] | None = None, prefixes: _typing_Sequence[str] | None = None, _extend_on: Set[Table] | None = None, _no_init: bool = True, **kw: Any) None

Constructor for Table.

参数:
  • name

    The name of this table as represented in the database.

    The table name, along with the value of the schema parameter, forms a key which uniquely identifies this Table within the owning MetaData collection. Additional calls to Table with the same name, metadata, and schema name will return the same Table object.

    Names which contain no upper case characters will be treated as case insensitive names, and will not be quoted unless they are a reserved word or contain special characters. A name with any number of upper case characters is considered to be case sensitive, and will be sent as quoted.

    To enable unconditional quoting for the table name, specify the flag quote=True to the constructor, or use the quoted_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 particular Connection or Engine.

  • *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, other SchemaItem constructs may be added here, including PrimaryKeyConstraint, and ForeignKeyConstraint.

  • autoload_replace

    Defaults to True; when using Table.autoload_with in conjunction with Table.extend_existing, indicates that Column objects present in the already-existing Table object should be replaced with columns of the same name retrieved from the autoload process. When False, columns already present under existing names will be omitted from the reflection process.

    Note that this setting does not impact Column objects specified programmatically within the call to Table that also is autoloading; those Column objects will always replace existing columns of the same name when Table.extend_existing is True.

  • autoload_with

    An Engine or Connection object, or a Inspector object as returned by inspect() against one, with which this Table object will be reflected. When set to a non-None value, the autoload process will take place for this table against the given engine or connection.

  • extend_existing

    When True, indicates that if this Table is already present in the given MetaData, apply further arguments within the constructor to the existing Table.

    If Table.extend_existing or Table.keep_existing are not set, and the given name of the new Table refers to a Table that is already present in the target MetaData collection, and this Table specifies additional columns or other constructs or flags that modify the table’s state, an error is raised. The purpose of these two mutually-exclusive flags is to specify what action should be taken when a Table is specified that matches an existing Table, yet specifies additional constructs.

    Table.extend_existing will also work in conjunction with Table.autoload_with to run a new reflection operation against the database, even if a Table of the same name is already present in the target MetaData; newly reflected Column objects and other options will be added into the state of the Table, potentially overwriting existing columns and options of the same name.

    As is always the case with Table.autoload_with, Column objects can be specified in the same Table constructor, which will take precedence. Below, the existing table mytable will be augmented with Column objects both reflected from the database, as well as the given Column named “y”:

    Table(
        "mytable",
        metadata,
        Column("y", Integer),
        extend_existing=True,
        autoload_with=engine,
    )

  • implicit_returning

    True by default - indicates that RETURNING can be used, typically by the ORM, in order to fetch server-generated values such as primary key values and server side defaults, on those backends which support RETURNING.

    In modern SQLAlchemy there is generally no reason to alter this setting, except for some backend specific cases (see 触发器 in the SQL Server dialect documentation for one such example).

  • include_columns – A list of strings indicating a subset of columns to be loaded via the autoload operation; table columns who aren’t present in this list will not be represented on the resulting Table object. Defaults to None which indicates all columns should be reflected.

  • resolve_fks

    Whether or not to reflect Table objects related to this one via ForeignKey objects, when Table.autoload_with is specified. Defaults to True. Set to False to disable reflection of related tables as ForeignKey objects are encountered; may be used either to save on SQL calls or to avoid issues with related tables that can’t be accessed. Note that if a related table is already present in the MetaData collection, or becomes present later, a ForeignKey object associated with this Table will resolve to that table normally.

  • info – Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.

  • keep_existing

    When True, indicates that if this Table is already present in the given MetaData, ignore further arguments within the constructor to the existing Table, and return the Table object as originally created. This is to allow a function that wishes to define a new Table on first call, but on subsequent calls will return the same Table, without any of the declarations (particularly constraints) being applied a second time.

    If Table.extend_existing or Table.keep_existing are not set, and the given name of the new Table refers to a Table that is already present in the target MetaData collection, and this Table specifies additional columns or other constructs or flags that modify the table’s state, an error is raised. The purpose of these two mutually-exclusive flags is to specify what action should be taken when a Table is specified that matches an existing Table, yet specifies additional constructs.

  • listeners

    A list of tuples of the form (<eventname>, <fn>) which will be passed to listen() upon construction. This alternate hook to listen() allows the establishment of a listener function specific to this Table before the “autoload” process begins. Historically this has been intended for use with the DDLEvents.column_reflect() event, however note that this event hook may now be associated with the MetaData 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)],
    )

  • must_exist – When True, indicates that this Table must already be present in the given MetaData collection, else an exception is raised.

  • prefixes – A list of strings to insert after CREATE in the CREATE TABLE statement. They will be separated by spaces.

  • quote

    Force quoting of this table’s name on or off, corresponding to True or False. When left at its default of None, the column identifier will be quoted according to whether the name is case sensitive (identifiers with at least one upper case character are treated as case sensitive), or if it’s a reserved word. This flag is only needed to force quoting of a reserved word which is not known by the SQLAlchemy dialect.

    备注

    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 this Table specifies its own MetaData.schema parameter, then that schema name will be applied to this Table if the schema parameter here is set to None. To set a blank schema name on a Table that would otherwise use the schema set on the owning MetaData, specify the special symbol BLANK_SCHEMA.

    The quoting rules for the schema name are the same as those for the name parameter, in that quoting is applied for reserved words or case-sensitive names; to enable unconditional quoting for the schema name, specify the flag quote_schema=True to the constructor, or use the quoted_name construct to specify the name.

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

  • **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.

method sqlalchemy.schema.Table.add_is_dependent_on(table: Table) None

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: str | None = None, flat: bool = False) NamedFromClause

inherited from the FromClause.alias() method of FromClause

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: ColumnClause[Any], replace_existing: bool = False) None

Append a Column to this Table.

The “key” of the newly added Column, i.e. the value of its .key attribute, will then be available in the .c collection of this Table, and the column definition will be included in any CREATE TABLE, SELECT, UPDATE, etc. statements generated from this Table 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.

参数:

replace_existing

When True, allows replacing existing columns. When False, the default, an warning will be raised if a column with the same .key already exists. A future version of sqlalchemy will instead rise a warning.

在 1.4.0 版本加入.

method sqlalchemy.schema.Table.append_constraint(constraint: Index | Constraint) None

Append a Constraint to this Table.

This has the effect of the constraint being included in any future CREATE TABLE statement, assuming specific DDL creation events have not been associated with the given 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 of DialectKWArgs

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

E.g.:

Index.argument_for("mydialect", "length", None)

some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

参数:
  • dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.

  • argument_name – name of the parameter.

  • default – default value of the parameter.

attribute sqlalchemy.schema.Table.autoincrement_column

Returns the Column object which currently represents the “auto increment” column, if any, else returns None.

This is based on the rules for Column as defined by the Column.autoincrement parameter, which generally means the column within a single integer column primary key constraint that is not constrained by a foreign key. If the table does not have such a primary key constraint, then there’s no “autoincrement” column. A Table may have only one column defined as the “autoincrement” column.

在 2.0.4 版本加入.

attribute sqlalchemy.schema.Table.c

inherited from the FromClause.c attribute of FromClause

A synonym for FromClause.columns

返回:

a ColumnCollection

attribute sqlalchemy.schema.Table.columns

inherited from the FromClause.columns attribute of FromClause

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

The columns, or c collection, is the gateway to the construction of SQL expressions using table-bound or other selectable-bound columns:

select(mytable).where(mytable.c.somecolumn == 5)
返回:

a ColumnCollection object.

method sqlalchemy.schema.Table.compare(other: ClauseElement, **kw: Any) bool

inherited from the ClauseElement.compare() method of ClauseElement

Compare this ClauseElement to the given ClauseElement.

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

**kw are arguments consumed by subclass compare() methods and may be used to modify the criteria for comparison (see ColumnElement).

method sqlalchemy.schema.Table.compile(bind: _HasDialect | None = None, dialect: Dialect | None = None, **kw: Any) Compiled

inherited from the CompilerElement.compile() method of CompilerElement

Compile this SQL expression.

The return value is a Compiled object. Calling str() or unicode() on the returned value will yield a string representation of the result. The Compiled object also can return a dictionary of bind parameter names and values using the params accessor.

参数:
  • bind – An Connection or Engine which can provide a Dialect in order to generate a Compiled object. If the bind and dialect parameters are both omitted, a default SQL compiler is used.

  • 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 which can generate a Compiled object. This argument takes precedence over the bind argument.

  • 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}))

attribute sqlalchemy.schema.Table.constraints: Set[Constraint]

A collection of all Constraint objects associated with this Table.

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

method sqlalchemy.schema.Table.corresponding_column(column: KeyedColumnElement[Any], require_embedded: bool = False) KeyedColumnElement[Any] | None

inherited from the Selectable.corresponding_column() method of Selectable

Given a ColumnElement, return the exported ColumnElement object from the Selectable.exported_columns collection of this Selectable which corresponds to that original ColumnElement via a common ancestor column.

参数:
  • column – the target ColumnElement to be matched.

  • require_embedded – only return corresponding columns for the given ColumnElement, if the given ColumnElement is actually present within a sub-element of this Selectable. Normally the column will match if it merely shares a common ancestor with one of the exported columns of this Selectable.

参见

Selectable.exported_columns - the ColumnCollection that is used for the operation.

ColumnCollection.corresponding_column() - implementation method.

method sqlalchemy.schema.Table.create(bind: _CreateDropBind, checkfirst: bool = False) None

Issue a CREATE statement for this Table, using the given Connection or Engine for connectivity.

method sqlalchemy.schema.Table.delete() Delete

inherited from the TableClause.delete() method of TableClause

Generate a delete() construct against this TableClause.

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 of TableClause

attribute sqlalchemy.schema.Table.dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of DialectKWArgs

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

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

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

参见

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.schema.Table.dialect_options

inherited from the DialectKWArgs.dialect_options attribute of DialectKWArgs

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

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

在 0.9.2 版本加入.

参见

DialectKWArgs.dialect_kwargs - flat dictionary form

method sqlalchemy.schema.Table.drop(bind: _CreateDropBind, checkfirst: bool = False) None

Issue a DROP statement for this Table, using the given Connection or Engine for connectivity.

attribute sqlalchemy.schema.Table.entity_namespace

inherited from the FromClause.entity_namespace attribute of FromClause

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.

attribute sqlalchemy.schema.Table.exported_columns

inherited from the FromClause.exported_columns attribute of FromClause

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

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

在 1.4 版本加入.

attribute sqlalchemy.schema.Table.foreign_key_constraints

ForeignKeyConstraint objects referred to by this Table.

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 of FromClause

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

Each ForeignKey is a member of a Table-wide ForeignKeyConstraint.

method sqlalchemy.schema.Table.get_children(*, omit_attrs: Tuple[str, ...] = (), **kw: Any) Iterable[HasTraverseInternals]

inherited from the HasTraverseInternals.get_children() method of HasTraverseInternals

Return immediate child HasTraverseInternals elements of this HasTraverseInternals.

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 of TableClause

TableClause doesn’t support having a primary key or column -level defaults, so implicit returning doesn’t apply.

attribute sqlalchemy.schema.Table.indexes: Set[Index]

A collection of all Index objects associated with this Table.

attribute sqlalchemy.schema.Table.info

inherited from the SchemaItem.info attribute of SchemaItem

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 and Column.

attribute sqlalchemy.schema.Table.inherit_cache: bool | None = None

inherited from the HasCacheKey.inherit_cache attribute of HasCacheKey

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 to False, 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.

参见

为自定义构造启用缓存支持 - General guideslines for setting the HasCacheKey.inherit_cache attribute for third-party or user defined SQL constructs.

method sqlalchemy.schema.Table.insert() Insert

inherited from the TableClause.insert() method of TableClause

Generate an Insert construct against this TableClause.

E.g.:

table.insert().values(name="foo")

See insert() for argument and usage information.

method sqlalchemy.schema.Table.is_derived_from(fromclause: FromClause | None) bool

inherited from the FromClause.is_derived_from() method of FromClause

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

An example would be an Alias of a Table is derived from that Table.

method sqlalchemy.schema.Table.join(right: _FromClauseArgument, onclause: _ColumnExpressionArgument[bool] | None = None, isouter: bool = False, full: bool = False) Join

inherited from the FromClause.join() method of FromClause

Return a Join from this FromClause to another FromClause.

E.g.:

from sqlalchemy import join

j = user_table.join(
    address_table, user_table.c.id == address_table.c.user_id
)
stmt = select(user_table).select_from(j)

would emit SQL along the lines of:

SELECT user.id, user.name FROM user
JOIN address ON user.id = address.user_id
参数:
  • right – the right side of the join; this is any FromClause object such as a Table object, and may also be a selectable-compatible object such as an ORM-mapped class.

  • onclause – a SQL expression representing the ON clause of the join. If left at None, 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.

参见

join() - standalone function

Join - the type of object produced

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 of Table.name for a table with no Table.schema set; otherwise it is typically of the form schemaname.tablename.

attribute sqlalchemy.schema.Table.kwargs

inherited from the DialectKWArgs.kwargs attribute of DialectKWArgs

A synonym for DialectKWArgs.dialect_kwargs.

method sqlalchemy.schema.Table.lateral(name: str | None = None) LateralFromClause

inherited from the Selectable.lateral() method of Selectable

Return a LATERAL alias of this Selectable.

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

参见

LATERAL 相关 - overview of usage.

method sqlalchemy.schema.Table.outerjoin(right: _FromClauseArgument, onclause: _ColumnExpressionArgument[bool] | None = None, full: bool = False) Join

inherited from the FromClause.outerjoin() method of FromClause

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

E.g.:

from sqlalchemy import outerjoin

j = user_table.outerjoin(
    address_table, user_table.c.id == address_table.c.user_id
)

The above is equivalent to:

j = user_table.join(
    address_table, user_table.c.id == address_table.c.user_id, isouter=True
)
参数:
  • right – the right side of the join; this is any FromClause object such as a Table object, and may also be a selectable-compatible object such as an ORM-mapped class.

  • onclause – a SQL expression representing the ON clause of the join. If left at None, 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.

method sqlalchemy.schema.Table.params(*optionaldict, **kwargs)

inherited from the Immutable.params() method of Immutable

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 of FromClause

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 the PrimaryKeyConstraint which itself is an iterable collection of Column objects.

method sqlalchemy.schema.Table.replace_selectable(old: FromClause, alias: Alias) Self

inherited from the Selectable.replace_selectable() method of Selectable

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

自 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: str | None = None

inherited from the FromClause.schema attribute of FromClause

Define the ‘schema’ attribute for this FromClause.

This is typically None for most objects except that of Table, where it is taken as the value of the Table.schema argument.

method sqlalchemy.schema.Table.select() Select

inherited from the FromClause.select() method of FromClause

Return a SELECT of this FromClause.

e.g.:

stmt = some_table.select().where(some_table.c.id == 5)

参见

select() - general purpose method which allows for arbitrary column lists.

method sqlalchemy.schema.Table.self_group(against: OperatorType | None = None) ClauseElement

inherited from the ClauseElement.self_group() method of ClauseElement

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 another select(). (Note that subqueries should be normally created using the Select.alias() method, as many platforms require nested SELECT statements to be named).

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

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

method sqlalchemy.schema.Table.table_valued() TableValuedColumn[Any]

inherited from the NamedFromClause.table_valued() method of NamedFromClause

Return a TableValuedColumn object for this FromClause.

A TableValuedColumn is a ColumnElement 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 Database 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

在 1.4.0b2 版本加入.

method sqlalchemy.schema.Table.tablesample(sampling: float | Function[Any], name: str | None = None, seed: roles.ExpressionElementRole[Any] | None = None) TableSample

inherited from the FromClause.tablesample() method of FromClause

Return a TABLESAMPLE alias of this FromClause.

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

参见

tablesample() - usage guidelines and parameters

method sqlalchemy.schema.Table.to_metadata(metadata: MetaData, schema: str | Literal[SchemaConst.RETAIN_SCHEMA] = SchemaConst.RETAIN_SCHEMA, referred_schema_fn: Callable[[Table, str | None, ForeignKeyConstraint, str | None], str | None] | None = None, name: str | None = None) Table

Return a copy of this Table associated with a different MetaData.

E.g.:

m1 = MetaData()

user = Table("user", m1, Column("id", Integer, primary_key=True))

m2 = MetaData()
user_copy = user.to_metadata(m2)

在 1.4 版本发生变更: The Table.to_metadata() function was renamed from Table.tometadata().

参数:
  • metadata – Target MetaData object, into which the new Table object will be created.

  • schema

    optional string name indicating the target schema. Defaults to the special symbol RETAIN_SCHEMA which indicates that no change to the schema name should be made in the new Table. If set to a string name, the new Table will have this new name as the .schema. If set to None, the schema will be set to that of the schema set on the target MetaData, which is typically None as well, unless set explicitly:

    m2 = MetaData(schema="newschema")
    
    # user_copy_one will have "newschema" as the schema name
    user_copy_one = user.to_metadata(m2, schema=None)
    
    m3 = MetaData()  # schema defaults to None
    
    # user_copy_two will have None as the schema name
    user_copy_two = user.to_metadata(m3, schema=None)

  • referred_schema_fn

    optional callable which can be supplied in order to provide for the schema name that should be assigned to the referenced table of a ForeignKeyConstraint. The callable accepts this parent Table, the target schema that we are changing to, the ForeignKeyConstraint 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 symbol BLANK_SCHEMA. To effect no change, return None or RETAIN_SCHEMA.

    在 1.4.33 版本发生变更: The referred_schema_fn function may return the BLANK_SCHEMA or RETAIN_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
    )

  • name – optional string name indicating the target table name. If not specified or None, the table name is retained. This allows a Table to be copied to the same MetaData target with a new name.

method sqlalchemy.schema.Table.tometadata(metadata: MetaData, schema: str | Literal[SchemaConst.RETAIN_SCHEMA] = SchemaConst.RETAIN_SCHEMA, referred_schema_fn: Callable[[Table, str | None, ForeignKeyConstraint, str | None], str | None] | None = None, name: str | None = None) Table

Return a copy of this Table associated with a different MetaData.

自 1.4 版本弃用: Table.tometadata() is renamed to Table.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 of Immutable

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() Update

inherited from the TableClause.update() method of TableClause

Generate an update() construct against this TableClause.

E.g.:

table.update().where(table.c.id == 7).values(name="foo")

See update() for argument and usage information.