Python操作MySQL数据库 示例

# pip install pymysql
import pymysql

# 打开数据库连接，参数1：主机名或IP；参数2：用户名；参数3：密码；参数4：数据库名
db = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='mysql')

# 使用cursor()创建一个cursor对象
cursor = db.cursor()

# 使用execute()方法执行SQL查询
cursor.execute("SELECT VERSION()")

# 使用fetchone()方法获取单条数据
data = cursor.fetchone()
print("Database version: %s" % data)

# 关闭数据库
db.close()

目录

• 将MySQL与其他 SQL 数据库进行比较
• 安装MySQL服务器和MySQL连接器/Python
  • 安装MySQL服务器
  • 安装 MySQL 连接器/Python
• 与MySQL服务器建立连接
  • 建立连接
  • 创建新数据库
  • 连接到现有数据库
• 创建、更改和删除表
  • 定义数据库架构
  • 使用CREATE TABLE语句创建表
  • 使用 DESCRIBE 语句显示表架构
  • 使用 ALTER 语句修改表架构
  • 使用 DROP 语句删除表
• 在表中插入记录
  • 使用 .execute（）
  • 使用 .executemany（）
• 从数据库中读取记录
  • 使用 SELECT 语句读取记录
  • 使用 WHERE 子句筛选结果
• 使用 JOIN 语句处理多个表
• 更新和删除数据库中的记录
  • 更新命令
  • 删除命令
• 连接Python和MySQL的其他方法
• 结论

MySQL是当今市场上最受欢迎的数据库管理系统（DBMS）之一。它在今年的数据库引擎排名中仅次于甲骨文DBMS排名第二。由于大多数软件应用程序需要以某种形式与数据进行交互，因此像Python这样的编程语言提供了用于存储和访问这些数据源的工具。

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

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

• MySQL的独特功能
• 将应用程序连接到 MySQL 数据库
• 查询数据库以获取所需数据
• 处理访问数据库时发生的异常
• 在构建数据库应用程序时使用最佳实践

要充分利用本教程，您应该具备Python概念的工作知识，例如循环，函数，异常处理以及使用pip安装Python包。您还应该对关系数据库管理系统和 SQL 查询（如 、 、 和 ）有基本的了解。SELECT DROP CREATE JOIN

将MySQL与其他 SQL 数据库进行比较

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

自1995年成立以来，MySQL一直是开源的，它迅速成为SQL解决方案的市场领导者。MySQL也是甲骨文生态系统的一部分。虽然它的核心功能是完全免费的，但也有一些付费的附加组件。目前，几乎所有主要科技公司都在使用MySQL，包括谷歌，LinkedIn，Uber，Netflix，推特等。

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

1. 易于安装：MySQL被设计为用户友好的。设置MySQL数据库非常简单，并且几个广泛使用的第三方工具（如phpMyAdmin）进一步简化了设置过程。MySQL 可用于所有主要操作系统，包括 Windows、macOS、Linux 和 Solaris。
2. 速度：MySQL以速度极快的数据库解决方案而闻名。它具有相对较小的占用空间，并且从长远来看具有极高的可扩展性。
3. 用户权限和安全性：MySQL附带一个脚本，允许您设置密码安全级别，分配管理员密码以及添加和删除用户帐户权限。此脚本使 Web 托管用户管理门户的管理过程变得简单。其他 DBMS（如 PostgreSQL）使用更易于使用的配置文件。

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

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

注意：有关在实际环境中对 MySQL 和后greSQL 进行更深入的比较，请查看为什么Uber Engineering从Postgres切换到 MySQL。

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

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

由MySQL的原始作者迈克尔·威德纽斯领导的几位开发人员创建了MySQL代码库的分支，并奠定了MariaDB的基础。目的是保护对MySQL的访问并使其永远免费。

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

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

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

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

安装MySQL管理服务器

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

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

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

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

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

安装 MySQL 连接器/Python

数据库驱动程序是一种软件，它允许应用程序连接数据库系统并与之交互。像Python这样的编程语言需要一个特殊的驱动程序，然后才能与来自特定供应商的数据库进行通信。

