MySQL-Connection-Python-揭秘-三-MySQL Connection/Python 揭秘（三）五

MySQL Connection/Python 揭秘（三）

原文：MySQL Connector/Python Revealed

协议：CC BY-NC-SA 4.0

五、连接池和故障转移

在前两章中，您从查询的角度了解了 MySQL Connector/Python 的工作原理。是时候稍微改变一下话题，看看一些更高级的连接特性:连接池和故障转移。

小费

本章有几个示例程序。列表中出现的所有示例程序都可以下载。有关使用示例程序的更多信息，参见第一章中对示例程序的讨论。

连接池–背景

连接池使得应用可以利用大量的连接进行查询。这在多线程应用中很有用，在多线程应用中，查询最终是并行执行的。通过使用池，可以控制并发连接的数量，并减少开销，因为不必为每个任务创建新的连接。

虽然在 MySQL 中创建连接相对较快，特别是对于执行许多快速查询的应用，并且如果 MySQL 的网络稳定，使用持久连接可以节省足够的时间，从而值得实现连接池。另一方面，如果您正在为网络连接不稳定的物联网(IoT)编写一个程序，并且该程序每分钟只会执行几个查询，那么每次创建一个新连接会更好。

连接池使用两个类。这些类别是

pooling. MySQLConnectionPool
pooling.PooledMySQLConnection

本节将介绍这两个类、它们的方法和属性。下一节将讨论连接池更实际的一面。

注意

不能对连接池使用 C 扩展。结合连接池设置和use_pure调用mysql.connector.connect()函数时，将忽略use_pure选项。

联营。MySQLConnectionPool 类

pooling.MySQLConnectionPool类是定义池的主类。当应用需要执行查询时，在这个类中添加、配置和检索连接。

除了构造函数，还有三个方法和一个属性用于pooling.MySQLConnectionPool类。它们在表 5-1 中进行了总结。

表 5-1

pooling.MySQLConnectionPool课小结

名字

类型

描述

| | --- | --- | --- | | MySQLConnectionPool | 构造器 | 创建连接池的构造函数。 | | add_connection( ) | 方法 | 向池中添加或返回一个连接(即池中的连接数增加 1)。 | | get_connection( ) | 方法 | 从池中获取连接。 | | set_config( ) | 方法 | 配置池中的连接。 | | pool_``nam | 财产 | 池的名称。这可以在实例化池时设置。 |

首先通过调用构造函数来创建连接池。这可以直接发生，也可以通过mysql.connector.connect()函数间接发生。当应用需要连接时，它可以通过调用get_connection()方法来获取一个连接。

警告

在内部使用add_connection()方法来返回到池的连接。也可以使用MySQLConnection类的连接从外部调用它。(不支持使用 C 扩展名的连接。)但是，向池中添加新连接实际上不会增加池的大小。因此，结果是所有的连接都不能再返回到池中，并且出现一个PoolError异常，错误为“添加连接失败；队列已满”。

如有必要，可使用set_config()方法重新配置配置。与前面章节中使用的独立连接不同，不能直接更改连接的配置。如果可能的话，将不再保证池中的所有连接都配置相同。由于应用不知道哪个连接被返回，如果连接与连接之间的配置不同，那将是非常不幸的。如果需要不同配置的连接，请为每个配置创建一个池。

小费

可以有一个以上的池。例如，这可以用于提供不同的连接配置。一种使用情形是读写拆分，即写入到复制主机(源)，读取到复制从机(副本)。

没有官方方法来断开池中的连接。构造函数和方法将在本节后面的示例中更详细地讨论。然而，首先让我们看看使用连接池的另一半:连接。

联营。PooledMySQLConnection 类

从连接池中检索的连接是pooling.PooledMySQLConnection类的实例，而不是MySQLConnection或CMySQLConnection类的实例。在大多数方面，池连接的行为方式与独立连接相同，但也有一些不同。

两个最重要的区别是close()和config()方法被改变了。close()方法实际上并不关闭池化的连接，而是将它返回到池中。因为池中的所有连接必须具有相同的配置，所以config()方法将返回一个PoolError异常。

除了close()和config()方法的行为改变之外，还有pool_name属性。这与pooling. MySQLConnectionPool类相同，可用于确认连接来自哪个池。这在将连接传递给另一个函数或方法时非常有用。

配置选项

连接池的配置由三个选项控制，它们都带有前缀pool_。这些选项允许您设置名称和大小，并控制连接返回池时是否重置。表 5-2 总结了这些选项。创建池后，无法更改任何设置。

表 5-2

配置连接池的选项

名字

缺省值

描述

| | --- | --- | --- | | pool_``nam | 自动生成 | 连接池的名称。默认情况下，该名称是通过连接host、port、user和database连接选项的值生成的。名称最长为pooling.CNX_POOL_MAXNAMESIZE(默认为 64 个)字符，允许使用字母数字字符以及以下字符:。、_、:、-、*、$和#。 | | pool_reset_``sessio | True | 当True时，当连接返回到池中时，会话变量被重置。 | | pool_``siz | 5 | 池中容纳的连接数。该值必须至少为 1，最多为pooling.CNX_POOL_MAXSIZE(默认为 32)。 |

所有连接池都有一个名称。如果在创建池时没有明确设置名称，将通过连接host、port、user和database连接选项的值自动生成名称。如果在关键字参数中没有设置这两个选项，就会引发一个PoolError异常。生成名称时，不考虑通过选项文件设置的选项。

小费

建议显式配置池名称。这确保了对配置的更改不会更改池名称。如果您有多个池，请为它们指定唯一的名称以避免混淆。请记住，即使在您当前的代码中没有使用池名称，以后也可能需要它。

pool_reset_session选项控制当连接返回到池中时是否重置会话变量。重置意味着取消设置所有用户变量(例如@my_user_variable)并确保所有会话变量(例如@@session.sort_buffer_size)具有与全局默认值相同的值。在 MySQL Server 5.6 和更低版本中重置连接有两个限制:

通过重新连接完成重置。
不支持压缩(compress选项)。

在大多数情况下，建议重置连接，因为这样可以确保从池中提取连接时，连接的状态始终相同。

小费

除非明确要求保持连接的状态，否则总是使用pool_reset_session = True(缺省值)来确保在从池中获取连接时知道它们的状态。

使用pool_size选项指定池中的连接数。默认情况下，创建具有五个连接的池，但是最多可以有pooling.CNX_POOL_MAXSIZE个连接。pooling.CNX_POOL_MAXSIZE属性默认为 32。

除了这三个连接池选项之外，必须以与独立连接相同的方式指定连接所需的其他连接选项。还可以使用set_config()方法为现有池中的连接设置与池无关的选项，该方法的工作方式与用于独立连接的config()方法相同。下一节包括一个使用set_config()方法的例子。

既然已经讨论了两个连接池类和配置的基础知识，让我们看看如何使用它们。

使用连接池

终于是时候更实际地开始使用连接池了。本节将首先展示如何创建连接池，然后展示获取和返回连接的示例。本节的后半部分将讨论使用连接池时的查询执行和连接的重新配置。

创建连接池

使用连接池时，第一步是创建池。如前所述，创建池有两种不同的方式:隐式或显式。

要隐式创建一个连接池，使用mysql.connector.connect()函数创建独立连接。只要存在至少一个连接池选项，如果不存在同名的池，就会创建一个池，并返回一个pooling.PooledMySQLConnection类的连接。如果存在具有相同池名称的池，则返回来自该池的连接。一个例子是

import mysql.connector

db = mysql.connector.connect(
  option_files="my.ini",
  pool_name="test_connect",
)

print("Pool name: {0}".format(db.pool_name))

间接方法的优点是它更类似于创建独立连接。缺点是与使用pooling. MySQLConnectionPool类相比，您对池的控制较少。

另一种方法是通过实例化pooling.MySQLConnectionPool类显式地创建一个连接池，例如:

from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
  option_files="my.ini",
  pool_name="test_constructor",
)

print("Pool name: {0}".format(pool.pool_name))

以这种方式创建池的优点是它允许重新配置连接。

当直接调用pooling. MySQLConnectionPool构造函数时，所有的连接池选项都是可选的。如果池是使用mysql.connector.connect()函数创建的，则必须指定至少一个选项。

一旦创建了连接池，就可以从池中检索连接并将其返回到池中。让我们看看这是如何做到的。

使用连接池连接

显然，连接池的主要目的是让连接可供使用。因此，让我们更深入地了解检索和返回连接以及如何使用它们。

获取连接的方式取决于池的创建方式。对于使用mysql.connector.connect()函数创建的池，使用与创建池时相同的池名再次使用mysql.connector.connect()函数检索连接。另一方面，对于通过调用pooling. MySQLConnectionPool构造函数显式创建的池，使用get_connection()方法获取连接。

警告

不要试图混合这两种获取连接的方式。如果使用与使用构造函数创建的池相同的池名使用mysql.connector.connect()函数，则创建第二个池。

使用 mysql.connector.connect()函数

当使用mysql.connector. connect()函数创建池时，立即返回第一个连接，并使用相同的池名再次调用该函数获取其他连接。除了pool_name选项，传递给mysql.connector.connect()的任何选项都被忽略。

图 5-1 显示了使用带有两个连接的连接池和通过mysql.connector. connect()函数创建的池时的一般工作流程。

img/463459_1_En_5_Fig1_HTML.jpg

图 5-1

使用mysql.connector.connect()功能时的工作流程

图的中间是应用中执行的命令。开始时会创建一个有两个连接的连接池。一个连接可以在池中等待应用获取它(左边的连接)，也可以在应用中使用(右边的连接)。

您可以看到，创建池和从池中取出第一个连接的步骤被合并到一个对mysql.connector.connect()函数的调用中。后续的连接可以用同样的方式获取。一旦应用完成了连接，就通过关闭它来将其返回到池中。如果需要，可以通过再次从池中获取连接来重用它(图中未显示)。

清单 5-1 展示了一个使用mysql.connector.connect()函数管理连接池的例子。

import mysql.connector
from mysql.connector.errors import PoolError

print(__file__ + " - connect():")
print("")

# Create a pool and return the first
# connection

db1 = mysql.connector.connect(

  option_files="my.ini",
  pool_size=2,
  pool_name="test",

)

# Get a second connection in the same pool

db2 = mysql.connector.connect(

  pool_name="test")

# Attempt to get a third one
try:
  db3 = mysql.connector.connect(
    pool_name="test")

except PoolError as err:

  print("Unable to fetch connection:\n{0}\n"
    .format(err))

# Save the connection id of db1 and
# return it to the pool, then try
# fetching db3 again.
db1_connection_id = db1.connection_id

db1.close()

db3 = mysql.connector.connect(
  pool_name="test")

print("Connection IDs:\n")
print("db1   db2   db3")
print("-"*15)
print("{0:3d}   {1:3d}   {2:3d}".format(
    db1_connection_id,
    db2.connection_id,
    db3.connection_id
  )
)

db2.close()
db3.close()

Listing 5-1Managing a Connection Pool with mysql.connector.connect()

最初，获取连接的方式与获取独立连接的方式相同。唯一的区别是连接池是通过设置至少一个连接池选项来启用的；在这种情况下，pool_size和pool_name选项都被设置。这个连接是pooling.PooledMySQLConnection类的一个实例。

以类似的方式获取db2连接。pool_name选项是这里唯一设置的，也是唯一必需的选项。然而，如果保留原来的选项可以使代码更容易编写，那么这样做是很好的；当从池中获取额外的连接时，任何额外的选项都会被忽略，只要它们是有效的选项。

当尝试第三次连接时，会出现PoolError异常。该异常已经从示例顶部附近的mysql.connector.errors导入。发生异常是因为池已耗尽。将db1连接返回到池中可以让你得到db3。最后，打印三个连接 id:

listing_5_1.py - connect():

Unable to fetch connection:
Failed getting connection; pool exhausted

Connection IDs:

db1   db2   db3
---------------
324   325   324

实际的 id 将与示例输出不同，因为它们取决于自 MySQL 上次重启以来建立了多少个连接。重要的是，输出确认了db3以之前由db1使用的连接 ID 结束。

使用 get_connection()方法

直接使用连接池时使用的代码与使用mysql.connector.connect()函数有些不同；然而，功能本质上是相同的。用get_connection()方法获取一个连接。正如使用mysql.connector.connect()函数一样，返回的连接是pooling.PooledMySQLConnection类的一个实例。图 5-2 显示了具有两个连接的池的基本工作流程。

img/463459_1_En_5_Fig2_HTML.jpg

图 5-2

使用pooling.MySQLConnectionPool类时的工作流

具有两个连接的连接池是使用pooling. MySQLConnectionPool构造函数池显式创建的。MySQLConnectionPool:构造函数。最初，两个连接都在池中。每当应用需要一个连接时，就使用池对象的get_connection()方法获取它。一旦应用完成了连接，就通过关闭它来将其返回到池中。在清单 5-2 中可以看到前一个例子的对等物。

from mysql.connector import pooling

from mysql.connector import errors

print(__file__ + " - MySQLConnectionPool():")
print("")

pool = pooling.MySQLConnectionPool(

  option_files="my.ini",
  pool_name="test",
  pool_size=2,

)

# Fetch the first connection

db1 = pool.get_connection()

# Get a second connection in the same pool
db2 = pool.get_connection()

# Attempt to get a third one
try:
  db3 = pool.get_connection()

except errors.PoolError as err:

  print("Unable to fetch connection:\n{0}\n"
    .format(err))

# Save the connection id of db1 and
# return it to the pool, then try
# fetching db3 again.
db1_connection_id = db1.connection_id

db1.close()

db3 = pool.get_connection()

print("Connection IDs:\n")
print("db1   db2   db3")
print("-"*15)
print("{0:3d}   {1:3d}   {2:3d}".format(
    db1_connection_id,
    db2.connection_id,
    db3.connection_id
  )
)

db2.close()
db3.close()

Listing 5-2Managing a Connection Pool Using the pool Object Directly

连接池是显式创建的，使用 pool 对象的get_connection()方法检索连接，但除此之外，该示例与使用mysql.connector.connect()函数的示例相同。当连接池耗尽并尝试连接时，再次出现PoolError异常，使用 connection 对象的close()方法将连接返回到连接池。

输出类似于使用mysql.connector.connect()函数创建池时的输出。同样，实际的 id 会有所不同。输出示例如下

listing_5_2.py - MySQLConnectionPool():

Unable to fetch connection:
Failed getting connection; pool exhausted

Connection IDs:

db1   db2   db3
---------------
350   351   350

现在您已经知道了如何创建和返回连接，让我们继续讨论连接最终最重要的目的:执行查询。

执行查询

创建连接池并获取和返回连接可能很有趣，但是不用于查询的连接没有多大价值。当连接不在池中时，它可以像任何常规连接一样使用，只是config()方法不起作用，而close()方法将连接返回到池中，而不是关闭连接。

为了执行查询，可以使用第三章和第四章中讨论的所有其他特性(除了 C 扩展)。清单 5-3 展示了一个使用游标执行简单SELECT查询的例子。

from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
  option_files="my.ini",
  pool_name="test",
)

db = pool.get_connection()

cursor = db.cursor(named_tuple=True)
cursor.execute("""
SELECT Name, CountryCode, Population
  FROM world.city
 WHERE CountryCode = %s""", ("AUS",))

if (cursor.with_rows):
  # Print the rows found
  print(
    "{0:15s}   {1:7s}   {2:10s}".format(
      "City", "Country", "Population"
    )
  )
  city = cursor.fetchone()
  while (city):
    print(
      "{0:15s}   {1:⁷s}    {2:8d}".format(
        city.Name,
        city.CountryCode,
        city.Population
      )
    )
    city = cursor.fetchone()

cursor.close()
db.close()

Listing 5-3Executing a Query in a Connection Pool Connection

这个程序很简单。创建连接后，会获取一个连接。该查询正在使用命名元组的游标中执行。处理完查询结果后，游标关闭，连接返回到池中。正如您所看到的，与前几章中的查询相比，本例中没有什么特别之处，除了连接来自池的事实。执行程序的输出是

City              Country   Population
Sydney              AUS       3276207
Melbourne           AUS       2865329
Brisbane            AUS       1291117
Perth               AUS       1096829
Adelaide            AUS        978100
Canberra            AUS        322723
Gold Coast          AUS        311932
Newcastle           AUS        270324
Central Coast       AUS        227657
Wollongong          AUS        219761
Hobart              AUS        126118
Geelong             AUS        125382
Townsville          AUS        109914
Cairns              AUS         92273

对于连接池，最后要考虑的是如何重新配置池中的连接以及它对连接的影响。

重新配置连接

当你像第三章和第四章那样使用独立连接时，重新配置的概念很简单。重新配置发生在与连接用于查询相同的执行流中。然而，对于池连接来说，情况就不同了，因为有些连接将在池中，而其他连接将在池外工作。更改应用中其他地方使用的连接的配置可能会导致未定义的行为，并可能导致查询突然作为另一个用户或在另一个 MySQL 服务器实例上执行。

MySQL Connector/Python 处理重新配置请求的方式是，对于给定的连接，只有当它在池中时，它才被重新配置。对于在重新配置请求时正在使用的连接，对配置的改变被推迟，直到它被返回到池中。

清单 5-4 展示了一个在有两个连接的连接池中重新配置连接的例子。其中一个连接(db1)在调用set_config()时在池外，而另一个(db2)在池内。

from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
  option_files="my.ini",
  pool_name="test",
  pool_size=2,
)

print("{0:18s}: {1:3s}   {2:3s}".format(
  "Stage", "db1", "db2"
))
print("-"*29)
fmt = "{0:18s}: {1:3d}   {2:3d}"
db1 = pool.get_connection()
db2 = pool.get_connection()
print(
  fmt.format(
    "Initially",
    db1.connection_id,
    db2.connection_id
  )
)

# Return one of the connections before
# the reconfiguration
db2.close()

# Reconfigure the connections
pool.set_config(user="pyuser")

