Python 和 MySQL 数据库：实用介绍

目录

比较 Mysql 与其他 SQL 数据库

安装 MySQL 服务器和 MySQL 连接器/Python

安装 MySQL 服务器

安装 MySQL 连接器/Python

建立与 MySQL 服务器的连接

建立连接

创建新数据库

连接到现有数据库

创建、更改和删除表

定义数据库架构

使用 CREATE TABLE 语句创建表

使用 DESCRIBE 语句显示表模式

使用 ALTER 语句修改表架构

使用 DROP 语句删除表

在表中插入记录

使用 .execute()

使用 .executemany()

从数据库中读取记录

使用 SELECT 语句读取记录

使用 WHERE 子句过滤结果

使用 JOIN 语句处理多个表

从数据库更新和删除记录

更新命令

删除命令

连接 Python 和 MySQL 的其他方法

结论

MySQL是当今市场上最流行的数据库管理系统 (DBMS)之一。它在今年的DB-Engines 排名中仅次于Oracle DBMS。由于大多数软件应用程序需要以某种形式与数据交互，因此 Python 等编程语言提供了用于存储和访问这些数据源的工具。

使用本教程中讨论的技术，您将能够有效地将 MySQL 数据库与 Python 应用程序集成。您将为电影分级系统开发一个小型 MySQL 数据库，并学习如何直接从 Python 代码中查询它。

在本教程结束时，您将能够：

识别MySQL 的独特功能

将您的应用程序连接到 MySQL 数据库

查询数据库以获取所需数据

处理访问数据库时发生的异常

在构建数据库应用程序时使用最佳实践

要从本教程中获得最大收益，您应该具备 Python 概念的应用知识，例如for循环、函数、异常处理以及使用pip. 你也应该有关系数据库管理系统的一个基本的了解和SQL查询一样SELECT，DROP，CREATE，和JOIN。

比较 MySQL 与其他 SQL 数据库

SQL代表结构化查询语言，是一种广泛使用的用于管理关系数据库的编程语言。您可能听说过不同风格的基于 SQL 的 DBMS。最流行的包括MySQL、PostgreSQL、SQLite和SQL Server。所有这些数据库都符合SQL 标准，但符合程度各不相同。

作为开源自1995年成立以来，MySQL的很快成为SQL解决方案中的市场领导者。MySQL 也是 Oracle 生态系统的一部分。虽然其核心功能是完全免费的，但也有一些付费附加组件。目前，所有主要科技公司都在使用 MySQL，包括 Google、LinkedIn、Uber、Netflix、Twitter 等。

除了大型开源社区的支持，MySQL 的成功还有许多其他原因：

易于安装： MySQL 被设计为用户友好的。设置 MySQL 数据库非常简单，几个广泛使用的第三方工具，如phpMyAdmin，进一步简化了设置过程。MySQL 可用于所有主要操作系统，包括 Windows、macOS、Linux 和 Solaris。

速度： MySQL 以极快的数据库解决方案而著称。从长远来看，它的占地面积相对较小，并且具有极强的可扩展性。

用户权限和安全性： MySQL 附带一个脚本，允许您设置密码安全级别、分配管理员密码以及添加和删除用户帐户权限。此脚本简化了 Web 托管用户管理门户的管理过程。其他 DBMS，如 PostgreSQL，使用更复杂的配置文件。

虽然 MySQL 以其速度和易用性而闻名，但您可以使用 PostgreSQL获得更多高级功能。此外，MySQL 并不完全符合 SQL 标准，并且具有某些功能限制，例如不支持FULL JOIN子句。

您可能还面临一些在 MySQL 中并发读写的问题。如果您的软件有很多用户同时向其写入数据，那么 PostgreSQL 可能是更合适的选择。

注意：要在实际环境中对 MySQL 和 PostgreSQL 进行更深入的比较，请查看为什么优步工程从 Postgres 切换到 MySQL。

SQL Server 也是一种非常流行的 DBMS，以其可靠性、效率和安全性而闻名。它是公司的首选，尤其是在银行领域，他们经常处理大流量工作负载。它是一种商业解决方案，是与 Windows 服务最兼容的系统之一。

2010 年，当甲骨文收购Sun Microsystems和 MySQL 时，很多人都担心 MySQL 的未来。当时，甲骨文是 MySQL 最大的竞争对手。开发人员担心这是甲骨文的敌意收购，目的是摧毁 MySQL。

迄今为止，MariaDB 仍然完全获得 GPL 许可，将其完全保留在公共领域中。另一方面，MySQL 的某些功能仅适用于付费许可证。此外，MariaDB 提供了几个 MySQL 服务器不支持的非常有用的功能，例如分布式 SQL和列式存储。您可以在MariaDB 的网站上找到 MySQL 和 MariaDB 之间的更多差异。

MySQL 使用与标准 SQL 非常相似的语法。但是，官方文档中提到了一些显着差异。

安装 MySQL 服务器和 MySQL 连接器/Python

现在，要开始学习本教程，您需要设置两件事：MySQL 服务器和MySQL 连接器。MySQL 服务器将提供处理数据库所需的所有服务。服务器启动并运行后，您可以使用 MySQL Connector/Python 将您的 Python 应用程序与其连接。

安装 MySQL 服务器

在官方文档详细介绍了推荐的方法来下载和安装MySQL服务器。您会找到所有流行操作系统的说明，包括Windows、macOS、Solaris、Linux等等。

对于 Windows，最好的方法是下载MySQL Installer并让它负责整个过程。安装管理器还可以帮助您配置 MySQL 服务器的安全设置。在 Accounts and Roles 页面上，您需要输入root (admin) 帐户的密码，还可以选择添加其他具有不同权限的用户：

