MySQL-Connection-Python-揭秘-一-MySQL Connection/Python 揭秘（一）一

MySQL Connection/Python 揭秘（一）

原文：MySQL Connector/Python Revealed

协议：CC BY-NC-SA 4.0

一、简介和安装

您将踏上 MySQL Connector/Python 世界的旅程。欢迎登机！这是十步指南的第一章，将带您完成从安装到故障排除的所有内容。在这个过程中，您将熟悉连接器及其 API 的特性和工作方式。

本章将通过浏览版本、版本和 API 来介绍 MySQL 连接器/Python。本章的中间部分将讨论如何下载和安装连接器，最后一部分将讨论 MySQL 服务器，如何为本书中的示例设置服务器，并对示例本身说几句话。

介绍

MySQL Connector/Python 是 Python 程序和 MySQL 服务器数据库之间的粘合剂。它可用于使用数据定义语言(DDL)语句操作数据库对象，以及通过数据操作语言(DML)语句更改或查询数据。

也可以把 MySQL Connector/Python 称为数据库驱动。它是 Python 的官方 MySQL 连接器，由 Oracle 公司的 MySQL 开发团队开发和维护。它有效地支持三种不同的 API，尽管通常只直接使用两种。

本节介绍 MySQL 连接器/Python 版本、版本和三个 API。

版本

在 2012 年之前，没有 Oracle 维护的 Python 连接器。还有其他第三方连接器，比如 MySQL-python (MySQLdb)接口；然而，它越来越老，官方只支持 MySQL 5.5 和 Python 2.7。

MySQL 决定开发自己的连接器:MySQL Connector/Python。它被编写为与 MySQL-python 接口兼容，并与最新的 MySQL 服务器和 python 版本保持同步。最初的正式发布(GA)版本是 1.0.7，于 2012 年 9 月发布。版本 2.1 发生了重大更新；它引入了 C 扩展，允许更好的性能。截至 2018 年 4 月的最新 GA 版本是 8.0.11 版，其中额外引入了 X DevAPI。这是本书主要关注的版本。

注意

如果看一下 MySQL Connector/Python 的变化历史，可能会有点疑惑。8.0 之前的版本系列是 2.1，有几个 2.2 的 GA 前版本。8.0 版本的列表同样令人困惑:最新的 GA 前版本是 8.0.6，而第一个 GA 版本是 8.0.11。为什么跳跃？大多数 MySQL 产品的版本号是一致的，这要求发布号有些不规则，但现在这意味着 MySQL Server 8.0.11 和 MySQL Connector/Python 8.0.11 是一起发布的。

建议使用 GA quality 最新系列的最新补丁发布。只有最新的 GA 系列获得了所有的改进和错误修复。这意味着，在撰写本文时，建议使用最新的 MySQL Connector/Python 8.0 版本。虽然 MySQL Connector/Python 8.0 版本与 MySQL Server 和其他 MySQL 产品的版本结合在一起， ¹ 它们与较旧的 MySQL Server 版本向后兼容。所以，即使你还在用，比如 MySQL Server 5.7，你还是应该用 MySQL Connector/Python 8.0。

小费

使用 GA quality 最新版本系列的最新版本，确保您不仅可以获得所有最新功能，还可以获得最新的错误修复。最新的 MySQL 连接器/Python 版本可以与旧的 MySQL 服务器版本一起使用。另一方面，旧版本的 MySQL Connector/Python 可能与最新的 MySQL 服务器版本不兼容。例如，MySQL Server 8.0 默认使用caching_sha2_password身份验证插件，直到最近 MySQL Connector/Python 才支持该插件。

与任何正在开发的产品一样，新功能会定期添加，错误也会得到修复。您可以关注发行说明中的变化，这些信息可从 https://dev.mysql.com/doc/relnotes/connector-python/en/ .获得

除了各种版本的 MySQL Connector/Python 之外，还有两个不同的版本可供选择。让我们看看他们。

社区版和企业版

MySQL 产品有两个不同的版本:社区版和企业版。企业版是 Oracle 提供的商业产品。两个版本之间的差异因产品而异。例如，对于 MySQL Server，企业版有几个额外的插件。对于 MySQL Connector/Python，区别更微妙。

所有产品的一个共同区别是许可证。社区版是在 GNU 通用公共许可证 2.0 版下发布的，而企业版使用专有许可证。此外，企业版通过 MySQL 技术支持服务提供技术支持。对于 MySQL Connector/Python 本身来说，这是目前两个版本之间唯一的区别。

本书适用于两个版本中的任何一个，除了在本章后面简要讨论下载位置和安装方法时，不会提到版本。所有的例子都是用 Community Edition 编写和测试的。

相比之下，当涉及到 API 时，使用哪种 API 有很大的不同。

蜜蜂

MySQL Connector/Python 中实际上可以使用三种不同的 API。如何使用 API 是第 2 - 9 章的主要目的。在真正开始之前，有必要简要了解一下它们的区别。

表 1-1 显示了三个 API，它们在哪个 MySQL 连接器/Python 模块中可用，包括 API 支持的第一个 GA 版本，以及讨论它们的章节。

表 1-1

MySQL 连接器/Python API

应用接口

组件

第一版

章

| | --- | --- | --- | --- | | 连接器/Python API | mysql.connector | 1.0.7 | 2, 3, 4, 5, 9, 10 | | c 扩展 API | _mysql_connector | 2.1.3 | four | | X DevAPI | mysqlx | 8.0.11 | 6, 7, 8, 9, 10 |

此外，Connector/Python API 和 X DevAPI 既存在于纯 Python 实现中，也存在于使用 C 扩展的实现中。这两种实现是可以互换的。整本书都会提到这两种实现之间的一些差异。

正如您所看到的，主要的焦点是连接器/Python API 和 X DevAPI。连接器/Python API 和 C 扩展 API 专门使用 SQL 语句来执行查询。另一方面，X DevAPI 支持 NoSQL 方法来处理 JSON 文档和 SQL 表，并支持 SQL 语句。X DevAPI 也是其他编程语言的通用 API，包括 JavaScript (Node.js)、PHP、Java、DotNet 和 C++。

那么应该选择哪个 API 呢？从到目前为止的描述来看，选择 X DevAPI 听起来是显而易见的。然而，事情远不止如此。

如果专门使用 SQL 语句执行查询，C 扩展和 C 扩展 API 更成熟。例如，它们为参数绑定和预处理语句等特性提供了更好的支持。如果您需要连接池，它们也是可供选择的 API。如果您有现有的 Python 程序，它们也很可能使用连接器/Python API(启用或不启用 C 扩展实现)。

另一方面，X DevAPI 是一个新的 API，它是为适应现代需求而从头设计的。该 API 也适用于其他编程语言，当应用需要多种语言时，可以更容易地在语言之间进行切换。API 的 NoSQL 部分使得针对 SQL 表的简单查询和使用 JSON 文档变得更加简单。新的命令行客户端 MySQL Shell 也支持通过 Python 或 JavaScript 使用 X DevAPI。所以，X DevAPI 有很多新的项目。

由于 X DevAPI 本质上是 1.0 版本(MySQL 8.0 是 X DevAPI 的第一个 GA 版本)，新特性更有可能在相对较短的时间内陆续推出。如果您缺少某个功能，请留意发行说明，看看该功能是否可用，或者在 https://bugs.mysql.com/ 注册您感兴趣的功能。

与“便利性”相比，是否使用 C 扩展在很大程度上是一个性能问题 C 扩展实现提供了更好的性能，尤其是在处理大型结果集和准备好的语句时。然而，纯 Python 实现可以在更多平台上使用，在自己构建 MySQL Connector/Python 时更容易使用，也更容易修改(顾名思义，纯 Python 实现完全是用 Python 编写的)。

MySQL 连接器/Python 的介绍到此结束。是时候开始安装过程了。第一步是下载 MySQL 连接器/Python。

下载

直接下载 MySQL 连接器/Python；但是，还是有几点考虑。这些注意事项和执行下载的步骤是本节的主题。

首先要问的是，您需要连接器的社区版还是企业版。这决定了下载和安装选项。社区版可以从几个地方获得，既有源代码形式，也有二进制发行版。企业版仅作为 Oracle 的二进制发行版提供。

小费

安装 MySQL Connector/Python 社区版的推荐方法是使用 Python 打包权威(PyPa)/Python 包索引(PyPi)中的包。这是使用pip工具完成的，不需要预先下载任何文件。使用 PyPi 的一个缺点是从发布到在 PyPi 中可用会有一个小的延迟。

表 1-2 概述了 MySQL Connector/Python 可用的交付方法，以及该方法是否可用于社区版和企业版。

表 1-2

MySQL 连接器/Python 下载选项

分配

社区版

企业版

| | --- | --- | --- | | Python 包(pip) | 可用；参见安装 | | | Windows 安装程序 | 有空的 | 有空的 | | MSI 安装程序 | 有空的 | 有空的 | | APT 知识库 | 有空的 | | | SUSE 知识库 | 有空的 | | | Yum 仓库 | 有空的 | | | RPM 下载 | 有空的 | 有空的 | | DEB 包 | 有空的 | 有空的 | | Solaris 软件包 | 有空的 | 有空的 | | 苹果 | 有空的 | 有空的 | | 独立于平台的 tar 或 zip 文件 | 有空的 | 有空的 |

如您所见，MySQL Connector/Python 可用于各种平台和不同的发行版。社区版可以直接使用pip命令行工具获得；为 Red Hat Enterprise Linux、Oracle Linux 和 Fedora Linux 使用 MySQL Yum 存储库；适用于 Debian 和 Ubuntu 的 MySQL APT 库；并为 SLES 使用 MySQL 数据库。pip和包存储库选项仅适用于社区版。

小费

MySQL 连接器/Python 的 MySQL 安装程序和 MSI 安装程序都可用于 Microsoft Windows。如果您想使用这些安装程序中的一个，建议使用 MySQL 安装程序，因为它也支持大多数其他 MySQL 产品。

表 1-3 显示了各种源代码和安装程序的下载位置的 URL。在这个上下文中，MySQL 存储库算作安装程序，即使它们更像是安装程序使用的定义文件。

表 1-3

下载资源

源/安装程序

统一资源定位器

| | --- | --- | | **社区:**Microsoft Windows 的 MySQL 安装程序 | https://dev.mysql.com/downloads/installer/ | | APT 知识库 | https://dev.mysql.com/downloads/repo/apt/ | | SUSE 知识库 | https://dev.mysql.com/downloads/repo/suse/ | | Yum 仓库 | https://dev.mysql.com/downloads/repo/yum/ | | MySQL 下载 | https://dev.mysql.com/downloads/connector/python/ | | 开源代码库 | https://github.com/mysql/mysql-connector-python | | **企业:**我的甲骨文支持 | https://support.oracle.com/ | | 甲骨文软件交付云 | https://edelivery.oracle.com/ |

与社区版相关的下载可从 https://dev.mysql.com/downloads 下的页面获得。如果您需要源代码，可以从 MySQL 下载站点和 MySQL 的 GitHub 存储库中获得。 ²

企业版可从 My Oracle Support (MOS)中的补丁&更新选项卡获得，也可从 Oracle 软件交付云获得(需要创建帐户并登录)。建议 MySQL 客户使用 My Oracle Support，因为它比 Oracle 软件交付云包含更多版本，更新更频繁。另一方面，Oracle 软件交付云提供了 MySQL 产品企业版的 30 天试用版。微软视窗的 MySQL 安装程序也有企业版；这可以从我的 Oracle 支持或 Oracle 软件交付云下载。

下载非常简单。图 1-1 显示了用于下载微软 Windows 的 MySQL 安装程序的下载屏幕。

img/463459_1_En_1_Fig1_HTML.jpg

图 1-1

下载 Microsoft Windows 的 MySQL 安装程序

点击下载后，如果您没有登录，将被带到图 1-2 中的页面。在这里，您可以选择登录一个现有的 Oracle Web 帐户，注册一个新的 Oracle Web 帐户，或者点击下载而不使用帐户。不，谢谢，开始下载吧。选择最适合自己的选项。Oracle Web 帐户还用于 My Oracle Support 和 Oracle Software Delivery Cloud，因此如果您是 Oracle 客户，您可以使用现有帐户。

img/463459_1_En_1_Fig2_HTML.jpg

图 1-2

准备下载

从社区下载页面下载其他 MySQL 产品，包括 MySQL Connector/Python，遵循相同的模式。主要区别在于，您需要选择操作系统，并且可以选择您正在使用的操作系统版本。选择的默认操作系统将是您正在浏览的操作系统。图 1-3 展示了下载 MySQL Connector/Python 时如何选择操作系统。

img/463459_1_En_1_Fig3_HTML.jpg

图 1-3

为 MySQL 连接器/Python 选择平台

一旦选择了平台，您就可以选择要下载的特定文件。区别可能在于使用哪个 Python 版本的 MySQL Connector/Python，以及它是纯 Python 还是 C 扩展实现。

一个关于 C 扩展和下载的词。根据平台的不同，C 扩展实现可能与下载的其余部分捆绑在一起并自动安装，或者可能有一个单独的文件要下载。在 Microsoft Windows 上，如果 C 扩展名适用于 Python 版本，则总是包括在内。一般来说，最新的几个受支持的 Python 版本将包含 C 扩展；对于较旧的 Python 版本，它不包括在内。对于 RPM 和 DEB 包，每个 MySQL Connector/Python 版本和支持的 Python 版本都有两个包:一个文件包含纯 Python 实现，另一个包含 C 扩展实现。

可以下载 MySQL Installer 和 MySQL Connector/Python 的企业版的网站设计不同，但思路是一样的。本书不会进一步讨论如何从 My Oracle Support 和 Oracle 软件交付云下载。相反，让我们看看安装过程本身。

装置

MySQL 连接器/Python 支持几种安装连接器的方法。可用的方法取决于操作系统。如果您习惯于安装软件，这些步骤应该不会让您感到惊讶。

如果您的安装方法包括是否安装 C 扩展的选项(例如，RPM 或 DEB 包)，建议包括 C 扩展包。即使您不打算直接使用_mysql_connector模块，使用其他 API 的 C 扩展实现也可以提供更好的性能。

所需的安装类型与下载安装文件的方式密切相关。安装 MySQL Connector/Python 最独特的方式是使用 MySQL 安装程序。本节将使用pip命令、MySQL 安装程序和 MySQL Yum 存储库来完成安装。

pip–所有平台

如果使用 Community Edition，安装 MySQL Connector/Python 的推荐方法是使用pip命令从 Python Packaging Authority (PyPa)安装软件包。这确保了自动解决任何潜在的依赖性，并且相同的安装方法可以用于所有需要 MySQL Connector/Python 的平台。

如果您从 https://www.python.org/ 下载了 Python，那么pip命令是 Python 版本 2.7.9 和更高版本的正常 Python 安装的一部分。值得注意的例外是一些 Linux 发行版，如 RedHat Enterprise Linux、Oracle Linux 和 CentOS Linux。，它仍然使用相对较旧版本的 Python。一般安装说明见 https://pip.pypa.io/en/stable/installing/ 和 https://packaging.python.org/guides/installing-using-linux-tools/ 。侧栏“在 RedHat 系列 Linux 上安装 pip”包括一个如何在 RedHat Enterprise Linux、Oracle Linux 和 CentOS 上安装pip的示例。

当pip可用时，使用install命令安装最新可用的 MySQL 连接器/Python 版本是很简单的。例如，安装的确切输出会有所不同，这取决于是否已经安装了 protobuf 之类的依赖项。输出示例如下

PS: Python> pip install mysql-connector-python
Collecting mysql-connector-python
  Downloading https://files.pythonhosted.org/.../mysql_connector_python-8.0.11-cp36-cp36m-win_amd64.whl
(3.0MB)
    100% |███████████████| 3.0MB 3.5MB/s