# Fetch db2 again
db2 = pool.get_connection()
print(
  fmt.format(
    "After set_config()",
    db1.connection_id,
    db2.connection_id
  )
)

# Return the db1 connection to the pool
# and refetch it.
db1.close()
db1 = pool.get_connection()
print(
  fmt.format(
    "After refetching",
    db1.connection_id,
    db2.connection_id
  )
)

db1.close()
db2.close()

Listing 5-4Using the 
set_config()

Method

首先创建一个连接池。然后检索两个连接(耗尽池)并打印连接 id。在重新配置之前，db2连接被恢复，而db1保持使用。重新配置连接后，将再次打印连接 id。在这种情况下，配置实际上没有任何变化，但这并不影响 MySQL Connector/Python 的行为。最后，db1连接被返回到池中并再次被检索，最后一次打印连接 ID。输出类似于

Stage             : db1   db2
-----------------------------
Initially         : 369   370
After set_config(): 369   371
After refetching  : 372   371

连接 ID 的改变意味着旧的连接被关闭，连接配置被更新，并且连接被重新建立。从输出中可以看到，db1连接的连接 ID 并没有因为调用set_config()而改变。已经从池中提取的连接在返回池之前不会更新配置。位于池中的连接，就像与db2一起使用的，将会立即更新。在db1连接回到池中并被再次获取后，连接 ID 被更改，反映了配置更新时发生的重新连接。

连接池的讨论到此结束。关于连接还有另一个高级主题:故障转移配置。这将是本章的最后一个主题。

连接故障转移

如今，许多应用需要全天候可用。但是，仍然需要能够在数据库后端执行维护，例如为了升级操作系统或 MySQL 服务器。也可能是由于硬件问题或数据库问题造成的中断。当数据库实例不可用时，应用如何保持在线？答案是执行故障转移到具有相同数据的另一个 MySQL 服务器实例。

有几种方法可以实现应用的高可用性。这是一个大而有趣的话题，许多书都是关于它的。所以，在本书中不可能详细讨论。然而，有一个选项与 MySQL 连接器/Python 直接相关:当主数据库不可用时，连接器可以自动进行故障转移。

本节将介绍 MySQL Connector/Python 中内置的故障转移是如何工作的。第一个主题是配置，然后是如何在应用代码中使用故障转移，最后会有一个例子。

注意

很容易认为实现故障转移所需要的只是配置它。但是，为了让故障转移正常工作，在编写应用时必须考虑到故障转移。“故障转移编码”一节将提供更多信息。

故障转移配置

配置应用以使用 MySQL Connector/Python 中的故障转移特性是使用故障转移最简单的部分。只有一个选项可以考虑:第一个选项。

failover选项为每个 MySQL 服务器实例获取一个带有字典的元组(或列表),以便在创建连接时考虑。字典必须具有对于该实例唯一的连接选项。常见的连接选项可以设置为正常。如果在mysql.connector.connect()函数的参数列表和failover字典中都指定了一个选项，则failover字典中的值优先。

failover选项支持连接选项的子集。只允许与指定连接到哪里、哪个用户和连接池选项直接相关的选项。受支持选项的完整列表如下

user
password
host
port
unix_socket
database
pool_name
pool_size

一般来说，最好让所有 MySQL 服务器实例的选项尽可能相似，因为这样可以减少出现难以调试的错误的几率。例如，如果用户名不同，那么对特权的更改在不同的实例之间结束的可能性就会增加。

创建带故障转移的连接的一个示例是

import mysql.connector

primary_args = {
  "host": "192.168.56.10",
}
failover_args = {
  "host": "192.168.56.11",
}
db = mysql.connector.connect(
  option_files="my.ini",
  failover=(
    primary_args,
    failover_args,
  )
)

在本例中，标准的my.ini文件用于设置两个实例的公共选项。在failover选项中设置的唯一选项是每个实例的host。MySQL Connector/Python 将尝试按照列出的顺序连接到实例，因此列出的第一个实例将是主实例，第二个实例是故障转移实例。如果需要，可以添加更多实例。

注意

MySQL 服务器实例添加到故障转移元组的顺序很重要。MySQL Connector/Python 将尝试从列出的第一个实例开始按顺序连接到实例。

只有在请求新连接时，才会考虑failover选项中列出的实例。也就是说，如果首先成功创建了一个连接，但后来失败了，MySQL Connector/Python 不会自动重新连接，既不会连接到旧实例，也不会连接到其他实例。失败连接的检测和新连接的建立必须在应用中明确编码。类似地，应用必须在用完要连接的实例时处理这种情况。

故障转移编码

如前所述，使用故障转移的困难部分是让应用与它们一起工作。MySQL Connector/Python 提供了连接到第一个可用实例的框架，但是由应用来确保它用于提高可用性。

当连接失败时，MySQL Connector/Python 永远不会自动重新连接。无论是否使用failover选项进行连接，情况都是如此。这样做的原因是，如果什么都没发生，只是重新连接并继续通常是不安全的。例如，当断开连接发生时，应用可能正在处理事务，在这种情况下，有必要返回到事务的开始。

这意味着开发人员在使用连接器时必须检查错误。一般来说，错误处理是第九章的主题。关于故障转移，重要的是检查它是否真的是连接错误；否则，初始化故障转移就没有什么意义了。表 5-3 中列出了创建连接后可能出现的一些常见连接错误。

表 5-3

常见的连接相关错误

全局变量

errno–定义符号

出错信息

| | --- | --- | --- | | (无) | (无) | MySQL 连接不可用 | | One thousand and fifty-three | ER_SERVER_SHUTDOWN | 服务器正在关闭 | | Two thousand and five | CR_SERVER_LOST_EXTENDED | 与位于“…”的 MySQL 服务器失去连接，系统错误:… | | Two thousand and six | CR_SERVER_GONE_ERROR | MySQL 服务器已经消失了 | | Two thousand and thirteen | CR_SERVER_LOST | 查询期间失去了与 MySQL 服务器的连接 |

当试图以非查询方式使用连接时，会出现“MySQL 连接不可用”错误，例如在连接丢失后创建游标时。当错误号可用时，可以在异常的errno属性中找到。定义符号在mysql.connector.errorcode模块中可用，可用于更容易地查看错误号与哪个错误进行比较。

如果有几个应用实例都使用相同的 MySQL 服务器实例，并且它们写入数据库，那么确保没有应用实例进行故障转移或者所有应用实例都进行故障转移也很重要。如果一些应用实例最终写入一个数据库实例，而其他应用实例写入另一个数据库实例，则数据可能会不一致。在有多个应用实例的情况下，最好使用一个代理来实现故障转移，比如 MySQL 路由器或 ProxySQL，它将连接指向正确的 MySQL 服务器实例。

小费

为了避免不一致的数据，确保故障转移 MySQL 实例设置了super_read_only选项，直到它们打算接受写入。MySQL Server 5.7 和更高版本中提供了super_read_only选项。早期版本只提供了较弱的read_only选项，它不会阻止拥有SUPER特权的用户写入实例。

当涉及到故障转移时，测试也比平常更重要。确保您测试了各种故障条件，包括在应用执行查询和引入网络故障时强行终止 MySQL 服务器。此外，添加一些不会导致故障转移的故障，例如锁定等待超时。这是验证应用能够正确处理故障的唯一方法。

为了总结关于使用故障转移特性的讨论，让我们看一个例子，这个例子包含了到目前为止所讨论的一些内容。

故障转移示例

在使用故障转移的应用中，很难考虑所有必须考虑的事情。希望一个例子将有助于使事情更清楚。

为了让这个例子正常工作，必须有两个 MySQL 服务器实例。在真实的应用中，数据库实例通常位于不同的主机上，因此即使整个主机都关闭了，也有可能进行故障转移。然而，对于这个例子，在同一个主机上有两个实例使用不同的 TCP 端口、到数据目录的路径(datadir选项)和其他特定于数据库的文件，并且在 Linux 和 Unix 上有不同的 Unix 套接字路径(socket选项)是没有问题的。

小费

根据您的操作系统和 MySQL 的安装方式，在一台机器上运行多个实例有不同的选择。参见 https://dev.mysql.com/doc/refman/en/multiple-servers.html 和其中的参考资料，了解微软视窗和 Unix/Linux 的操作说明。如果你在 Linux 上使用 systemd 管理 MySQL，参见 https://dev.mysql.com/doc/refman/en/using-systemd.html 。

该示例假设两个实例都在本地主机(127.0.0.1)上，主实例使用端口 3306(与前面所有示例一样)，故障切换实例使用端口 3307。见清单 5-5 。

import mysql.connector
from mysql.connector import errorcode
from mysql.connector import errors

def connect():

  """Connect to MySQL Server and return
  the connection object."""
  primary_args = {
    "host": "127.0.0.1",
    "port": 3306,
  }
  failover_args = {
    "host": "127.0.0.1",
    "port": 3307,
  }
  db = mysql.connector.connect(
    option_files="my.ini",
    use_pure=True,
    failover=(
      primary_args,
      failover_args,
    )
  )

  return db

def execute(db, wait_for_failure=False):

  """Execute the query and print
  the result."""
  sql = """
SELECT @@global.hostname AS Hostname,
       @@global.port AS Port"""

  retry = False
  try:
    cursor = db.cursor(named_tuple=True)
  except errors.OperationalError as err:
    print("Failed to create the cursor."
      + " Error:\n{0}\n".format(err))
    retry = True
  else:
    if (wait_for_failure):
      try:
        input("Shut down primary now to"
          + " fail when executing query."
          + "\nHit Enter to continue.")
      except SyntaxError:
        pass
      print("")

    try:
      cursor.execute(sql)
    except errors.InterfaceError as err:
      print("Failed to execute query"
        + " (InterfaceError)."
        + " Error:\n{0}\n".format(err))
      retry = (err.errno == errorcode.CR_SERVER_LOST)
    except errors.OperationalError as err:
      print("Failed to execute query"
        + " (OperationalError)."
        + " Error:\n{0}\n".format(err))
      retry = (err.errno == errorcode.CR_SERVER_LOST_EXTENDED)
    else:
      print("Result of query:")
      print(cursor.fetchall())
    finally:
      cursor.close()

  return retry

# Execute for the first time This should
# be against the primary instance
db = connect()
retry = True
while retry:
  retry = execute(db)
  if retry:
    # Reconnect
    db = connect()
print("")

# Wait for the primary instance to
# shut down.
try:
  input("Shut down primary now to fail"
      + " when creating cursor."
      + "\nHit Enter to continue.")
except SyntaxError:
  pass
print("")

# Attempt to execute again
retry = True
allow_failure = True
while retry:
  retry = execute(db, allow_failure)
  allow_failure = False
  if retry:
    # Reconnect
    db = connect()

db.close()

Listing 5-5Using the Failover Feature

该连接是在connect()功能中创建的。将它放入自己的函数中的主要原因是，当故障发生时，有必要显式地重新连接，因此将与连接相关的代码隔离并可重用是很方便的。

这也是使用execute()函数的原因，该函数创建一个游标并执行一个查询来获取程序所连接的 MySQL 服务器实例的主机名和端口。执行代码包括try语句，用于测试操作是否成功，如果不成功，则在重新连接后是否应该重试查询(以及可能的故障转移)。

该示例假设主 MySQL 服务器实例和故障转移 MySQL 服务器实例在开始时都可用。当第一次创建连接时，它将针对主实例，因为它在failover选项中首先列出。一旦针对主实例的查询完成，执行将暂停，这样，如果在创建下一个游标时出现故障，就可以关闭主实例。

当执行继续时(按下输入后)，将再次尝试查询。如果主实例已关闭，创建游标将失败，并将打印错误。否则，将会创建一个新的暂停，因为第二轮第一次调用execute()函数时wait_for_failover被设置为True。如果主实例此时关闭，则在尝试执行实际查询时会出现错误。在这种情况下，将错误号与预期值进行比较，以确保确实是连接问题导致了失败。

当检测到连接失败时，代码将尝试重新连接。这次mysql.connector.connect()将故障转移到故障转移实例。然后就可以执行查询了。

当游标创建失败时的输出是

Result of query:
[Row(Hostname='MY-COMPUTER', Port=3306)]

Shut down primary now to fail when creating cursor.
Hit Enter to continue.

Failed to create the cursor. Error:
MySQL Connection not available.

Result of query:
[Row(Hostname='MY-COMPUTER', Port=3307)]

收到的错误是一个没有错误号的OperationalError异常。请注意连接失败后端口号是如何变化的，这表明程序现在已连接到故障转移实例。

第二种情况是在尝试执行查询时发生错误，根据平台的不同，这种情况有不同的异常和错误。在 Microsoft Windows 上，输出是

Result of query:
[Row(Hostname='MY-COMPUTER', Port=3306)]

Shut down primary now to fail when creating cursor.
Hit Enter to continue.

Shut down primary now to fail when executing query.
Hit Enter to continue.

Failed to execute query (OperationalError). Error:
2055: Lost connection to MySQL server at '127.0.0.1:3306', system error: 10053 An established connection was aborted by the software in your host machine

Result of query:
[Row(Hostname='MY-COMPUTER', Port=3307)]

这里是另一个OperationalError异常，但是错误号设置为 2055。在 Linux 上，错误是

Failed to execute query (InterfaceError). Error:
2013: Lost connection to MySQL server during query

因此，在 Linux 上，这是一个错误号为 2013 的InterfaceError异常。这表明失败的细节也可能取决于平台。它还取决于是使用纯 Python 实现还是 C 扩展，因此在编码时也必须考虑到这一点。

摘要

在本章中，您了解了两个高级连接特性:连接池和故障转移。它们不常用，但在某些应用中会很有用。

连接池特性使应用可以从池中检索连接。这对于多线程应用特别有用，在多线程应用中，可以使用池来减少开销并限制查询执行的并发性。

故障转移功能使 MySQL Connector/Python 依次检查每个已配置的连接，以找到第一个可用的连接。这有助于提高可用性，但也需要在应用中做额外的工作。可以将连接池和故障转移功能结合起来。

除了错误处理和故障排除(第九章和第十章)，传统 MySQL 连接器/Python 的讨论到此结束。在接下来的三章中，您将看到 MySQL Connector/Python 8.0 独有的 X DevAPI 是如何作为文档存储与 MySQL Server 一起使用的。

六、X DevAPI

MySQL Server 最初是在 1995 年作为 SQL 数据库发布的。如第 2 和第三章所示执行的 SQL 语句仍然是 MySQL 中最常见的执行查询的方式，mysql.connector模块使用传统的协议。然而，还有另一种方法:新的 X 协议。

本章将首先简要介绍 X 插件(后端)和 X DevAPI(应用使用的 API)以及它们之间的特性。本章的其余部分将集中在 X DevAPI 的 MySQL 连接器/Python 实现的部分，这些部分在 API 的三个主要部分中是通用的:MySQL 文档存储、SQL 表的创建-读取-更新-删除(CRUD)接口和 SQL 语句。这包括如何创建连接、常见的参数类型、语句类和结果对象。

接下来的两章将详细介绍 API 的其余部分。第七章将展示如何使用 MySQL 文档库。第八章将展示如何通过 CRUD NoSQL 方法和 SQL 语句将 X DevAPI 用于 SQL 表。错误处理和故障排除被推迟到第 9 和 10 章。

小费

MySQL X DevAPI 非常新；到了 MySQL 8.0 就变成 GA 了。这意味着新功能仍在以相对较快的速度开发。如果您找不到某个特性，请在 https://dev.mysql.com/doc/dev/connector-python/8.0/ 查看在线 API 文档，查看该特性是否已被添加。您也可以在 https://bugs.mysql.com/ 请求新功能。

什么是 NoSQL？

对于 NoSQL，没有一个大家都认同的定义。“不”是指“不，SQL 根本不用于定义查询”还是“不”是指“不仅如此？”“SQL”是指用于编写查询的语言还是指关系数据库？即使在 NoSQL，现有产品之间也有很大差异。有些是键值存储，有些存储 JSON 文档等文档。换句话说，还不清楚 NoSQL 是什么，但一个共同点是使用 API 方法而不是 SQL 语句来查询数据。

从 MySQL 的角度考虑 NoSQL，使用 MySQL 作为关系数据库，并使用结构化查询语言(SQL)如SELET * FROM world.city编写查询，这意味着它显然属于 SQL 范畴。另一方面，使用 MySQL 文档存储(将数据存储在 JSON 文档中)并使用 X DevAPI(编程语言特性)的方法来定义查询来查询数据，这意味着它处于 NoSQL 体系中。

然而，两者之间有一个灰色地带。X DevAPI 还支持查询 SQL(关系)表，而无需编写 SQL 查询，并且您可以使用 SQL 语句查询文档存储中的文档。这些用途是否应被视为 NoSQL 可以讨论。在这个意义上，你可以说 MySQL 8.0 和文档存储以及 X DevAPI 是一个“不仅仅是 SQL”的数据库。

MySQL X 插件

在 MySQL Server 5.7.12 中，MySQL X 插件是作为测试版特性引入的。此后，它被赋予了成熟的时间，并在 MySQL Server 8.0 中正式发布。除了传统的 SQL 语句之外，X 插件允许您以类似于其他文档存储的方式使用 NoSQL 来使用 MySQL。

X 插件有几个部分来处理它使用的各个级别。这些零件是

X 插件:这是特性的服务器端实现。在 MySQL 8.0.11 和更高版本中，它是一个内置插件，因此不需要任何操作就可以启用它。
X 协议:应用用来与 X 插件通信的新协议。X 协议的默认 TCP 端口是端口 33060。

*** X DevAPI :这是用于 X 协议的新 API。

*   **mysqlx 模块**:MySQL 连接器/Python 模块，实现了 X DevAPI。**

**此外，还有 MySQL 文档库的概念，它是 X 插件、X 协议、X DevAPI 和 MySQL Shell(稍后讨论)的集合。