虽然您必须在安装过程中为 root 帐户指定凭据，但您可以稍后修改这些设置。

注意：记住主机名、用户名和密码，因为这些将在稍后与 MySQL 服务器建立连接时需要。

虽然本教程只需要 MySQL 服务器，但您也可以使用这些安装程序设置其他有用的工具，如MySQL Workbench。如果您不想直接在操作系统中安装 MySQL，那么使用 Docker 在 Linux 上部署 MySQL是一个方便的选择。

安装 MySQL 连接器/Python

一个数据库驱动程序是一个软件，它允许连接的应用程序和交互与数据库系统。像 Python 这样的编程语言需要一个特殊的驱动程序才能与来自特定供应商的数据库对话。

这些驱动程序通常作为第三方模块获得。在Python数据库API（DB-API）定义了标准接口与所有的Python数据库驱动程序必须遵守。这些细节记录在PEP 249 中。所有 Python 数据库驱动程序，例如SQLite 的sqlite3、PostgreSQL 的psycopg和MySQL 的 MySQL Connector/Python，都遵循这些实现规则。

注意： MySQL 的官方文档使用术语连接器而不是驱动程序。从技术上讲，连接器仅与连接到数据库相关联，而不与数据库交互。但是，该术语通常用于包含连接器和驱动程序的整个数据库访问模块。

为了与文档保持一致，每当提到 MySQL 时，您都会看到术语连接器。

许多流行的编程语言都有自己的数据库 API。例如，Java 具有Java 数据库连接 (JDBC) API。如果需要将 Java 应用程序连接到 MySQL 数据库，则需要使用MySQL JDBC 连接器，它遵循 JDBC API。

同样，在 Python 中，您需要安装 Python MySQL 连接器才能与 MySQL 数据库交互。许多包都遵循 DB-API 标准，但其中最受欢迎的是MySQL Connector/Python。您可以通过以下方式获得它pip：

$ pip install mysql-connector-python

pip将连接器作为第三方模块安装在当前活动的虚拟环境中。建议您为项目以及所有依赖项设置一个隔离的虚拟环境。

要测试安装是否成功，请在 Python 终端上键入以下命令：

>>>

>>> import mysql.connector

如果上面的代码执行没有错误，那么mysql.connector就安装好了，可以使用了。如果您遇到任何错误，请确保您处于正确的虚拟环境中并且您使用的是正确的 Python 解释器。

确保您安装了正确的mysql-connector-python包，这是一个纯 Python 实现。谨防类似名称但现在已贬值的连接器，如mysql-connector.

建立与 MySQL 服务器的连接

MySQL 是一个基于服务器的数据库管理系统。一台服务器可能包含多个数据库。要与数据库交互，您必须首先与服务器建立连接。一个 Python 程序与基于 MySQL 的数据库交互的一般工作流程如下：

连接到 MySQL 服务器。

创建一个新的数据库。

连接到新创建的或现有的数据库。

执行 SQL 查询并获取结果。

如果对表进行了任何更改，请通知数据库。

关闭与 MySQL 服务器的连接。

这是一个通用工作流程，可能因个别应用程序而异。但无论应用程序是什么，第一步都是将数据库与应用程序连接起来。

建立连接

与 MySQL 服务器交互的第一步是建立连接。为此，您需要connect()从mysql.connector模块。这个函数像参数host，user以及password并返回一个MySQLConnection对象。您可以从用户那里接收这些凭据作为输入并将它们传递给connect()：

from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), ) as connection: print(connection) except Error as e: print(e)

上面的代码使用输入的登录凭据与您的 MySQL 服务器建立连接。作为回报，您会得到一个MySQLConnection对象，该对象存储在connection变量中。从现在开始，您将使用这个变量来访问您的 MySQL 服务器。

在上面的代码中有几个重要的事情需要注意：

您应该始终处理在与MySQL 服务器建立连接时可能引发的异常。这就是为什么您使用try...except块来捕获和打印您可能遇到的任何异常的原因。

访问完数据库后，您应该始终关闭连接。保留未使用的打开连接可能会导致一些意外错误和性能问题。上面的代码利用了上下文管理器 usingwith，它抽象了连接清理过程。

您永远不应该直接在 Python 脚本中硬编码您的登录凭据，即您的用户名和密码。这对于部署来说是一种糟糕的做法，并且会带来严重的安全威胁。上面的代码提示用户输入登录凭据。它使用内置getpass模块隐藏密码。虽然这比硬编码要好，但还有其他更安全的方式来存储敏感信息，比如使用环境变量。

您现在已经在程序和 MySQL 服务器之间建立了连接，但您仍然需要创建一个新数据库或连接到服务器内的现有数据库。

创建新数据库

在上一节中，您建立了与 MySQL 服务器的连接。要创建一个新的数据库，您需要执行一条 SQL 语句：

CREATE DATABASE books_db;

上面的语句将创建一个名为 的新数据库books_db。

注意：在 MySQL 中，必须;在语句的末尾放置一个分号 ( )，它表示查询的终止。但是，MySQL Connector/Python 会自动在您的查询末尾附加一个分号，因此无需在您的 Python 代码中使用它。

要在 Python 中执行 SQL 查询，您需要使用cursor，它抽象了对数据库记录的访问。MySQL Connector/Python 为您提供了MySQLCursor类，该类实例化了可以在 Python 中执行 MySQL 查询的对象。MySQLCursor类的实例也称为 a cursor。

cursor对象使用MySQLConnection对象与您的 MySQL 服务器进行交互。要创建cursor，请使用变量的.cursor()方法connection：

cursor = connection.cursor()

上面的代码为您提供了一个MySQLCursor类的实例。