Collecting protobuf>=3.0.0 (from mysql-connector-python)
  Using cached https://files.pythonhosted.org/.../protobuf-3.5.2.post1-cp36-cp36m-win_amd64.whl
Requirement already satisfied: six>=1.9 in c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-connector-python)
 (1.11.0)
Requirement already satisfied: setuptools in c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-connector-python)
 (28.8.0)
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.11 protobuf-3.5.2.post1

该示例来自在 PowerShell 中执行pip命令的 Microsoft Windows。该命令假设pip命令位于可执行文件的搜索路径中(这可以在 Windows 上安装 Python 时启用，在 Linux 上通常也是如此)。如果pip命令不在搜索路径中，你必须使用完整路径。当在其他平台上执行安装时，命令是相同的，输出也非常相似。

如果要卸载这个包，命令非常类似；只需使用uninstall命令即可。

因此

PS: Python> pip uninstall mysql-connector-python
Uninstalling mysql-connector-python-8.0.11:

  Would remove:
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\_mysql_connector.cp36-win_amd64.pyd
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\_mysqlxpb.cp36-win_amd64.pyd
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\libeay32.dll
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\libmysql.dll
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\mysql\*
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\mysql_connector_python-8.0.11.dist-info\*
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\mysqlx\*
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\ssleay32.dll
Proceed (y/n)? y
  Successfully uninstalled mysql-connector-python-8.0.11

在 Linux 的 Redhat 系列上安装 pip

在 Oracle Linux、RedHat Enterprise Linux 和 CentOS 上安装pip命令的最佳方式是使用 EPEL Yum 存储库。以下步骤假设您使用的是 Linux 发行版的版本 7。旧版本需要稍微不同的说明。步骤如下:

从 https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm 下载 EPEL 存储库定义。
安装下载的 EPEL RPM。
安装 python-pip 和 python-wheel 包。
可选地，让pip使用pip install –upgrade pip命令升级自身。

python-wheel 包支持用于 python 包的 wheel 内置包格式。 https://pypi.org/project/wheel/亦见。

在 Linux shell 中执行的组合步骤如下:

shell$ wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

...

2018-03-10 20:26:28 (55.3 KB/s) - 'epel-release-latest-7.noarch.rpm' saved [15080/15080]

shell$ sudo yum localinstall epel-release-latest-7.noarch.rpm

...
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : epel-release-7-11.noarch                      1/1
  Verifying  : epel-release-7-11.noarch                      1/1

Installed:
  epel-release.noarch 0:7-11

Complete!

shell$ sudo yum install python-pip python-wheel

...
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : python-wheel-0.24.0-2.el7.noarch              1/2
  Installing : python2-pip-8.1.2-5.el7.noarch                2/2
  Verifying  : python2-pip-8.1.2-5.el7.noarch                1/2
  Verifying  : python-wheel-0.24.0-2.el7.noarch              2/2

Installed:
  python-wheel.noarch 0:0.24.0-2.el7
  python2-pip.noarch 0:8.1.2-5.el7

Complete!

shell$ sudo pip install --upgrade pip

Collecting pip
  Downloading pip-9.0.1-py2.py3-none-any.whl (1.3MB)
    100% |████████████████| 1.3MB 296kB/s
Installing collected packages: pip
  Found existing installation: pip 8.1.2
    Uninstalling pip-8.1.2:
      Successfully uninstalled pip-8.1.2
Successfully installed pip-9.0.1

此时，pip命令已经安装为/usr/bin/pip。在大多数情况下，/usr/bin中的命令可以在不指定完整路径的情况下执行。

Microsoft windows-MySQL 安装程序

对于在 Microsoft Windows 上的安装，由于某种原因您不希望使用pip命令，首选的安装方法是 MySQL Installer。一个优点是它可以用来安装 MySQL Connector/Python 的社区版和企业版。安装哪个版本取决于 MySQL 安装程序的版本。

以下说明假设您的计算机上已经安装了 MySQL 安装程序。如果不是这种情况，请参阅“微软视窗的 MySQL 安装程序”的说明。第一步是启动 MySQL 安装程序。首次使用安装程序时，会要求您接受许可条款。然后你会被带到一个屏幕，在这里你可以选择你想要安装的 MySQL 产品。在讨论完图 1-4 之后，我们将再次回到这一点。

如果你已经使用 MySQL Installer 安装产品，会出现图 1-4 的画面；这是已经安装的 MySQL 产品和可用操作的概述。

img/463459_1_En_1_Fig4_HTML.jpg

图 1-4

MySQL 安装程序屏幕显示已经安装的 MySQL 产品

如果您前段时间安装了 MySQL 安装程序，并且最近没有更新目录，建议首先单击右下角的目录操作，以确保您可以从所有最新版本中进行选择。这将把您带到一个屏幕，您可以在那里执行目录更新。该更新不会更改任何已安装的产品；它只更新 MySQL 安装程序用来通知升级的产品列表，您可以在安装新产品时从中选择。

目录更新后，您可以使用已安装产品列表右侧的 Add 操作添加新产品。这将把你带到图 1-5 所示的屏幕，这也是你第一次启动 MySQL 安装程序时被直接带到的屏幕。

img/463459_1_En_1_Fig5_HTML.jpg

图 1-5

选择要安装的内容

顶部的过滤器可用于缩小或扩大应包含的产品和版本。默认情况下，32 位和 64 位体系结构中的所有软件都包含最新的 GA 版本。如果您想要尝试一个开发里程碑版本或候选版本，您需要通过编辑过滤器来包含预发布版本。在图 1-6 中可以看到一个在 MySQL Connectors 类别下过滤搜索 Connector/Python GA 版本并要求其为 64 位的示例。

img/463459_1_En_1_Fig6_HTML.jpg

图 1-6

过滤产品列表

通过展开 MySQL 连接器组，可以在可用产品下找到 MySQL 连接器/Python。每个受支持的 Python 版本都有一个产品列表。MySQL 安装程序将检查是否安装了正确的 Python 版本。找到正确的版本后，点击指向右侧的箭头，然后点击下一步，将其添加到要安装的产品和功能列表中。

下一个屏幕显示了要安装的产品的概述。确认一切正确后，点击执行开始安装。执行可能需要一点时间，因为它包括下载连接器。一旦安装完成，点击下一步。这将允许您将日志复制到剪贴板并完成。

Microsoft Windows 的 MySQL 安装程序

用于 Microsoft Windows 的 MySQL 安装程序是管理各种 MySQL 产品的入口点(MySQL NDB 集群是一个例外)。它允许您从一个界面安装、升级和删除产品和功能。对配置 MySQL 服务器的支持也是有限的。

MySQL 安装程序有两种风格:一种包含 MySQL Server 版本，另一种不包含(“web”下载)。如果您知道您将安装 MySQL Server，那么使用捆绑了 MySQL Server 的下载会很方便，因为这样可以节省安装时间。无论选择哪种方式，如果您没有准备好本地安装文件，MySQL 安装程序都会下载该产品作为安装的一部分。

要安装 MySQL 安装程序，请按照以下步骤操作:

下载 MySQL 安装程序。如果您使用的是 MySQL 产品的社区版，请从 https://dev.mysql.com/downloads/installer/ 下载。如果使用企业版，从我的甲骨文支持( https://support.oracle.com/ )或甲骨文软件交付云( https://edelivery.oracle.com/ )下载。企业版的两个位置都需要使用现有的 Oracle 帐户或创建一个新帐户。如果您是现有客户，建议使用 My Oracle Support。
下载的文件是一个 MSI 安装程序，但对于企业版，它将包含在一个 zip 文件中，您可以解压缩该文件，然后执行 MSI 安装程序并按照说明进行操作。
如果您还没有下载最新版本的 MySQL 安装程序，您将有机会升级它。建议这样做。
安装完成后，MySQL 安装程序会自动启动。

安装程序也可以稍后启动，例如通过开始菜单。

Linux–MySQL Yum 存储库

在 Linux 发行版的 Community Edition 中安装 MySQL 产品的最简单方法是使用 MySQL 存储库。对于 RedHat Enterprise Linux、Oracle Linux、CentOS 和 Fedora，这意味着 MySQL Yum 存储库。这样,yum命令就可以找到包，Yum 将能够自动解析依赖关系。除了使用pip命令安装 MySQL Connector/Python 之外，如果您希望安装得到管理，这是安装 MySQL 软件的推荐方式。

管理安装意味着安装程序(pip或yum)为您处理依赖关系，并且可以使用安装程序请求升级。对于安装和升级，软件都是自动从存储库中下载的。

MySQL Yum 库是使用 RPM 安装的，可以从 https://dev.mysql.com/downloads/repo/yum/ 下载。选择与您的 Linux 发行版相对应的 RPM。例如，可以使用yum localinstall命令安装 RPM:

shell$ sudo yum localinstall \
            mysql57-community-release-el7-11.noarch.rpm
...
Running transaction
  Installing : mysql57-community-release-el7-11.noarch      1/1
  Verifying  : mysql57-community-release-el7-11.noarch      1/1

Installed:
  mysql57-community-release.noarch 0:el7-11                     

Complete!

MySQL RPMs 用 GnuPG 签名。为了让rpm命令(由yum调用)检查签名并且不抱怨丢失密钥，您需要安装 MySQL 使用的公钥。有几种方法可以做到这一点，如 https://dev.mysql.com/doc/refman/en/checking-gpg-signature.html 中所述。一种选择是从该页面获取公钥，并将其保存在一个文件中。你需要从-----BEGIN PGP PUBLIC KEY BLOCK-----开始到-----END PGP PUBLIC KEY BLOCK-----结束的部分(包括一切)。将密钥保存到一个文件中，例如命名为mysql_pubkey.asc。然后，您将密钥导入 RPM 的密匙环:

shell$ sudo rpm --import mysql_pubkey.asc

一旦安装了存储库和公钥，就可以安装 MySQL Connector/Python，如清单 1-1 所示。

shell$ sudo yum install mysql-connector-python \
                        mysql-connector-python-cext
...
Downloading packages:
(1/2): mysql-connector-python-8.0.11-1.el7.x86_64.rpm | 418 kB 00:00
(2/2): mysql-connector-python-cext-8.0.11-1.el7.x86_6 | 4.8 MB 00:01
---------------------------------------------------------------
Total                                   3.3 MB/s | 5.2 MB 00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-connector-python-8.0.11-1.el7.x86_64   1/2
  Installing : mysql-connector-python-cext-8.0.11-1.el7.x86_64   2/2
  Verifying  : mysql-connector-python-8.0.11-1.el7.x86_64        1/2
  Verifying  : mysql-connector-python-cext-8.0.11-1.el7.x86_64   2/2

Installed:
  mysql-connector-python.x86_64 0:8.0.11-1.el7
  mysql-connector-python-cext.x86_64 0:8.0.11-1.el7

Complete!

Listing 1-1Installing MySQL Connector/Python Using Yum on Linux

这段代码安装了 MySQL Connector/Python 的纯 Python 和 C 扩展(名称中带有 cext )实现。在继续之前，让我们验证一下 MySQL 连接器/Python 的安装。

验证安装

验证 MySQL Connector/Python 安装工作的一个简单方法是创建一个小的测试程序来打印来自mysql.connector模块的一些属性。如果程序执行时没有错误，则安装成功。

清单 1-2 展示了一个检索 MySQL 连接器/Python 版本以及一些其他属性的例子。

import mysql.connector

print(
  "MySQL Connector/Python version: {0}"
  .format(mysql.connector.__version__)
)

print("Version as tuple:")
print(mysql.connector.__version_info__)

print("")
print("API level: {0}"
  .format(mysql.connector.apilevel))

print("Parameter style: {0}"
  .format(mysql.connector.paramstyle))

print("Thread safe: {0}"
  .format(mysql.connector.threadsafety))

Listing 1-2Verifying That the MySQL Connector/Python Installation Works

版本以两种不同的方式打印，一种是字符串，另一种是元组。如果您需要一个应用与 MySQL Connector/Python 的两个不同版本兼容，并且根据版本需要不同的代码路径，那么 tuple 会很有用。