为了能够使用 X 插件，开发了一个名为 X DevAPI 的新 API。它可用于多种编程语言，包括 Python、JavaScript (Node.js)、PHP、.Net、C++和 Java。MySQL Shell 是一个新的命令行客户端，在某种程度上可以取代传统的mysql命令行客户端。它支持 X DevAPI，可用于使用 SQL 语句、Python 和 JavaScript 执行查询。此外，MySQL Shell 可用于使用 Python 或 JavaScript 管理 MySQL InnoDB 集群。

应用和文档存储之间的通信是使用 X 协议完成的。因为与传统的 MySQL 协议相比，它是一个新的协议，所以文档存储使用自己的端口。默认端口号是 33060；这可以使用mysqlx_port选项进行更改。

X DevAPI 的服务器端部分是作为 MySQL 服务器的插件实现的。这个插件被命名为 X 插件。在 MySQL Server 8.0.11 及更高版本中默认启用；事实上，它已经成为一个内置的插件，无法删除，所以它会一直存在。X 插件也适用于 MySQL 的旧版本；然而，直到 8 . 0 . 11 MySQL 8.0 普遍上市时才发生了变化。所以，确保你用的是 MySQL 8.0.11 或者更高版本。

您可以通过查询information_schema数据库中的PLUGINS视图来确认插件是否处于活动状态:

mysql> SELECT *
         FROM information_schema.PLUGINS
        WHERE PLUGIN_NAME = 'mysqlx'\G
*************************** 1\. row ***************************
           PLUGIN_NAME: mysqlx
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: DAEMON
   PLUGIN_TYPE_VERSION: 80011.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Oracle Corp
    PLUGIN_DESCRIPTION: X Plugin for MySQL
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
1 row in set (0.00 sec)

注意这里的PLUGIN_STATUS是ACTIVE。如果不是这样，最可能的原因是 X 插件在 MySQL 配置文件中被显式禁用(在微软 Windows 上是my.ini，在其他平台上是my.cnf)。寻找一个选项，如

[mysqld]
mysqlx = 0

代替mysqlx = 0，你也可能看到skip-mysqlx。去掉这个选项，注释掉，或者改成mysqlx = 1。因为 X 插件是默认启用的，所以推荐的方法是移除或注释掉它。

在这一章和接下来的两章中，你会学到 X 特性的一些特征。然而，在开始使用 X DevAPI 之前，您需要对mysqlx模块有一个高层次的概述。

mysqlx 模块

与 MySQL Connector/Python 的其他部分相比，Connector/Python 中的 X DevAPI 支持在它自己的独立模块中。这个模块叫做mysqlx。mysqlx模块和 mysql.connector 模块的名称和一般用法有很大的不同。这可能看起来很奇怪，但是 X DevAPI 的部分思想是在支持的语言之间有一个相对统一的 API。这意味着如果你习惯于在 MySQL Connector/Python 中使用 X DevAPI，很容易实现另一个项目，例如使用 MySQL Connector/node . js .¹

为了开始使用 Python 的 X DevAPI，您必须导入mysqlx模块:

import mysqlx

就这样。下一步是创建一个会话，但是让我们首先看一下图 6-1 ，它显示了mysqlx模块是如何根据将在 X DevAPI 讨论的剩余部分中使用的类来组织的。

img/463459_1_En_6_Fig1_HTML.jpg

图 6-1。

mysqlx模块的组织

类别显示为较小的黄色(浅灰色)框。类周围较大的方框是类所在的子模块。例如，Session类位于mysqlx.connection中。

图 6-1 也显示了代码执行的一般流程。您从一个会话开始，可以从会话对象获得一个模式对象，用于 CRUD 语句或 SQL 语句。SQL 语句的流程很简单，因为这会产生一个可能返回行的 SQL 结果。

CRUD 模式对象包括集合、表和视图。集合用于文档存储，而表和视图用于 SQL 表。CRUD 对象可用于创建 CRUD 语句。这些是statement子模块中的语句；总共将讨论八个 CRUD 语句类。(“…”表示图中未包括的类别。)

对于没有结果集的查询，CRUD 语句以“普通”结果结束。返回数据的语句以集合的文档结果或 SQL 表和视图的行结果结束。文档结果返回数据作为DbDoc对象，行结果返回Row对象。

当您完成所有部分的工作时，请随时返回本概述。在继续创建会话之前，让我们讨论一下命令是如何执行的以及对链接它们的支持。

实现一系列命令有两种不同的方式:每个方法一行代码和execute()(已经定义并准备执行的语句所必需的)，或者链接方法调用。考虑一个 find 语句，其中您想要定义从文档中提取的字段，设置一个过滤器，并执行查询。用省略号代替实际的参数，可以像下面这样创建、优化和执行这个查询

statement = collection.find()
statement.fields(...)
statement.where(...)
result = statement.execute()

或者，相同的查询可以写成一个链，如下所示:

result = collection.find().fields(...).where(...).execute()

也可以使用混合语句，语句的一部分使用链，而另一部分不使用，或者可以使用几个较短的链。如果您需要执行几个具有公共库的查询，但随后更改了过滤器或值，这将非常有用。

一种方法并不比另一种更正确。您应该根据代码的风格、需求以及语句的使用方式来决定使用哪一个。接下来的两章包括了将命令流组合在一起的各种方法的例子。

MySQL Shell

MySQL Shell 是一个新的命令行客户端，与传统的mysql命令行客户端相比，它提供了几个额外的特性。它包含的特性之一是支持在 Python 中使用 X DevAPI。虽然 MySQL Shell 不使用 MySQL Connector/Python，因此mysqlx模块并不是 100%相同，但是您可以使用 MySQL Shell 来交互式地测试 X DevAPI。

一个使用 MySQL Shell 创建会话并执行读取请求的示例是

MySQL  Py > connect_args = {
         ...   'host'        : '127.0.0.1',
         ...   'port'        : 33060,
         ...   'user'        : 'pyuser',
         ...   'password'    : 'Py@pp4Demo',
         ... };
         ...
 MySQL  Py > db = mysqlx.get_session(**connect_args)
 MySQL  Py > schema = db.get_schema('world_x')
 MySQL  Py > countries = schema.get_collection('countryinfo')
 MySQL  Py > country = countries.get_one('AUS')
 MySQL  Py >
 MySQL  Py > fmt = "{0:13s} {1}"
 MySQL  Py > print(fmt.format(
         ...   "Name ........:",
         ...   country["Name"]
         ... ))
         ...
Name ........: Australia

 MySQL  Py > print(fmt.format(
         ...   "Continent ...:",
         ...   country["geography"]["Continent"]
         ... ))
         ...
Continent ...: Oceania

如果代码还没有意义，不要担心；这就是这一章和接下来两章的内容。到最后，一切都应该清楚了。第十章还将探讨在开发过程中使用 MySQL Shell 作为工具。

请注意，该示例使用的是world_x示例数据库，它是从前面章节中使用的world示例数据库派生而来的。如果想玩world_x数据库，可以从 https://dev.mysql.com/doc/index-other.html 下载。

创建会话

在 X DevAPI 中，一个会话对应于传统 MySQL 连接器/Python mysql.connector模块中的连接对象。无论您是希望创建一个会话来使用文档存储、使用 SQL 表的 CRUD 方法，还是执行传统的 SQL 查询，都没有关系。这是 X DevAPI 的优势之一:它结合了 NoSQL 和 SQL 世界。

小费

X DevAPI 的主要用途是 NoSQL CRUD 方法。如果您需要的不仅仅是基本的 SQL 特性，建议使用本书前面介绍的mysql.connector模块。也可以结合使用mysqlx和mysql.connector模块来获得两个世界的最佳效果。

使用get_session()方法创建一个会话。传递给函数的参数用于配置会话。该函数的定义是

mysqlx.get_session(*args, **kwargs)

可以用两种方式之一指定连接参数:

以与在mysql.connector模块中创建连接时相同的方式传递选项
创建 URI

让我们看看如何使用每种方式，从明确指定选项开始。在讨论了配置会话之后，将会有使用get_session()函数创建会话的例子。

警告

不要将密码硬编码到应用中。这既不安全也不实际。通常，最好将连接选项放在应用之外。这也确保了可以在不更新应用的情况下更新连接选项。

传递单个选项

如果您习惯于使用 MySQL Connector/Python 进行编码，指定选项的最简单方法是将它们分别作为直接参数或字典进行传递。

与前几章中创建的连接相比，一个重要的区别是不可能指定 MySQL 配置文件。因此，如果您使用 X DevAPI，建议将选项存储在自定义配置文件中。支持选项的完整列表见表 6-1 。这些选项按字母顺序排列。

表 6-1。

用于创建会话的 X DevAPI 选项

名字

缺省值

描述

| | --- | --- | --- | | aut h | 使用 SSL、Unix 套接字和 Windows 命名管道:PLAIN否则，尝试MYSQL41最后，试试SHA256_MEMORY | auth取三个值之一:MYSQL41、SHA256_MEMORY或PLAIN. MYSQL41应该与mysql_native_password认证插件一起使用。SHA256_MEMORY可用于在没有 SSL 连接的情况下使用sha2_caching_password身份验证插件连接帐户，前提是自 MySQL 服务器上次重启以来，在使用 SSL 之前至少建立了一次连接。PLAIN用于大多数其他情况，但是需要安全连接，使用 SSL、Unix 套接字或 Windows 命名管道。很少需要设置此选项。 | | hos t | localhost | 要连接的主机名；默认设置是连接到本地主机。 | | passwor d | | 用于身份验证的密码；对于测试用户来说，就是Py@pp4Demo。 | | por t | 33060 | MySQL 正在监听 X DevAPI 连接的端口。端口 33060 是标准的 MySQL X DevAPI 端口。 | | router s | | 带有host和port键的字典列表定义了可能要连接的 MySQL 实例。可选地，也可以设置priority；该值越高，使用该实例的可能性越大。如果为一个实例设置了优先级，则必须为所有实例设置优先级。如果没有给定优先级，实例将按照它们在列表中出现的顺序使用。如果除了routers之外还指定了host选项，则host和port选项用于在routers列表的末尾创建一个实例。 | | schem a | | 用于会话的默认模式(数据库)。不要求模式已经存在。默认模式只适用于mysqlx.crud.Session.get_default_schema()方法。 | | socke t | | Unix 套接字或 Windows 命名管道。 | | ssl-``c | | 包含 SSL 证书颁发机构(CA)的文件的路径。 | | ssl-``cr | | 包含 SSL 证书吊销列表的文件的路径。 | | ssl-``cer | | 包含公共 SSL 证书的文件的路径。 | | ssl-``ke | | 包含私有 SSL 密钥的文件的路径。 | | ssl-mode | REQUIRE D | 使用哪种 SSL 模式。这与 MySQL 服务器附带的客户端相同。它可以取一系列值:DISABLED、PREFERRED、REQUIRED、VERIFY_CA、VERIFY_IDENTITY。值VERIFY_IDENTITY相当于旧的ssl_verify_cert选项。亦见 https://dev.mysql.com/doc/refman/en/encrypted-connection-options.html#option_general_ssl-mode 。 | | use_pure | Fals e | 是使用纯 Python 实现(当use_pure = True)还是 C 扩展。 | | use r | | 应用用户的用户名。不要包括@和后面的主机名(也就是说，对于测试用户，只需指定pyuser)。 |

正如您从选项列表中看到的，选项远没有mysql.connector模块多。最值得注意的是没有字符集选项。X DevAPI 总是使用utf8mb4。

小费

如果您的数据使用不同于utf8、utf8mb3或utf8mb4的字符集存储，请在查询中转换它或使用mysql.connector模块。

支持的 SSL 选项列表(除了ssl-mode)存储在mysqlx模块的_SSL_OPTS常量中，完整的选项列表可以在_SESS_OPTS常量中找到。这使得获取选项列表变得很容易，例如，通过使用以下代码:

import mysqlx

print("SSL options ...: {0}".format(
  mysqlx._SSL_OPTS
))
print("All options ...: {0}".format(
  mysqlx._SESS_OPTS
))

这段代码举例说明了在 MySQL Connector/Python 和 MySQL Shell 中使用 X DevAPI 的区别。在 MySQL Shell 中，mysqlx模块没有这两个属性，所以这个例子会引发一个异常。使用 MySQL 连接器/Python 8.0.11 的输出是

SSL options ...: ['ssl-cert', 'ssl-ca', 'ssl-key', 'ssl-crl']
All options ...: ['ssl-cert', 'ssl-ca', 'ssl-key', 'ssl-crl', 'user', 'password', 'schema', 'host', 'port', 'routers', 'socket', 'ssl-mode', 'auth', 'use_pure']

MySQL Connector/Python 还支持使用连接选项指定 URI。让我们看看这是如何做到的。

路过一个 URI

连接到数据库的一种常见方法是创建一个包含所有连接选项的 URI(统一资源标识符)。URI 也用于访问网站(所有 URL(统一资源定位符)也是 URIs)。从 MySQL Connector/J (Java)中也可以了解到使用 URI 进行数据库连接。

X DevAPI 的 URI 的基本形式是

scheme://[user[:[password]]@]target[:port][/schema][?attribute1=value1][&attribute2=value2...]

scheme 始终是mysqlx，可以省略(MySQL Connector/Python 会在缺少的情况下添加)。在撰写本文时，MySQL Connector/Python 不支持 URI 中的字符转义(这在其他方面是正常的)，因此目前还不支持某些值。

注意

由于 https://bugs.mysql.com/89614 中描述的 bug，参数中目前不支持某些字符。最值得注意的是，密码中不支持@字符。如果您想使用 URI 进行测试，您必须将密码更改为不包含@字符，直到错误被修复。

目标是套接字选项、主机选项或者用冒号分隔的主机和端口选项。属性是除user、password、host、port、socket和schema之外的任何支持选项。

例如，考虑使用以下参数(按照它们在 URI 中出现的顺序)创建一个连接:

user : Pyuser
password : PyApp4Demo
host : 127.0.0.1
port : 33060
schema : py_test_db
ssl-mode : REQUIRED
auth : PLAIN

由此产生的 URI 是

mysqlx://pyuser:PyApp4Demo@127.0.0.1:33060/py_test_db?ssl-mode=REQUIRED&auth=PLAIN

关于 X DevAPI 的其余讨论将逐个传递连接选项。

连接示例

是时候创建一个使用 X DevAPI 连接到 MySQL 的实际会话了。为了避免将连接选项编码到示例中，本章剩余部分和后面两章中的示例所共有的配置将存储在config模块中。

本书源代码中包含的config模块的内容见下文config.py:

connect_args = {
  'host': '127.0.0.1',
  'port': 33060,
  'user': 'pyuser',
  'password': 'Py@pp4Demo',
};

使用config模块，可创建如下会话:

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

使用完会话后，建议将其关闭，以确保连接完全终止。这是通过close()方法实现的:

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

...

db.close()

仅此而已。所以，让我们继续，开始使用 X DevAPI 会话。

使用会话

session 对象包含一些自己的有用方法。请记住，会话相当于mysql.connector连接对象，因此是在会话级别控制事务。本节将介绍这些功能。此外，会话还包含用于操作模式的方法，如创建和删除模式。下一节将介绍如何使用模式。

处理

X DevAPI 支持除操作模式对象之外的所有操作的事务。这与使用mysql.connector模块或任何其他方式与 MySQL 服务器交互时是一样的。X DevAPI 实际上比mysql.connector模块具有更好的事务支持，因为除了事务本身之外，它还支持保存点。

注意

mysqlx和mysql.connector的一个重要区别是autocommit. mysqlx从 MySQL 服务器中的全局缺省设置中继承值，除了使用 SQL 语句之外，没有其他方法可以改变它的值。因此，建议总是显式使用事务。

有六种方法可以控制事务和保存点。它们在表 6-2 中进行了总结。这些方法的顺序取决于它们在事务中的使用时间。

表 6-2。

控制事务的会话方法

方法

争吵

描述

| | --- | --- | --- | | start_``transactio | | 开始交易。 | | set_``savepoin | name | 设置保存点。如果未指定名称，将使用uuid.uuid1()函数生成一个名称。将返回保存点的名称。 | | release_``savepoin | name | 释放具有指定名称的保存点(相当于提交，但不保存更改)。 | | rollback_``t | name | 回滚自指定保存点以来所做的更改。 | | commi t | | 提交(保存)自start_transaction()调用以来所做的所有更改。 | | rollbac k | | 取消自start_transaction()调用以来所做的所有更改。保存点的使用不会改变回滚的结果。 |

在 X DevAPI 讨论的剩余部分将会有使用事务的例子。在此之前，有几个会话实用程序方法需要讨论。

其他会话方法

让我们讨论几种对各种目的有用的方法。它们主要围绕检索对象展开，比如获取底层连接对象或启动 SQL 语句。表 6-3 按字母顺序总结了实用方法。所有返回的对象都相对于mysqlx模块。

表 6-3。

会话实用程序方法

方法

争吵

返回对象

描述

| | --- | --- | --- | --- | | get_``connectio | | connection.Connection | 检索基础连接。 | | is_``ope | | | 根据连接是否打开，返回True或False。 | | sq l | sql | statement.SqlStatement | 用于执行 SQL 查询。另请参见第章第八部分中的“SQL 语句”部分。 |

这些方法中的一些将在后面的例子中使用。这个方法列表中缺少的一点是，如果你想使用 NoSQL CRUD 方法，该如何进行。这是通过获取一个模式对象来实现的，这是下一步要讨论的内容，还有其他模式方法。

计划

模式(schemata)是可以包含表或文档集合的容器。它们本身并不直接使用，在某种程度上可以看作是一种名称空间。在 X DevAPI 中，只有在使用 CRUD 方法时才需要模式对象。

使用模式的方法分为两类。创建、删除和检索模式的方法在mysqlx. Session类中，而使用模式或获取模式信息的方法在mysqlx.crud.Schema类中。本节研究这些方法，但与操作集合和表相关的方法除外，这是下两章的主题。在本节的最后，有一个例子将讨论的方法和属性放在一起。

模式操作

当应用需要一个模式时，要做的第一件事就是创建一个新的模式或者检索一个现有的模式。或者，如果不再需要该模式，可以在最后将其删除。

