核心和 ORM 示例¶

Core and ORM Examples

中文

SQLAlchemy 发行版包含各种代码示例，展示了一些典型和一些不太典型的模式。所有示例都可以运行，并且可以在发行版的 /examples 目录中找到。所有示例的描述和源代码都可以在这里找到。

更多 SQLAlchemy 示例，包括一些用户贡献的，可以在 wiki 上找到 https://www.sqlalchemy.org/trac/wiki/UsageRecipes 。

英文

The SQLAlchemy distribution includes a variety of code examples illustrating a select set of patterns, some typical and some not so typical. All are runnable and can be found in the /examples directory of the distribution. Descriptions and source code for all can be found here.

Additional SQLAlchemy examples, some user contributed, are available on the wiki at https://www.sqlalchemy.org/trac/wiki/UsageRecipes.

继承映射配方¶

Inheritance Mapping Recipes

Basic Inheritance Mappings¶

中文

单表、连接表和具体表继承的工作示例，如映射类继承层次结构中所述。

英文

Working examples of single-table, joined-table, and concrete-table inheritance as described in 映射类继承层次结构.

Listing of files:

joined.py - Joined-table (table-per-subclass) inheritance example.
single.py - Single-table (table-per-hierarchy) inheritance example.
concrete.py - Concrete-table (table-per-class) inheritance example.

特殊 API¶

Special APIs

属性检测¶

Attribute Instrumentation

中文

说明对 SQLAlchemy 的属性管理系统的修改的示例。

英文

Examples illustrating modifications to SQLAlchemy’s attribute management system.

Listing of files:

active_column_defaults.py - Illustrates use of the AttributeEvents.init_scalar() event, in conjunction with Core column defaults to provide ORM objects that automatically produce the default value when an un-set attribute is accessed.
custom_management.py - Illustrates customized class instrumentation, using the sqlalchemy.ext.instrumentation extension package.
listen_for_events.py - Illustrates how to attach events to all instrumented attributes and listen for change events.

水平分片¶

Horizontal Sharding

中文

使用 SQLAlchemy 分片（Sharding）API 的基础示例。分片指的是在多个数据库之间横向扩展数据的能力。

“分片”映射的基本组成部分包括：

多个 Engine 实例，每个实例分配一个“分片 ID”（shard id）。这些 Engine 实例可以指向不同的数据库，或是同一数据库中不同的 schema / accounts，甚至也可以仅通过一些选项加以区分，使其在使用时访问不同的 schema 或表。
一个函数，它接收一个待保存的实例并返回一个分片 ID；这个函数被称为 “shard_chooser”。
一个函数，它接收一个实例标识符，并返回一个适用的分片 ID 列表；这个函数称为 “id_chooser”。如果该函数返回所有分片 ID，则表示将搜索所有分片。
一个函数，它接收一个查询对象并返回一个要尝试的分片 ID 列表；这个函数称为 “query_chooser”。如果返回所有分片 ID，则表示将对所有分片进行查询，并将结果合并。

在这些示例中，我们对相同的基础模型使用了不同类型的分片方式，该模型用于按大陆存储天气数据。我们提供了示例性的 shard_chooser、id_chooser 和 query_chooser 函数。其中 query_chooser 展示了如何通过检查 SQL 表达式元素，尝试推断出所请求的唯一分片。

构建通用的分片操作是一种富有挑战性的方式，目的是为多个数据库之间的实例组织问题提供解决方案。作为一种更简单直白的替代方式，“独立实体（distinct entity）”方法可以显式地将对象分配到不同的表（并可能是不同的数据库节点）中 —— 该方法在 Wiki 页面中有描述，参见 EntityName。

英文

A basic example of using the SQLAlchemy Sharding API. Sharding refers to horizontally scaling data across multiple databases.

The basic components of a “sharded” mapping are:

