Python系列：教你使用PyMySQL操作MySQL数据库

【介绍】：本文教你如何基于Python读写MySQL数据库。

PyMySQL是一个用于Python语言连接和操作MySQL数据库的库。它提供了一种简单而强大的方式来与MySQL数据库进行交互，允许开发者执行各种数据库操作，如建立连接、执行查询、插入数据等。

可以使用pip命令安装PyMySQL库：

pip install PyMySQL

要使用PyMySQL连接MySQL数据库，需要提供数据库的主机名、端口号、用户名、密码以及数据库名称等信息。可以将这些信息存储在一个字典中，如：

config = {'host': 'localhost','port': 3306,'user': 'username', # 你的用户名'password': 'password', # 你的密码'database': 'database_name' # 你的数据库名
}

在PyMySQL中，游标（Cursor）是一个非常重要的概念，它用于执行查询和获取结果。游标允许逐行处理查询结果，从而可以控制从数据库检索的数据量和顺序。

执行SQL语句：游标对象用来执行SQL语句，如查询、插入、更新和删除。

获取结果：执行查询后，游标可以用来逐行或批量获取结果数据。

参数化查询：游标支持使用参数化查询，这有助于防止SQL注入攻击。

事务控制：通过游标执行的操作可以被提交或回滚，这对于维护数据的一致性非常重要。

从数据库连接对象中创建游标对象的基本方法如下：

cursor = connection.cursor()

此外，PyMySQL允许指定游标的类型，例如，使用DictCursor，查询结果将以字典形式返回，其中列名是字典的键：

cursor = connection.cursor(pymysql.cursors.DictCursor)

这种方式使得处理结果更加直观和方便，因为可以通过列名访问数据，而不是通过列的索引位置。

创建游标对象后，可以使用它来执行SQL命令。例如，执行一个查询并获取所有结果：

cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:print(row)

在这个例子中，execute方法用于执行SQL查询，fetchall方法用于获取所有行的数据。

从连接对象中创建一个游标对象，用于执行SQL查询：

cursor = connection.cursor()

上面介绍了，在PyMySQL中，执行SQL查询是通过游标对象（Cursor）完成的。这包括SELECT查询来获取数据，以及INSERT、UPDATE和DELETE查询来修改数据。接下来具体介绍如何使用PyMySQL执行这些操作。

要执行SELECT查询，首先需要编写SQL查询语句。然后，使用游标对象的execute()方法来执行这个查询。查询结果可以通过游标提供的方法如fetchall()或fetchone()来获取。
步骤：

1. 编写查询语句：确定你需要执行的SELECT语句。
2. 执行查询：使用游标的execute()方法执行SQL语句。
3. 获取结果：使用fetchall()获取所有结果，或使用fetchone()获取单个结果。

query = "SELECT * FROM users"
cursor.execute(query)
results = cursor.fetchall()
for row in results:print(row)

在这个例子中，execute()方法执行了SQL查询，fetchall()方法获取了所有的查询结果，并且结果通过一个循环打印出来。

对于INSERT、UPDATE和DELETE操作，步骤类似于执行SELECT查询，但通常需要在执行这些操作后提交更改，以确保更改被保存到数据库中。步骤：

1. 编写SQL语句：根据需要修改数据的类型编写相应的INSERT、UPDATE或DELETE语句。
2. 执行查询：使用游标的execute()方法执行SQL语句。对于INSERT和UPDATE操作，可以传递参数来避免SQL注入。
3. 提交更改：使用连接对象的commit()方法提交更改到数据库。

query = "INSERT INTO users (name, age) VALUES (%s, %s)"
values = ('John', 25)cursor.execute(query, values)
connection.commit()

在这个例子中，execute()方法不仅执行了INSERT查询，还通过传递参数的方式提供了要插入的数据，这有助于防止SQL注入。执行后，使用commit()方法确保更改被保存。

可以使用for循环遍历fetchall()返回的查询结果：

for row in results:print(row)

可以使用索引或字段名访问查询结果中的特定字段：

name = row[0] # 使用索引age = row['age'] # 使用字段名