执行这些任务的方法的共同点是它们都存在于mysqlx. Session类中。表 6-4 总结了本节将要讨论的方法。

表 6-4。

会话模式方法

方法

争吵

返回对象

描述

| | --- | --- | --- | --- | | create_``schem | name | crud.Schema | 使用指定的名称作为参数创建架构。 | | drop_``schem | name | | 删除由 name 参数指定的架构。 | | get_default_``schem | | crud.Schema | 返回创建会话时指定的架构的架构对象。如果不存在默认模式，就会出现ProgrammingError异常。 | | get_``schem | name | crud.Schema | 返回请求的架构对象。如果不存在具有指定名称的模式，仍然会返回一个Schema对象。 | | get_schemas | | | 返回用户有权访问的架构名称列表。8.0.12 版中引入了此方法。 |

图 6-2 显示了模式在会话和对象类之间的工作流中的位置。红色(深灰色)框是可用于从一个类(大框)转到另一个类的方法示例。

img/463459_1_En_6_Fig2_HTML.jpg

图 6-2。

模式对象周围的工作流示例

图 6-2 开始使用mysqlx. get_session()方法创建一个会话，如本章前面所讨论的。然后使用get_schema()方法获得一个模式对象。另一个选择是对新模式使用create_schema()。此时，可以选择使用哪种对象，从那时起，语句就被定义了。这是下两章的主题。

您将首先使用create_schema()方法创建一个模式，然后您将使用get_default_schema()和get_schema()方法为一个现有的模式获取一个mysqlx.Schema类的对象，最后您将使用drop_schema()方法删除一个模式。

创建模式

模式操作通常不在应用代码中进行，因为模式是长期存在的数据库对象。然而，能够不时地创建和删除模式仍然是有用的，例如在实用程序脚本中。

创建模式的方法是create_schema()，它是会话对象的一部分。它只需要一个参数:要创建的模式的名称。创建py_test_db模式的一个例子是

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

schema = db.create_schema("py_test_db")

db.close()

create_schema()方法为模式返回一个mysqlx.crud.Schema类的对象，该对象可用于操作模式对象，例如创建或查找集合，这将在下一章中讨论。

如果您将它与一个CREATE SCHEMA SQL 语句进行比较，会发现两个明显的差异。首先，没有为模式设置默认字符集的选项。用create_schema()创建的模式的默认字符集总是用character_set_server MySQL 服务器选项指定的。同样，collation_server MySQL 服务器选项的值用于指定默认的模式排序规则。

另一个区别是没有与CREATE SCHEMA的IF NOT EXISTS子句等价的子句。同样，drop_schema()也没有对应的IF EXISTS。相反，即使模式已经存在，create_schema()也会成功。事实上，上述示例执行的底层 SQL 语句是

CREATE DATABASE IF NOT EXISTS `py_test_db`;

因此，IF NOT EXISTS子句将始终被包含在内。这并不意味着建议只使用create_schema()来获取现有的模式。CREATE DATABSE IF NOT EXISTS导致了不必要的开销，并且使得代码的意图不那么清晰。相反，使用两种专用方法之一来获取现有模式的模式对象。

正在检索默认架构

实际应用中最常见的情况是模式已经存在，应用需要获得一个模式对象来操作模式，或者更深入地获得一个集合或一个表来使用。有两种方法可以在 X DevAPI 中获得模式对象:要么请求创建会话时指定的默认模式，要么通过模式名请求它。

创建会话时，可以使用schema选项指定默认模式。要获得默认模式的模式对象，可以使用get_default_schema() session 方法。例如，考虑一个使用默认的py_test_db模式创建的会话:

import mysqlx
from config import connect_args

db = mysqlx.get_session(
  schema="py_test_db",
  **connect_args
)

print("Retrieving default schema")

schema = db.get_default_schema()

print("Schema name: {0}"
  .format(schema.name)
)

db.close()

创建连接时添加了schema='py_test_db'选项，然后使用get_default_schema()方法检索模式对象。最后，使用模式的name属性打印模式名，并关闭会话。输出是

Retrieving default schema
Schema name: py_test_db

get_default_schema()方法是检索默认模式的好方法，例如，如果在编写应用时不知道模式名。但是，在其他情况下，无论默认模式如何，都有必要检索特定的模式。让我们看看这是如何做到的。

按名称检索模式

按名称检索特定模式类似于创建新模式。不同的是使用了get_schema()方法，而不是create_schema()。需要一个参数:name，它是一个带有模式名称的字符串。考虑下面的例子来获取py_test_db模式的模式对象:

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

print("Retrieving non-default schema")

schema = db.get_schema("py_test_db")

print("Schema name: {0}"
  .format(schema.name)
)

db.close()

创建会话后，将检索模式并打印名称。这个例子与前面使用get_default_schema()的例子非常相似，除了模式的名称是在检索时指定的，而不是在创建会话时指定的。该示例的输出与前面的类似:

Retrieving non-default schema
Schema name: py_test_db

操作模式的最后一步是删除模式。

删除模式

删除模式的方式与创建模式的方式非常相似。该会话包括drop_schema()方法，该方法接受要删除的模式的名称。删除本节中使用的py_test_db模式的一个例子是

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

db.drop_schema("py_test_db")

db.close()

与本节前面的create_schema()示例相比，这里唯一的区别是使用了drop_schema()方法。与创建模式类似，如果模式不存在，也不会出现错误。也就是说，底层 SQL 语句是

DROP DATABASE IF EXISTS `py_test_db`;

与会话一样，mysqlx.crud.Schema类也有一些实用方法和两个属性。它们是模式对象最后要考虑的事情。

其他模式方法和属性

mysqlx.Schema类包括几个方法和三个属性，对于检查模式对象的状态很有用。它们包括关于模式名、模式是否存在等信息。此外，还有一些方法可以获取底层连接和会话。所有这些都将在这里讨论。

表 6-5 总结了讨论中涉及的模式方法。返回的对象都是相对于mysqlx模块的。这些方法都不带任何参数。

表 6-5。

模式实用程序方法

方法

返回对象

描述

| | --- | --- | --- | | exists_in_``databas | | 根据模式是否存在，返回True或False。 | | get_``connectio | connection.Connection | 获取会话的基础连接。 | | get_``nam | | 返回架构的名称。这与使用name属性是一样的。 | | get_``schem | crud.Schema | 这将返回模式本身。这与使用schema属性是一样的。它在应用中并不常用。 | | get_``sessio | connection.Session | 返回架构的会话对象。当模式作为参数传递给函数时，这很有用。在 8.0.12 和更高版本中，也可以从会话属性中检索会话。 |

最常用的方法是获取名称(get_name())或检查模式是否实际存在(exists_in_database())。该名称也可以使用name属性获得。类似地，可以使用get_session()方法或者在 MySQL Connector/Python 8.0.12 和后来的 session 属性中检索会话。此外，模式本身存储在schema属性中；但是，在应用中通常不使用这种方法。

在继续讨论 CRUD 参数之前，有必要看一个结合了本节中讨论的特性的示例。

模式示例

到目前为止，这些都有点抽象，只有很少的代码示例。因此，让我们来看一个扩展的例子，它将讨论的几件事情放在一起，并展示了方法和属性是如何关联的。清单 6-1 使用默认模式，然后使用实用程序和模式操作方法进行调查并采取行动。

import mysqlx
from config import connect_args

# Create the session
db = mysqlx.get_session(
  schema="py_test_db",
  **connect_args
)

# Retrieve the default schema
# (py_test_db)

py_schema = db.get_default_schema()

print("Schema name: {0} - Exists? {1}"
  .format(
    py_schema.name,
    py_schema.exists_in_database()
  )
)

# If py_test_db does not exist,
# create it
if (not py_schema.exists_in_database()):
  db.create_schema(py_schema.name)

print("Schema name: {0} - Exists? {1}"
  .format(
    py_schema.name,
    py_schema.exists_in_database()
  )
)

# Get the world schema

w_schema = db.get_schema("world")

print("Schema name: {0} - Exists? {1}"
  .format(
    w_schema.name,
    w_schema.exists_in_database()
  )
)

# Get the session object of the world
# schema and see if it is the same as
# the db object.

w_session = w_schema.get_session()

print("db == w_session? {0}".format(
  db == w_session))

# Drop the py_test_db schema.

db.drop_schema(py_schema.name)

print("Schema name: {0} - Exists? {1}"
  .format(
    py_schema.name,
    py_schema.exists_in_database()
  )
)

db.close()

Listing 6-1.Manipulating and Checking Schemas

该示例首先创建一个会话，其中默认模式被设置为py_test_db，并且使用get_default_schema() session 方法来获取py_test_db模式的模式对象。schema 对象的name属性和exists_in_database()方法用于打印名称和它是否存在。每当 schema 对象发生变化时，就会在整个示例中重复这一过程。

如果py_test_db模式不存在，则创建它。在调用create_schema()方法之前，实际上并不需要检查模式是否不存在，但是这使得意图更加清晰。

然后检索第二个模式对象，这次使用get_schema()方法为world模式获取一个对象。使用get_session()方法检索新创建的w_schema对象的会话，并确认db和w_session对象是相同的(因为它们的身份或内存地址是相同的)。

最后，使用drop_schema()方法再次删除py_test_db模式，使数据库保持与示例之前相同的状态(假设py_test_db模式在开始时不存在)。假设py_test_db不存在，而前面章节中的world数据库存在，运行该示例的输出是

Schema name: py_test_db - Exists? False
Schema name: py_test_db - Exists? True
Schema name: world - Exists? True
db == w_session? True
Schema name: py_test_db - Exists? False

输出显示开始时py_test_db并不存在，但是在create_schema()调用之后它存在了。由于您在第一章中手动加载了world模式，所以它没有被创建就存在了。当比较会话的两个副本时，您可以看到它们是相同的。最后，在删除了py_test_db模式之后，exists_in_database()方法再次返回False。

为了完成这一章，你将会看到下两章中讨论的方法的三个共同点:首先，CRUD 方法使用的参数。

混乱的争论

将在第七章和第八章中讨论的 CRUD 方法都使用一组有限的参数。与其一遍又一遍地解释这些论点是什么意思，不如让我们现在就来讨论一下。

CRUD 方法使用以下四种参数类型:

文档
文档 id
情况
田地（复数）；场；域；字段

文档和文档 id 是文档存储所独有的，而字段和条件是两者共享的。下面的讨论将会看到这四种类型的每一种，并探究它们的用法。

文档

文档是存储在文档存储中的数据的容器。在 MySQL 中，文档存储为 JavaScript 对象符号(JSON)文档。在 Python 中，JSON 可以表示为一个字典，其中 JSON 对象是带有值的字典键。JSON 数组在 Python 中被创建为一个列表。

小费

如果您想了解更多关于 JSON 文档的信息，一些参考资料有 https://json.org/ 、 https://en.wikipedia.org/wiki/JSON 和 https://dev.mysql.com/doc/refman/en/json.html 。

作为一个例子，考虑一个名为 John Doe 的雇员，他目前是团队领导。他有一个经理，之前担任过开发人员(2010-2014 年)和高级开发人员(2014-2017 年)。代表这一点的文件是

document = {
  "_id"        : "10001",
  "Name"       : "John Doe",
  "Manager_id" : "10000",
  "Title"      : "Team Lead",
  "Previous_roles": [
    {
      "Title"      : "Developer",
      "Start_year" : "2010",
      "End_year"   : "2014"
    },
    {
      "Title"      : "Senior Developer",
      "Start_year" : "2014",
      "End_year"   : "2017"
    },
  ]
}

文档从字典开始，顶层是关于雇员的所有标量数据，如姓名、经理等。该员工在公司的历史表现为一个列表，其中包含该员工以前担任的每个角色的字典。

数组中的数据不需要有结构。例如，如果存储了一个人最喜欢的水果，它们可以表示为一个简单的列表，每个水果指定为一个字符串。

文档中的一个特殊字段是_id元素。这是文档 ID，是文档的唯一标识符(主键)。让我们来看看文档 ID。

文档标识

文档 ID 是 MySQL 用来唯一标识文档的。它也是底层存储中用作主键的内容。所有文档都必须有文档 ID。如果在连接到 MySQL Server 8.0 时没有明确提供，它将自动生成。

文档 ID 是一个(最多)32 字节长的二进制字符串。如果没有提供 ID，MySQL Server 8.0 将使用三个部分创建一个 ID:

一个前缀:这是一个十六进制编码的无符号整数。它是 0，除非它是由数据库管理员设置的，或者 MySQL 服务器是 InnoDB 集群组的一部分。前缀存储在mysqlx_document_id_unique_prefix MySQL 服务器选项中。
一个时间戳:MySQL 服务器实例最后一次以十六进制编码启动的时间。
一个自动递增计数器:这是一个十六进制编码的无符号整数。初始值是 MySQL 服务器选项auto_increment_offset的值，然后随着生成的每个 ID 增加auto_increment_increment。

用mysqlx_document_id_unique_prefix = 5678生成的 ID 的一个例子是162e5ae987780000000000000003。前缀是前四个十六进制数字(162e)，后面是时间戳；末尾的3显示它是 MySQL 服务器上次重启后生成的第三个 ID。

以这种方式生成的 id 是经过专门设计的，因此它们可以是全局唯一的，同时还针对 InnoDB 存储引擎进行了优化，并且生成成本低廉。当使用单调递增的主键时，InnoDB 的性能最佳。自动生成的文档 id 为此进行了优化。

从表面上看，自然选择可能是 UUID，但它们不是单调递增的。即使您交换了 UUID 的高位和低位时间部分，如果有来自多个连接主机的应用实例，您也会得到交错的值。这就是开发三部分 ID 的原因。

下一个要讨论的参数类型是条件。

情况

条件用于过滤哪些文档应该受到操作的影响，并用于文档存储和 SQL 表的 CRUD 方法。对于读取操作，只返回与过滤器匹配的文档；对于更新和删除操作，条件指定应该更改哪些文档。条件的 SQL 等价物是一个WHERE子句。除了 8.0.12 和更高版本中集合的modify()之外，接受条件作为参数的方法还在其语句对象中提供了一个可以替代使用的where()方法。

该条件编写起来相当简单，并且使用了与 MySQL SQL WHERE子句相同的语法。例如，要包含所有Office字段设置为 Sydney 的文档，可以使用以下条件:

Office = 'Sydney'

SQL 表和文档之间的一个区别是，在过滤字符串数据类型时，MySQL 默认将WHERE子句视为不区分大小写，而文档存储总是区分大小写。对此的解释是，JSON 文档被存储为二进制对象，因为否则就不可能在文档中存储任意值。

注意

MySQL WHERE子句和 SQL 表默认执行不区分大小写的匹配，但是文档存储总是区分大小写的。

最后一个参数类型是 fields，它与 CRUD 方法一起用于指定要返回或修改的内容。

菲尔茨

例如，fields 参数指定在 select 语句中返回哪些字段，或者在 insert 语句中为哪些字段设置值。fields 参数类型也用在一些语句方法中，如FindStatement.fields()方法。在 SQL 语言中，字段等同于列。

每个字段都表示为一个字符串，带有要包含或设置其值的字段的名称。对于 JSON 文档，该字段是指向该字段在 JSON 文档中的位置的 JSON 路径。文档本身由$表示。然后通过指定由句点(.)分隔的元素来构建路径。除了在集合上创建索引时，前导的$.是可选的。

可以使用*作为通配符。指定.*意味着对象的所有成员都匹配。或者，您可以使用语法[prefix]**{suffix}，其中前缀是可选的，后缀是强制的。这将匹配以前缀开始并以后缀结束的所有内容。

对于数组，方括号可用于指定要包含哪些元素。[N]返回数组的第 N 个元素(从 0 开始)。使用[*]等同于根本不指定索引(即返回整个数组)。

小费

在 MySQL 中指定 JSON 路径的规则记录在 https://dev.mysql.com/doc/refman/en/json-path-syntax.html 中。

这些字段可以指定为单个参数、元组或列表。select 语句的以下三个初始化做同样的事情:

stmt = city.select("Name", "District")

fields = ("Name", "District")
stmt = city.select(fields)

fields = ["Name", "District"]
stmt = city.select(fields)

关于 CRUD 方法使用的参数的讨论到此结束。接下来，您将看到查询的中间部分:语句对象。

声明

下两章将要讨论的大多数方法都涉及到一个语句对象。这用于细化查询并执行它。表面上不涉及语句对象的方法，比如count()方法，仍然在幕后使用语句。

语句对象是完成大部分查询工作的地方。例如，您可以使用 statement 对象为一个find()查询设置过滤条件，或者限制结果中的文档数量。表 6-6 总结了接下来两章将遇到的语句类。

表 6-6。

语句类

班级

范围

create, read, update, and delete

方法

| | --- | --- | --- | --- | | AddStatement | 募捐 | 创造 | add() | | FindStatement | 募捐 | 阅读 | find() | | ModifyStatement | 募捐 | 更新 | modify() | | RemoveStatement | 募捐 | 删除 | remove() | | InsertStatement | 桌子 | 创造 | insert() | | SelectStatement | 桌子 | 阅读 | select() | | UpdateStatement | 桌子 | 更新 | update() | | DeleteStatement | 桌子 | 删除 | delete() | | SqlStatement | 结构化查询语言 | | sql() |

该类是为该方法返回的mysqlx.statement模块中的类。范围指定它是用于基于集合、基于表还是基于 SQL 的查询。CRUD 列显示了相应的 CRUD 操作。最后,“方法”列列出了用于创建语句的方法。

具体的报表方法将在讨论各自的方法时介绍。但是，有些特性是所有或几个语句共有的。表 6-7 中列出了这些常用方法。

表 6-7。

方法来获取有关语句的信息

方法

声明

返回类型

描述