API 级别、参数样式和线程安全属性通常不会改变。它们与mysql.connector模块实现的 Python 数据库 API 规范( https://www.python.org/dev/peps/pep-0249/ )有关。这三个属性是模块必需的全局属性。

使用 MySQL 连接器/Python 8.0.11 时的输出是

PS: Chapter 1> python listing_1_1.py
MySQL Connector/Python version: 8.0.11
Version as tuple:
(8, 0, 11, '', 1)

API Level: 2.0
Parameter style: pyformat
Thread safe: 1

MySQL 服务器

MySQL 连接器/Python 本身价值不高。除非您有一个 MySQL 服务器实例可以连接，否则您将被限制在检查版本之类的事情上，就像上一节中的例子一样。因此，如果您还没有安装 MySQL 服务器的权限，您也需要安装它。本节将简要概述 MySQL 服务器的安装和配置。

装置

如果您使用 MySQL 安装程序或 MySQL Yum 存储库，MySQL Server 的安装过程类似于针对 MySQL Connector/Python 描述的步骤。在这两种情况下，安装程序都会为您设置 MySQL。此外，还可以选择在 Microsoft Windows 上使用 zip 归档文件安装，或者在 Linux、macOS、Oracle Solaris 和 FreeBSD 上使用 tar 归档文件安装。

注意

此讨论假设一个新的安装。如果已经安装了 MySQL，也可以选择升级。但是，如果您当前的 MySQL 安装不是来自 MySQL 存储库，那么最好先删除现有的安装以避免冲突，然后进行全新安装。

由于在使用安装程序时，MySQL Server 的安装步骤与 MySQL Connector/Python 非常相似，所以本文将重点讨论使用 zip 或 tar 归档文件进行安装。在 Linux 上使用安装程序时，关于检索为管理员帐户(root@localhost)设置的密码的讨论也是相关的。由于 MySQL 安装程序是一个交互式安装程序，它会问你密码应该是什么，并为你设置。

小费

本书中关于 MySQL 服务器安装的讨论只涉及了一些基础知识。完整的安装说明见 https://dev.mysql.com/doc/refman/en/installing.html 和 https://dev.mysql.com/doc/refman/en/data-directory-initialization-mysqld.html 。

如果您需要在同一台计算机上安装多个不同的版本，使用 zip 或 tar 归档文件会特别有用，因为它允许您找到您喜欢的安装位置。如果选择这种方法，您需要手动初始化数据目录。在以下示例中可以看到在 Microsoft Windows 上这样做的示例:

PS: Python> D:\MySQL\mysql-8.0.11-winx64\bin\mysqld
              --basedir=D:\MySQL\mysql-8.0.11-winx64
              --datadir=D:\MySQL\Data_8.0.11
              --log_error=D:\MySQL\Data_8.0.11\error.log
              --initialize

该命令被分成几行，以提高可读性。确保在执行时将所有部分组合成一行。该命令可能需要一点时间才能完成，尤其是在非基于内存(即旋转)的磁盘驱动器上安装时。

Linux 和其他类 Unix 系统中的命令非常相似，除了添加了-- user选项:

shell$ /MySQL/base/8.0.11/bin/mysqld \
          --basedir=/MySQL/base/8.0.11/ \
          --datadir=/MySQL/Data_8.0.11 \
          --log_error=/MySQL/Data_8.0.11/error.log \
          --user=mysql \
          --initialize

这两个命令使用几个参数。他们是

--basedir:这个选项告诉 MySQL 服务器二进制文件、库等在哪里。已安装。这个目录包括一个 bin、lib、share 和更多子目录，其中包含 MySQL 服务器所需的文件。
--datadir:该选项告知数据的存储位置。这是由命令初始化的目录。此目录必须不存在或为空。如果它不存在，--log_error选项不能指向数据目录中的文件。
--log_error:该选项告知日志信息的写入位置。
--user:在 Linux 和 Unix 上，这个选项用来告诉 MySQL 将以哪个用户的身份执行。只有当您以 root 用户身份初始化数据目录时，这才是必需的(但通常是允许的)。在这种情况下，MySQL 将确保新创建的文件归由--user参数指定的用户所有。传统上使用的用户是mysql用户，但是对于个人测试实例，您也可以使用您的普通登录用户。
--initialize:这个选项告诉 MySQL 初始化数据目录。

初始化包括设置root@localhost账户的密码。密码是随机的，可以在错误日志中找到；这也适用于使用 RPMs 等工具安装 MySQL 的情况。但是，MySQL 安装程序会在安装过程中要求输入密码并进行设置。如果您使用的是 macOS，密码也会显示在通知中。包含临时密码的错误日志示例如下

2018-03-11T05:01:08.871014Z 0 [System] [MY-010116] D:\MySQL\mysql-8.0.4-rc-winx64\bin\mysqld.exe (mysqld 8.0.4-rc) starting as process 3964 ...
2018-03-11T05:01:20.240818Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.

2018-03-11T05:01:20.259178Z 5 [Note] [MY-010454] A temporary password is generated for root@localhost: fj3dJih6Ao*T

首次连接时，您需要此密码。连接后，您必须先更改密码，然后才能执行常规查询，因为安装过程中生成的随机密码会被标记为过期。您使用ALTER USER语句更改密码，如清单 1-3 所示。

PS: Python> D:\MySQL\mysql-8.0.11-winx64\bin\mysql --user=root --password
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; org.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark ofOracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '&lknjJ2lAc1)#';

Query OK, 0 rows affected (0.15 sec)

Listing 1-3Changing the Password of the root@localhost Account

请确保您选择了一个别人很难猜到的密码。您还可以使用该命令更改用户的其他设置，如 SSL 要求、身份验证插件等。

小费

MySQL 8.0 中默认的认证插件是caching_sha2_password插件。它提供了很好的安全性，因为它基于 sha256 加盐散列。同时，缓存使其性能良好。然而，由于它是一个新的认证插件，旧的连接器和客户端包括 MySQL Connector/Python 2.1.7 和更早的版本，MySQL Server 5.7 的 MySQL 命令行客户端，以及 PHP 和 Perl 等第三方连接器在编写时都不支持caching_sha2_password插件。如果您需要使用这些连接器或客户端中的一个来连接，您可以使用旧的mysql_native_password插件来代替(因为它是基于 sha1 的，没有 salted，所以不太安全)。有关CREATE USER和ALTER USER语句语法的更多信息，请参见 https://dev.mysql.com/doc/refman/en/create-user.html 和 https://dev.mysql.com/doc/refman/en/alter-user.html 。

除非您使用 MySQL 安装程序，否则该实例将使用默认配置。最后要考虑的是如何改变配置。

配置

在某些情况下，有必要更改新安装的 MySQL 服务器实例的配置。通常，默认值是一个很好的起点。显然，对于生产服务器来说，有些更改是必需的，但通常情况下，少量更改比大量更改要好。对于本书中的示例来说，默认配置很好。

也就是说，在数据目录被手动初始化的例子中，您已经看到了一些非默认设置。此外，如果您正在开发将部署到生产环境的应用，建议使用尽可能接近生产环境的配置，以避免因差异而出现问题。当然，这并不意味着您正在开发的桌面应该能够为 InnoDB 缓冲池分配半 TB 的内存，因为生产服务器正在使用它，您可以使用一个类似的配置，但是要缩小规模。

小费

您可以在参考手册的 https://dev.mysql.com/doc/refman/en/server-system-variables.html 和 https://dev.mysql.com/doc/refman/en/option-files.html 阅读更多关于配置 MySQL 的信息，包括选项的完整列表。

一般来说，最好使用 MySQL 配置文件来设置任何必需的选项。这样可以避免启动 MySQL 时遗漏一些选项。然后，您可以使用带有配置文件路径的--defaults-file选项启动 MySQL 守护进程(Linux/Unix 上的mysqld和 Microsoft Windows 上的mysqld.exe)。按照惯例，MySQL 配置文件在微软 Windows 上被命名为my.ini，在其他平台上被命名为my.cnf。

如果您使用的是 Microsoft Windows 并选择安装 MySQL 作为服务，您将通过控制面板应用启动和停止 MySQL 服务(或让 Microsoft Windows 自动完成)。在这种情况下，配置文件甚至更有用，因为您可以指定在服务定义中使用它，这样可以避免在以后想要更改 MySQL 配置时修改服务。

配置文件遵循 INI 文件格式。下面是一个示例，其中包含本节前面 Microsoft Windows 上的初始化选项以及 TCP 端口号:

[mysqld]
basedir   = D:\MySQL\mysql-8.0.11-winx64
datadir   = D:\MySQL\Data_8.0.11
log_error = D:\MySQL\Data_8.0.11\error.log
port      = 3306

关于安装和配置 MySQL 服务器的讨论到此结束。一个相关的主题是如何创建应用用来连接 MySQL 的数据库用户。

创建应用用户

当应用连接到 MySQL 时，需要指定用于连接的用户名。此外，MySQL 考虑了连接来自的主机名，因此用户的帐户名形成为username@hostname。用户的权限决定了允许用户在数据库中做什么。

MySQL 服务器有一个可用于登录的标准用户，即root@localhost用户。该用户拥有所有权限；也就是说，它是一个管理员帐户。出于几个原因，应用不应该使用这个用户，这将在下面的讨论中解释。

一般来说，应用不应该拥有做任何事情的权限。例如，不应该允许应用访问它不需要的表，并且很少要求应用管理用户。此外，MySQL 对允许的并发连接数有限制(max_connections配置选项)。但是，为拥有CONNECTION_ADMIN ( SUPER)权限的用户保留了一个额外的连接。因此，如果应用用户拥有所有特权，它可以阻止数据库管理员调查为什么所有连接都在使用中。

深入 MySQL 特权系统的细节已经超出了本书的范围。主要的要点是，您应该为您的用户分配所需的最低权限，包括在开发阶段，因为当您准备好部署应用时，根据需要添加新权限比删除不必要的权限要容易得多。

小费

熟悉 MySQL 的安全特性是值得的，包括访问权限系统和用户帐户管理。MySQL 参考手册中的安全章节是一个极好的来源: https://dev.mysql.com/doc/refman/en/security.html 。

以下 SQL 语句可用于创建一个测试用户，该用户拥有本书中示例所需的权限:

mysql> CREATE USER 'pyuser'@'localhost'
              IDENTIFIED BY 'Py@pp4Demo';

mysql> GRANT ALL PRIVILEGES
             ON world.*
             TO 'pyuser'@'localhost';

mysql> GRANT ALL PRIVILEGES
             ON py_test_db.*
             TO 'pyuser'@'localhost';

假设测试程序将在安装 MySQL 的同一台主机上执行。如果不是这样，用执行测试程序的主机名替换localhost。GRANT语句中的ALL PRIVILEGES给出了模式(数据库)级别上所有可用的特权，但不包括管理特权。这仍然超出了典型应用的需求，但是这里使用它是为了简单起见，并允许演示通常不会在应用中执行的查询。

密码被选择为Py@pp4Demo。这不是一个非常强的密码，强烈建议使用一个更难猜到的不同密码。

如果您想使用第七章中简要提到的world_x样本数据库，您还需要以下权限:

mysql> GRANT ALL PRIVILEGES
             ON world_x.*
             TO 'pyuser'@'localhost';

然而，本书中讨论的例子都没有使用world_x示例数据库。world_x示例数据库的安装说明与下一步非常相似，就是为第 3 、 4 和 5 章中的代码示例安装一些示例数据。

安装世界样本数据库

在整本书中，世界样本数据库被用于几个例子。示例数据库被视为“其他 MySQL 文档”的一部分，可以从 https://dev.mysql.com/doc/index-other.html 访问。世界数据库可以作为 gzip 文件或 zip 文件下载；无论哪种方式，解压后都是单个文件:world.sql。

注意

有world数据库和world_x数据库。第 3 、 4 和 5 章使用world数据库。world_x数据库不是必需的，但是如果您想让它用于您自己的测试，可以使用类似的步骤安装。

world.sql文件是独立的。如果存在的话，它将删除world模式，并用三个表重新创建它:country、countrylanguage和city，包括一些示例数据。应用world.sql文件最简单的方法是使用与world.sql文件所在目录相同的mysql命令行客户端( https://dev.mysql.com/doc/refman/en/mysql.html ):

shell$ mysql --user=pyuser --password \
             --host=127.0.0.1 --port=3306 \
             --execute="SOURCE world.sql"
Enter password:

这假设mysql二进制文件在执行搜索路径中；否则，必须使用完整路径。在 Microsoft Windows 上，将整个命令放在同一行，并删除反斜杠。结果表格在清单 1-4 中列出。

mysql> SHOW TABLES FROM world;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE world.city\G
*************************** 1\. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM world.city;
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE world.country\G
*************************** 1\. row ***************************
       Table: country
Create Table: CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM world.country;
+----------+
| COUNT(*) |
+----------+
|      239 |
+----------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE world.countrylanguage\G
*************************** 1\. row ***************************
       Table: countrylanguage
Create Table: CREATE TABLE `countrylanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM world.countrylanguage;
+----------+
| COUNT(*) |
+----------+
|      984 |
+----------+
1 row in set (0.00 sec)

Listing 1-4The Tables of the world Sample Database

小费

有关世界样本数据库的更多信息，包括安装说明，请参见 https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html 。

在结束这一章之前，我们需要了解一下代码示例。

代码示例

这本书里有许多示例程序。这些程序已经用 Python 3.6 测试过了。对于其他 Python 版本，包括来自 Oracle Linux 7/Red Hat Enterprise Linux(RHEL)7/CentOS 7 的 Python 2.7，这些示例只需稍加修改即可使用。MySQL 连接器/Python 特定的部分不需要任何更改。

在 Python 2 中，建议加载打印函数from__future__:

from __future__ import print_function

此外，Python 2 中的 UTF-8 字符串处理是不同的，因此可能有必要使用encode()方法来打印字符串。例如:

print(
  "{0:15s}   {1:⁷s}   {2:4.1f}".format(
    city['Name'].encode('utf8'),
    city['CountryCode'].encode('utf8'),
    city['Population']/1000000
  )
)

使用mysql.connector模块的例子假设一个名为my.ini的文件存在于执行 Python 的目录中，该文件带有连接 MySQL 服务器所需的连接选项。一个示例配置文件是

[connector_python]
user     = pyuser
host     = 127.0.0.1
port     = 3306
password = Py@pp4Demo

使用mysqlx模块的示例将配置存储在名为config.py的文件中，该文件也位于执行 Python 的同一个目录中。示例配置如下

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

示例中的编码风格是针对印刷品，尤其是 Kindle 等电子书阅读器而优化的。因为这留下了很少的空间来处理，所以这些行通常保持在 40 个字符以下，而“长”行最多 50 个字符，以最小化换行量。不利的一面是，这意味着不可能遵循一种标准的编码风格，例如 PEP 8 中指定的风格( https://www.python.org/dev/peps/pep-0008/ )。建议在自己的项目中遵循 PEP 8 或另一个成熟的编码标准。

列表中出现的所有示例程序都可以下载。文件名反映了清单编号；例如，清单 1-2 中的代码可以在文件listing_1_2.py中找到。有关如何下载源代码的说明，请参见该书的主页。

安装和准备工作到此结束。下一步是创建从 MySQL 连接器/Python 到 MySQL 服务器的连接，这是下一章的主题。

摘要

这一章帮助你开始并运行。首先介绍了 MySQL 连接器/Python。最新的 GA 发布系列是 8.0 版，与大多数其他 MySQL 产品一样。MySQL 产品有社区版和商业企业版。对于 MySQL Connector/Python，主要区别是许可和支持，两个版本都可以和本书一起使用。

MySQL Connector/Python 有三个 API:两个仅支持 SQL 语句的遗留 API 和一个支持 NoSQL 和 SQL 查询的名为 X DevAPI 的新 API。如何使用这三个 API 是本书其余部分的主题。

为了开始，您下载并安装了 MySQL Connector/Python 和 MySQL Server。有一个关于配置 MySQL 服务器的简短讨论，关于如何创建可用于本书的用户的说明，如何安装测试数据，以及关于代码示例的一句话。

你已经准备好使用 MySQL 连接器/Python 了。下一章将向您展示如何使用连接器/Python API 中的 API(mysql.connector模块)进行连接。

Footnotes 1

https://mysqlrelease.com/2018/03/mysql-8-0-it-goes-to-11/

如果你熟悉 git，建议使用 GitHub 获取源代码，因为它可以让你轻松地切换分支，其中包括旧版本。然而，与 PyPi 一样，最新的更改可能会延迟上传。

二、连接到 MySQL

在前一章中，您安装了 MySQL Connector/Python，并确保该模块工作正常。然而，打印连接器的版本字符串并不令人兴奋，所以本章将开始两个遗留 API 的特性之旅。

mysql.connector模块包括 Python 数据库 API 的实现，在 PEP249 ( https://www.python.org/dev/peps/pep-0249/ )中定义。这包括在使用相同 API 的同时使用 C 扩展的选项。这个 API 是第 2 - 5 章的主要焦点。此外，第四章简要讨论了实现 C 扩展 API 的_mysql_connector模块。

本章详细介绍了创建和配置 MySQL 连接的细节。建立联系很简单，也是你要学的第一件事。然而，这种联系不仅仅是创造出来的。本章的其余部分将讨论如何配置连接，包括避免将用户名和密码硬编码到应用中的技巧。本章最后讨论了其他与连接相关的选项，特别关注字符集。

从 Python 创建连接

到这一步需要做一些工作，但是现在您已经准备好第一次从 Python 连接到 MySQL 了。本节将介绍创建连接的语法、最常见的连接选项、创建连接、重新配置连接的示例以及一些连接的最佳实践。

句法

有几种方法可以创建连接。其中四个是

mysql.connector. connect()功能:这是最灵活的连接方式。它提供了一种使用 C 扩展创建连接或者启用连接池和故障转移相关选项的统一方式。该函数作为包装器工作，根据设置返回适当类的对象。
MySQLConnection()构造函数
MySQLConnection.connect()方法:它要求首先实例化不带参数的MySQLConnection类，然后创建连接。
与之前使用的MySQLConnection.connect()方法相同，但不同之处在于MySQLConnection.config()方法被显式调用来配置连接。

MySQLConnection类是纯 Python 实现。或者，可以使用CMySQLConnection类，它为 Python 数据库 API 提供了 C 扩展后端的实现。

所有方法都以相同的连接对象结束，并且它们都将连接选项作为关键字参数。这意味着您可以选择任何方式来创建最适合程序的连接。然而，由于mysql.connector. connect()函数是最强大的，它是连接的首选方式，因为它使得在纯 Python 和 C 扩展实现之间切换或者启用连接池或故障转移变得更加容易。

小费

使用mysql.connector.connect()功能创建连接可访问所有与连接相关的功能。

图 2-1 显示了使用四种方式创建连接的基本流程。红色(深灰色)框直接从应用代码调用，黄色(浅灰色)框由最后一个间接调用的方法调用。该图使用了MySQLConnection类；然而，如果使用了CMySQLConnection类，同样适用。

img/463459_1_En_2_Fig1_HTML.jpg

图 2-1

创建连接的流程

最左边的路线是使用mysql.connector. connect()功能的路线。Python 程序调用带有连接参数的函数，然后该函数处理其余部分。该图假设创建了一个MySQLConnection连接(使用纯 Python 实现)，但是如果使用 C 扩展，该函数也可以返回一个CMySQLConnection对象。mysql.connector.connect()函数的基本语法是

db = mysql.connector.connect(**kwargs)

左起第二条路径让 Python 程序在实例化MySQLConnection类时将连接参数发送给构造函数。这将触发构造函数调用connect()方法，后者又调用config()方法。使用MySQLConnection类时的语法是

db = mysql.connector.MySQLConnection(**kwargs)

在左起第三条路径中，首先实例化了MySQLConnection类，然后显式调用了connect()方法。代码语法变成了

db = mysql.connector.MySQLConnection()
db.connect(**kwargs)

最后，在最右边的路径中，所有步骤都是显式完成的。注意，与创建连接的其他三种方式相比，在这种情况下调用connect()和config()方法的顺序颠倒了。语法是

db = mysql.connector.MySQLConnection()
db.config(**kwargs)
db.connect()

在创建一些真正的连接之前，有必要看一看创建连接时最常用的选项。

常见连接选项

表 2-1 总结了最常用的选项，用于指定如何连接到 MySQL、作为谁进行身份验证以及使用哪个密码。

表 2-1

常见的连接相关选项

争吵

缺省值

描述

| | --- | --- | --- | | host | 127.0.0.1 | 安装了您要连接的 MySQL 实例的主机的主机名。默认设置是连接到回环(即本地主机)。 | | port | 3306 | MySQL 正在监听的端口。端口 3306 是标准的 MySQL 端口。 | | unix_socket | | 在 Linux 和 Unix 上，可以使用 Unix 套接字连接到本地主机上的 MySQL 实例。指定套接字文件的路径。 | | user | | 应用用户的用户名。不要包含@和以下主机名；这是为第一章中创建的测试用户准备的。只需指定pyuser。 | | password | | 用来进行身份验证的密码。对于测试用户，这将是Py@pp4Demo。 | | ssl_ca | | 包含 SSL 证书颁发机构(CA)的文件的路径。 | | ssl_cert | | 包含公共 SSL 证书的文件的路径。 | | ssl_cipher | | 用于连接的 SSL 密码。通过使用 SSL 连接 MySQL 并执行查询SHOW GLOBAL STATUS LIKE‘SSL _ cipher _ list’，可以获得有效密码的列表；当前使用的密码可以通过 Ssl_cipher 会话状态变量来确定。 | | ssl_disabled | | 强制使用非 SSL 连接。 | | ssl_key | | 包含私有 SSL 密钥的文件的路径。 | | ssl_verify_cert | False | MySQL 连接器/Python 是否应该根据用ssl_ca选项指定的 CA 来验证 MySQL 服务器使用的证书。 |

例如，如果您使用过 MySQL 命令行客户端，那么这些选项名称可能看起来很熟悉。这不是巧合。使用这些选项，可以演示如何创建连接。

小费

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

连接示例

是时候将创建连接的四种方式以及最常见的连接选项结合起来，创建用于创建 MySQL Connector/Python 连接的源代码示例了。清单 2-1 展示了如何使用创建连接的四种方式进行连接。这些示例的顺序与本节前面讨论的顺序相同。

import mysql.connector

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

# ---- connect() function ----

db1 = mysql.connector.connect(

  **connect_args

)

print(
  "MySQL connection ID for db1: {0}"
  .format(db1.connection_id)
)

db1.close()

# ---- Explicit MySQLConnection ----

db2 = mysql.connector.MySQLConnection(

  **connect_args

)

print(
  "MySQL connection ID for db2: {0}"
  .format(db2.connection_id)
)

db2.close()

# ---- Two steps manually ----

db3 = mysql.connector.MySQLConnection()

db3.connect(**connect_args)

print(
  "MySQL connection ID for db3: {0}"
  .format(db3.connection_id)
)

db3.close()

# ---- All three steps manually ----

db4 = mysql.connector.MySQLConnection()

db4.config(**connect_args)

db4.connect()

print(
  "MySQL connection ID for db4: {0}"
  .format(db4.connection_id)
)

db4.close()

Listing 2-1Examples of Connecting to MySQL

这四种连接使用相同的连接选项。一旦创建了连接，就使用连接的connection_id属性打印连接的连接 ID(来自 MySQL 服务器端)。最后，使用close()方法关闭连接。当应用完成连接时，最好总是显式关闭连接。

小费

当你完成时，总是关闭连接。关闭连接可以确保与 MySQL 服务器完全断开连接。在服务器终止连接之前，可能还需要一些时间；同时，它占用了一个可用的连接。

输出类似于以下示例，只是连接 id 不同:

MySQL connection ID for db1: 13
MySQL connection ID for db2: 14
MySQL connection ID for db3: 15
MySQL connection ID for db4: 16

还可以为现有的连接调用config()方法。接下来让我们讨论如何重新配置连接和重新连接。

重新配置和重新连接

通常不这样做，但是可以重新配置现有的连接并重新连接。在这种情况下，重新配置意味着可能要更改所有选项，包括应用所连接的 MySQL 服务器实例。当进行这样的更改时，有必要明确地告诉 MySQL Connector/Python 重新连接。

要重新配置一个连接，使用config()方法，方法与初始连接之前相同。一旦创建了所需的新配置，如果任何配置更改需要新的连接，就调用reconnect()方法。调用reconnect()关闭旧的连接并用新的配置创建一个新的连接。清单 2-2 展示了一个重新配置连接的例子。

import mysql.connector

initial_args = {
  "host": "127.0.0.1",
  "port": 3306,
  "user": "pyuser",
  "password": "Py@pp4Demo",
};

# Create initial connection
db = mysql.connector.connect(
  **initial_args
)

print(
  "Initial MySQL connection ID ...: {0}"
  .format(db.connection_id)
)

new_args = {

  "host": "<your_IP_goes_here_in_quotes>",

};

db.config(**new_args)

db.reconnect()

print(
  "New MySQL connection ID .......: {0}"
  .format(db.connection_id)
)

db.close()

Listing 2-2Reconfiguring a Connection

这个例子要求在同一个 MySQL 实例上有第二个用户帐户。用户与现有的pyuser@localhost连接相同，但被定义为从公共 IP 地址连接(替换为您计算机的 IP 地址，或者如果 IP 地址解析为主机名):

mysql> CREATE USER pyuser@'<your_IP_goes_here_in_quotes>'
                   IDENTIFIED BY 'Py@pp4Demo';
Query OK, 0 rows affected (0.84 sec)

任何防火墙允许连接也是必要的。

从示例中可以看出，没有必要更改所有的配置选项。那些没有在新选项集中显式设置的值将保持原来的值。程序的输出是(除了 IDs 之外)

Initial MySQL connection ID ...: 21
New MySQL connection ID .......: 22

本节的最后一个主题是一些关于连接的最佳实践。

连接最佳实践

当涉及到连接时，有一些很好的最佳实践可以遵循。最佳实践总是很困难，因为所有应用都有独特的需求。因此，建议将集中在 MySQL 连接器/Python 的技术方面。

主要的最佳实践是

当你完成时，总是关闭连接。这个已经讨论过了。
使用 SSL (TLS)加密连接。如果您要连接到远程主机，这一点尤其重要，如果连接是在不安全的网络上，这一点更为重要。一个例外是使用 Unix 套接字进行连接，因为即使不使用 SSL，这也总是被认为是安全的连接。
不要将配置硬编码到源代码中。这尤其适用于密码。

注意

虽然 MySQL 使用ssl_作为与加密连接相关的选项的前缀，但实际上使用的是 TLS。

在 MySQL 8.0 和 MySQL 5.7 的某些版本中，默认情况下使用自签名证书启用 SSL，MySQL Connector/Python 默认情况下将使用加密连接。

到目前为止，这些例子有一个主要的缺陷:它们不仅硬编码了连接到哪里，还硬编码了用户名，尤其是密码。这使得代码更难维护，而且这也是一个安全问题，因为任何有权访问源代码的人都可以看到密码。硬编码连接选项还意味着开发和生产系统必须共享连接选项，或者部署过程需要更改源代码来更新配置参数。

警告

不要将密码存储在源代码中。

两者都不是好的选择，因此必须找到替代方案。下一节将讨论另一种选择:使用配置文件。

配置文件

在调用中直接指定连接选项以创建到 MySQL 的连接的方法对于快速测试非常有用，但是在实际应用中这样做既不实用也不安全(对于密码来说)。这一节将首先讨论一些替代方案，然后将详细讨论如何使用 MySQL 配置文件。

硬编码配置的替代方法

有几种方法可以避免将连接配置硬编码到源代码中。每种方法都有利弊，所以这不是一个一刀切的问题。将讨论四种方法:

交互询问信息
使用环境变量
从应用自己的配置文件或作为命令行参数读取信息
使用 MySQL 配置文件。

如果你正在编写一个可以被不同用户使用的程序，那么交互式方法是很棒的，所以不知道程序将以谁的身份连接。这也是将密码传递给程序的最安全的方式。然而，对于更多类似守护进程的进程来说，每次需要重新启动进程时都要求手动启动它并不方便。

环境变量可用于指定会话选项。子进程将继承父进程的环境，因此环境变量可用于将设置传递给子进程，例如从 shell 传递给应用。这是一种配置应用的好方法，不需要文件或解析命令行上的选项。例如，配置在 Docker 等容器中运行的应用是一种常见的方式。 ¹

使用环境变量有一些缺点。当自动启动进程时，有必要将环境变量存储在一个文件中，这意味着它最终会成为一个配置文件的替代格式。环境通常也是长寿的；例如，如果应用自己启动新进程，它将默认传递其环境，包括潜在的秘密信息，如密码。具有高权限的用户也可以读取环境变量。因此，在使用环境变量时应该小心。

使用应用自己的配置文件或提供选项作为命令行参数意味着所有的配置都在一个地方完成。在这种情况下，MySQL 选项的处理方式与其他选项相同，编写代码时只需将选项及其值传递给 MySQL 连接。

警告

使用密码作为命令行选项时要非常小心。主机上的其他用户可能会看到传递给程序的参数，比如在 Linux 上使用ps命令。因此，建议不要将密码指定为命令行参数。

然而，还有另一种方法。MySQL Connector/Python 拥有读取 MySQL 配置文件的原生支持。在应用自己的配置文件上使用这种方法的一些原因是，除了与 MySQL 相关的选项之外，应用可能不需要配置文件，或者应用配置和 MySQL 配置可能有不同的所有者。如果开发人员负责定义应用本身的行为，而数据库管理员负责 MySQL 特定的选项，则可能会发生后一种情况。

因为这本书是关于使用 MySQL 连接器/Python 特性的，而不是一般的 Python 编程，所以四个选项中唯一要详细讨论的是使用 MySQL 配置文件的选项。

使用 MySQL 配置文件

MySQL 的配置文件使用INI文件格式。以下是一个使用 MySQL Connector/Python 的简单示例，使用了与本章前面相同的配置:

[connector_python]
user     = pyuser
host     = 127.0.0.1
port     = 3306
password = Py@pp4Demo

有两个连接选项控制 MySQL 配置文件的使用:

option_files:该选项指定要读取的一个或多个配置文件的路径。该值可以是字符串或字符串列表。没有默认值。
option_groups:该选项指定从哪些选项组中读取。选项组被指定为方括号中的名称；在示例配置中，选项组是connector_python。该值是包含组名的字符串列表。默认是从client和connector_python组中读取。

按照惯例，MySQL 配置文件在微软 Windows 上称为my.ini，在其他平台上称为my.cnf。从功能角度来看，对文件名或文件扩展名没有要求。

需要注意的一个重要特性是,option_groups选项不会平等对待所有组。具体来说，connector_python组是特殊的，因为该组中的所有选项都必须有效，否则将引发ValueError异常。对于其他组，未知选项将被忽略。忽略未知选项的原因是几个程序可能会读取相同的选项组。例如，客户端组也由mysql命令行客户端和其他 MySQL 客户端程序读取。

清单 2-3 展示了一个连接 MySQL 的例子，连接选项是从与程序位于同一目录的my.ini文件中读取的。

import mysql.connector

db = mysql.connector.connect(

  option_files="my.ini")

print(__file__ + " - single config file:")
print(
  "MySQL connection ID for db: {0}"
  .format(db.connection_id)
)

db.close()

Listing 2-3Using a MySQL Configuration File

输出类似于前面打印连接 ID 的示例，例如:

listing_2_3.py - single config file:
MySQL connection ID for db: 35

在某些情况下，您可能希望将 MySQL 配置分成几个文件。例如，假设几个应用需要连接到同一个 MySQL 后端，因此它们共享主机和端口信息，但是每个应用使用不同的凭据进行连接。继续这个例子，可以用以下内容创建两个文件my_shared.ini和my_app_specific.ini:

my_shared.ini:

[client]
host     = 127.0.0.1
port     = 3306

my_app_specific.ini:

[connector_python]
user     = pyuser
password = Py@pp4Demo

测试程序所需的唯一改变是将option_ files的值改为一个列表。为了演示如何设置option_groups选项，它也被添加到程序中。产生的源代码可以在清单 2-4 中看到。

import mysql.connector

db = mysql.connector.connect(

  option_files = [
    "my_shared.ini",
    "my_app_specific.ini"
  ],
  option_groups = [
    "client",
    "connector_python"
  ]

)

print(__file__ + " - two config files:")
print(
  "MySQL connection ID for db: {0}"
  .format(db.connection_id)
)

db.close()

Listing 2-4Using Multiple Configuration Files

输出如下(ID 除外，它会随着执行的不同而变化):

listing_2_4.py - two config files:
MySQL connection ID for db: 42

最后要考虑的是路径名。如果指定了相对路径，则执行 Python 的目录将用作基本目录。例如，使用下面的命令来执行一个程序(ID 通常是不同的):

PS C:\MySQL> python Source/test.py
MySQL connection ID for db: 56

当C:\MySQL是当前工作目录时执行。如果test.py有option_files="my.ini"，那么my.ini文件必须位于C:\MySQL。

另一个观察结果是，对于 Microsoft Windows，使用反斜杠()还是正斜杠(/)来分隔路径组件(目录)是可选的。

配置文件的讨论到此结束。本章的最后一个主题是 MySQL Connector/Python 支持的连接的其余选项。

一般配置

到目前为止，已经讨论过的唯一配置选项是指定连接到哪里、以谁的身份连接以及是否使用 SSL 所需的选项。还有其他几个选项与应用的行为更相关。这些选项是本节的主题。

表 2-2 至表 2-5 总结了本章前面的连接选项列表中未包含的选项，以下选项类型各有一个表:连接、字符集、查询行为和警告。本书的其余部分将包括使用其中几个选项的例子。

关系

除了“创建连接”一节中讨论的选项之外，还有更多连接选项。它们并不常用，但在某些用例中可能是必需的。表 2-2 总结了这些选项。一些选项将在表后更详细地讨论。

表 2-2

不太常见的连接相关选项

名字

缺省值

描述

| | --- | --- | --- | | auth_plugin | | 要使用哪个认证插件。例如，当使用 MySQL Connector/Python 2.1 连接到 MySQL Server 8.0 时，这是必需的，因为旧的 MySQL Connector/Python 版本不支持服务器的默认身份验证插件。 | | client_flags | | 通过标志配置几个选项的另一种方法。 | | compress | False | 启用后，网络流量将被压缩。 | | connection_timeout | | 创建连接时等待多长时间后超时。 | | converter_class | | 指定用于将原始行数据转换为 Python 类型的自定义转换器类。 | | failover | | 字典元组，指定在主连接失败时要故障转移到的备用 MySQL 服务器实例。这仅在使用mysql.connector. connect()功能时受支持。 | | force_ipv6 | False | 当True时，尽可能使用 IPv6。 | | pool_name | 自动生成 | 连接池的名称。默认情况下，该名称是通过连接host、port、user和database连接选项的值生成的。名称最长为pooling.CNX_POOL_MAXNAMESIZE(默认为 64 个)字符，允许使用字母数字字符以及以下字符:。、_、:、-、*、$和#。只有使用mysql.connector. connect()函数或直接实例化pooling.MySQLConnectionPool构造函数类才能支持这一点。 | | pool_reset_session | True | 当True时，当连接返回到池中时，会话变量被重置。只有使用mysql.connector.connect()函数或者直接实例化pooling.MySQLConnectionPool构造函数类才能支持这一点。 | | pool_size | 5 | 池中容纳的连接数。该值必须至少为 1，最多为pooling.CNX_POOL_MAXSIZE(默认为 32)。只有使用mysql.connector. connect()函数或通过直接实例化pooling.MySQLConnectionPool构造函数类才支持这一点。 | | use_pure | False | 当True时，使用连接器的纯 Python 实现。当False时，使用 C 扩展。如果未指定选项，默认情况下使用 C 扩展(如果已安装);否则，它会退回到纯 Python 实现。这仅支持使用mysql.connector.功能。在大多数情况下，建议使用 C 扩展。 |

通过压缩应用和 MySQL 服务器之间传输的数据(反之亦然),可以使用compress选项以额外的计算资源为代价来减少网络流量。如果将大型 SQL 语句发送到服务器，或者将大型查询结果返回到应用，并且应用安装在远程主机上，这可能特别有用。

值得多加注意的四个选项是failover和pool选项。failover选项可用于定义一个或多个 MySQL 服务器实例，如果与主实例的连接失败，MySQL Connector/Python 将故障转移到这些实例。每个可选的 MySQL 服务器实例都被指定为元组或列表中的一个字典。pool选项设置了一个连接池，应用可以向其请求连接。这些选项将在第五章中详细讨论。

client_flags选项可用于设置多个选项。可用选项列表可以使用ClientFlag常量的get_full_info()方法确定:

from mysql.connector.constants import ClientFlag

print("\n".join(
  sorted(ClientFlag.get_full_info())
))

连接器/Python 8.0.11 的输出可以在清单 2-5 中看到。首先列出客户端标志的名称，然后是对标志控制内容的描述。大多数标志也有专用选项，但有一些附加标志如INTERACTIVE只能通过client_flags选项设置。

CAN_HANDLE_EXPIRED_PASSWORDS : Don't close the connection for a connection with expired password
COMPRESS : Can use compression protocol
CONNECT_ARGS : Client supports connection attributes
CONNECT_WITH_DB : One can specify db on connect
DEPRECATE_EOF : Client no longer needs EOF packet
FOUND_ROWS : Found instead of affected rows
IGNORE_SIGPIPE : IGNORE sigpipes
IGNORE_SPACE : Ignore spaces before ''
INTERACTIVE : This is an interactive client
LOCAL_FILES : Can use LOAD DATA LOCAL
LONG_FLAG : Get all column flags
LONG_PASSWD : New more secure passwords
MULTI_RESULTS : Enable/disable multi-results
MULTI_STATEMENTS : Enable/disable multi-stmt support
NO_SCHEMA : Don't allow database.table.column
ODBC : ODBC client
PLUGIN_AUTH : Client supports plugin authentication
PLUGIN_AUTH_LENENC_CLIENT_DATA : Enable authentication response packet to be larger than 255 bytes
PROTOCOL_41 : New 4.1 protocol
PS_MULTI_RESULTS : Multi-results in PS-protocol
REMEMBER_OPTIONS :
RESERVED : Old flag for 4.1 protocol
SECURE_CONNECTION : New 4.1 authentication
SESION_TRACK : Capable of handling server state change information
SSL : Switch to SSL after handshake
SSL_VERIFY_SERVER_CERT :
TRANSACTIONS : Client knows about transactions
Listing 2-5List of Client Flags

为了配置client_flags，指定应启用或禁用的标志列表。要启用标志，只需指定标志的名称；要禁用该标志，请在前面加上一个减号。清单 2-6 展示了一个例子，告诉这个连接它是一个交互连接，但是它不能处理过期的密码。

import mysql.connector
from mysql.connector.constants import ClientFlag

connect_args = {
  "host": "127.0.0.1",
  "port": 3306,
  "user": "pyuser",
  "password": "Py@pp4Demo",
  "client_flags": [
    ClientFlag.INTERACTIVE,
    -ClientFlag.CAN_HANDLE_EXPIRED_PASSWORDS
  ]
};

db = mysql.connector.connect(
  **connect_args
)

print(__file__ + " - Client flags:")
print(
  "MySQL connection ID for db: {0}"
  .format(db.connection_id)
)

db.close()

Listing 2-6Using Client Flags in the Connection

这将给出以下输出(除了 ID 的值之外):

listing_2_6.py - Client flags:
MySQL connection ID for db: 60

use_pure选项可以用来指定是使用 C 扩展还是连接器的纯 Python 实现。C 扩展提供了比纯实现更好的性能，尤其是在处理大型结果集和准备好的语句时。另一方面，纯 Python 实现在更多的平台上得到支持，具有更多的特性，并且更容易修改源代码。在 8.0.11 和更高版本中，安装 C 扩展是默认的，而早期版本默认使用纯 Python 实现。

也可以通过导入_mysql_connector模块而不是通常的mysql.connector模块来使用 C 扩展。第四章中包含了一个使用 C 扩展的例子。

其他连接选项将不再详细讨论。相反，焦点将转移到字符集选项上。

字符集

字符集定义了字符的编码方式。在互联网的早期，经常使用 ASCII 字符集。ASCII 对每个字符使用 7 位，这样可以节省空间，但这意味着只有 128 个不同的字符可用。这对于英文的纯文本相当有效，但是对于其他语言却缺少字符。多年来，各种其他字符集被使用，如拉丁字符集。

特定于地区的字符集有助于支持所有语言，但缺点是不同的语言需要不同的编码。对此的一种回应是 Unicode 转换格式(UTF)编码；特别是 UTF-8 已经变得流行起来。UTF-8 使用可变数量的字节来存储字符。最初的 128 个 ASCII 字符在 UTF-8 中具有相同的编码；其他字符使用两到四个字节。

在 MySQL Server 5.7 之前，服务器端的默认字符集是 Latin1，但在 MySQL 8.0 中，当utf8mb4成为默认字符集时，这种情况发生了变化。mb4后缀表示每个字符最多使用四个字节(mb =多字节)。这是必需的，因为 MySQL 中的utf8以前意味着每个字符最多支持三个字节。然而，三字节的 UTF-8 实现错过了几个表情符号，它已经被否决，所以最好使用四字节的变体。在 8.0.12 版本之前，Connector/Python 的默认字符集是utf8，这是 UTF-8 的三字节实现(在 MySQL 服务器中称为utf8或utf8mb3)。从 8.0.12 版本开始，默认设置为utf8mb4，就像 MySQL 服务器一样。

还有整理的概念要考虑。归类定义了如何对两个字符或字符序列进行相互比较，例如在比较中是否应该将“”和“”视为同一字符，以及是否应该将 ss 视为等于“”(德语尖音 s)。归类还定义了字符的排序顺序以及比较是否区分大小写。每个字符集都有一个默认的排序规则，但是也可以显式地请求排序规则。

小费

除非您有特定的国家要求，否则当选择utf8或utf8mb4作为字符集时，MySQL Server 中的默认排序通常是一个不错的选择。

通常，MySQL 中可用的字符集和排序规则在不同版本之间变化不大。然而，MySQL Server 8.0 的主要变化之一是增加了一系列 UCA 9.0.0 排序规则。关于可用字符集及其默认排序规则的信息可以使用信息模式中的CHARACTER_SETS表找到，如清单 2-7 所示。

mysql> SELECT CHARACTER_SET_NAME AS Name,
              DEFAULT_COLLATE_NAME
         FROM information_schema.CHARACTER_SETS
        ORDER BY CHARACTER_SET_NAME;
+----------+----------------------+
| Name     | DEFAULT_COLLATE_NAME |
+----------+----------------------+
| armscii8 | armscii8_general_ci  |
| ascii    | ascii_general_ci     |
| big5     | big5_chinese_ci      |
| binary   | binary               |
...
| ujis     | ujis_japanese_ci     |
| utf16    | utf16_general_ci     |
| utf16le  | utf16le_general_ci   |
| utf32    | utf32_general_ci     |
| utf8     | utf8_general_ci      |
| utf8mb4  | utf8mb4_0900_ai_ci   |
+----------+----------------------+
41 rows in set (0.00 sec)
Listing 2-7Character Set Collations in MySQL 8.0.11

类似地，特定字符集可用的排序规则可以使用COLLATIONS表来确定。清单 2-8 显示了utf8mb4字符集的输出。

mysql> SELECT COLLATION_NAME, IS_DEFAULT
         FROM information_schema.COLLATIONS
        WHERE CHARACTER_SET_NAME = 'utf8mb4';
+----------------------------+------------+
| COLLATION_NAME             | IS_DEFAULT |
+----------------------------+------------+
| utf8mb4_general_ci         |            |
| utf8mb4_bin                |            |
| utf8mb4_unicode_ci         |            |
...
| utf8mb4_0900_ai_ci         | Yes        |
| utf8mb4_de_pb_0900_ai_ci   |            |
| utf8mb4_is_0900_ai_ci      |            |
| utf8mb4_lv_0900_ai_ci      |            |
...
| utf8mb4_vi_0900_as_cs      |            |
| utf8mb4_ja_0900_as_cs      |            |
| utf8mb4_ja_0900_as_cs_ks   |            |
| utf8mb4_0900_as_ci         |            |
| utf8mb4_ru_0900_ai_ci      |            |
| utf8mb4_ru_0900_as_cs      |            |
+----------------------------+------------+
73 rows in set (0.00 sec)
Listing 2-8The Collations Available for the utf8mb4 Character Set

输出显示了 MySQL Server 8.0.11 中可用于utf8mb4的 73 种排序规则。归类名称由几部分组成:

字符集名称
该校对适用于哪个国家(例如ja适用于日本),或者它是否具有更一般的性质
修饰符(重音符号):并非所有的排序规则都有这些修饰符。例如，ai不区分重音，as区分重音，ci不区分大小写，cs区分大小写。

小费

MySQL 中字符集和排序规则的主题很大。更深入的讨论见 https://dev.mysql.com/doc/refman/en/charset.html 及其中的参考文献。

MySQL Connector/Python 有三个与字符集和排序规则相关的选项。这些总结在表 2-3 中。

表 2-3

字符集相关选项

名字

缺省值

描述

| | --- | --- | --- | | charset | utf8mb4 | 用于连接的字符集。在 MySQL Connector/Python 8.0.11 及更早版本中，默认为utf8。大多数情况下，建议使用utf8mb4。 | | collation | utf8mb4_general_ci | 用于字符串比较和排序的排序规则。在许多情况下，可以使用默认值。MySQL Connector/Python 8.0.11 及更早版本的默认值为utf8_general_ci。在 MySQL Server 8.0 中，utf8mb4字符集的默认排序规则是utf8mb4_0900_ai_ci，这通常是一个不错的选择，除非存在特定的需求。 | | use_unicode | True | 是否将查询结果中的字符串作为 Python Unicode 文本返回。默认值是True，这通常也是最好的值。 |

清单 2-9 展示了一个配置字符集相关选项的例子。

import mysql.connector

connect_args = {
  "host": "127.0.0.1",
  "port": 3306,
  "user": "pyuser",
  "password": "Py@pp4Demo",
  "charset": "utf8mb4",
  "collation": "utf8mb4_unicode_ci",
  "use_unicode": True
};

db = mysql.connector.connect(
  **connect_args)

print(__file__ + " - Setting character set:")
print(
  "MySQL connection ID for db: {0}"
  .format(db.connection_id)
)

db.close()

Listing 2-9Specifying the Character Set and Collation

可用的字符集和排序规则被编码到 MySQL 连接器/Python 源代码中。这意味着当您升级 MySQL Server 时，如果包含新的字符集或排序规则，您只能在 Python 程序中使用它们，前提是您将 MySQL Connector/Python 更新到支持新字符集和排序规则的版本。

小费

如果您升级 MySQL Server，您可能还需要升级 MySQL Connector/Python 以获得对所有新功能的支持。

在首次连接到 MySQL 服务器后，可以更改连接所使用的字符集和排序规则。最好的方法是使用清单 2-10 中展示的set_charset_collation()方法来改变连接的charset和collation属性。注意，与其他示例不同，这个示例首先实例化了MySQLConnection类，以便能够在创建连接之前打印初始字符集和排序规则。

import mysql.connector

db = mysql.connector.MySQLConnection()

# Print banner and initial settings
print(
  "{0:<9s}   {1:<7s}   {2:<18s}".format(
    "Stage", "charset", "collation"
  )
)
print("-" * 40)
print(
  "{0:<9s}   {1:<7s}   {2:<18s}".format(
    "Initial", db.charset, db.collation
  )
)

# Create the connection
connect_args = {
  "host": "127.0.0.1",
  "port": 3306,
  "user": "pyuser",
  "password": "Py@pp4Demo"
};

db.connect(**connect_args)

# The connection does not change the
# settings
print(
  "{0:<9s}   {1:<7s}   {2:<18s}".format(
    "Connected",
    db.charset, db.collation
  )
)

# Change only the character set

db.set_charset_collation(

  charset = "utf8mb4"

)

print(
  "{0:<9s}   {1:<7s}   {2:<18s}".format(
    "Charset", db.charset, db.collation
  )
)

# Change only the collation

db.set_charset_collation(

  collation = "utf8mb4_unicode_ci"

)

print(
  "{0:<9s}   {1:<7s}   {2:<18s}".format(
    "Collation",
    db.charset, db.collation
  )
)

# Change both the character set and
# collation

db.set_charset_collation(

  charset   = "latin1",
  collation = "latin1_general_ci"

)

print(
  "{0:<9s}   {1:<7s}   {2:<18s}".format(
    "Both", db.charset, db.collation
  )
)

db.close()

Listing 2-10Changing the Character Set of a Connection

从示例中可以看出，字符集和排序规则属性甚至可以在连接建立之前使用。然而，在连接建立之前，不可能使用set_charset_collation()方法来改变字符集或排序规则。

注意

始终使用set_charset_collation()方法来更改连接的字符集和/或排序规则。与直接将SET NAMES作为 SQL 语句执行相比，它确保了 Connector/Python 知道哪些设置用于将 bytearrays 转换为 Python 字符串(参见下一章)，字符集和排序规则选择根据 Connector/Python 已知的进行验证，并且 C 扩展设置保持同步。

建立连接不会改变charset和collation属性的值。字符集可以自行更改，在这种情况下，归类设置为字符集的默认值。在这种情况下，字符集设置为utf8mb4，因此默认字符集为utf8mb4_general_ci。

也可以单独设置归类，最后字符集和归类都设置好了。使用版本 8.0.11 执行清单 2-10 中的程序的输出是

Stage       charset   collation
----------------------------------------
Initial     utf8      utf8_general_ci
Connected   utf8      utf8_general_ci
Charset     utf8mb4   utf8mb4_general_ci
Collation   utf8mb4   utf8mb4_unicode_ci
Both        latin1    latin1_general_ci

如果您使用的是 MySQL Connector/Python 8.0.12 或更高版本，初始和连接的字符集和排序规则是utf8mb4和utf8mb4_general_ci。

查询行为

有几个选项可以控制查询的行为。从定义事务配置是否允许特性到定义 MySQL Connector/Python 如何处理结果。选项在表 2-4 中列出。

表 2-4

与查询相关的选项

名字

缺省值

描述

| | --- | --- | --- | | allow_local_infile | True | 是否允许使用LOAD DATA LOCAL INFILE语句。 | | autocommit | False | 当True时，在每个查询后执行一个隐式的COMMIT。 | | buffered | False | 当True时，结果集被立即提取并缓存在应用中。 | | consume_results | False | 当True时，如果有未提取的行，则自动提取查询结果，并执行新的查询。 | | database | | 在没有为表显式给出数据库名称的情况下，哪个数据库(模式)用作查询的默认数据库。 | | raw | False | 默认情况下，使用游标时，结果值会转换为 Python 类型。当将该选项设置为True时，返回的结果没有转换。 | | sql_mode | (服务器默认值) | 执行查询时使用的 SQL 模式。参见https://dev.mysql.com/doc/refman/en/sql-mode.html。 | | time_zone | | 设置后，时间戳将转换为该时区，而不是使用服务器端时区。 |

第三章和第四章讨论了这些选项，包括使用这些选项的代码示例。

警告信息

以正确的方式处理警告和错误是非常重要的。否则会导致数据损坏或丢失。使用游标时，有两个选项控制 MySQL Connector/Python 如何处理警告(游标将在下一章讨论)。选项如表 2-5 所示。

表 2-5

游标的警告相关选项

名字

缺省值

描述

| | --- | --- | --- | | get_warnings | False | 当设置为True时，每次查询后自动提取警告。 | | raise_on_warnings | False | 当设置为True时，警告会引发异常。 |

由于正确处理警告和错误非常重要，第九章专门讨论这个主题。

摘要

本章讲述了如何创建和配置从 Python 程序到 MySQL 服务器数据库的连接。讨论了以下主题:

建立连接的四种不同方式，包括初始配置。mysql.connector. connect()功能是四种方法中最灵活的。
配置选项。
连接的最佳实践:关闭连接，使用 SSL/TLS 加密流量，不要在源代码中硬编码连接选项(尤其是密码)。
MySQL 配置文件。
字符集。

能够创建到数据库的连接当然很好，但是除非您能够执行查询，否则它没有多大用处。接下来的两章将讨论查询执行，从更基本的用例开始。

Footnotes 1

关于设置环境变量的 Docker 文档见 https://docs.docker.com/engine/reference/commandline/run/#set-environment-variables--e---env---env-file 。

三、基本查询执行

前一章讨论了如何从 Python 程序连接到 MySQL。然而，仅仅为了获得一个连接 ID 或什么都不做而创建一个连接没有多大意义。毕竟，MySQL Connector/Python 的全部意义在于执行查询。本章将着眼于查询执行的基础。

首先，您将学习如何使用连接对象的cmd_query()方法执行查询。然后，您将探索更高级的游标概念。最后，您将看到如何处理用户输入。

小费

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

简单执行

通过 MySQL Connector/Python 执行查询有几种不同的方法。最简单但也是最不强大的是连接对象的cmd_query()方法。我还将讨论获取SELECT语句结果的get_rows()和get_row()方法。

在深入研究查询和获取结果的三种方法之前，考虑一下它们之间的关系是很有用的，所以请看一下图 3-1 。

img/463459_1_En_3_Fig1_HTML.jpg

图 3-1

通过连接对象执行查询的流程

图 3-1 显示了一旦创建了连接，就可以使用cmd_query()方法执行查询。如果有结果(返回行)，可以使用get_rows()或get_row()方法来读取行。该连接可以在更多的查询中重用。最后，当不再有查询时，使用close()方法关闭连接。与真实世界的程序相比，这有点简化；比如没有关于交易的考虑。但是，它是一个有用的高层次概述。

本节的主题是cmd_query()、get_rows()和get_row()方法，以及如何处理结果。对于更一般的用法，有必要使用光标；这将是下一节以及下一章的主题。

注意

在大多数情况下，最好按照下一节所述使用游标。然而，这一节很重要，因为它解释了游标是如何工作的。

执行查询:cmd_query()

cmd_query()方法很简单。它接受一个参数，即要执行的查询，并返回一个字典，其中包含有关已执行查询的信息。返回的字典的确切内容取决于查询。例如，对于一个SELECT查询，字典将包含关于所选列的信息。对于所有查询，还包括查询的状态。本节中的示例将包括结果字典的内容。

清单 3-1 显示了一个使用cmd_query()执行返回单行的SELECT查询的简单示例。

import mysql.connector
import pprint

printer = pprint.PrettyPrinter(indent=1)

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini")

# Execute a query

result = db.cmd_query(

  """SELECT *
       FROM world.city
      WHERE ID = 130"""

)

# Print the result dictionary
print("Result Dictionary\n" + "="*17)
printer.pprint(result)

db.close()

Listing 3-1Executing a Simple SELECT Using cmd_query()

警告

这个例子(以及本章中的其他几个例子)有一个查询参数(在这个例子中，ID的值用130)。这是可以接受的，因为它是一个固定的查询。但是，不要将用户提交的数据内联到查询中。本章后面的“处理用户输入”部分将向您展示如何安全地处理用户提交的值。

这个程序创建了一个连接，正如你在第二章中看到的。建立连接后，使用cmd_query()方法执行查询，返回的字典存储在result变量中，使用 pretty 打印模块(pprint)打印出来:

Result Dictionary
=================
{'columns': [('ID', 3, None, None, None, None, 0, 49667),
             ('Name', 254, None, None, None, None, 0, 1),
             ('CountryCode', 254, None, None, None, None, 0, 16393),
             ('District', 254, None, None, None, None, 0, 1),
             ('Population', 3, None, None, None, None, 0, 32769)],
 'eof': {'status_flag': 16385, 'warning_count': 0}}

结果字典的列部分将在下一章详细讨论；现在，只需要知道列的元组的第一个元素是列名。结果字典的第二部分是eof元素，包括查询的一些细节；包含的字段取决于查询。您获得的列元组中最后一个整数的值和status_flag的值可能与示例输出不同，因为它们取决于是否使用了 C 扩展。

在eof元素中常见的字段是status_flag和warning_count字段。状态标志远没有听起来那么有用；其实价值是没有记载的，不应该从它的价值中取任何意义。另一方面，警告计数显示查询期间发生的警告数量。第九章讲述了如何检查警告。

对于没有结果集的查询(即不返回行)，eof信息是一个“OK 包”，其中包含关于查询的信息。例如，以下信息是使用纯 Python 实现更新 14 行的UPDATE语句的结果:

Result Dictionary
=================
{'affected_rows': 14,
 'field_count': 0,
 'info_msg': 'Rows matched: 14  Changed: 14  Warnings: 0',
 'insert_id': 0,
 'status_flag': 1,
 'warning_count': 0}

两个最重要的参数是

affected_rows:显示受影响的行数。在本例中，更新了 14 行。
insert_ id:对于INSERT和REPLACE语句，将数据插入到带有自动递增列的表中，insert_id是该语句插入的第一行的 ID。

当use_pure = False时，info_msg参数不存在，status_flag被替换为server_status。

与cmd_query()类似的是cmd_query_iter()方法，它可以用来向 MySQL 发送多个查询。在一个调用中执行多个查询和处理多个结果集是下一章的主题。

像刚才讨论的例子那样执行查询当然很好，但是如果不检索结果，像清单 3-1 中的SELECT语句这样的查询就没什么意思了。为了获取找到的行，使用了get_rows()和get_row()方法。

正在检索行–get _ Rows()

有些查询，如CREATE TABLE、ALTER TABLE、INSERT、UPDATE和DELETE语句，不返回任何结果，只需检查查询是否成功。然而，一般来说，程序中的大多数查询都是返回结果集的SELECT查询。对于返回结果集的查询，必须提取行。当使用cmd_query()执行查询时，相应的获取行的方法是get_rows()，它返回查询找到的所有行。

get_rows()的用法很简单。所需要做的就是调用它，行作为元组列表返回，如清单 3-2 所示。

import mysql.connector
import pprint

printer = pprint.PrettyPrinter(indent=1)

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini", use_pure=True)

# Execute a query
result = db.cmd_query(
  """SELECT Name, CountryCode,
            Population
       FROM world.city
      WHERE Population > 9000000
      ORDER BY Population DESC"""
)

# Fetch the rows

result_set = db.get_rows()

# Print the result dictionary
print("Result Dictionary\n" + "="*17)
printer.pprint(result)

# Print the rows
print("\nResult Set\n" + "="*10)
printer.pprint(result_set)

db.close()

Listing 3-2Fetching Rows with get_rows()

清单 3-2 中的程序类似于清单 3-1 中的程序，除了在这种情况下，它通过使用use_pure = True强制使用纯 Python 实现。这次的查询查找人口超过 900 万的城市，并要求结果行按人口降序排列。输出看起来如清单 3-3 所示。输出是用 MySQL 连接器/Python 版本 8.0.11 生成的。

注意

MySQL 连接器 8.0.12 中的一个重要变化是在纯 Python 和 C 扩展实现之间调整了get_rows()和get_row()的行为。这意味着在 MySQL Connector/Python 8.0.12 和更高版本中，get_rows()和get_row()的纯 Python 实现不再以字节数组的形式返回结果。下面的讨论仍然有助于说明结果。

Result Dictionary
=================
{'columns': [('Name', 254, None, None, None, None, 0, 1),
             ('CountryCode', 254, None, None, None, None, 0, 16393),
             ('Population', 3, None, None, None, None, 0, 1)],
 'eof': {'status_flag': 33, 'warning_count': 0}}

Result Set
==========
([(bytearray(b'Mumbai (Bombay)'), bytearray(b'IND'), bytearray(b'10500000')),
  (bytearray(b'Seoul'), bytearray(b'KOR'), bytearray(b'9981619')),
  (bytearray(b'S\xc3\xa3o Paulo'), bytearray(b'BRA'), bytearray(b'9968485')),
  (bytearray(b'Shanghai'), bytearray(b'CHN'), bytearray(b'9696300')),
  (bytearray(b'Jakarta'), bytearray(b'IDN'), bytearray(b'9604900')),
  (bytearray(b'Karachi'), bytearray(b'PAK'), bytearray(b'9269265'))],
 {'status_flag': 33, 'warning_count': 0})

Listing 3-3The Output of Executing the Program in Listing 3-2

结果字典类似于前面的例子，有列信息和eof信息。更有趣的是get_rows()返回的结果集。这些值以二进制数据数组表示的字符串形式返回(bytearray)。虽然这在技术上是结果的正确表示，但它并不十分有用。例如，人口是一个整数，所以数据最好是整数而不是字符串。另一个问题是像圣保罗这样的城市，其字节序列是“S \ xc3 \ xa3o Paulo”；请注意，γ表示为\xc3\xa3。

注意

如果使用 C 扩展或 8.0.12 及更高版本，则这些值不会以字节数组的形式返回，而是以 Unicode 字符串的形式返回。这是一个在早期版本中两个实现不相同的例子。

为了让数据在程序中真正有用，有必要将字节数组转换为原生 Python 数据类型。具体的转换方式取决于数据，为每种数据类型实现显式转换超出了本书的范围。然而，它也不是必需的，因为 MySQL Connector/Python 已经包含了它的代码；稍后会有更多的介绍。现在，请看清单 3-4 中转换清单 3-2 结果中的字符串和整数的例子。

Note

这个示例和后面的输出包含非 ASCII 字符的示例展示了 Python 2 和 Python 3 在 Unicode 处理上的差异。示例假设 Python 3 和 MySQL Connector/Python 8.0.11。这些示例在 8.0.12 版和更高版本中不起作用。

import mysql.connector

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini", use_pure=True)

# Execute a query
result = db.cmd_query(
  """SELECT Name, CountryCode,
            Population
       FROM world.city
      WHERE Population > 9000000
      ORDER BY Population DESC"""
)

# Fetch the rows
(cities, eof) = db.get_rows()

# Print the rows found
print(__file__ + " – Using decode:")
print("")
print(
  "{0:15s}   {1:7s}   {2:3s}".format(
    "City", "Country", "Pop"
  )
)
for city in cities:
  print(
    "{0:15s}   {1:⁷s}   {2:4.1f}".format(
      city[0].decode(db.python_charset),
      city[1].decode(db.python_charset),
      int(
        city[2].decode(db.python_charset)
      )/1000000.0
    )
  )

# Print the eof package
print("\nEnd-of-file:");
for key in eof:
  print("{0:15s} = {1:2d}".format(
    key, eof[key]
  ))

db.close()

Listing 3-4Converting the Result to Native Python Types

清单 3-2 和清单 3-4 的主要区别在于对结果集的处理。首先，结果集被分成返回的行(城市)和文件尾(eof)包。然后在将值转换为本地 Python 类型的同时打印城市。

使用bytearray类型的decode()方法转换字符串值。这需要解析连接的字符集。在这种情况下，字符集是utf8(使用默认)；但是，为了确保可以处理任何字符集，连接的python_ charset属性用于设置转换中使用的字符集。由于utf8mb4是 MySQL 的发明，所以有必要抓住这一点，用utf8来代替；这就是charset和python_charset属性的区别。可以使用int()函数转换人口，然后除以一百万，得到百万人口。

最后，打印结果集的文件结尾部分。这与由cmd_query()返回的结果的eof部分中可用的信息相同。该程序的输出是

listing_3_4.py – Using decode

City              Country   Pop
Mumbai (Bombay)     IND     10.5
Seoul               KOR     10.0
São Paulo           BRA     10.0
Shanghai            CHN      9.7
Jakarta             IDN      9.6
Karachi             PAK      9.3

End-of-file:
status_flag     = 33
warning_count   =  0

手动转换字节数组在一般情况下是不可行的，也是不必要的，这将在下面讨论行的自动转换时显示。

自动转换成原生 Python 类型

在前面的示例中，查询返回的行是手动处理的。这可能是理解正在发生的事情的一个很好的方法，但是在更真实的情况下，通常更倾向于将结果作为原生 Python 类型返回。

注意

和前面的例子一样，只有 MySQL Connector/Python 8.0.11 和更早的版本(包括 2.1 版)才需要这个讨论。在更高版本中，转换会自动发生；然而，调用row_to_python()是安全的，因为如果转换已经发生，它将只是一个空操作。

MySQL Connector/Python 包括转换模块，该模块提供了对 MySQL 服务器返回的结果进行转换的工具。具体来说，MySQLConverter类中的row_to_python()方法可以转换一行中的所有值。清单 3-5 展示了清单 3-4 中示例的等效物，但是这次使用row_to_python()来处理转换。

import mysql.connector

from mysql.connector.conversion import MySQLConverter

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini", use_pure=True)

# Execute a query
result = db.cmd_query(
  """SELECT Name, CountryCode,
            Population
       FROM world.city
      WHERE Population > 9000000
      ORDER BY Population DESC"""
)

# Fetch the rows
(cities, eof) = db.get_rows()

# Initialize the converter

converter = MySQLConverter(

  db.charset, True)

# Print the rows found
print(__file__ + " - Using MySQLConverter:")
print("")
print(
  "{0:15s}   {1:7s}   {2:3s}".format(
    "City", "Country", "Pop"
  )
)
for city in cities:
  values = converter.row_to_python(
    city, result["columns"])
  print(
    "{0:15s}   {1:⁷s}   {2:4.1f}".format(
      values[0],
      values[1],
      values[2]/1000000.0
    )
  )

db.close()

Listing 3-5Converting Query Results Using MySQLConverter.row_to_python()

清单 3-5 中例子的重要部分是那些涉及MySQLConverter类的部分。第一，导入类；然后，当结果集准备好打印时，实例化该类；最后，使用row_to_python()方法转换行。

当实例化MySQLConverter类时，需要两个参数:字符集和 Python 中是否使用 Unicode。请记住第二章中的内容，在创建连接时可以配置两者。字符集是通过连接的charset属性公开的，因此，和前面一样，它用于确保在转换行时，连接字符集的改变不需要代码的改变。MySQLConverter类知道如何处理utf8mb4，所以没有必要明确地处理它。Python 中没有使用 Unicode 的属性，因此有必要显式指定它。

有了MySQLConverter类的实例，就可以一次转换一行。来自cmd_query()调用结果的列信息作为一个参数沿着要转换的值传递；这确保了 MySQL 连接器/Python 知道每一列的数据类型。输出与清单 3-4 中的示例相同，除了eof部分的信息已被删除:

listing_3_5.py - Using MySQLConverter

City              Country   Pop
Mumbai (Bombay)     IND     10.5
Seoul               KOR     10.0
São Paulo           BRA     10.0
Shanghai            CHN      9.7
Jakarta             IDN      9.6
Karachi             PAK      9.3

到目前为止，示例已经获取了结果集中的所有行，然后使用这些行。这对于小结果来说很好，但是对于大量具有大值的行来说效率不高。

正在检索行–get _ Rows()有限制

限制检索的行数的一个选项是指定要提取的行数作为get_rows()的参数。这可以通过两种方式之一来实现:要么直接给出行数作为参数，要么显式地作为count参数。指定的行数是批中要读取的最大行数。当有更多行要被读取时，eof将被设置为None。如果可用的行数少于请求的行数，get_rows()将返回剩余的行数，并设置eof包含文件结束信息。清单 3-6 对此进行了说明。

import mysql.connector
from mysql.connector.conversion import MySQLConverter

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini", use_pure=True)

# Execute a query
result = db.cmd_query(
  """SELECT Name, CountryCode,
            Population
       FROM world.city
      WHERE Population > 9000000
      ORDER BY Population DESC"""
)

# Initialize the converter
converter = MySQLConverter(
  db.charset, True)

# Fetch and print the rows
print(__file__
      + " - Using get_rows with limit:")
print("")
count = 0

(cities, eof) = db.get_rows(4)

while (cities):
  count = count + 1
  print("count = {0}".format(count))

  # Print the rows found in this batch
  print(
    "{0:15s}   {1:7s}   {2:3s}".format(
      "City", "Country", "Pop"
    )
  )
  for city in cities:
    values = converter.row_to_python(
      city, result["columns"])
    print(
      "{0:15s}   {1:⁷s}   {2:4.1f}".format(
        values[0],
        values[1],
        values[2]/1000000.0
      )
    )
  print("")

  # Read the next batch of rows
  if (eof == None):
    (cities, eof) = db.get_rows(count=4)
  else:
    cities = []

db.close()

Listing 3-6Fetching a Limited Number of Rows at a Time Using get_rows()

提取前四行时，行数被指定为单独的参数:

(cities, eof) = db.get_rows(4)

其余的行是在循环中读取的:

  if (eof == None):
    (cities, eof) = db.get_rows(count=4)
  else:
    cities = []

有必要检查结果集的eof部分的值，因为先前的读取可能已经获取了最后的行。事实上，这里就是这样。第一次循环打印结果的前四行，第二次循环打印剩下的两行:

listing_3_6.py - Using get_rows with limit

count = 1
City              Country   Pop
Mumbai (Bombay)     IND     10.5
Seoul               KOR     10.0
São Paulo           BRA     10.0
Shanghai            CHN      9.7

count = 2
City              Country   Pop
Jakarta             IDN      9.6
Karachi             PAK      9.3

这种用法需要注意的一点是，get_rows()总共读取七个“行”:六行是查询的结果加上eof信息。

一次读取有限数量的行的特殊情况是获取一行。对于这种情况，read_row()方法可以作为用count=1包装对get_rows()的调用的包装器。

正在检索行–get _ row()

使用cmd_query()方法执行查询后，有两种不同的获取行的策略。可以使用get_rows()一次提取几行，正如到目前为止所展示的那样，也可以使用get_row()方法一次提取一行。

一次只获取一行的好处是应用一次只在内存中存储一行。对于大型结果集来说，这可能更有效，尽管它需要更多的对get_row()方法的调用和更多的从 MySQL 服务器读取数据的往返行程。

注意

这个有点简化了。正如您将在下一章中看到的，游标支持缓冲结果(即预取结果集)。然而，当直接使用cmd_query()方法时，这是不受支持的。

get_row()的另一个潜在优势是它提供了不同的代码流。使用get_rows()，首先获取行，然后代码遍历这些行。另一方面，当一次获取一行时，可以直接在循环中使用get_row()，如清单 3-7 所示。哪种代码流更好取决于程序的情况和一般风格。

import mysql.connector
from mysql.connector.conversion import MySQLConverter

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini", use_pure=True)

# Execute a query
result = db.cmd_query(
  """SELECT Name, CountryCode,
            Population
       FROM world.city
      WHERE Population > 9000000
      ORDER BY Population DESC"""
)

# Print the rows found
print(__file__ + " - Using get_row:")
print("")
converter = MySQLConverter(
  db.charset, True)
print(
  "{0:15s}   {1:7s}   {2:3s}".format(
    "City", "Country", "Pop"
  )
)

(city, eof) = db.get_row()

while (not eof):
  values = converter.row_to_python(
    city, result["columns"])
  print(
    "{0:15s}   {1:⁷s}   {2:4.1f}".format(
      values[0],
      values[1],
      values[2]/1000000
    )
  )
  (city, eof) = db.get_row()

db.close()

Listing 3-7Using get_row() to Read the Rows One by One

清单 3-7 中的大部分代码与前面的例子相同。区别在于打印结果集的循环是如何完成的。这里每个城市的值和文件结束信息是使用get_row()方法获得的。当有更多行要读取时,eof变量是None。然后使用 while 循环继续获取行，直到eof被设置为与get_rows()相同的值。输出是

listing_3_7.py - Using get_row

City              Country   Pop
Mumbai (Bombay)     IND     10.5
Seoul               KOR     10.0
São Paulo           BRA     10.0
Shanghai            CHN      9.7
Jakarta             IDN      9.6
Karachi             PAK      9.3

在转向游标之前，有必要考虑一下 MySQL Connector/Python 中使用结果的一般性质。

消费结果

到目前为止，示例只是使用了get_rows()或get_row()来获取由SELECT语句返回的行。这在测试时很好，但是值得更深入地研究消费结果。

每当查询返回结果集时，必须先消耗这些行，然后才能执行另一个查询。如果这些行尚未被消耗，将会出现异常:

mysql.connector.errors.InternalError: Unread result found

有两种方法可以避免这种错误:

用get_rows()或get_row()读取行。所有的行和eof包都必须被阅读。
创建连接时启用can_consume连接属性。

警告

总是确保使用方法get_rows()或get_row()之一或者在创建连接时启用can_consume来使用查询返回的所有行。

您可以通过使用连接的can_consume_results属性来检查can_consume选项是否已启用。当can_consume被启用时，如果一个新的查询即将被执行并且仍然有未读的行，MySQL 连接器/Python 将在内部调用get_rows()。

程序如何知道是否还有未读的行？连接类通过unread_result属性对此进行跟踪。当读取结果集的最后一行时，unread_result被设置为False。该属性是公共可访问的，因此可以与get_rows()一起使用。

can_consume_results属性只是连接对象的许多属性之一。在前一章我讨论如何创建连接时，提到了几个属性。现在，随着对连接和查询执行如何工作有了更好的理解，您可以继续讨论游标了。

小费

如果有大量数据要使用，并且不需要这些数据，那么关闭连接并重新连接会比获取行更快。

光标

到目前为止，本章中的所有示例都专门使用了 connection 对象的方法和属性来执行查询并获取结果行。直接使用连接可以认为是低级方法。对于实际的程序，更常见的是选择高级游标，它提供了一种更好的处理查询的方式。

注意

虽然连接方法cmd_query()、get_rows()和get_row()很少被直接使用，但了解这些方法的工作原理仍然很有用。它有助于解释为什么游标以这样的方式工作，并且在调试问题时很有用。

在可以使用游标执行查询之前，必须对其进行实例化。这是使用游标之旅的第一个主题。

实例化

有两种方法可以实例化一个游标:要么使用 connection 对象的cursor()方法，要么直接使用MySQLCursor构造函数。下面的代码片段说明了这两种方法:

import mysql.connector
from mysql.connector.cursor import MySQLCursor

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini", use_pure=True)

# Create a cursor using cursor()
cursor1 = db.cursor()
cursor1.close()

# Create a cursor using the constructor
cursor2 = MySQLCursor(db)
cursor2.close()

db.close()

注意

这个例子并不是故事的全部。有几个光标子类，由db.cursor()返回的那个依赖于光标设置。稍后会详细介绍。

与数据库连接本身一样，游标是使用 cursor 对象的close()方法关闭的。使用完游标后关闭它可以确保删除对连接对象的引用，从而避免内存泄漏。

有几种不同的游标类。使用哪一个取决于需求。这些类别是

MySQLCursor:这是一个“普通”游标类，用于将无缓冲输出转换为 Python 类型。这是默认的游标类。
MySQLCursorBuffered:这使用缓冲的结果集(参见第四章)，但是仍然将结果集转换为 Python 类型。
MySQLCursorRaw:这将原始结果作为字节数组返回，类似于 8.0.11 和更早版本中的get_rows()，但不使用缓冲。
MySQLCursorBufferedRaw:返回原始结果集并启用缓冲。
MySQLCursorDict:与MySQLCursor相同，但行作为字典返回。
MySQLCursorBufferedDict:与MySQLCursorBuffered相同，但行作为字典返回。
MySQLCursorNamedTuple:与MySQLCursor相同，但行以命名元组的形式返回。
MySQLCursorBufferedNamedTuple:与MySQLCursorBuffered相同，但行以命名元组的形式返回。
MySQLCursorPrepared:与预准备语句一起使用。准备好的语句将在本章末尾讨论。

使用cursor()方法的一个优点是，您可以为游标提供参数，该方法将使用适当的游标类返回一个游标对象。支持的参数有

buffered:是否缓冲应用中的结果集。默认值来自连接的buffered选项。
raw:是否返回原始结果集，而不是将其转换为 Python 类型。默认值来自连接的raw选项。
prepared:光标是否将使用准备好的语句。“处理用户输入”一节将给出这样的例子。默认为None ( False)。
cursor_class:指定要使用的自定义光标类别。这个自定义类必须是CursorBase类的子类。默认是None。自定义类超出了本书的范围。
dictionary:是否将行作为字典返回。不能与raw和named_tuple组合使用。默认为None ( False)。
named_tuple:是否将行作为命名元组返回。如果raw或dictionary也被启用，则该选项不可用。默认为None ( False)。

表 3-1 总结了支持的选项组合和返回的光标类别。在标题中，dictionary选项被缩写为“dict”，而named_tuple选项被缩写为“tuple”。表格中留空的选项可以是False或None。

表 3-1

游标对象的参数

减轻

生的

准备

词典

元组

班级

| | --- | --- | --- | --- | --- | --- | | | | | | | MySQLCursor | | True | | | | | MySQLCursorBuffered | | | True | | | | MySQLCursorRaw | | True | True | | | | MySQLCursorBufferedRaw | | | | | True | | MySQLCursorDict | | True | | | True | | MySQLCursorBufferedDict | | | | | | True | MySQLCursorNamedTuple | | True | | | | True | MySQLCursorBufferedNamedTuple | | | | True | | | MySQLCursorPrepared |

如果使用了不受支持的选项组合，则会引发ValueError异常，例如:

ValueError: Cursor not available with given criteria: dictionary, named_tuple

本节的其余部分将涵盖游标执行流以及实例化和使用游标的示例，从执行流开始。

mysql 游标

MySQLCursor类的用法类似于直接从连接类执行查询时的用法:执行查询，然后获取行。

执行查询的主要方法是execute()方法，而读取查询返回的行有三种不同的方法。图 3-2 总结了execute()和行提取方法及其关系。此外，还有用于执行查询的executemany()和callproc()方法。它们在第四章与stored_results()一起讨论，后者与callproc()方法一起使用。

img/463459_1_En_3_Fig2_HTML.jpg

图 3-2

使用游标的典型代码流

流程从应用创建连接开始。然后使用cursor()方法创建一个光标。不止有一个游标类；更确切地说，它是一系列依赖于游标的确切性质的类。本章讨论的单个查询是使用execute()方法执行的。

游标类有一个名为with_rows的属性，它指定是否有要处理的结果集。可以使用以下三种方法之一获取行:fetchone()、fetchmany()或fetchall()。一旦获取了所有行，fetch 方法将返回None或一个空结果。可以重用该游标来执行更多的查询。一旦执行完所有查询，游标和连接都将关闭。

与显示如何使用连接方法的流程图一样，这是一个简化的示例。在这一节的最后，将会更清楚光标是如何工作的，下一章将会添加更多的细节。

mysql 游标

对于存储过程以外的单个查询，使用execute()方法；这包括支持在一次调用中执行多个不同的查询。executemany()方法可用于使用不同的参数集执行相同的查询。

execute()方法接受一个必需的参数，即要执行的查询，以及两个可选参数:

operation:要执行的查询。这个参数是强制性的。
params:与查询一起使用的参数的字典、列表或元组。“处理用户输入”一节讨论了参数化查询的使用。默认为None。
multi:当True时，操作被认为是由分号分隔的多个查询，并且execute()返回一个迭代器，使得可以迭代每个查询的结果。第四章包含了这样的例子。默认为False。

可以使用下列方法之一提取查询返回的行:

fetchall():获取所有剩余的行。这类似于没有任何参数的get_rows()方法。fetchall()使用get_rows()进行连接，以便在一次调用中获得无缓冲游标的所有行。
fetchmany():获取一批行，可以设置该批中包含的最大行数。这类似于使用带有参数的get_rows()。fetchmany()使用fetchone()实现。默认是一次读取一行。
fetchone():一次读取一行。这相当于get_row()方法，也用于无缓冲的结果。

可以使用callproc()方法执行存储过程。stored_results()是一个相关的方法，可以在存储过程返回一个或多个结果集时使用。执行多重查询和使用存储过程将在第四章中讨论。

注意

所有行都必须显式获取，或者通过为连接启用consume_results来获取。如果发现未读的行，使用相同的游标执行新的查询，或者关闭游标，除非启用consume_results，否则将引发异常。一次只能有一个光标用于未读结果的连接。

清单 3-8 展示了一个使用光标查找人口超过 900 万的城市的简单示例(在几个cmd_query()示例中使用了相同的查询)。

import mysql.connector

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini")

# Instantiate the cursor

cursor = db.cursor()

# Execute the query
cursor.execute(
  """SELECT Name, CountryCode,
            Population
       FROM world.city
      WHERE Population > 9000000
      ORDER BY Population DESC"""
)

print(__file__
      + " - Using the default cursor:")
print("")

if (cursor.with_rows):

  # Print the rows found
  print(
    "{0:15s}   {1:7s}   {2:3s}".format(
      "City", "Country", "Pop"
    )
  )
  city = cursor.fetchone()
  while (city):
    print(
      "{0:15s}   {1:⁷s}   {2:4.1f}".format(
        city[0],
        city[1],
        city[2]/1000000.0
      )
    )
    city = cursor.fetchone()

cursor.close()

db.close()

Listing 3-8Using a Cursor to Execute a SELECT Statement

关于这个程序首先要注意的是，与使用get_row()相比，打印结果的循环更加紧凑。在清单 3-7 中的例子，本质上是使用cmd_query()和get_row()的相同例子，循环是 13 行代码(包括读取第一行)；在游标示例中，循环是 11 行。这样做的原因是MySQLCursor类自动处理从原始数据到 Python 类型的转换，而不管是使用纯 Python 还是 C 扩展实现，这使得循环遍历行并打印它们变得更加简单。

第二点是fetchone()的使用和循环条件与使用get_rows()的例子相比略有不同。fetchone()的返回值只是该行值的一个元组，而get_rows()还包括eof信息。这意味着当fetchone()返回None时，循环必须终止。

第三点是在获取行之前检查游标的with_rows属性。当查询返回行时,with_rows属性是True。即使提取了所有行，该值也不会改变；这不同于前面检查的 connection 对象的unread_result属性。

除了标题之外，输出与前面的示例相同:

listing_3_8.py - Using the default cursor

City              Country   Pop
Mumbai (Bombay)     IND     10.5
Seoul               KOR     10.0
São Paulo           BRA     10.0
Shanghai            CHN      9.7
Jakarta             IDN      9.6
Karachi             PAK      9.3

既然execute()和获取方法都不包含eof信息，那么如何获得该信息呢？我们来看看。

mysql 游标

使用游标的一个优点是不再需要考虑cmd_query()、get_rows()和get_row()返回的eof信息。相反，相关信息可通过光标的属性获得。

可用的属性有

column_names
description
lastrowid
rowcount
statement
with_rows

所有属性都是只读的，并且包含与最近执行的查询相关的信息。下面几节将简要讨论每个属性。

列名

column_names属性包括每一列的名称，其顺序与它们的值相同。它与由cmd_query()方法返回的结果字典中的列的列表中的第一个元素相同。

例如，如果应该使用列名作为键将行转换为字典，则列名会很有用:

row = cursor.fetchone()
row_dict = dict(
  zip(cursor.column_names, row)
)

小费

如果您想将所有结果转换成字典，那么使用MySQLCursorDict游标类。“字典和命名元组游标子类”一节中提供了一个示例。

描述

description属性相当于cmd_query()结果字典中的整个 columns 元素。属性的值是一个元组列表，如下所示(使用pprint模块打印):

[('Name', 254, None, None, None, None, 0, 1),
 ('CountryCode', 254, None, None, None, None, 0, 16393),
 ('Population', 3, None, None, None, None, 0, 1)]

元组中包含的值的详细信息可以在第四章的“查询元数据”一节中找到。

lastrawid

在将最后一个分配的 ID 插入到具有自动递增列的表中之后，可以使用lastrowid来获取该 ID。这与cmd_query()为INSERT语句返回的 OK 包的insert_id元素相同。如果该语句插入多行，则第一行的 ID 被分配给lastrowid。如果没有 ID 可用，则lastrowid的值为None。

行数

属性的含义取决于所执行的语句。对于SELECT语句，它是返回的行数。对于数据修改语言(DML)语句，如INSERT、UPDATE和DELETE，它是受影响的行数。

对于无缓冲的SELECT查询(默认)，行数只有在所有行都被提取后才知道。在这些情况下，rowcount被初始化为-1，在读取第一行时被设置为 1，然后在读取行时递增。也就是说，rowcount将为-1，直到提取了第一行，之后将反映直到读取该属性时提取的行数。

声明

statement属性保存要执行的最后一个或多个查询。当使用参数替换时(参见“处理用户输入”一节)，statement属性被设置为结果查询，这对于调试非常有用。

包含 _ 行

with_rows属性是一个布尔值，当查询返回一个结果集时为True。与连接的unread_result属性不同，当所有行都被读取后，with_rows不会被设置为False。

字典和命名元组游标子类

除了MySQLCursor之外，其他可用的光标类，比如MySQLCursorDict，都是MySQLCursor类的子类。这意味着所有游标类的行为通常是相同的；区别在于它们如何处理SELECT语句结果的细节，以及对于MySQLCursorPrepared类，查询是如何执行的。

经常出现的一种情况是，需要以字典的形式获取查询结果，而不是每一行都是一个(匿名)元组。例如，在本章的几个示例中使用的循环查询中，城市名称被发现为city[0]或类似名称。按序号位置引用列会使代码难以理解，并且容易出错。使用错误的列号或向查询中添加列很容易导致错误。

更好的解决方案是通过列名来引用它。MySQLCursorDict子类可以自动完成从一组值到一个字典的转换。清单 3-9 展示了一个例子，展示了如何使用设置为True的dictionary参数创建光标。

import mysql.connector

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini")

# Instantiate the cursor

cursor = db.cursor(dictionary=True)

# Execute the query
cursor.execute(
  """SELECT Name, CountryCode,
            Population
       FROM world.city
      WHERE Population > 9000000
      ORDER BY Population DESC"""
)

print(__file__
      + " - Using the dictionary cursor:")
print("")
if (cursor.with_rows):
  # Print the rows found
  print(
    "{0:15s}   {1:7s}   {2:3s}".format(
      "City", "Country", "Pop"
    )
  )
  city = cursor.fetchone()
  while (city):
    print(
      "{0:15s}   {1:⁷s}   {2:4.1f}".format(
        city["Name"],
        city["CountryCode"],
        city["Population"]/1000000
      )
    )
    city = cursor.fetchone()

cursor.close()
db.close()

Listing 3-9Using the MySQLCursorDict cursor Subclass

与前一个例子的唯一区别是，dictionary=True作为参数提供给db.cursor()，当打印值时，列值由列名引用，例如city["Name"]。

MySQLCursorNamedTuple子类的工作方式类似:

...

cursor = db.cursor(named_tuple=True)

...
  city = cursor.fetchone()
  while (city):
    print(
      "{0:15s}   {1:⁷s}   {2:4.1f}".format(
        city.Name,
        city.CountryCode,
        city.Population/1000000
      )
    )
    city = cursor.fetchone()
...

关于游标的讨论到此结束。下一章是关于高级查询用法，它将包括更多关于游标的讨论。然而，在此之前，必须解决一个非常重要的问题:如何处理用户提供的输入。

处理用户输入

程序中的一个常见场景是基于用户或其他外部来源的输入生成查询。毕竟，一个全是静态查询的程序很少让人感兴趣。如何处理这种输入至关重要。如果处理不当，在最好的情况下，会导致神秘的错误；在最坏的情况下，它会导致数据失窃、数据丢失和数据损坏。本节讨论如何正确处理外部提供的数据。

警告

在没有确保信息得到处理以使其不会改变查询含义的情况下，不要将信息输入数据库。例如，如果不这样做，应用就会受到 SQL 注入攻击。

有几种方法可以保护程序。将要讨论的三种方法是

验证输入
参数化查询
使用准备好的语句

这三种方法是本章剩余部分的主题。

验证输入

每当应用读取数据时，验证输入是很重要的。例如，如果应用要求以年为单位的年龄，请验证输入的数据是否为正整数，还可以检查指定的年龄是否在预期范围内。验证不仅有助于使应用更安全，还可以更容易地向用户提供有用的反馈，从而增强用户体验。

注意

客户端数据验证，比如在网页中使用 JavaScript，对于改善用户体验很有帮助，但是不能算作应用的数据验证。原因是用户可以覆盖他们这边执行的验证。

就数据验证而言，Python 编程没有什么独特之处。无论使用何种编程语言，这都是一个常见的要求。然而，如何进行查询参数化是特定于 MySQL Connector/Python 的，这是第二道防线。

查询参数化

防止数据库遭受 SQL 注入攻击的一个好方法是使用参数化查询。这样就把转义和引用数据的任务交给了 MySQL Connector/Python。

使用光标execute()方法进行参数替换有两种方式。第一种是提供一个列表或元组，其中的值与它们在查询中出现的顺序相同。在这种情况下，每个参数在查询文本中都用一个%s表示。如果只有几个参数，或者重复使用，例如对于一个INSERT语句，这是一个提供参数的有用方法。

小费

在参数中指定单个参数，如(“John Doe”)，不会创建元组；最终结果是一个标量字符串。如果只有一个参数，可以使用一个列表或者在值后添加一个逗号，比如(" John Doe "，)，将值强制转换成一个元组。

另一种方法是提供一个字典，其中每个参数都有一个名称(字典的键，值是参数值)。这更加冗长，但是从好的方面来说，这也使得源代码更容易阅读。如果查询包括几个参数，情况尤其如此。参数在查询中指定，如%(name_of_parameter)s。

例如，考虑以下查询:

SELECT *
  FROM world.city
 WHERE Name = ?

问号表示应用用户将提供的数据。假设用户将城市名称指定为'Sydney' OR True。列表 3-10 显示了使用参数化查询的字典处理输入的两种不同方式。

import mysql.connector

input = "'Sydney' OR True"

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini")

# Instantiate the cursor
cursor = db.cursor(dictionary=True)

# Execute the query without parameter

sql = """SELECT *

           FROM world.city
          WHERE Name = {0}""".format(input)

cursor.execute(sql)

cursor.fetchall()
print("1: Statement: {0}".format(
  cursor.statement))
print("1: Row count: {0}\n".format(
  cursor.rowcount))

# Execute the query with parameter

sql = """SELECT *

           FROM world.city
          WHERE Name = %(name)s"""

params = {'name': input}

cursor.execute(

  sql,
  params=params

)

cursor.fetchall()
print("2: Statement: {0}".format(
  cursor.statement))
print("2: Row count: {0}".format(
  cursor.rowcount))

cursor.close()
db.close()

Listing 3-10Handling User-Provided Data

该输入首先在程序的input变量中设置。然后查询执行两次。在第一次执行中，使用format()字符串方法将输入简单地添加到查询中。在第二次执行中，通过在调用execute()光标方法时设置params选项来添加输入。每次执行后，打印执行的语句和找到的行数。输出是

1: Statement: SELECT *
           FROM world.city
          WHERE Name = 'Sydney' OR True
1: Row count: 4079

2: Statement: SELECT *
           FROM world.city
          WHERE Name = '\'Sydney\' OR True'
2: Row count: 0

注意第一次执行是如何在world.city表中找到所有 4079 行的。原因是WHERE子句最终由两部分组成:Name = 'Sydney'和True。由于两个条件之间的OR，所有的城市将最终匹配，因为True匹配所有的东西。

另一方面，第二次执行会转义单引号，并在整个字符串周围添加引号。因此，找不到行，因为没有城市被命名为“‘悉尼’或 True。”

警告

MySQL 连接器/Python 使用 Python 数据类型来确定如何将参数插入到查询中。因此，它不是针对用户提供错误类型数据的防御措施。为了防止使用错误的数据类型，必须使用数据验证和/或准备好的语句。

使用参数化不仅有利于确保数据被正确引用和转义。它还使得重用查询变得容易，并且可以在应用中使用 Python 数据类型，并让 MySQL Connector/Python 处理到 MySQL 数据类型的正确转换。日期就是一个例子。在清单 3-11 中，首先创建一个临时表，然后插入一个包含日期的行，然后打印实际执行的查询。这次参数是在一个元组中提供的。

import mysql.connector
import datetime

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini")

# Instantiate the cursor
cursor = db.cursor()

# Create a temporary table
sql = """
CREATE TEMPORARY TABLE world.tmp_person (
  Name varchar(50) NOT NULL,
  Birthday date NOT NULL,
  PRIMARY KEY (Name)
)"""
cursor.execute(sql)

sql = """
INSERT INTO world.tmp_person
VALUES (%s, %s)
"""

params = (

  "John Doe",
  datetime.date(1970, 10, 31)

)

cursor.execute(sql,params=params)

print("Statement:\n{0}".format(
  cursor.statement))

cursor.close()
db.close()

Listing 13-11Using Parameters with a datetime Value

print 语句的输出是:

Statement:
INSERT INTO world.tmp_person
VALUES ('John Doe', '1970-10-31')

所以参数替换确保了 1970 年 10 月 31 日的日期在发送给 MySQL 的查询中被正确地表示为'1970-10-31'。

与参数优化相关的一种方法是准备语句。这是将要讨论的最后一种防御方法。

准备好的陈述

在处理数据库时，预准备语句非常有用，因为它们比迄今为止使用的更直接的查询方式有一些优势。其中的两个优点是重用查询时性能的提高和防止 SQL 注入。

从 MySQL Connector/Python 的角度来看，使用参数化或预处理语句差别不大。事实上，除了创建不同的游标子类之外，从应用的角度来看，用法是相同的。

然而，在幕后，还是有一些微妙的不同。第一次执行查询时，准备语句；也就是将语句提交给 MySQL 服务器，并带有占位符，MySQL 服务器准备语句以备将来使用。然后，游标发送一个命令，告诉 MySQL 服务器执行准备好的语句以及用于查询的参数。这种方法有两个优点:

MySQL Server 在准备阶段尽可能多地准备查询。这意味着对于后续的执行，需要做的工作更少，并且只需要通过网络发送参数，因此性能得到了提高。
MySQL 服务器解析查询需要哪些表和列，因此它能够确保根据列的数据类型处理提交的参数。这阻止了 SQL 注入。

注意

关于性能，需要注意的一件事是，如果查询只执行一次，性能不会提高。另一方面，会有到 MySQL 服务器的额外往返，所以使用准备好的语句的性能会比直接执行查询差。重用预处理语句的次数越多，性能就越好。

用于准备和执行准备好的语句的确切方法取决于使用的是纯 Python 还是 MySQL Connector/Python 的 C 扩展实现。纯 Python 实现使用了PREPARE和EXECUTE语句(参见 https://dev.mysql.com/doc/refman/en/sql-syntax-prepared-statements.html )。C 扩展使用二进制协议，效率更高。使用 C 扩展和预准备语句需要使用_mysql_connector模块，这将在第四章中讨论。

清单 3-12 展示了一个使用相同查询(除了国家代码之外)来查找美国和印度人口最多的三个城市的例子。

import mysql.connector

# Format strings
FMT_QUERY = "Query {0}:\n" + "-"*8
FMT_HEADER = "{0:18s}   {1:7s}   {2:3s}"
FMT_ROW = "{0:18s}   {1:⁷s}   {2:4.1f}"

# Define the queries
SQL = """
SELECT Name, CountryCode, Population
  FROM world.city
 WHERE CountryCode = %s
 ORDER BY Population DESC
 LIMIT 3"""

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini", use_pure=True)

cursor = db.cursor(prepared=True)

# Execute the query finding the top
# three populous cities in the USA and
# India.
count = 0
for country in ("USA", "IND"):
  count = count + 1;
  print(FMT_QUERY.format(count))

  cursor.execute(SQL, (country,))

  if (cursor.with_rows):
    # Print the result.
    print(FMT_HEADER.format(
      "City", "Country", "Pop"))
    city = cursor.fetchone()
    while (city):
      print(FMT_ROW.format(
        city[0],
        city[1],
        city[2]/1000000
      ))
      city = cursor.fetchone()

  print("")

cursor.close()
db.close()

Listing 13-12Using Prepared Statements

这个例子与前面的例子非常相似，除了光标是用prepared=True创建的。主要区别是不支持命名参数，所以使用了%s。该程序的输出是

Query 1:
--------
City                 Country    Pop
New York               USA      8.0
Los Angeles            USA      3.7
Chicago                USA      2.9

Query 2:
--------
City                 Country    Pop
Mumbai (Bombay)        IND     10.5
Delhi                  IND      7.2
Calcutta [Kolkata]     IND      4.4

使用准备好的语句将行转换为字典或命名元组时，不支持。这使得使用预准备语句有些困难。最后，callproc()和stored_results()方法(参见第四章中的“存储过程”一节)没有实现。好的一面是提高了对 SQL 注入的保护，所以值得做额外的工作。

注意

预准备语句游标比其他游标子类更基本。不支持字符串、字典、命名元组和存储过程方法的数据转换。如果经常使用预准备语句，那么值得考虑添加对这些特性的支持的自定义游标类。

摘要

本章讲述了使用 MySQL Connector/Python 执行查询的基础知识。您开始时使用了可用于连接对象的方法:

cmd_query()执行查询
get_rows()在查询生成结果集时获取多行(默认为所有行)
get_row()一次提取一行

这些方法可以被认为是低级方法。在更高层次上，游标类支持执行查询，同时支持将结果自动转换为 Python 类型和其他特性。讨论的光标方法有

execute()执行查询
用于读取结果集的fetchone()、fetchmany()和fetchall()

最后，您学习了如何处理用户输入。非常重要的一点是，所有的输入都要经过验证，并且使用参数化来防止 SQL 注入。可以使用游标执行参数化。在游标中启用预准备语句提供了额外的保护，因为是 MySQL 服务器在了解目标数据类型的情况下处理参数。当重复执行相同的基本查询时，预处理语句也可以提高性能。

MySQL Connector/Python 中的查询执行还有很多工作要做，所以下一章将继续介绍更高级的例子。