SQLAlchemy：Python数据库实战(第2版)(txt+pdf+epub+mobi电子书下载)

作者：（美） 贾森·迈尔斯（Jason Myers） 里克·科普兰（Rick Copeland）

出版社：人民邮电出版社有限公司

格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT

SQLAlchemy：Python数据库实战(第2版)试读：

前言

表示新术语或重点强调的内容。● 等宽字体（constant width）

表示程序片段，以及正文中出现的变量、函数名、数据库、数据

类型、环境变量、语句和关键字等。● 加粗等宽字体（constant width bold）

表示应该由用户输入的命令或其他文本。● 斜体等宽字体（constant width italic）

表示应该替换成用户输入的值，或根据上下文替换的值。　该图标表示提示或建议。　该图标表示一般性说明。　该图标表示警告或警示。使用代码示例补充材料（代码示例、练习等）可以从 https://github.com/oreillymedia/essential-sqlalchemy-2e 下载。本书是要帮你完成工作的。一般来说，如果书中提供了示例代码，你可以把它用在你的程序或文档中。除非你使用了很大一部分代码，否则无须联系我们获得许可。比如，用书中的几个代码片段写一个程序无须获得许可，销售或分发 O'Reilly 图书的示例光盘则需要获得许可；引用书中的示例代码回答问题无须获得许可，将书中大量的代码放到你的产品文档中则需要获得许可。我们很希望但并不强制要求你在引用本书内容时加上引用说明。引用说明一般包括书名、作者、出版社和 ISBN。比如：“Essential SQLAlchemy, Second Edition, by Jason Myers and Rick Copeland (O'Reilly). Copyright 2016 Jason Myers and Rick Copeland, 978-1-4919-1646-9.”如果你觉得自己对示例代码的用法超出了上述许可的范围，欢迎你通过 permissions@oreilly.com 与我们联系。O'Reilly SafariSafari（之前称作 Safari Books Online）一个针对企业、政府、教育者和个人的会员制培训和参考平台。会员可以访问来自 250 多家出版商的上千种图书、培训视频、学习路径、互动式教程和精选播放列表，这些出版商包括 O'Reilly Media、Harvard Business Review、Prentice Hall Professional、Addison-Wesley Professional、Microsoft Press、Sams、Que、Peachpit Press、Adobe、Focal Press、Cisco Press、John Wiley & Sons、Syngress、Morgan Kaufmann、IBM Redbooks、Packt、Adobe Press、FT Press、Apress、Manning、New Riders、McGraw-Hill、Jones & Bartlett、Course Technology 等。要了解更多信息，可以访问 http://www.oreilly.com/safari。联系我们请把对本书的评价和问题发给出版社。美国：　　O'Reilly Media, Inc.　　1005 Gravenstein Highway North　　Sebastopol, CA 95472中国：　　北京市西城区西直门南大街 2 号成铭大厦 C 座 807 室（100035）　　奥莱利技术咨询（北京）有限公司O'Reilly 的每一本书都有专属网页，你可以在那儿找到书的相关信息，2包括勘误表 、示例代码以及其他信息。本书的网站地址是：http://oreil.ly/1lwEdiw。2本书中文版勘误请到 http://www.ituring.com.cn/book/1986 查看和提交。——编者注对于本书的评论和技术性问题，请发送电子邮件到：bookquestions@oreilly.com。要了解更多 O'Reilly 图书、培训课程、会议和新闻的信息，请访问以下网站：　　http://www.oreilly.com。我们在 Facebook 的地址如下：http://facebook.com/oreilly。请关注我们的 Twitter 动态：http://twitter.com/oreillymedia。我们的 YouTube 视频地址如下：http://www.youtube.com/oreillymedia。致谢首先，非常感谢 Patrick Altman、Eric Floehr 和 Alex Grönholm 在本书出版前提供的重要反馈。如果没有他们的帮助，本书无疑会有许多技术问题，而且会更难读。其次，感谢 Mike Bayer，正是在他的推荐下我才动笔撰写本书。感谢 Meghan Blanchette 和 Dawn Schanafelt 督促我完成本书的写作，让我成为一个更好的作者，并且包容了我的种种缺点。还要感谢 Brian Dailey 认真审读了本书部分初稿，并提供了很好的反馈，我们相谈甚欢。再次，感谢纳什维尔开发社区对我的支持，特别是 Cal Evans、Jacques Woodcock、Luke Stokes、William Golden 这几位朋友。感谢我的雇主——思科系统公司给了我充足的时间并支持我完成本书。还要感谢 Mechanical Keyboards 公司的 Justin，他为我提供了码字所需的一切。最后，也是最重要的一点：我要感谢我的妻子，感谢她忍受我大声朗读，放任我去写作，并一直给予我支持和鼓励。我爱你，丹尼丝。电子书扫描如下二维码，即可购买本书电子版。SQLAlchemy 入门SQLAlchemy 库用于与各种数据库交互，你可使用一种类似于 Python 类和语句的方式创建数据模型和查询。SQLAlchemy 库是 Mike Bayer 在 2005 年创建的，现在大大小小很多公司都在使用它。事实上，许多公司都把 SQLAlchemy 看作在 Python 中使用关系型数据库的标准方式。SQLAlchemy 可用于连接大多数常见的数据库，比如 Postgres、MySQL、SQLite、Oracle 等。SQLAlchemy 还提供了一种为其他关系型数据库添加支持的方式。Amazon Redshift（使用 PostgreSQL 自定义方言）就是 SQLAlchemy 社区添加的数据库支持的一个很好的例子。在本章中，我们要搞清楚为什么要使用 SQLAlchemy，还要学习它的两种主要模式，以及如何连接到数据库。为什么使用SQLAlchemy使用 SQLAlchemy 的首要原因是，它将你的代码从底层数据库及其相关的 SQL 特性中抽象出来。SQLAlchemy 使用功能强大的常见语句和类型，确保其 SQL 语句为每种数据库和供应商正确、高效地构建出来，而无须你考虑这些。同时，这使得将逻辑从 Oracle 迁移到 PostgreSQL 或从应用程序数据库迁移到数据仓库变得很容易。它还有助于确保数据在提交到数据库之前得到“净化”并正确地进行了转义。这可以避免一些常见的问题，比如 SQL 注入攻击。SQLAlchemy 提供了两种主要的使用模式——SQL 表达式语言（通常称为 Core）和 ORM，这为我们使用 SQLAlchemy 提供了很大的灵活性。这两种模式可以单独使用，也可以一起使用，具体用法取决于你的喜好以及应用程序的需求。SQLAlchemy Core和SQL表达式语言SQL 表达式语言允许我们以 Python 方式使用常见的 SQL 语句和表达式，它是对标准 SQL 语言的简单抽象。SQL 表达式语言关注的是实际的数据库模式，它在经过标准化之后为大量后端数据库提供了一种一致性的语言。SQL 表达式语言也是 SQLAlchemy ORM 的基础。ORMSQLAlchemy ORM 类似于你在其他语言中遇到的对象关系映射（ORM）。它关注应用程序的领域模型，并利用工作单元模式来维护对象状态。它还在 SQL 表达式语言之上做了进一步的抽象，使用户能够以惯用的方式工作。你可以组合或搭配使用 ORM 与 SQL 表达式语言，从而创建出功能更为强大的应用程序。ORM 中用到了一个声明式系统，该系统类似于许多其他 ORM 中使用的 Active-Record 系统，比如 Ruby on Rails 中使用的那个。虽然 ORM 极其有用，但你必须记住，类的关联方式和底层数据库关系的工作方式是有区别的。第 6 章会更加全面地探讨这些方式如何影响你的实现。选择SQLAlchemy Core还是SQLAlchemy ORM在使用 SQLAlchemy 构建应用程序之前，你要决定主要使用 ORM 还是 Core。选择使用 Core 还是 ORM 作为应用程序的主要数据访问层，通常取决于多个因素和个人偏好。Core 和 ORM 使用的语法略有不同，但它们之间最大的区别是把数据看作模式还是业务对象。SQLAlchemy Core 的视图以模式为中心，与传统 SQL 一样，它关注的是表、键和索引结构。SQLAlchemy Core 在数据仓库、报表、分析和其他场景中（在这些场景中，严格控制对未建模的数据进行查询或操作将非常有用）可以大放异彩。强大的数据库连接池和结果集优化非常适合处理大量数据，甚至适合处理多个数据库的数据。但是，如果你主要关注的是领域驱动设计，那么选用 ORM 会更好，它会帮你把元数据和业务对象中的底层模式和结构封装起来。这种封装使得和数据库的交互变得更加容易，就像在使用普通的 Python 代码一样。大多数常见的应用程序都可以通过这种方式进行建模。ORM 还可以有效地把领域驱动设计注入到遗留的应用程序或者到处是原始 SQL 语句的应用程序中。微服务也能从对底层数据库的抽象中获益，它使得开发人员可以只关注正在实现的流程。但是，由于 ORM 构建在 SQLAlchemy Core 之上，所以你也可以借助 ORM 使用 Oracle 数据仓库和 Amazon Redshift 这样的服务，就像和 MySQL 交互一样。因此，ORM 很适合用来合并业务对象和仓库数据。下面列出了几种应用场景，了解它们有助于你在 Core 和 ORM 中做出最佳选择。● 虽然你使用的框架中已经内置了 ORM，但你希望添加更强大的