| | --- | --- | --- | --- | | get_binding_``ma | 阅读更新删除 | 词典 | 返回带有绑定映射的字典。 | | get_``binding | 阅读更新删除 | 目录 | 返回绑定列表。每个绑定由一个带有名称和值的字典表示。 | | get_``groupin | 阅读更新删除 | 目录 | 返回用于对结果进行分组的表达式。 | | get_``havin | 阅读更新删除 | protobuf.Message对象 | 返回 having 筛选器的对象。 | | get_limit_``offse | 阅读更新删除 | Integer | 返回限制的偏移量。 | | get_limit_row_``coun | 阅读更新删除 | Integer | 返回查询返回的最大文档数。 | | get_projection_``exp | 阅读更新删除 | List | 返回包含字段投影映射的列表。 | | get_sort_``exp | 阅读更新删除 | List | 返回一个列表，其中包含用于对结果进行排序的表达式。 | | get_``sq | 挑选 | String | 根据当前语句定义返回 SQL 语句。 | | get_update_``op | 更新 | UpdateSpec对象 | 返回包含更新操作的列表。 | | get_``value | 创造 | List | 返回该语句将创建或已经创建的值的列表。 | | get_where_``exp | 阅读更新删除 | protobuf.Message object | 返回 where 筛选器的对象。 | | is_doc_``base | 全部 | Boolean | 对于基于集合的语句，总是返回True，对于基于表和基于 SQL 的语句，总是返回False。 | | is_lock_``exclusiv | 阅读 | Boolean | 如果请求了独占锁，则返回True。 | | is_lock_``share | 阅读 | Boolean | 如果请求了共享锁，则返回True。 | | is_``upser | 创造 | Boolean | 如果语句执行 upsert(即，如果记录已经存在则替换，否则添加新文档)操作，则返回True。对于 insert 语句，它总是返回False。第七章包括一个 upsert 的例子。 |

Statements列显示了该方法适用于哪些语句类。在大多数情况下，语句类将由具有该方法的 CRUD 操作指定；例如，“读取”意味着两个读取方法(Collection.find()和Table.select())在返回的语句对象中有它。两个特殊的值是“All”，这意味着它适用于所有的语句类型，以及“Select”，这意味着它只适用于SelectStatement对象(来自Table.select())。

get_having()和get_where_expr()方法返回一个mysqlx.protobuf.Message类的对象。get_update_ops()方法返回一个mysqlx.statement.UpdateSpec类的对象。这本书不会深入讨论如何使用这个类的任何细节。

一旦执行了一个语句，就会返回一个结果对象。返回的结果对象将取决于语句类。

结果

执行查询时，会返回一个结果对象。result 对象的确切性质取决于查询以及它是使用集合、SQL 表还是 SQL 语句。本节将讨论与 X DevAPI 一起使用的各种结果对象。

对于文档存储 CRUD 语句，对于不返回任何数据的查询，例如添加文档时，返回一个result.Result类的对象。对于返回数据的查询，返回一个result.DocResult类的对象。唯一的例外是count()方法，它直接以整数形式返回集合中的文档总数。

对于 SQL 表，模式是类似的，除了 select 语句的结果对象属于result.RowResult类。SQL 语句总是以一个result.SqlResult对象结束。

表 6-8 总结了哪个方法返回哪个结果对象。如何得到结果和使用结果的例子将在接下来的两章中给出。

表 6-8。

语句类型到结果对象的映射

语句类型

收集

桌子

结构化查询语言

| | --- | --- | --- | --- | | CRUD–创建 | Result | Result | | | CRUD–读取 | DocResult | RowResult | | | CRUD–更新 | Result | Result | | | CRUD–删除 | Result | Result | | | 结构化查询语言 | | | SqlResult |

这四个结果类值得仔细研究一下。下面的讨论将从较高的层次来看结果类。在 CRUD 和 SQL 方法的讨论中，将给出使用这些结果的示例。

结果。结果

result.Result类用于没有结果集的 CRUD 语句，并提供关于查询的元数据。例如，在将文档插入到集合中之后，它将包含关于插入的文档数量的信息。它既用于基于集合的语句，也用于基于表的语句。

表 6-9 概述了result.Result类的一些最重要的方法，包括它们返回的内容。这些方法都不带任何参数。

表 6-9。

result.Result类的重要方法

方法

数据类型

描述

| | --- | --- | --- | | get_affected_items_``coun | 整数 | 返回受查询影响的文档数或行数，例如插入或更新了多少文档。 | | get_autoincrement_``valu | 整数 | 返回为表插入语句生成的最后一个自动递增 ID。这在插入单行时非常有用。它仅适用于表格对象。 | | get_generated_``id | 字符串列表 | 返回由查询插入到集合中的所有文档 id。它仅适用于集合对象。 | | get_``warning | 元组列表 | 返回查询生成的警告。 | | get_warnings_``coun | 整数 | 返回查询发生的警告数。 |

这些方法与mysql.connector模块中光标可用的方法相似或提供相似的数据。

结果。文档结果和结果。RowResult

result.DocResult和result.RowResult类分别用于Collection.find()和Table.select()方法。这些类的工作方式类似于在mysql.connector模块中使用光标来处理查询结果。

表 6-10 总结了result.DocResult和result.RowResult类最重要的方法。这些方法都不带任何参数。

表 6-10。

result.DocResult和result.RowResult类的重要方法

方法

数据类型

描述

| | --- | --- | --- | | fetch_``al | 文件清单 | 返回结果集中所有剩余的文档。列表中的每个元素都是表后描述的mysqlx.dbdoc.DbDoc或mysql.result.Row类的实例。 | | fetch_``on | 目标 | 返回结果集中的下一个文档或行，如果已检索到所有文档/行，则返回 None。返回的对象类型在表后讨论。 | | get_columns | 对象列表 | 从 column 属性返回列信息。它只存在于result.RowResult类。在 8.0.12 版本中添加。 | | get_``warning | 元组列表 | 返回查询生成的警告。 | | get_warnings_``coun | 整数 | 返回查询发生的警告数。 |

除了列出的方法之外，还有count属性，它被设置为使用fetch_all()方法检索的文档总数。result.RowResult类还包括 columns 属性，该属性包括与第 3 和 4 章中讨论的列信息类似的信息；在版本 8.0.12 和更高版本中，也可以使用get_columns()方法检索列。由fetch_one()返回的对象类型以及组成由fetch_all()返回的列表取决于语句类型:

Collection.find():该对象属于mysqlx.dbdoc.DbDoc类
Table.select():mysqlx.result.Row类的对象

在这两种情况下，对象的行为都像一个字典，因此在使用返回的文档时不需要特别考虑。

使用的最后一个结果类是SqlResult，用于所有 SQL 语句。

结果。SqlResult

X DevAPI 的 MySQL 连接器/Python 实现在确定为 SQL 语句返回哪种类型的结果对象时，不区分SELECT类型查询和其他查询。返回的总是result.SqlResult类的对象。

表 6-11 总结了SqlResult类最重要的方法。

表 6-11。

result.SqlResult类的重要方法

方法

争吵

数据类型

描述

| | --- | --- | --- | --- | | fetch_``al | | 行列表 | 返回结果集中剩余的所有行。每一行都是mysql.result.Row类的一个实例。 | | fetch_``on | | result.Row | 返回结果集中的下一行，如果检索了所有行，则返回 None。 | | get_autoincrement_``valu | | 整数 | 如果查询是 insert 语句，则返回最后生成的自动递增 ID。这在插入单行时非常有用。 | | get_columns | | 对象列表 | 从 column 属性返回列信息。在 8.0.12 版本中添加。 | | get_``warning | | 元组列表 | 返回查询生成的警告。 | | get_warnings_``coun | | 整数 | 返回查询发生的警告数。 | | has_data | | 布尔代数学体系的 | 返回查询是否有结果集。对于不返回任何行的选择查询，该值为 False。在 8.0.12 版本中添加。 | | index_``o | col_name | 整数 | 返回具有指定名称的列的数字索引。 | | next_``resul | | 布尔代数学体系的 | 当查询生成多个结果集时，重新初始化 result 对象以处理下一个结果集。如果有另一个结果要处理，则返回True；否则False。 |

此外，SqlResult类有两个有用的属性:

列:结果的列列表
计数:用fetch_all()方法检索到的项目总数

关于 X DevAPI 使用的结果类的讨论到此结束。在接下来的两章中，当你看如何执行语句时，它们会再次出现。

摘要

本章介绍了 MySQL X DevAPI。它首先简要概述了 X 插件、X 协议、X DevAPI 和mysqlx模块。为了让 MySQL Connector/Python 能够使用 X DevAPI 执行查询，需要在 MySQL 服务器上安装 X 插件。在 MySQL Server 8.0.11 和更高版本中，这是默认情况。

本章的其余部分讨论了与 X DevAPI 的哪一部分无关的通用特性。程序从创建一个可用于创建、获取和删除模式的会话开始。模式对象是使用 CRUD 方法的下一步。工作流的最后一部分是获取结果对象，这样就可以检查查询的结果或使用结果集。

在创建会话并可能获得模式对象之后，本章没有讨论中间的大部分。这就是有趣的部分所在(定义和执行语句),也是接下来两章的内容。下一章着眼于如何在 MySQL 文档存储中使用 X DevAPI。

Footnotes 1

也就是说，每种语言都保持其特性，例如，关于命名约定，以及是否使用 setter 和 getter 方法而不是属性。所以，还是有一些区别的。

七、MySQL 文档存储

顾名思义，MySQL 传统上就是使用 SQL 语句来执行查询。这不仅反映在用于描述查询应该做什么的语言中，还反映在数据的基本构造方式中。MySQL 文档存储颠倒了这一点，不仅提供 NoSQL 数据库，还使用 JSON 文档，就像其他用于存储数据的文档存储一样。

文档存储不会替换 SQL 数据库。这两者应该共存，所以您可以使用最适合您的应用和数据的那一个。您甚至可以将两者混合使用，因此一些数据存储在传统的 SQL 表中，而另一些数据存储为文档。

本章将探讨如何使用 MySQL 连接器/Python 和 X DevAPI 来处理 MySQL 文档存储。

MySQL 文档存储

MySQL 文档存储是 X 插件的一部分，该插件在 MySQL Server 5.7.12 中作为测试版特性引入，在 MySQL Server 8.0 中成为 GA。文档存储将数据存储为 JSON 文档，但使用 InnoDB 存储引擎来提供事务支持等特性。

注意