这些驱动程序通常作为第三方模块获取。Python数据库 API （DB-API） 定义了所有 Python 数据库驱动程序必须遵守的标准接口。这些详细信息记录在 PEP 249 中。所有 Python 数据库驱动程序（如用于 SQLite 的 sqlite3、用于后绿色SQL 的 psycopg 和用于 MySQL 的 MySQL 连接器/Python）都遵循这些实现规则。

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

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

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

同样，在Python中，您需要安装一个Python MySQL连接器才能与MySQL数据库进行交互。许多软件包都遵循数据库 API 标准，但其中最受欢迎的是 MySQL 连接器/Python。你可以用pip得到它：

$ pip install mysql-connector-python

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

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

>>> import mysql.connector

如果上述代码执行时没有错误，则已安装并可供使用。如果您遇到任何错误，请确保您处于正确的虚拟环境中，并且您使用的是正确的Python解释器。mysql.connector

确保安装正确的 mysql 连接器-python 包，这是一个纯 Python 实现。当心名称相似但现在已折旧的连接器，如 mysql 连接器。

与MySQL服务器建立连接

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

1. 连接到MySQL服务器。
2. 创建新数据库。
3. 连接到新创建的数据库或现有数据库。
4. 执行 SQL 查询并获取结果。
5. 如果对表进行了任何更改，请通知数据库。
6. 关闭与 MySQL 服务器的连接。

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

建立连接

与MySQL服务器交互的第一步是建立连接。为此，您需要从模块中connect()。此函数采用像 、 和 这样的参数，并返回一个 MySQLConnection对象。您可以从用户接收这些凭据作为输入，并将它们传递给：mysql.connector host user password 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服务器建立连接。作为回报，您将获得一个对象，该对象存储在变量中。从现在开始，您将使用此变量来访问MySQLserver.MySQLConnection connection

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

• 您应该始终处理在建立与MySQL服务器的连接时可能引发的异常。这就是为什么你使用一个try…except阻止 以捕获并打印可能遇到的任何异常。
• 访问完数据库后，应始终关闭连接。保留未使用的打开连接可能会导致一些意外错误和性能问题。上面的代码利用了使用 with 的上下文管理器，它抽象出连接清理过程。
• 切勿直接在 Python 脚本中对登录凭据（即用户名和密码）进行硬编码。这是部署的不良做法，并构成严重的安全威胁。上面的代码提示用户输入登录凭据。它使用内置的 getpass 模块来隐藏密码。虽然这比硬编码更好，但还有其他更安全的方法来存储敏感信息，例如使用环境变量。

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

创建新数据库

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

CREATE DATABASE books_db;

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

注意：在MySQL中，必须在语句末尾放置分号（），这表示查询的终止。但是，MySQL连接器/ Python会自动在查询结束时附加一个分号，因此无需在Python代码中使用它。;

要在 Python 中执行 SQL 查询，您需要使用游标cursor，该游标会抽象出对数据库记录的访问。MySQL连接器/Python 为您提供了 MySQLCursor类，该类实例化了可以在Python中执行MySQL查询的对象。该类的实例也称为 MySQLCursorcursor

cursor对象利用对象与MySQL服务器进行交互。要创建 ，请使用变量的方法：MySQLConnectioncursor.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)

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

查询作为字符串存储在变量中，然后传递给 执行。该代码使用带有对象的上下文管理器来处理清理process.CREATE DATABASE create_db_querycursor.execute() cursor

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

>>> 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服务器中的所有数据库的名称。该命令还会输出一些未在服务器中创建的数据库，如information_schema、performance_schema等。这些数据库由MySQL服务器自动生成，并提供对各种数据库元数据和MySQL服务器设置的访问。SHOW DATABASES

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

连接到现有数据库

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

您可以使用之前使用的相同函数来执行此操作，方法是发送名为 ：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)

上面的代码与您之前使用的连接脚本非常相似。此处唯一的更改是附加参数，其中数据库的名称将传递给 。执行此脚本后，您将连接到数据库。databaseconnect() online_movie_rating

创建、更改和删除表

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

定义数据库架构

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

1. 电影包含有关电影的一般信息，并具有以下属性：
   • id
   • title
   • release_year
   • genre
   • collection_in_mil