报表功能，请选用 Core。● 如果你想在一个以模式为中心的视图中查看数据（用法类似于

SQL），请使用 Core。● 如果你的数据不需要业务对象，请使用 Core。● 如果你要把数据看作业务对象，请使用 ORM。● 如果你想快速创建原型，请使用 ORM。● 如果你需要同时使用业务对象和其他与问题域无关的数据，请组

合使用 Core 和 ORM。你已经了解了 SQLAlchemy 的结构以及 Core 和 ORM 之间的区别，接下来开始安装并使用 SQLAlchemy 来连接数据库。安装SQLAlchemy并连接到数据库SQLAlchemy 可以与 Python 2.6、Python 3.3 和 PyPy 2.1 或更高版本一起使用。建议使用 pip 来安装 SQLAlchemy（使用命令 pip install sqlalchemy）。值得注意的是，还可以使用 easy_install 和 distutils 来安装它。不过，相比之下，使用 pip 安装更简单。安装过程中，SQLAlchemy 会尝试构建一些 C 扩展，这些扩展可以加快结果集的处理速度，同时提高内存使用效率。如果你的系统中缺少编译器，所以你想禁用这些扩展，那么可以使用 --global-option=--without-cextensions。请注意，如果没有 C 扩展，SQLAlchemy 的性能会受到影响。你应该在带有 C 扩展的系统上测试代码，然后再进行优化。安装数据库驱动程序默认情况下，SQLAlchemy 直接支持 SQLite3，不需要额外安装驱动程序。不过，在连接其他数据库时需要额外安装一个数据库驱动程序，并且该驱动程序要遵守标准的 Python DBAPI 规范（PEP-249）。这些 DBAPI 为各个数据库服务器所用的方言提供了基础，并且为不同数据库服务器和版本中的独特特性提供了支持。虽然许多数据库都有多种 DBAPI 可用，但我们将只介绍那些最常用的。● PostgreSQL