文档存储本身就是一个很大的主题，给出全面的介绍超出了本书的范围。如果您打算使用文档存储，建议您阅读更多关于它的内容。两个优秀的参考是介绍查尔斯·贝尔的 MySQL 8 文档库(https://www.apress.com/gp/book/9781484227244)和 MySQL 参考手册 ( https://dev.mysql.com/doc/refman/en/document-store.html )。

虽然 X 协议和文档存储的细节将留给读者作为练习，但在继续之前，有几个特征值得考虑。顾名思义，数据存储在文档中。这意味着与普通关系模式不同，所有“列”都存储在同一个数据对象中。

在文档存储中，据说文档存储在一个集合中。文档使用 JSON 格式。如果您想到一个常规 MySQL 表中的一行，列名是 JSON 文档中每个对象的名称，列值是对象的值。与 SQL 表不同，不要求每个文档(“行”)都有相同的字段或包含相同类型的数据。这些文档被称为集合的一部分(SQL 术语中的“表”)。

警告

与其他文档存储一样，MySQL 文档存储是无模式的。从开发的角度来看，这似乎是一个非常吸引人的特性；事实上，这使得向应用添加新类型的数据变得更加容易。但是，它也消除了数据库层验证数据和检查约束的机会。因此，如果您选择无模式数据模型，那么确保数据一致性完全取决于开发人员。

所有文档都必须有一个惟一的键，这个键总是 JSON 文档中带有键_id的对象。通常通过在保存集合的表上创建虚拟列来支持索引。X DevAPI 支持为集合创建索引，这是操作索引的首选方式。如果您需要为集合创建或删除索引，MySQL Shell 非常有用。

现在让我们看看在文档存储中使用集合时的一般工作流。

小费

本章中有许多示例程序。列表中出现的所有示例程序都可以下载。有关使用示例程序的更多信息，参见第一章中对示例程序的讨论。一般来说，更改会被回滚，以允许重新执行示例并得到相同的结果。清单 7-3 中加载样本数据时例外。

工作流程

工作流程在前一章中已经暗示过了，但还是值得更详细地看一下。让我们集中讨论如何使用集合。通过架构获取集合，并从集合中创建语句。最后，语句返回结果。当然，还有更多的内容，这一节将详细介绍。这不是一个详尽的讨论；相反，它的意思是作为本章其余部分的概述。

从方案开始到结果结束的工作流程如图 7-1 所示。主要关注获取集合和创建语句的步骤。省略了细化和执行语句的细节，但将在本章稍后针对每种查询方法进行讨论。红色(深灰色)框是被调用的方法。

img/463459_1_En_7_Fig1_HTML.jpg

图 7-1

使用集合时的工作流

正如您将在本章前半部分看到的，集合是使用get_collection()、get_collections()或create_collection()从模式中获得的。该架构也可用于删除集合。在集合对象中，一种可能是创建和删除索引。

另一种可能性是执行查询，这将在本章的后半部分讨论。有许多方法可以执行查询。主要有add()、find()、modify()、remove()。它们返回一条语句，一旦该语句被执行，该语句又返回一个结果。但是，也有三个补充方法将所有步骤结合起来，直接返回一个结果。最后，还有count()方法，图中没有包括。

工作流就绪后，您就可以开始研究集合的工作方式了。

收集

在前一章中，您已经看到了如何从会话中创建模式。然而，模式只是真正重要的东西的容器:集合和表。在关系数据库中，我们谈论数据存储在表中。X DevAPI 也可以处理表(它们将在下一章讨论)，但是目前我们将坚持使用文档存储。文档存储中的表的等价物是集合。

这一节将介绍如何创建、维护和删除不再需要的集合。该流程将类似于用于模式的流程。实际的集合操作是使用模式对象上的方法完成的，就像模式操作是使用会话方法完成的一样。集合对象本身也以与架构相同的方式包含方法和属性。数据的实际操作将推迟到下面的部分。第一步是创建、获取和删除集合。

集合操作

创建和删除集合不是应用最常见的任务，但它们可能是部署脚本或实用程序的一部分，应用可能暂时需要一个集合，或者您可能使用 MySQL Shell 来优化集合的性能。在所有情况下，应用都需要获得一个集合对象，以便能够对它执行查询。创建、获取、删除和创建集合索引是本次讨论的主题。

集合的操作方式与模式非常相似。例如，要创建一个模式，可以使用 session create_schema()方法。同样，您可以使用 schema create_collection()方法创建一个集合。这使得学习如何操作集合变得很容易。将要讨论的模式收集方法在表 7-1 中列出。返回的对象是相对于mysqlx模块的。

表 7-1

架构收集方法

方法

争论

描述

| | --- | --- | --- | | create_collection | name | 创建一个名称指定为参数的集合。集合作为一个crud.Collection对象返回。如果集合存在，就会出现一个ProgrammingError异常，除非reuse为True，在这种情况下，将返回现有的集合。 | | reuse=False | | drop_collection | name | 删除具有指定名称的集合。不要求集合存在。 | | get_collection | name | 将具有指定名称的集合作为crud.Collection对象返回。如果check_existence = True，如果集合不存在，则发生ProgrammingError异常。 | | check_existence=False | | get_collections | | 返回架构中集合的列表。列表中的每个元素都是mysqlx.crud.Collection类的一个实例。 |

该表显示，与模式相比，只有一些差异:主要是在创建集合时，可能会影响集合是否必须不存在，以及在获取集合时，集合是否必须存在。此外，还将讨论表 7-2 中创建和删除索引的两种收集方法。这两种方法都不返回值。

表 7-2

创建和删除索引的集合方法

方法

争论

描述

| | --- | --- | --- | | create_ index | index_name | 创建一个索引，该索引根据index_name的值命名，并由fields_desc中指定的字典定义(“desc”代表描述)。集合的索引名称必须是唯一的。**注意:**调用create_index()只定义索引，返回一个statement.CreateCollectionIndexStatement对象。要创建索引，对返回的对象调用execute()方法。 | | fields_desc | | drop_ index | index_name | 删除参数中指定名称的索引。 |

用于定义新索引的字典具有以下结构:

{
  "type"   : INDEX|SPATIAL,
  "unique" : False,
  "fields" : [...]
}

普通索引的类型可以是INDEX(这是默认设置)，几何索引的类型可以是SPATIAL。目前不支持全文索引。unique元素定义了它是否是唯一的索引。默认值是False，这也是当前唯一支持的值(即当前不支持唯一索引)。fields元素包含了要包含在索引中的字段列表。每个字段是一个字典，包含表 7-3 中的元素。

表 7-3

定义索引中的字段的字典

元素

缺省值

类型

描述

| | --- | --- | --- | --- | | Field | | 线 | 如何在文档中查找值的定义。例如，要获得名为Name的顶级对象，使用$.Name。 | | type | | 线 | 类似于表列的数据类型。此表后面给出了支持类型的示例。 | | required | False | 布尔代数学体系的 | 该值是否必须存在于文档中(“NOT NULL”)。对于 GeoJSON 类型，这必须设置为True。 | | collation | | 线 | 类型为TEXT(…)时的排序规则。仅当类型为TEXT类型时才能设置。从 MySQL Server 8.0.11 开始，索引不支持自定义排序规则，因此该值被忽略。 | | options | 1 | 整数 | 创建空间索引时，ST_GeomFromGeoJSON()函数的options参数。它只允许用于空间索引。支持的值为 1、2、3 和 4。它定义了文档包含大于 2 的维值时的行为。 | | sri d | 4326 | 整数 | 创建空间索引时，ST_GeomFromGeoJSON()函数的srid(空间参考系统标识符)参数。它只允许用于空间索引。这必须是一个无符号的 32 位整数。information_schema中的ST_SPATIAL_REFERENCE_SYSTEMS表包括 MySQL 支持的所有空间参考系统。SRIDs 的两个参考是 https://epsg.io/ 和 http://spatialreference.org/ 。 |

字段定义中总是需要field和type元素，而其余元素取决于创建的索引。一些常用的type值有

INT:指定一个有符号整数。变奏包括TINYINT、SMALLINT、MEDIUMINT、BIGINT。要获得无符号值，请添加UNSIGNED，例如INT UNSIGNED。
FLOAT:四字节浮点数。
DOUBLE:八字节浮点数。
DECIMAL:定点(精确)数。可以选择指定精度。默认精度是(10,0)。
DATE:一个日期。
TIME:一个时间规格。
TIMESTAMP:由日期和时间组成的时间戳。范围是从 1970 年 1 月 1 日午夜后一秒到 2038 年 1 月 19 日 03:14:07 UTC。为秒添加的小数位数可以在括号中指定，例如TIMESTAMP(3)具有毫秒精度。
DATETIME:类似于时间戳，但支持从 1000 年 1 月 1 日午夜到 9999 年 12 月 31 日白天结束的范围。为秒添加的小数位数可以在括号中指定，例如DATETIME(3)具有毫秒精度。
YEAR:四位数的年份，例如 2018。
BIT:指定一个位值。
BLOB:用于存储二进制对象(即没有字符集的字节)。必须指定索引值的最大大小，例如BLOB(50)索引值的前 50 个字节。不支持TINYBLOB、SMALLBLOB、MEDIUMBLOB、LONGBLOB的变化。
TEXT:用于存储字符串。这需要指定排序规则。必须指定要索引的值的最大大小，例如TEXT(50)来索引值的前 50 个字符。不支持TINYTEXT、SMALLTEXT、MEDIUMTEXT、LONGTEXT的变化。
GEOJSON:使用 GeoJSON 格式的空间值。使用时，required必须是True，并且可以设置options和srid元素。GeoJSON 格式仅支持空间索引。使用ST_GeomFromGeoJSON()函数( https://dev.mysql.com/doc/refman/en/spatial-geojson-functions.html#function_st-geomfromgeojson )从文档中提取 GeoJSON 值。options和srid值是该功能支持的值。

值得注意的是，类型本身不能是 JSON 仅支持标量类型。

小费

如果您想深入了解索引规范的需求，请参见 MySQL Connector/Python 中的mysqlx/ lib/mysqlx/statement.py文件中的CreateCollectionIndexStatement类和 MySQL 服务器源代码中的plugin/x/src/admin_cmd_index.cc文件中的Admin_command_index::Index_field::create()方法。

本节后面将有一个创建索引的示例。下面的讨论将依次讨论这四种方法，从创建集合的任务开始。

创建收藏

使用create_collection()方法创建一个集合，该方法将集合的名称作为第一个参数或作为name关键字参数。还有第二个可选参数reuse，默认为False。如果reuse为False，则集合不存在或者发生ProgrammingError异常。如果reuse为True，则不存在则创建集合；否则，将返回现有集合。在大多数情况下，最好让reuse保持默认值，这样就可以知道是否存在同名的现有集合。否则，很容易出现细微的错误。

以下示例显示了如何在py_test_db模式中创建集合my_docs:

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

# Reset the py_test_db schema
db.drop_schema("py_test_db")
schema = db.create_schema("py_test_db")

docs = schema.create_collection("my_docs")

db.close()

该示例首先删除了py_test_db模式，以确保您从一个空模式开始。create_collection()方法返回一个集合对象，所以可以直接工作。

在mysql命令行客户端中使用SHOW CREATE TABLE语句显示表定义可能会很有趣:

mysql> SHOW CREATE TABLE py_test_db.my_docs\G
*************************** 1\. row ***************************
       Table: my_docs
Create Table: CREATE TABLE `my_docs` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

在创建任何辅助索引之前，这是文档存储中集合的标准表定义。doc列是文档(使用 JSON 数据类型)。_id列是一个生成的列，它从doc列中提取了_id对象。_ id列被用作主键。

这还显示了如何对文档中的值创建索引。对于每个索引，都会创建一个生成的列并用于索引。虽然主键必须是存储生成的列，但对于辅助索引，可以使用虚拟列。虚拟列的优点是它不需要任何存储，而是在需要时进行计算。但是，索引仍然使用与存储列时相同的空间。您将很快看到如何为集合创建额外的索引。

小费

要阅读有关生成的列的更多信息，请参见 MySQL 服务器参考手册中的 https://dev.mysql.com/doc/refman/en/create-table-generated-columns.html 和 https://dev.mysql.com/doc/refman/en/create-table-secondary-indexes.html 。

create_collection()方法总是将字符集设置为 utf8mb4 但是，请注意，JSON 数据类型是作为 BLOB 存储的(即，作为没有字符集的二进制数据)。主键始终是一个(最多)32 个字符长的二进制字符串，存储在文档内的_id对象中，并使用生成的列进行检索，以允许 MySQL 对其进行索引。

对于现有的集合，最好使用get_collection()或get_collections()。让我们看看它们是如何工作的。

检索单个集合

检索集合的最简单的情况是根据名称获取一个集合，这是使用get_collection()方法完成的。这使得控制获取哪些集合变得容易，并且为它们分配有意义的变量名也变得容易。

例如，考虑检索在前面的例子中创建的my_docs集合。这可以使用以下代码来完成:

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

schema = db.create_schema("py_test_db")

docs = schema.get_collection("my_docs")

print("Name of collection: {0}".format(
  docs.name))

db.close()

这个例子中唯一的新东西是将使用get_collection()方法检索的集合分配给docs变量的那一行。使用集合对象的name属性打印集合的名称:

Name of collection: my_docs

如果您需要检索许多集合，那么在一次调用中获取所有集合会很有用。这可以使用get_collections()来完成，如下所示。

检索架构中的所有集合

在某些情况下，应用可能会使用几个集合。一种解决方案是使用get_collection()逐个检索集合。不过，还有另一种方法。

另一种方法是使用get_collections()方法，它将所有集合作为一个列表返回。这是集合的name属性变得非常有用的情况之一，因为否则就不可能知道哪个集合对象包含哪个文档。

下面的代码显示了一个在具有两个集合的架构中检索集合的示例。这个例子类似于前两个例子，但是从删除模式开始，从头开始。示例代码是

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

# Reinitialize the py_test_db schema
db.drop_schema("py_test_db")
schema = db.create_schema("py_test_db")

# Create two collections
schema.create_collection("employees")
schema.create_collection("customers")

# Get all collections and print their
# names

collections = schema.get_collections()

for collection in collections:
  print("Collection name: {0}".format(
    collection.name))

# Create an index using the collection
# name as the key and the collection
# as the value

coll_dict = {

  collection.name: collection
    for collection in collections

}

db.close()

在用两个集合employees和customers建立了模式之后，使用get_collections()方法检索这两个集合。然后，代码遍历这些集合，并打印出每个集合的名称。此外，使用集合的名称作为键来创建字典。这使得以后检索特定集合更加容易。例如employees集合可以参照coll_dict["employees"]使用。该程序的输出是

Collection name: customers
Collection name: employees

下一个集合操作方法是drop_collection()方法，用于删除不再需要的集合。

删除收藏

当应用不再需要一个集合时，可以使用 schema 对象上的drop_collection()方法删除它。方法接受要删除的集合的名称。如果收藏不存在，drop_collection()会默默忽略。

删除本章中使用的所有三个集合的示例是

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

# Reinitialize the py_test_db schema
schema = db.get_schema("py_test_db")

# Drop all three collections that has
# been used in this section

schema.drop_collection("my_docs")

schema.drop_collection("employees")

schema.drop_collection("customers")

# For good measure also drop the schema
db.drop_schema("py_test_db")

db.close()

由于drop_schema()将删除模式中的所有集合(和表),drop_collection()调用是多余的，但是它们展示了如何一个接一个地删除集合。

与 SQL 查询一样，通过添加索引可以极大地提高文档集合的查询性能。这将在下一节中介绍。

创建索引

索引提供了值的搜索树，这些值通过引用包含这些值的实际文档来索引。这使得查找特定值比扫描所有文档并逐个检查它们是否满足要求要快得多。

注意

还有其他类型的索引，如全文索引。在撰写本文时，集合只支持 B 树(“普通”)和 R 树(空间)索引。

与常规的 SQL 表相比，为集合创建索引稍微复杂一些，因为除了索引定义本身之外，还需要定义如何从文档中检索值以及这些值代表什么。这是使用无模式数据存储的代价。

可以使用集合对象的create_index()方法定义索引。该方法的完整定义已在前面讨论过，不再重复。相反，我们来看一个例子。清单 7-1 展示了一个创建集合并添加三个索引的例子。

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

# Reinitialize employees collection in
# the py_test_db schema
schema = db.create_schema("py_test_db")
schema.drop_collection("employees")
employees = schema.create_collection(
  "employees")

# Define the three fields that will be
# used in the indexes.

field_name = {

  "field"     : "$.Name",
  "type"      : "TEXT(60)",
  "required"  : True,
  "collation" : "utf8mb4_0900_ai_ci",

}

field_office_location = {

  "field"     : "$.Office.Location",
  "type"      : "GEOJSON",
  "required"  : True,
  "options"   : 1,
  "srid"      : 4326,

}

field_birthday = {

  "field"     : "$.Birthday",
  "type"      : "DATE",
  "required"  : False,

}

# Create a normal index on the
# employee's name

index_name = "employee_name"

index_def =   {

  "fields" : [
    field_name
  ],
  "type"   : "INDEX",

}

index = employees.create_index(

  index_name, index_def)

index.execute()

print(
  "Index created: {0}".format(index_name)
)

# Create a spatial index for the
# location the employee work at.

index_name = "employee_office_location"

index_def =   {

  "fields" : [
    field_office_location
  ],
  "type"   : "SPATIAL",

}

employees.create_index(

  index_name, index_def

).execute()

print(
  "Index created: {0}".format(index_name)
)

# Create a normal index on the
# employee's birthday and name

index_name = "employee_birthday_name"

index_def =   {

  "fields" : [
    field_birthday,
    field_name
  ],
  "type"   : "INDEX",

}

index = employees.create_index(

  index_name, index_def)

index.execute()

print(

  "Index created: {0}".format(index_name)

)

db.close()

Listing 7-1Creating Indexes for a Collection

该示例从重新初始化集合开始。这确保了即使多次执行该示例，添加索引的起点也是相同的。然后定义将在索引中使用的三个字段(SQL 语言中的列)。这允许您根据需要重用它们。在这种情况下，您需要设置雇员的姓名和办公地点，但是生日是可选的。

创建的第一个索引位于Name字段。这是一个普通的索引，您指定应该使用utf8mb4_0900_ai_ci排序规则。索引宽度设置为 60 个字符。不用担心；仍然支持超过 60 个字符的名称。只是索引只考虑前 60 个字符。由create_index()创建的对象存储在index变量中，索引的实际创建由index.execute()执行。

第二个索引是雇员工作的办公室位置的空间索引。使用options和srid的默认值。在这种情况下，索引执行与定义索引相结合。

第三个索引结合了雇员的生日和姓名。这允许你搜索生日和名字。当应用多个条件时，将多个字段组合成一个索引是提高查询性能的有效方法。

对于三个索引中的每一个，在创建索引后都会打印一条确认消息:

Index created: employee_name
Index created: employee_office_location
Index created: employee_birthday_name

小费

如果您将最后一个索引创建为(Name，Birthday)，这将使仅基于名称的索引变得多余，因为使用组合索引也可以解决仅基于名称的搜索。这是因为 MySQL 允许您在索引的左前缀中搜索 B 树索引。组合多个字段时的另一个注意事项是，当最具选择性的值位于第一位时，可以获得最佳性能。

检查执行该示例产生的表定义可能会很有趣。稍微重新格式化的输出是

mysql> SHOW CREATE TABLE py_test_db.employees\G
*************************** 1\. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS
        (json_unquote(json_extract(`doc`,_utf8mb4'$._id')))
        STORED NOT NULL,
  `$ix_t60_r_4CB1E32CCBE4FE2585D3C8F059CB3A909FC536B7` text
        GENERATED ALWAYS AS
        (json_unquote(json_extract(`doc`,_utf8mb4'$.Name')))
        VIRTUAL NOT NULL,
  `$ix_gj_r_E933A4A981E8AB89AF33A3DB0B1D45F8E76A6E38` geometry
        GENERATED ALWAYS AS
        (st_geomfromgeojson(
           json_extract(`doc`,_utf8mb4'$.Office.Location'),1,4326)
        ) STORED NOT NULL /*!80003 SRID 4326 */,
  `$ix_d_CAA21771B5BB2089412F3D426AF25DEE3EDD1B76` date
        GENERATED ALWAYS AS
        (json_unquote(json_extract(`doc`,_utf8mb4'$.Birthday')))
        VIRTUAL,
  PRIMARY KEY (`_id`),
  SPATIAL KEY `employee_office_location`
        (`$ix_gj_r_E933A4A981E8AB89AF33A3DB0B1D45F8E76A6E38`),
  KEY `employee_name`
        (`$ix_t60_r_4CB1E32CCBE4FE2585D3C8F059CB3A909FC536B7`(60)),
  KEY `employee_birthday_name`
        (`$ix_d_CAA21771B5BB2089412F3D426AF25DEE3EDD1B76`,
         `$ix_t60_r_4CB1E32CCBE4FE2585D3C8F059CB3A909FC536B7`(60))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

当然，首先突出的是生成的列的难以阅读的名称。它们是自动生成的，是唯一的，但是 X DevAPI 可以很容易地使用它们。这些名称取决于字段定义，因此对于相同的提取字段，它们将保持不变。这样，当同一个字段在多个索引中使用时，就不会被多次添加。在这个例子中，雇员姓名用于两个索引中，但是只定义了一次如何提取它。

另一件事是，对于这些新的二级索引，生成的列有VIRTUAL子句。这意味着这些值实际上并不存储在表中，而是根据需要提取。这可以节省磁盘空间。

create_index()的恭维语是drop_index()，接下来讨论。

删除索引

删除索引比创建索引简单得多。所需要做的就是用要删除的索引名调用集合的drop_index()方法。MySQL 将负责删除索引，如果索引中生成的列不再为其他索引所需要，它们也将被删除。

清单 7-2 展示了一个删除employee_name索引的例子，它是上一个例子中创建的索引之一。

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)

# Reinitialize employees collection in
# the py_test_db schema
schema = db.get_schema("py_test_db")
employees = schema.get_collection(
  "employees")

# Drop the index on the Name field.

employees.drop_index("employee_name")

print("Index employee_name has been dropped")

db.close()

Listing 7-2Dropping an Index

在这种情况下，只需调用drop_index()方法就可以删除索引。该脚本的输出是

Index employee_name has been dropped

读者可以在删除索引之前和之后比较SHOW CREATE TABLE py_test_db.employees\G的输出，以确认Name字段仍然被提取。第二个练习是修改示例，删除employee_birthday_name索引，并看到Name和Birthday生成的列被删除。

这就完成了可用于操作集合的方法的演练(不考虑存储在集合中的文档)。在继续执行查询之前，还有一些额外的方法需要考虑。

其他收集方法和属性

mysqlx.Collection类包括一些方法和三个属性，在处理集合对象时会很有用。它们与已经讨论过的模式非常相似，所以这里只包括一个简单的介绍。

表 7-4 中列出了可以返回集合信息的方法。返回的对象是相对于mysqlx模块的。这些方法都不带任何参数。

表 7-4

收集实用程序方法

方法

返回对象

描述

| | --- | --- | --- | | exists_in_``databas | | 根据集合是否存在，返回True或False。 | | get_``connectio | connection.Connection | 返回基础连接对象。 | | get_``nam | | 返回集合的名称。这与name属性相同。 | | get_``schem | crud.Schema | 返回集合所在架构的对象。这与schema属性相同。 | | get_ session | connection.Session | 返回会话的对象。这与会话属性相同。在 8.0.12 版本中添加。 |

从返回名称和模式的方法的描述中可以看出，还有三个属性。name属性是集合的名称，schema属性保存模式对象，会话属性(在 8.0.12 和更高版本中可用)保存会话对象。

由于收集实用程序的方法和属性本质上与模式相同，所以让我们继续执行一些查询。

查询–CRUD

处理数据的有趣部分是执行查询。现在您已经知道如何使用会话、模式和集合，您可以开始使用会话来使用 API 的 NoSQL 部分。实现是围绕 CRUD(创建、读取、更新、删除)原则构建的，接下来的四个部分将依次介绍这四个操作。

在深入研究之前，有必要对 CRUD 方法进行一个高层次的了解。它们被收集在表 7-5 中。返回的对象是相对于myqslx模块的。

表 7-5

集合对象的 CRUD 方法

方法

争论

返回对象

描述

| | --- | --- | --- | --- | | ad d | 文件清单 | Statement.AddStatement | 准备将文档添加到词典列表中。每本词典都是一个文档。 | | add_or_replace_``on | doc_id | Result.Result | 向上插入文档，因此如果文档 ID 存在，它将替换现有文档；否则，它会将其添加为新文档。 | | doc | | coun t | | | 以整数形式返回集合中的文档数。 | | fin d | condition | Statement.FindStatement | 准备 find 语句，返回符合条件的文档。 | | modif y | condition | Statement.ModifyStatement | 准备一条修改语句，更新符合条件的文档。在 8.0.12 及更高版本中，该条件是强制性的。 | | remov e | condition | Statement.RemoveStatement | 准备删除符合条件的文档的 remove 语句。 | | remove_``on | doc_id | result.Result | 移除具有指定文档 ID 的文档。 | | replace_``on | doc_id | result.Result | 用新文档更新具有指定文档 ID 的文档。 | | doc |

从表中可以看出，CRUD 方法使用了一些常见的参数:

文档:这是一个描述 JSON 文档的字典。
单据 ID :该单据的唯一键。
条件:这相当于 SQL 中的WHERE子句，它定义了搜索文档时使用的过滤器。

注意

当然，这不仅仅是三种参数类型的列表所暗示的。例如，可以对读取请求的结果进行排序。你很快就会知道如何去做。

对于某些方法，直接返回结果对象。它将包含有关已执行操作的信息。除了count()之外，其余的方法都返回statement模块中的一个类的对象。它们要求你在执行动作之前调用返回的语句对象的execute()方法，然后execute()返回一个结果对象。

是时候停止讨论，开始创建一些文档了。这意味着您需要查看 CRUD 的创建部分。

CRUD:创建

使用存储在数据库中的数据的第一步是创建数据。在数据存在于集合中之前，没有什么可查询或修改的。因此，您的第一个任务是用一些数据填充一个集合。本节中插入的数据是其余文档存储 CRUD 讨论的基础。

有两种方法可以向集合中添加数据:add()和add_or_replace_one()。本节将讨论add()方法，而add_or_replace_one()将被推迟到“CRUD: Update”一节，因为它既可以添加新数据，也可以更新现有数据。

add()方法将零个或多个文档作为参数。每个文档都是使用 Python 字典定义的，这自然形成了一个 JSON 文档。当在同一个add()调用中插入多个文档时，它们可以作为列表或元组提供，或者通过指定多个参数来提供。

add()方法返回一个mysqlx.statement.AddStatement对象。两个最重要的方法是add()和execute()。这些方法总结在表 7-6 中。

表 7-6

方法来使用 Add 语句

方法

争论

描述

| | --- | --- | --- | | add | *values | 将值中指定的文档添加到 add 语句中。 | | execut e | | 通过向 MySQL 服务器提交值来执行 add 语句。 |

可以使用AddStatement. add()方法将更多的文档添加到语句中，AddStatement.execute()将文档发送到文档存储中。所有添加的文档都作为一份声明发送。

将要插入的数据可以在本书源代码中的cities.py文件中找到。该文件包括cities字典中 15 个澳大利亚城市的数据。文件的开头是

cities = {
  "Sydney": {
    "Name"           : "Sydney",
    "Country_capital": False,
    "State_capital"  : True,
    "Geography": {
      "Country" : "Australia",
      "State"   : "New South Wales",
      "Area"    : 12367.7,
      "Location": "{'Type': 'Point', 'Coordinates': [151.2094, -33.8650]}",
      "Climate" : {
        "Classification"      : "humid subtropical",
        "Mean_max_temperature": 22.5,
        "Mean_min_temperature": 14.5,
        "Annual_rainfaill"    : 1222.7
      },
    },
    "Demographics": {
      "Population": 5029768,
      "Median Age": 36
    },
    "Suburbs": [
      "Central Business District",
      "Parramatta",
      "Bankstown",
      "Sutherland",
      "Chatswood"
    ]
  },
...

清单 7-3 展示了一个例子，其中使用了三次add()方法将城市插入到一个新创建的集合中。使用add()的第一个例子插入一个城市，第二个例子在一个add()调用中插入两个城市，第三个例子使用多个add()调用插入几个城市，创建一个大的语句。

import mysqlx
from config import connect_args

from cities import cities

db = mysqlx.get_session(**connect_args)
schema = db.create_schema("py_test_db")

# Reinitalize the city collection
schema.drop_collection("city")
city_col = schema.create_collection("city")

# Insert a single city
sydney = cities.pop("Sydney")

db.start_transaction()

result = city_col.add(sydney).execute()

db.commit()

items = result.get_affected_items_count()
print("1: Number of docs added: {0}"
  .format(items))
ids = result.get_generated_ids()
print("1: Doc IDs added: {0}".format(ids))
print("")

# Insert two cities in one call
melbourne = cities.pop("Melbourne")
brisbane  = cities.pop("Brisbane")
data = (melbourne, brisbane)

db.start_transaction()

result = city_col.add(data).execute()

db.commit()

items = result.get_affected_items_count()
print("2: Number of docs added: {0}"
  .format(items))
ids = result.get_generated_ids()
print("2: Doc IDs added: {0}".format(ids))
print("")

# Insert the rest of the cities by
# adding them to the statement object
# one by one.
db.start_transaction()

statement = city_col.add()

for city_name in cities:

  statement.add(cities[city_name])

result = statement.execute()

db.commit()

items = result.get_affected_items_count()
print("3: Number of docs added: {0}"
  .format(items))
print("")

db.close()

Listing 7-3Adding Data to a Collection

该示例首先确保py_test_db模式中的city集合不存在，然后创建它。然后添加从cities.py文件导入的城市。

第一个插入的城市是悉尼。这是通过插入城市本身来完成的。该命令被链接起来，在一行代码中执行所有的工作，结果是一个result.Result对象。注意调用是如何被db.start_transaction()和db.commit()调用包装的。因为autocommit的值是从 MySQL 服务器全局设置继承的，所以显式添加事务是最安全的。

注意

和往常一样，您应该在提交数据之前检查是否出现了任何警告或错误。为了让示例更容易阅读，我们省略了对警告和错误的处理。第九章将会更详细地介绍如何检查警告和错误。

接下来，插入墨尔本和布里斯班。这是通过用文档创建一个元组来完成的，这个元组被传递给add()方法。文档也可以作为两个参数添加，例如:

result = city_col.add(
  melbourne, brisbane
).execute()

最后，添加剩余的城市。这是通过首先创建AddStatement对象，然后遍历剩余的城市，并逐个添加它们来完成的。最后，调用AddStatement. execute()方法在一个数据库调用中插入所有的城市。

执行该示例的输出类似于以下示例:

1: Number of docs added: 1
1: Doc IDs added: ['00005af3e4f7000000000000008f']

2: Number of docs added: 2
2: Doc IDs added: ['00005af3e4f70000000000000090', '00005af3e4f70000000000000091']

3: Number of docs added: 12

实际的文档 id 将会不同，因为它们是自动生成的。city集合现在有 15 个澳大利亚城市要查询，所以是时候看看读操作了。

读取

对于大多数数据库，大多数查询都是读取查询(不修改任何数据的查询)。这些查询是本节的主题。

有两种方法可以在不改变文档存储中任何数据的情况下读取集合:count()和find()。count()方法是最简单的，因为它只是将集合中文档的数量作为一个整数返回。find()方法更复杂，因为它支持条件(SQL 语言中的WHERE子句)、排序、返回文档等等。

find()方法返回一个mysqlx.statement.FindStatement对象。这是可以通过调用FindStatement方法来修改查询的主要构件。这些修饰符方法都返回FindStatement对象本身，因此可以进一步修改。最后有一个定义查询的调用链，可以使用execute()方法提交查询。表 7-7 显示了可用于修改语句的FindStatement方法。这些方法按照它们通常被调用的顺序进行排序。

表 7-7

方法来修改 Find 语句

方法

争论

描述

| | --- | --- | --- | | field s | *fields | 定义要包含在结果中的字段。每个字段都可以是使用与 SQL 相同语言的表达式。 | | group_``b | *fields | 描述对于涉及聚合函数的查询，要对哪些字段进行分组。 | | havin g | condition | 描述在查询以其他方式得到解决后，根据什么来筛选结果(排序除外)。这对于根据聚合函数的值进行过滤非常有用。 | | sor t | *sort_clauses | 描述结果的排序依据。 | | limi t | row_count``offset=0 | 第一个参数设置要返回的最大文档数。第二个可选参数定义偏移量。默认偏移量为 0。**注意:**offset 参数在 8.0.12 版本中已被否决，并将在以后的版本中被删除。相反，引入了offset()方法来设置偏移。 | | offset | offset | 设置要返回的行的偏移量。在 8.0.12 版本中添加。 | | lock_``exclusiv | | 使语句获得独占锁。一次只能有一个语句拥有独占锁。如果稍后将在同一事务中更新文档，则使用。 | | lock_``share | | 使语句获取共享锁。这可以防止其他语句修改匹配的文档，但是它们可以读取这些文档。 | | bin d | *args | 第一个参数提供要替换的参数的名称。第二个参数提供值。每个参数调用bind()一次。 | | execut e | | 执行 find 语句。 |

查询修改方法的调用顺序并不重要，但建议在整个程序中坚持相同的顺序，以便一眼就能确定查询是如何修改的。

清单 7-4 展示了一个例子，其中首先确定了city集合中城市的总数。然后显示集合中城市最多的州。结果按州内城市的数量排序，最多限于三个州。

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)
schema = db.create_schema("py_test_db")
city_col = schema.get_collection("city")

# Use the count() method to get the
# total number of cities.

num_cities = city_col.count()

print("Total number of cities: {0}"
  .format(num_cities))
print("")

statement = city_col.find(
  "Geography.Country = :country") \

  .fields("Geography.State AS State",
          "COUNT(*) AS NumCities") \
  .group_by("Geography.State") \
  .having("COUNT(*) > 1") \
  .sort("COUNT(*) DESC") \
  .limit(3)

result = statement.bind(

  "country", "Australia"

).execute()

states = result.fetch_all()
print("Num states in result: {0}"
  .format(result.count))
print("")

print("{0:15s}   {1:8s}"
  .format("State", "# Cities"))
print("-"*26)
for state in states:
  print("{State:15s}       {NumCities:1d}"
    .format(**state))

  db.close()

Listing 7-4Querying the city Collection

除了新的语法，这个例子很简单。集合的count()方法用于获取总行数。然后定义查询。您要求查询由Geography.Country元素过滤，该元素的值被占位符(:country)占用，该占位符后来在bind()方法中被设置为澳大利亚的值。查询将返回要返回并重命名为State的Geography.State元素，每个州的城市数返回为NumCities。

由于字段包括聚合函数(COUNT())和非聚合字段(Geography.State)，因此还需要定义分组依据。这是通过调用group_by()方法来完成的。此外，在这种情况下，您选择进行筛选，以便只包含拥有一个以上城市的州；这是使用having()方法完成的。

最后，您告诉文档存储库，您希望按照州内城市的数量以降序对结果进行排序，并且您最多希望返回三个州(城市最多的三个州)。此时，语句保存在statement变量中。在这种情况下，这并不重要，但是通过在执行时指定绑定参数，可以重用查询。

小费

如果语句对象存储在一个变量中，就有可能多次执行同一个查询。通过将bind()调用保存到执行时间，可以重用具有不同值的相同查询模板。下面是一个在修改文档时重用查找查询的例子。

一旦执行了查询，就使用fetch_all()获取行。这也将结果的count属性设置为结果中的文档数。该程序的输出是

Total number of cities: 15

Num states in result: 3

State             # Cities
--------------------------
Queensland            5
New South Wales       3
Victoria              2

已知city集合中所有城市的位置。作为第二个例子，让我们看看地理数据是如何使用的。清单 7-5 显示了一个查找所有城市、计算到悉尼的距离并从最近的城市开始按距离排序的例子。悉尼本身将被跳过，因为它不提供任何信息。

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)
schema = db.get_schema("py_test_db")
city_col = schema.get_collection("city")