multiple Engine instances, each assigned a “shard id”. These Engine instances may refer to different databases, or different schemas / accounts within the same database, or they can even be differentiated only by options that will cause them to access different schemas or tables when used.
a function which can return a single shard id, given an instance to be saved; this is called “shard_chooser”
a function which can return a list of shard ids which apply to a particular instance identifier; this is called “id_chooser”.If it returns all shard ids, all shards will be searched.
a function which can return a list of shard ids to try, given a particular Query (“query_chooser”). If it returns all shard ids, all shards will be queried and the results joined together.

In these examples, different kinds of shards are used against the same basic example which accommodates weather data on a per-continent basis. We provide example shard_chooser, id_chooser and query_chooser functions. The query_chooser illustrates inspection of the SQL expression element in order to attempt to determine a single shard being requested.

The construction of generic sharding routines is an ambitious approach to the issue of organizing instances among multiple databases. For a more plain-spoken alternative, the “distinct entity” approach is a simple method of assigning objects to different tables (and potentially database nodes) in an explicit way - described on the wiki at EntityName.

Listing of files:

asyncio.py - Illustrates sharding API used with asyncio.
separate_databases.py - Illustrates sharding using distinct SQLite databases.
separate_schema_translates.py - Illustrates sharding using a single database with multiple schemas, where a different “schema_translates_map” can be used for each shard.
separate_tables.py - Illustrates sharding using a single SQLite database, that will however have multiple tables using a naming convention.

扩展核心¶

Extending Core

Extending Statements like SELECT, INSERT, etc¶

中文

扩展 Select 构造以包含新的非 SQL 标准子句 QUALIFY 的详细示例。

此示例说明了自定义 SQL 构造和编译扩展以及称为 SyntaxExtension 的扩展。

英文

A detailed example of extending the Select construct to include a new non-SQL standard clause QUALIFY.

This example illustrates both the 自定义 SQL 构造和编译扩展 as well as an extension known as SyntaxExtension.

Listing of files:

扩展 ORM¶

Extending the ORM

ORM 查询事件¶

ORM Query Events-

中文

这些配方说明了 Session.execute() 使用的 ORM SELECT 行为的增强，以及 2.0 样式的 select() 以及 1.x 样式 Query 对象。

示例包括 with_loader_criteria() 选项以及 SessionEvents.do_orm_execute() 钩子的演示。

从 SQLAlchemy 1.4 开始，Query 构造与 Select 构造统一，因此这两个对象大致相同。

英文

Recipes which illustrate augmentation of ORM SELECT behavior as used by Session.execute() with 2.0 style use of select(), as well as the 1.x style Query object.

Examples include demonstrations of the with_loader_criteria() option as well as the SessionEvents.do_orm_execute() hook.

As of SQLAlchemy 1.4, the Query construct is unified with the Select construct, so that these two objects are mostly the same.

Listing of files:

temporal_range.py - Illustrates a custom per-query criteria that will be applied to selected entities.
filter_public.py - Illustrates a global criteria applied to entities of a particular type.

Dogpile 缓存¶

Dogpile Caching

中文

演示了如何将 dogpile.cache 的功能嵌入到 ORM 查询中，从而实现对缓存的全面控制，以及从长期缓存中获取 “惰性加载”（lazy loaded）属性的能力。

在本演示中，涵盖了以下技术：

使用 SessionEvents.do_orm_execute() 事件钩子
利用基本技术绕过 Session.execute()，从自定义缓存源中获取数据，而不是从数据库中查询。
使用 dogpile.cache 进行基础缓存，借助“区域”（regions）机制对一组固定配置进行全局控制。
使用自定义的 UserDefinedOption 对象，在语句对象中配置选项。

参见

重新执行语句 - 包含一个展示此处技术的一般性示例。

示例代码如下:

# 查询 Person 对象，并指定使用缓存
stmt = select(Person).options(FromCache("default"))

# 指定每个 Person 的 "addresses" 集合也来自缓存
stmt = stmt.options(RelationshipCache(Person.addresses, "default"))

# 执行语句并获取结果
result = session.execute(stmt)