Psycopg2 为 PostgreSQL 的各个版本和特性提供了广泛的支

持，你可以使用 pip install psycopg2 命令安装它。● MySQL

PyMySQL 是我用来连接 MySQL 数据库服务器的首选 Python

库。可以使用 pip install pymysql 命令安装它。SQLAlchemy 支

持 MySQL 4.1 及更高版本，这是由 MySQL 的密码工作方式造成

的。另外，如果某些语句只在 MySQL 的某个版本中可用，那么，

对于那些不支持这些语句的 MySQL 版本，SQLAlchemy 不会为

它们提供使用这些语句的方法。如果 SQLAlchemy 中的某个组

件或函数在你的环境下不起作用，那你首先要做的是查看一下

MySQL 文档。● 其他

SQLAlchemy 还可以与 Drizzle、Firebird、Oracle、Sybase 和

Microsoft SQL Server 一起使用。SQLAlchemy 社区还为许多其

他的数据库提供了外部方言，如 IBM DB2、Informix、Amazon

Redshift、EXASolution、SAP SQL Anywhere、Monet 等。此

外，SQLAlchemy 还对创建方言提供了很好的支持，第 7 章将讲

解相关内容。既然我们已经安装好 SQLAlchemy 和 DBAPI 了，接下来创建一个引擎去连接数据库。连接到数据库要连接到数据库，需要先创建一个 SQLAlchemy 引擎。SQLAlchemy 引擎为数据库创建一个公共接口来执行 SQL 语句。这是通过包装数据库连接池和方言来实现的，这样它们就可以一起工作，提供对后端数据库的统一访问。如此一来，我们的 Python 代码就不必考虑数据库之间或 DBAPI 之间的差异了。SQLAlchemy 提供了一个函数来创建引擎。在这个函数中，你可以指定连接字符串，以及其他一些可选的关键字参数。连接字符串是一种特殊格式的字符串，包含如下信息：● 数据库类型（Postgres、MySQL 等）● 各数据库类型默认之外的方言（Psycopg2、PyMySQL 等）● 可选的认证细节（用户名和密码）● 数据库位置（数据库服务器的文件或主机名）● 数据库服务器端口（可选）● 数据库名（可选）SQLite 数据库连接字符串指定了一个特定的数据库文件或存储位置。示例 P-1 定义了一个存储在当前目录下、名为 cookies.db 的 SQLite 数据库文件，当前目录由第二行代码中的相对路径指定，第三行代码是一个内存数据库，第四行（Unix）和第五行（Windows）中指定了数据库文件的完整路径。在 Windows 平台下，连接字符串如最后一行代码所示；除非你使用原始字符串（r''），否则就要使用 \\ 进行字符串转义。