# Get the location of Sydney
statement = city_col.find("Name = :city")
statement.fields(
  "Geography.Location AS Location")
statement.bind("city", "Sydney")
result = statement.execute()
sydney = result.fetch_one()

# Define the formula for converting
# the location in GeoJSON format to
# the binary format used in MySQL

to_geom = "ST_GeomFromGeoJSON({0})"

sydney_geom = to_geom.format(
  sydney["Location"])
other_geom = to_geom.format(
  "Geography.Location")

distance = "ST_Distance({0}, {1})".format(

  sydney_geom, other_geom)

statement = city_col.find("Name != 'Sydney'")
statement.fields(
    "Name",
    "Geography.State AS State",
    distance + " AS Distance"
)
statement.sort(distance)
result = statement.execute()

cities = result.fetch_all()
print("{0:14s}   {1:28s}   {2:8s}"
  .format("City", "State", "Distance"))
print("-"*56)
for city in cities:
  # Convert the distance to kilometers
  distance = city["Distance"]/1000
  print("{Name:14s}   {State:28s}"
    .format(**city)
    + "     {0:4d}"
    .format(int(distance))
  )

db.close()

Listing 7-5Querying Geographical Data

这个例子的基本原理与上一个例子相同。有趣的部分是两个 MySQL 几何函数的使用。ST_GeomFromGeoJSON()函数用于将 GeoJSON 坐标转换为 MySQL 中使用的二进制几何格式。然后，ST_Distance()函数计算两点之间的距离，将空间参考系统考虑在内；这里使用默认值，即地球。程序的输出如清单 7-6 所示。

City             State                          Distance
--------------------------------------------------------
Wollongong       New South Wales                    69
Newcastle        New South Wales                   116
Canberra         Australian Capital Territory      249
Gold Coast       Queensland                        681
Melbourne        Victoria                          714
Brisbane         Queensland                        730
Geelong          Victoria                          779
Sunshine Coast   Queensland                        819
Hobart           Tasmania                         1056
Adelaide         South Australia                  1164
Townsville       Queensland                       1676
Cairns           Queensland                       1960
Darwin           Northern Territory               3145
Perth            Western Australia                3297

Listing 7-6The Output of the Example Program in Listing 7-5

添加和检索数据涵盖了很多数据库用例，但是如果数据不再是最新的，该怎么办呢？让我们看看如何更新数据库中的数据。

CRUD:更新

在 CRUD 中，更新部分是修改现有的数据，因此一个或多个字段的值被改变或者字段被添加或删除。例如，当一个城市的人口因为人口迁移、出生和死亡而发生变化时，更新该城市的人口。这一节将研究可以做到这一点的方法。

在 MySQL Connector/Python X DevAPI 中为文档存储修改数据有三种方法:

add_or_replace_one():用于追加插入一张单据。例如，如果有一个文档具有相同的文档 ID，它将被替换；否则，将添加一个新文档。
modify():更新现有文档。查找文档的方式与读取查询过滤文档的方式相同。
replace_one():用给定的文档 ID 替换文档。

由于add_or_replace_one()和replace_one()方法非常相似，所以将一起讨论它们，然后讨论modify()方法。

替换文档

add_or_replace_one()和replace_one()的区别在于如果没有指定 ID 的文档会发生什么。add_or_replace_one()方法添加了一个新文档，而replace_one()方法最终什么也没做。

清单 7-7 显示了一个例子，其中 Geelong 的文档被替换为人口被更改为 193000 的文档。之后，它会尝试用不存在的文档 ID 替换文档。

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)
schema = db.get_schema("py_test_db")
city_col = schema.get_collection("city")

# Get the current document for Geelong
db.start_transaction()
result = city_col.find("Name = :city_name") \
  .bind("city_name", "Geelong") \
  .lock_exclusive() \
  .execute()
geelong = result.fetch_one()

# Get the Geelong document ID and
# update the population
geeling_id = geelong["_id"]
geelong["Demographics"]["Population"] = 193000

# Upsert the document

result = city_col.add_or_replace_one(

  geeling_id, geelong)

print("Number of affected docs: {0}"
  .format(result.get_affected_items_count()))

# Attempt to use the same document
# to change a non-existing ID

result = city_col.replace_one(

  "No such ID", geelong)

print("Number of affected docs: {0}"
  .format(result.get_affected_items_count()))

# Leave the data in the same state as
# before the changes
db.rollback()
db.close()

Listing 7-7Upserting a Document and Attempting to Replace One

该示例从检索 Geelong 的现有文档开始。它用于获取文档 ID，并作为新文档的基础。这也是一个使用独占锁的例子，所以在事务完成之前，其他人不能访问文档。使用add_or_replace_one()方法将新文档向上插入到文档存储中。之后，相同的文档被用于replace_one()方法，但是文档 ID 不存在。对于这两种用途中的每一种，都会打印受影响文档的数量。输出是

Number of affected docs: 2
Number of affected docs: 0

add_or_replace_one()调用的结果显示两个文档受到了影响。怎么会？这是因为 upserts 的实现方式。首先，尝试插入新文档。如果有重复的文档，则更新现有的文档(SQL 中的一个INSERT ... ON DUPLICATE KEY UPDATE ...语句)。如果文档不存在，只有一个文档会受到影响。另一方面，replace_one()不影响任何文件；它既不插入新文档，也不更新现有文档。

注意

不要认为replace_one()是 SQL REPLACE语句。与replace_one()对应的 SQL 语句是UPDATE，在主键上有一个WHERE子句。

如果您只想更新已经更改的字段，而不是整个文档，那么您需要使用modify()方法。

修改文档

您在前面的讨论中看到的两种替换方法用于替换整个文档。对于示例集合中相对较小的文档来说，这不是一个大问题，但是对于较大的文档来说，这很快就会变得低效。此外，这两种替换方法一次只能修改一个文档，这使得更新一个国家中的所有城市或集合中的所有文档变得很麻烦。

这就是modify()方法发挥作用的地方。它支持指定一个类似于 read 查询的条件来指定要更改的文档，并且更改可以基于现有的值。modify()方法返回一个mysqlx.statement.ModifyStatement对象，它和find()返回的语句对象一样，可以用来定义整个修改语句。表 7-8 包含了最重要的方法。

表 7-8

修改修改查询的方法

方法

争论

描述

| | --- | --- | --- | | sor t | *sort_clauses | 描述文档的排序依据。 | | limi t | row_count | 该参数设置要修改的最大文档数。 | | array_``appen | doc_path | 在文档中由doc_path指定的点将值追加到现有的数组元素。 | | value | | array_``inser | field | 将值插入到由field指定的数组中。 | | value | | se t | doc_path | 如果文档路径已经存在，则更新该值；否则，该字段将添加给定值。 | | value | | patc h | Doc | 添加或替换文档中包含的字段。它还支持删除现有字段。 | | unse t | *doc_paths | 从文档中删除匹配的文档路径。 | | bin d | *args | 第一个参数提供要替换的参数的名称。第二个参数提供值。每个参数调用bind()一次。 | | execut e | | 执行 modify 语句。 |

有几个方法与find()相同，这并不奇怪，因为 modify 语句的第一个任务是定位要更新的文档。与 find 语句不同，它要求定义一个条件。否则，8.0.11 中会出现ProgrammingErrror异常:

mysqlx.errors.ProgrammingError: No condition was found for modify