2. 评论者包含有关发布评论或评分的用户的信息，并具有以下属性：
   • id
   • first_name
   • last_name
3. 评级包含有关已发布的评级的信息，并具有以下属性：
   • movie_id（外键）
   • reviewer_id（外键）
   • rating

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

下图描述了数据库架构：

此数据库中的表彼此相关。 并将具有多对多关系，因为一部电影可以由多个评论者审阅，一个审阅者可以审阅多个电影。该表将表与表连接起来。table.movies reviewer sratings movies reviewers

使用语句创建表CREATE TABLE

现在，要在MySQL中创建新表，您需要使用创建表语句。以下 MySQL 查询将为您的数据库创建表：moviesonline_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有各种各样的数据类型供您阅读，包括，，等。此外，当列值必须在插入新记录时自动递增时，MySQL使用关键字。YEARINTBIGINTAUTO_INCREMENT

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

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()

现在，您的数据库中有该表。传递给 执行所需执行的 。moviescreate_movies_table_querycursor.execute()

注意：该变量引用连接到数据库时返回的对象。connectionMySQLConnection

另外，请注意代码末尾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中，外键约束中的父级和子级必须使用相同的存储引擎。

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

1. 事务存储引擎是事务安全的，允许您使用简单的命令（如rollback）回滚事务。许多流行的MySQL引擎，包括InnoDB和NDB，都属于这一类。
2. 非事务性存储引擎依赖于精心设计的手动代码来撤消在数据库上提交的语句。MyISAM, MEMORY,和许多其他MySQL引擎都是非事务性的。

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

另请注意，该表使用列和 ，这两个外键共同用作主键。此步骤可确保审阅者不能对同一影片进行两次评分。ratingsmovie_idreviewer_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()

上面的代码将首先执行所有三个语句。然后，它将向提交事务的MySQL服务器发送命令。您还可以使用 .rollback（） 向 MySQL 服务器发送命令，并从事务中删除所有数据更改。CREATECOMMITROLLBACK

使用StatementDESCRIBE语句显示表架构

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

DESCRIBE <table_name>;

要从对象中获取一些结果，您需要使用cursor.fetchall().。此方法从上次执行的语句中获取所有行。假设变量中已有该对象，则可以打印出由 以下人员获取的所有结果：cursor MySQLConnection connectioncursor.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

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

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

DECIMAL(4,1)表示可以具有最大数字的十进制数，其中是十进制数，如 、 、 等。执行 ALTER TABLE 语句后，可以使用 显示更新的表架构：41120.13.438.0DESCRIBE

>>> 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_milDECIMAL(4,1)cursor.execute()cursor.fetchall()show_table_query

使用语句删除表DROP

要删除表，您需要在MySQL中执行 DROP TABLE statement。删除表是一个不可逆的过程。如果执行下面的代码，则需要再次调用查询才能使用后续部分中的表。CREATE TABLE ratings

要删除该表，请发送到：ratings drop_table_querycursor.execute()

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

如果执行上述代码，则将成功删除该表。ratings

在表中插入记录

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

第一种方法 在记录数较少且记录可以硬编码时效果很好。第二种方法 更受欢迎，更适合实际方案。.execute().executemany()

用.execute()

第一种方法使用与到目前为止一直使用的方法相同的方法。将 INSERT INTO query 写入字符串中，并将其传递给 。可以使用此方法将数据插入到表中。cursor.execute() cursor.execute() movies

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

1. id
2. title
3. release_year
4. genre
5. 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()

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

用.executemany()

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

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

1. 包含需要插入的记录的占位符的查询
2. 包含要插入的所有记录的列表

下面的示例为该表插入记录：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

该代码用作必须插入到 中的两个字符串的占位符。占位符充当格式说明符，并帮助为字符串中的变量保留一个位置。然后，指定的变量将在执行期间添加到此点。%sinsert_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

要检索记录，需要将查询发送到 。然后，使用以行或记录列表的形式提取检索到的表。SELECTcursor.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中，子句采用一个或两个非负数字参数。使用一个参数时，可以指定要返回的最大行数。由于您的查询包含 ，因此只提取第一条记录。使用这两个参数时，还可以指定要返回的第一行的偏移量：LIMITLIMIT 55