示例 P-1　为 SQLite 数据库创建引擎from sqlalchemy import create_engineengine = create_engine('sqlite:///cookies.db')engine2 = create_engine('sqlite:///:memory:')engine3 = create_engine('sqlite:////home/cookiemonster/cookies.db')engine4 = create_engine('sqlite:///c:\\Users\\cookiemonster\\cookies.db')　create_engine 函数会返回一个引擎的实例。但是，在

调用需要使用连接的操作（比如查询）之前，它实际上并不

会打开连接。接下来，让我们为名为 mydb 的本地 PostgreSQL 数据库创建一个引擎。为此，我们先从 sqlalchemy 包导入 create_engine 函数。然后，使用 create_engine 函数创建引擎实例。在示例 P-2 中，你会看到我把 postgresql+psycopg2 用作连接字符串的引擎和方言组件，当然只使用 postgres 也可以。相比于隐式方式，我更喜欢显式方式，这也正是“Python 之禅”（Zen of Python）所提倡的。

示例 P-2　为本地 PostgreSQL 数据库创建引擎from sqlalchemy import create_engineengine = create_engine('postgresql+psycopg2://username:password@localhost:' \ '5432/mydb')下面看看位于远程服务器上的 MySQL 数据库。在示例 P-3 中你会看到，在连接字符串之后，我们使用了一个关键字参数 pool_recycle，用来指定回收连接的频率。

示例 P-3　为远程 MySQL 数据库创建引擎from sqlalchemy import create_engineengine = create_engine('mysql+pymysql://cookiemonster:chocolatechip' '@mysql01.monster.internal/cookies', pool_recycle=3600)　默认情况下，超过 8 小时，MySQL 才会关闭空闲连

接。为了解决这个问题，可在创建引擎时把 pool_recycle 设

置为 3600，如示例 P-3 所示。create_engine 函数还有一些可选参数，如下所示。● echo

开启这个参数会记录引擎处理的操作，例如 SQL 语句及其参

数。默认值为 false。● encoding

这个参数用于指定 SQLAlchemy 所使用的字符串编码方式，默

认值为 utf-8。大多数 DBAPI 默认支持这种编码。但这个参数并

不用来指定后端数据库所使用的编码类型。● isolation_level

这个参数用来为 SQLAlchemy 指定隔离级别。例如，使用

Psycopg2 的 PostgreSQL 拥有 READ COMMITTED、READ

UNCOMMITTED、REPEATABLE READ、SERIALIZABLE 和

AUTOCOMMIT 选项，默认值为 READCOMMITTED。PyMySQL

也有一样的选项，就 InnoDB 数据库来说，默认值为

REPEATABLEREAD。　可以使用 isolation_level 这个关键字参数为任何给定的 DBAPI 设置隔离级别。此外，还可以通过方言连接字符串中的键 - 值对来设置隔离级别，比如支持这个方法的 Psycopg2。● pool_recycle

设置这个参数会定期回收数据库连接或者让数据库连接超时。因

为前面提到过的连接超时，这对 MySQL 非常重要。该参数的默