需要被执行的查询发送到cursor.execute()的字符串格式。在这种特殊情况下，您将CREATE DATABASE查询发送至cursor.execute()：

from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), ) as connection: create_db_query = "CREATE DATABASE online_movie_rating" with connection.cursor() as cursor: cursor.execute(create_db_query) except Error as e: print(e)

执行上述代码后，您将online_movie_rating在 MySQL 服务器中调用一个新数据库。

该CREATE DATABASE查询被存储为一个字符串create_db_query变量，然后传递到cursor.execute()用于执行。该代码使用带有cursor对象的上下文管理器来处理清理过程。

如果您的服务器中已存在同名数据库，您可能会在此处收到错误消息。为了确认这一点，您可以显示服务器中所有数据库的名称。使用之前的相同MySQLConnection对象，执行SHOW DATABASES语句：

>>>

>>> show_db_query = "SHOW DATABASES" >>> with connection.cursor() as cursor: ... cursor.execute(show_db_query) ... for db in cursor: ... print(db) ... ('information_schema',) ('mysql',) ('online_movie_rating',) ('performance_schema',) ('sys',)

上面的代码打印当前在您的 MySQL 服务器中的所有数据库的名称。该SHOW DATABASES命令还会输出一些您未在服务器中创建的数据库，例如information_schema、performance_schema等。这些数据库由 MySQL 服务器自动生成，并提供对各种数据库元数据和 MySQL 服务器设置的访问。

您在本节中通过执行CREATE DATABASE语句创建了一个新数据库。在下一节中，您将看到如何连接到已经存在的数据库。

连接到现有数据库

在上一节中，您创建了一个名为 的新数据库online_movie_rating。但是，您仍然没有连接到它。在许多情况下，您已经拥有一个想要与 Python 应用程序连接的 MySQL 数据库。

您可以connect()使用之前使用的相同函数发送一个名为 的附加参数来执行此操作database：

from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), database="online_movie_rating", ) as connection: print(connection) except Error as e: print(e)

上面的代码与您之前使用的连接脚本非常相似。这里唯一的变化是一个附加database参数，您的数据库名称将传递给connect(). 执行此脚本后，您将连接到online_movie_rating数据库。

创建、更改和删除表

在本节中，您将学习如何执行一些基本的DDL查询，如CREATE，DROP和ALTER与Python。您将快速了解将在本教程的其余部分中使用的 MySQL 数据库。您还将创建数据库所需的所有表，并在稍后了解如何对这些表进行修改。

定义数据库架构

您可以从为在线电影评级系统创建数据库模式开始。数据库将由三个表组成：

movies 包含有关电影的一般信息并具有以下属性：

id

title

release_year

genre

collection_in_mil

reviewers 包含有关发表评论或评级的人的信息，并具有以下属性：

id

first_name

last_name

ratings 包含有关已发布评级的信息，并具有以下属性：

movie_id （外键）

reviewer_id （外键）

rating

现实世界的电影分级系统，如IMDb，需要存储一堆其他属性，如电子邮件、电影演员列表等。如果需要，您可以向该数据库添加更多表和属性。但这三个表足以满足本教程的目的。

下图描述了数据库架构：

该数据库中的表相互关联。movies并且reviewers会有多对多的关系，因为一部电影可以被多个评论者评论，一个评论者可以评论多部电影。该ratings表的连接movies表与reviewers表。

使用CREATE TABLE语句创建表

现在，要在 MySQL 中创建一个新表，您需要使用CREATE TABLE语句。以下 MySQL 查询将为movies您的online_movie_rating数据库创建表：

CREATE TABLE movies( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100), collection_in_mil INT );

如果您之前看过 SQL 语句，那么上面的大部分查询可能都有意义。但是您应该注意 MySQL 语法中的一些差异。

例如，MySQL 有多种数据类型供您阅读，包括YEAR、INT、BIGINT等。此外，AUTO_INCREMENT当插入新记录时必须自动增加列值时，MySQL 使用关键字。

要创建新表，您需要将此查询传递给cursor.execute()，它接受 MySQL 查询并在连接的 MySQL 数据库上执行查询：

create_movies_table_query = """ CREATE TABLE movies( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100), collection_in_mil INT ) """ with connection.cursor() as cursor: cursor.execute(create_movies_table_query) connection.commit()

现在您movies的数据库中有该表。您传递create_movies_table_query到cursor.execute()，它执行所需的执行。

注意：该connection变量指的MySQLConnection是连接到数据库时返回的对象。

另外，请注意connection.commit()代码末尾的语句。默认情况下，您的 MySQL 连接器不会自动提交事务。在 MySQL 中，事务中提到的修改只有在您COMMIT最终使用命令时才会发生。每次事务后始终调用此方法以在实际表中执行更改。

与对movies表所做的一样，执行以下脚本来创建reviewers表：

create_reviewers_table_query = """ CREATE TABLE reviewers ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100) ) """ with connection.cursor() as cursor: cursor.execute(create_reviewers_table_query) connection.commit()

如果需要，您可以添加有关审阅者的更多信息，例如他们的电子邮件 ID 或人口统计信息。但是first_name，last_name现在将满足您的目的。

最后，您可以ratings使用以下脚本创建表：

create_ratings_table_query = """ CREATE TABLE ratings ( movie_id INT, reviewer_id INT, rating DECIMAL(2,1), FOREIGN KEY(movie_id) REFERENCES movies(id), FOREIGN KEY(reviewer_id) REFERENCES reviewers(id), PRIMARY KEY(movie_id, reviewer_id) ) """ with connection.cursor() as cursor: cursor.execute(create_ratings_table_query) connection.commit()