在 MySQL Connector/Python 8.0.12 及更高版本中，必须在创建修改语句时指定条件。否则会发生 TypeError 异常:

TypeError: modify() missing 1 required positional argument: 'condition'

这是一项安全功能，可防止因编码错误而意外更新所有文档。如果您确实需要修改所有文档，请添加一个始终计算为True的条件，例如:

collectoin.modify("True")

注意

modify 语句必须有一个指定要更新哪些文档的条件。如果真的要更新所有文档，用True作为条件；这也使你的意图变得清晰，这在你以后回到代码的时候很有帮助。

还有几种方法可以改变匹配文档的内容。让我们看一些使用这些方法的例子。

set()和 unset()

修改文档最简单的方法是set()和unset()方法。set()和unset()的方法非常相似。set()方法将改变指定字段的值。如果该字段不存在，它将被添加。新值可以是包含旧值的计算结果。另一方面，unset()方法删除一个存在的字段。

假设已经进行了人口普查，维多利亚州的数据显示人口增长了 10%。清单 7-8 展示了如何使用modify()方法进行更改。

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)
schema = db.get_schema("py_test_db")
city_col = schema.get_collection("city")

db.start_transaction()

# Get the current population
statement = city_col.find(   "Geography.State = :state")
statement.fields("Name AS CityName",
  "Demographics.Population AS Population")
statement.sort("Name")
statement.bind("state", "Victoria")

before = statement.execute()

# Update the population for cities
# in the state of Victoria to increase
# the population with 10%

result = city_col.modify(
  "Geography.State = :state") \

  .set("Demographics.Population",
    "FLOOR(Demographics.Population * 1.10)") \
  .bind("state", "Victoria") \
  .execute()

print("Number of affected docs: {0}"
  .format(result.get_affected_items_count()))
print("")

after = statement.execute()

before_cities = before.fetch_all()
after_cities  = after.fetch_all()

print("{0:10s}   {1:¹⁷s}"
  .format("City", "Population"))
print("{0:10s}   {1:7s}   {2:7s}"
  .format("", "Before", "After"))
print("-"*30)

for before_city, after_city \
  in zip(before_cities, after_cities):
  print("{0:10s}   {1:7d}   {2:7d}"
    .format(
      before_city["CityName"],
      int(before_city["Population"]),
      int(after_city["Population"])
    )
  )

# Leave the data in the same state as
# before the changes
db.rollback()
db.close()

Listing 7-8Modifying Several Documents at a Time with set()

在修改维多利亚州的城市之前，定义了一个 find 语句来获取城市名称及其人口。这用于获取 modify 语句前后的填充，因此有可能确认结果是预期的。

该示例最有趣的部分是调用modify()和后续的方法调用来定义更新。set()方法用于定义更新本身，新群体是基于旧群体计算的。注意FLOOR()函数是如何围绕决定新人口的计算的。这是为了避免分数人。即使您允许分数，也有必要在新值两边加上括号，以表示应该使用Demographics.Population的值，而不是字符串“Demographics”。人口”。

执行 modify 语句后，将再次读取人口，并打印城市的前后人口。最后，事务被回滚，因此可以多次执行程序并获得相同的输出，如下所示:

Number of affected docs: 2

City            Population
             Before    After
------------------------------
Geelong       192393    211632
Melbourne    4725316   5197847

接下来要讨论的两种修改方法是array_append ()和array_insert()方法。

array_append()和 array_insert()

顾名思义，array_append()和array_insert()方法处理文档中的数组。虽然它们的用途相对有限，但它们非常擅长自己的工作。它们的用法是相似的，所以将它们放在一起讨论。

这两种方法采用相同的两个参数，尽管它们的名称不同。这两个论点是

要修改的数组元素的路径或插入值的位置
价值

主要的区别是在指定的元素上做了什么。array_append()方法用一个数组替换现有值，该数组由现有值后跟新值组成。另一方面，array_insert()将新元素插入数组中的那个位置。

解释它们如何工作的最简单的方法是举一个例子。清单 7-9 使用这两种方法来修改悉尼的郊区。使用array_append()方法将中央商务区郊区变成一个由自身作为第一元素，郊区地点作为第二元素组成的数组。array_insert()方法用于将利物浦添加到郊区列表中。

import mysqlx
from config import connect_args
import pprint

printer = pprint.PrettyPrinter(indent=1)

db = mysqlx.get_session(**connect_args)
schema = db.get_schema("py_test_db")
city_col = schema.get_collection("city")

# Run inside a transaction, so the
# changes can be rolled back at the end.
db.start_transaction()

# Get the current suburbs, the document
# id, and the index of Central Business
# District in the Suburbs array.
statement = city_col.find("Name = :city_name")
statement.fields(
  "_id",
  "Suburbs",
  "JSON_SEARCH("
     + "Suburbs,"
     + " 'one',"
     + " 'Central Business District')"
  + " AS Index")
statement.bind("city_name", "Sydney")

before = statement.execute().fetch_one()
print("Suburbs before the changes:")
print("-"*27)
printer.pprint(before["Suburbs"])
print("")

docid = before["_id"]
# The returned index includes $ to
# signify the start of the path, but
# that is relative to Suburbs, so
# remove for this use.

index = before["Index"][1:]

print("Index = '{0}'\n"
  .format(before["Index"]))

# Use array_append() to change the
# Central Busines District suburb into
# an array of itself plus an array of
# some places within the suburb.
modify = city_col.modify("_id = :id")

modify.array_append(

  "Suburbs{0}".format(index),
  ["Circular Quay", "Town Hall"])

modify.bind("id", docid)

modify.execute()

after1 = statement.execute().fetch_one()
print("Suburbs after the array_append:")
print("-"*31)
printer.pprint(after1["Suburbs"])
print("")

# Reset the data
db.rollback()

# Use array_insert to add the suburb
# Liverpool
db.start_transaction()
num_suburbs = len(before["Suburbs"])
modify = city_col.modify("_id = :id")

modify.array_insert(

  "Suburbs[{0}]".format(num_suburbs),
  "Liverpool")
modify.bind("id", docid)
modify.execute()

after2 = statement.execute().fetch_one()
print("Suburbs after the array_insert:")
print("-"*31)
printer.pprint(after2["Suburbs"])

# Reset the data
db.rollback()
db.close()

Listing 7-9Updating a Document with array_append() and array_insert()

示例的开始只是像往常一样设置环境。然后读取悉尼现有的郊区以及郊区数组中的文档 ID 和中央商务区的索引。

文档 ID 允许您在 modify 语句的过滤条件中使用它。通过文档 ID 查找文档总是最有效的定位方法。

中央商务区元素的索引作为$[0](数组中的第一个元素)返回。美元符号表示文档的头部，但是因为您使用了JSON_SEARCH()函数来搜索Suburbs数组，所以它是相对于Suburbs数组的，而不是文档的根。所以，为了使用指数，有必要去掉美元符号。

小费

MySQL 中有几个 JSON 函数可以用来搜索或处理 JSON 文档。一般来说，它们都是围绕将 MySQL 作为文档存储时所需的功能展开的，但也有一些功能与 SQL 方面更相关。关于 MySQL 中 JSON 函数的完整概述，请参见 https://dev.mysql.com/doc/refman/en/json-functions.html 。

现在，您可以使用array_append()添加郊区内的位置，并使用array_insert()添加利物浦郊区。对于插入，路径被设置为Suburbs[{0}]，其中{0}被替换为更改前的郊区数，以在末尾添加新的郊区。在每个 modify 语句之后，事务回滚以重置数据。输出是

Suburbs before the changes:
---------------------------
['Central Business District',
 'Parramatta',
 'Bankstown',
 'Sutherland',
 'Chatswood']

Index = '$[0]'

Suburbs after the array_append:
-------------------------------
[['Central Business District', ['Circular Quay', 'Town Hall']],
 'Parramatta',
 'Bankstown',
 'Sutherland',
 'Chatswood']

Suburbs after the array_insert:
-------------------------------
['Central Business District',
 'Parramatta',
 'Bankstown',
 'Sutherland',
 'Chatswood',
 'Liverpool']

输出清楚地表明了所做的更改(参见输出中的粗体部分)。array_append()把字符串'Central Business District'改成了数组['Central Business District', ['Circular Quay', 'Town Hall']]。array_insert()方法在现有数组的末尾插入了'Liverpool'。

现在，修改语句只剩下patch()方法了。

补丁()

修改文档的最后一种方法是patch()方法。这是最强大的方法，但是一旦你掌握了它，它会出奇的简单。在某种程度上，它的工作方式类似于用于对源代码进行修改的patch命令，因此得名；但是，语法不相关。

对于作为参数提供的文档所匹配的每个 JSON 字段，patch()方法有三种可能的结果:

元素被删除:当字段名匹配而补丁文档中没有值时会发生这种情况。
元素被修改:当字段匹配并且在补丁文档中有值时，就会发生这种情况。
元素被插入:当字段不存在并且它在补丁文档中有一个值时会发生这种情况。

指定字段缺少值的方式(删除或不添加)取决于如何指定文档。如果文档被指定为一个内部写有 JSON 文档的字符串，那么缺少的值通过写null来指定。当文档被指定为字典时，使用None表示没有值。

因此，要修补文档，您需要提供一个具有所需新值的新文档。然后 MySQL 解决剩下的问题。这就是该方法的简单之处。

以阿德莱德市为例。数据已经过时，所以现在应该确定文档中存储的字段的最新值。城市面积和人口已经更改，但是无法找到Median weekly individual income的更新数量，所以您决定删除该字段。描述补丁的文档就变成了

doc = {
  "Geography": {
    "Area": 3400
  },
  "Demographics": {
    "Population": 1500000,
    "Median weekly individual income": None
  }
}

这相当简单，而且很容易看出发生了什么变化。清单 7-10 展示了进行更改的完整示例。

import mysqlx
from config import connect_args
import pprint

printer = pprint.PrettyPrinter(indent=1)

db = mysqlx.get_session(**connect_args)
schema = db.get_schema("py_test_db")
city_col = schema.get_collection("city")

# Run inside a transaction, so the
# changes can be rolled back at the end.
db.start_transaction()

# Get the current suburbs, the document
# id, and the index of Central Business
# District in the Suburbs array.
statement = city_col.find(
  "Name = :city_name")
statement.bind("city_name", "Adelaide")
before = statement.execute().fetch_one()
print("Adelaide before patching:")
print("-"*25)
printer.pprint(dict(before))
print("")

docid = before["_id"]

# Make the following changes:
#  * Increase the area to 3400
#  * Increase the population to 1500000
#  * Remove the median weekly individual
#    income.

doc = {

  "Geography": {
    "Area": 3400
  },
  "Demographics": {
    "Population": 1500000,
    "Median weekly individual income": None
  }

}

modify = city_col.modify("_id = :id")

modify.patch(doc)

modify.bind("id", docid)

modify.execute()

after = statement.execute().fetch_one()
print("Adelaide after patching:")
print("-"*24)
printer.pprint(dict(after))

# Reset the data
db.rollback()
db.close()

Listing 7-10Using patch() to Modify a Document

本例中唯一的新部分是定义了用来修补现有文档的文档，并使用了patch()方法。清单 7-11 显示了阿德莱德之前和之后的文件。

Adelaide before patching:
-------------------------
{'Country_capital': False,
 'Demographics': {'Median weekly individual income': 447,
                  'Population': 1324279},
 'Geography': {'Area': 3257.7,
               'Climate': {'Annual rainfaill': 543.9,
                           'Classification': 'Mediterranean',
                           'Mean max temperature': 22.4,
                           'Mean min temperature': 12.3},
               'Country': 'Australia',
               'Location': {'Coordinates': [138.601, -34.929], 'Type': 'Point'},
               'State': 'South Australia'},
 'Name': 'Adelaide',
 'State_capital': True,
 'Suburbs': ['Adelaide',
             'Elizabeth',
             'Wingfield',
             'Henley Beach',
             'Oaklands Park'],
 '_id': '00005af3e4f70000000000000093'}

Adelaide after patching:
------------------------
{'Country_capital': False,
 'Demographics': {'Population': 1500000},
 'Geography': {'Area': 3400,
               'Climate': {'Annual rainfaill': 543.9,
                           'Classification': 'Mediterranean',
                           'Mean max temperature': 22.4,
                           'Mean min temperature': 12.3},
               'Country': 'Australia',
               'Location': {'Coordinates': [138.601, -34.929], 'Type': 'Point'},
               'State': 'South Australia'},
 'Name': 'Adelaide',
 'State_capital': True,
 'Suburbs': ['Adelaide',
             'Elizabeth',
             'Wingfield',
             'Henley Beach',
             'Oaklands Park'],
 '_id': '00005af3e4f70000000000000093'}

Listing 7-11The Before and After Documents for Adelaide After the Patching Process

请注意这里的Median weekly individual income是如何被移除的，而Population和Area是如何被更新的。_id的值将不同于该输出中的值，但是_id在修补前后是相同的。

小费

如果您想了解更多关于修补文档的信息，可以从提供底层功能的JSON_MERGE_PATCH() SQL 函数的文档开始。https://dev.mysql.com/doc/refman/en/json-modification-functions.html#function_json-merge-patch见。

关于修改语句的讨论到此结束。CRUD 的最后一部分是删除文档。

CRUD:删除

在大多数应用中，有些文档应该在某个时候被删除。删除文档减少了数据的大小，这不仅减少了磁盘的使用，而且使查询更有效，因为它们需要处理更少的数据。文档存储有两种从集合中删除文档的方法:

remove():根据条件任意删除文件。
remove_one():用于根据文档 ID 删除单个文档。

这些方法是最简单的 CRUD 方法，因为所需要的只是指定要删除哪些文档。remove()方法提供了一个最多删除多少文档的选项。

remove_one()方法是两种方法中最简单的一种，因为它只需要一个文档 ID 并直接返回一个Result对象。remove()方法接受一个条件，比如find()和modify()，并返回一个RemoveStatement类的对象。可以使用表 7-9 中列出的方法进一步细化该陈述。

表 7-9

修改移除查询的方法

方法

争论

描述

| | --- | --- | --- | | sor t | *sort_clauses | 描述文档的排序依据。 | | limi t | row_count | 设置要删除的最大文件数。 | | bin d | *args | 第一个参数提供要替换的参数的名称。第二个参数提供值。每个参数调用bind()一次。 | | execute | | 执行删除语句。 |

这些修饰符方法现在应该都很熟悉了。与modify()方法一样，必须指定一个条件；否则，8.0.11 中会出现ProgrammingError异常:

mysqlx.errors.ProgrammingError: No condition was found for remove

在 MySQL Connector/Python 8.0.12 及更高版本中，调用remove()创建 remove 语句时必须指定条件。如果没有给定条件，将发生 TypeError 异常:

TypeError: remove() missing 1 required positional argument: 'condition'

如果需要删除所有文档，可以使用True作为条件，或者删除/重新创建集合。

清单 7-12 展示了如何首先使用文档 ID 删除一个城市，然后通过国家过滤删除几个城市。

import mysqlx
from config import connect_args

db = mysqlx.get_session(**connect_args)
schema = db.get_schema("py_test_db")
city_col = schema.get_collection("city")

# For printing information along the way
fmt = "{0:36s}: {1:2d}"

# Run inside a transaction, so the
# changes can be rolled back at the end.
db.start_transaction()

# Get the document ID for Canberra.
statement = city_col.find("Name = :city_name")
statement.fields("_id")
statement.bind("city_name", "Canberra")

result = statement.execute()
canberra_id = result.fetch_one()["_id"]

# Number of rows in the collection
# before removing any documents
print(fmt.format(
  "Initial number of documents",
  city_col.count()
))
print("")

result = city_col.remove_one(

  canberra_id)

items = result.get_affected_items_count()
print(fmt.format(
  "Number of rows deleted by remove_one",
  result.get_affected_items_count()
))
print(fmt.format(
  "Number of documents after remove_one",
  city_col.count()
))
print("")

statement = city_col.remove(   "Geography.Country = :country")

statement.bind("country", "Australia")

result = statement.execute()

print(fmt.format(
  "Number of rows deleted by remove",
  result.get_affected_items_count()
))
print(fmt.format(
  "Number of documents after remove",
  city_col.count()
))

# Reset the data
db.rollback()
db.close()

Listing 7-12Deleting Documents in a Collection

在这个例子中，堪培拉的文档 ID 首先是使用find()方法找到的。然后，在使用remove()方法删除所有剩余的澳大利亚城市之前，使用remove_one()删除 Canberra 文档 ID 的文档。在此过程中，将打印受删除操作影响的城市数和行数。输出是

Initial number of documents         : 15

Number of rows deleted by remove_one:  1
Number of documents after remove_one: 14

Number of rows deleted by remove    : 14
Number of documents after remove    :  0

集合中有 15 个城市。不出所料，remove_one()删除了一个城市，剩下 14 个城市。由于集合中只有澳大利亚的城市，使用过滤器Geography.Country = 'Australia'删除会删除剩余的 14 个文档。

文档存储世界的漫长旅程到此结束。MySQL X DevAPI 不仅仅是关于文档存储；它还能以 NoSQL 的方式处理 SQL 表并执行 SQL 查询，所以下一章会有更多的内容要深入探讨。

摘要

本章详细介绍了 MySQL 文档库。具体来说，您了解了集合以及如何使用它们。集合是相关文档的容器；例如，您可以使用一个名为city的容器来存储关于城市的信息，比如城市所在的国家、人口等等。

文档本身是以无模式方式存储数据的 JSON 文档。这允许开发人员快速向数据库添加新类型的数据，但也将保持数据一致性的任务推给了开发人员。

您已经开始学习如何从创建集合到删除集合进行操作。在这两者之间，可以创建和删除索引，还可以检索一个集合以供 create-read-update-delete (CRUD)方法使用。本章的其余部分详细介绍了每个 CRUD 方法。

MySQL X DevAPI 不仅仅用于文档存储。下一章将介绍如何对 SQL 表使用 CRUD 方法，以及如何执行任意 SQL 语句。