认值为 -1，表示不超时。初始化引擎之后，就可以实际打开数据库连接了。这可以通过调用 connect() 方法实现，代码如下：from sqlalchemy import create_engineengine = create_engine('mysql+pymysql://cookiemonster:chocolatechip' \ '@mysql01.monster.internal/cookies', pool_recycle=3600)connection = engine.connect()现在我们有了一个数据库连接，接下来就可以开始使用 SQLAlchemy Core 或 SQLAlchemy ORM 了。在第一部分中，我们将讲解 SQLAlchemy Core，并学习如何定义和查询数据库。第一部分　SQLAlchemy Core既然我们已经可以连接到数据库了，接下来学习一下如何使用 SQLAlchemy Core 为应用程序提供数据库服务。SQLAlchemy Core 允许我们以 Python 方式使用 SQL 命令和数据结构，即所谓的 SQL 表达式语言。SQLAlchemy Core 既可以与 Django 或 SQLAlchemy ORM 一起使用，也可以作为独立的解决方案使用。第 1 章　模式和类型我们要做的第一件事是定义数据表存储什么样的数据、数据之间如何相互关联，以及数据上的约束。为了访问底层数据库，SQLAlchemy 需要用某种东西来代表数据库中的数据表。为此，可以使用下面三种方法中的一种：● 使用用户定义的 Table 对象● 使用代表数据表的声明式类● 从数据库中推断本章重点讲解第一种方法，因为它正是 SQLAlchemy Core 所采用的方式。在掌握了基础知识之后，我们会在后面章节中学习另外两种方法。Table 对象包含一系列带有类型的列和属性，它们与一个常见的元数据容器相关联。本章我们会学习模式定义，首先来看看 SQLAlchemy 中有哪些类型可用来构建表格。1.1　类型在 SQLAlchemy 中有四种类型可用：● 通用类型● SQL 标准类型● 厂商自定义类型● 用户定义类型SQLAlchemy 定义了大量通用类型，它们是从各后端数据库所支持的实际 SQL 类型中抽象出来的。这些类型在 sqlalchemy.types 模块中都可用，为方便起见，它们在 sqlalchemy 模块中也可用。接下来看看这些通用类型为什么有用。Boolean 通用类型一般使用 BOOLEAN SQL 类型，在 Python 端处理成 true 或 false。但是，它在不支持 BOOLEAN 类型的后端数据库上使用 SMALLINT。幸运的是 SQLAlchemy 隐藏了这个小细节，你可以坚信，你创建的所有查询或语句都能针对该类型的字段正确操作，而不管你使用的是哪种类型的数据库。你只需要在 Python 代码中处理 true 或 false 即可。在数据库迁移或分割后端系统（其中数据仓库是一种数据库类型，事务性系统是另外一种数据库类型）时，这种行为使得通用类型非常强大、有用。通用类型在 Python 和 SQL 中分别对应的类型如表 1-1 所示。表1-1：通用类型及对应关系SQLAlchemyPythonSQLBigIntegerintBIGINTBooleanboolBOOLEAN 或 SMALLINTDatedatetime.dateDATE(SQLite:STRING)DateTimedatetime.datetimeDATETIME (SQLite: STRING)EnumstrENUM 或 VARCHARFloatfloat 或 DecimalFLOAT 或 REALIntegerintINTEGERIntervaldatetime.timedeltaINTERVAL 或 DATE（从纪元开始）LargeBinarybyteBLOB 或 BYTEANumericdecimal.DecimalNUMERIC 或 DECIMALUnicodeunicodeUNICODE 或 VARCHARTextstrCLOB 或 TEXTTimedatetime.timeDATETIME　学习这些通用类型很重要，因为你会经常使用和定义

