Python MySQL Shell 入门指南（二）
四、对 SQL 数据库使用 Shell
大多数使用 MySQL 的人使用结构化查询语言(SQL)接口来利用关系数据库功能与他们的数据进行交互。正如我们所见，MySQL Shell 是一个非常强大的客户端，您可以使用结构化查询语言(SQL)语句来处理您的数据。然而，MySQL Shell 也是一个强大的脚本语言编辑器和执行引擎。
在本章中，我们将简要了解什么是 SQL 数据库以及如何在 shell 中使用它们，包括使用 SQL 的简要概述。然而，我们不会在这里花太多时间，因为很多人都熟悉 SQL。无论你是否是 MySQL 和 SQL 的新手，我建议你阅读这些章节，这样你就能理解我们将在本章后面使用的访问方法。
虽然我们会看到一些简短的例子，但第五章包含了一个更详细的例子，展示了如何将 MySQL Shell 用于 SQL 数据库，包括如何使用新的 X DevAPI for SQL 数据库。
让我们先简单介绍一下 MySQL 的 SQL 接口。
重温关系数据库
如你所知，MySQL 是作为后台进程(Windows 中的一项服务)运行的。如果您从命令行启动它，也可以将其作为前台进程运行。像大多数数据库系统一样，MySQL 支持 SQL。您可以使用 SQL 创建数据库和对象(使用数据定义语言[DDL])，写入或更改数据(使用数据操作语言[DML])，以及执行各种命令来管理服务器。
DDL 语句是我们用来在数据库中创建存储机制(对象，如表)的语句——包括数据库本身。另一方面，DML 语句是为存储和检索数据(行)而设计的。MySQL 还支持其他实用的 SQL 命令，比如显示系统状态、变量和类似元数据的命令。清单 4-1 展示了每种形式(DDL 和 DML)的一个例子，以及一些实用的 SQL 命令。 1
C:\Users\cbell>mysqlsh --sql --uri root@localhost:3306
...
SQL > CREATE DATABASE test_db;
Query OK, 1 row affected (0.0586 sec)
SQL > USE test_db;
Query OK, 0 rows affected (0.0003 sec)
SQL > CREATE TABLE test_tbl (id int auto_increment, name char(20), primary key(id));
Query OK, 0 rows affected (0.0356 sec)
SQL > INSERT INTO test_tbl VALUES (NULL, 'one');
Query OK, 1 row affected (0.1117 sec)
SQL > INSERT INTO test_tbl VALUES (NULL, 'two');
Query OK, 1 row affected (0.0078 sec)
SQL > INSERT INTO test_tbl VALUES (NULL, 'three');
Query OK, 1 row affected (0.0109 sec)
SQL > SELECT ∗ FROM test_tbl;
+----+-------+
| id | name |
+----+-------+
| 1 | one |
| 3 | three |
| 4 | one |
| 5 | two |
| 6 | three |
+----+-------+
5 rows in set (0.0011 sec)
SQL > DELETE FROM test_tbl WHERE id = 2;
Query OK, 0 rows affected (0.0005 sec)
SQL > SELECT ∗ FROM test_tbl;
+----+-------+
| id | name |
+----+-------+
| 1 | one |
| 3 | three |
| 4 | one |
| 5 | two |
| 6 | three |
+----+-------+
5 rows in set (0.0005 sec)
SQL > SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_tbl |
+-------------------+
1 row in set (0.0015 sec)
SQL > SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| test_db |
| world |
| world_x |
+--------------------+
8 rows in set (0.0009 sec))
SQL > SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.16 |
+-----------+
1 row in set (0.0004 sec)
SQL > DROP DATABASE test_db;
Query OK, 1 row affected (0.2659 sec)
Listing 4-1Example DDL and DML Statements
注意
您必须用分号(;或\G来结束每个 SQL 命令。
这个例子演示了以CREATE DATABASE和CREATE TABLE语句形式的 DDL 语句，以INSERT、DELETE和SELECT语句形式的 DML 语句，以及几个实用程序语句，包括一个简单的管理命令来检索全局服务器变量(@@version)。
请注意创建数据库和表来存储数据，在表中添加几行，删除一行，最后检索表中的数据。注意我是如何用大写字母表示 SQL 命令关键字的。这是一种常见的做法，有助于使 SQL 命令更容易阅读，更容易找到用户提供的选项或数据(小写)。
MySQL 中有很多可用的命令。幸运的是，你只需要掌握几个比较常见的。以下是您最常使用的命令。<>中包含的部分表示用户提供的命令组件，而[...]表示需要额外的选项。
-
CREATE DATABASE <database_name>:创建数据库 -
USE <database>:设置默认数据库(不是 SQL 命令) -
CREATE TABLE <table_name> [...]:创建一个表格或结构来存储数据 -
INSERT INTO <table_name> [...]:向表格中添加数据 -
UPDATE [...]:更改特定行的一个或多个值 -
DELETE FROM <table_name> [...]:从表格中删除数据 -
SELECT [...]:从表格中检索数据(行) -
SHOW [...]:显示对象、系统变量等的列表
虽然这个列表只是一个简短的介绍，并不像一个完整的语法指南，但有一个很好的在线参考手册详细解释了每个命令(以及更多)。当你对 MySQL 有任何疑问时，你应该参考在线参考手册。你可以在 https://dev.mysql.com/doc/refman/8.0/en/sql-syntax.html 找到 MySQL 支持的每个 SQL 命令的解释和细节。
显示的一个更有趣的命令允许您查看对象列表。例如，您可以看到带有SHOW DATABASES的数据库，带有SHOW TABLES的表列表(一旦您用USE命令设置了默认数据库)，甚至带有SHOW GRANTS的用户权限。我发现自己经常使用这些命令。
如果你认为 MySQL 不仅仅是几个简单的命令，那你就错了。尽管 MySQL 易于使用且启动时间快，但它是一个成熟的关系数据库管理系统(RDBMS)。比你在这里看到的要多得多。有关 MySQL 的更多信息，包括所有高级特性，请参见在线参考手册。
什么是关系数据库管理系统？
RDBMS 是一种基于数据关系模型的数据存储和检索服务，由 E. F. Codd 于 1970 年提出。这些系统是结构化数据的标准存储机制。大量的研究致力于改进 Codd 提出的基本模型，正如 C. J. Date 在《数据库关系模型:回顾和分析》中所讨论的那样。这种理论和实践的演变最好地记录在 Date 的第三宣言中。
关系模型是存储库(数据库)的直观概念，可以通过使用一种称为查询语言的机制来检索、更新和插入数据，从而方便地查询存储库。许多供应商已经实现了关系模型，因为它具有完善的系统理论、坚实的数学基础和简单的结构。最常用的查询机制是 SQL，它类似于自然语言。虽然关系模型中不包括 SQL，但它提供了关系模型在 RDBMSs 中的实际应用的一个组成部分。
现在，您已经知道了什么是 MySQL，并且已经看到了一个用于处理数据的 SQL 命令的简洁示例，让我们来探索一些成功部署和使用 MySQL 来存储和检索数据所需的更常见的概念和操作。
使用 MySQL 命令和函数
学习和掌握数据库系统需要训练、经验和极大的毅力。精通所需的主要知识是如何使用常见的 SQL 命令和概念。本节通过介绍最常见的命令和概念来完成 MySQL 和 SQL 入门。
本节介绍了高级命令和概念，而不是重复参考手册。如果您决定使用任何命令或概念，并需要更多信息，请参考在线参考手册，了解更多详细信息、完整的命令语法和其他示例。但首先，让我们澄清一些我们在使用传统关系数据库(SQL)处理 MySQL 时使用的术语。
术语
在 MySQL 中，像其他关系数据库系统一样，我们以固定的方式存储数据，我们使用数据库来存储给定任务、工作、应用、域等的数据。我们使用表来存储相似的数据(具有相同格式的数据)。在一个表中，数据被表示为行，每一行都具有相同的格式(或模式)。
如果您以前从未使用过数据库，您可以松散地关联一个关系数据库表，如电子表格 2 ，其中定义了列，每一行包含每一列的值。因此，插入数据或检索数据需要将数据作为表中的行来形成或查看。
创建用户和授予访问权限
要开始处理数据，您需要在使用 MySQL 之前了解两个管理操作:创建用户帐户和授予对数据库的访问权限。MySQL 可以用CREATE USER和GRANT语句来执行这些操作。要创建一个用户，您可以发出一个CREATE USER命令，后跟一个或多个GRANT命令。例如，下面显示了名为简的用户的创建，并授予该用户对名为 store_inventory 的数据库的访问权限:
CREATE USER 'jane'@'%' IDENTIFIED BY 'secret';
GRANT SELECT, INSERT, UPDATE ON store_inventory.∗ TO 'jane'@'%';
第一个命令创建名为 jane 的用户，但是该名称也有一个@后跟另一个字符串。第二个字符串是与用户相关联的机器的主机名。也就是说，MySQL 中的每个用户都有一个用户名和一个主机名，以user@host的形式来惟一地标识他们。这意味着用户和主机jane@10.0.1.16和用户和主机jane@10.0.1.17是不同的。但是，%符号可以用作通配符，将用户与任何主机关联起来。IDENTIFIED BY子句为用户设置密码。
警告
为您的应用创建没有 MySQL 系统完全访问权限的用户帐户并为管理员保留完全访问权限总是一个好主意。此外，您应该避免对主机使用通配符，这样您就可以将用户限制到已知的机器(IP 地址)、子网等。这是为了最大限度地减少任何意外更改，并防止被利用。
对主机使用通配符%时要小心。虽然创建单个用户并让用户从任何主机访问数据库服务器变得更加容易，但这也使得一些怀有恶意的人更容易从任何地方访问您的服务器(一旦他们发现了密码)。
第二个命令允许访问数据库。您可以授予用户许多权限。这个例子展示了您最有可能给数据库用户的集合:read ( SELECT)、add data ( INSERT)和 change data ( UPDATE)。有关安全性和帐户访问权限的更多信息，请参见在线参考手册。 3
该命令还指定要授予权限的数据库和对象。因此，可以给用户一些表的读(SELECT)权限，给另一些表的写(INSERT、UPDATE)权限。这个例子给了用户访问所有对象(表、视图等)的权限。)在商店 _ 库存数据库中。
小费
MySQL 的新版本不再允许你用GRANT语句创建用户。您必须首先显式创建用户。
创建数据库和表
您需要学习和掌握的最基本的命令是CREATE DATABASE和CREATE TABLE命令。回想一下，MySQL 之类的数据库服务器允许您创建任意数量的数据库，您可以用逻辑方式添加表和存储数据。
创建数据库
要创建一个数据库，使用CREATE DATABASE后跟一个数据库名称。发出命令后，shell 不会将上下文“切换”到该数据库(像其他一些客户端一样)。相反，如果您想要设置默认数据库，您必须使用USE <database>命令。每当您决定在 SQL 命令的名称限定符中省略使用数据库时，这是需要的。
例如，通过以<database>.<table>的形式指定数据库和表，您可以使用SELECT命令从任何数据库的任何表中选择行。请注意，我们用句点分隔名称。此外，SELECT ∗ FROM db1.table1将执行，不管默认的数据库设置。您应该养成在命令中指定数据库的习惯。下面显示了创建和更改数据库焦点的两个命令:
CREATE DATABASE factory_sensors;
USE factory_sensors;
创建表格
要创建一个表格，使用CREATE TABLE命令。该命令有许多选项，不仅允许您指定列及其数据类型，还允许您指定附加选项，如索引、外键等。下面显示了如何创建一个简单的表来存储装配线的传感器数据。
CREATE TABLE `factory_sensors`.`trailer_assembly` (
`id` int auto_increment,
`sensor_name` char(30) NOT NULL,
`sensor_value` float DEFAULT NULL,
`sensor_event` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`sensor_units` char(15) DEFAULT NULL,
PRIMARY KEY `sensor_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
注意，我在这里指定了表名( trailer_assembly )和四列( sensor_name 、 sensor_value 、 sensor_event 和 sensor_units )。我使用了几种数据类型。对于 sensor_name ，我使用了一个最多 30 个字符的字符字段，一个浮点数据类型用于 sensor_value ，一个时间戳值用于 sensor_event ，另一个字符字段用于 15 个字符的 sensor_units 。
我还添加了一个带有自动增量列的索引，以确保我们可以用相同的名称存储传感器值。也就是说，我们可能会在一段时间内对相同的传感器进行多次采样。也可以使用CREATE INDEX命令创建索引。
注意
这个虚构的表来自一个工作概念，在这个概念中，传感器数据在一个给定的时间段(比如 24 小时)内放在一个表中，然后移动到另一个系统进行分析。因此，该表不是为长时间存储传感器数据而设计的。
注意TIMESTAMP栏。这种数据类型的列在传感器网络或物联网(IOT)解决方案中，或者在您想要记录事件或操作的日期和时间的任何时候特别有用。例如，知道何时读取传感器值通常是有帮助的。通过向表中添加一个TIMESTAMP列，您不需要在数据收集时计算、读取或格式化日期和时间。
还要注意，我指定将 sensor_name 列定义为一个键，这将创建一个索引。在这种情况下，它也是主键。PRIMARY KEY短语告诉服务器确保表中存在且只有一行匹配列的值。通过重复关键字，可以指定几个要在主键中使用的列。注意，所有主键列都不允许空值(NOT NULL)。
注意
此示例是工厂设置中典型传感器网络的高级概念，具有代表性，用于教学目的。
如果您不能确定唯一标识一行的一组列(您希望这样做——有些人喜欢没有这种限制的表，但是一个好的数据库管理员(DBA)不会这样做),那么您可以为整数字段使用一个称为AUTO INCREMENT的人工数据类型选项。当用于某一列(必须是第一列)时，服务器会为插入的每一行自动增加该值。这样，它就创建了一个默认主键。有关自动递增列的更多信息，请参见联机参考手册。
但是，最佳实践表明，在某些情况下，在字符字段上使用主键并不是最佳选择，例如表中的每一列都有很大的值或者有许多唯一值。这可能会降低搜索和索引的速度。在这种情况下，您可以使用 auto increment 字段来人工添加一个更小的主键(但有点神秘)。
可用的数据类型比上一个示例中显示的多得多。您应该查阅联机参考手册，以获得数据类型的完整列表。参见“数据类型一节。"如果你想知道一个表格的布局或“模式”，使用如下所示的SHOW CREATE TABLE命令。
SQL > SHOW CREATE TABLE factory_sensors.trailer_assembly \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
Table: trailer_assembly
Create Table: CREATE TABLE `trailer_assembly` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sensor_name` char(30) NOT NULL,
`sensor_value` float DEFAULT NULL,
`sensor_event` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`sensor_units` char(15) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.0009 sec)
像数据库一样，您也可以使用SHOW TABLES命令获得数据库中所有表的列表。
存储数据
现在您已经创建了一个数据库和表，您将希望向表中加载或插入数据。您可以使用INSERT INTO语句来实现。这里，我们为行指定表和数据。下面是一个简单的例子:
INSERT INTO factory_sensors.trailer_assembly (sensor_name, sensor_value, sensor_units) VALUES ('paint_vat_temp', 32.815, 'Celsius');
在本例中，我为拖车装配线上的一个传感器手动插入数据。你想知道其他的柱子呢？在这种情况下，其他列包括一个时间戳列，它将由数据库服务器填充。所有其他列(只有一列)将被设置为 NULL，这意味着没有值可用、缺少值、值不为零或值为空。对于自动增量和时间戳列，NULL 触发它们的行为，例如将值设置为下一个唯一的整数或捕获当前日期和时间。下面显示了在表中插入这一行的示例。
SQL > SELECT ∗ FROM factory_sensors.trailer_assembly \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
sensor_name: paint_vat_temp
sensor_value: 32.815
sensor_event: 2019-02-01 14:59:35
sensor_units: Celsius
1 row in set (0.0005 sec)
请注意，我在该行的数据之前指定了列。当您希望插入的列数少于表中包含的列数时，这是必要的。更具体地说，关闭列列表意味着您必须为表中的所有列提供数据(或NULL)。此外，列出的列的顺序可以不同于它们在表中的定义顺序。关闭列列表将导致列数据根据它们在表中的显示方式进行排序。
您也可以使用逗号分隔的行值列表，使用相同的命令插入几行，如下所示:
INSERT INTO factory_sensors.trailer_assembly (sensor_name, sensor_value, sensor_units) VALUES ('tongue_height_variance', 1.52, 'mm'), ('ambient_temperature', 24.5, 'Celsius'), ('gross_weight', 1241.01, 'pounds');
这里我用相同的命令插入了几行。请注意，这只是一种简化机制，除了自动提交之外，与发出单独的命令没有什么不同。
更新数据
有时，您需要更改或更新数据。您可能需要更改一列或多列的值，替换多行的值，或者更正数字数据的格式甚至比例。为了更新数据，我们使用UPDATE命令。
您可以更新一列、更新一组列、对一列或多列执行计算等等。本节中使用的示例—工厂传感器网络—不太可能需要更改数据(IOT 就是按照记录存储数据，只要数据相关就存储)，但有时在传感器读取代码出错或类似数据输入问题的情况下，这可能是必要的。
更有可能的是，您或您的用户想要重命名数据库中的对象。例如，假设我们确定甲板上的植物实际上不是蕨类植物，而是一种外来开花植物。在这种情况下，我们希望将工厂名称为 gross_weight 的所有行更改为 trailer_weight 。以下命令执行更改。注意这里的关键操作符是SET操作符。这告诉数据库为指定的列分配一个新值。您可以在命令中列出多个 set 操作。
UPDATE factory_sensors.trailer_assembly SET sensor_name = 'trailer_weight' WHERE sensor_name = 'gross_weight';
还要注意，我在这里使用了一个WHERE子句将UPDATE限制为一组行。这就是你在SELECT语句中看到的同一个WHERE子句，它做同样的事情；它允许您指定限制受影响的行的条件。如果不使用WHERE子句，更新将应用于所有行。
删除数据
有时，您最终会得到需要删除的表中的数据。也许您使用了测试数据，并希望去除虚假的行，或者也许您希望压缩或清除您的表，或者希望消除不再适用的行。要删除行，使用DELETE FROM命令。
我们来看一个例子。假设您有一个正在开发的工厂监控解决方案，您发现您的一个传感器或传感器节点读取的值太低，原因是编码、布线或校准错误。在这种情况下，我们希望删除传感器值小于 0.001 的所有行(可能是虚假数据)。以下命令可以做到这一点:
DELETE FROM factory_sensors.trailer_assembly WHERE sensor_value < 0.001;
当形成从句时，你应该小心。我喜欢使用带有SELECT的WHERE子句来确保我对我想要的行进行操作。使用SELECT以这种方式测试潜在受影响的行比简单地盲目发出命令要安全得多。例如，我将首先发出下面的命令来检查我是否要删除我想要的行，并且只删除那些行。注意这是同一个WHERE条款。
SELECT ∗ FROM factory_sensors.trailer_assembly WHERE sensor_value < 0.001;
警告
发出不带WHERE子句的UPDATE或DELETE命令将影响表中的所有行！
选择数据(结果)
您需要知道的最常用的基本命令是从表中返回数据的命令(也称为结果集或行)。为此，您可以使用SELECT语句。这个 SQL 语句是数据库系统的核心。所有对数据的查询都将使用该命令执行。因此，我们将花更多的时间来看看可以使用的各种子句(部分)，从列列表开始。
SELECT语句允许您指定想要从数据中选择哪些列。该列表作为语句的第一部分出现。第二部分是FROM子句，它指定了要从中检索行的表。FROM子句还允许您合并两个或多个表中的数据。这被称为连接，并使用JOIN操作符来链接表。在后面的小节中，您将看到一个简单的连接示例。
指定列的顺序决定了结果集中的显示顺序。如果需要所有列，请使用星号(∗)代替。清单 4-2 展示了生成相同结果集的三条语句。也就是说，在每个的输出中将显示相同的行。事实上，为了简单起见，我使用了一个只有四行的表。
SQL > SELECT sensor_name FROM factory_sensors.trailer_assembly;
+------------------------+
| sensor_name |
+------------------------+
| ambient_temperature |
| paint_vat_temp |
| tongue_height_variance |
| trailer_weight |
+------------------------+
4 rows in set (0.0006 sec)
SQL > SELECT sensor_name, sensor_value, sensor_event, sensor_units FROM factory_sensors.trailer_assembly \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
sensor_name: ambient_temperature
sensor_value: 24.5
sensor_event: 2019-02-01 15:04:08
sensor_units: Celsius
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 2\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
sensor_name: paint_vat_temp
sensor_value: 32.815
sensor_event: 2019-02-01 14:59:35
sensor_units: Celsius
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 3\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
sensor_name: tongue_height_variance
sensor_value: 1.52
sensor_event: 2019-02-01 15:04:08
sensor_units: mm
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 4\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
sensor_name: trailer_weight
sensor_value: 1241.01
sensor_event: 2019-02-01 15:06:17
sensor_units: pounds
4 rows in set (0.0004 sec)
SQL > SELECT ∗ FROM factory_sensors.trailer_assembly \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
sensor_name: ambient_temperature
sensor_value: 24.5
sensor_event: 2019-02-01 15:04:08
sensor_units: Celsius
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 2\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
sensor_name: paint_vat_temp
sensor_value: 32.815
sensor_event: 2019-02-01 14:59:35
sensor_units: Celsius
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 3\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
sensor_name: tongue_height_variance
sensor_value: 1.52
sensor_event: 2019-02-01 15:04:08
sensor_units: mm
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 4\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
sensor_name: trailer_weight
sensor_value: 1241.01
sensor_event: 2019-02-01 15:06:17
sensor_units: pounds
4 rows in set (0.0005 sec)
SQL > SELECT sensor_value, sensor_name, sensor_units FROM factory_sensors.trailer_assembly;
+--------------+------------------------+--------------+
| sensor_value | sensor_name | sensor_units |
+--------------+------------------------+--------------+
| 24.5 | ambient_temperature | Celsius |
| 32.815 | paint_vat_temp | Celsius |
| 1.52 | tongue_height_variance | mm |
| 1242.00 | trailer_weight | pounds |
+--------------+------------------------+--------------+
4 rows in set (0.0005 sec)
Listing 4-2Example SELECT Statements
请注意，第一条语句列出了表中的传感器名称。接下来的两个语句以相同的顺序生成相同的行和相同的列，但是第三个语句虽然生成相同的行但没有传感器事件，却以不同的顺序显示列。
您还可以使用列列表中的函数来执行计算和类似操作。一个特殊的例子是使用COUNT()函数来确定结果集中的行数，如下所示。注意，我们传入通配符(∗)来计算所有行。关于 MySQL 提供的函数的更多例子，请参阅在线参考手册。 4
SELECT COUNT(∗) FROM factory_sensors.trailer_assembly;
SELECT语句中的下一个子句是WHERE子句。就像我们在更新和删除行中看到的那样，这是您指定想要用来限制结果集中的行数的条件的地方。也就是说，只有那些符合条件的行。这些条件基于列，可能相当复杂。也就是说，您可以基于计算、连接结果等来指定条件。但是为了回答一个问题，大多数条件将是一列或多列上的简单等式或不等式。例如，假设您想要查看传感器读数小于 10.00 的植物。在这种情况下，我们发出以下查询并接收结果。注意，我只指定了两列:传感器名称和从传感器读取的值。
SQL > SELECT sensor_name, sensor_value FROM factory_sensors.trailer_assembly WHERE sensor_value < 10.00;
+------------------------+--------------+
| sensor_name | sensor_value |
+------------------------+--------------+
| tongue_height_variance | 1.52 |
+------------------------+--------------+
1 row in set (0.0008 sec)
您还可以使用其他子句，包括用于对行进行分组以进行聚合或计数的GROUP BY子句，以及用于对结果集进行排序的ORDER BY子句。让我们从聚合开始，快速地看一下每一个。
假设您想要计算每个传感器在表中读取的传感器值的平均值。在这种情况下，我们有一个包含各种传感器随时间变化的传感器读数的表。虽然该示例只包含四行(因此可能没有统计信息)，但是该示例非常清楚地展示了聚合的概念，如清单 4-3 所示。请注意，我们收到的只是四个传感器读数的平均值。
SQL > SELECT sensor_name, sensor_value FROM factory_sensors.trailer_assembly WHERE sensor_name = 'gross_weight';
+--------------+--------------+
| sensor_name | sensor_value |
+--------------+--------------+
| gross_weight | 1250 |
| gross_weight | 1235 |
| gross_weight | 1266 |
| gross_weight | 1242 |
+--------------+--------------+
4 rows in set (0.0040 sec)
SQL > SELECT sensor_name, AVG(sensor_value) as avg_value FROM factory_sensors.trailer_assembly WHERE sensor_name = 'gross_weight' GROUP BY sensor_name;
+--------------+-----------+
| sensor_name | avg_value |
+--------------+-----------+
| gross_weight | 1248.25 |
+--------------+-----------+
1 row in set (0.0006 sec)
SQL > SELECT sensor_name, sensor_value FROM factory_sensors.trailer_assembly WHERE sensor_name = 'gross_weight' ORDER BY sensor_value ASC;
+--------------+--------------+
| sensor_name | sensor_value |
+--------------+--------------+
| gross_weight | 1235 |
| gross_weight | 1242 |
| gross_weight | 1250 |
| gross_weight | 1266 |
+--------------+--------------+
4 rows in set (0.0007 sec)
SQL > SELECT sensor_name, sensor_value FROM factory_sensors.trailer_assembly WHERE sensor_name = 'gross_weight' ORDER BY sensor_value DESC;
+--------------+--------------+
| sensor_name | sensor_value |
+--------------+--------------+
| gross_weight | 1266 |
| gross_weight | 1250 |
| gross_weight | 1242 |
| gross_weight | 1235 |
+--------------+--------------+
4 rows in set (0.0009 sec)
Listing 4-3GROUP BY Example
请注意，在第二个示例中，我在列列表中指定了 average 函数AVG()，并传入了我想要求平均值的列的名称。MySQL 中有许多这样的函数可以用来执行一些强大的计算。显然，这是另一个例子，说明数据库服务器中存在的能力需要客户机上更多的资源(更不用说对于大型数据集，这意味着在操作之前将数据传输到客户机)。
还要注意，我用关键字AS重命名了平均值列。您可以使用它来重命名任何指定的列，这将更改结果集中的名称，如清单所示。
最后两个示例展示了如何查看按传感器值排序的结果集的结果。我们使用ORDER BY子句按照传感器值的升序和降序对行进行排序。如果将它与 LIMIT 子句结合使用，可以看到最大值(max)和最小值(min ),如下所示。但是最好使用MIN()和MAX()函数——参见 https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html 获得 MySQL 中可用函数的完整列表。
SQL > SELECT sensor_value AS min FROM factory_sensors.trailer_assembly WHERE sensor_name = 'gross_weight' ORDER BY sensor_value ASC LIMIT 1;
+------+
| min |
+------+
| 1235 |
+------+
1 row in set (0.0008 sec)
SQL > SELECT sensor_value as max FROM factory_sensors.trailer_assembly WHERE sensor_name = 'gross_weight' ORDER BY sensor_value DESC LIMIT 1;
+------+
| max |
+------+
| 1266 |
+------+
1 row in set (0.0005 sec)
子句的另一个用途是计数。在这种情况下，我们用COUNT()替换了AVG()，得到了与WHERE子句匹配的行数。更具体地说，我们想知道为每个传感器存储了多少个传感器值。
SQL > SELECT sensor_name, COUNT(sensor_value) as num_values FROM factory_sensors.trailer_assembly GROUP BY sensor_name;
+------------------------+------------+
| sensor_name | num_values |
+------------------------+------------+
| paint_vat_temp | 1 |
| tongue_height_variance | 1 |
| ambient_temperature | 1 |
| trailer_weight | 1 |
| gross_weight | 4 |
+------------------------+------------+
5 rows in set (0.0008 sec)
正如我提到的,SELECT语句比这里显示的要多得多，但是我们在这里看到的会让你走得更远，尤其是在处理大多数中小型解决方案的典型数据时。
创建索引
创建表时不使用任何排序。也就是说，表是无序的。虽然 MySQL 每次都会以相同的顺序返回数据，但是没有隐含的(或可靠的)顺序，除非您创建一个索引。 5 我这里指的排序并不是你在排序时想的那样(用SELECT语句中的ORDER BY子句就有可能)。
相反，索引是服务器在执行查询时用来读取数据的映射。例如，如果一个表上没有索引，并且希望选择某列中值大于某个值的所有行，则服务器必须读取所有行来查找所有匹配项。但是，如果我们在该列上添加了一个索引，服务器将只能读取那些符合标准的行。
要创建索引，您可以在CREATE TABLE语句中指定索引，或者发出一个CREATE INDEX命令。我们可以使用这个命令在sensor_name列上添加一个索引。清单 4-4 展示了添加索引前后对表结构(模式)的影响。回想一下，我们在前面创建表时添加了主键索引。
SQL > SHOW CREATE TABLE factory_sensors.trailer_assembly \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
Table: trailer_assembly
Create Table: CREATE TABLE `trailer_assembly` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sensor_name` char(30) NOT NULL,
`sensor_value` float DEFAULT NULL,
`sensor_event` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`sensor_units` char(15) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
1 row in set (0.0005 sec)
SQL > CREATE INDEX sensor_name ON factory_sensors.trailer_assembly (sensor_name);
Query OK, 0 rows affected (0.2367 sec)
Records: 0 Duplicates: 0 Warnings: 0
SQL > SHOW CREATE TABLE factory_sensors.trailer_assembly \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
Table: trailer_assembly
Create Table: CREATE TABLE `trailer_assembly` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sensor_name` char(30) NOT NULL,
`sensor_value` float DEFAULT NULL,
`sensor_event` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`sensor_units` char(15) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `sensor_name` (`sensor_name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
1 row in set (0.0009 sec)
Listing 4-4Adding Indexes
像这样创建的索引不会影响表中行的唯一性，换句话说，确保存在且只有一行可以被特定列的特定值访问。我所指的是主键(或主索引)的概念，这是在创建表时使用的一个特殊选项，如前所述。
您可以使用如下所示的DROP INDEX命令删除索引。
DROP INDEX sensor_name ON factory_sensors.trailer_assembly;
创建视图
视图是一个或多个表的结果的逻辑映射。它们可以像查询中的表一样被引用，这使它们成为创建数据子集的强大工具。您用CREATE VIEW创建一个视图，并给它起一个类似表格的名字。下面显示了一个简单的例子，其中我们创建了一个测试视图来从表中读取值。在这种情况下，我们限制了视图的大小(行数)，但是您可以为视图使用各种各样的条件，包括组合来自不同表的数据。因此，视图可以像表一样用于查询。它们是处理数据子集的便捷方式(当构造正确时)。
SQL > CREATE VIEW list_weights AS SELECT ∗ FROM factory_sensors.trailer_assembly WHERE sensor_units = 'pounds' LIMIT 3;
Query OK, 0 rows affected (0.0525 sec)
SQL > SELECT ∗ FROM factory_sensors.list_weights;
+----+----------------+--------------+---------------------+--------------+
| id | sensor_name | sensor_value | sensor_event | sensor_units |
+----+----------------+--------------+---------------------+--------------+
| 4 | trailer_weight | 1241.01 | 2019-02-01 15:40:35 | pounds |
| 5 | gross_weight | 1250 | 2019-02-01 15:40:35 | pounds |
| 6 | gross_weight | 1235 | 2019-02-01 15:40:35 | pounds |
+----+----------------+--------------+---------------------+--------------+
3 rows in set (0.0047 sec)
在中小型数据库解决方案中通常不会遇到视图，但是我将它们包括在内是为了在您决定进行额外的分析并希望将数据组织成更小的组以便于阅读时让您了解它们。
简单连接
数据库系统最强大的概念之一是在数据之间建立关系的能力(因此得名关系型)。也就是说，一个表中的数据可以引用另一个(或多个)表中的数据。最简单的形式称为主从关系，其中一个表中的一行引用或关联到另一个表中的一行或多行。
一个常见的(也是经典的)主从关系的例子来自订单跟踪系统，其中一个表包含订单的数据，另一个表包含订单的行项目。因此，我们只存储一次订单信息，如客户号和发货信息，并在检索订单时合并或“连接”这些表。
让我们来看一个名为 world_x 的示例数据库。您可以在 MySQL 网站( http://dev.mysql.com/doc/index-other.html )上找到这个数据库。请随意下载它和任何其他示例数据库。它们都展示了数据库系统的各种设计。您还会发现练习查询数据很方便，因为它包含了许多简单的行。
注意
如果你想运行下面的例子，你需要安装第三章中描述的world_x示例数据库。
清单 4-5 展示了一个简单连接的例子。这里发生了很多事情，所以花点时间检查一下SELECT语句的各个部分，尤其是我是如何指定JOIN子句的。您可以忽略LIMIT选项，因为它只是限制了结果集中的行数。
SQL > SELECT Name, Code, Language FROM world_x.Country JOIN world_x.CountryLanguage ON Country.Code = CountryLanguage.CountryCode LIMIT 10;
+-------------+------+------------+
| Name | Code | Language |
+-------------+------+------------+
| Aruba | ABW | Dutch |
| Aruba | ABW | English |
| Aruba | ABW | Papiamento |
| Aruba | ABW | Spanish |
| Afghanistan | AFG | Balochi |
| Afghanistan | AFG | Dari |
| Afghanistan | AFG | Pashto |
| Afghanistan | AFG | Turkmenian |
| Afghanistan | AFG | Uzbek |
| Angola | AGO | Ambo |
+-------------+------+------------+
10 rows in set (0.0165 sec)
Listing 4-5Simple JOIN Example
警告
如果您系统的文件系统支持区分大小写的名称，请确保使用一致的命名。比如world_x和World_X在某些平台上是两个不同的名字。有关区分大小写的标识符的更多信息，请参见 https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html 。
这里我使用了一个JOIN子句，它接受两个指定的表，这样第一个表使用特定的列及其值连接到第二个表(ON指定匹配)。数据库服务器所做的是从表中读取每一行，并只返回那些列中的值指定匹配的行。一个表中不在另一个表中的任何行都不会被返回。
还要注意，我只包括了几个专栏。在本例中，我从Country表中指定了国家代码和洲，从CountryLanguage表中指定了语言列。如果列名不是惟一的(相同的列出现在每个表中)，我就必须用表名来指定它们，比如Country.Name。事实上，总是以这种方式限定列被认为是一种好的做法。
这个例子中有一个有趣的异常，我觉得有必要指出来。事实上，有些人会认为这是一个设计缺陷。注意，在JOIN子句中，我指定了表格和每个表格的列。这是正常且正确的，但是请注意，两个表中的列名不匹配。虽然这真的没有关系，并且只需要一点额外的输入，但是一些 DBA 会认为这是错误的，并且希望在两个表中使用相同的公共列名。
连接的另一个用途是检索公共数据、存档数据或查找数据。例如，假设您有一个表，其中存储了不变(或很少变)的事物的详细信息，例如与邮政编码相关联的城市或与身份证号相关联的名称(例如，社会保险号(SSN))。您可以将这些信息存储在一个单独的表中，并在需要时将数据连接到一个公共列(和值)上。在这种情况下，公共列可以用作外键，这是另一个高级概念。
外键用于维护数据完整性(即，如果一个表中的数据与另一个表相关，但这种关系需要保持一致)。例如，如果您想确保在删除主行时所有的细节行也被删除，您可以在主表中声明一个外键，指向细节表的一列(或多列)。有关外键的更多信息，请参见联机参考手册。 6
关于连接的讨论只涉及最基本的内容。事实上，连接可以说是数据库系统中最困难和最容易混淆的领域之一。如果您发现想要使用联接来组合几个表或扩展数据，以便从几个表中提供数据(外部联接)，您应该花一些时间深入学习数据库概念，如 Clare Churcher 的书开始数据库设计(Apress，2012) 。
其他高级概念
MySQL 中有更多可用的概念和命令，但有两个可能会引起人们的兴趣，那就是PROCEDURE和FUNCTION，它们有时被称为例程。我在这里介绍这些概念，以便如果您想探索它们，您可以理解它们是如何在高层次上使用的。
假设您需要运行几个命令来更改数据。也就是你需要在计算的基础上做一些复杂的改变。对于这些类型的操作，MySQL 提供了存储过程的概念。存储过程允许您在调用该过程时执行复合语句(一系列 SQL 命令)。存储过程有时被认为是一种主要用于定期维护的高级技术，但它们在更简单的情况下也很方便。
例如，假设您想要开发您的解决方案，但是由于您正在开发它，您需要定期重新开始，并且想要首先清除所有数据。如果只有一个表，存储过程不会有太大帮助，但是假设有几个表分布在几个数据库中(对于较大的解决方案来说并不少见)。在这种情况下，存储过程可能会有所帮助。
在 shell 中输入带有复合语句的命令时，您需要临时更改分隔符(分号),以便行尾的分号不会终止命令条目。例如，在用复合语句编写命令之前使用DELIMITER //，使用//结束命令，用DELIMITER ;将分隔符改回来。
假设您想执行一个复合语句并返回一个结果——您想将它用作一个函数。您可以使用函数通过执行计算、数据转换或简单的翻译来填充数据。因此，函数可用于提供值来填充列值、提供聚合、提供日期操作等等。
您已经看到了几个函数(COUNT、AVG)。这些被认为是内置函数，在线参考手册中有一整节专门介绍它们。但是，您也可以创建自己的函数。例如，您可能希望创建一个函数来对您的数据执行一些数据规范化。更具体地说，假设您有一个传感器，它产生一个特定范围内的值，但是根据该值和来自不同传感器或查找表的另一个值，您想要对该值进行加、减、平均等操作来校正它。您可以编写一个函数来实现这一点，并在触发器中调用它来填充计算列的值。
由于存储过程可能相当复杂，如果您决定使用它们，在尝试开发自己的存储过程之前，请阅读在线参考手册的“CREATE PROCEDURE and CREATE FUNCTION Syntax”一节。 7 创建存储过程的内容远不止这一部分。
换对象呢？
您可能想知道当您需要修改表、过程、触发器等时该怎么做。放心吧，你不必从头开始！MySQL 为每个对象提供了一个ALTER命令。也就是说，有一个ALTER TABLE、ALTER PROCEDURE等等。关于每个ALTER命令的更多信息，参见在线参考手册标题为数据定义语句的章节。 8
现在，我们已经对使用 SQL 命令和 MySQL 存储和检索关系数据有了一个较高的认识，让我们看看如何使用 X DevAPI 来编写使用相同关系数据的 Python 代码。我们终于要写点 Python 了！
使用 Python 管理数据库
现在我们已经很好地介绍了 SQL 和 MySQL 命令，我们可以将注意力转向一种令人兴奋的在 MySQL 中使用 SQL 数据库的新方法——使用 X DevAPI 编写 Python 脚本来合并代码和数据。
我们将从处理关系数据的角度开始探索 API，而不是对 X DevAPI 必须提供的所有功能进行艰苦、乏味的探索。在这一节中，我们将发现关于 X DevAPI 的足够多的知识，以便能够编写 Python 代码来处理关系数据库对象(数据库和表)。我们把更复杂的部分留到第六章来看 X DevAPI 对 JSON 文档的支持。
然而，我们仍然需要知道 X DevAPI 是什么以及它的主要特性。如前所述，我们将从处理关系数据的角度在本章和下一章探讨其中的一些。API 远不止这些，但是从一个已知的(或者至少是熟悉的)基础开始将有助于那些新手在应用中加入数据库支持。毕竟，X DevAPI 就是为了让从代码中访问数据变得容易！
您应该知道的第一件事是，shell 内置了对我们需要的几个库对象的支持。比如前面列出的mysqlx模块就是 shell 中的内置模块之一。我们将内置模块称为全局变量。shell 还包括以下库作为内置模块(有时称为库或对象)作为全局变量。
-
会话:如果已经建立了全局会话，则表示全局会话。
-
db :如果已经定义了一个模式，则表示该模式，例如，由 URI 类型的字符串定义。
-
dba :表示 AdminAPI，它是 InnoDB 集群的一个组件，使您能够管理服务器实例集群。有关 InnoDB 集群的更多信息，请参见第十章。
-
mysqlx :提供对连接到 MySQL 服务器产生的会话对象的操作。
-
shell :提供通用功能，例如配置 MySQL Shell。
-
util :提供实用函数，例如，在升级前检查服务器实例。
在学习将 X DevAPI 用于关系数据时，需要理解的关键概念包括。这些在 Python 代码中被表示(实现)为对象。也就是说，我们将使用一个或多个方法来创建对象，然后使用它的一个或多个方法来执行我们的代码以处理数据。
-
CRUD :创建、读取、更新和删除——对数据的基本操作
-
数据库/模式:一个或多个数据库级对象的容器，如表、视图、触发器等。
-
Result :来自一个读操作(SELECT)或其他操作的一组零行或多行，这些操作产生从服务器返回的值
-
会话:到 MySQL 服务器的连接，包括管理连接的属性
-
表:以特定布局格式化的数据容器，用于将数据存储在具有数据类型的预定义列中
这些概念中的大多数应该是熟悉的，或者至少足够熟悉，以至于学习使用它们不需要太多的努力。例如，作为 SQL 数据库用户，我们理解数据库(模式)、表和结果集的基本概念。代表这些概念的对象只不过是它们行为的模型，我们可以用它们作为方法来调用。
然而，三个最新的概念可能是mysqlx模块、会话和 CRUD 操作。让我们来看看每一个。
MySQL X 模块
mysqlx模块是用 X DevAPI 编写应用的入口点。您可以将该模块视为一个库，其中包含几个可以在 Python 脚本中使用的对象。我们需要的最值得注意的对象是那些可用于连接和处理 MySQL 中的关系数据的类和方法，但是还有更多可用于 JSON 文档的对象。
要理解的关键概念是，对象是从其他对象上调用的方法生成的。更具体地说，当我们在对象a上调用方法x()时，它返回对象b的实例。例如，我们调用方法并将返回的对象赋给另一个变量，就像这个b = a.x()。一旦理解了这一点，就可以检查方法的返回类型，然后引用返回的对象类型来找出它提供了什么方法。
当使用mysqlx模块时，这一切都从返回一个Session对象 9 的连接开始——从get_session()方法返回的也是这个对象。从那里，我们可以调用会话中的方法，它们返回不同的对象。让我们看看mysqlx模块中有哪些类。表 4-1 显示了模块中可用的类别。
表 4-1
mysqlx模块中的类
班级
|
描述
|
| --- | --- |
| BaseResult | 服务器返回的不同类型结果的基类 |
| Collection | 集合是一个容器，可用于在 MySQL 数据库中存储文档 |
| CollectionAdd | 集合上文档添加的处理程序 |
| CollectionFind | 集合上文档选择的处理程序 |
| CollectionModify | 更新集合中文档的操作 |
| CollectionRemove | 删除集合中文档的操作 |
| DocResult | 允许遍历由 Collection.find 操作返回的 DbDoc 对象 |
| LockContention | 常数来表示锁争用类型 |
| Result | 允许检索对数据库执行的非查询操作的相关信息 |
| RowResult | 允许遍历表返回的行对象。选择操作 |
| Schema | 表示从使用 X 协议创建的会话中检索的架构 |
| Session | 支持使用 X 协议与 MySQL 服务器进行交互 |
| SqlExecute | SQL 语句执行处理程序，支持参数绑定 |
| SqlResult | 允许在通过 Session.sql 对数据库执行操作后浏览结果信息 |
| Table | 表示模式上的表，通过使用 mysqlx 模块创建的会话进行检索 |
| TableDelete | 从表中删除数据的操作 |
| TableInsert | 表中插入操作的处理程序 |
| TableSelect | 表中记录选择的处理程序 |
| TableUpdate | 表中记录更新操作的处理程序 |
| Type | 常数来表示列对象上的数据类型 |
正如您所看到的，有几个类是由mysqlx模块提供的。如果这看起来有点难以承受，请不要担心。我们不需要所有这些来处理关系数据；大多数是为 JSON 文档(也称为文档存储)设计的。然而，我们将需要使用Session类。
注意
本书中的表格引用了在线 Doxygen 文档( https://dev.mysql.com/doc/dev/mysqlsh-api-python/8.0/ )中记录的 MySQL Shell 中实现的 Python X DevAPI。其他语言的 X DevAPI 在组织以及类和方法的命名方案上可能略有不同。
我们已经在第三章中发现了会话。回想一下，我们使用\connect shell 命令与 shell 中的会话进行交互，这允许您在 shell 交互会话中建立连接。在 Python 中使用会话略有不同。接下来我们来看看Session类。
会话类
Session 类是我们在编写与数据交互的 Python 应用时将使用的主要类。我们使用该模块将连接信息以连接字符串或特定于语言的结构(Python 中的字典)的形式传递给服务器，以将连接参数作为 URI 或连接字典作为参数传递(而不是两者都作为)。获取会话对象最常用的方法如下所示。
get_session(<URI or connection dictionary>)
下面显示了使用连接选项字典获取会话对象实例和使用连接字符串(URI)获取会话对象实例的示例。
import mysqlx
mysqlx_session1 = mysqlx.get_session({'host': 'localhost', 'port': 33060, 'user': 'root', 'password': 'secret'})
mysqlx_session2 = mysqlx.get_session('root:secret@localhost:33060')
如果连接成功，结果变量将指向一个对象实例。如果失败，结果可能是一个错误或未初始化的连接。我们将在下一章看到如何处理这个问题。
一旦我们有了一个会话，我们就可以通过获取一个模式对象来开始处理我们的数据。
模式类
X DevAPI 使用术语“模式”来指代一组集合，它们是文档的集合。然而，当处理关系数据时，我们使用“数据库”来指代表和类似对象的集合。人们可能会认为“模式”是“数据库”的同义词，对于 MySQL 的旧版本来说，这是正确的。然而，当使用文档存储和 X DevAPI 时，应该使用“模式”，而当引用关系数据时，应该使用“数据库”
模式或数据库:重要吗？
从 MySQL 5.0.2 开始，这两个术语通过 SQL 命令CREATE DATABASE和CREATE SCHEMA成为同义词。然而，其他数据库系统做出了区分。也就是说，在某些情况下，模式是表的集合，而数据库是模式的集合。其他人认为模式是定义数据结构的东西。如果您使用其他数据库系统，请确保检查定义，以便正确使用术语。
开始处理数据时，您需要做的第一件事是选择(获取)一个现有的模式，删除一个现有的模式，或者创建一个新的模式。您可能还想列出服务器上的模式。Session类提供了几个执行这些操作的方法，所有这些方法都返回一个Schema对象。表 4-2 列出了与模式相关的方法、参数和返回值。
表 4-2
会话类–模式方法
|方法
|
返回
|
描述
|
| --- | --- | --- |
| create_schema(str name) | 计划 | 在数据库上创建一个模式，并返回相应的对象 |
| get_schema(str name) | 计划 | 通过名称从当前会话中检索架构对象 |
| get_default_schema() | 计划 | 检索了配置为会话默认值的架构 |
| get_current_schema() | 计划 | 检索到会话中的活动架构 |
| set_current_schema(str name) | 计划 | 为此会话设置当前架构，并返回其架构对象 |
| get_schemas() | 目录 | 检索了会话中可用的架构 |
| drop_schema(str name) | 没有人 | 删除具有指定名称的架构 |
现在让我们来看看用于执行符合 ACID 的事务的事务方法。
交易方式
事务提供了一种机制，允许一组操作作为单个原子操作执行。例如，如果为一个银行机构建立一个数据库，将资金从一个账户转移到另一个账户的宏操作将优选地被完整地执行(资金从一个账户转移到另一个账户),而不会中断。
事务允许将这些操作封装在一个原子操作中，如果在所有操作完成之前发生错误，该原子操作将取消任何更改，从而避免数据从一个表中删除，并且永远不会进入下一个表。包含在事务命令中的 SQL 语句形式的一组示例操作如下:
START TRANSACTION;
UPDATE SavingsAccount SET Balance = Balance – 100
WHERE AccountNum = 123;
UPDATE CheckingAccount SET Balance = Balance + 100
WHERE AccountNum = 345;
COMMIT;
MySQL 的 InnoDB 存储引擎(默认存储引擎)支持确保数据完整性的 ACID 事务，能够在所有操作成功的情况下提交(保存)结果更改，或者在任何一个操作失败的情况下回滚(撤消)更改。
会话类实现了事务处理的方法，这些方法反映了前面显示的 SQL 命令。表 4-3 列出了交易方式。
表 4-3
交易方式
|方法
|
返回
|
描述
|
| --- | --- | --- |
| start_transaction() | 没有人 | 在服务器上启动事务上下文 |
| commit() | 没有人 | 提交调用 startTransaction()后执行的所有操作 |
| rollback() | 没有人 | 放弃调用 startTransaction()后执行的所有操作 |
| set_savepoint(str name="") | 潜艇用热中子反应堆（submarine thermal reactor 的缩写） | 创建或替换具有给定名称的事务保存点 |
| release_savepoint(str name) | 没有人 | 删除在事务中定义的保存点 |
| rollback_to(str name) | 没有人 | 将事务回滚到指定的保存点，而不终止事务 |
注意，最后三种方法允许您创建命名事务保存点，这是事务处理的一种高级形式。有关保存点和事务的更多信息，请参见服务器在线参考手册。10
现在，让我们看看与服务器连接相关的方法。
连接方法
下划线连接有两种方法。一个用于检查连接是否打开，另一个用于关闭连接。表 4-4 显示了Session类中剩余的可用实用方法。
表 4-4
连接方法
|方法
|
返回
|
描述
|
| --- | --- | --- |
| close() | 没有人 | 关闭会话 |
| is_open() | 弯曲件 | 如果已知会话是打开的，则返回 true |
杂项方法
Session 类中还有几个实用方法。表 4-5 列出了附加功能。有关这些方法的更多信息，请参见 X DevAPI 在线参考。
表 4-5
杂项方法
|方法
|
返回
|
描述
|
| --- | --- | --- |
| quote_name(str id) | 潜艇用热中子反应堆（submarine thermal reactor 的缩写） | 转义标识符 |
| get_uri() | 潜艇用热中子反应堆（submarine thermal reactor 的缩写） | 返回会话的 URI |
| set_fetch_warnings(bool enable) | 没有人 | 启用或禁用警告生成 |
| sql(str sql) | sqlstatesment | 创建一个 SqlStatement 对象，以允许在目标 MySQL 服务器上运行收到的 SQL 语句 |
注意sql()方法。我们可以使用这个方法来发布 SQL 语句，但是一般来说，在处理数据时我们不需要这样做，因为有一个Table对象。一旦我们看到哪些 CRUD 操作可用于处理关系数据，我们将更详细地研究这个类。
CRUD 操作(关系数据)
X DevAPI 实现了一个创建、读取、更新和删除(CRUD)模型，用于处理模式中包含的对象。模式可以包含任意数量的集合、文档、表、视图和其他关系数据对象(如触发器)。在这一节中，我们将看到 schema 和 tables 类的概述。CRUD 模型是为模式中的所有对象实现的，这些对象可以包含文档存储和关系数据的数据。
正如我们将在第六章中看到的，文档存储数据 CRUD 操作使用动词 add、find、modify 和 remove，而关系数据使用匹配等效 SQL 命令(insert、select、update 和 delete)的术语。表 4-6 快速展示了这些方法是如何命名的，以及每个方法的简要描述。注意，我们对文档存储数据使用Collection类，对关系数据使用Table类。
表 4-6
文档存储和关系数据的 CRUD 操作
|CRUD 操作
|
描述
|
文档存储
|
关系数据
|
| --- | --- | --- | --- |
| 创造 | 添加新项目/对象 | collection.add() | table.insert() |
| 阅读 | 检索/搜索数据 | collection.find() | table.select() |
| 更新 | 修改数据 | collection.modify() | table.update() |
| 删除 | 移除项目/对象 | collection.remove() | table.delete() |
在下面几节中，我们将看到处理关系数据(模式和表)所需的特定于每个类的方法。让我们从查看 Schema 类的细节开始。
模式类
模式是存储数据的对象的容器。回想一下，这可以是文档存储数据的集合，也可以是关系数据的表或视图。就像过去处理关系数据一样，您必须选择(或使用)一个模式来存储集合、表或视图中的数据。
虽然您可以混合使用文档存储数据(集合)和关系数据(表、视图)，但为了便于记忆，我们将研究 Schema 类方法，因为它们与处理关系数据有关。
表 4-7 显示了集合和表格的操作方法。同样，在本章和下一章中，我们将只使用这些方法来处理表，但是看一下文档存储方法并没有什么坏处。注意，create 和 get 方法返回一个对象的实例。例如，get_table()方法返回一个Table对象。
表 4-7
模式类方法
|方法
|
返回
|
描述
|
| --- | --- | --- |
| get_tables() | 目录 | 返回该模式的表列表 |
| get_collections() | 目录 | 返回此架构的集合列表 |
| get_table(str name) | 桌子 | 返回该模式的给定名称的表 |
| get_collection(str name) | 募捐 | 返回此架构的给定名称的集合 |
| get_collection_as_table(str name) | 桌子 | 返回一个代表数据库集合的 Table 对象 |
| create_collection(str name) | 募捐 | 在当前架构中创建具有指定名称的新集合，并检索表示所创建的新集合的对象 |
| drop_collection(str name) | 没有人 | 删除指定的集合 |
现在，让我们看看Table类的方法。
表格类
表的概念是关系数据的主要组织机制。在 X DevAPI 中，表是我们都熟悉的相同的关系数据结构。X DevAPI 有一个Table(您也可以将它们用于视图)类，包含 CRUD 操作(选择、插入、更新和删除)以及用于计算行数或基对象是否是视图的其他方法。表 4-8 显示了Table类的方法。
表 4-8
表格类
|方法
|
返回
|
描述
|
| --- | --- | --- |
| insert() | 表格插入 | 创建 TableInsert 对象以向表中插入新记录 |
| insert(list columns) | 表格插入 | 使用列列表插入一行 |
| insert(str col1, str col2,...) | 表格插入 | 使用列的参数列表插入一行 |
| select() | 表格选择 | 创建 TableSelect 对象以从表中检索行 |
| select(list columns) | 表格选择 | 创建 TableSelect 对象以从表中检索行 |
| update() | 表格更新 | 创建记录更新处理程序 |
| delete() | 表删除 | 创建记录删除处理程序 |
| is_view() | 弯曲件 | 指示此表对象是否表示数据库上的视图 |
| count() | （同 Internationalorganizations）国际组织 | 返回表格中的行数 |
| get_name() | 潜艇用热中子反应堆（submarine thermal reactor 的缩写） | 返回对象的名称 |
| get_session() | 目标 | 返回该数据库对象的会话对象 |
| get_schema() | 目标 | 返回该数据库对象的架构对象 |
| exists_in_database() | 弯曲件 | 验证该对象是否存在于数据库中 |
请注意，没有创建表的方法。我们必须使用CREATE TABLE SQL 命令或sql()方法来执行 SQL 语句。事实上，没有创建任何关系数据对象的方法。您必须使用 SQL 发出适当的 create 语句来创建对象。例如，在前面的例子中，要为我们的工厂 _ 传感器数据创建一个表，我们可以使用下面的CREATE TABLE语句。虽然我们之前已经看到了这一点，但是下面显示了一个 Python 代码片段，其中我们声明了一个变量来保存查询，并演示了使用sql()方法执行查询。
...
CREATE_TBL = """
CREATE TABLE `factory_sensors`.`trailer_assembly` (
`id` int auto_increment,
`sensor_name` char(30) NOT NULL,
`sensor_value` float DEFAULT NULL,
`sensor_event` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`sensor_units` char(15) DEFAULT NULL,
PRIMARY KEY `sensor_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
"""
my_session = mysqlx.get_session(user_info)
my_db = my_session.create_schema('factory_sensors')
sql_res = my_session.sql(CREATE_TBL).execute()
my_tbl = my_db.get_table('trailer_assembly')
...
小费
没有创建表或视图的 create 方法。您必须将 SQL 命令传递给sql()方法来创建这些(和其他关系数据)对象。
现在我们已经创建了表格，我们可以使用Table对象插入数据。回想一下，我们在这里有一组对象。我们从Session对象create_schema()方法开始，该方法返回一个Database对象，我们将该对象保存到一个名为my_db的变量中。在我们创建了表格之后，我们调用了my_db.get_table()方法来获取Table对象。
在我们看到处理数据的例子之前，让我们看看处理关系数据的其他类和方法。表 4-9 列出了与关系数据的 CRUD 操作相关的每个类的方法。
表 4-9
关系数据的 CRUD 操作类
|班级
|
方法
|
返回
|
描述
|
| --- | --- | --- | --- |
| 表格选择 | 对表进行记录检索操作的语句 |
| select (list searchExprStr) | 表格选择 | 定义要从表中检索的列 |
| where (str expression) | 表格选择 | 设置搜索条件以过滤要从表中检索的记录 |
| group_by (list searchExprStr) | 表格选择 | 为检索到的行设置分组标准 |
| having (str condition) | 表格选择 | 为聚合函数运算中要考虑的记录设置条件 |
| order_by (list sortExprStr) | 表格选择 | 设置检索记录的顺序 |
| limit (int numberOfRows) | 表格选择 | 设置选择操作返回的最大行数 |
| offset (int numberOfRows) | 表格选择 | 当定义了限制时，设置结果集上要跳过的行数 |
| bind (str name, Value value) | 表格选择 | 将值绑定到此操作中使用的特定占位符 |
| execute () | RowResult | 使用所有配置的选项执行选择操作 |
| 表格插入 | 对表执行插入操作的语句 |
| insert () | 表格插入 | 初始化记录插入处理程序 |
| insert (list columns) | 表格插入 | 用接收到的列列表初始化记录插入处理程序 |
| insert (str col1, str col2,...) | 表格插入 | 用接收到的列列表初始化记录插入处理程序 |
| values (Value, Value value,...) | 表格插入 | 用给定值向插入操作添加新行 |
| execute () | 结果 | 执行插入操作 |
| 表格更新 | 对表进行记录更新操作的语句 |
| update () | 表格更新 | 已初始化更新操作 |
| set (str attribute, Value value) | 表格更新 | 添加更新操作 |
| where (str expression) | 表格更新 | 设置搜索条件以过滤要更新的记录 |
| order_by (list sortExprStr) | 表格更新 | 设置记录更新的顺序 |
| limit (int numberOfRows) | 表格更新 | 设置操作要更新的最大行数 |
| bind (str name, Value value) | 表格更新 | 将值绑定到此操作中使用的特定占位符 |
| execute () | 结果 | 使用所有配置的选项执行删除操作 |
| 表删除 | 删除表的语句 |
| delete () | 表删除 | 已初始化此记录删除处理程序 |
| where (str expression) | 表删除 | 设置搜索条件以过滤要从表中删除的记录 |
| order_by (list sortExprStr) | 表删除 | 设置删除记录的顺序 |
| limit (int numberOfRows) | 表删除 | 设置操作要删除的最大行数 |
| bind (str name, Value value) | 表删除 | 将值绑定到此操作中使用的特定占位符 |
| execute () | 结果 | 使用所有配置的选项执行删除操作 |
哇，方法真多！请注意，语句类之间有一些相似之处。例如，大多数都有绑定参数、搜索条件等的方法。为了更好地理解这一点，让我们看看 X DevAPI 用户指南中 CRUD 操作的语法图( https://dev.mysql.com/doc/x-devapi-userguide/en/ )。
我们将使用这些类和方法的方式是一个称为方法链的概念，我们可以将我们的类和方法调用组合成一个“链”，在这个“链”中，我们通过使用点标记来扩展语法，为返回的对象调用方法。换句话说，如果method_a()返回一个具有名为count()的方法的对象的实例，我们可以像这样将它链接在一起:method_a().count()，从而避免存储中间对象的需要。
什么是方法链？
方法链接(也称为命名参数习惯用法)是面向对象编程中的一种设计约束，其中每个方法(支持链接)返回一个对象实例。因此，只需将调用添加到第一个方法的末尾，就可以访问(调用)返回对象上的任何方法。
例如，如果一个类X有一个方法a()，它用方法b()返回对象Y，我们可以如下将调用链接在一起。
x = something.get_x()
res = x.a().b()
在这种情况下，x.a()方法首先执行，然后当它返回一个Y对象实例时，它调用 Y 对象实例上的b()方法。
有关方法链接概念的更多信息，请参见 https://en.wikipedia.org/wiki/Method_chaining 。
以下部分演示了关系数据的 CRUD 操作的简单示例。回想一下，我们将使用从会话中检索的表对象(实例)来执行 CRUD 操作。让我们来看一个例子。
创建数据
创建操作使用一个名为insert()的Table对象方法，该方法将一列列表作为参数。然后，我们可以使用方法链(见后面)将值列表作为参数传递，从而将values()方法用于TableInsert对象。这是因为 insert()方法返回了一个TableInsert类的实例。例如，我们用下面的INSERT查询在前面的部分添加了一行。
INSERT INTO factory_sensors.trailer_assembly (sensor_name, sensor_value, sensor_units) VALUES ('paint_vat_temp', 32.815, 'Celsius');
为了在 Python 中执行这个，我们使用下面的语句。注意，我们使用了一个变量来存储列的列表。我们还使用方法链接来调用values()和execute()方法，以便在一条语句中完成行插入。
...
COLUMNS = ['sensor_name', 'sensor_value', 'sensor_units']
my_tbl.insert(COLUMNS).values('paint_vat_temp', 32.815, 'Celsius').execute()
...
图 4-1 显示了读取操作的语法图。这里，这个链相当小，因为我们只有两个中间对象(values()和execute()的“所有者”)。如果这看起来有点奇怪，不要难过。从 SQL 语句的世界过渡到高级编码技术可能是一个挑战，但是通过实践和更多的例子，像这样链接方法将变得非常自然。
图 4-1
语法图- Table.insert()
阅读日期
从表对象中读取数据是我们使用方法链接将对象方法链接在一起的另一种情况。如果您考虑典型的SELECT语句的复杂性，那么读操作也可能相当复杂就不足为奇了。然而，我们将在这个演示中保持简单，在下一章看更复杂的例子。
下面是获取表中所有行的SELECT语句。
SELECT sensor_value, sensor_name, sensor_units FROM factory_sensors.trailer_assembly;
同样的方法链接也适用于select()方法，它返回一个Table对象，在那里我们链接了where()子句。在这个简单的例子中，我们没有一个WHERE子句，所以我们没有参数。我们仍然添加了execute()方法来运行查询。
...
COLUMNS = ['sensor_name', 'sensor_value', 'sensor_units']
my_res = my_tbl.select(COLUMNS).execute()
...
图 4-2 显示了Table类的select()方法的语法图。在这里，我们看到不同的类和方法是如何通过链末端的execute()方法链接在一起的。链中描述的各种方法是可选的，但是大多数读操作将包括where()方法(子句)。
图 4-2
语法图- Table.select()
更新数据
更新操作类似于读取操作，我们使用一个或多个方法来指定在典型的UPDATE SQL 查询中可以找到的子子句。下面是一个简单的例子，我们用等于 1.52 的传感器值更新行，改变传感器单位。请注意，我们使用了 MySQL 函数库的一个技巧来将浮点值转换为特定的小数，以消除舍入产生的错误(sensor_units = 1.52不起作用)。
UPDATE factory_sensors.trailer_assembly SET sensor_units = 'inches' WHERE sensor_value LIKE 1.52;
为了在 Python 中执行这个，我们使用表对象的update()方法，将它与TableUpdate对象的set()和where()方法链接起来，并传入我们的参数。
my_tbl.update().set().where('sensor_value LIKE 1.52').execute()
图 4-3 显示了更新操作的语法图。这类似于 read 操作，因为我们有几个共同的中间步骤，如WHERE子句、order by 等。
图 4-3
语法图- Table.update()
删除数据
删除操作类似于读取和更新操作，我们使用一个或多个方法来指定在典型的DELETE SQL 查询中会找到的子子句。下面是一个简单的例子，我们删除了传感器值为> 30 的行。
DELETE FROM factory_sensors.trailer_assembly WHERE sensor_value > 30;
为了在 Python 中执行这个，我们使用表对象的delete()方法，并用TableDelete对象的where()方法链接它，并传入我们的参数。
my_tbl.delete().where('sensor_value > 30').execute()
图 4-3 显示了更新操作的语法图。这类似于更新操作，因为我们有几个共同的中间步骤，如WHERE子句、order by 等。
图 4-4
语法图- Table.delete()
在我们看到使用关系数据的完整 Python 示例之前，我们还应该探索一件事——使用结果集(有时称为数据集)。
使用结果
到目前为止，我们已经看到了一些处理结果的简单例子，虽然看起来所有的结果都是同一个类，但是有几个结果类。返回的结果类的对象实例取决于操作。例如，每种类型的结果都有一个单独的类。结果有时被称为数据集或结果集。
表 4-10 显示了您在处理数据集和结果时会遇到的所有类及其方法。所有结果类都是从 BaseResult 对象派生的，该对象提供了一组属性和基方法。为了完整起见，我在表中重复了这些。
注意
类方法用()表示，属性用()表示。
表 4-10
处理数据集和结果的类和方法
|班级
|
方法
|
返回
|
描述
|
| --- | --- | --- | --- |
| 结果 | 允许检索对数据库执行的非查询操作的相关信息 |
| get_affected_item_count() | （同 Internationalorganizations）国际组织 | 最后一道工序中受影响的物件数量 |
| get_auto_increment_value() | （同 Internationalorganizations）国际组织 | (从插入操作中)自动生成的最后一个插入 id |
| get_generated_ids() | 目录 | 返回服务器上生成的文档 id 列表 |
| affected_item_count | （同 Internationalorganizations）国际组织 | 与 get_affected_itemCount()相同 |
| auto_increment_value | （同 Internationalorganizations）国际组织 | 与 get_auto_increment_value()相同 |
| generated_ids | 目录 | 与 get_generated_ids()相同 |
| affected_items_count | （同 Internationalorganizations）国际组织 | 与 get_affected_items_count()相同 |
| warning_count | （同 Internationalorganizations）国际组织 | 与 get_warning_count()相同 |
| warnings_count | （同 Internationalorganizations）国际组织 | 与 get_warnings_count()相同 |
| warnings | 目录 | 与 get_warnings()相同 |
| execution_time | 潜艇用热中子反应堆（submarine thermal reactor 的缩写） | 与 get_execution_time()相同 |
| RowResult | 允许遍历表返回的行对象。选择操作 |
| fetch_one() | 排 | 检索 rowresult 中的下一行 |
| fetch_all() | 目录 | 返回 DbDoc 对象列表，其中包含每个未读文档的元素 |
| get_column_count() | （同 Internationalorganizations）国际组织 | 检索当前结果的列数 |
| get_column_names() | 目录 | 获取当前结果中的列 |
| get_columns() | 目录 | 获取活动结果中列的列元数据 |
| column_count | （同 Internationalorganizations）国际组织 | 与 get_column_count()相同 |
| column_names | 目录 | 与 get_column_names()相同 |
| columns | 目录 | 与 get_columns()相同 |
| affected_items_count | （同 Internationalorganizations）国际组织 | 与 get_affected_items_count()相同 |
| warning_count | （同 Internationalorganizations）国际组织 | 与 get_warning_count()相同 |
| warnings_count | （同 Internationalorganizations）国际组织 | 与 get_warnings_count()相同 |
| warnings | 目录 | 与 get_warnings()相同 |
| execution_time | 潜艇用热中子反应堆（submarine thermal reactor 的缩写） | 与 get_execution_time()相同 |
| SqlResult | 表示 SQL 语句的结果 |
| get_auto_increment_value() | （同 Internationalorganizations）国际组织 | 返回插入的最后一条记录的标识符 |
| get_affected_row_count() | （同 Internationalorganizations）国际组织 | 返回受已执行查询影响的行数 |
| has_data() | 弯曲件 | 如果最后一个语句执行有结果集，则返回 true |
| next_data_set() | 弯曲件 | 准备 SqlResult 以从下一个结果开始读取数据(如果返回了许多结果) |
| next_result() | 弯曲件 | 准备 SqlResult 以从下一个结果开始读取数据(如果返回了许多结果) |
| auto_increment_value | （同 Internationalorganizations）国际组织 | 与 get_auto_increment_value()相同 |
| affected_row_count | （同 Internationalorganizations）国际组织 | 与 get_affected_row_count()相同 |
| column_count | （同 Internationalorganizations）国际组织 | 与 get_column_count()相同 |
| column_names | 目录 | 与 get_column_names()相同 |
| columns | 目录 | 与 get_columns()相同 |
| affected_items_count | （同 Internationalorganizations）国际组织 | 与 get_affected_items_count()相同 |
| warning_count | （同 Internationalorganizations）国际组织 | 与 get_warning_count()相同 |
| warnings_count | （同 Internationalorganizations）国际组织 | 与 get_warnings_count()相同 |
| warnings | 目录 | 与 get_warnings()相同 |
| execution_time | 潜艇用热中子反应堆（submarine thermal reactor 的缩写） | 与 get_execution_time()相同 |
| domresult | 允许遍历由 Collection.find 操作返回的 DbDoc 对象 |
| fetch_one() | 文件 | 在 domresult 上检索下一个 dbdoc |
| fetch_all() | 目录 | 返回 DbDoc 对象列表，其中包含每个未读文档的元素 |
| affected_items_count | （同 Internationalorganizations）国际组织 | 与 get_affected_items_count()相同 |
| warning_count | （同 Internationalorganizations）国际组织 | 与 get_warning_count()相同 |
| warnings_count | （同 Internationalorganizations）国际组织 | 与 get_warnings_count()相同 |
| warnings | 目录 | 与 get_warnings()相同 |
| execution_time | 潜艇用热中子反应堆（submarine thermal reactor 的缩写） | 与 get_execution_time()相同 |
有迭代器的三个类实现了两个方法:fetch_one()和fetch_all()。它们像您想象的那样工作，返回一个数据集或一组文档的一组对象。fetch_one()方法返回数据集中的下一个数据项，如果没有更多的数据项，则NULL返回所有的数据项。更具体地说，fetch_one()一次从服务器检索一个数据项，而fetch_all()一次从服务器检索所有数据。您使用哪一个将取决于数据集的大小和您想要如何处理数据。
那么，这在 Python 中是什么样子的呢？下面显示了一个简单的例子，执行一个读操作来获取表中的所有行(没有WHERE子句)。这里，我们首先检索表列的列表，以便打印它们。我们将使用两个循环:一个循环遍历列名列表，另一个循环遍历读取操作返回的行。
column_names = my_res.get_column_names()
column_count = my_res.get_column_count()
for i in range(0,column_count):
if i < column_count - 1:
print "{0}, ".format(column_names[i]),
else:
print "{0}".format(column_names[i]),
print
在这里，我们看到了一些用于获得结果的 Python 语句。在这种情况下，我们使用的是TableSelect类，但是由于大多数结果类都有相同的方法，所以对于其他结果，您的代码也是类似的。您可能会注意到一些基本的格式化代码，使输出以逗号分隔。这只是为了演示。您自己的应用可能会一次消费一行数据，并对其进行处理。然而，获取列和获取行的概念是相同的。一旦你记下这些概念，我们只需要添加如何开始的概念。
开始编写 Python 脚本
现在是时候获取一些实际的代码了，我们可以使用这些代码来加强到目前为止所讨论的概念。让我们来看一个简单的例子。乍一看，这段代码似乎有点吓人，但它是一个非常简单的示例，包含您需要通过打开一个会话、创建新模式和创建表来连接到服务器的样板代码。从这里，我们看到了从对表对象的select()调用开始的 CRUD 操作的例子，以及使用 CRUD 操作的演示。
清单 4-6 显示了一个 Python 脚本来创建之前使用的数据库和表，包括添加数据和执行简单的 select 查询。只是，这次我们用 Python 来做！如果您想继续，请打开 shell 并连接到您的服务器，如下所示。
#
# Introducing the MySQL 8 Shell
#
# This example shows a simple X DevAPI script to work with relational data
#
# Dr. Charles A. Bell, 2019
from mysqlsh import mysqlx # needed in case you run the code outside of the shell
# SQL CREATE TABLE statement
CREATE_TBL = """
CREATE TABLE `factory_sensors`.`trailer_assembly` (
`id` int auto_increment,
`sensor_name` char(30) NOT NULL,
`sensor_value` float DEFAULT NULL,
`sensor_event` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`sensor_units` char(15) DEFAULT NULL,
PRIMARY KEY `sensor_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
"""
# column list, user data structure
COLUMNS = ['sensor_name', 'sensor_value', 'sensor_units']
user_info = {
'host': 'localhost',
'port': 33060,
'user': 'root',
'password': 'secret',
}
print("Listing 4-6 Example - Python X DevAPI Demo with Relational Data.")
# Get a session (connection)
my_session = mysqlx.get_session(user_info)
# Precautionary drop schema
my_session.drop_schema('factory_sensors')
# Create the database (schema)
my_db = my_session.create_schema('factory_sensors')
# Execute the SQL statement to create the table
sql_res = my_session.sql(CREATE_TBL).execute()
# Get the table object
my_tbl = my_db.get_table('trailer_assembly')
# Insert some rows (data)
my_tbl.insert(COLUMNS).values('paint_vat_temp', 32.815, 'Celsius').execute()
my_tbl.insert(COLUMNS).values('tongue_height_variance', 1.52, 'mm').execute()
my_tbl.insert(COLUMNS).values('ambient_temperature', 24.5, 'Celsius').execute()
my_tbl.insert(COLUMNS).values('gross_weight', 1241.01, 'pounds').execute()
# Execute a simple select (SELECT ∗ FROM)
print("\nShowing results after inserting all rows.")
my_res = my_tbl.select(COLUMNS).execute()
# Display the results . Demonstrates how to work with results
# Print the column names followed by the rows
column_names = my_res.get_column_names()
column_count = my_res.get_column_count()
for i in range(0,column_count):
if i < column_count - 1:
print "{0}, ".format(column_names[i]),
else:
print "{0}".format(column_names[i]),
print
for row in my_res.fetch_all():
for i in range(0,column_count):
if i < column_count - 1:
print "{0}, ".format(row[i]),
else:
print "{0}".format(row[i]),
print
# Update a row
my_tbl.update().set('sensor_units', 'inches').where('sensor_value LIKE 1.52').execute()
print("\nShowing results after updating row with sensor_value LIKE 1.52.")
# Execute a simple select (SELECT ∗ FROM)
my_res = my_tbl.select(COLUMNS).execute()
# Display the results
for row in my_res.fetch_all():
print row
# Delete some rows
my_tbl.delete().where('sensor_value > 30').execute()
# Execute a simple select (SELECT ∗ FROM)
print("\nShowing results after deleting rows with sensor_value > 30.")
my_res = my_tbl.select(COLUMNS).execute()
# Display the results
for row in my_res.fetch_all():
print row
# Delete the database (schema)
my_session.drop_schema('factory_sensors')
Listing 4-6Simple Relational Data Example
花点时间通读代码，确保找到 CRUD 操作。同样，这些都是非常简单的例子，只有一些关于WHERE子句表达式的小例子。注释行和额外的打印语句形成了一个指南，有助于使代码更易于阅读。我们将在下一章看到一个更详细的例子，完整地解释如何使用各种方法来限制输出(WHERE子句)。
现在，让我们看看代码的执行。在这种情况下，我们将使用 shell 的批处理执行特性来读取我们之前创建的文件并执行它。清单 4-7 显示了运行脚本的命令和结果。
C:\Users\cbell\MySQL Shell\source\Ch04>mysqlsh --py -f listing4-6.py
Listing 4-6 Example - Python X DevAPI Demo with Relational Data.
Showing results after inserting all rows.
sensor_name, sensor_value, sensor_units
paint_vat_temp, 32.815, Celsius
tongue_height_variance, 1.52, mm
ambient_temperature, 24.5, Celsius
gross_weight, 1241.01, pounds
Showing results after updating row with sensor_value LIKE 1.52.
[
"paint_vat_temp",
32.815,
"Celsius"
]
[
"tongue_height_variance",
1.52,
"inches"
]
[
"ambient_temperature",
24.5,
"Celsius"
]
[
"gross_weight",
1241.01,
"pounds"
]
Showing results after deleting rows with sensor_value > 30.
[
"tongue_height_variance",
1.52,
"inches"
]
[
"ambient_temperature",
24.5,
"Celsius"
]
Listing 4-7Executing the Sample Code
输出显示了第一个读取操作，该操作使用我们前面看到的处理结果的方法打印行，即以逗号分隔的输出形式打印列名和行，每行一行。另一个输出显示了结果是如何返回给 Python 的——它们是列表的列表！这就是为什么我们看到输出显示为行的字符串值列表。
花点时间再次浏览代码，确保您可以看到 CRUD 操作对数据的影响。也就是说，在每次读取、更新和删除操作之后，结果的输出应该略有不同。
连接器/Python 呢？
如果您一直在使用名为 Connector/Python 的 Python 数据库连接器，您可能会想知道这里有什么不同，而连接器却不能做到。在这一点上，你的直觉是正确的。到目前为止，我介绍的所有内容都可以用连接器和优秀的 Python 编程来完成。事实上，正是这种重叠表明 X DevAPI 已经实现了它的一个目标。
现在，您可能有兴趣知道连接器完全支持 X DevAPI，并且 Shell 使用了连接器。我们正在学习如何从一个不同的角度处理我们的数据——数据的角度就是代码。一旦你阅读了第六章，它将全部点击(如果它还没有)。
摘要
MySQL 中传统的数据存储和检索机制是 SQL 接口。这是大多数人熟悉的，事实上大多数人学习 SQL 是为了成为开发人员或数据库管理员。因此，对于许多人来说，学习像 MySQL Shell 这样的新工具最好从熟悉的 SQL 开始。事实上，这就是本章的全部内容。
在这一章中，我们看到了一个简单的游览，首先是在传统的 SQL 交互会话中使用 shell，在这个会话中我们发出 SQL 语句并处理关系数据。它很熟悉，并且演示了关系数据的基本概念。即使您以前没有使用过 SQL，这个小演示也足以让您上手。
然而，趋势是将我们的数据混合到我们的代码中，也就是说，使我们的数据成为代码的一部分。为此，我们需要一个强大的 API，让我们可以像处理代码中的对象一样处理数据。X DevAPI 就是答案。此外，我们还看到了对用于关系数据的 X DevAPI 的简要介绍。我们不仅学习了如何开始使用 X DevAPI，还看到了一些可用的 Python 代码，您可以使用它们开始编写自己的 Python 脚本。
但这仅仅是开始。我们真正需要的是一个更大的例子，可以作为编写更高级 Python 脚本的教程。下一章将更详细地介绍本章中介绍的概念。
Footnotes 1MySQL 命令列表中并不是所有的 SQL 命令都是真正的标准 SQL 命令。许多实用程序命令都是非标准的 SQL 命令。因此，如果您使用其他数据库系统，这些命令可能看起来相似，但略有不同。
2
虽然在某些层面上非常不准确，但对于数据库新手来说，这种相似性是有效的。
3
https://dev.mysql.com/doc/refman/8.0/en/access-control.html
4
https://dev.mysql.com/doc/refman/8.0/en/functions.html
5
所以，如果不使用索引，你永远不要期望结果是相同的顺序！可以演示在一个系统上输入的相同数据在另一个系统(如系统)上输入时会有什么不同。这涉及到很多因素，包括字符集、操作系统等。这可能会导致订单不同。如果顺序是一个问题，使用索引。
6
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
7
https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
8
https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-data-definition.html
9
在这种情况下，对象是一个类的可执行实例。
10
https://dev.mysql.com/doc/refman/8.0/en/savepoint.html
五、示例：SQL 数据库开发
在上一章中，我们探索了使用两种模式的 shell:传统的 SQL 命令执行和使用 X DevAPI 编写 Python 代码在没有 SQL 数据库的情况下进行交互的简短旅程。
在这一章中，我们将看到如何使用 shell 开发 Python 代码模块来处理传统的关系数据库。事实上，我们会相信 MySQL Shell 是一个开发工具的说法。
我们将首先检查示例应用的数据库，然后构建数据库代码来访问数据库中的数据。我们将以循序渐进的方式来完成这项工作，以便让您更好地了解如何使用 shell 来开发您自己的代码。最后，我们将看到一个非常简短的演示，演示如何使用 shell 来测试数据库代码。
我们不会详细讨论示例应用，因为重点是如何使用 shell，而不是示例应用本身。然而，本书的附录包含了用于实现示例应用的代码。
让我们从检查示例数据库和简要讨论示例应用开始。
入门指南
能够用散文成功地解释主题，并用实例巩固读者的理解是一回事，但解释使用数据或代码的新方法的好处是完全不同的另一回事。在这些情况下，人们必须以互动的方式演示概念，以便通过例子来证明概念，而不是简单地展示它是如何可能实现的。在这一节中，我们将了解一个努力做到这一点的示例应用——证明如何使用 shell 来开发自己的代码。
然而，为了以这种方式展示 shell 的全部功能，样本必须足够复杂，具有足够的深度(和宽度)来完成它的角色。因此，这一章，我们将重点解决一个重大问题:如何组织你的车库！
好吧，那可能太远了，够不着。让我们回到简单地组织你的车库或车间的工具。如果你有任何工具，或者像我一样，有大量的工具用于各种各样的建造、修理和维护，知道每个工具的位置可能是一个真正的斗争，尤其是当你获得如此多的工具，你需要多个存储位置来存放它们。
示例应用概念
示例应用概念是一个组织概念。因此，我们将存储我们想要组织的事物的描述，包括它们被组织的事物。具体来说，我们想知道我们有哪些工具，它们存放在哪里。如果存储位置是工具箱或橱柜，我们还想知道它在哪个抽屉或架子上。也可能是我们把工具存放在盒子或箱子里，而这些工具又存放在某个地方。因此，我们不仅要建模工具，还要建模工具存储。
车库应用诞生于更好地组织的需要。事实上，拥有一个杂乱的车库或者只是随意存放你的工具可能会满足一些人的需求，像我这样的其他人需要更多的结构。另外，如果你想知道你是否有某种工具，最好不仅知道你有，而且知道它在哪里！
因此，这个应用主要被设计为一个查找工具，因此主要关注显示表中所有行的列表视图。
这就是存储位置是默认视图的原因。如果你走进你的车库(或车间)，你首先应该看到的是存储设备——工具箱、架子、搁板等。当你寻找一个工具时，你通常会在一个或多个地方(存储设备)寻找，利用你存储它的最后一个地方或公共地方的记忆。但是，如果您有许多工具，可能无法记住每个工具的位置，尤其是如果您已经有一段时间没有使用它了。
然而，示例应用还提供了一个视图来显示您所有的手动工具和电动工具。这些类别的列表视图显示了为您排序的所有项目。您只需要浏览列表来找到所需的工具，然后查看列来确定工具的存储位置。因此，通过在车库应用中点击几次，您就知道去哪里获得您想要的工具。我们将这个示例应用称为 MyGarage。酷吧。
小费
我们不会解释示例应用的每一个细微差别，而是将重点放在最适合用来证明使用 shell 开发代码的实用性的部分——数据库访问代码模块。
让我们快速看一下样例应用的部分用户界面。图 5-1 显示了手工具记录的详细视图。在这里，我们可以指定供应商、描述、工具大小、类型和位置。通过这种方式，我们可以获取关于一个工具的基本信息，包括哪个公司制造的以及我们把它存放在哪里。
图 5-1
手动工具详细视图
虽然这个视图看起来相当简单，但是底层数据库的设计要复杂一些。例如，人们可以查看表单并预测我们将有某种方式来存储供应商，因为一个供应商可能有许多工具。您还可以预测存储位置也会出现类似的情况。但是，请考虑一下，一件储物设备可能有一个或多个抽屉或搁板，或者两者都有。因此，我们可能希望对这些工具进行建模。
在开始数据库设计之前，让我们更好地理解示例应用中的对象。下面列出了应用中标识的对象及其使用方法。这将有助于理解数据是如何存储的(及其设计)。
-
手动工具:没有动力的工具
-
电动工具:依靠空气(气动)或电力运行的工具，有线或无线
-
储藏设备:有一个或多个地方可以存放工具(东西)的架子、箱子、柜子等
-
存储位置:存储设备的一个特征，如架子或抽屉
-
整理器:可以放置一个或多个工具，但需要存放在存放处的容器
-
厂商:工具的制造商
让我们看看示例应用的存储部分。这可能看起来有点复杂，但是一旦你看到它的实际应用，你就会明白了。假设我们有一个新的工具储物盒，里面有几个抽屉和搁板。如果我们要做一张桌子，只存放箱子，我们怎么知道工具放在哪个抽屉或哪个架子上呢？
例如，我们可以将工具列为在tool_chest_1中，但是如果它有十个抽屉和四个架子，那对我们没有多大帮助。谁想要一个告诉你大概位置的应用？你不得不拉开抽屉或者随意检查架子，直到找到你的工具。但是，如果我们抽象抽屉和架子，我们可以通过引用存储位置(抽屉、架子)来指定工具箱中给定工具的确切位置，存储位置引用存储设备。
让我们看一个例子。图 5-2 显示了一个可从家装商店(Lowe's)买到的 Kobalt 工具箱。注意衣柜有七个抽屉和两个搁板。
图 5-2
Kobalt 工具箱
如果我们在表格中为每个抽屉建模或创建条目，那么我们可以在工具、抽屉和工具箱之间分配一个关系。这不仅展示了我们如何更好地分类(组织)我们的数据，还展示了大多数使用真实数据的应用的一个关键方面——数据中存在多种一对多关系。
既然我们已经了解了示例应用的目标以及我们需要如何对存储特性建模，那么让我们来看看数据库是如何设计的。
数据库设计
让我们从实体关系图(ERD)开始我们的数据库设计之旅。图 5-3 显示了数据库的 ERD。如果您不熟悉这些图，它们通常会显示表、视图或您想要的任何其他对象，以及实体之间的关系(虚线)。该示例中还包括每个表的索引。有一个视图显示为实心矩形。
图 5-3
我的车库数据库 ERD(版本 1)
花些时间研究一下图表，以便熟悉我们将使用的表格。我们将在本节的后面看到这些表的更多细节。我们将数据库命名为garage_v1，因为我们将在第七章中看到如何将该数据库从关系模型迁移到 NoSQL 模型，这将成为garage_v2。
您可能会注意到，每个表都有一个定义为自动递增字段的代理键。这是一种很好的、简单的方法，可以确保表中的行是唯一的，并且是一种允许存储多个相同项目的人工机制。例如，在典型的工具收藏中，人们通常有不止一种某种工具，例如锤子、钳子、可调扳手等。使用自动增量键允许我们给每个工具一个独特的Id。
现在，让我们看看 ERD 中的每个实体，以便了解它们存储什么。我们将从关系最少的表开始构建，这样您就可以理解它们是如何构建的。
当通读这个设计时，精明的读者可能会看到改进设计的方法。然而，回想一下这个示例应用的目标是双重的:足够复杂以演示重要的示例，并且读者可以自己运行。因此，采取了一些设计折衷以避免过于复杂。 1
供应商表
vendor表包含数据库中工具的供应商或制造商的基本信息。我们记录了名称、供应商网站的 URL 以及可以从该供应商那里购买产品的简短描述。清单 5-1 显示了创建vendor表的 SQL CREATE TABLE命令。
CREATE TABLE `garage_v1`.`vendor` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(50) NOT NULL,
`URL` char(128) DEFAULT NULL,
`Sources` char(40) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Listing 5-1Vendor Table
注意，我们使用自动递增字段作为主键，这是一个整数字段。仔细查看表格选项。在这里，我们演示如何设置自动递增字段(列)的初始值。在这种情况下，我们选择起始值 100。我们可以为其他表选择其他起始值，使每个 id 范围都有些独特。例如，如果我们将另一个表的起始值设置为 1000，我们一眼就可以看出Id为 103 的行是供应商，而值为 1022 的行来自另一个表。
当然，大多数精明的数据库管理员会引用一些关系数据库教科书 2 中的章节和版本来描述这种做法有多糟糕，但是在实践中，如果您出于调试目的使用类似这样的通用编码形式，这可能会很方便。因为行在不同的表中，所以编码的恐惧或“罪恶”没有实现。也就是没有碰撞的可能。因此，您可以放心，因为这与关系数据库设计并不完全对立；相反，它是一个调试或编码工具。
组织者表格
organizer表用于存储关于组织者的信息，比如盒子、箱子、塑壳等等。这有助于解决一些工具具有它们自己的特殊情况和必须组合在一起使用的工具的问题，例如套筒组、某些类型的扳手等。我们还使用了自动递增的技巧，从 2000 开始为Id列赋值。清单 5-2 显示了创建organizer表的 SQL CREATE TABLE命令。
CREATE TABLE `garage_v1`.`organizer` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`PlaceId` int(11) NOT NULL,
`Type` enum('Bin','Box','Case') DEFAULT 'Case',
`Description` char(40) NOT NULL,
`Width` int(11) DEFAULT '0',
`Depth` int(11) DEFAULT '0',
`Height` int(11) DEFAULT '0',
PRIMARY KEY (`Id`),
KEY `OrganizerStorage_FK_idx` (`PlaceId`),
CONSTRAINT `OrganizerStorage_FK` FOREIGN KEY (`PlaceId`) REFERENCES `place` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Listing 5-2Organizer Table
请注意，该表包含一个外键。外键可以被视为一个表中的一行与另一个表中的一行之间的链接或关系。它们主要用于加强关系。例如，请注意前面的 SQL 代码中的限制。在这里，我们看到外键被限制在删除和更新操作上，因此如果该表中的一行引用了它的Id列，则不能在place表中删除该行。它还指定了在更新中不能更改 place 表中的Id列。这就是它被称为“foreign”的原因，因为它对另一个表施加了限制。这是另一种关系数据库构造，数据库设计人员使用它来帮助构建数据库的健壮性(以及防止意外更改)。
存储位置表
存储位置表(简称为place)用于存储关于我们可以存储东西的位置的信息，比如抽屉或架子。事实上，这个表通过一个名为Type的枚举列仅限于这两种类型。我们还存储一个描述，这个存储位置所在的存储设备的Id,它的尺寸。清单 5-3 显示了place表的 SQL CREATE TABLE。
CREATE TABLE `garage_v1`.`place` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`StorageId` int(11) NOT NULL,
`Type` enum('Drawer','Shelf') DEFAULT 'Drawer',
`Description` char(40) NOT NULL,
`Width` int(11) DEFAULT '0',
`Depth` int(11) DEFAULT '0',
`Height` int(11) DEFAULT '0',
PRIMARY KEY (`Id`),
KEY `PlaceStorage_FK_idx` (`StorageId`),
CONSTRAINT `PlaceStorage_FK` FOREIGN KEY (`StorageId`) REFERENCES `storage` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=1038 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Listing 5-3Place Table
这个表也像 organizer 表一样有一个外键。同样，这样做是为了在该表中的某一行引用的存储表中不能删除行(或更改Id列)。
存储设备表
存储设备表简称为storage,用于存储工具或一般存储结构的信息，如工具箱、橱柜、工作台或架子。事实上，与存储位置表一样，我们使用一个名为Type的枚举列来指定存储设备类型。
除了存储设备类型，我们还存储了描述、抽屉、搁板和门的数量(如果适用)及其总体尺寸，以及存储其在车库或车间中的位置(物理描述)的通用文本字段。清单 5-4 显示了针对storage表的 SQL CREATE TABLE命令。
CREATE TABLE `garage_v1`.`storage` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`VendorId` int(11) NOT NULL,
`Type` enum('Cabinet','Shelving','Toolchest','Workbench') DEFAULT 'Toolchest',
`Description` char(125) DEFAULT NULL,
`NumDrawers` int(11) DEFAULT '0',
`NumShelves` int(11) DEFAULT '0',
`NumDoors` int(11) DEFAULT '0',
`Width` int(11) DEFAULT NULL,
`Depth` int(11) DEFAULT NULL,
`Height` int(11) DEFAULT NULL,
`Location` char(40) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `VendorKey_idx` (`VendorId`),
CONSTRAINT `StorageVendor_FK` FOREIGN KEY (`VendorId`) REFERENCES `vendor` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=503 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Listing 5-4Storage Table
我们再次看到这个表和 vendor 表之间有一个外键，这样就不会删除任何供应商，也不会更改它的Id列，只要这个表中有引用它的行。
手工工具桌
handtool表用于存储每个非电动工具的信息。我们收集供应商、描述和尺寸。我们还使用了一个名为Type的枚举字段，它存储了工具的类型，这样我们就可以按类别对工具进行分组。这应该使得发出诸如“显示我所有的螺丝刀”这样的查询更加容易——特别是当一些类别的工具被放在不同的地方时。允许的类型可以在 SQL 语句中看到。
我们还存储供应商和存储位置的链接(的Id值)。因此，我们正在形成这些表之间的关系。清单 5-5 显示了针对handtool表的 SQL CREATE TABLE命令。
CREATE TABLE `garage_v1`.`handtool` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`VendorId` int(11) NOT NULL,
`Description` char(125) NOT NULL,
`Type` enum('Adjustable Wrench','Awl','Clamp','Crowbar','Drill Bit','File','Hammer','Knife','Level','Nutdriver','Pliers','Prybar','Router Bit','Ruler','Saw','Screwdriver','Socket','Socket Wrench','Wrench') DEFAULT NULL,
`ToolSize` char(50) DEFAULT NULL,
`PlaceId` int(11) NOT NULL,
PRIMARY KEY (`Id`),
KEY `VendorKey_idx` (`VendorId`),
KEY `PlaceKey_idx` (`PlaceId`),
CONSTRAINT `HandtoolPlace_FK` FOREIGN KEY (`PlaceId`) REFERENCES `place` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `HandtoolVendor_FK` FOREIGN KEY (`VendorId`) REFERENCES `vendor` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=2253 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Listing 5-5Handtool Table
在这个表中，我们有两个外键；一个用于存放地点(place)表中的Id，另一个用于vendor表中的Id。
电动工具台
powertool桌子与handtool桌子相似，只是这里存放的是由空气、电力(主电源)或电池驱动的工具。我们存储了描述和一个名为Typ e 的枚举字段，用于表示工具使用的电源类型。如果我们需要所有气动工具(空气)的列表，这可能会很方便。
我们还存储供应商和存储位置的链接(的Id值)。因此，我们正在形成这些表之间的关系。清单 5-6 显示了针对powertool表的 SQL CREATE TABLE命令。
CREATE TABLE `garage_v1`.`powertool` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`VendorId` int(11) NOT NULL,
`Description` char(125) DEFAULT NULL,
`Type` enum('Air','Corded','Cordless') DEFAULT NULL,
`PlaceId` int(11) NOT NULL,
PRIMARY KEY (`Id`),
KEY `VendorKey_idx` (`VendorId`),
KEY `PlaceKey_idx` (`PlaceId`),
CONSTRAINT `PowerToolPlace_FK` FOREIGN KEY (`PlaceId`) REFERENCES `place` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `PowertoolVendor_FK` FOREIGN KEY (`VendorId`) REFERENCES `vendor` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=3022 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Listing 5-6Powertool Table
我们在这个表中拥有与在handtool表中相同的外键；一个用于存放地点(place)表中的Id，另一个用于vendor表中的Id。
位置视图
最后，我们将使用一个视图。这个名为location的视图可以让我们快速获得存储位置和存储设备组合的查找表(视图)。我们可以用它在我们的示例应用中创建一个漂亮的下拉列表。图 5-4 显示了下拉列表的结果示例。我们将使用这个列表来创建对前面描述的handtool或powertool表中存储位置的引用。请注意，我们看到了一些表格的组合，以便于查看和选择适当的位置。这是现实世界中的应用如何利用数据库中的技巧来使用户界面更易于使用的另一个例子。
图 5-4
使用下拉列表中的位置视图
这个视图的 SQL 相当复杂，涉及到一个连接(在关系数据库中很常见),它将两个表中的信息结合起来。清单 5-7 显示了视图location的 SQL CREATE VIEW。请注意，视图重命名了一些列(使用AS关键字)，以便更容易区分每个表中的字段。这一点尤其重要，因为我们为每个表的主键使用了一个通用的“Id”。 3
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `garage_v1`.`location` AS
SELECT `garage_v1`.`storage`.`Id` AS `StorageId`,
`garage_v1`.`storage`.`Description` AS `StorageEquipment`,
`garage_v1`.`place`.`Id` AS `PlaceId`,
`garage_v1`.`place`.`Type` AS `Type`,
`garage_v1`.`place`.`Description` AS `Location`
FROM (`garage_v1`.`storage` JOIN `garage_v1`.`place` ON
((`garage_v1`.`storage`.`Id` = `garage_v1`.`place`.`StorageId`)))
Listing 5-7Location View
现在，让我们花点时间来讨论数据库代码设计。
代码设计
虽然你可能会在数据库设计中看到一些你会以不同方式做的事情 4 并且有几种“正确”的方式来做事情，但是代码设计将它们带到了一个更高的层次。也就是说，给两个程序员和一组要评审的代码，他们可能会花更多的时间仔细检查这种或那种编码方式的细微之处，而不是一开始就去写。这并不是说审查是无益的——它肯定是有益的——相反，这意味着总是有办法使用不同的机制、结构和哲学在代码中做同样的事情。
这适用于为示例应用设计的代码。选择使代码模块化，更容易阅读，最重要的是，演示(一种方式)构建关系数据库应用。因此，您将要遇到的可能不是您应该如何编写代码，但是它应该仍然可以以当前的形式用于演示目的。更具体地说，为示例应用做出的代码设计选择包括:
-
将 Flask 框架用于基于 web 的界面
-
使用一个类来表示数据库中的每个表
-
将单个类放在它自己的代码模块中
-
将所有数据库代码模块放在它自己的文件夹(名为 database)中
-
使用类封装到数据库服务器的连接
-
使用类模块测试每个表/视图类
-
使用从 shell 运行的代码模块来测试类模块
我们将在演示中看到这些约束中的大部分。如前所述，用户界面的描述包含在附录中。
我们在这一部分关注的代码包括我们需要与数据库交互的代码。因此，我们需要实现创建、读取、更新和删除(CRUD)操作的代码。我们还需要代码来帮助我们连接到数据库服务器。
表 5-1 显示了每个计划的数据库代码文件的代码模块、类名和描述。在下一节中，我们将看到如何使用 shell 来开发这些组件。
表 5-1
数据库代码模块
|代码模块
|
类别名
|
描述
|
| --- | --- | --- |
| garage_v1 | MyGarage | 实现与服务器和通用服务器接口的连接 |
| handtool.py | Handtool | 为手工工具表建模 |
| location.py | Location | 模拟位置视图 |
| organizer.py | Organizer | 模拟组织者表格 |
| place.py | Place | 模拟位置表 |
| powertool.py | Powertool | 为电动工具表建模 |
| storage.py | Storage | 为存储表建模 |
| vendor.py | Vendor | 对供应商表建模 |
当我们为示例应用编写代码以使用这些代码模块时，我们将使用MyGarage类连接到数据库服务器，并且当被请求时，使用与每个表相关联的类来调用每个表上的 CRUD 操作。唯一的例外是Location类只实现读取操作，因为它是一个视图，而视图被用作查找(读取)表。
现在我们已经了解了示例应用及其设计的目标，让我们开始为示例应用编写数据库代码。
设置和配置
以下演示的设置不需要安装任何东西，甚至不需要使用示例应用；相反，我们只需要加载示例数据库，因为我们将只使用数据库代码模块。虽然图像用于描述示例应用的某些方面，但在本章中并不一定需要它。同样，关于如何设置和使用完整的示例应用，请参见附录。
要安装示例数据库，我们必须从图书网站( https://www.apress.com/us/book/9781484250822 )下载示例源代码。选择本章的文件夹并下载文件。示例源代码包含一个名为database/garage_v1.sql的文件，该文件包含用于创建示例数据库并使用示例数据填充该数据库的 SQL 语句。
这个文件不仅发出CREATE DATABASE和CREATE TABLE命令，还包含使用INSERT SQL 命令的每个表的一小组数据。也就是说，它是一个典型的车库或车间中一套工具的库存。所以，你不必花宝贵的时间去想出描述、尺寸等。对于要使用的一组数据，已经为您做好了！
由于这个文件是一个 SQL 文件，我们将需要对 shell 使用--sql模式。幸运的是，我们可以使用选项来读取这个文件，导入(源)它，然后退出，如下所示。请记住，您必须指定文件的路径，或者从文件所在的目录执行 shell。
mysqlsh --uri root@localhost:3306 --sql -f garage_v1.sql
转到数据库文件夹，发出以下命令，告诉 shell 打开文件并执行语句。运行它不需要一分钟，因为我们是在批处理模式下运行的，完成后将退出 shell。清单 5-8 显示了运行这些命令的结果。如果您对文件中的命令感到好奇，可以随意打开它，看看 SQL 语句是如何编写的。您应该注意到这是一个使用mysqlpump服务器客户端应用的数据库转储。
小费
关于mysqlpump的更多信息，请参见 https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html 。
C:\Users\cbell\Documents\mygarage_v1>cd database
C:\Users\cbell\Documents\mygarage_v1\database>mysqlsh --uri root@localhost:3306 --sql -f garage_v1.sql
Records: 31 Duplicates: 0 Warnings: 0
Records: 6 Duplicates: 0 Warnings: 0
Records: 250 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 22 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 22 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 2 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
Listing 5-8Populating the Example Database (Windows 10)
既然我们已经创建并填充了数据库，我们就可以开始探索数据库代码模块了！
示范
当您从图书网站下载相同的源代码时，您可能已经意识到代码模块包含在下载中并且是完整的。因此，如果你是一个精明的 Python 程序员，你可能会想浏览或跳过这一章的其余部分。但是，您应该继续阅读，因为我们将看到如何使用 shell 来帮助开发这些模块的演示。也就是说，我们将使用 shell 开始为示例应用开发 Python 代码。
注意
本章完整的工作示例应用可以从本书的网站上下载。请参阅附录，了解如何设置您的机器来运行该应用。
如果你以前从未使用 Python 编程，不要担心，因为它是一种非常容易学习的语言。事实上，您只需要遵循本节中的示例，到最后您就会对代码在做什么(以及为什么)有一个坚实的理解。但是，如果您想学习 Python 或者需要更深入的 Python 知识，有几本关于这个主题的优秀书籍。下面我列出了几个我最喜欢的。Python 网站上的文档是一个很好的资源:python.org/doc/.
-
Pro Python ，第二版(Apress 2014)，J. Burton Browning，Marty Alchin
-
学习 Python ，第五版(奥赖利媒体 2013)，马克·卢茨
-
用 Python 自动化枯燥的东西:面向所有初学者的实用编程(无淀粉出版社，2015 年)，Al Sweigart
在接下来的部分中，我们将看到如何先创建最简单的类(Location ),然后再创建一些其他的类。正如你将看到的，它们遵循相同的设计模式/布局，所以一旦你看到一两个，其他的就很容易预测了。因此，我们将看到使用两个类的详细演练，为了简洁起见，其余的将被演示和呈现较少的细节。
如果您想跟进，请确保已经加载了示例数据库，并且 MySQL Shell 已经准备就绪。您可能还想使用代码或文本编辑器来编写代码模块。更重要的是，您应该创建一个名为database的文件夹，并从父文件夹启动 shell。
例如，您应该创建一个名为mygarage_v1,的文件夹，并在该文件夹中创建database文件夹。然后，我们将执行来自mygarage_v1\. Why? Because we will usePython import 指令的 shell，并使用文件夹名命名代码模块的路径(例如from database import Location)。我们还将创建单元测试，因此需要一个名为unittests的文件夹来存储测试文件。
让我们从MyGarage类开始。
我的车库班
该类旨在通过提供登录(连接)到服务器的机制和封装一些常见操作(如获取会话、当前数据库、检查到 MySQL 的连接是否活动、断开连接等)来简化 MySQL 服务器的工作。我们还将包括将 SQL 结果或 select 结果转换为 Python 列表(数组)的方法，以便于处理。表 5-2 显示了我们将为这个类创建的方法的完整列表，包括所需的参数(一些方法不需要它们)。
表 5-2
我的车库类方法
|方法
|
因素
|
描述
|
| --- | --- | --- |
| __init__() | mysqlx_sh(消歧义) | 构造函数-如果从 MySQL Shell 运行，则提供 mysqlx |
| connect() | 用户名，密码，主机，端口 | 连接到主机端口的 MySQL 服务器 |
| get_session() | | 返回会话以便在其他类中使用 |
| get_db() | | 返回数据库供其他类使用 |
| is_connected() | | 检查是否连接到服务器 |
| disconnect() | | 断开与服务器的连接 |
| make_rows() | sql_select | 为从选择结果的读取操作返回的行返回 Python 数组 |
| make_rows_sql() | sql_res，num_cols | 为从 sql 结果的读取操作返回的行返回 Python 数组 |
编写源代码
对于这个代码模块，我们不会使用 shell 来开发代码，因为这更像是一个方便的类，并且您已经看到了它的大多数方法的例子，或者至少是代码中使用的mysqlx模块中的方法。相反，我们将看到完整的代码，然后看看如何使用 shell 测试该类。该类维护当前会话，并隐藏了与服务器连接和断开连接的大部分机制。
有些人可能倾向于将连接机制转移到类中(您也可以这样做)，但是使用单独的类来管理意味着您不会复制任何代码，这总是首选的。
清单 5-9 显示了MyGarage类的完整代码。在您的文本或代码编辑器中打开一个新文件，并将该代码保存在名为garage_v1.py的文件的database文件夹中。花几分钟时间通读代码。即使你正在学习 Python，它也应该易于阅读和理解。
注意
为简洁起见，本章源代码清单中的注释和不必要的行已被删除。
from __future__ import print_function
# Attempt to import the mysqlx module. If unsuccessful, we are
# running from the shell and must pass mysqlx in to the class
# constructor.
try:
import mysqlx
except Exception:
print("Running from MySQL Shell. Provide mysqlx in constructor.")
class MyGarage(object):
def __init__(self, mysqlx_sh=None):
self.session = None
if mysqlx_sh:
self.mysqlx = mysqlx_sh
self.using_shell = True
else:
self.mysqlx = mysqlx
self.using_shell = False
def connect(self, username, passwd, host, port):
config = {
'user': username,
'password': passwd,
'host': host,
'port': port,
}
try:
self.session = self.mysqlx.get_session(∗∗config)
except Exception as err:
print("CONNECTION ERROR:", err)
self.session = None
raise
def get_session(self):
return self.session
def get_db(self):
return self.session.get_schema('garage_v1')
def is_connected(self):
return self.session and (self.session.is_open())
def disconnect(self):
try:
self.session.close()
except Exception as err:
print("WARNING: {0}".format(err))
def make_rows(self, sql_select):
cols = []
if self.using_shell:
cols = sql_select.get_column_names()
else:
for col in sql_select.columns:
cols.append(col.get_column_name())
rows = []
for row in sql_select.fetch_all():
row_item = []
for col in cols:
if self.using_shell:
row_item.append("{0}".format(row.get_field(col)))
else:
row_item.append("{0}".format(row[col]))
rows.append(row_item)
return rows
@staticmethod
def make_rows_sql(sql_res, num_cols):
rows = []
all_rows = sql_res.fetch_all()
for row in all_rows:
row_item = []
for col in range(0, num_cols):
row_item.append("{0}".format(row[col]))
rows.append(row_item)
return rows
def get_last_insert_id(self):
return self.get_session().sql(
"SELECT 6042780").execute().fetch_one()
Listing 5-9
garage_v1 Code
注意import行。这被放在一个try … except块中，因为当从 shell 中使用代码模块时，shell 并不直接公开mysqlx模块(它是内置模块之一)。相反，我们可以在构造函数中提供内置mysqlx模块的实例。
实际上，__init__()接受一个参数，mysql_sh，我们可以用它从 shell 中运行代码。这是让您的代码既可以在 shell 中使用，也可以交互使用(在应用中)的一个好方法。
还要注意，我们使用了一个变量self.using_shell来存储我们是否在使用 shell。这在make_rows∫方法中是需要的，因为 Shell 中的mysqlx模块与连接器中提供的mysqlx模块略有不同。参见下面的侧栏，了解为什么会这样。
Shell 和连接器的差异
当您进入更高级的应用时，您会注意到 MySQL Shell 中使用的mysqlx模块与 MySQL 连接器(Connector/Python、Connector/J 等)中使用的模块有所不同。).这些差异的原因主要是因为希望保持模块中方法的操作或机制在不同语言之间是相同的。由于连接器支持的语言很多，标准化行为的尝试导致了 shell 如何实现相同方法的一些细微差异。幸运的是，差异很小，很容易纠正。
现在我们已经写好了源代码，让我们使用 MySQL Shell 来测试这个类。
测试类
在我们开始测试这个类之前，我们必须设置 Python path 变量(PYTHONPATH)来包含我们想要运行测试的文件夹。这是因为我们使用的模块没有安装在系统级，而是位于与我们测试的代码相关的文件夹中。在 Windows 中，可以使用以下命令将执行路径添加到 Python 路径中。
C:\Users\cbell\Documents\my_garage_v1> set PYTHONPATH=%PYTHONPATH%;c:\users\cbell\Documents\mygarage_v1
或者，在 Linux 和 macOS 上，您可以使用这个命令来设置 Python 路径。
export PYTHONPATH=$(pwd);$PYTHONPATH
现在我们可以运行 shell 了。为此，我们将使用--py选项在 Python 模式下启动。让我们在课堂上练习一些方法。我们可以尝试所有的方法，除了make_rows()方法。我们稍后会看到这些。清单 5-10 展示了如何在 shell 中导入类，初始化(创建)一个名为mygarage的类实例，然后连接connect()，并执行一些方法。最后，我们调用disconnect()来关闭与服务器的连接。
C:\Users\cbell\Documents\my_garage_v1> mysqlsh --py
MySQL Py > from database.garage_v1 import MyGarage
Running from MySQL Shell. Provide mysqlx in constructor.
MySQL Py > myg = MyGarage(mysqlx)
MySQL Py > myg.connect('root', 'SECRET', 'localhost', 33060)
MySQL Py > db = myg.get_db()
MySQL Py > db
<Schema:garage_v1>
MySQL Py > s = myg.get_session()
MySQL Py > s
<Session:root@localhost:33060>
MySQL Py > myg.is_connected()
true
MySQL Py > myg.disconnect()
MySQL Py > myg.is_connected()
false
Listing 5-10Testing MyGarage using MySQL Shell
注意这里我们导入了模块，然后在内置的mysqlx模块中创建了一个类实例。然后，我们连接到服务器(确保使用您系统的密码)，检索数据库并打印它(通过将变量放在一行上并按下 ENTER ，为会话做同样的事情，然后最后测试is_connected()和disconnect()方法。
在执行过程中，我们发出 print()语句来打印方法调用的一些结果。shell 的一个很好的特性是，如果您打印一个类实例变量，它会显示该变量的类名。这是你可以用来帮助你学习课程和选择正确方法的另一个技巧。这将节省你继续学习 X DevAPI 的时间。
警告
该类使用mysqlx模块，这需要 X 协议连接。确保使用 X 协议端口(默认为 33060)。
如果您想将这些命令保存在一个文件中，您可以。事实上，这是测试代码类(单元)的手工单元测试的一种形式。 5 为了在批处理模式下更容易阅读，我们将添加一些print()语句。要运行这个测试，创建一个名为unittests的文件夹，并将名为garage_v1_test.py的文件放在那里。清单 5-11 显示了文件的完整清单。我们还添加了提示用户 Id 和密码的代码，这比硬编码在文件中要好得多！
from getpass import getpass
from database.garage_v1 import MyGarage
print("MyGarage Class Unit test")
mygarage = MyGarage(mysqlx)
user = raw_input("User: ")
passwd = getpass("Password: ")
print("Connecting...")
mygarage.connect(user, passwd, 'localhost', 33060)
print("Getting the database...")
database = mygarage.get_db()
print(database)
print("Getting the session...")
session = mygarage.get_session()
print(session)
print("Connected?")
print(mygarage.is_connected())
print("Disconnecting...")
mygarage.disconnect()
print("Connected?")
print(mygarage.is_connected())
Listing 5-11
garage_v1_test.py
稍后，如果您想执行它，可以使用下面的命令。记得从您之前创建的文件夹(mygarage_v1)中运行这个。这是一个很好的方法，可以确保在不对整个应用进行排序的情况下测试部分代码。清单 5-12 显示了测试代码的执行。
> mysqlsh --py -f unittests\garage_v1_test.py
Running from MySQL Shell. Provide mysqlx in constructor.
MyGarage Class Unit test
User: root
Password:
Connecting...
Getting the database...
<Schema:garage_v1>
Getting the session...
<Session:root@localhost:33060>
Connected?
True
Disconnecting...
Connected?
False
Listing 5-12Running the garage_v1_test Unit Test
现在，让我们看一下最简单的类，它们对一个数据库表建模，或者在本例中，对一个视图建模。
位置类别
这个类是location视图的一个模型。回想一下，location视图执行连接，将所有存储位置和存储设备的列表合并到一个列表中，该列表可以用作查找表。因此，这个类只需要实现 read CRUD 操作。
在下一节中，我们将演示如何使用 MySQL Shell 编写该类的源代码。
编写源代码
使用 shell 编写代码的方法之一是使用交互式会话，一次编写一行代码。这允许您尝试如何组织代码，更重要的是，学习使用哪些方法。
对于这个类，我们只需要 read 操作来填充手工具、电动工具和管理器表的详细信息表单中的下拉列表。因为我们使用database\garage_v1.py代码模块进行数据库连接，所以我们需要首先初始化这个类。一旦我们登录并拥有了一个MyGarage类的实例，我们就可以用它来获取表并读取表中的行。清单 5-13 显示了可以完成这些步骤的代码。
from database.garage_v1 import MyGarage
LOCATION_READ_COLS = ['PlaceId', 'StorageEquipment', 'Type', 'Location']
LOCATION_READ_BRIEF_COLS = ['StorageEquipment', 'Type', 'Location']
mygarage = MyGarage(mysqlx)
mygarage.connect('root', 'SECRET', 'localhost', 33060)
schema = mygarage.get_db()
table = schema.get_table('location')
sql_res = table.select(LOCATION_READ_COLS).order_by(∗LOCATION_READ_BRIEF_COLS).limit(5).execute()
rows = mygarage.make_rows(sql_res)
print(rows)
Listing 5-13
Primitive Code
注意这里我们使用常量来设置列名。这使得select()方法中的代码更好一些，尤其是如果您使用需要列名列表的其他子句。在本例中，我们还使用了limit()方法，该方法将输出限制在前五行，这使得代码的执行很简短。清单 5-14 展示了这个代码在 shell 中的执行。
MySQL Py > from database.garage_v1 import MyGarage
MySQL Py > LOCATION_READ_COLS = ['PlaceId', 'StorageEquipment', 'Type', 'Location']
MySQL Py > LOCATION_READ_BRIEF_COLS = ['StorageEquipment', 'Type', 'Location']
MySQL Py > mygarage = MyGarage(mysqlx)
MySQL Py > mygarage.connect('root', 'SECRET', 'localhost', 33060)
MySQL Py > schema = mygarage.get_db()
MySQL Py > table = schema.get_table('location')
MySQL Py > sql_res = table.select(LOCATION_READ_COLS).order_by(∗LOCATION_READ_BRIEF_COLS).limit(5).execute()
MySQL Py > rows = mygarage.make_rows(sql_res)
MySQL Py > print(rows)
[['1007', 'Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Bottom'], ['1001', 'Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Left 1'], ['1002', 'Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Left 2'], ['1003', 'Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Left 3'], ['1004', 'Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Right 1']]
Listing 5-14Executing the Primitive Code
虽然这些行的输出不是以易读的方式打印出来的(实际上并不需要)，但是如果您想查看细节，可以添加代码来实现这一点，但是打印原始 Python 列表就足以看到返回了 5 行。
现在，让我们从前面的示例代码中构造一个类。我们简单地应用编码构造来创建一个只有一个名为read()的方法的类。我们还使用 mysqlx 对象的实例编写了一个构造函数来获取该表。清单 5-15 显示了修改后的代码。
虽然本章中的清单显示了如何键入创建类所需的代码，但是有一点您必须遵循的过程。具体来说，您必须在类声明及其方法之间输入带有空格的行。这是因为当您在空白行上按下 ENTER 时，shell 将评估类代码。这同样适用于任何多行代码块，包括字典、列表等。
警告
如果您遇到关于意外缩进的错误，即使代码是正确的，也可以尝试使用带有空格的行来分隔方法。请注意，您可以在批处理模式下执行文件，而不需要带有空格的行。
from database.garage_v1 import MyGarage
LOCATION_READ_COLS = ['PlaceId', 'StorageEquipment', 'Type', 'Location']
LOCATION_READ_BRIEF_COLS = ['StorageEquipment', 'Type', 'Location']
class Location(object):
def __init__(self, myg):
self.table = myg.get_db().get_table('location')
def read(self):
sql_res = self.table.select(LOCATION_READ_COLS).order_by(
∗LOCATION_READ_BRIEF_COLS).limit(5).execute()
return(mygarage.make_rows(sql_res))
mygarage = MyGarage(mysqlx)
mygarage.connect('root', 'SECRET', 'localhost', 33060)
location = Location(mygarage)
rows = location.read()
print(rows)
Listing 5-15Location Class Primitive
请注意，我们创建了一个带有构造函数的类，该构造函数设置了一个包含该类的类变量。这是从 X DevAPI 运行方法以实现 CRUD 操作所必需的。记住，我们已经有了来自MyGarage类实例(myg)的连接，并且它被传递到构造函数的 Location 类中。
那只是班级代码。我们还需要添加代码来执行或测试该类。我们在课后补充。当我们将这段代码放在一个文件(名为listing5-15.py)中并执行它时，shell 将按照编写的方式创建类并执行类后面的代码行。例如，我们使用以下命令执行清单，该命令告诉 shell 打开文件，并以 Python 模式一次运行一行文件的内容。
$ mysqlsh --py -f listing5-15.py
现在，当我们在 shell 中执行该代码时，我们得到与之前相同的输出，如清单 5-16 所示。
MySQL Py > from database.garage_v1 import MyGarage
Running from MySQL Shell. Provide mysqlx in constructor.
MySQL Py > LOCATION_READ_COLS = ['PlaceId', 'StorageEquipment', 'Type', 'Location']
MySQL Py > LOCATION_READ_BRIEF_COLS = ['StorageEquipment', 'Type', 'Location']
MySQL Py > class Location(object):
-> def __init__(self, myg):
-> self.table = myg.get_db().get_table('location')
->
-> def read(self):
-> sql_res = self.table.select(LOCATION_READ_COLS).order_by(∗LOCATION_READ_BRIEF_COLS).limit(5).execute()
-> return(mygarage.make_rows(sql_res))
->
MySQL Py > mygarage = MyGarage(mysqlx)
MySQL Py > mygarage.connect('root', 'SECRET', 'localhost', 33060)
MySQL Py > location = Location(mygarage)
MySQL Py > rows = location.read()
MySQL Py > print(rows)
[['1007', 'Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Bottom'], ['1001', 'Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Left 1'], ['1002', 'Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Left 2'], ['1003', 'Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Left 3'], ['1004', 'Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Right 1']]
Listing 5-16Executing the Location Class Primitive
正如你所看到的，我们不仅能够编写这个类，我们还在最后测试了这个类。这是创建类模块的一种常见且简单的方法。也就是说，shell 使动态编写代码成为可能，而不是在 Python 代码文件中从头开始编写代码，然后再执行它们(很多人都这样做)。这与 Python 解释器的工作方式非常相似。区别在于 shell 使得直接使用 X DevAPI 成为可能。
一旦您完善了您的类，您就可以创建适当的代码模块来存储该类。在示例应用中，这段代码以类的名称放在数据库文件夹中。例如，Location类存储在名为database/location.py的文件中。清单 5-17 中显示了Location类的完整代码。
class Location(object):
"""Location class
This class encapsulates the location view permitting read operations
on the data.
"""
def __init__(self, mygarage):
"""Constructor"""
self.mygarage = mygarage
self.schema = mygarage.get_db()
self.tbl = self.schema.get_table('location')
def read(self):
"""Read data from the table"""
sql_res = self.tbl.select(LOCATION_READ_COLS).order_by(
∗LOCATION_READ_BRIEF_COLS).execute()
return self.mygarage.make_rows(sql_res)
Listing 5-17Completed Location Class Module (database/location.py)
请注意，完整的代码略有不同，我们添加了注释，存储了MyGarage实例，检索了模式(数据库),并将两者都存储在类变量中。也就是说，表的检索分两步完成，而不是链接get_schema()和get_table()方法。这种简化有时会使代码更容易阅读。
现在我们已经编写了代码模块，让我们编写一个单元测试来测试这个类。
测试类
我们已经看到了如何测试清单 5-16 中的类的原型。因此，我们所要做的就是执行那些相同的行，只添加 Location 类的 import 语句。清单 5-18 显示了这个类的完整测试代码。注意，我们为行的 print 语句添加了[:5]。这将打印限制在列表中的前五个项目(行)。
from database.garage_v1 import MyGarage
from database.location import Location
mygarage = MyGarage(mysqlx)
mygarage.connect('root', 'SECRET', 'localhost', 33060)
location = Location(mygarage)
rows = location.read()
print(rows[:5])
Listing 5-18Test Code for the Location Class
我们可以将这段代码放在一个文件中并执行它，但是让我们使用 shell 来代替。清单 5-19 显示了代码在 shell 中的执行。
MySQL Py > from database.garage_v1 import MyGarage
Running from MySQL Shell. Provide mysqlx in constructor.
MySQL Py > from database.location import Location
MySQL Py > mygarage = MyGarage(mysqlx)
MySQL Py > mygarage.connect('root', 'SECRET', 'localhost', 33060)
MySQL Py > location = Location(mygarage)
MySQL Py > rows = location.read()
MySQL Py > print(rows[:5])
[['Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Bottom'], ['Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Left 1'], ['Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Left 2'], ['Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Left 3'], ['Kobalt 3000 Steel Rolling Tool Cabinet (Black)', 'Drawer', 'Right 1']]
Listing 5-19Executing the Location Class Test Code
如果你认为我们可能想让测试代码更正式、更容易使用，那你就对了。我们将在后面的章节中探讨这一点。但是首先，让我们看看如何为 vendor 表创建类。
供应商类别
Vendor类负责封装vendor表上的创建、读取、更新和删除(CRUD)操作。因此，该类将按名称实现这些方法。事实上，其他类将实现相同的方法。这样，我们在示例应用数据库代码中就有了一致性。
在这一节中，我们将详细检查Vendor类代码，包括如何使用 shell 构建类，以及如何在 shell 中测试类。现在我们已经看到了一个更小的例子(只有 read 操作)，这个类的代码至少在外观上是熟悉的，但是您将会看到更详细的内容。
我们将看到一个详细的演示，展示如何从第一个操作——create 开始，以增量方式(一次一个方法)为类编写代码。我们还将看到测试代码被添加到每个示例中，但是为了简洁起见，我们将只显示为我们关注的方法执行的代码(每个 CRUD 操作)。
创造
创建操作是我们在表中创建新行的地方。因此，我们需要提供该行的所有数据。在这种情况下，它包括名称、URL 和源字段。回想一下，这允许我们给供应商一个我们认识的名称(例如，Kobalt，Craftsman)，一个到供应商网站的 URL，以及一个 sources 字段，它是一个描述我们可以为该供应商购买产品的商店的文本字段。
像Location类一样，我们需要添加一些指令来开始包含MyGarage类的导入和一个包含列名的列表。该列表纯粹是一种簿记方法，它允许我们更改列或者为不同的 SQL 操作使用替代的列定义。我们将在数据库代码的其他类中更详细地使用这种技术。
我们还包括了类定义，就像我们之前做的那样，适当地命名类，并添加一个构造函数方法，该方法接受MyGarage实例，将其存储在类变量中以备后用，获取模式并将其保存到类变量中，最后获取表类实例。
create()方法只是以字典的形式接受来自调用者的值，其中列名是键，然后发出insert()方法，通过链接values()方法传入列名和列值的列表。清单 5-20 显示了Vendor类的初始版本。花点时间通读一下类定义。
注意在清单的底部是额外的代码来创建一个Vendor类的实例，创建一个测试值字典，然后调用create()方法。最后，我们使用MyGarage方法get_6042780检索最后一个自动增量值并打印出来。
from database.garage_v1 import MyGarage
VENDOR_COLS_CREATE = ['Name', 'URL', 'Sources']
class Vendor(object):
def __init__(self, myg):
self.mygarage = mygarage
self.schema = mygarage.get_db()
self.tbl = self.schema.get_table('vendor')
def create(self, vendor_data):
vendor_name = vendor_data.get("Name", None)
link = vendor_data.get("URL", None)
sources = vendor_data.get("Sources", None)
self.tbl.insert(VENDOR_COLS_CREATE).values(
vendor_name, link, sources).execute()
mygarage = MyGarage(mysqlx)
mygarage.connect('root', 'SECRET', 'localhost', 33060)
vendor = Vendor(mygarage)
vendor_data = {
"Name": "ACME Bolt Company",
"URL": "www.acme.org",
"Sources": "looney toons"
}
vendor.create(vendor_data)
last_id = mygarage.get_6042780[0]
Listing 5-20Vendor Class create() Method
当我们在 shell 中执行这段代码时，我们将看到每一行代码都经过了验证。不要忘记使用一个上面有空格的空行来分隔类方法，并使用一个上面没有空格的空行来终止类和字典定义。请参阅前面的注释，了解我们为什么需要这样做。
让我们专注于测试代码。这里，我们简单地创建了一个新行，并检索了它的Id列的值，如下所示(为了简洁起见，省略了演示的其余部分)。
...
MySQL Py > print("Last insert id = {0}".format(last_id))
Last insert id = 177
然而，这不是很多细节，是吗？我们实际上无法判断该行是否被插入——只知道我们获得了最后一个插入 Id。因此，让我们实现读操作并使用它来验证创建。
阅读
读取操作需要两件事:它需要能够读取表中的所有行并返回它们，就像我们对Location类所做的那样，但是它也需要用于读取单个行并返回该数据。这是因为我们要么读取列表视图的所有行，要么在查看单行时检索该行的值。
为此，我们将使用一个名为vendor_id的参数，默认设置为None。这允许我们测试这个参数，如果是None，检索所有行，或者如果有值，检索单个行。
读操作还有另一个方面。回想一下 create 操作使用了一个列表来包含列名。在这种情况下，我们不需要Id字段，因为创建操作(插入)将导致 MySQL 填充该值。然而，为了读取一行或所有行，我们需要获取Id列。因此，我们构建了另一个列表来为 select()方法调用添加 Id 列。
让我们也添加一些错误处理代码。在这种情况下，我们将使用一个try…except块来捕捉在insert()和select()期间的任何错误。我们还添加了一项技术来返回一个布尔值，告诉调用者操作是否有效，如果无效，则向用户显示一条错误消息。我们通过返回一个元组来做到这一点，比如(True, None)表示成功，或者(False, <error>)表示错误。这将有助于我们以后如果有问题。清单 5-21 显示了添加了read()方法并更新了测试代码的类。
from database.garage_v1 import MyGarage
VENDOR_COLS_CREATE = ['Name', 'URL', 'Sources']
VENDOR_COLS = []
VENDOR_COLS.extend(VENDOR_COLS_CREATE)
VENDOR_COLS.insert(0, 'Id') # Add the Id to the list
class Vendor(object):
def __init__(self, mygarage):
self.mygarage = mygarage
self.schema = mygarage.get_db()
self.tbl = self.schema.get_table('vendor')
def create(self, vendor_data):
vendor_name = vendor_data.get("Name", None)
link = vendor_data.get("URL", None)
sources = vendor_data.get("Sources", None)
assert vendor_name, "You must supply a name for the vendor."
try:
self.tbl.insert(VENDOR_COLS_CREATE).values(
vendor_name, link, sources).execute()
except Exception as err:
print("ERROR: Cannot add vendor: {0}".format(err))
return (False, err)
return (True, None)
def read(self, vendor_id=None):
if not vendor_id:
# return all vendors
sql_res = self.tbl.select(VENDOR_COLS).order_by("Name").execute()
else:
# return specific vendor
sql_res = self.tbl.select(VENDOR_COLS).where(
"Id = '{0}'".format(vendor_id)).execute()
return self.mygarage.make_rows(sql_res)
mygarage = MyGarage(mysqlx)
mygarage.connect('root', 'SECRET', 'localhost', 33060)
vendor = Vendor(mygarage)
vendor_data = {
"Name": "ACME Bolt Company",
"URL": "www.acme.org",
"Sources": "looney toons"
}
vendor.create(vendor_data)
last_id = mygarage.get_6042780[0]
print("Last insert id = {0}".format(last_id))
rows = vendor.read(last_id)
print("{0}".format(", ".join(rows[0])))
rows = vendor.read()
print(rows[:5])
Listing 5-21Adding the read() Method
注意，为了简洁，最后一行代码只打印了返回的前五行。像以前一样，我们将省略该类的条目，并将重点放在测试该类的行上。下面显示了为测试create()和read()方法而执行的代码行。我们通过使用在create()之后返回的最后一个 Id 来测试读取单个行，然后执行一个read()来获取所有行。
...
MySQL Py > print("Last insert id = {0}".format(last_id))
Last insert id = 178
MySQL Py > rows = vendor.read(last_id)
MySQL Py > print("{0}".format(", ".join(rows[0])))
178, ACME Bolt Company, www.acme.org, looney toons
MySQL Py > rows = vendor.read()
MySQL Py > print(rows[:5])
[['178', 'ACME Bolt Company', 'www.acme.org', 'looney toons'], ['175', 'ACME Bolt Company', 'www.acme.org', 'looney toons'], ['172', 'ACME Bolt Company', 'www.acme.org', 'looney toons'], ['171', 'ACME Bolt Company', 'www.acme.org', 'looney toons'], ['170', 'ACME Bolt Company', 'www.acme.org', 'looney toons']]
同样，这些行的打印并不漂亮，但出于开发目的，它确实显示了create()和read()正在工作。酷！现在，让我们添加更新操作。
更新
更新操作类似于创建操作，因为我们需要该行的所有数据。但是与创建操作不同，我们需要Id列，以便更新正确的行。精明的开发人员会在发布更新之前添加一个验证列的步骤，以便只更新那些发生变化的列，但是我们将采用一种更简单的方法，提供所有的列，并让数据库进行排序。
然而，由于更新操作必须有Id列，我们将添加一个断言来确保调用者为where()方法提供该信息。否则，更新就太危险了！
我们还在update()周围使用了一个try...except块来捕捉任何错误。清单 5-22 显示了添加了update()方法的类，为了简洁起见，省略了构造函数、create()和read()方法。注意我们如何使用一个for循环来设置列的值。
from database.garage_v1 import MyGarage
VENDOR_COLS_CREATE = ['Name', 'URL', 'Sources']
VENDOR_COLS = []
VENDOR_COLS.extend(VENDOR_COLS_CREATE)
VENDOR_COLS.insert(0, 'Id') # Add the Id to the list
class Vendor(object):
...
def update(self, vendor_data):
vendor_id = vendor_data.get("VendorId", None)
vendor_name = vendor_data.get("Name", None)
link = vendor_data.get("URL", None)
sources = vendor_data.get("Sources", None)
assert vendor_id, "You must supply an Id to update the vendor."
field_value_list = [('Name', vendor_name),
('URL', link), ('Sources', sources)]
try:
tbl_update = self.tbl.update()
for field_value in field_value_list:
tbl_update.set(field_value[0], field_value[1])
tbl_update.where("Id = '{0}'".format(vendor_id)).execute()
except Exception as err:
print("ERROR: Cannot update vendor: {0}".format(err))
return (False, err)
return (True, None)
mygarage = MyGarage(mysqlx)
mygarage.connect('root', 'SECRET', 'localhost', 33060)
vendor = Vendor(mygarage)
vendor_data = {
"Name": "ACME Bolt Company",
"URL": "www.acme.org",
"Sources": "looney toons"
}
vendor.create(vendor_data)
last_id = mygarage.get_6042780[0]
print("Last insert id = {0}".format(last_id))
rows = vendor.read(last_id)
print("{0}".format(", ".join(rows[0])))
rows = vendor.read()
print(rows[:5])
vendor_data = {
"VendorId": last_id,
"Name": "ACME Nut Company",
"URL": "www.acme.org",
"Sources": "looney toons"
}
vendor.update(vendor_data)
rows = vendor.read(last_id)
print("{0}".format(", ".join(rows[0])))
Listing 5-22Adding the update() Method
更新操作的测试代码简单地使用与创建操作相同的字典，只是我们更改了一些值来测试更新。下面显示了从创建操作后的读取开始的测试执行的输出。注意 update()确实改变了我们之前创建的那一行的值。
...
MySQL Py > rows = vendor.read(last_id)
MySQL Py > print("{0}".format(", ".join(rows[0])))
179, ACME Bolt Company, www.acme.org, looney toons
MySQL Py > rows = vendor.read()
...
MySQL Py > vendor_data = {
-> "VendorId": last_id,
-> "Name": "ACME Nut Company",
-> "URL": "www.acme.org",
-> "Sources": "looney toons"
-> }
MySQL Py > vendor.update(vendor_data)
MySQL Py > rows = vendor.read(last_id)
MySQL Py > print("{0}".format(", ".join(rows[0])))
179, ACME Nut Company, www.acme.org, looney toons
现在，让我们添加最后一个操作—删除。
删除
删除操作只是删除表中的一行。我们需要做的就是 Id 列。因此，delete()方法被编写为使用vendor_id作为参数测试，以确保提供了一个参数，然后在表上发出删除操作。
像其他方法一样，我们使用try…except块并返回一个元组来报告操作是否成功。清单 5-23 显示了添加了delete()方法的类，为了简洁起见，省略了构造函数、create()、read()和update()方法。
from database.garage_v1 import MyGarage
VENDOR_COLS_CREATE = ['Name', 'URL', 'Sources']
VENDOR_COLS = []
VENDOR_COLS.extend(VENDOR_COLS_CREATE)
VENDOR_COLS.insert(0, 'Id') # Add the Id to the list
class Vendor(object):
...
def delete(self, vendor_id=None):
"""Delete a row from the table"""
assert vendor_id, "You must supply an Id to delete the vendor."
try:
self.tbl.delete().where("Id = '{0}'".format(vendor_id)).execute()
except Exception as err:
print("ERROR: Cannot delete vendor: {0}".format(err))
return (False, err)
return (True, None)
mygarage = MyGarage(mysqlx)
mygarage.connect('root', 'secret', 'localhost', 33060)
vendor = Vendor(mygarage)
vendor_data = {
"Name": "ACME Bolt Company",
"URL": "www.acme.org",
"Sources": "looney toons"
}
vendor.create(vendor_data)
last_id = mygarage.get_6042780[0]
print("Last insert id = {0}".format(last_id))
rows = vendor.read(last_id)
print("{0}".format(", ".join(rows[0])))
rows = vendor.read()
print(rows[:5])
vendor_data = {
"VendorId": last_id,
"Name": "ACME Nut Company",
"URL": "www.acme.org",
"Sources": "looney toons"
}
vendor.update(vendor_data)
rows = vendor.read(last_id)
print("{0}".format(", ".join(rows[0])))
vendor.delete(last_id)
rows = vendor.read(last_id)
if not rows:
print("Record not found.")
Listing 5-23Adding the delete() Method
好了，这就是这个类的完整代码。下面显示了调用update()后的执行。在这里，我们尝试删除我们插入并稍后更新的行，然后尝试从表中读取它。如果没有返回行，则没有找到该行，因此我们显示删除操作成功。
...
MySQL Py > rows = vendor.read(last_id)
MySQL Py > print("{0}".format(", ".join(rows[0])))
180, ACME Nut Company, www.acme.org, looney toons
MySQL Py > vendor.delete(last_id)
MySQL Py > rows = vendor.read(last_id)
MySQL Py > if not rows:
-> print("Record not found.")
Record not found.
既然我们已经详细了解了如何使用 shell 来创建数据库表(和视图)的类，那么让我们来看看其余每个类的概述。每个类都以与Vendor类相同的方式编写，并注明了该类的具体实现细节。正如你将看到的，一致性是我们的朋友。
手工工具类
Handtool类封装了handtool表的 CRUD 操作。它使用与其他类相同的类结构和方法。它在复杂性上与Vendor类有三个主要的不同。
首先，handtool表有几个不能为空的字段，所以插入和更新操作有一些额外的断言，如下所示。
assert tool_size, "You must specify a toolsize for the handtool."
assert handtool_type, "You must specify a type for the handtool."
assert description, "You must supply a description for the handtool."
assert place_id, "You must supply an Id for the handtool."
其次，该表有一个枚举字段，我们用代码中的另一个列表来表示它，如下所示。这允许我们将名称映射到枚举值。这可能看起来很奇怪，因为这是一个值重复的元组列表。那是故意的。映射可以这样定义，这样键(元组中的第一个值)就被用来“映射”到第二个值(或者只是值)。因为我们是优秀的数据库开发人员，我们不为枚举的字段值编码数值，我们必须重复该值。
HANDTOOL_TYPES = [
('Adjustable Wrench', 'Adjustable Wrench'), ('Awl', 'Awl'),
('Clamp', 'Clamp'), ('Crowbar', 'Crowbar'), ('Drill Bit', 'Drill Bit'),
('File', 'File'), ('Hammer', 'Hammer'), ('Knife', 'Knife'), ('Level', 'Level'),
('Nutdriver', 'Nutdriver'), ('Pliers', 'Pliers'), ('Prybar', 'Prybar'),
('Router Bit', 'Router Bit'), ('Ruler', 'Ruler'), ('Saw', 'Saw'),
('Screwdriver', 'Screwdriver'), ('Socket', 'Socket'),
('Socket Wrench', 'Socket Wrench'), ('Wrench', 'Wrench'),
]
第三，因为handtool表有几个字段，所以用于查找或浏览操作的表中的行列表不需要所有的字段。我们也希望看到外键所指向的值。因此，我们使用如下所示的 SQL SELECT查询来代替read()方法的 read all rows 特性。我们将它存储在一个常量中，以便于阅读和修改。
HANDTOOL_READ_LIST = (
"SELECT handtool.Id, handtool.type, handtool.description, "
"handtool.toolsize, storage.description as StorageEquipment, "
"place.type as locationtype, place.description as location FROM garage_v1.handtool "
"JOIN garage_v1.place ON "
"handtool.placeid = place.id JOIN garage_v1.storage ON place.storageid = storage.id "
"ORDER BY handtool.type, handtool.description"
)
该查询用于读取操作，如下所示。注意，我们使用会话对象的 sql()方法，而不是 select()方法来发出查询。因此，我们还在构造函数中捕获会话对象实例。
if not handtool_id:
# return all handtools - uses a JOIN so we have to use the sql()
# method instead of select, but we arrive at the same results
sql_res = self.session.sql(HANDTOOL_READ_LIST).execute()
return self.mygarage.make_rows_sql(sql_res, len(HANDTOOL_READ_COLS))
else:
# return specific handtool
sql_res = self.tbl.select(HANDTOOL_COLS).where(
"Id = '{0}'".format(handtool_id)).execute()
return self.mygarage.make_rows(sql_res)
你可以在database/handtool.py代码模块中找到这个代码。花一些时间来研究这些变化，亲自看看它们是如何结合在一起的。
组织者类
Organizer类封装了organizer表的 CRUD 操作。它使用与其他类相同的类结构和方法。它在复杂程度上不同于Vendor级，比如Handtool级；它需要枚举列的映射和读取操作的 SQL SELECT语句，如下所示。否则，代码与Vendor类的模式相同。
ORGANIZER_TYPES = [('Bin', 'Bin'), ('Box', 'Box'), ('Case', 'Case')]
...
ORGANIZER_READ_LIST = (
"SELECT organizer.Id, organizer.Type, organizer.Description, "
"storage.description as StorageEquipment, place.type as LocationType, "
"place.description as Location FROM garage_v1.organizer JOIN "
"garage_v1.place ON organizer.placeid = place.ID JOIN "
"garage_v1.storage ON place.storageid = storage.id "
"ORDER BY Type, organizer.description"
)
处级
Place类封装了place表的 CRUD 操作。它使用与其他类相同的类结构和方法。它在复杂程度上不同于Vendor级，比如Handtool级；它需要枚举列的映射和读取操作的 SQL SELECT语句，如下所示。否则，代码与Vendor类的模式相同。
PLACE_TYPES = [('Drawer', 'Drawer'), ('Shelf', 'Shelf')]
...
PLACE_READ_LIST = (
"SELECT place.Id, storage.description as StorageEquipment, place.Type as LocationType, "
"place.Description as Location FROM garage_v1.place JOIN "
"garage_v1.storage ON place.StorageId = storage.ID ORDER BY "
"StorageEquipment, LocationType, Location"
)
电动工具类
Powertool类封装了powertool表的 CRUD 操作。它使用与其他类相同的类结构和方法。它在复杂程度上不同于Vendor级，比如Handtool级；它需要枚举列的映射和读取操作的 SQL SELECT语句，如下所示。否则，代码与Vendor类的模式相同。
POWERTOOL_TYPES = [('Corded', 'Corded'), ('Cordless', 'Cordless'), ('Air', 'Air')]
...
POWERTOOL_READ_LIST = (
"SELECT powertool.Id, powertool.type, powertool.description, "
"storage.description as StorageEquipment, place.type as locationtype, "
"place.description as location FROM garage_v1.powertool JOIN garage_v1.place "
"ON powertool.placeid = place.id JOIN garage_v1.storage ON "
"place.storageid = storage.id ORDER BY powertool.type, powertool.description"
)
存储类
Storage类封装了storage表的 CRUD 操作。它使用与其他类相同的类结构和方法。它在复杂程度上不同于Vendor级，比如Handtool级；它需要枚举列的映射和读取操作的 SQL SELECT语句，如下所示。它在返回所有行的读操作上也有所不同，返回一个较小的列列表。这用于在用户界面中显示所有的存储设备。否则，代码与Vendor类的模式相同。
STORAGE_TYPES = [
('Cabinet', 'Cabinet'), ('Shelving', 'Shelving'),
('Toolchest', 'Toolchest'), ('Workbench', 'Workbench')
]
STORAGE_COLS_BRIEF = [
'storage.Id', 'Type', 'Description', 'Location'
]
...
STORAGE_READ_LIST = (
"SELECT storage.Id, vendor.name, Type, description, Location FROM "
"garage_v1.storage JOIN garage_v1.vendor ON storage.VendorId = vendor.Id "
"ORDER BY Type, Location"
)
测试类模块
开发人员工具箱中的一个工具是一组强大的测试。既然我们已经看到了如何在 shell 中创建数据库类，那么现在让我们看看如何开发一个测试框架来测试数据库类。
回想一下，这些类的测试使用了非常相似的机制，并且实际上遵循了相同的步骤序列。每当开发人员看到这一点，他们就会想到“自动化”和“类”也就是说，很容易创建一个包含所有步骤和子类的基类，这些步骤和子类实现了特定于被测试类的类(test)。这是实现可重复测试的一种非常常见的方式。
在这种情况下，我们在unittests/crud_test.py代码模块中创建了一个名为CRUDTest的基类，它实现了启动(或设置)测试的方法，一个显示返回行的通用方法，以及一个我们想要运行的测试用例。表 5-3 显示了类中实现的方法。
表 5-3
CRUDTest 类方法
|方法
|
因素
|
描述
|
| --- | --- | --- |
| __init__() | | 构造器 |
| begin() | mysqlx 实例，类名，用户名，密码 | 连接到 MySQL 服务器并设置 MyGarage 类。由 setup()方法调用 |
| show_rows() | 行数(列表)，要显示的行数 | 打印列表中的行，直到指定的数目 |
| set_up() | | 设置测试并初始化测试用例。每个类的覆盖 |
| create() | | 运行创建测试用例。每个类的覆盖 |
| read_all() | | 运行读取测试用例以返回所有行。每个类的覆盖 |
| read_one() | | 运行读取测试用例以返回特定的行。每个类的覆盖 |
| update() | | 运行更新测试用例。每个类的覆盖 |
| delete() | | 运行删除测试用例。每个类的覆盖 |
| tear_down() | | 关闭测试并断开与服务器的连接 |
这看起来工作量很大，但是让我们先看看这个类，然后看一个例子，看看我们如何从它派生出一个数据库类来创建一个测试。清单 5-24 显示了CRUDTest类的代码。
from __future__ import print_function
from getpass import getpass
from database.garage_v1 import MyGarage
class CRUDTest(object):
"""Base class for Unit testing table/view classes."""
def __init__(self):
"""Constructor"""
self.mygarage = None
def __begin(self, mysql_x, class_name, user=None, passwd=None):
"""Start the tests"""
print("\n∗∗∗ {0} Class Unit test ∗∗∗\n".format(class_name))
self.mygarage = MyGarage(mysql_x)
if not user:
user = raw_input("User: ")
if not passwd:
passwd = getpass("Password: ")
print("Connecting...")
self.mygarage.connect(user, passwd, 'localhost', 33060)
return self.mygarage
@staticmethod
def show_rows(rows, num_rows):
"""Display N rows from row result"""
print("\n\tFirst {0} rows:".format(num_rows))
print("\t--------------------------")
for item in range(0, num_rows):
print("\t{0}".format(", ".join(rows[item])))
def set_up(self, mysql_x, user=None, passwd=None):
"""Setup functions"""
pass
def create(self):
"""Run Create test case"""
pass
def read_all(self):
"""Run Read(all) test case"""
pass
def read_one(self):
"""Run Read(record) test case"""
pass
def udpate(self):
"""Run Update test case"""
pass
def delete(self):
"""Run Delete test case"""
pass
def tear_down(self):
"""Tear down functions"""
print("\nDisconnecting...")
self.mygarage.disconnect()
Listing 5-24Code for the CRUDTest Class
注意，我们在基类中执行初始化、设置和拆卸步骤。这样，我们可以确保对每个类都以相同的方式执行这些步骤。
还要注意，我们要覆盖的方法是以“pass”作为主体列出的。这本质上是一个“什么都不做”但合法的方法体。我们将在用于为数据库类创建测试的类中编写每个方法的细节。
例如，我们通过创建一个名为VendorTests的新类来迁移我们对 Vendor 类的测试，这个新类是从CRUDTest派生的，并存储在文件unittests/vendor_test.py中。清单 5-25 显示了新类的代码。
from __future__ import print_function
from unittests.crud_test import CRUDTest
from database.vendor import Vendor
class VendorTests(CRUDTest):
"""Test cases for the Vendor class"""
def __init__(self):
"""Constructor"""
CRUDTest.__init__(self)
self.vendor = None
self.last_id = None
def set_up(self, mysql_x, user=None, passwd=None):
"""Setup the test cases"""
self.mygarage = self.begin(mysql_x, "Vendor", user, passwd)
self.vendor = Vendor(self.mygarage)
def create(self):
"""Run Create test case"""
print("\nCRUD: Create test case")
vendor_data = {
"Name": "ACME Bolt Company",
"URL": "www.acme.org",
"Sources": "looney toons"
}
self.vendor.create(vendor_data)
self.last_id = self.mygarage.get_6042780[0]
print("\tLast insert id = {0}".format(self.last_id))
def read_all(self):
"""Run Read(all) test case"""
print("\nCRUD: Read (all) test case")
rows = self.vendor.read()
self.show_rows(rows, 5)
def read_one(self):
"""Run Read(record) test case"""
print("\nCRUD: Read (row) test case")
rows = self.vendor.read(self.last_id)
print("\t{0}".format(", ".join(rows[0])))
def update(self):
"""Run Update test case"""
print("\nCRUD: Update test case")
vendor_data = {
"VendorId": self.last_id,
"Name": "ACME Nut Company",
"URL": "www.acme.org",
"Sources": "looney toons"
}
self.vendor.update(vendor_data)
def delete(self):
"""Run Delete test case"""
print("\nCRUD: Delete test case")
self.vendor.delete(self.last_id)
rows = self.vendor.read(self.last_id)
if not rows:
print("\tNot found (deleted).")
Listing 5-25Code for the VendorTests Class
这项技术的强大之处在于，我们可以继续为每个以类命名的数据库类创建新的测试，并将它们存储在同一个unittests文件夹中。然后我们可以编写一个驱动脚本，在一个循环中运行所有的测试。由于 Location 类只有一个 read all 操作，我们可以为其他操作编写“no operation ”,这允许我们在循环中包含LocationTests。清单 5-26 显示了名为run_all.py的驱动脚本的代码，也存储在unittests文件夹中。
from __future__ import print_function
from getpass import getpass
from unittests.handtool_test import HandtoolTests
from unittests.location_test import LocationTests
from unittests.organizer_test import OrganizerTests
from unittests.place_test import PlaceTests
from unittests.powertool_test import PowertoolTests
from unittests.storage_test import StorageTests
from unittests.vendor_test import VendorTests
print("CRUD Tests for all classes...")
crud_tests = []
handtool = HandtoolTests()
crud_tests.append(handtool)
location = LocationTests()
crud_tests.append(location)
organizer = OrganizerTests()
crud_tests.append(organizer)
place = PlaceTests()
crud_tests.append(place)
powertool = PowertoolTests()
crud_tests.append(powertool)
storage = StorageTests()
crud_tests.append(storage)
vendor = VendorTests()
crud_tests.append(vendor)
# Get user, passwd
user = raw_input("User: ")
passwd = getpass("Password: ")
# Run the CRUD operations for all classes that support them
for test in crud_tests:
test.set_up(mysqlx, user, passwd)
test.create()
test.read_one()
test.read_all()
test.update()
test.read_one()
test.delete()
test.tear_down()
Listing 5-26Test Driver run_all.py
要执行这个测试，您可以使用清单 5-27 中所示的命令以及预期的输出。这里，为了简洁起见，我们只看到输出的一部分。
C:\Users\cbell\Documents\mygarage_v1>mysqlsh --py -f unittests/run_all.py
Running from MySQL Shell. Provide mysqlx in constructor.
CRUD Tests for all classes...
User: root
Password:
∗∗∗ Handtool Class Unit test ∗∗∗
Connecting...
CRUD: Create test case
Last insert id = 2267
CRUD: Read (row) test case
2267, 101, Plumpbus, Hammer, medium, 1001
CRUD: Read (all) test case
First 5 rows:
--------------------------
2050, Awl, Alloy Steel Scratch, 6-in, Kobalt 3000 Steel Rolling Tool Cabinet (Black), Drawer, Left 3
2048, Awl, Complex Hook, 3-in, Kobalt 3000 Steel Rolling Tool Cabinet (Black), Drawer, Left 3
2049, Awl, Curved Hook, 3-in, Kobalt 3000 Steel Rolling Tool Cabinet (Black), Drawer, Left 3
2047, Awl, Hook, 3-in, Kobalt 3000 Steel Rolling Tool Cabinet (Black), Drawer, Left 3
2046, Awl, Scratch, 3-in, Kobalt 3000 Steel Rolling Tool Cabinet (Black), Drawer, Left 3
CRUD: Update test case
CRUD: Read (row) test case
2267, 101, Plumpbus Pro, Screwdriver, grande, 1001
CRUD: Delete test case
Not found (deleted).
Disconnecting...
∗∗∗ Location Class Unit test ∗∗∗
Connecting...
CRUD: Create test case (SKIPPED)
CRUD: Read (row) test case (SKIPPED)
CRUD: Read (all) test case
First 5 rows:
--------------------------
1007, Kobalt 3000 Steel Rolling Tool Cabinet (Black), Drawer, Bottom
1001, Kobalt 3000 Steel Rolling Tool Cabinet (Black), Drawer, Left 1
1002, Kobalt 3000 Steel Rolling Tool Cabinet (Black), Drawer, Left 2
1003, Kobalt 3000 Steel Rolling Tool Cabinet (Black), Drawer, Left 3
1004, Kobalt 3000 Steel Rolling Tool Cabinet (Black), Drawer, Right 1
CRUD: Update test case (SKIPPED)
CRUD: Read (row) test case (SKIPPED)
CRUD: Delete test case (SKIPPED)
...
Listing 5-27Executing the Test Driver
花些时间从书籍网站下载代码，并自己测试单元测试。您应该注意到使用这个概念非常容易，并且您可以开发其他类似的概念来测试您的数据库代码。只是觉得；我们无需编写任何用户界面代码就可以做到这一点，这允许在编写第一行用户界面代码之前验证我们的数据库代码。很好！
摘要
起初，有些人可能会怀疑 MySQL Shell 可以用作开发工具的说法。这可能部分是因为它是新的，部分是因为它不是一个典型的代码编辑器；相反，它更像是 Python 解释器。
但是，您已经亲眼看到了如何使用 shell 来测试代码，以了解哪些方法有效以及如何使用 X DevAPI，而且您还看到了在 shell 中编写代码并执行代码是多么容易。
事实上，我们通过演示如何在没有用户界面支持的情况下用 Python 开发和测试数据库代码模块，进一步提升了这一点。这对开发人员来说是一个巨大的好处，因为它经常被留到最后来测试像数据库类这样的模块。这样，我们在编写用户界面之前测试数据库代码，从而允许我们一次只关注应用的一部分。
在下一章中，我们将继续我们在 X DevAPI 中使用 shell 的旅程，深入了解如何使用 MySQL 文档存储——一种全新的、非 SQL (NOSQL)的数据处理方式。
Footnotes 1例如，我选择单数形式的对象(表)名称，而有些人更喜欢复数形式。习惯上使用单数名称，但有些人可能会争论这个问题，以至于出现问题。对我们大多数人来说那只是噪音。
2
在我职业生涯的某个阶段，我也是其中之一。随着我获得越来越多的经验，我开始意识到如果谨慎和安全地使用，一些权衡确实是有益的。
3
关系数据库纯粹主义者的另一个“罪过”。嘿，这是常有的事。
4
欢迎你这么做！
5
这些不是 Python 中使用单元测试框架( https://docs.python.org/2/library/unit test.html)可用的单元测试。相反，它们是单元测试，因为它们测试应用的各个部分( https://en.wikipedia.org/wiki/Unit_testing )。