与标准 SQL 相比，MySQL 中外键关系的实现略有不同且受到限制。在 MySQL 中，外键约束中的 parent 和 child 必须使用相同的存储引擎。

一个存储引擎是基础软件组件，数据库管理系统用于执行SQL操作。在 MySQL 中，存储引擎有两种不同的风格：

事务存储引擎是事务安全的，并允许您使用简单的命令回滚事务，例如rollback. 许多流行的 MySQL 引擎，包括InnoDB和NDB，都属于这一类。

非事务性存储引擎依赖于复杂的手动代码来撤消在数据库上提交的语句。MyISAM、MEMORY和许多其他 MySQL 引擎是非事务性的。

InnoDB 是默认且最受欢迎的存储引擎。它通过支持外键约束来帮助维护数据完整性。这意味着检查外键上的任何CRUD操作以确保它不会导致不同表之间的不一致。

另外，请注意该ratings表使用列movie_id和reviewer_id，这两个外键，共同作为主键。此步骤可确保审阅者不能对同一部电影进行两次评分。

您可以选择对多次执行重复使用同一个游标。在这种情况下，所有执行都将成为一个原子事务，而不是多个单独的事务。例如，您可以CREATE TABLE使用一个游标执行所有语句，然后只提交一次事务：

with connection.cursor() as cursor: cursor.execute(create_movies_table_query) cursor.execute(create_reviewers_table_query) cursor.execute(create_ratings_table_query) connection.commit()

上面的代码将首先执行所有三个CREATE语句。然后它会向COMMITMySQL 服务器发送一个命令来提交你的事务。您还可以使用.rollback()向ROLLBACKMySQL 服务器发送命令并从事务中删除所有数据更改。

使用DESCRIBE语句显示表模式

现在，您已经创建了所有三个表，您可以使用以下 SQL 语句查看它们的架构：

DESCRIBE ;

要从cursor对象返回一些结果，您需要使用cursor.fetchall(). 此方法从上次执行的语句中获取所有行。假设您已经MySQLConnection在connection变量中拥有该对象，您可以打印出通过cursor.fetchall()以下方式获取的所有结果：

>>>

>>> show_table_query = "DESCRIBE movies" >>> with connection.cursor() as cursor: ... cursor.execute(show_table_query) ... # Fetch rows from last executed query ... result = cursor.fetchall() ... for row in result: ... print(row) ... ('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment') ('title', 'varchar(100)', 'YES', '', None, '') ('release_year', 'year(4)', 'YES', '', None, '') ('genre', 'varchar(100)', 'YES', '', None, '') ('collection_in_mil', 'int(11)', 'YES', '', None, '')

执行上述代码后，您应该会收到一个表格，其中包含有关表格中所有列的信息movies。对于每一列，您将收到列的数据类型、列是否为主键等详细信息。

使用ALTER语句修改表架构

在movies表中，您有一个名为 的列collection_in_mil，其中包含以百万美元为单位的电影票房收入。您可以编写以下 MySQL 语句将collection_in_mil属性的数据类型从修改INT为DECIMAL：

ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1);

DECIMAL(4,1)表示可以有最多4位数的十进制数，其中1为十进制，如120.1、3.4、38.0等。执行ALTER TABLE语句后，您可以使用DESCRIBE以下命令显示更新的表架构：

>>>

>>> alter_table_query = """ ... ALTER TABLE movies ... MODIFY COLUMN collection_in_mil DECIMAL(4,1) ... """ >>> show_table_query = "DESCRIBE movies" >>> with connection.cursor() as cursor: ... cursor.execute(alter_table_query) ... cursor.execute(show_table_query) ... # Fetch rows from last executed query ... result = cursor.fetchall() ... print("Movie Table Schema after alteration:") ... for row in result: ... print(row) ... Movie Table Schema after alteration ('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment') ('title', 'varchar(100)', 'YES', '', None, '') ('release_year', 'year(4)', 'YES', '', None, '') ('genre', 'varchar(100)', 'YES', '', None, '') ('collection_in_mil', 'decimal(4,1)', 'YES', '', None, '')

如输出所示，该collection_in_mil属性现在的类型为DECIMAL(4,1)。另请注意，在上面的代码中，您调用了cursor.execute()两次。但是cursor.fetchall()只从最后执行的查询中获取行，即show_table_query.

使用DROP语句删除表

要删除表，需要在 MySQL 中执行该DROP TABLE语句。删除表是一个不可逆的过程。如果您执行下面的代码，那么您将需要CREATE TABLE再次调用查询以ratings在接下来的部分中使用该表。

要删除ratings表，请发送drop_table_query至cursor.execute()：

drop_table_query = "DROP TABLE ratings" with connection.cursor() as cursor: cursor.execute(drop_table_query)

如果你执行上面的代码，你就成功地删除了ratings表。

在表中插入记录

在最后一节，您创建数据库中的三个表：movies，reviewers，和ratings。现在您需要用数据填充这些表。本节将介绍在 MySQL Connector for Python 中插入记录的两种不同方法。

第一种方法 ，.execute()当记录数量很少并且可以硬编码记录时效果很好。第二种方法.executemany()更受欢迎，更适合现实世界的场景。

使用 .execute()

第一种方法使用的cursor.execute()方法与您迄今为止一直使用的方法相同。您将INSERT INTO查询写入字符串并将其传递给cursor.execute(). 您可以使用此方法向movies表中插入数据。

作为参考，该movies表有五个属性：

id

title

release_year

genre

collection_in_mil

您不需要添加数据，id因为它会AUTO_INCREMENT自动id为您计算。以下脚本将记录插入movies表中：