它们。除了表 1-1 中列出的通用类型外，你还可以使用 SQL 标准类型和厂商自定义类型。当通用类型因其类型或现有模式中指定的特定类型而无法在数据库模式中按照需要使用时，我们通常会使用这两种类型。CHAR 和 NVARCHAR 类型就是很好的例子，它们都要求使用正确的 SQL 类型而不仅仅是通用类型。如果我们使用的是在使用 SQLAlchemy 之前就已经定义好的数据库模式，那么就要准确地匹配类型。请务必记住，SQL 标准类型的行为和可用性因数据库而异。SQL 标准类型在 sqlalchemy.types 模块中是可用的。为了将 SQL 标准类型和通用类型区分开，标准类型全部采用大写字母。厂商自定义类型和 SQL 标准类型一样有用，但是它们只适用于特定的后端数据库。可以通过所选方言的文档或 SQLALchemy 站点确定有哪些类型可用。它们在 sqlalchemy.dialects 模块中都是可用的，并且每种数据库方言都有若干子模块。同样，这些类型采用的全是大写字母，以便同通用类型区分开。有时，我们可能想使用 PostgreSQL 强大的 JSON 字段，为此可以使用下面的语句来实现：from sqlalchemy.dialects.postgresql import JSON现在，我们可以定义 JSON 字段了。稍后，在我们的应用程序中，PostgreSQL 专用的许多 JSON 函数都会用到它，比如 array_to_json 函数。你还可以自定义类型，以便用你选择的方式存储数据。例如，在把字符放入数据库记录时，将其放在存储在 VARCHAR 列中的文本的前面，并在从记录中获取这个字段时去掉它们。在处理现有系统仍然使用的遗留数据时，这可能很有用，因为这种类型的前缀在新应用程序中可能没用或者并不重要。我们已经学习了用于创建数据表的四种变体类型，接下来看看数据库结构是如何通过元数据结合在一起的。1.2　元数据元数据用来把数据库结构结合在一起，以便在 SQLAlchemy 中快速访问它。一般可以把元数据看作一种 Table 对象目录，其中包含与引擎和连接有关的信息。这些表可以通过字典 MetaData.tables 来访问。读操作是线程安全的，但是表的创建并不是完全线程安全的。在把对象绑定到元数据之前，需要先导入并初始化元数据。接下来，初始化 MetaData 对象的一个实例，在本章的其余示例中我们会用它来保存信息目录：from sqlalchemy import MetaDatametadata = MetaData()到这里，我们已经有了用来保存数据库结构的方法，接下来开始定义表。1.3　表在 SQLAlchemy Core 中，我们通过调用 Table 构造函数来初始化 Table 对象。我们要在构造函数中提供 MetaData 对象（元数据）和表名，任何其他参数都被认为是列对象。此外，还有一些额外的关键字参数，它们用来开启相关特性，相关内容稍后讲解。列对象对应于数据表中的各个字段。列是通过调用 Column() 函数创建的，我们需要为这个函数提供列名、类型，以及其他表示 SQL 结构和约束的参数。在本章的其余部分中，我们将创建一组表，并在第一部分中使用。在示例 1-1 中，我们创建了一个表，用于为线上 cookie 配送服务存储 cookie 库存。

示例 1-1　实例化 Table 对象和列from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKeycookies = Table('cookies', metadata, Column('cookie_id', Integer(), primary_key=True), ➊ Column('cookie_name', String(50), index=True), ➋ Column('cookie_recipe_url', String(255)), Column('cookie_sku', String(55)), Column('quantity', Integer()), Column('unit_cost', Numeric(12, 2)) ➌)❶ 请注意我们把此列标记为表的主键的方式。稍后会详细介绍。❷ 创建 cookie 名称索引，以加快该列的查询速度。❸ 这个列包含多个参数，有长度和精度，比如 11.2，其中长度最多为 11 位数字，精度为两位小数。在深入了解表之前，需要先了解表的基本构造块：列。1.3.1　列列用来定义数据表中的字段，它们提供了通过关键字参数定义其他约束的主要方法。不同类型的列的主要参数是不一样的。例如，String 类型的列的主要参数是长度，而带有小数部分的数字有精度和长度。其他大部分类型没有主要参数。　有时你会看到有些 String 列没有长度这个主要参数。

这种行为并没有被普遍支持，例如，MySQL 和其他几个数

据库后端就不允许这样做。除此之外，列还有其他一些关键字参数，这些参数有助于进一步塑造它们的行为。可以把列标记为必需，或者强制它们是唯一的。还可以为列设置默认的初始值，并在记录更新时更改列值。一个常见的用例是那些用来指示何时为日志或审计的目的创建或更新记录的字段。下面看一下示例 1-2 中的这些关键字参数。

示例 1-2　另一个带有更多列选项的表from datetime import datetimefrom sqlalchemy import DateTimeusers = Table('users', metadata, Column('user_id', Integer(), primary_key=True), Column('username', String(15), nullable=False, unique=True), ➊ Column('email_address', String(255), nullable=False), Column('phone', String(20), nullable=False), Column('password', String(25), nullable=False), Column('created_on', DateTime(), default=datetime.now), ➋ Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now) ➌)❶ 我们让这个列是必需的（nullable=False），而且值唯一。❷ 如果未指定日期，就把当前时间设置为列的默认值。❸ 通过使用 onupdate，使得每次更新记录时，都把当前时间设置给当前列。　你可能注意到了，我们在设置 default 和 onupdate 时

使用的是 datetime.now，而没有调用 datetime.now() 函数。

如果调用这个函数，它就会把 default 设置为表首次实例化

的时间。通过使用 datetime.now，可以得到实例化和更新每

