自动映射¶
Automap
定义一个扩展 sqlalchemy.ext.declarative
系统
,该系统可以自动从数据库模式生成映射类和关系,通常但不一定是反射出来的。
我们希望 AutomapBase
系统提供一个快速且现代化的解决方案,
来解决著名的 SQLSoup
也试图解决的问题,即动态地从现有数据库生成一个快速且基础的对象模型。
通过严格从映射器配置层面解决这个问题,并与现有的声明式类技术完全集成, AutomapBase
旨在为快速自动生成临时映射提供一个良好的集成方案。
小技巧
自动映射 扩展旨在实现“零声明”方法,
其中可以从数据库模式动态生成一个完整的 ORM 模型,包括类和预命名的关系。
对于仍然希望结合表格反射使用显式类声明(包括显式关系定义)的应用,
DeferredReflection
类(在 使用 DeferredReflection 中描述)是更好的选择。
Define an extension to the sqlalchemy.ext.declarative
system
which automatically generates mapped classes and relationships from a database
schema, typically though not necessarily one which is reflected.
It is hoped that the AutomapBase
system provides a quick
and modernized solution to the problem that the very famous
SQLSoup
also tries to solve, that of generating a quick and rudimentary object
model from an existing database on the fly. By addressing the issue strictly
at the mapper configuration level, and integrating fully with existing
Declarative class techniques, AutomapBase
seeks to provide
a well-integrated approach to the issue of expediently auto-generating ad-hoc
mappings.
小技巧
The 自动映射 extension is geared towards a
“zero declaration” approach, where a complete ORM model including classes
and pre-named relationships can be generated on the fly from a database
schema. For applications that still want to use explicit class declarations
including explicit relationship definitions in conjunction with reflection
of tables, the DeferredReflection
class, described at
使用 DeferredReflection, is a better choice.
基本用法¶
Basic Use
最简单的用法是将现有的数据库反射到一个新的模型中。
我们通过类似于创建声明式基类的方式,创建一个新的 AutomapBase
类,使用 automap_base()
。
然后,我们在结果基类上调用 AutomapBase.prepare()
方法,要求它反射模式并生成映射:
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
Base = automap_base()
# 假设 engine 已经设置了两个表 'user' 和 'address'
engine = create_engine("sqlite:///mydatabase.db")
# 反射表格
Base.prepare(autoload_with=engine)
# 映射类现在是通过表名自动生成的
User = Base.classes.user
Address = Base.classes.address
session = Session(engine)
# 基本的关系已生成
session.add(Address(email_address="foo@bar.com", user=User(name="foo")))
session.commit()
# 基于集合的关系默认命名为
# "<classname>_collection"
u1 = session.query(User).first()
print(u1.address_collection)
在上面的例子中,调用 AutomapBase.prepare()
并传递 AutomapBase.prepare.reflect
参数,表示
将调用此声明基类的 MetaData
集合上的 MetaData.reflect()
方法;
然后,在 MetaData
中的每个 可用的 Table
都会自动生成一个新的映射类。 连接各个表的 ForeignKeyConstraint
对象
将用于生成类之间新的双向 relationship()
对象。
类和关系遵循一个默认的命名方案,我们可以自定义该方案。 到此为止,
我们基本的映射由相关的 User
和 Address
类组成,已准备好以传统方式使用。
备注
可用的 表示一个表要被映射,必须指定主键。 此外,如果检测到表是两个其他表之间的纯关联表,它将不会直接映射, 而是作为两个引用表之间的多对多表进行配置。
The simplest usage is to reflect an existing database into a new model.
We create a new AutomapBase
class in a similar manner as to how
we create a declarative base class, using automap_base()
.
We then call AutomapBase.prepare()
on the resulting base class,
asking it to reflect the schema and produce mappings:
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
Base = automap_base()
# engine, suppose it has two tables 'user' and 'address' set up
engine = create_engine("sqlite:///mydatabase.db")
# reflect the tables
Base.prepare(autoload_with=engine)
# mapped classes are now created with names by default
# matching that of the table name.
User = Base.classes.user
Address = Base.classes.address
session = Session(engine)
# rudimentary relationships are produced
session.add(Address(email_address="foo@bar.com", user=User(name="foo")))
session.commit()
# collection-based relationships are by default named
# "<classname>_collection"
u1 = session.query(User).first()
print(u1.address_collection)
Above, calling AutomapBase.prepare()
while passing along the
AutomapBase.prepare.reflect
parameter indicates that the
MetaData.reflect()
method will be called on this declarative base
classes’ MetaData
collection; then, each viable
Table
within the MetaData
will get a new mapped class
generated automatically. The ForeignKeyConstraint
objects which
link the various tables together will be used to produce new, bidirectional
relationship()
objects between classes.
The classes and relationships
follow along a default naming scheme that we can customize. At this point,
our basic mapping consisting of related User
and Address
classes is
ready to use in the traditional way.
备注
By viable, we mean that for a table to be mapped, it must specify a primary key. Additionally, if the table is detected as being a pure association table between two other tables, it will not be directly mapped and will instead be configured as a many-to-many table between the mappings for the two referring tables.
从现有元数据生成用法¶
Generating Mappings from an Existing MetaData
我们可以将预先声明的 MetaData
对象传递给 automap_base()
。
这个对象可以以任何方式构造,包括通过编程、从序列化文件中或通过
MetaData.reflect()
反射自身构造。下面我们展示了反射和
显式表声明的组合:
from sqlalchemy import create_engine, MetaData, Table, Column, ForeignKey
from sqlalchemy.ext.automap import automap_base
engine = create_engine("sqlite:///mydatabase.db")
# 创建我们自己的 MetaData 对象
metadata = MetaData()
# 我们可以从数据库中反射它,使用选项
# 比如 'only' 来限制我们查看的表格...
metadata.reflect(engine, only=["user", "address"])
# ...或者仅使用它定义我们自己的 Table 对象(或两者结合)
Table(
"user_order",
metadata,
Column("id", Integer, primary_key=True),
Column("user_id", ForeignKey("user.id")),
)
# 然后我们可以从这个 MetaData 中生成一组映射。
Base = automap_base(metadata=metadata)
# 调用 prepare() 只是设置映射类和关系。
Base.prepare()
# 映射类已准备好
User = Base.classes.user
Address = Base.classes.address
Order = Base.classes.user_order
We can pass a pre-declared MetaData
object to
automap_base()
.
This object can be constructed in any way, including programmatically, from
a serialized file, or from itself being reflected using
MetaData.reflect()
.
Below we illustrate a combination of reflection and
explicit table declaration:
from sqlalchemy import create_engine, MetaData, Table, Column, ForeignKey
from sqlalchemy.ext.automap import automap_base
engine = create_engine("sqlite:///mydatabase.db")
# produce our own MetaData object
metadata = MetaData()
# we can reflect it ourselves from a database, using options
# such as 'only' to limit what tables we look at...
metadata.reflect(engine, only=["user", "address"])
# ... or just define our own Table objects with it (or combine both)
Table(
"user_order",
metadata,
Column("id", Integer, primary_key=True),
Column("user_id", ForeignKey("user.id")),
)
# we can then produce a set of mappings from this MetaData.
Base = automap_base(metadata=metadata)
# calling prepare() just sets up mapped classes and relationships.
Base.prepare()
# mapped classes are ready
User = Base.classes.user
Address = Base.classes.address
Order = Base.classes.user_order
从多个架构生成用法¶
Generating Mappings from Multiple Schemas
当与反射一起使用时,AutomapBase.prepare()
方法每次最多只能反射一个模式中的表,
可以使用 AutomapBase.prepare.schema
参数指示要反射的模式名称。
为了从多个模式中填充 AutomapBase
,可以多次调用 AutomapBase.prepare()
,
每次传递不同的名称给 AutomapBase.prepare.schema
参数。
AutomapBase.prepare()
方法会维护一个已映射的 Table
对象的内部列表,
并且仅会为自上次运行 AutomapBase.prepare()
后新添加的 Table
对象添加新的映射:
e = create_engine("postgresql://scott:tiger@localhost/test")
Base.metadata.create_all(e)
Base = automap_base()
Base.prepare(e)
Base.prepare(e, schema="test_schema")
Base.prepare(e, schema="test_schema_2")
在 2.0 版本加入: AutomapBase.prepare()
方法可以被调用任意次数;
每次运行时,只有新增的表会被映射。 在版本 1.4 及更早版本中,多次调用会导致错误,
因为它会尝试重新映射已经映射的类。 之前的解决方法是直接调用
MetaData.reflect()
,此方法仍然可用。
The AutomapBase.prepare()
method when used with reflection may reflect
tables from one schema at a time at most, using the
AutomapBase.prepare.schema
parameter to indicate the name of a
schema to be reflected from. In order to populate the AutomapBase
with tables from multiple schemas, AutomapBase.prepare()
may be invoked
multiple times, each time passing a different name to the
AutomapBase.prepare.schema
parameter. The
AutomapBase.prepare()
method keeps an internal list of
Table
objects that have already been mapped, and will add new
mappings only for those Table
objects that are new since the
last time AutomapBase.prepare()
was run:
e = create_engine("postgresql://scott:tiger@localhost/test")
Base.metadata.create_all(e)
Base = automap_base()
Base.prepare(e)
Base.prepare(e, schema="test_schema")
Base.prepare(e, schema="test_schema_2")
在 2.0 版本加入: The AutomapBase.prepare()
method may be called
any number of times; only newly added tables will be mapped
on each run. Previously in version 1.4 and earlier, multiple calls would
cause errors as it would attempt to re-map an already mapped class.
The previous workaround approach of invoking
MetaData.reflect()
directly remains available as well.
跨多个架构自动映射同用法¶
Automapping same-named tables across multiple schemas
对于多模式可能具有同名表的常见情况,因此会生成同名类,可以通过以下方式解决冲突:
使用 AutomapBase.prepare.classname_for_table
钩子在每个模式的基础上应用不同的类名,或者使用
AutomapBase.prepare.modulename_for_table
钩子,通过更改类的有效 __module__
属性来区分同名类。
在下面的示例中,使用该钩子为所有类创建一个 __module__
属性,其形式为 mymodule.<schemaname>
,
如果没有模式,则使用模式名称 default
:
e = create_engine("postgresql://scott:tiger@localhost/test")
Base.metadata.create_all(e)
def module_name_for_table(cls, tablename, table):
if table.schema is not None:
return f"mymodule.{table.schema}"
else:
return f"mymodule.default"
Base = automap_base()
Base.prepare(e, modulename_for_table=module_name_for_table)
Base.prepare(
e, schema="test_schema", modulename_for_table=module_name_for_table
)
Base.prepare(
e, schema="test_schema_2", modulename_for_table=module_name_for_table
)
同名类被组织到一个分层的集合中,可以通过 AutomapBase.by_module
访问。
此集合可以使用特定包/模块的点分隔名称来遍历,直到所需的类名。
备注
当使用 AutomapBase.prepare.modulename_for_table
钩子返回一个新的 __module__
且不为 None
时,类将
不会 被放入 AutomapBase.classes
集合中;只有那些没有显式给定模块名的类
会放入该集合,因为该集合无法单独表示同名类。
在上面的示例中,如果数据库中包含一个名为 accounts
的表,
并且该表存在于默认模式、test_schema
模式和 test_schema_2
模式中,
则将提供三个单独的类,分别为:
Base.by_module.mymodule.default.accounts
Base.by_module.mymodule.test_schema.accounts
Base.by_module.mymodule.test_schema_2.accounts
为所有 AutomapBase
类生成的默认模块命名空间是
sqlalchemy.ext.automap
。如果没有使用
AutomapBase.prepare.modulename_for_table
钩子,
则 AutomapBase.by_module
的内容将完全位于
sqlalchemy.ext.automap
命名空间中(例如,
MyBase.by_module.sqlalchemy.ext.automap.<classname>
),
其中包含与 AutomapBase.classes
中看到的相同的一系列类。
因此,通常只有在显式 __module__
约定存在时,才需要使用 AutomapBase.by_module
。
在 2.0 版本加入: 添加了 AutomapBase.by_module
集合,该集合根据点分隔的模块名称
存储类,并且添加了 Automap.prepare.modulename_for_table
参数,
允许为自动映射的类定义自定义的 __module__
方案。
For the common case where multiple schemas may have same-named tables and
therefore would generate same-named classes, conflicts can be resolved either
through use of the AutomapBase.prepare.classname_for_table
hook to
apply different classnames on a per-schema basis, or by using the
AutomapBase.prepare.modulename_for_table
hook, which allows
disambiguation of same-named classes by changing their effective __module__
attribute. In the example below, this hook is used to create a __module__
attribute for all classes that is of the form mymodule.<schemaname>
, where
the schema name default
is used if no schema is present:
e = create_engine("postgresql://scott:tiger@localhost/test")
Base.metadata.create_all(e)
def module_name_for_table(cls, tablename, table):
if table.schema is not None:
return f"mymodule.{table.schema}"
else:
return f"mymodule.default"
Base = automap_base()
Base.prepare(e, modulename_for_table=module_name_for_table)
Base.prepare(
e, schema="test_schema", modulename_for_table=module_name_for_table
)
Base.prepare(
e, schema="test_schema_2", modulename_for_table=module_name_for_table
)
The same named-classes are organized into a hierarchical collection available
at AutomapBase.by_module
. This collection is traversed using the
dot-separated name of a particular package/module down into the desired
class name.
备注
When using the AutomapBase.prepare.modulename_for_table
hook to return a new __module__
that is not None
, the class is
not placed into the AutomapBase.classes
collection; only
classes that were not given an explicit modulename are placed here, as the
collection cannot represent same-named classes individually.
In the example above, if the database contained a table named accounts
in
all three of the default schema, the test_schema
schema, and the
test_schema_2
schema, three separate classes will be available as:
Base.by_module.mymodule.default.accounts
Base.by_module.mymodule.test_schema.accounts
Base.by_module.mymodule.test_schema_2.accounts
The default module namespace generated for all AutomapBase
classes is
sqlalchemy.ext.automap
. If no
AutomapBase.prepare.modulename_for_table
hook is used, the
contents of AutomapBase.by_module
will be entirely within the
sqlalchemy.ext.automap
namespace (e.g.
MyBase.by_module.sqlalchemy.ext.automap.<classname>
), which would contain
the same series of classes as what would be seen in
AutomapBase.classes
. Therefore it’s generally only necessary to use
AutomapBase.by_module
when explicit __module__
conventions are
present.
在 2.0 版本加入: Added the AutomapBase.by_module
collection, which stores
classes within a named hierarchy based on dot-separated module names,
as well as the Automap.prepare.modulename_for_table
parameter
which allows for custom __module__
schemes for automapped
classes.
明确指用法¶
Specifying Classes Explicitly
小技巧
如果应用程序中预计显式类会占据重要地位,
考虑改用 DeferredReflection
。
automap
扩展允许显式定义类,
方式类似于 DeferredReflection
类。
从 AutomapBase
扩展的类像常规的声明式类一样工作,
但在构造后不会立即映射,而是在我们调用 AutomapBase.prepare()
时映射。
AutomapBase.prepare()
方法将利用我们基于表名建立的类。
如果我们的模式包含表 user
和 address
,
我们可以定义一个或两个类来使用:
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine
# automap base
Base = automap_base()
# 为 'user' 表预声明 User 类
class User(Base):
__tablename__ = "user"
# 重写 schema 元素,如列
user_name = Column("name", String)
# 也可以重写关系,如有需要
# 我们必须使用 automap 默认使用的关系名称,
# 还必须引用 automap 会为 "address" 生成的类名
address_collection = relationship("address", collection_class=set)
# 反射
engine = create_engine("sqlite:///mydatabase.db")
Base.prepare(autoload_with=engine)
# 我们仍然有从表名 "address" 生成的 Address 类,
# 但 User 现在与 Base.classes.User 相同
Address = Base.classes.address
u1 = session.query(User).first()
print(u1.address_collection)
# backref 仍然存在:
a1 = session.query(Address).first()
print(a1.user)
上述示例中,一个比较复杂的细节是,我们演示了如何覆盖
automap 本应创建的 relationship()
对象。
为此,我们需要确保名称与 automap 通常生成的名称匹配,
即关系名称应为 User.address_collection
,
从 automap 角度看,所引用的类名为 address
,
尽管在我们使用此类时,它被称为 Address
。
小技巧
If explicit classes are expected to be prominent in an application,
consider using DeferredReflection
instead.
The automap
extension allows classes to be defined
explicitly, in a way similar to that of the DeferredReflection
class.
Classes that extend from AutomapBase
act like regular declarative
classes, but are not immediately mapped after their construction, and are
instead mapped when we call AutomapBase.prepare()
. The
AutomapBase.prepare()
method will make use of the classes we’ve
established based on the table name we use. If our schema contains tables
user
and address
, we can define one or both of the classes to be used:
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine
# automap base
Base = automap_base()
# pre-declare User for the 'user' table
class User(Base):
__tablename__ = "user"
# override schema elements like Columns
user_name = Column("name", String)
# override relationships too, if desired.
# we must use the same name that automap would use for the
# relationship, and also must refer to the class name that automap will
# generate for "address"
address_collection = relationship("address", collection_class=set)
# reflect
engine = create_engine("sqlite:///mydatabase.db")
Base.prepare(autoload_with=engine)
# we still have Address generated from the tablename "address",
# but User is the same as Base.classes.User now
Address = Base.classes.address
u1 = session.query(User).first()
print(u1.address_collection)
# the backref is still there:
a1 = session.query(Address).first()
print(a1.user)
Above, one of the more intricate details is that we illustrated overriding
one of the relationship()
objects that automap would have created.
To do this, we needed to make sure the names match up with what automap
would normally generate, in that the relationship name would be
User.address_collection
and the name of the class referred to, from
automap’s perspective, is called address
, even though we are referring to
it as Address
within our usage of this class.
覆盖命名用法¶
Overriding Naming Schemes
automap
的任务是基于模式生成映射类和关系名称,
这意味着它在确定这些名称时会有决策点。
这些决策点是通过函数提供的,可以传递给 AutomapBase.prepare()
方法,
并且分别称为 classname_for_table()
、
name_for_scalar_relationship()
,
以及 name_for_collection_relationship()
。 这些函数中的任何一个或所有都可以提供,
如下例所示,我们使用“驼峰命名法”方案来命名类名,并使用 Inflect 包
为集合名称添加“复数化”功能:
import re
import inflect
def camelize_classname(base, tablename, table):
"生成一个 '驼峰化' 的类名,例如"
"'words_and_underscores' -> 'WordsAndUnderscores'"
return str(
tablename[0].upper()
+ re.sub(
r"_([a-z])",
lambda m: m.group(1).upper(),
tablename[1:],
)
)
_pluralizer = inflect.engine()
def pluralize_collection(base, local_cls, referred_cls, constraint):
"生成一个 '非驼峰化'、'复数化' 的类名,例如"
"'SomeTerm' -> 'some_terms'"
referred_name = referred_cls.__name__
uncamelized = re.sub(
r"[A-Z]",
lambda m: "_%s" % m.group(0).lower(),
referred_name,
)[1:]
pluralized = _pluralizer.plural(uncamelized)
return pluralized
from sqlalchemy.ext.automap import automap_base
Base = automap_base()
engine = create_engine("sqlite:///mydatabase.db")
Base.prepare(
autoload_with=engine,
classname_for_table=camelize_classname,
name_for_collection_relationship=pluralize_collection,
)
从上述映射中,我们现在会有 User
和 Address
类,
其中从 User
到 Address
的集合称为 User.addresses
:
User, Address = Base.classes.User, Base.classes.Address
u1 = User(addresses=[Address(email="foo@bar.com")])
automap
is tasked with producing mapped classes and
relationship names based on a schema, which means it has decision points in how
these names are determined. These three decision points are provided using
functions which can be passed to the AutomapBase.prepare()
method, and
are known as classname_for_table()
,
name_for_scalar_relationship()
,
and name_for_collection_relationship()
. Any or all of these
functions are provided as in the example below, where we use a “camel case”
scheme for class names and a “pluralizer” for collection names using the
Inflect package:
import re
import inflect
def camelize_classname(base, tablename, table):
"Produce a 'camelized' class name, e.g."
"'words_and_underscores' -> 'WordsAndUnderscores'"
return str(
tablename[0].upper()
+ re.sub(
r"_([a-z])",
lambda m: m.group(1).upper(),
tablename[1:],
)
)
_pluralizer = inflect.engine()
def pluralize_collection(base, local_cls, referred_cls, constraint):
"Produce an 'uncamelized', 'pluralized' class name, e.g."
"'SomeTerm' -> 'some_terms'"
referred_name = referred_cls.__name__
uncamelized = re.sub(
r"[A-Z]",
lambda m: "_%s" % m.group(0).lower(),
referred_name,
)[1:]
pluralized = _pluralizer.plural(uncamelized)
return pluralized
from sqlalchemy.ext.automap import automap_base
Base = automap_base()
engine = create_engine("sqlite:///mydatabase.db")
Base.prepare(
autoload_with=engine,
classname_for_table=camelize_classname,
name_for_collection_relationship=pluralize_collection,
)
From the above mapping, we would now have classes User
and Address
,
where the collection from User
to Address
is called
User.addresses
:
User, Address = Base.classes.User, Base.classes.Address
u1 = User(addresses=[Address(email="foo@bar.com")])
关系用法¶
Relationship Detection
自动映射完成的大部分工作是基于外键生成 relationship()
结构。
在多对一和一对多关系中,工作机制如下:
给定的
Table
,已知映射到特定类, 将检查是否存在ForeignKeyConstraint
对象。从每个
ForeignKeyConstraint
中,匹配 其中的远程Table
对象,并将其与 要映射到的类进行匹配,如果没有匹配,则跳过。当我们检查的
ForeignKeyConstraint
对应于来自直接映射类的引用时,关系将设置为多对一, 指向被引用的类;在被引用的类上将创建一个相应的 一对多 backref,指向这个类。如果
ForeignKeyConstraint
中的任何列 是不可为空的(例如nullable=False
), 则会将relationship.cascade
关键字参数 设置为all, delete-orphan
,并将其传递给关系或 backref。 如果ForeignKeyConstraint
报告ForeignKeyConstraint.ondelete
被设置为CASCADE
(对于不可为空的列)或SET NULL
(对于可为空的列), 则relationship.passive_deletes
标志会被设置为True
, 并包含在关系的关键字参数中。 请注意,并非所有后端都支持反射 ON DELETE。关系的名称是通过
AutomapBase.prepare.name_for_scalar_relationship
和AutomapBase.prepare.name_for_collection_relationship
可调用函数确定的。需要注意的是,默认的关系命名是基于 实际类名 进行的。 如果你为某个类显式指定了名称,或指定了其他类命名方案, 那么关系名称将会从该名称中派生。类会检查是否存在与这些名称匹配的已映射属性。 如果在一侧检测到映射属性,但另一侧没有,
AutomapBase
会尝试在缺失的一侧创建关系, 然后使用relationship.back_populates
参数指向另一侧的关系。在通常情况下,如果两侧都没有关系,
AutomapBase.prepare()
会在”多对一”的一侧生成relationship()
, 并使用relationship.backref
参数将其匹配到另一侧。relationship()
和可选的backref()
的生成 会交给AutomapBase.prepare.generate_relationship
函数处理, 该函数可以由最终用户提供,以增强传递给relationship()
或backref()
的参数,或使用这些函数的自定义实现。
The vast majority of what automap accomplishes is the generation of
relationship()
structures based on foreign keys. The mechanism
by which this works for many-to-one and one-to-many relationships is as
follows:
A given
Table
, known to be mapped to a particular class, is examined forForeignKeyConstraint
objects.From each
ForeignKeyConstraint
, the remoteTable
object present is matched up to the class to which it is to be mapped, if any, else it is skipped.As the
ForeignKeyConstraint
we are examining corresponds to a reference from the immediate mapped class, the relationship will be set up as a many-to-one referring to the referred class; a corresponding one-to-many backref will be created on the referred class referring to this class.If any of the columns that are part of the
ForeignKeyConstraint
are not nullable (e.g.nullable=False
), arelationship.cascade
keyword argument ofall, delete-orphan
will be added to the keyword arguments to be passed to the relationship or backref. If theForeignKeyConstraint
reports thatForeignKeyConstraint.ondelete
is set toCASCADE
for a not null orSET NULL
for a nullable set of columns, the optionrelationship.passive_deletes
flag is set toTrue
in the set of relationship keyword arguments. Note that not all backends support reflection of ON DELETE.The names of the relationships are determined using the
AutomapBase.prepare.name_for_scalar_relationship
andAutomapBase.prepare.name_for_collection_relationship
callable functions. It is important to note that the default relationship naming derives the name from the the actual class name. If you’ve given a particular class an explicit name by declaring it, or specified an alternate class naming scheme, that’s the name from which the relationship name will be derived.The classes are inspected for an existing mapped property matching these names. If one is detected on one side, but none on the other side,
AutomapBase
attempts to create a relationship on the missing side, then uses therelationship.back_populates
parameter in order to point the new relationship to the other side.In the usual case where no relationship is on either side,
AutomapBase.prepare()
produces arelationship()
on the “many-to-one” side and matches it to the other using therelationship.backref
parameter.Production of the
relationship()
and optionally thebackref()
is handed off to theAutomapBase.prepare.generate_relationship
function, which can be supplied by the end-user in order to augment the arguments passed torelationship()
orbackref()
or to make use of custom implementations of these functions.
自定义关系用法¶
Custom Relationship Arguments
AutomapBase.prepare.generate_relationship
钩子可以用来
为关系添加参数。对于大多数情况,我们可以使用现有的
generate_relationship()
函数,返回
对象,并在给定的关键字字典中添加我们的参数。
下面是一个示例,演示如何将
relationship.cascade
和
relationship.passive_deletes
选项传递给所有一对多关系:
from sqlalchemy.ext.automap import generate_relationship
from sqlalchemy.orm import interfaces
def _gen_relationship(
base, direction, return_fn, attrname, local_cls, referred_cls, **kw
):
if direction is interfaces.ONETOMANY:
kw["cascade"] = "all, delete-orphan"
kw["passive_deletes"] = True
# 使用内置函数来实际返回
# 结果。
return generate_relationship(
base, direction, return_fn, attrname, local_cls, referred_cls, **kw
)
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine
# automap base
Base = automap_base()
engine = create_engine("sqlite:///mydatabase.db")
Base.prepare(autoload_with=engine, generate_relationship=_gen_relationship)
The AutomapBase.prepare.generate_relationship
hook can be used
to add parameters to relationships. For most cases, we can make use of the
existing generate_relationship()
function to return
the object, after augmenting the given keyword dictionary with our own
arguments.
Below is an illustration of how to send
relationship.cascade
and
relationship.passive_deletes
options along to all one-to-many relationships:
from sqlalchemy.ext.automap import generate_relationship
from sqlalchemy.orm import interfaces
def _gen_relationship(
base, direction, return_fn, attrname, local_cls, referred_cls, **kw
):
if direction is interfaces.ONETOMANY:
kw["cascade"] = "all, delete-orphan"
kw["passive_deletes"] = True
# make use of the built-in function to actually return
# the result.
return generate_relationship(
base, direction, return_fn, attrname, local_cls, referred_cls, **kw
)
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine
# automap base
Base = automap_base()
engine = create_engine("sqlite:///mydatabase.db")
Base.prepare(autoload_with=engine, generate_relationship=_gen_relationship)
多对多用法¶
Many-to-Many relationships
automap
将生成多对多关系,例如那些包含 secondary
参数的关系。生成这些关系的过程如下:
给定的
Table
会在映射类被分配之前,检查是否存在ForeignKeyConstraint
对象。如果表包含恰好两个
ForeignKeyConstraint
对象,并且表中的所有列都是这两个ForeignKeyConstraint
对象的一部分,则该表被假定为一个“secondary”表,并且 不会直接映射。该
Table
所引用的两个(或一个,自引用的情况)外部表会与它们将要映射到的类进行匹配(如果有的话)。如果找到两侧的映射类,则会在这两个类之间创建一个多对多的双向
relationship()
/backref()
配对。多对多的覆盖逻辑与一对多/多对一的逻辑相同;会调用
generate_relationship()
函数来生成结构,并保持现有属性。
automap
will generate many-to-many relationships, e.g.
those which contain a secondary
argument. The process for producing these
is as follows:
A given
Table
is examined forForeignKeyConstraint
objects, before any mapped class has been assigned to it.If the table contains two and exactly two
ForeignKeyConstraint
objects, and all columns within this table are members of these twoForeignKeyConstraint
objects, the table is assumed to be a “secondary” table, and will not be mapped directly.The two (or one, for self-referential) external tables to which the
Table
refers to are matched to the classes to which they will be mapped, if any.If mapped classes for both sides are located, a many-to-many bi-directional
relationship()
/backref()
pair is created between the two classes.The override logic for many-to-many works the same as that of one-to-many/ many-to-one; the
generate_relationship()
function is called upon to generate the structures and existing attributes will be maintained.
具有继承的用法¶
Relationships with Inheritance
automap
不会在继承关系中的两个类之间生成任何关系。也就是说,给定以下两个类:
- class Employee(Base):
__tablename__ = “employee” id = Column(Integer, primary_key=True) type = Column(String(50)) __mapper_args__ = {
“polymorphic_identity”: “employee”, “polymorphic_on”: type,
}
- class Engineer(Employee):
__tablename__ = “engineer” id = Column(Integer, ForeignKey(“employee.id”), primary_key=True) __mapper_args__ = {
“polymorphic_identity”: “engineer”,
}
从 Engineer
到 Employee
的外键用于建立两者之间的连接继承关系,而不是作为关系使用。
请注意,这意味着 automap 不会为从子类到父类的外键生成 任何 关系。如果映射中有从子类到父类的实际关系,这些关系需要显式声明。以下示例中,由于 Engineer
到 Employee
有两个独立的外键,我们需要设置我们希望的关系,并明确指定 inherit_condition
,因为这些是 SQLAlchemy 无法自动推断的内容:
- class Employee(Base):
__tablename__ = “employee” id = Column(Integer, primary_key=True) type = Column(String(50))
- __mapper_args__ = {
“polymorphic_identity”: “employee”, “polymorphic_on”: type,
}
- class Engineer(Employee):
__tablename__ = “engineer” id = Column(Integer, ForeignKey(“employee.id”), primary_key=True) favorite_employee_id = Column(Integer, ForeignKey(“employee.id”))
- favorite_employee = relationship(
Employee, foreign_keys=favorite_employee_id
)
- __mapper_args__ = {
“polymorphic_identity”: “engineer”, “inherit_condition”: id == Employee.id,
}
automap
will not generate any relationships between
two classes that are in an inheritance relationship. That is, with two
classes given as follows:
class Employee(Base):
__tablename__ = "employee"
id = Column(Integer, primary_key=True)
type = Column(String(50))
__mapper_args__ = {
"polymorphic_identity": "employee",
"polymorphic_on": type,
}
class Engineer(Employee):
__tablename__ = "engineer"
id = Column(Integer, ForeignKey("employee.id"), primary_key=True)
__mapper_args__ = {
"polymorphic_identity": "engineer",
}
The foreign key from Engineer
to Employee
is used not for a
relationship, but to establish joined inheritance between the two classes.
Note that this means automap will not generate any relationships
for foreign keys that link from a subclass to a superclass. If a mapping
has actual relationships from subclass to superclass as well, those
need to be explicit. Below, as we have two separate foreign keys
from Engineer
to Employee
, we need to set up both the relationship
we want as well as the inherit_condition
, as these are not things
SQLAlchemy can guess:
class Employee(Base):
__tablename__ = "employee"
id = Column(Integer, primary_key=True)
type = Column(String(50))
__mapper_args__ = {
"polymorphic_identity": "employee",
"polymorphic_on": type,
}
class Engineer(Employee):
__tablename__ = "engineer"
id = Column(Integer, ForeignKey("employee.id"), primary_key=True)
favorite_employee_id = Column(Integer, ForeignKey("employee.id"))
favorite_employee = relationship(
Employee, foreign_keys=favorite_employee_id
)
__mapper_args__ = {
"polymorphic_identity": "engineer",
"inherit_condition": id == Employee.id,
}
处理简单命名用法¶
Handling Simple Naming Conflicts
在映射过程中如果出现命名冲突,可以根据需要覆盖 classname_for_table()
、name_for_scalar_relationship()
和 name_for_collection_relationship()
。例如,如果 automap 尝试为多对一关系命名时与现有列名称相同,可以有条件地选择其他命名约定。考虑以下模式:
CREATE TABLE table_a (
id INTEGER PRIMARY KEY
);
CREATE TABLE table_b (
id INTEGER PRIMARY KEY,
table_a INTEGER,
FOREIGN KEY(table_a) REFERENCES table_a(id)
);
上述模式将首先将 table_a
表自动映射为名为 table_a
的类;然后,它会将一个与该类相关的关系映射到 table_b
类上,关系的名称也为 table_a
。这种关系名称与映射列 table_b.table_a
冲突,因此会在映射时抛出错误。
我们可以通过使用下划线来解决此冲突,如下所示:
- def name_for_scalar_relationship(
base, local_cls, referred_cls, constraint
- ):
name = referred_cls.__name__.lower() local_table = local_cls.__table__ if name in local_table.columns:
newname = name + “_” warnings.warn(
“Already detected name %s present. using %s” % (name, newname)
) return newname
return name
- Base.prepare(
autoload_with=engine, name_for_scalar_relationship=name_for_scalar_relationship,
)
另外,我们可以改变列端的名称。可以使用 显式命名声明性映射列 中描述的技术,通过显式地为列指定新名称来修改映射的列:
Base = automap_base()
- class TableB(Base):
__tablename__ = “table_b” _table_a = Column(“table_a”, ForeignKey(“table_a.id”))
Base.prepare(autoload_with=engine)
In the case of naming conflicts during mapping, override any of
classname_for_table()
, name_for_scalar_relationship()
,
and name_for_collection_relationship()
as needed. For example, if
automap is attempting to name a many-to-one relationship the same as an
existing column, an alternate convention can be conditionally selected. Given
a schema:
CREATE TABLE table_a (
id INTEGER PRIMARY KEY
);
CREATE TABLE table_b (
id INTEGER PRIMARY KEY,
table_a INTEGER,
FOREIGN KEY(table_a) REFERENCES table_a(id)
);
The above schema will first automap the table_a
table as a class named
table_a
; it will then automap a relationship onto the class for table_b
with the same name as this related class, e.g. table_a
. This
relationship name conflicts with the mapping column table_b.table_a
,
and will emit an error on mapping.
We can resolve this conflict by using an underscore as follows:
def name_for_scalar_relationship(
base, local_cls, referred_cls, constraint
):
name = referred_cls.__name__.lower()
local_table = local_cls.__table__
if name in local_table.columns:
newname = name + "_"
warnings.warn(
"Already detected name %s present. using %s" % (name, newname)
)
return newname
return name
Base.prepare(
autoload_with=engine,
name_for_scalar_relationship=name_for_scalar_relationship,
)
Alternatively, we can change the name on the column side. The columns that are mapped can be modified using the technique described at 显式命名声明性映射列, by assigning the column explicitly to a new name:
Base = automap_base()
class TableB(Base):
__tablename__ = "table_b"
_table_a = Column("table_a", ForeignKey("table_a.id"))
Base.prepare(autoload_with=engine)
使用带有明确声明的 Autom用法¶
Using Automap with Explicit Declarations
如前所述,automap 不依赖于反射,并且可以使用任何 Table
对象集合,这些对象位于 MetaData
集合中。因此,automap 也可以用于在完全定义表元数据的模型基础上,生成缺失的关系:
from sqlalchemy.ext.automap import automap_base from sqlalchemy import Column, Integer, String, ForeignKey
Base = automap_base()
- class User(Base):
__tablename__ = “user”
id = Column(Integer, primary_key=True) name = Column(String)
- class Address(Base):
__tablename__ = “address”
id = Column(Integer, primary_key=True) email = Column(String) user_id = Column(ForeignKey(“user.id”))
# 生成关系 Base.prepare()
# 映射完成,生成 “address_collection” 和 # “user” 关系 a1 = Address(email=”u1”) a2 = Address(email=”u2”) u1 = User(address_collection=[a1, a2]) assert a1.user is u1
上述代码中,给定了基本完整的 User
和 Address
映射,定义在 Address.user_id
上的 ForeignKey
使得在映射类中生成了双向关系对 Address.user
和 User.address_collection
。
请注意,当继承 AutomapBase
时,必须调用 AutomapBase.prepare()
方法;如果未调用,已声明的类将处于未映射状态。
As noted previously, automap has no dependency on reflection, and can make
use of any collection of Table
objects within a
MetaData
collection. From this, it follows that automap can also be used
generate missing relationships given an otherwise complete model that fully
defines table metadata:
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import Column, Integer, String, ForeignKey
Base = automap_base()
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = Column(String)
class Address(Base):
__tablename__ = "address"
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(ForeignKey("user.id"))
# produce relationships
Base.prepare()
# mapping is complete, with "address_collection" and
# "user" relationships
a1 = Address(email="u1")
a2 = Address(email="u2")
u1 = User(address_collection=[a1, a2])
assert a1.user is u1
Above, given mostly complete User
and Address
mappings, the
ForeignKey
which we defined on Address.user_id
allowed a
bidirectional relationship pair Address.user
and
User.address_collection
to be generated on the mapped classes.
Note that when subclassing AutomapBase
,
the AutomapBase.prepare()
method is required; if not called, the classes
we’ve declared are in an un-mapped state.
拦截列用法¶
Intercepting Column Definitions
MetaData
和 Table
对象支持一个事件钩子 DDLEvents.column_reflect()
,可以在构造 Column
对象之前拦截关于数据库列的信息。例如,如果我们希望使用诸如 "attr_<columnname>"
的命名约定来映射列,可以使用该事件,如下所示:
@event.listens_for(Base.metadata, “column_reflect”) def column_reflect(inspector, table, column_info):
# 设置列的 key 为 “attr_<小写列名>” column_info[“key”] = “attr_%s” % column_info[“name”].lower()
# 执行反射 Base.prepare(autoload_with=engine)
在 1.4.0b2 版本加入: DDLEvents.column_reflect()
事件
可以应用于 MetaData
对象。
The MetaData
and Table
objects support an
event hook DDLEvents.column_reflect()
that may be used to intercept
the information reflected about a database column before the Column
object is constructed. For example if we wanted to map columns using a
naming convention such as "attr_<columnname>"
, the event could
be applied as:
@event.listens_for(Base.metadata, "column_reflect")
def column_reflect(inspector, table, column_info):
# set column.key = "attr_<lower_case_name>"
column_info["key"] = "attr_%s" % column_info["name"].lower()
# run reflection
Base.prepare(autoload_with=engine)
在 1.4.0b2 版本加入: the DDLEvents.column_reflect()
event
may be applied to a MetaData
object.
API 参考¶
API Reference
Object Name | Description |
---|---|
automap_base([declarative_base], **kw) |
Produce a declarative automap base. |
Base class for an “automap” schema. |
|
classname_for_table(base, tablename, table) |
Return the class name that should be used, given the name of a table. |
generate_relationship(base, direction, return_fn, attrname, ..., **kw) |
Generate a |
name_for_collection_relationship(base, local_cls, referred_cls, constraint) |
Return the attribute name that should be used to refer from one class to another, for a collection reference. |
name_for_scalar_relationship(base, local_cls, referred_cls, constraint) |
Return the attribute name that should be used to refer from one class to another, for a scalar object reference. |
- function sqlalchemy.ext.automap.automap_base(declarative_base: Type[Any] | None = None, **kw: Any) Any ¶
Produce a declarative automap base.
This function produces a new base class that is a product of the
AutomapBase
class as well a declarative base produced bydeclarative_base()
.All parameters other than
declarative_base
are keyword arguments that are passed directly to thedeclarative_base()
function.
- class sqlalchemy.ext.automap.AutomapBase¶
Base class for an “automap” schema.
The
AutomapBase
class can be compared to the “declarative base” class that is produced by thedeclarative_base()
function. In practice, theAutomapBase
class is always used as a mixin along with an actual declarative base.A new subclassable
AutomapBase
is typically instantiated using theautomap_base()
function.参见
-
attribute
sqlalchemy.ext.automap.AutomapBase.
by_module: ClassVar[ByModuleProperties]¶ An instance of
Properties
containing a hierarchal structure of dot-separated module names linked to classes.This collection is an alternative to the
AutomapBase.classes
collection that is useful when making use of theAutomapBase.prepare.modulename_for_table
parameter, which will apply distinct__module__
attributes to generated classes.The default
__module__
an automap-generated class issqlalchemy.ext.automap
; to access this namespace usingAutomapBase.by_module
looks like:User = Base.by_module.sqlalchemy.ext.automap.User
If a class had a
__module__
ofmymodule.account
, accessing this namespace looks like:MyClass = Base.by_module.mymodule.account.MyClass
在 2.0 版本加入.
参见
-
attribute
sqlalchemy.ext.automap.AutomapBase.
classes: ClassVar[Properties[Type[Any]]]¶ An instance of
Properties
containing classes.This object behaves much like the
.c
collection on a table. Classes are present under the name they were given, e.g.:Base = automap_base() Base.prepare(autoload_with=some_engine) User, Address = Base.classes.User, Base.classes.Address
For class names that overlap with a method name of
Properties
, such asitems()
, the getitem form is also supported:Item = Base.classes["items"]
-
attribute
sqlalchemy.ext.automap.AutomapBase.
metadata: ClassVar[MetaData]¶ Refers to the
MetaData
collection that will be used for newTable
objects.参见
-
classmethod
sqlalchemy.ext.automap.AutomapBase.
prepare(autoload_with: Engine | None = None, engine: Any | None = None, reflect: bool = False, schema: str | None = None, classname_for_table: PythonNameForTableType | None = None, modulename_for_table: PythonNameForTableType | None = None, collection_class: Any | None = None, name_for_scalar_relationship: NameForScalarRelationshipType | None = None, name_for_collection_relationship: NameForCollectionRelationshipType | None = None, generate_relationship: GenerateRelationshipType | None = None, reflection_options: Dict[_KT, _VT] | immutabledict[_KT, _VT] = {}) None ¶ Extract mapped classes and relationships from the
MetaData
and perform mappings.For full documentation and examples see 基本用法.
- 参数:
autoload_with¶ – an
Engine
orConnection
with which to perform schema reflection; when specified, theMetaData.reflect()
method will be invoked within the scope of this method.engine¶ –
legacy; use
AutomapBase.autoload_with
. Used to indicate theEngine
orConnection
with which to reflect tables with, ifAutomapBase.reflect
is True.自 1.4 版本弃用: The
AutomapBase.prepare.engine
parameter is deprecated and will be removed in a future release. Please use theAutomapBase.prepare.autoload_with
parameter.reflect¶ –
legacy; use
AutomapBase.autoload_with
. Indicates thatMetaData.reflect()
should be invoked.自 1.4 版本弃用: The
AutomapBase.prepare.reflect
parameter is deprecated and will be removed in a future release. Reflection is enabled whenAutomapBase.prepare.autoload_with
is passed.classname_for_table¶ – callable function which will be used to produce new class names, given a table name. Defaults to
classname_for_table()
.modulename_for_table¶ –
callable function which will be used to produce the effective
__module__
for an internally generated class, to allow for multiple classes of the same name in a single automap base which would be in different “modules”.Defaults to
None
, which will indicate that__module__
will not be set explicitly; the Python runtime will use the valuesqlalchemy.ext.automap
for these classes.When assigning
__module__
to generated classes, they can be accessed based on dot-separated module names using theAutomapBase.by_module
collection. Classes that have an explicit__module_
assigned using this hook do not get placed into theAutomapBase.classes
collection, only intoAutomapBase.by_module
.在 2.0 版本加入.
参见
name_for_scalar_relationship¶ – callable function which will be used to produce relationship names for scalar relationships. Defaults to
name_for_scalar_relationship()
.name_for_collection_relationship¶ – callable function which will be used to produce relationship names for collection-oriented relationships. Defaults to
name_for_collection_relationship()
.generate_relationship¶ – callable function which will be used to actually generate
relationship()
andbackref()
constructs. Defaults togenerate_relationship()
.collection_class¶ – the Python collection class that will be used when a new
relationship()
object is created that represents a collection. Defaults tolist
.schema¶ –
Schema name to reflect when reflecting tables using the
AutomapBase.prepare.autoload_with
parameter. The name is passed to theMetaData.reflect.schema
parameter ofMetaData.reflect()
. When omitted, the default schema in use by the database connection is used.备注
The
AutomapBase.prepare.schema
parameter supports reflection of a single schema at a time. In order to include tables from many schemas, use multiple calls toAutomapBase.prepare()
.For an overview of multiple-schema automap including the use of additional naming conventions to resolve table name conflicts, see the section 从多个架构生成用法.
在 2.0 版本加入:
AutomapBase.prepare()
supports being directly invoked any number of times, keeping track of tables that have already been processed to avoid processing them a second time.reflection_options¶ –
When present, this dictionary of options will be passed to
MetaData.reflect()
to supply general reflection-specific options likeonly
and/or dialect-specific options likeoracle_resolve_synonyms
.在 1.4 版本加入.
-
attribute
- function sqlalchemy.ext.automap.classname_for_table(base: Type[Any], tablename: str, table: Table) str ¶
Return the class name that should be used, given the name of a table.
The default implementation is:
return str(tablename)
Alternate implementations can be specified using the
AutomapBase.prepare.classname_for_table
parameter.- 参数:
- 返回:
a string class name.
备注
In Python 2, the string used for the class name must be a non-Unicode object, e.g. a
str()
object. The.name
attribute ofTable
is typically a Python unicode subclass, so thestr()
function should be applied to this name, after accounting for any non-ASCII characters.
- function sqlalchemy.ext.automap.name_for_scalar_relationship(base: Type[Any], local_cls: Type[Any], referred_cls: Type[Any], constraint: ForeignKeyConstraint) str ¶
Return the attribute name that should be used to refer from one class to another, for a scalar object reference.
The default implementation is:
return referred_cls.__name__.lower()
Alternate implementations can be specified using the
AutomapBase.prepare.name_for_scalar_relationship
parameter.- 参数:
base¶ – the
AutomapBase
class doing the prepare.local_cls¶ – the class to be mapped on the local side.
referred_cls¶ – the class to be mapped on the referring side.
constraint¶ – the
ForeignKeyConstraint
that is being inspected to produce this relationship.
- function sqlalchemy.ext.automap.name_for_collection_relationship(base: Type[Any], local_cls: Type[Any], referred_cls: Type[Any], constraint: ForeignKeyConstraint) str ¶
Return the attribute name that should be used to refer from one class to another, for a collection reference.
The default implementation is:
return referred_cls.__name__.lower() + "_collection"
Alternate implementations can be specified using the
AutomapBase.prepare.name_for_collection_relationship
parameter.- 参数:
base¶ – the
AutomapBase
class doing the prepare.local_cls¶ – the class to be mapped on the local side.
referred_cls¶ – the class to be mapped on the referring side.
constraint¶ – the
ForeignKeyConstraint
that is being inspected to produce this relationship.
- function sqlalchemy.ext.automap.generate_relationship(base: Type[Any], direction: RelationshipDirection, return_fn: Callable[..., Relationship[Any]] | Callable[..., ORMBackrefArgument], attrname: str, local_cls: Type[Any], referred_cls: Type[Any], **kw: Any) Relationship[Any] | ORMBackrefArgument ¶
Generate a
relationship()
orbackref()
on behalf of two mapped classes.An alternate implementation of this function can be specified using the
AutomapBase.prepare.generate_relationship
parameter.The default implementation of this function is as follows:
if return_fn is backref: return return_fn(attrname, **kw) elif return_fn is relationship: return return_fn(referred_cls, **kw) else: raise TypeError("Unknown relationship function: %s" % return_fn)
- 参数:
base¶ – the
AutomapBase
class doing the prepare.direction¶ – indicate the “direction” of the relationship; this will be one of
ONETOMANY
,MANYTOONE
,MANYTOMANY
.return_fn¶ – the function that is used by default to create the relationship. This will be either
relationship()
orbackref()
. Thebackref()
function’s result will be used to produce a newrelationship()
in a second step, so it is critical that user-defined implementations correctly differentiate between the two functions, if a custom relationship function is being used.attrname¶ – the attribute name to which this relationship is being assigned. If the value of
generate_relationship.return_fn
is thebackref()
function, then this name is the name that is being assigned to the backref.local_cls¶ – the “local” class to which this relationship or backref will be locally present.
referred_cls¶ – the “referred” class to which the relationship or backref refers to.
**kw¶ – all additional keyword arguments are passed along to the function.
- 返回:
a
relationship()
orbackref()
construct, as dictated by thegenerate_relationship.return_fn
parameter.