insert_movies_query = """ INSERT INTO movies (title, release_year, genre, collection_in_mil) VALUES ("Forrest Gump", 1994, "Drama", 330.2), ("3 Idiots", 2009, "Drama", 2.4), ("Eternal Sunshine of the Spotless Mind", 2004, "Drama", 34.5), ("Good Will Hunting", 1997, "Drama", 138.1), ("Skyfall", 2012, "Action", 304.6), ("Gladiator", 2000, "Action", 188.7), ("Black", 2005, "Drama", 3.0), ("Titanic", 1997, "Romance", 659.2), ("The Shawshank Redemption", 1994, "Drama",28.4), ("Udaan", 2010, "Drama", 1.5), ("Home Alone", 1990, "Comedy", 286.9), ("Casablanca", 1942, "Romance", 1.0), ("Avengers: Endgame", 2019, "Action", 858.8), ("Night of the Living Dead", 1968, "Horror", 2.5), ("The Godfather", 1972, "Crime", 135.6), ("Haider", 2014, "Action", 4.2), ("Inception", 2010, "Adventure", 293.7), ("Evil", 2003, "Horror", 1.3), ("Toy Story 4", 2019, "Animation", 434.9), ("Air Force One", 1997, "Drama", 138.1), ("The Dark Knight", 2008, "Action",535.4), ("Bhaag Milkha Bhaag", 2013, "Sport", 4.1), ("The Lion King", 1994, "Animation", 423.6), ("Pulp Fiction", 1994, "Crime", 108.8), ("Kai Po Che", 2013, "Sport", 6.0), ("Beasts of No Nation", 2015, "War", 1.4), ("Andadhun", 2018, "Thriller", 2.9), ("The Silence of the Lambs", 1991, "Crime", 68.2), ("Deadpool", 2016, "Action", 363.6), ("Drishyam", 2015, "Mystery", 3.0) """ with connection.cursor() as cursor: cursor.execute(insert_movies_query) connection.commit()

该movies表现在加载了 30 条记录。代码connection.commit()在最后调用。.commit()在对表进行任何修改后调用是至关重要的。

使用 .executemany()

前一种方法更适用于记录数量相当少并且您可以将这些记录直接写入代码的情况。但这很少是真的。您通常会将这些数据存储在某个其他文件中，或者这些数据将由不同的脚本生成并需要添加到 MySQL 数据库中。

这就是.executemany()派上用场的地方。它接受两个参数：

包含需要插入的记录的占位符的查询

一个列表，其中包含的所有记录，你要插入

以下示例为reviewers表插入记录：

insert_reviewers_query = """ INSERT INTO reviewers (first_name, last_name) VALUES ( %s, %s ) """ reviewers_records = [ ("Chaitanya", "Baweja"), ("Mary", "Cooper"), ("John", "Wayne"), ("Thomas", "Stoneman"), ("Penny", "Hofstadter"), ("Mitchell", "Marsh"), ("Wyatt", "Skaggs"), ("Andre", "Veiga"), ("Sheldon", "Cooper"), ("Kimbra", "Masters"), ("Kat", "Dennings"), ("Bruce", "Wayne"), ("Domingo", "Cortes"), ("Rajesh", "Koothrappali"), ("Ben", "Glocker"), ("Mahinder", "Dhoni"), ("Akbar", "Khan"), ("Howard", "Wolowitz"), ("Pinkie", "Petit"), ("Gurkaran", "Singh"), ("Amy", "Farah Fowler"), ("Marlon", "Crafford"), ] with connection.cursor() as cursor: cursor.executemany(insert_reviewers_query, reviewers_records) connection.commit()

在上面的脚本中，您将查询和记录列表作为参数传递给.executemany()。这些记录可能是从文件或用户那里获取的，并存储在reviewers_records列表中。

该代码%s用作必须插入到insert_reviewers_query. 占位符充当格式说明符并帮助为字符串中的变量保留一个位置。然后在执行期间将指定的变量添加到该位置。

您可以类似地使用.executemany()在ratings表中插入记录：

insert_ratings_query = """ INSERT INTO ratings (rating, movie_id, reviewer_id) VALUES ( %s, %s, %s) """ ratings_records = [ (6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17), (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4), (8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10), (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19), (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9), (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19), (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15), (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20), (8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13), (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17), (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4), (9.8, 13, 1) ] with connection.cursor() as cursor: cursor.executemany(insert_ratings_query, ratings_records) connection.commit()

所有三个表现在都填充了数据。您现在拥有一个功能齐全的在线电影评级数据库。下一步是了解如何与该数据库进行交互。

从数据库中读取记录

到目前为止，您一直在构建数据库。现在是时候对其执行一些查询并从该数据集中找到一些有趣的属性了。在本节中，您将学习如何使用SELECT语句从数据库表中读取记录。

使用SELECT语句读取记录

要检索记录，您需要向 发送SELECT查询cursor.execute()。然后使用cursor.fetchall()以行或记录列表的形式提取检索到的表。

尝试编写一个 MySQL 查询以从movies表中选择所有记录并将其发送到.execute()：

>>>

>>> select_movies_query = "SELECT * FROM movies LIMIT 5" >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... result = cursor.fetchall() ... for row in result: ... print(row) ... (1, 'Forrest Gump', 1994, 'Drama', Decimal('330.2')) (2, '3 Idiots', 2009, 'Drama', Decimal('2.4')) (3, 'Eternal Sunshine of the Spotless Mind', 2004, 'Drama', Decimal('34.5')) (4, 'Good Will Hunting', 1997, 'Drama', Decimal('138.1')) (5, 'Skyfall', 2012, 'Action', Decimal('304.6'))

该result变量保存使用返回的记录.fetchall()。它是代表表中单个记录的元组列表。