个记录的时间。我们一直使用列关键字参数来定义表结构和约束，但是，你也可以在 Column 对象之外声明它们。当使用一个现有数据库时，这一点非常重要，因为你必须告诉 SQLAlchemy 数据库中的模式、结构和约束。例如，如果数据库中的已有索引与 SQLAlchemy 使用的默认索引命名方案不匹配，那么你必须手动定义该索引。下面两节内容将演示如何做到这一点。　1.5 节和 1.6 节中的所有命令都包含在 Table 构造函数

中，或者通过特殊方法被添加到表中。它们将作为独立语句

进行持久化或附加到元数据。1.3.2　键和约束键和约束用来确保我们的数据在存储到数据库之前满足某些要求。可以在基本的 SQLAlchemy 模块中找到代表键和约束的对象，其中三个常见的对象导入如下：from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint最常见的键类型是主键。主键用作数据库表中每个记录的唯一标识符，用于确保不同表中两个相关数据之间的关系正确。就像你在示例 1-1 和示例 1-2 中看到的那样，只需使用 primary_key 这个关键字参数，就可以让一个列成为主键。还可以通过在多个列上将 primary_key 设置为 True 来定义复合主键。本质上，键会被视为一个元组，其中标记为键的列将按照它们在表中定义的顺序出现。主键也可以在表构造函数的列之后定义，如下面的代码片段所示。可以通过添加多个由逗号分隔的列来创建复合键。如果想像示例 1-2 那样显式地定义键，它看起来会像下面这样：PrimaryKeyConstraint('user_id', name='user_pk')另一个常见的约束是唯一性约束，它用来确保给定字段中不存在重复的两个值。例如，就我们的线上 cookie 配送服务来说，我们希望确保每个客户都有一个唯一的用户名来登录我们的系统。我们还可以为列分配唯一约束，就像前面在 username 列中所做的那样。当然，你也可以手动定义约束，如下所示：UniqueConstraint('username', name='uix_username')示例 1-2 中没有检查约束（check constraint）类型。这种类型的约束用于确保列数据和一组由用户定义的标准相匹配。在下面的示例中，我们会确保 unit_cost 永远不会小于 0.00，因为每个 cookie 的制作都要花费一定的成本（从经济学角度来说，就是没有免费的 cookie ！）：CheckConstraint('unit_cost >= 0.00', name='unit_cost_positive')除了键和约束之外，我们可能还希望提高某些字段的查找效率。这就是索引的作用。1.3.3　索引索引用来加快字段值的查找速度。在示例 1-1 中，我们在 cookie_name 列上创建了一个索引，因为我们知道以后会经常通过它来进行搜索。在索引创建好之后，你会拥有一个名为 ix_cookies_cookie_name 的索引。还可以使用显式构造类型来定义索引。你可以指定多个列，各列之间用逗号分隔。你还可以添加 unique=True 这个关键字参数，指定索引也必须唯一。当显式地创建索引时，它们会在列之后传递给 Table 构造函数。示例 1-1 中的索引也可以显式地创建：from sqlalchemy import IndexIndex('ix_cookies_cookie_name', 'cookie_name')我们还可以创建函数索引，函数索引因所使用的后端数据库而略有不同。这允许你为经常需要基于某些不寻常的上下文进行查询的情况创建索引。例如，如果我们想通过 cookie SKU 和名称进行选择，比如 SKU0001 Chocolate Chip，该怎么办？可以定义下面这样的索引来优化查找：Index('ix_test', mytable.c.cookie_sku, mytable.c.cookie_name))接下来该深入研究关系型数据库最重要的部分了：表关系以及如何定义它们。1.3.4　关联关系和外键约束现在我们已经有了一个表，其中的列拥有正确的约束和索引。接下来看看如何在表之间创建关系。我们需要一种跟踪订单的方法，里面有表示每种 cookie 和订购量的行项目。为了帮助理解这些表之间的关系，请参阅图 1-1。图 1-1：关系可视化示例 1-3 给出了实现关系的一种方法，即在 line_items 表的 order_id 列上添加外键，通过 ForeignKeyConstraint 来定义两个表之间的关系。示例中，许多行项目都可以出现在单个订单中。但是，如果深入研究 line_items 表，你会发现我们还通过 cookie_id 这个外键在 line_items 表与 cookies 表之间建立了关系。这是因为 line_items 实际是 orders 表和 cookies 表之间的一个关联表，其中包含一些额外的数据。关联表用于支持两个其他表之间的多对多关系。表上的单个外键（ForeignKey）通常表示一对多的关系，但是，如果一个表上存在多个外键关系，那么它很可能就是关联表。