在完成数据库操作后，需要关闭游标和连接对象以释放资源：

cursor.close()
connection.close()

可以使用with语句自动管理连接和游标的关闭：

with pymysql.connect(**config) as connection:with connection.cursor() as cursor:# 执行查询操作pass

现在假设你安装好了MySQL、Python3环境，并安装了PyMySQL库。由于PyMySQL库并不会自动创建连接的数据库，需要先登录你的MySQL中手动创建一个数据库。
假设你的root用户的密码为：123456，登录你的MySQL交互环境：

mysql -h localhost -P 3306 -u root -p

在显示Enter password:输入123456（假设为你的密码），回车。其中h localhost可以依据你的MySQL主机地址修改，若为localhost，可以不指定h参数。

现在我们执行SQL创建一个名为mydatabase的数据库：

CREATE DATABASE mydatabase;

然后执行quit命令退出。

PyMySQL依赖于cryptography包的C扩展。如果在后续运行中提示缺少，可以使用下面的命令进行安装：

pip install cryptography 

这里的cryptography是一个用于加密和解密的Python库，提供了多种加密算法和工具，帮助开发者实现安全通信和数据保护。它支持对称加密、非对称加密、哈希函数、数字签名等多种加密技术。

接下来编写我们的脚本：

import pymysql# 数据库配置
config = {'host': 'localhost','port': 3306,'user': 'root','password': '123456','database': 'mydatabase'
}# 建立数据库连接
connection = pymysql.connect(**config)try:with connection.cursor() as cursor:# 创建表create_table_query = """CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255),age INT)"""cursor.execute(create_table_query)connection.commit()# 插入数据insert_query = "INSERT INTO users (name, age) VALUES (%s, %s)"users_data = [('John', 25),('Alice', 30),('Bob', 35)]cursor.executemany(insert_query, users_data)connection.commit()# 查询数据select_query = "SELECT * FROM users"cursor.execute(select_query)results = cursor.fetchall()print("查询结果:")for row in results:print(row)# 更新数据update_query = "UPDATE users SET age = %s WHERE name = %s"cursor.execute(update_query, (28, 'John'))connection.commit()# 删除数据delete_query = "DELETE FROM users WHERE name = %s"cursor.execute(delete_query, ('Bob',))connection.commit()# 再次查询数据cursor.execute(select_query)results = cursor.fetchall()print("更新后的查询结果:")for row in results:print(row)finally:# 关闭连接connection.close()

这个脚本使用了Python的PyMySQL库来与MySQL数据库进行交互，具体执行了以下操作：

1. 建立数据库连接： 使用提供的配置（包括主机名、端口号、用户名、密码和数据库名）建立到MySQL数据库的连接；

3. 创建表： 在数据库中创建一个名为users的表，如果这个表已经存在，则不会重复创建。表中包含三个字段：id（自动增长的整数主键）、name（字符串类型，最大长度255）、age（整数类型）。

4. 插入数据： 向users表中插入三条数据记录，分别是John（25岁）、Alice（30岁）和Bob（35岁）。

5. 查询数据：从users表中查询所有记录，并打印出来。这是在插入数据后立即进行的，所以查询结果将显示所有刚插入的数据。

6. 更新数据： 更新名为John的用户的年龄，将其从25岁改为28岁。

7. 删除数据： 删除名为Bob的用户记录。

8. 再次查询数据： 在更新和删除操作之后，再次从users表中查询所有记录，并打印出来。这次查询的结果将反映出对John年龄的更新和Bob记录的删除。

9. 关闭数据库连接： 在所有操作完成后，脚本会关闭数据库连接，释放资源。

整个脚本展示了如何使用PyMySQL进行基本的数据库操作，包括连接数据库、执行SQL命令（创建表、插入、查询、更新、删除数据）以及最后关闭数据库连接。这些操作是数据库管理和数据处理中常见的任务。

运行后的控制套输出为：

查询结果:
(1, 'John', 25)
(2, 'Alice', 30)
(3, 'Bob', 35)
更新后的查询结果:
(1, 'John', 28)
(2, 'Alice', 30)