在上面的查询中，您使用LIMIT子句来限制从SELECT语句接收的行数。开发人员经常使用LIMIT进行分页处理大量数据的时候。

在 MySQL 中，该LIMIT子句采用一或两个非负数字参数。使用一个参数时，您指定要返回的最大行数。由于您的查询包括LIMIT 5，因此仅5获取第一条记录。使用这两个参数时，您还可以指定要返回的第一行的偏移量：

SELECT * FROM movies LIMIT 2,5;

第一个参数指定 的偏移量2，第二个参数将返回的行数限制为5。上面的查询将返回第 3 到 7 行。

您还可以查询选定的列：

>>>

>>> select_movies_query = "SELECT title, release_year FROM movies LIMIT 5" >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for row in cursor.fetchall(): ... print(row) ... ('Forrest Gump', 1994) ('3 Idiots', 2009) ('Eternal Sunshine of the Spotless Mind', 2004) ('Good Will Hunting', 1997) ('Skyfall', 2012)

现在，代码仅从两个指定的列输出值：title和release_year。

使用WHERE子句过滤结果

您可以使用WHERE子句按特定条件过滤表记录。例如，要检索票房收入超过 3 亿美元的所有电影，您可以运行以下查询：

SELECT title, collection_in_mil FROM movies WHERE collection_in_mil > 300;

您还可以在最后一个查询中使用ORDER BY子句将结果从最高收入者到最低收入者进行排序：

>>>

>>> select_movies_query = """ ... SELECT title, collection_in_mil ... FROM movies ... WHERE collection_in_mil > 300 ... ORDER BY collection_in_mil DESC ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchall(): ... print(movie) ... ('Avengers: Endgame', Decimal('858.8')) ('Titanic', Decimal('659.2')) ('The Dark Knight', Decimal('535.4')) ('Toy Story 4', Decimal('434.9')) ('The Lion King', Decimal('423.6')) ('Deadpool', Decimal('363.6')) ('Forrest Gump', Decimal('330.2')) ('Skyfall', Decimal('304.6'))

MySQL 提供了大量的字符串格式化操作，例如CONCAT连接字符串。通常，网站会显示电影名称及其发行年份以避免混淆。要检索票房收入前五名的电影的片名，并与它们的发行年份相连，您可以编写以下查询：

>>>

>>> select_movies_query = """ ... SELECT CONCAT(title, " (", release_year, ")"), ... collection_in_mil ... FROM movies ... ORDER BY collection_in_mil DESC ... LIMIT 5 ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchall(): ... print(movie) ... ('Avengers: Endgame (2019)', Decimal('858.8')) ('Titanic (1997)', Decimal('659.2')) ('The Dark Knight (2008)', Decimal('535.4')) ('Toy Story 4 (2019)', Decimal('434.9')) ('The Lion King (1994)', Decimal('423.6'))

如果您不想使用该LIMIT子句并且不需要获取所有记录，则该cursor对象也具有.fetchone()和.fetchmany()方法：

.fetchone()检索结果的下一行，作为元组，或者None如果没有更多行可用。

.fetchmany()从结果中检索下一组行作为元组列表。它有一个size参数，默认为1，您可以使用它来指定需要获取的行数。如果没有更多行可用，则该方法返回一个空列表。

尝试再次检索与发行年份串联的五部票房最高的电影的片名，但这次使用.fetchmany()：

>>>

>>> select_movies_query = """ ... SELECT CONCAT(title, " (", release_year, ")"), ... collection_in_mil ... FROM movies ... ORDER BY collection_in_mil DESC ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchmany(size=5): ... print(movie) ... cursor.fetchall() ... ('Avengers: Endgame (2019)', Decimal('858.8')) ('Titanic (1997)', Decimal('659.2')) ('The Dark Knight (2008)', Decimal('535.4')) ('Toy Story 4 (2019)', Decimal('434.9')) ('The Lion King (1994)', Decimal('423.6'))

with 的输出.fetchmany()类似于您在使用该LIMIT子句时收到的输出。您可能已经注意到最后的附加cursor.fetchall()调用。您这样做是为了清除所有未读取的剩余结果.fetchmany()。

在同一连接上执行任何其他语句之前，有必要清除所有未读结果。否则，InternalError: Unread result found将引发异常。

使用JOIN语句处理多个表

如果您发现上一节中的查询非常简单，请不要担心。您可以SELECT使用上一节中的相同方法使查询尽可能复杂。

让我们看一些稍微复杂的JOIN查询。如果您想找出数据库中评分最高的前五部电影的名称，则可以运行以下查询：

>>>

>>> select_movies_query = """ ... SELECT title, AVG(rating) as average_rating ... FROM ratings ... INNER JOIN movies ... ON movies.id = ratings.movie_id ... GROUP BY movie_id ... ORDER BY average_rating DESC ... LIMIT 5 ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchall(): ... print(movie) ... ('Night of the Living Dead', Decimal('9.90000')) ('The Godfather', Decimal('9.90000')) ('Avengers: Endgame', Decimal('9.75000')) ('Eternal Sunshine of the Spotless Mind', Decimal('8.90000')) ('Beasts of No Nation', Decimal('8.70000'))

如上所示，《活死人之夜》和《教父》并列为您online_movie_rating数据库中评分最高的电影。

要查找给出最多评分的评论者的姓名，请编写以下查询：

>>>