示例 1-3　定义更多表from sqlalchemy import ForeignKeyorders = Table('orders', metadata, Column('order_id', Integer(), primary_key=True), Column('user_id', ForeignKey('users.user_id')), ➊ Column('shipped', Boolean(), default=False))line_items = Table('line_items', metadata, Column('line_items_id', Integer(), primary_key=True), Column('order_id', ForeignKey('orders.order_id')), Column('cookie_id', ForeignKey('cookies.cookie_id')), Column('quantity', Integer()), Column('extended_cost', Numeric(12, 2)))➊ 请注意，在这个列上，我们使用的是一个字符串，而不是对列的实际引用。使用字符串而非实际列，这允许我们跨多个模块分离表定义，而且不必担心表的加载顺序。这是因为 SQLAlchemy 只会在第一次访问表名和列时对该字符串执行解析。如果在 ForeignKey 定义中使用了硬引用（hardreference），比如 cookies.c.cookie_id，那它会在模块初始化期间执行解析，并且有可能失败，这取决于表的加载顺序。你还可以显式地定义一个 ForeignKeyConstraint。外键约束在试图匹配现有数据库模式，以便与 SQLAlchemy 一起使用时会很有用。这与以前创建键、约束和索引以匹配名称模式等的工作方式相同。在表定义中定义外键约束之前，需要先从 sqlalchemy 模块导入 ForeignKeyConstraint。下面的代码演示了如何为 line_items 和 orders 表之间的 order_id 字段创建 ForeignKeyConstraint：ForeignKeyConstraint(['order_id'], ['orders.order_id'])到目前为止，我们一直以 SQLAlchemy 能够理解的方式定义表。如果你的数据库已经存在，并且已经构建好了模式，那接下来你可以着手编写查询了。但是，如果你需要创建完整的模式或添加一个表，就需要知道如何持久化表以实现永久性存储。1.4　表的持久化事实上，所有表和模式定义都与 metadata 的实例有关。要想将模式持久化到数据库中，只需调用 metadata 实例的 create_all() 方法，并提供创建表的引擎即可：metadata.create_all(engine)默认情况下，create_all 不会尝试重新创建数据库中已经存在的表，并且它可以安全地运行多次。使用 Alembic 这样的数据库迁移工具来处理对现有表或额外模式所做的更改，要比直接在应用程序代码中进行编码更改更明智（第 11 章会详细地讲解相关内容）。现在我们已经对数据库中的表做了持久化，接下来看看示例 1-4，里面是我们在本章中处理的表的完整代码。

示例 1-4　内存中完整的 SQLite 代码from datetime import datetimefrom sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String, DateTime, ForeignKey, create_engine)metadata = MetaData()cookies = Table('cookies', metadata, Column('cookie_id', Integer(), primary_key=True), Column('cookie_name', String(50), index=True), Column('cookie_recipe_url', String(255)), Column('cookie_sku', String(55)), Column('quantity', Integer()), Column('unit_cost', Numeric(12, 2)))users = Table('users', metadata, Column('user_id', Integer(), primary_key=True), Column('customer_number', Integer(), autoincrement=True), Column('username', String(15), nullable=False, unique=True), Column('email_address', String(255), nullable=False), Column('phone', String(20), nullable=False), Column('password', String(25), nullable=False), Column('created_on', DateTime(), default=datetime.now), Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now))orders = Table('orders', metadata, Column('order_id', Integer(), primary_key=True), Column('user_id', ForeignKey('users.user_id')) Column('shipped', Boolean(), default=False))line_items = Table('line_items', metadata, Column('line_items_id', Integer(), primary_key=True), Column('order_id', ForeignKey('orders.order_id')), Column('cookie_id', ForeignKey('cookies.cookie_id')), Column('quantity', Integer()), Column('extended_cost', Numeric(12, 2)))engine = create_engine('sqlite:///:memory:')metadata.create_all(engine)在本章中，我们学习了 SQLAlchemy 如何将元数据用作目录来存储表模式和其他数据，以及如何定义一个拥有多个列和约束的表。我们了解了约束的类型，以及如何在列对象之外显式地构造它们，以匹配现有的模式或命名方案。然后讨论了如何为审计设置默认值和 onupdate 值。最后学习了如何把模式持久化或保存到数据库中以供重用。接下来学习如何通过 SQL 表达式语言处理模式中的数据。

试读结束[说明：试读内容隐藏了图片]

下载完整电子书