print(result.scalars().all())

运行该示例前，需要确保 SQLAlchemy 和 dogpile.cache 都已安装，或已位于当前 PYTHONPATH 中。该演示将创建一个本地目录用于存放数据文件，插入初始数据并运行。第二次运行该演示时将利用已存在的缓存文件，整个过程只会对两个表执行一条 SQL 语句——但所展示的结果将会通过缓存执行数十次惰性加载操作。

演示脚本本身按复杂度排序，并作为 Python 模块运行以确保相对导入正常工作:

$ python -m examples.dogpile_caching.helloworld

$ python -m examples.dogpile_caching.relationship_caching

$ python -m examples.dogpile_caching.advanced

$ python -m examples.dogpile_caching.local_session_caching

英文

Illustrates how to embed dogpile.cache functionality with ORM queries, allowing full cache control as well as the ability to pull “lazy loaded” attributes from long term cache.

In this demo, the following techniques are illustrated:

Using the SessionEvents.do_orm_execute() event hook
Basic technique of circumventing Session.execute() to pull from a custom cache source instead of the database.
Rudimental caching with dogpile.cache, using “regions” which allow global control over a fixed set of configurations.
Using custom UserDefinedOption objects to configure options in a statement object.

参见

重新执行语句 - includes a general example of the technique presented here.

E.g.:

# query for Person objects, specifying cache
stmt = select(Person).options(FromCache("default"))

# specify that each Person's "addresses" collection comes from
# cache too
stmt = stmt.options(RelationshipCache(Person.addresses, "default"))

# execute and results
result = session.execute(stmt)

print(result.scalars().all())

To run, both SQLAlchemy and dogpile.cache must be installed or on the current PYTHONPATH. The demo will create a local directory for datafiles, insert initial data, and run. Running the demo a second time will utilize the cache files already present, and exactly one SQL statement against two tables will be emitted - the displayed result however will utilize dozens of lazyloads that all pull from cache.

The demo scripts themselves, in order of complexity, are run as Python modules so that relative imports work:

$ python -m examples.dogpile_caching.helloworld

$ python -m examples.dogpile_caching.relationship_caching

$ python -m examples.dogpile_caching.advanced

$ python -m examples.dogpile_caching.local_session_caching

Listing of files:

environment.py - Establish data / cache file paths, and configurations, bootstrap fixture data if necessary.
caching_query.py - Represent functions and classes which allow the usage of Dogpile caching with SQLAlchemy. Introduces a query option called FromCache.
model.py - The datamodel, which represents Person that has multiple Address objects, each with PostalCode, City, Country.
fixture_data.py - Installs some sample data. Here we have a handful of postal codes for a few US/Canadian cities. Then, 100 Person records are installed, each with a randomly selected postal code.
helloworld.py - Illustrate how to load some data, and cache the results.
relationship_caching.py - Illustrates how to add cache options on relationship endpoints, so that lazyloads load from cache.
advanced.py - Illustrate usage of Query combined with the FromCache option, including front-end loading, cache invalidation and collection caching.
local_session_caching.py - This example creates a new dogpile.cache backend that will persist data in a dictionary which is local to the current session. remove() the session and the cache is gone.

核心和 ORM 示例¶

映射配方¶

邻接表¶

关联¶

Asyncio 集成¶

有向图¶

动态关系作为字典¶

通用关联¶

物化路径¶

嵌套集¶

性能¶

文件列表¶

随时间运行所有测试¶

转储单个测试的配置文件¶

编写您自己的套件¶

太空入侵者¶

对象版本控制¶

使用历史表进行版本控制¶

使用时间行进行版本控制¶

垂直属性映射¶

继承映射配方¶

Basic Inheritance Mappings¶

特殊 API¶

属性检测¶

水平分片¶

扩展核心¶

Extending Statements like SELECT, INSERT, etc¶

扩展 ORM¶

ORM 查询事件¶

Dogpile 缓存¶