>>> select_movies_query = """ ... SELECT CONCAT(first_name, " ", last_name), COUNT(*) as num ... FROM reviewers ... INNER JOIN ratings ... ON reviewers.id = ratings.reviewer_id ... GROUP BY reviewer_id ... ORDER BY num DESC ... LIMIT 1 ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchall(): ... print(movie) ... ('Mary Cooper', 4)

Mary Cooper是这个数据库中最频繁的评论者。如上所示，查询有多复杂并不重要，因为它最终由 MySQL 服务器处理。您执行查询的过程将始终保持不变：将查询传递给cursor.execute()并使用 获取结果.fetchall()。

从数据库更新和删除记录

在本节中，您将更新和删除数据库中的记录。这两种操作都可以对表中的单个记录或多个记录执行。您将使用WHERE子句选择需要修改的行。

UPDATE 命令

您数据库中的一位审阅者Amy Farah Fowler，现在与Sheldon Cooper. 她的姓氏现在已更改为Cooper，因此您需要相应地更新您的数据库。对于更新记录，MySQL 使用以下UPDATE语句：

update_query = """ UPDATE reviewers SET last_name = "Cooper" WHERE first_name = "Amy" """ with connection.cursor() as cursor: cursor.execute(update_query) connection.commit()

代码将更新查询传递给cursor.execute()，并将.commit()所需的更改带到reviewers表中。

注意：在UPDATE查询中，WHERE子句帮助指定需要更新的记录。如果你不使用WHERE，那么所有的记录都会被更新！

假设您需要提供一个选项，允许审阅者修改评级。审阅者将提供三个值movie_id、reviewer_id、 和新的rating. 代码将在执行指定的修改后显示记录。

假设movie_id = 18，reviewer_id = 15和新的rating = 5.0，你可以使用下面的MySQL查询执行所需的修改：

UPDATE ratings SET rating = 5.0 WHERE movie_id = 18 AND reviewer_id = 15; SELECT * FROM ratings WHERE movie_id = 18 AND reviewer_id = 15;

上述查询首先更新评级，然后显示它。您可以创建一个完整的 Python 脚本来建立与数据库的连接并允许审阅者修改评级：

from getpass import getpass from mysql.connector import connect, Error movie_id = input("Enter movie id: ") reviewer_id = input("Enter reviewer id: ") new_rating = input("Enter new rating: ") update_query = """ UPDATE ratings SET rating = "%s" WHERE movie_id = "%s" AND reviewer_id = "%s"; SELECT * FROM ratings WHERE movie_id = "%s" AND reviewer_id = "%s" """ % ( new_rating, movie_id, reviewer_id, movie_id, reviewer_id, ) try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), database="online_movie_rating", ) as connection: with connection.cursor() as cursor: for result in cursor.execute(update_query, multi=True): if result.with_rows: print(result.fetchall()) connection.commit() except Error as e: print(e)

将此代码保存到名为modify_ratings.py. 上面的代码使用%s占位符将接收到的输入插入到update_query字符串中。在本教程中，您第一次在单个字符串中有多个查询。要将多个查询传递给单个cursor.execute()，您需要将该方法的multi参数设置为True。

如果multi是True，则cursor.execute()返回一个迭代器。迭代器中的每一项都对应一个cursor对象，该对象执行查询中传递的语句。上面的代码for在这个迭代器上运行一个循环，然后调用.fetchall()每个cursor对象。

注意：.fetchall()在所有游标对象上运行很重要。要在同一个连接上执行新语句，您必须确保没有来自先前执行的未读结果。如果有未读结果，那么您将收到异常。

如果操作中未获取任何结果集，则.fetchall()引发异常。为避免此错误，在上面的代码中使用了cursor.with_rows属性，该属性指示最近执行的操作是否产生了行。

虽然此代码应该可以解决您的目的，但该WHERE条款是当前状态下网络黑客的主要目标。它容易受到所谓的SQL 注入攻击，这可能允许恶意行为者破坏或滥用您的数据库。

警告：不要在您的数据库上尝试以下输入！它们会损坏您的表，您需要重新创建它。

例如，如果用户发送movie_id=18、reviewer_id=15和 newrating=5.0作为输入，则输出如下所示：

$ python modify_ratings.py Enter movie id: 18 Enter reviewer id: 15 Enter new rating: 5.0 Enter username: Enter password: [(18, 15, Decimal('5.0'))]

在rating与movie_id=18和reviewer_id=15已更改为5.0。但是如果你是黑客，那么你可能会在你的输入中发送一个隐藏的命令：

$ python modify_ratings.py Enter movie id: 18 Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A Enter new rating: 5.0 Enter username: Enter password: [(18, 15, Decimal('5.0'))]

同样，输出显示指定rating已更改为5.0。有什么变化？

黑客在进入reviewer_id. 更新查询将表中所有记录的update reviewers set last_name = "A更改为。如果您打印出表格，您可以看到此更改：last_namereviewers"A"reviewers

>>>

>>> select_query = """ ... SELECT first_name, last_name ... FROM reviewers ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_query) ... for reviewer in cursor.fetchall(): ... print(reviewer) ... ('Chaitanya', 'A') ('Mary', 'A') ('John', 'A') ('Thomas', 'A') ('Penny', 'A') ('Mitchell', 'A') ('Wyatt', 'A') ('Andre', 'A') ('Sheldon', 'A') ('Kimbra', 'A') ('Kat', 'A') ('Bruce', 'A') ('Domingo', 'A') ('Rajesh', 'A') ('Ben', 'A') ('Mahinder', 'A') ('Akbar', 'A') ('Howard', 'A') ('Pinkie', 'A') ('Gurkaran', 'A') ('Amy', 'A') ('Marlon', 'A')

上面的代码显示了表中所有记录的first_name和。SQL 注入攻击通过将所有记录的 更改为来破坏此表。last_namereviewerslast_name"A"

有一个快速修复可以防止此类攻击。不要将用户提供的查询值直接添加到您的查询字符串中。相反，更新modify_ratings.py脚本以将这些查询值作为参数发送到.execute()：