SELECT * FROM movies LIMIT 2,5;

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

您还可以查询所选列：

>>> 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)

现在，代码仅从两个指定的列中输出值：和 .titlerelease_year

使用子句筛选结果WHERE

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

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'))

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

• .fetchone（） 检索结果的下一行（作为元组），如果没有更多行可用，则检索“None”。
• .fetchmany（） 从结果中以元组列表的形式检索下一组行。它有一个参数，默认为 ，可用于指定需要读取的行数。如果没有更多行可用，则该方法返回一个空列表。size1

尝试再次检索与发行年份相关的五部票房最高的电影的标题，但这次使用：.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'))

输出 与 使用子句时收到的输出类似。您可能已经注意到最后的额外呼叫。执行此操作是为了清除所有未被 读取的剩余结果。.fetchmany() LIMITcursor.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 命令

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

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

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

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

假设 、 和 new ，您可以使用以下 MySQL 查询来执行所需的修改：movie_id = 18reviewer_id = 15rating = 5.0

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)

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

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

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

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

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

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

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

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

的 和 已更改为 。但是，如果您是黑客，那么您可能会在输入中发送一个隐藏的命令：ratingmovie_id=18reviewer_id=155.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: <user_name>
Enter password:
[(18, 15, Decimal('5.0'))]

同样，输出显示指定的 已更改为 。更改了哪些内容？rating5.0

黑客在输入 .更新查询 将表中所有记录的 更改为 。如果您打印出表格，则可以看到此更改：reviewer_idupdate reviewers set last_name = "Alast_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')

上面的代码显示表中所有记录的 和。SQL 注入攻击通过将 所有记录更改为 来损坏此表。first_namelast_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: <user_name>
Enter password:
1292 (22007): Truncated incorrect DOUBLE value: '15";
UPDATE reviewers SET last_name = "A'

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

DELETE命令

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

注意：删除是一个不可逆转的过程。如果不使用该子句，则将删除指定表中的所有记录。您需要再次运行查询才能取回已删除的记录。WHEREINSERT 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 数据库交互的方法。还有另外两种流行的连接器：

1. mysqlclient 是一个库，是官方连接器的紧密竞争对手，并且正在积极更新新功能。因为它的核心是用C语言编写的，所以它的性能比纯Python官方连接器更好。一个很大的缺点是设置和安装相当困难，尤其是在Windows上。
2. MySQLdb 是一款仍在商业应用程序中使用的旧版软件。它是用C语言编写的，比MySQL连接器/ Python更快，但仅适用于Python 2。

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

对象关系映射 （ORM） 是一种允许您使用面向对象的语言直接查询和操作数据库中的数据的技术。ORM库封装了操作数据所需的代码，这甚至消除了使用一点点SQL的需要。以下是用于基于 SQL 的数据库的最流行的Python ORMs：

1. SQL算法是一种ORM，用于促进Python与其他SQL数据库之间的通信。您可以为不同的数据库（如 MySQL、后浏览器、SQL 等）创建不同的引擎。SQLAlchemy通常与pandas库一起使用，以提供完整的数据处理功能。
2. peewee是一种轻量级且快速的ORM，可以快速设置。当您与数据库的交互仅限于提取一些记录时，这非常有用。例如，如果您需要将MySQL数据库中的选定记录复制到CSV文件中，那么peewee可能是您的最佳选择。
3. Django ORM是Django最强大的功能之一，与Django Web框架一起提供。它可以与各种数据库进行交互，例如SQLite，后格雷SQL和MySQL。许多基于 Django 的应用程序使用 Django ORM 进行数据建模和基本查询，但通常切换到 SQLAlchemy 以满足更复杂的要求。

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

结论

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

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

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

• 将你的Python应用与 MySQL 数据库连接起来
• 将数据从 MySQL 数据库引入Python以进行进一步分析
• 从您的Python应用程序执行 SQL 查询
• 在访问数据库时处理异常
• 防止对应用程序的 SQL 注入攻击

如果您有兴趣，Python也有用于其他DBMS的连接器，如MongoDB和PostgreSQL。有关详细信息，请查看 Python 数据库教程。