反射数据库对象¶
Reflecting Database Objects
可以指示 Table
对象从数据库中已经存在的对应数据库模式对象中加载信息。这个过程称为 反射(reflection) 。在最简单的情况下,您只需要指定表名、一个 MetaData
对象和 autoload_with
参数:
>>> messages = Table("messages", metadata_obj, autoload_with=engine)
>>> [c.name for c in messages.columns]
['message_id', 'message_name', 'date']
上述操作将使用给定的引擎查询数据库中的 messages
表的信息,然后将生成与这些信息对应的 Column
、ForeignKey
和其他对象,就像 Table
对象是在Python中手工构造的一样。
当表被反射时,如果一个给定的表通过外键引用另一个表,一个代表连接的第二个 Table
对象将在 MetaData
对象中创建。下面,假设表 shopping_cart_items
引用了一个名为 shopping_carts
的表。反射 shopping_cart_items
表的效果是使 shopping_carts
表也会被加载:
>>> shopping_cart_items = Table("shopping_cart_items", metadata_obj, autoload_with=engine)
>>> "shopping_carts" in metadata_obj.tables
True
MetaData
具有有趣的“单例”行为,即如果您分别请求两个表,MetaData
将确保每个不同的表名只创建一个 Table
对象。如果一个给定名称的 Table
对象已经存在,Table
构造函数实际上会返回已经存在的 Table
对象。例如,下面我们可以通过命名它来访问已经生成的 shopping_carts
表:
shopping_carts = Table("shopping_carts", metadata_obj)
当然,无论如何,使用 autoload_with=engine
是一个好主意。这是为了确保如果表的属性尚未加载,则将加载它们。反射操作仅在表尚未加载时发生;一旦加载,对具有相同名称的新调用将不会重新发出任何反射查询。
A Table
object can be instructed to load
information about itself from the corresponding database schema object already
existing within the database. This process is called reflection. In the
most simple case you need only specify the table name, a MetaData
object, and the autoload_with
argument:
>>> messages = Table("messages", metadata_obj, autoload_with=engine)
>>> [c.name for c in messages.columns]
['message_id', 'message_name', 'date']
The above operation will use the given engine to query the database for
information about the messages
table, and will then generate
Column
, ForeignKey
,
and other objects corresponding to this information as though the
Table
object were hand-constructed in Python.
When tables are reflected, if a given table references another one via foreign
key, a second Table
object is created within the
MetaData
object representing the connection.
Below, assume the table shopping_cart_items
references a table named
shopping_carts
. Reflecting the shopping_cart_items
table has the
effect such that the shopping_carts
table will also be loaded:
>>> shopping_cart_items = Table("shopping_cart_items", metadata_obj, autoload_with=engine)
>>> "shopping_carts" in metadata_obj.tables
True
The MetaData
has an interesting “singleton-like”
behavior such that if you requested both tables individually,
MetaData
will ensure that exactly one
Table
object is created for each distinct table
name. The Table
constructor actually returns to
you the already-existing Table
object if one
already exists with the given name. Such as below, we can access the already
generated shopping_carts
table just by naming it:
shopping_carts = Table("shopping_carts", metadata_obj)
Of course, it’s a good idea to use autoload_with=engine
with the above table
regardless. This is so that the table’s attributes will be loaded if they have
not been already. The autoload operation only occurs for the table if it
hasn’t already been loaded; once loaded, new calls to
Table
with the same name will not re-issue any
reflection queries.
覆盖反射列¶
Overriding Reflected Columns
在反射表时,可以为各个列显式指定值以进行覆盖; 这对于指定自定义数据类型、在数据库中未配置的主键约束等情况非常有用:
>>> mytable = Table(
... "mytable",
... metadata_obj,
... Column(
... "id", Integer, primary_key=True
... ), # 将反射出的 'id' 列覆盖为主键
... Column("mydata", Unicode(50)), # 将反射出的 'mydata' 列覆盖为 Unicode 类型
... # 其他无需更改的列正常反射
... autoload_with=some_engine,
... )
参见
使用自定义类型和反射 - 展示了上述列覆盖技术如何与 使用自定义数据类型和表反射相结合。
Individual columns can be overridden with explicit values when reflecting tables; this is handy for specifying custom datatypes, constraints such as primary keys that may not be configured within the database, etc.:
>>> mytable = Table(
... "mytable",
... metadata_obj,
... Column(
... "id", Integer, primary_key=True
... ), # override reflected 'id' to have primary key
... Column("mydata", Unicode(50)), # override reflected 'mydata' to be Unicode
... # additional Column objects which require no change are reflected normally
... autoload_with=some_engine,
... )
参见
使用自定义类型和反射 - illustrates how the above column override technique applies to the use of custom datatypes with table reflection.
反射视图¶
Reflecting Views
反射系统也支持视图(views)的反射。基本用法与表相同:
my_view = Table("some_view", metadata, autoload_with=engine)
上述代码中,my_view
是一个 Table
对象,
其包含的 Column
对象代表视图 “some_view” 中每列的名称和类型。
通常我们希望在反射视图时至少包含主键约束(如果可能,还包括外键约束)。 但视图反射不会自动推断这些约束。
可以使用“覆盖”技术,显式指定那些作为主键或具有外键约束的列:
my_view = Table(
"some_view",
metadata,
Column("view_id", Integer, primary_key=True),
Column("related_thing", Integer, ForeignKey("othertable.thing_id")),
autoload_with=engine,
)
The reflection system can also reflect views. Basic usage is the same as that of a table:
my_view = Table("some_view", metadata, autoload_with=engine)
Above, my_view
is a Table
object with
Column
objects representing the names and types of
each column within the view “some_view”.
Usually, it’s desired to have at least a primary key constraint when reflecting a view, if not foreign keys as well. View reflection doesn’t extrapolate these constraints.
Use the “override” technique for this, specifying explicitly those columns which are part of the primary key or have foreign key constraints:
my_view = Table(
"some_view",
metadata,
Column("view_id", Integer, primary_key=True),
Column("related_thing", Integer, ForeignKey("othertable.thing_id")),
autoload_with=engine,
)
一次反射所有表¶
Reflecting All Tables at Once
MetaData
对象也可以列出所有表并进行完整反射。
可以通过调用 reflect()
方法来实现。
调用后,所有被找到的表都会出现在该 MetaData
对象的表字典中:
metadata_obj = MetaData()
metadata_obj.reflect(bind=someengine)
users_table = metadata_obj.tables["users"]
addresses_table = metadata_obj.tables["addresses"]
metadata.reflect()
也可以用于清空或删除数据库中所有表的所有行:
metadata_obj = MetaData()
metadata_obj.reflect(bind=someengine)
with someengine.begin() as conn:
for table in reversed(metadata_obj.sorted_tables):
conn.execute(table.delete())
The MetaData
object can also get a listing of
tables and reflect the full set. This is achieved by using the
reflect()
method. After calling it, all
located tables are present within the MetaData
object’s dictionary of tables:
metadata_obj = MetaData()
metadata_obj.reflect(bind=someengine)
users_table = metadata_obj.tables["users"]
addresses_table = metadata_obj.tables["addresses"]
metadata.reflect()
also provides a handy way to clear or delete all the rows in a database:
metadata_obj = MetaData()
metadata_obj.reflect(bind=someengine)
with someengine.begin() as conn:
for table in reversed(metadata_obj.sorted_tables):
conn.execute(table.delete())
反射来自其他架构的表¶
Reflecting Tables from Other Schemas
章节 指定架构名称 引入了“schema”的概念,
即数据库中的命名空间,包含表和其他对象,
这些 schema 可以显式指定。
Table
对象的 “schema”,
以及视图、索引和序列等其他对象的 schema,
可以通过 Table.schema
参数设置;
也可以通过 MetaData.schema
参数设置为整个 MetaData
对象的默认 schema。
schema 参数的使用会直接影响反射功能查找对象的位置。
例如,给定一个通过其 MetaData.schema
参数配置了默认 schema 为 “project” 的 MetaData
对象:
>>> metadata_obj = MetaData(schema="project")
MetaData.reflect()
方法将使用配置好的 .schema
进行反射:
>>> # 使用 metadata_obj 中配置的 `schema`
>>> metadata_obj.reflect(someengine)
最终结果是,来自 “project” schema 的 Table
对象将被反射,
并带有该 schema 名称作为限定名:
>>> metadata_obj.tables["project.messages"]
Table('messages', MetaData(), Column('message_id', INTEGER(), table=<messages>), schema='project')
类似地,单独的 Table
对象若提供了 Table.schema
参数,
也将从该 schema 中进行反射,优先于其所属的 MetaData
对象中配置的默认 schema:
>>> messages = Table("messages", metadata_obj, schema="project", autoload_with=someengine)
>>> messages
Table('messages', MetaData(), Column('message_id', INTEGER(), table=<messages>), schema='project')
最后,MetaData.reflect()
方法本身也支持传入
MetaData.reflect.schema
参数,
这使得我们可以从 “project” schema 中加载表,即使 MetaData
对象未设置默认 schema:
>>> metadata_obj = MetaData()
>>> metadata_obj.reflect(someengine, schema="project")
我们可以多次调用 MetaData.reflect()
,传入不同的
MetaData.schema
参数(或不传)来继续为 MetaData
对象添加更多对象:
>>> # 添加来自 "customer" schema 的表
>>> metadata_obj.reflect(someengine, schema="customer")
>>> # 添加来自默认 schema 的表
>>> metadata_obj.reflect(someengine)
The section 指定架构名称 introduces the concept of table
schemas, which are namespaces within a database that contain tables and other
objects, and which can be specified explicitly. The “schema” for a
Table
object, as well as for other objects like views, indexes and
sequences, can be set up using the Table.schema
parameter,
and also as the default schema for a MetaData
object using the
MetaData.schema
parameter.
The use of this schema parameter directly affects where the table reflection
feature will look when it is asked to reflect objects. For example, given
a MetaData
object configured with a default schema name
“project” via its MetaData.schema
parameter:
>>> metadata_obj = MetaData(schema="project")
The MetaData.reflect()
will then utilize that configured .schema
for reflection:
>>> # uses `schema` configured in metadata_obj
>>> metadata_obj.reflect(someengine)
The end result is that Table
objects from the “project”
schema will be reflected, and they will be populated as schema-qualified
with that name:
>>> metadata_obj.tables["project.messages"]
Table('messages', MetaData(), Column('message_id', INTEGER(), table=<messages>), schema='project')
Similarly, an individual Table
object that includes the
Table.schema
parameter will also be reflected from that
database schema, overriding any default schema that may have been configured on the
owning MetaData
collection:
>>> messages = Table("messages", metadata_obj, schema="project", autoload_with=someengine)
>>> messages
Table('messages', MetaData(), Column('message_id', INTEGER(), table=<messages>), schema='project')
Finally, the MetaData.reflect()
method itself also allows a
MetaData.reflect.schema
parameter to be passed, so we
could also load tables from the “project” schema for a default configured
MetaData
object:
>>> metadata_obj = MetaData()
>>> metadata_obj.reflect(someengine, schema="project")
We can call MetaData.reflect()
any number of times with different
MetaData.schema
arguments (or none at all) to continue
populating the MetaData
object with more objects:
>>> # add tables from the "customer" schema
>>> metadata_obj.reflect(someengine, schema="customer")
>>> # add tables from the default schema
>>> metadata_obj.reflect(someengine)
架构限定反射与默认架构的交互¶
Interaction of Schema-qualified Reflection with the Default Schema
小节最佳实践总结
本节讨论了 SQLAlchemy 在处理数据库会话中“默认 schema”下可见的表时的反射行为,
以及这些表如何与包含显式 schema 的 SQLAlchemy 指令交互。最佳实践是:
确保数据库的“默认” schema 仅是一个单独的名称,而不是多个名称组成的列表;
对于那些属于该“默认” schema,且在 DDL 和 SQL 中可以不加限定名直接引用的表,
应将相应的 Table.schema
及类似的 schema 参数保留为默认值 None
。
如 使用元数据指定默认架构名称 中所述,具有 schema 概念的数据库通常也包含“默认 schema”的概念。 这是因为,在常见情况下引用表对象时不写 schema 名, 但支持 schema 的数据库仍然会认为该表存在于某个 schema 中。 例如,PostgreSQL 更进一步引入了 schema 搜索路径 的概念,在一个数据库会话中,可以有多个 schema 被视为“隐式”的; 此时引用这些 schema 中的表名时不需要指定 schema 名(当然,指定了 schema 名也完全可以接受)。
由于大多数关系型数据库都支持一种表对象既可以使用 schema 限定名,也可以以“隐式”方式(无 schema)引用,
这就为 SQLAlchemy 的反射功能带来了复杂性。
在以 schema 限定方式反射一个表时,
会将其 Table.schema
属性设置为相应的 schema,
并以 schema 限定方式存储在 MetaData.tables
集合中;
而以非 schema 限定方式反射同一个表,则会以未限定 schema 的方式存储在该集合中。
最终结果是:在同一个 MetaData
集合中,会存在两个表示数据库中同一张表的 Table
对象。
为了说明这一问题的影响,考虑前例中的 “project” schema 中的表,
假设该 schema 是数据库连接的默认 schema,
或者在 PostgreSQL 中,假设 “project” 已被设置到 PostgreSQL 的 search_path
中。
这样一来,数据库会接受以下两个 SQL 语句作为等价的:
-- 带 schema 限定
SELECT message_id FROM project.messages
-- 无 schema 限定
SELECT message_id FROM messages
在数据库中,这不是问题,因为表可以通过这两种方式找到。
但在 SQLAlchemy 中,SQL 语句的语义取决于 Table
对象的“标识”。
根据 SQLAlchemy 当前的决策,如果我们同时以 schema 限定方式和非限定方式反射同一张 “messages” 表,
将会得到两个 不会 被视为语义等价的 Table
对象:
>>> # 以非 schema 限定方式反射
>>> messages_table_1 = Table("messages", metadata_obj, autoload_with=someengine)
>>> # 以 schema 限定方式反射
>>> messages_table_2 = Table(
... "messages", metadata_obj, schema="project", autoload_with=someengine
... )
>>> # 两个不同的对象
>>> messages_table_1 is messages_table_2
False
>>> # 分别存储在不同位置
>>> metadata.tables["messages"] is messages_table_1
True
>>> metadata.tables["project.messages"] is messages_table_2
True
当被反射的表包含对其他表的外键引用时,上述问题将更加复杂。
假设 “messages” 表有一个 “project_id” 列引用了同一 schema 下的另一张表 “projects”,
这意味着在 “messages” 表的定义中存在一个 ForeignKeyConstraint
约束对象。
此时,我们可能在一个 MetaData
集合中拥有多达四个 Table
对象来表示数据库中的这两张表,
其中一部分可能是反射过程中自动生成的;
这是因为反射过程中遇到外键时会自动反射被引用的表。
在为该被引用表分配 schema 时,SQLAlchemy 的逻辑是:
如果外键所属的 Table
对象未指定 schema,
且被引用表与当前表在同一个 schema 中,则反射的 ForeignKeyConstraint
对象将 省略 schema;
否则将 包含 schema。
最常见的情况是:以 schema 限定方式反射某张表后, 其关联表也会以 schema 限定方式被反射:
>>> # 以 schema 限定方式反射 "messages"
>>> messages_table_1 = Table(
... "messages", metadata_obj, schema="project", autoload_with=someengine
... )
上述 messages_table_1
中的 projects
表也将以 schema 限定方式被引用。
该 “projects” 表会因为 “messages” 引用了它而被自动反射:
>>> messages_table_1.c.project_id
Column('project_id', INTEGER(), ForeignKey('project.projects.project_id'), table=<messages>)
如果代码的其他部分又以非 schema 限定方式反射了 “projects” 表, 就会存在两个不相同的 “projects” 表对象:
>>> # 以非 schema 限定方式反射 "projects"
>>> projects_table_1 = Table("projects", metadata_obj, autoload_with=someengine)
>>> # messages_table_1 并不引用 projects_table_1
>>> messages_table_1.c.project_id.references(projects_table_1.c.project_id)
False
>>> # 它引用的是这个
>>> projects_table_2 = metadata_obj.tables["project.projects"]
>>> messages_table_1.c.project_id.references(projects_table_2.c.project_id)
True
>>> # 它们是不同的对象,一个有 schema,一个没有
>>> projects_table_1 is projects_table_2
False
这种混乱会给依赖反射加载应用级 Table
对象的程序带来问题,
尤其是在数据库迁移场景中,例如使用 Alembic Migrations 检测新表和外键约束时。
要避免上述行为带来的问题,只需遵循以下简单规则:
对于期望存在于数据库 默认 schema 中的
Table
, 不要为其指定Table.schema
参数。
对于 PostgreSQL 及其他支持 schema “搜索路径”的数据库,还需遵循以下规则:
将“搜索路径”限制为仅包含一个 schema,即默认 schema。
参见
远程模式表自检和 PostgreSQL search_path - 有关该行为在 PostgreSQL 数据库中的更多细节。
Section Best Practices Summarized
In this section, we discuss SQLAlchemy’s reflection behavior regarding
tables that are visible in the “default schema” of a database session,
and how these interact with SQLAlchemy directives that include the schema
explicitly. As a best practice, ensure the “default” schema for a database
is just a single name, and not a list of names; for tables that are
part of this “default” schema and can be named without schema qualification
in DDL and SQL, leave corresponding Table.schema
and
similar schema parameters set to their default of None
.
As described at 使用元数据指定默认架构名称, databases that have the concept of schemas usually also include the concept of a “default” schema. The reason for this is naturally that when one refers to table objects without a schema as is common, a schema-capable database will still consider that table to be in a “schema” somewhere. Some databases such as PostgreSQL take this concept further into the notion of a schema search path where multiple schema names can be considered in a particular database session to be “implicit”; referring to a table name that it’s any of those schemas will not require that the schema name be present (while at the same time it’s also perfectly fine if the schema name is present).
Since most relational databases therefore have the concept of a particular
table object which can be referenced both in a schema-qualified way, as
well as an “implicit” way where no schema is present, this presents a
complexity for SQLAlchemy’s reflection
feature. Reflecting a table in
a schema-qualified manner will always populate its Table.schema
attribute and additionally affect how this Table
is organized
into the MetaData.tables
collection, that is, in a schema
qualified manner. Conversely, reflecting the same table in a non-schema
qualified manner will organize it into the MetaData.tables
collection without being schema qualified. The end result is that there
would be two separate Table
objects in the single
MetaData
collection representing the same table in the
actual database.
To illustrate the ramifications of this issue, consider tables from the
“project” schema in the previous example, and suppose also that the “project”
schema is the default schema of our database connection, or if using a database
such as PostgreSQL suppose the “project” schema is set up in the PostgreSQL
search_path
. This would mean that the database accepts the following
two SQL statements as equivalent:
-- schema qualified
SELECT message_id FROM project.messages
-- non-schema qualified
SELECT message_id FROM messages
This is not a problem as the table can be found in both ways. However
in SQLAlchemy, it’s the identity of the Table
object
that determines its semantic role within a SQL statement. Based on the current
decisions within SQLAlchemy, this means that if we reflect the same “messages” table in
both a schema-qualified as well as a non-schema qualified manner, we get
two Table
objects that will not be treated as
semantically equivalent:
>>> # reflect in non-schema qualified fashion
>>> messages_table_1 = Table("messages", metadata_obj, autoload_with=someengine)
>>> # reflect in schema qualified fashion
>>> messages_table_2 = Table(
... "messages", metadata_obj, schema="project", autoload_with=someengine
... )
>>> # two different objects
>>> messages_table_1 is messages_table_2
False
>>> # stored in two different ways
>>> metadata.tables["messages"] is messages_table_1
True
>>> metadata.tables["project.messages"] is messages_table_2
True
The above issue becomes more complicated when the tables being reflected contain
foreign key references to other tables. Suppose “messages” has a “project_id”
column which refers to rows in another schema-local table “projects”, meaning
there is a ForeignKeyConstraint
object that is part of the
definition of the “messages” table.
We can find ourselves in a situation where one MetaData
collection may contain as many as four Table
objects
representing these two database tables, where one or two of the additional
tables were generated by the reflection process; this is because when
the reflection process encounters a foreign key constraint on a table
being reflected, it branches out to reflect that referenced table as well.
The decision making it uses to assign the schema to this referenced
table is that SQLAlchemy will omit a default schema from the reflected
ForeignKeyConstraint
object if the owning
Table
also omits its schema name and also that these two objects
are in the same schema, but will include it if
it were not omitted.
The common scenario is when the reflection of a table in a schema qualified fashion then loads a related table that will also be performed in a schema qualified fashion:
>>> # reflect "messages" in a schema qualified fashion
>>> messages_table_1 = Table(
... "messages", metadata_obj, schema="project", autoload_with=someengine
... )
The above messages_table_1
will refer to projects
also in a schema
qualified fashion. This “projects” table will be reflected automatically by
the fact that “messages” refers to it:
>>> messages_table_1.c.project_id
Column('project_id', INTEGER(), ForeignKey('project.projects.project_id'), table=<messages>)
if some other part of the code reflects “projects” in a non-schema qualified fashion, there are now two projects tables that are not the same:
>>> # reflect "projects" in a non-schema qualified fashion
>>> projects_table_1 = Table("projects", metadata_obj, autoload_with=someengine)
>>> # messages does not refer to projects_table_1 above
>>> messages_table_1.c.project_id.references(projects_table_1.c.project_id)
False
>>> # it refers to this one
>>> projects_table_2 = metadata_obj.tables["project.projects"]
>>> messages_table_1.c.project_id.references(projects_table_2.c.project_id)
True
>>> # they're different, as one non-schema qualified and the other one is
>>> projects_table_1 is projects_table_2
False
The above confusion can cause problems within applications that use table
reflection to load up application-level Table
objects, as
well as within migration scenarios, in particular such as when using Alembic
Migrations to detect new tables and foreign key constraints.
The above behavior can be remedied by sticking to one simple practice:
Don’t include the
Table.schema
parameter for anyTable
that expects to be located in the default schema of the database.
For PostgreSQL and other databases that support a “search” path for schemas, add the following additional practice:
Keep the “search path” narrowed down to one schema only, which is the default schema.
参见
远程模式表自检和 PostgreSQL search_path - additional details of this behavior as regards the PostgreSQL database.
使用检查器的细粒度反射¶
Fine Grained Reflection with Inspector
还提供一个低级接口,它提供了一个与后端无关的系统,用于从给定的数据库加载架构、表、列和约束描述列表。这被称为“检查器(Inspector)”:
from sqlalchemy import create_engine
from sqlalchemy import inspect
engine = create_engine("...")
insp = inspect(engine)
print(insp.get_table_names())
A low level interface which provides a backend-agnostic system of loading lists of schema, table, column, and constraint descriptions from a given database is also available. This is known as the “Inspector”:
from sqlalchemy import create_engine
from sqlalchemy import inspect
engine = create_engine("...")
insp = inspect(engine)
print(insp.get_table_names())
Object Name | Description |
---|---|
Performs database schema inspection. |
|
Dictionary representing the reflected elements corresponding to
|
|
Dictionary representing the reflected elements corresponding to
a |
|
Represent the reflected elements of a computed column, corresponding
to the |
|
Dictionary representing the reflected elements corresponding to
|
|
represent the reflected IDENTITY structure of a column, corresponding
to the |
|
Dictionary representing the reflected elements corresponding to
|
|
Dictionary representing the reflected elements corresponding to
|
|
Dictionary representing the reflected comment corresponding to
the |
|
Dictionary representing the reflected elements corresponding to
|
- class sqlalchemy.engine.reflection.Inspector¶
Performs database schema inspection.
The Inspector acts as a proxy to the reflection methods of the
Dialect
, providing a consistent interface as well as caching support for previously fetched metadata.A
Inspector
object is usually created via theinspect()
function, which may be passed anEngine
or aConnection
:from sqlalchemy import inspect, create_engine engine = create_engine("...") insp = inspect(engine)
Where above, the
Dialect
associated with the engine may opt to return anInspector
subclass that provides additional methods specific to the dialect’s target database.Members
__init__(), bind, clear_cache(), default_schema_name, dialect, engine, from_engine(), get_check_constraints(), get_columns(), get_foreign_keys(), get_indexes(), get_materialized_view_names(), get_multi_check_constraints(), get_multi_columns(), get_multi_foreign_keys(), get_multi_indexes(), get_multi_pk_constraint(), get_multi_table_comment(), get_multi_table_options(), get_multi_unique_constraints(), get_pk_constraint(), get_schema_names(), get_sequence_names(), get_sorted_table_and_fkc_names(), get_table_comment(), get_table_names(), get_table_options(), get_temp_table_names(), get_temp_view_names(), get_unique_constraints(), get_view_definition(), get_view_names(), has_index(), has_schema(), has_sequence(), has_table(), info_cache, reflect_table(), sort_tables_on_foreign_key_dependency()
Class signature
class
sqlalchemy.engine.reflection.Inspector
(sqlalchemy.inspection.Inspectable
)-
method
sqlalchemy.engine.reflection.Inspector.
__init__(bind: Engine | Connection)¶ Initialize a new
Inspector
.自 1.4 版本弃用: The __init__() method on
Inspector
is deprecated and will be removed in a future release. Please use theinspect()
function on anEngine
orConnection
in order to acquire anInspector
.- 参数:
bind¶ – a
Connection
, which is typically an instance ofEngine
orConnection
.
For a dialect-specific instance of
Inspector
, seeInspector.from_engine()
-
attribute
sqlalchemy.engine.reflection.Inspector.
bind: Engine | Connection¶
-
method
sqlalchemy.engine.reflection.Inspector.
clear_cache() None ¶ reset the cache for this
Inspector
.Inspection methods that have data cached will emit SQL queries when next called to get new data.
在 2.0 版本加入.
-
attribute
sqlalchemy.engine.reflection.Inspector.
default_schema_name¶ Return the default schema name presented by the dialect for the current engine’s database user.
E.g. this is typically
public
for PostgreSQL anddbo
for SQL Server.
-
attribute
sqlalchemy.engine.reflection.Inspector.
dialect: Dialect¶
-
attribute
sqlalchemy.engine.reflection.Inspector.
engine: Engine¶
-
classmethod
sqlalchemy.engine.reflection.Inspector.
from_engine(bind: Engine) Inspector ¶ Construct a new dialect-specific Inspector object from the given engine or connection.
自 1.4 版本弃用: The from_engine() method on
Inspector
is deprecated and will be removed in a future release. Please use theinspect()
function on anEngine
orConnection
in order to acquire anInspector
.- 参数:
bind¶ – a
Connection
orEngine
.
This method differs from direct a direct constructor call of
Inspector
in that theDialect
is given a chance to provide a dialect-specificInspector
instance, which may provide additional methods.See the example at
Inspector
.
-
method
sqlalchemy.engine.reflection.Inspector.
get_check_constraints(table_name: str, schema: str | None = None, **kw: Any) List[ReflectedCheckConstraint] ¶ Return information about check constraints in
table_name
.Given a string
table_name
and an optional string schema, return check constraint information as a list ofReflectedCheckConstraint
.- 参数:
table_name¶ – string name of the table. For special quoting, use
quoted_name
.schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a list of dictionaries, each representing the definition of a check constraints.
-
method
sqlalchemy.engine.reflection.Inspector.
get_columns(table_name: str, schema: str | None = None, **kw: Any) List[ReflectedColumn] ¶ Return information about columns in
table_name
.Given a string
table_name
and an optional stringschema
, return column information as a list ofReflectedColumn
.- 参数:
table_name¶ – string name of the table. For special quoting, use
quoted_name
.schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
list of dictionaries, each representing the definition of a database column.
-
method
sqlalchemy.engine.reflection.Inspector.
get_foreign_keys(table_name: str, schema: str | None = None, **kw: Any) List[ReflectedForeignKeyConstraint] ¶ Return information about foreign_keys in
table_name
.Given a string
table_name
, and an optional string schema, return foreign key information as a list ofReflectedForeignKeyConstraint
.- 参数:
table_name¶ – string name of the table. For special quoting, use
quoted_name
.schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a list of dictionaries, each representing the a foreign key definition.
-
method
sqlalchemy.engine.reflection.Inspector.
get_indexes(table_name: str, schema: str | None = None, **kw: Any) List[ReflectedIndex] ¶ Return information about indexes in
table_name
.Given a string
table_name
and an optional string schema, return index information as a list ofReflectedIndex
.- 参数:
table_name¶ – string name of the table. For special quoting, use
quoted_name
.schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a list of dictionaries, each representing the definition of an index.
-
method
sqlalchemy.engine.reflection.Inspector.
get_materialized_view_names(schema: str | None = None, **kw: Any) List[str] ¶ Return all materialized view names in schema.
- 参数:
schema¶ – Optional, retrieve names from a non-default schema. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
get_multi_check_constraints(schema: str | None = None, filter_names: Sequence[str] | None = None, kind: ObjectKind = ObjectKind.TABLE, scope: ObjectScope = ObjectScope.DEFAULT, **kw: Any) Dict[TableKey, List[ReflectedCheckConstraint]] ¶ Return information about check constraints in all tables in the given schema.
The tables can be filtered by passing the names to use to
filter_names
.For each table the value is a list of
ReflectedCheckConstraint
.- 参数:
schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.filter_names¶ – optionally return information only for the objects listed here.
kind¶ – a
ObjectKind
that specifies the type of objects to reflect. Defaults toObjectKind.TABLE
.scope¶ – a
ObjectScope
that specifies if constraints of default, temporary or any tables should be reflected. Defaults toObjectScope.DEFAULT
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dictionary where the keys are two-tuple schema,table-name and the values are list of dictionaries, each representing the definition of a check constraints. The schema is
None
if no schema is provided.
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
get_multi_columns(schema: str | None = None, filter_names: Sequence[str] | None = None, kind: ObjectKind = ObjectKind.TABLE, scope: ObjectScope = ObjectScope.DEFAULT, **kw: Any) Dict[TableKey, List[ReflectedColumn]] ¶ Return information about columns in all objects in the given schema.
The objects can be filtered by passing the names to use to
filter_names
.For each table the value is a list of
ReflectedColumn
.- 参数:
schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.filter_names¶ – optionally return information only for the objects listed here.
kind¶ – a
ObjectKind
that specifies the type of objects to reflect. Defaults toObjectKind.TABLE
.scope¶ – a
ObjectScope
that specifies if columns of default, temporary or any tables should be reflected. Defaults toObjectScope.DEFAULT
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dictionary where the keys are two-tuple schema,table-name and the values are list of dictionaries, each representing the definition of a database column. The schema is
None
if no schema is provided.
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
get_multi_foreign_keys(schema: str | None = None, filter_names: Sequence[str] | None = None, kind: ObjectKind = ObjectKind.TABLE, scope: ObjectScope = ObjectScope.DEFAULT, **kw: Any) Dict[TableKey, List[ReflectedForeignKeyConstraint]] ¶ Return information about foreign_keys in all tables in the given schema.
The tables can be filtered by passing the names to use to
filter_names
.For each table the value is a list of
ReflectedForeignKeyConstraint
.- 参数:
schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.filter_names¶ – optionally return information only for the objects listed here.
kind¶ – a
ObjectKind
that specifies the type of objects to reflect. Defaults toObjectKind.TABLE
.scope¶ – a
ObjectScope
that specifies if foreign keys of default, temporary or any tables should be reflected. Defaults toObjectScope.DEFAULT
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dictionary where the keys are two-tuple schema,table-name and the values are list of dictionaries, each representing a foreign key definition. The schema is
None
if no schema is provided.
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
get_multi_indexes(schema: str | None = None, filter_names: Sequence[str] | None = None, kind: ObjectKind = ObjectKind.TABLE, scope: ObjectScope = ObjectScope.DEFAULT, **kw: Any) Dict[TableKey, List[ReflectedIndex]] ¶ Return information about indexes in in all objects in the given schema.
The objects can be filtered by passing the names to use to
filter_names
.For each table the value is a list of
ReflectedIndex
.- 参数:
schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.filter_names¶ – optionally return information only for the objects listed here.
kind¶ – a
ObjectKind
that specifies the type of objects to reflect. Defaults toObjectKind.TABLE
.scope¶ – a
ObjectScope
that specifies if indexes of default, temporary or any tables should be reflected. Defaults toObjectScope.DEFAULT
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dictionary where the keys are two-tuple schema,table-name and the values are list of dictionaries, each representing the definition of an index. The schema is
None
if no schema is provided.
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
get_multi_pk_constraint(schema: str | None = None, filter_names: Sequence[str] | None = None, kind: ObjectKind = ObjectKind.TABLE, scope: ObjectScope = ObjectScope.DEFAULT, **kw: Any) Dict[TableKey, ReflectedPrimaryKeyConstraint] ¶ Return information about primary key constraints in all tables in the given schema.
The tables can be filtered by passing the names to use to
filter_names
.For each table the value is a
ReflectedPrimaryKeyConstraint
.- 参数:
schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.filter_names¶ – optionally return information only for the objects listed here.
kind¶ – a
ObjectKind
that specifies the type of objects to reflect. Defaults toObjectKind.TABLE
.scope¶ – a
ObjectScope
that specifies if primary keys of default, temporary or any tables should be reflected. Defaults toObjectScope.DEFAULT
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dictionary where the keys are two-tuple schema,table-name and the values are dictionaries, each representing the definition of a primary key constraint. The schema is
None
if no schema is provided.
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
get_multi_table_comment(schema: str | None = None, filter_names: Sequence[str] | None = None, kind: ObjectKind = ObjectKind.TABLE, scope: ObjectScope = ObjectScope.DEFAULT, **kw: Any) Dict[TableKey, ReflectedTableComment] ¶ Return information about the table comment in all objects in the given schema.
The objects can be filtered by passing the names to use to
filter_names
.For each table the value is a
ReflectedTableComment
.Raises
NotImplementedError
for a dialect that does not support comments.- 参数:
schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.filter_names¶ – optionally return information only for the objects listed here.
kind¶ – a
ObjectKind
that specifies the type of objects to reflect. Defaults toObjectKind.TABLE
.scope¶ – a
ObjectScope
that specifies if comments of default, temporary or any tables should be reflected. Defaults toObjectScope.DEFAULT
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dictionary where the keys are two-tuple schema,table-name and the values are dictionaries, representing the table comments. The schema is
None
if no schema is provided.
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
get_multi_table_options(schema: str | None = None, filter_names: Sequence[str] | None = None, kind: ObjectKind = ObjectKind.TABLE, scope: ObjectScope = ObjectScope.DEFAULT, **kw: Any) Dict[TableKey, Dict[str, Any]] ¶ Return a dictionary of options specified when the tables in the given schema were created.
The tables can be filtered by passing the names to use to
filter_names
.This currently includes some options that apply to MySQL and Oracle tables.
- 参数:
schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.filter_names¶ – optionally return information only for the objects listed here.
kind¶ – a
ObjectKind
that specifies the type of objects to reflect. Defaults toObjectKind.TABLE
.scope¶ – a
ObjectScope
that specifies if options of default, temporary or any tables should be reflected. Defaults toObjectScope.DEFAULT
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dictionary where the keys are two-tuple schema,table-name and the values are dictionaries with the table options. The returned keys in each dict depend on the dialect in use. Each one is prefixed with the dialect name. The schema is
None
if no schema is provided.
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
get_multi_unique_constraints(schema: str | None = None, filter_names: Sequence[str] | None = None, kind: ObjectKind = ObjectKind.TABLE, scope: ObjectScope = ObjectScope.DEFAULT, **kw: Any) Dict[TableKey, List[ReflectedUniqueConstraint]] ¶ Return information about unique constraints in all tables in the given schema.
The tables can be filtered by passing the names to use to
filter_names
.For each table the value is a list of
ReflectedUniqueConstraint
.- 参数:
schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.filter_names¶ – optionally return information only for the objects listed here.
kind¶ – a
ObjectKind
that specifies the type of objects to reflect. Defaults toObjectKind.TABLE
.scope¶ – a
ObjectScope
that specifies if constraints of default, temporary or any tables should be reflected. Defaults toObjectScope.DEFAULT
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dictionary where the keys are two-tuple schema,table-name and the values are list of dictionaries, each representing the definition of an unique constraint. The schema is
None
if no schema is provided.
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
get_pk_constraint(table_name: str, schema: str | None = None, **kw: Any) ReflectedPrimaryKeyConstraint ¶ Return information about primary key constraint in
table_name
.Given a string
table_name
, and an optional string schema, return primary key information as aReflectedPrimaryKeyConstraint
.- 参数:
table_name¶ – string name of the table. For special quoting, use
quoted_name
.schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dictionary representing the definition of a primary key constraint.
-
method
sqlalchemy.engine.reflection.Inspector.
get_schema_names(**kw: Any) List[str] ¶ Return all schema names.
- 参数:
**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
-
method
sqlalchemy.engine.reflection.Inspector.
get_sequence_names(schema: str | None = None, **kw: Any) List[str] ¶ Return all sequence names in schema.
- 参数:
schema¶ – Optional, retrieve names from a non-default schema. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
-
method
sqlalchemy.engine.reflection.Inspector.
get_sorted_table_and_fkc_names(schema: str | None = None, **kw: Any) List[Tuple[str | None, List[Tuple[str, str | None]]]] ¶ Return dependency-sorted table and foreign key constraint names in referred to within a particular schema.
This will yield 2-tuples of
(tablename, [(tname, fkname), (tname, fkname), ...])
consisting of table names in CREATE order grouped with the foreign key constraint names that are not detected as belonging to a cycle. The final element will be(None, [(tname, fkname), (tname, fkname), ..])
which will consist of remaining foreign key constraint names that would require a separate CREATE step after-the-fact, based on dependencies between tables.- 参数:
参见
sort_tables_and_constraints()
- similar method which works with an already-givenMetaData
.
-
method
sqlalchemy.engine.reflection.Inspector.
get_table_comment(table_name: str, schema: str | None = None, **kw: Any) ReflectedTableComment ¶ Return information about the table comment for
table_name
.Given a string
table_name
and an optional stringschema
, return table comment information as aReflectedTableComment
.Raises
NotImplementedError
for a dialect that does not support comments.- 参数:
table_name¶ – string name of the table. For special quoting, use
quoted_name
.schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dictionary, with the table comment.
-
method
sqlalchemy.engine.reflection.Inspector.
get_table_names(schema: str | None = None, **kw: Any) List[str] ¶ Return all table names within a particular schema.
The names are expected to be real tables only, not views. Views are instead returned using the
Inspector.get_view_names()
and/orInspector.get_materialized_view_names()
methods.- 参数:
schema¶ – Schema name. If
schema
is left atNone
, the database’s default schema is used, else the named schema is searched. If the database does not support named schemas, behavior is undefined ifschema
is not passed asNone
. For special quoting, usequoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
-
method
sqlalchemy.engine.reflection.Inspector.
get_table_options(table_name: str, schema: str | None = None, **kw: Any) Dict[str, Any] ¶ Return a dictionary of options specified when the table of the given name was created.
This currently includes some options that apply to MySQL and Oracle Database tables.
- 参数:
table_name¶ – string name of the table. For special quoting, use
quoted_name
.schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a dict with the table options. The returned keys depend on the dialect in use. Each one is prefixed with the dialect name.
-
method
sqlalchemy.engine.reflection.Inspector.
get_temp_table_names(**kw: Any) List[str] ¶ Return a list of temporary table names for the current bind.
This method is unsupported by most dialects; currently only Oracle Database, PostgreSQL and SQLite implements it.
- 参数:
**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
-
method
sqlalchemy.engine.reflection.Inspector.
get_temp_view_names(**kw: Any) List[str] ¶ Return a list of temporary view names for the current bind.
This method is unsupported by most dialects; currently only PostgreSQL and SQLite implements it.
- 参数:
**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
-
method
sqlalchemy.engine.reflection.Inspector.
get_unique_constraints(table_name: str, schema: str | None = None, **kw: Any) List[ReflectedUniqueConstraint] ¶ Return information about unique constraints in
table_name
.Given a string
table_name
and an optional string schema, return unique constraint information as a list ofReflectedUniqueConstraint
.- 参数:
table_name¶ – string name of the table. For special quoting, use
quoted_name
.schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
- 返回:
a list of dictionaries, each representing the definition of an unique constraint.
-
method
sqlalchemy.engine.reflection.Inspector.
get_view_definition(view_name: str, schema: str | None = None, **kw: Any) str ¶ Return definition for the plain or materialized view called
view_name
.- 参数:
view_name¶ – Name of the view.
schema¶ – Optional, retrieve names from a non-default schema. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
-
method
sqlalchemy.engine.reflection.Inspector.
get_view_names(schema: str | None = None, **kw: Any) List[str] ¶ Return all non-materialized view names in schema.
- 参数:
schema¶ – Optional, retrieve names from a non-default schema. For special quoting, use
quoted_name
.**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
在 2.0 版本发生变更: For those dialects that previously included the names of materialized views in this list (currently PostgreSQL), this method no longer returns the names of materialized views. the
Inspector.get_materialized_view_names()
method should be used instead.
-
method
sqlalchemy.engine.reflection.Inspector.
has_index(table_name: str, index_name: str, schema: str | None = None, **kw: Any) bool ¶ Check the existence of a particular index name in the database.
- 参数:
table_name¶ – the name of the table the index belongs to
index_name¶ – the name of the index to check
schema¶ – schema name to query, if not the default schema.
**kw¶ – Additional keyword argument to pass to the dialect specific implementation. See the documentation of the dialect in use for more information.
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
has_schema(schema_name: str, **kw: Any) bool ¶ Return True if the backend has a schema with the given name.
- 参数:
在 2.0 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
has_sequence(sequence_name: str, schema: str | None = None, **kw: Any) bool ¶ Return True if the backend has a sequence with the given name.
- 参数:
在 1.4 版本加入.
-
method
sqlalchemy.engine.reflection.Inspector.
has_table(table_name: str, schema: str | None = None, **kw: Any) bool ¶ Return True if the backend has a table, view, or temporary table of the given name.
- 参数:
在 1.4 版本加入: - the
Inspector.has_table()
method replaces theEngine.has_table()
method.在 2.0:: 版本发生变更:
Inspector.has_table()
now formally supports checking for additional table-like objects:any type of views (plain or materialized)
temporary tables of any kind
Previously, these two checks were not formally specified and different dialects would vary in their behavior. The dialect testing suite now includes tests for all of these object types and should be supported by all SQLAlchemy-included dialects. Support among third party dialects may be lagging, however.
-
attribute
sqlalchemy.engine.reflection.Inspector.
info_cache: Dict[Any, Any]¶
-
method
sqlalchemy.engine.reflection.Inspector.
reflect_table(table: Table, include_columns: Collection[str] | None, exclude_columns: Collection[str] = (), resolve_fks: bool = True, _extend_on: Set[Table] | None = None, _reflect_info: _ReflectionInfo | None = None) None ¶ Given a
Table
object, load its internal constructs based on introspection.This is the underlying method used by most dialects to produce table reflection. Direct usage is like:
from sqlalchemy import create_engine, MetaData, Table from sqlalchemy import inspect engine = create_engine("...") meta = MetaData() user_table = Table("user", meta) insp = inspect(engine) insp.reflect_table(user_table, None)
在 1.4 版本发生变更: Renamed from
reflecttable
toreflect_table
-
method
sqlalchemy.engine.reflection.Inspector.
sort_tables_on_foreign_key_dependency(consider_schemas: Collection[str | None] = (None,), **kw: Any) List[Tuple[Tuple[str | None, str] | None, List[Tuple[Tuple[str | None, str], str | None]]]] ¶ Return dependency-sorted table and foreign key constraint names referred to within multiple schemas.
This method may be compared to
Inspector.get_sorted_table_and_fkc_names()
, which works on one schema at a time; here, the method is a generalization that will consider multiple schemas at once including that it will resolve for cross-schema foreign keys.在 2.0 版本加入.
-
method
- class sqlalchemy.engine.interfaces.ReflectedColumn¶
Dictionary representing the reflected elements corresponding to a
Column
object.The
ReflectedColumn
structure is returned by theget_columns
method.Members
autoincrement, comment, computed, default, dialect_options, identity, name, nullable, type
Class signature
class
sqlalchemy.engine.interfaces.ReflectedColumn
(builtins.dict
)-
attribute
sqlalchemy.engine.interfaces.ReflectedColumn.
autoincrement: NotRequired[bool]¶ database-dependent autoincrement flag.
This flag indicates if the column has a database-side “autoincrement” flag of some kind. Within SQLAlchemy, other kinds of columns may also act as an “autoincrement” column without necessarily having such a flag on them.
See
Column.autoincrement
for more background on “autoincrement”.
-
attribute
sqlalchemy.engine.interfaces.ReflectedColumn.
comment: NotRequired[str | None]¶ comment for the column, if present. Only some dialects return this key
-
attribute
sqlalchemy.engine.interfaces.ReflectedColumn.
computed: NotRequired[ReflectedComputed]¶ indicates that this column is computed by the database. Only some dialects return this key.
-
attribute
sqlalchemy.engine.interfaces.ReflectedColumn.
default: str | None¶ column default expression as a SQL string
-
attribute
sqlalchemy.engine.interfaces.ReflectedColumn.
dialect_options: NotRequired[Dict[str, Any]]¶ Additional dialect-specific options detected for this reflected object
-
attribute
sqlalchemy.engine.interfaces.ReflectedColumn.
identity: NotRequired[ReflectedIdentity]¶ indicates this column is an IDENTITY column. Only some dialects return this key.
在 1.4 版本加入: - added support for identity column reflection.
-
attribute
sqlalchemy.engine.interfaces.ReflectedColumn.
name: str¶ column name
-
attribute
sqlalchemy.engine.interfaces.ReflectedColumn.
nullable: bool¶ boolean flag if the column is NULL or NOT NULL
-
attribute
sqlalchemy.engine.interfaces.ReflectedColumn.
type: TypeEngine[Any]¶ column type represented as a
TypeEngine
instance.
-
attribute
- class sqlalchemy.engine.interfaces.ReflectedComputed¶
Represent the reflected elements of a computed column, corresponding to the
Computed
construct.The
ReflectedComputed
structure is part of theReflectedColumn
structure, which is returned by theInspector.get_columns()
method.Class signature
class
sqlalchemy.engine.interfaces.ReflectedComputed
(builtins.dict
)-
attribute
sqlalchemy.engine.interfaces.ReflectedComputed.
persisted: bool¶ indicates if the value is stored in the table or computed on demand
-
attribute
sqlalchemy.engine.interfaces.ReflectedComputed.
sqltext: str¶ the expression used to generate this column returned as a string SQL expression
-
attribute
- class sqlalchemy.engine.interfaces.ReflectedCheckConstraint¶
Dictionary representing the reflected elements corresponding to
CheckConstraint
.The
ReflectedCheckConstraint
structure is returned by theInspector.get_check_constraints()
method.Members
Class signature
class
sqlalchemy.engine.interfaces.ReflectedCheckConstraint
(builtins.dict
)-
attribute
sqlalchemy.engine.interfaces.ReflectedCheckConstraint.
dialect_options: Dict[str, Any]¶ Additional dialect-specific options detected for this check constraint
-
attribute
sqlalchemy.engine.interfaces.ReflectedCheckConstraint.
sqltext: str¶ the check constraint’s SQL expression
-
attribute
- class sqlalchemy.engine.interfaces.ReflectedForeignKeyConstraint¶
Dictionary representing the reflected elements corresponding to
ForeignKeyConstraint
.The
ReflectedForeignKeyConstraint
structure is returned by theInspector.get_foreign_keys()
method.Class signature
class
sqlalchemy.engine.interfaces.ReflectedForeignKeyConstraint
(builtins.dict
)-
attribute
sqlalchemy.engine.interfaces.ReflectedForeignKeyConstraint.
constrained_columns: List[str]¶ local column names which comprise the foreign key
-
attribute
sqlalchemy.engine.interfaces.ReflectedForeignKeyConstraint.
options: Dict[str, Any]¶ Additional options detected for this foreign key constraint
-
attribute
sqlalchemy.engine.interfaces.ReflectedForeignKeyConstraint.
referred_columns: List[str]¶ referred column names that correspond to
constrained_columns
-
attribute
sqlalchemy.engine.interfaces.ReflectedForeignKeyConstraint.
referred_schema: str | None¶ schema name of the table being referred
-
attribute
sqlalchemy.engine.interfaces.ReflectedForeignKeyConstraint.
referred_table: str¶ name of the table being referred
-
attribute
- class sqlalchemy.engine.interfaces.ReflectedIdentity¶
represent the reflected IDENTITY structure of a column, corresponding to the
Identity
construct.The
ReflectedIdentity
structure is part of theReflectedColumn
structure, which is returned by theInspector.get_columns()
method.Members
always, cache, cycle, increment, maxvalue, minvalue, nomaxvalue, nominvalue, on_null, order, start
Class signature
class
sqlalchemy.engine.interfaces.ReflectedIdentity
(builtins.dict
)-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
always: bool¶ type of identity column
-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
cache: int | None¶ number of future values in the sequence which are calculated in advance.
-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
cycle: bool¶ allows the sequence to wrap around when the maxvalue or minvalue has been reached.
-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
increment: int¶ increment value of the sequence
-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
maxvalue: int¶ the maximum value of the sequence.
-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
minvalue: int¶ the minimum value of the sequence.
-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
nomaxvalue: bool¶ no maximum value of the sequence.
-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
nominvalue: bool¶ no minimum value of the sequence.
-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
on_null: bool¶ indicates ON NULL
-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
order: bool¶ if true, renders the ORDER keyword.
-
attribute
sqlalchemy.engine.interfaces.ReflectedIdentity.
start: int¶ starting index of the sequence
-
attribute
- class sqlalchemy.engine.interfaces.ReflectedIndex¶
Dictionary representing the reflected elements corresponding to
Index
.The
ReflectedIndex
structure is returned by theInspector.get_indexes()
method.Members
column_names, column_sorting, dialect_options, duplicates_constraint, expressions, include_columns, name, unique
Class signature
class
sqlalchemy.engine.interfaces.ReflectedIndex
(builtins.dict
)-
attribute
sqlalchemy.engine.interfaces.ReflectedIndex.
column_names: List[str | None]¶ column names which the index references. An element of this list is
None
if it’s an expression and is returned in theexpressions
list.
-
attribute
sqlalchemy.engine.interfaces.ReflectedIndex.
column_sorting: Dict[str, Tuple[str]]¶ optional dict mapping column names or expressions to tuple of sort keywords, which may include
asc
,desc
,nulls_first
,nulls_last
.
-
attribute
sqlalchemy.engine.interfaces.ReflectedIndex.
dialect_options: Dict[str, Any]¶ Additional dialect-specific options detected for this index
-
attribute
sqlalchemy.engine.interfaces.ReflectedIndex.
duplicates_constraint: str | None¶ Indicates if this index mirrors a constraint with this name
-
attribute
sqlalchemy.engine.interfaces.ReflectedIndex.
expressions: List[str]¶ Expressions that compose the index. This list, when present, contains both plain column names (that are also in
column_names
) and expressions (that areNone
incolumn_names
).
-
attribute
sqlalchemy.engine.interfaces.ReflectedIndex.
include_columns: List[str]¶ columns to include in the INCLUDE clause for supporting databases.
自 2.0 版本弃用: Legacy value, will be replaced with
index_dict["dialect_options"]["<dialect name>_include"]
-
attribute
sqlalchemy.engine.interfaces.ReflectedIndex.
name: str | None¶ index name
-
attribute
sqlalchemy.engine.interfaces.ReflectedIndex.
unique: bool¶ whether or not the index has a unique flag
-
attribute
- class sqlalchemy.engine.interfaces.ReflectedPrimaryKeyConstraint¶
Dictionary representing the reflected elements corresponding to
PrimaryKeyConstraint
.The
ReflectedPrimaryKeyConstraint
structure is returned by theInspector.get_pk_constraint()
method.Members
Class signature
class
sqlalchemy.engine.interfaces.ReflectedPrimaryKeyConstraint
(builtins.dict
)-
attribute
sqlalchemy.engine.interfaces.ReflectedPrimaryKeyConstraint.
constrained_columns: List[str]¶ column names which comprise the primary key
-
attribute
sqlalchemy.engine.interfaces.ReflectedPrimaryKeyConstraint.
dialect_options: Dict[str, Any]¶ Additional dialect-specific options detected for this primary key
-
attribute
- class sqlalchemy.engine.interfaces.ReflectedUniqueConstraint¶
Dictionary representing the reflected elements corresponding to
UniqueConstraint
.The
ReflectedUniqueConstraint
structure is returned by theInspector.get_unique_constraints()
method.Members
Class signature
class
sqlalchemy.engine.interfaces.ReflectedUniqueConstraint
(builtins.dict
)-
attribute
sqlalchemy.engine.interfaces.ReflectedUniqueConstraint.
column_names: List[str]¶ column names which comprise the unique constraint
-
attribute
sqlalchemy.engine.interfaces.ReflectedUniqueConstraint.
dialect_options: Dict[str, Any]¶ Additional dialect-specific options detected for this unique constraint
-
attribute
sqlalchemy.engine.interfaces.ReflectedUniqueConstraint.
duplicates_index: str | None¶ Indicates if this unique constraint duplicates an index with this name
-
attribute
- class sqlalchemy.engine.interfaces.ReflectedTableComment¶
Dictionary representing the reflected comment corresponding to the
Table.comment
attribute.The
ReflectedTableComment
structure is returned by theInspector.get_table_comment()
method.Members
Class signature
class
sqlalchemy.engine.interfaces.ReflectedTableComment
(builtins.dict
)-
attribute
sqlalchemy.engine.interfaces.ReflectedTableComment.
text: str | None¶ text of the comment
-
attribute
使用与数据库无关的类型进行反射¶
Reflecting with Database-Agnostic Types
当反射表的列时,无论是使用 Table.autoload_with
参数
还是使用 Inspector.get_columns()
方法,
数据类型将尽可能具体地与目标数据库匹配。
这意味着,如果从 MySQL 数据库中反射出一个“integer”数据类型,
该类型将由 sqlalchemy.dialects.mysql.INTEGER
类表示,
该类包含 MySQL 特有的属性,如 “display_width”。
在 PostgreSQL 中,可能会返回 PostgreSQL 特有的数据类型,如
sqlalchemy.dialects.postgresql.INTERVAL
或
sqlalchemy.dialects.postgresql.ENUM
。
有一种反射的使用场景是:将给定的 Table
转移到另一个供应商的数据库。
为了适应这种场景,可以使用一种技术,将这些供应商特有的数据类型
动态转换为 SQLAlchemy 后端无关的数据类型实例,例如上述的 Integer
、Interval
和 Enum
。
这可以通过在列反射时拦截 DDLEvents.column_reflect()
事件,并结合
TypeEngine.as_generic()
方法来实现。
考虑一个 MySQL 中的表(选择 MySQL 是因为它有很多供应商特有的数据类型和选项):
CREATE TABLE IF NOT EXISTS my_table (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
data1 VARCHAR(50) CHARACTER SET latin1,
data2 MEDIUMINT(4),
data3 TINYINT(2)
)
上述表包含了 MySQL 特有的整数类型 MEDIUMINT
和 TINYINT
以及包含 MySQL 特有的 CHARACTER SET
选项的 VARCHAR
。
如果我们正常反射该表,它会生成一个 Table
对象,
该对象将包含这些 MySQL 特有的数据类型和选项:
>>> from sqlalchemy import MetaData, Table, create_engine
>>> mysql_engine = create_engine("mysql+mysqldb://scott:tiger@localhost/test")
>>> metadata_obj = MetaData()
>>> my_mysql_table = Table("my_table", metadata_obj, autoload_with=mysql_engine)
上述示例将表架构反射到一个新的 Table
对象中。
然后,我们可以为了演示目的,使用 CreateTable
构造体打印出 MySQL 特有的
“CREATE TABLE” 语句:
>>> from sqlalchemy.schema import CreateTable
>>> print(CreateTable(my_mysql_table).compile(mysql_engine))
CREATE TABLE my_table (
id INTEGER(11) NOT NULL AUTO_INCREMENT,
data1 VARCHAR(50) CHARACTER SET latin1,
data2 MEDIUMINT(4),
data3 TINYINT(2),
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
上面,MySQL 特有的数据类型和选项被保留了。如果我们想要一个
可以干净地转移到另一个数据库供应商的 Table
,
并将特有的数据类型 sqlalchemy.dialects.mysql.MEDIUMINT
和
sqlalchemy.dialects.mysql.TINYINT
替换为 Integer
,
我们可以选择将该表的数据类型“通用化”,或以任何我们想要的方式修改它们,
通过使用 DDLEvents.column_reflect()
事件建立一个处理程序。
该自定义处理程序将使用 TypeEngine.as_generic()
方法,
通过替换传递给事件处理程序的列字典中的 "type"
项,
将上述 MySQL 特有的类型对象转换为通用类型。
该字典的格式可以在 Inspector.get_columns()
中找到:
>>> from sqlalchemy import event
>>> metadata_obj = MetaData()
>>> @event.listens_for(metadata_obj, "column_reflect")
... def genericize_datatypes(inspector, tablename, column_dict):
... column_dict["type"] = column_dict["type"].as_generic()
>>> my_generic_table = Table("my_table", metadata_obj, autoload_with=mysql_engine)
现在,我们得到一个新的通用 Table
,它使用 Integer
来表示这些数据类型。
我们现在可以为 PostgreSQL 数据库发出一个 “CREATE TABLE” 语句,例如:
>>> pg_engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test", echo=True)
>>> my_generic_table.create(pg_engine)
CREATE TABLE my_table (
id SERIAL NOT NULL,
data1 VARCHAR(50),
data2 INTEGER,
data3 INTEGER,
PRIMARY KEY (id)
)
上面也可以注意到,SQLAlchemy 通常会为其他行为做出合理的猜测,
例如 MySQL 的 AUTO_INCREMENT
指令在 PostgreSQL 中最接近的是 SERIAL
自增数据类型。
在 1.4 版本加入: 添加了 TypeEngine.as_generic()
方法,
并改进了 DDLEvents.column_reflect()
事件的使用,
使其可以方便地应用于 MetaData
对象。
When the columns of a table are reflected, using either the
Table.autoload_with
parameter of Table
or
the Inspector.get_columns()
method of
Inspector
, the datatypes will be as specific as possible
to the target database. This means that if an “integer” datatype is reflected
from a MySQL database, the type will be represented by the
sqlalchemy.dialects.mysql.INTEGER
class, which includes MySQL-specific
attributes such as “display_width”. Or on PostgreSQL, a PostgreSQL-specific
datatype such as sqlalchemy.dialects.postgresql.INTERVAL
or
sqlalchemy.dialects.postgresql.ENUM
may be returned.
There is a use case for reflection which is that a given Table
is to be transferred to a different vendor database. To suit this use case,
there is a technique by which these vendor-specific datatypes can be converted
on the fly to be instance of SQLAlchemy backend-agnostic datatypes, for
the examples above types such as Integer
, Interval
and Enum
. This may be achieved by intercepting the
column reflection using the DDLEvents.column_reflect()
event
in conjunction with the TypeEngine.as_generic()
method.
Given a table in MySQL (chosen because MySQL has a lot of vendor-specific datatypes and options):
CREATE TABLE IF NOT EXISTS my_table (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
data1 VARCHAR(50) CHARACTER SET latin1,
data2 MEDIUMINT(4),
data3 TINYINT(2)
)
The above table includes MySQL-only integer types MEDIUMINT
and
TINYINT
as well as a VARCHAR
that includes the MySQL-only CHARACTER
SET
option. If we reflect this table normally, it produces a
Table
object that will contain those MySQL-specific datatypes
and options:
>>> from sqlalchemy import MetaData, Table, create_engine
>>> mysql_engine = create_engine("mysql+mysqldb://scott:tiger@localhost/test")
>>> metadata_obj = MetaData()
>>> my_mysql_table = Table("my_table", metadata_obj, autoload_with=mysql_engine)
The above example reflects the above table schema into a new Table
object. We can then, for demonstration purposes, print out the MySQL-specific
“CREATE TABLE” statement using the CreateTable
construct:
>>> from sqlalchemy.schema import CreateTable
>>> print(CreateTable(my_mysql_table).compile(mysql_engine))
CREATE TABLE my_table (
id INTEGER(11) NOT NULL AUTO_INCREMENT,
data1 VARCHAR(50) CHARACTER SET latin1,
data2 MEDIUMINT(4),
data3 TINYINT(2),
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Above, the MySQL-specific datatypes and options were maintained. If we wanted
a Table
that we could instead transfer cleanly to another
database vendor, replacing the special datatypes
sqlalchemy.dialects.mysql.MEDIUMINT
and
sqlalchemy.dialects.mysql.TINYINT
with Integer
, we can
choose instead to “genericize” the datatypes on this table, or otherwise change
them in any way we’d like, by establishing a handler using the
DDLEvents.column_reflect()
event. The custom handler will make use
of the TypeEngine.as_generic()
method to convert the above
MySQL-specific type objects into generic ones, by replacing the "type"
entry within the column dictionary entry that is passed to the event handler.
The format of this dictionary is described at Inspector.get_columns()
:
>>> from sqlalchemy import event
>>> metadata_obj = MetaData()
>>> @event.listens_for(metadata_obj, "column_reflect")
... def genericize_datatypes(inspector, tablename, column_dict):
... column_dict["type"] = column_dict["type"].as_generic()
>>> my_generic_table = Table("my_table", metadata_obj, autoload_with=mysql_engine)
We now get a new Table
that is generic and uses
Integer
for those datatypes. We can now emit a
“CREATE TABLE” statement for example on a PostgreSQL database:
>>> pg_engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test", echo=True)
>>> my_generic_table.create(pg_engine)
CREATE TABLE my_table (
id SERIAL NOT NULL,
data1 VARCHAR(50),
data2 INTEGER,
data3 INTEGER,
PRIMARY KEY (id)
)
Noting above also that SQLAlchemy will usually make a decent guess for other
behaviors, such as that the MySQL AUTO_INCREMENT
directive is represented
in PostgreSQL most closely using the SERIAL
auto-incrementing datatype.
在 1.4 版本加入: Added the TypeEngine.as_generic()
method
and additionally improved the use of the DDLEvents.column_reflect()
event such that it may be applied to a MetaData
object
for convenience.
反射的局限性¶
Limitations of Reflection
需要注意的是,反射过程仅使用数据库中表示的信息来重新创建 Table
元数据。
根据定义,这个过程无法恢复数据库中未实际存储的 schema 方面的信息。
反射过程中无法恢复的状态包括但不限于:
客户端默认值,既可以是 Python 函数,也可以是使用
Column
的default
关键字定义的 SQL 表达式 (注意,这与server_default
是分开的,后者是反射时可以获取的内容)。列信息,例如可能已放入
Column.info
字典的数据
在许多情况下,关系型数据库报告的表元数据格式与 SQLAlchemy 中指定的格式不同。
反射返回的 Table
对象不能总是可靠地生成与原始 Python 定义的 Table
对象相同的 DDL。
发生这种情况的领域包括服务器默认值、列相关序列以及与约束和数据类型有关的各种特殊情况。
服务器端默认值可能会与强制转换指令一起返回(通常 PostgreSQL 会包括 ::<type>
强制转换)
或与最初指定的引号模式不同。
另一个限制类别包括反射仅部分定义或尚未定义的 schema 结构。 最近对反射的改进允许反射视图、索引和外键选项等内容。 截至本文写作时,像 CHECK 约束、表注释和触发器等结构尚未反射。
It’s important to note that the reflection process recreates Table
metadata using only information which is represented in the relational database.
This process by definition cannot restore aspects of a schema that aren’t
actually stored in the database. State which is not available from reflection
includes but is not limited to:
Client side defaults, either Python functions or SQL expressions defined using the
default
keyword ofColumn
(note this is separate fromserver_default
, which specifically is what’s available via reflection).Column information, e.g. data that might have been placed into the
Column.info
dictionaryThe association of a particular
Sequence
with a givenColumn
The relational database also in many cases reports on table metadata in a
different format than what was specified in SQLAlchemy. The Table
objects returned from reflection cannot be always relied upon to produce the identical
DDL as the original Python-defined Table
objects. Areas where
this occurs includes server defaults, column-associated sequences and various
idiosyncrasies regarding constraints and datatypes. Server side defaults may
be returned with cast directives (typically PostgreSQL will include a ::<type>
cast) or different quoting patterns than originally specified.
Another category of limitation includes schema structures for which reflection is only partially or not yet defined. Recent improvements to reflection allow things like views, indexes and foreign key options to be reflected. As of this writing, structures like CHECK constraints, table comments, and triggers are not reflected.