from getpass import getpass from mysql.connector import connect, Error movie_id = input("Enter movie id: ") reviewer_id = input("Enter reviewer id: ") new_rating = input("Enter new rating: ") update_query = """ UPDATE ratings SET rating = %s WHERE movie_id = %s AND reviewer_id = %s; SELECT * FROM ratings WHERE movie_id = %s AND reviewer_id = %s """ val_tuple = ( new_rating, movie_id, reviewer_id, movie_id, reviewer_id, ) try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), database="online_movie_rating", ) as connection: with connection.cursor() as cursor: for result in cursor.execute(update_query, val_tuple, multi=True): if result.with_rows: print(result.fetchall()) connection.commit() except Error as e: print(e)

请注意，%s占位符不再位于字符串引号中。传递给占位符的字符串可能包含一些特殊字符。如有必要，这些可以由底层库正确转义。

cursor.execute()确保作为参数接收的元组中的值是所需的数据类型。如果用户试图偷偷输入一些有问题的字符，那么代码将引发异常：

$ python modify_ratings.py Enter movie id: 18 Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A Enter new rating: 5.0 Enter username: Enter password: 1292 (22007): Truncated incorrect DOUBLE value: '15"; UPDATE reviewers SET last_name = "A'

cursor.execute()如果在用户输入中发现任何不需要的字符，则会引发异常。每当您将用户输入合并到查询中时，您都应该使用这种方法。还有其他方法可以防止 SQL 注入攻击。

DELETE 命令

删除记录的工作方式与更新记录非常相似。您使用该DELETE语句删除选定的记录。

注意：删除是一个不可逆的过程。如果不使用该WHERE子句，则指定表中的所有记录都将被删除。您需要INSERT INTO再次运行查询以取回已删除的记录。

建议您首先SELECT使用相同的过滤器运行查询，以确保您删除的是正确的记录。例如，要删除由 给出的所有评分reviewer_id = 2，您应该首先运行相应的SELECT查询：

>>>

>>> select_movies_query = """ ... SELECT reviewer_id, movie_id FROM ratings ... WHERE reviewer_id = 2 ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchall(): ... print(movie) ... (2, 7) (2, 8) (2, 12) (2, 23)

上面的代码片段输出表中的reviewer_id和movie_id记录，ratings其中reviewer_id = 2. 确认这些是您需要删除的记录后，您可以DELETE使用相同的过滤器运行查询：

delete_query = "DELETE FROM ratings WHERE reviewer_id = 2" with connection.cursor() as cursor: cursor.execute(delete_query) connection.commit()

使用此查询，您可以reviewer_id = 2从ratings表中删除审阅者给出的所有评分。

连接 Python 和 MySQL 的其他方法

在本教程中，您看到了 MySQL 连接器/Python，这是官方推荐的从 Python 应用程序与 MySQL 数据库交互的方法。还有另外两种流行的连接器：

mysqlclient是一个库，它是官方连接器的竞争对手，并积极更新新功能。因为它的核心是用C语言编写的，所以性能比纯Python官方连接器更好。一个很大的缺点是设置和安装相当困难，尤其是在 Windows 上。

MySQLdb是一种遗留软件，仍在商业应用程序中使用。它是用 C 编写的，比 MySQL Connector/Python 更快，但仅适用于 Python 2。

这些连接器充当您的程序和 MySQL 数据库之间的接口，您可以通过它们发送 SQL 查询。但是许多开发人员更喜欢使用面向对象的范式而不是 SQL 查询来操作数据。

对象关系映射(ORM) 是一种技术，允许您使用面向对象的语言直接查询和操作数据库中的数据。ORM 库封装了操作数据所需的代码，从而无需使用哪怕是一点点 SQL。以下是基于 SQL 的数据库最流行的 Python ORM：

SQLAlchemy是一种 ORM，可促进 Python 和其他 SQL 数据库之间的通信。您可以为不同的数据库（如 MySQL、PostgreSQL、SQLite 等）创建不同的引擎。SQLAlchemy 通常与 Pandas 库一起使用，以提供完整的数据处理功能。

peewee是一个轻量级、快速的 ORM，可以快速设置。当您与数据库的交互仅限于提取一些记录时，这非常有用。例如，如果您需要将 MySQL 数据库中的选定记录复制到 CSV 文件中，那么 peewee 可能是您的最佳选择。

Django的ORM是最强大的功能之一的Django，并沿着Django的web框架提供。它可以与多种数据库交互，例如 SQLite、PostgreSQL 和 MySQL。许多基于 Django 的应用程序使用 Django ORM 进行数据建模和基本查询，但通常会切换到 SQLAlchemy来满足更复杂的需求。

您可能会发现其中一种方法更适合您的应用程序。如果您不确定要使用哪一个，那么最好使用官方推荐的 MySQL 连接器/Python，您在本教程中看到了它的实际应用。

结论

在本教程中，您了解了如何使用 MySQL Connector/Python 将 MySQL 数据库与 Python 应用程序集成。您还看到了 MySQL 数据库的一些独特功能，这些功能将其与其他 SQL 数据库区分开来。

在此过程中，您学习了一些在建立连接、创建表以及在数据库应用程序中插入和更新记录时值得考虑的编程最佳实践。您还为在线电影评级系统开发了一个示例 MySQL 数据库，并直接从您的 Python 应用程序与其交互。

在本教程中，您学习了如何：

将您的 Python 应用程序与MySQL 数据库连接

将 MySQL 数据库中的数据导入Python进行进一步分析

从 Python 应用程序执行SQL 查询

访问数据库时处理异常

防止对您的应用程序的SQL 注入攻击

MySQL Python 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。