03 Python进阶：MySQL - mysql-connector

mysql-connector安装

要在 Python 中使用 MySQL 数据库，你需要安装 MySQL 官方提供的 MySQL Connector/Python。下面是安装 MySQL Connector/Python 的步骤：

1. 首先，确保你已经安装了 Python，如果没有安装，可以在 Python 官网（https://www.python.org）下载并安装最新版本的 Python。

2. 打开命令行或终端，在命令行中运行以下命令安装 MySQL Connector/Python：

这将会从 PyPI 上下载并安装 MySQL Connector/Python。

注意：如果你的 MySQL 是 8.0 版本，密码插件验证方式发生了变化，早期版本为 mysql_native_password，8.0 版本为 caching_sha2_password，所以需要做些改变：

先修改 my.ini 配置：

[mysqld]
default_authentication_plugin=mysql_native_password
然后在 mysql 下执行以下命令来修改密码：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
1
2
3
4
5
6
7
8
9

创建数据库连接

安装完成后，你就可以在 Python 脚本中使用 MySQL Connector/Python 来连接和操作 MySQL 数据库了。下面是一个简单的示例代码：

import mysql.connector

# 连接到 MySQL 数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# 创建游标对象
mycursor = mydb.cursor()

# 执行 SQL 查询
mycursor.execute("SELECT * FROM mytable")

# 获取查询结果
for x in mycursor:
  print(x)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

在上面的代码中，你需要将 host、user、password 和 database 替换为实际的数据库连接信息，然后根据需求执行相应的 SQL 查询。

这样，你就可以在 Python 中使用 MySQL Connector/Python 来连接、查询和操作 MySQL 数据库了。

创建数据库

在 Python 中，你可以使用 MySQL Connector/Python 来创建数据库。以下是一个简单的示例代码：

import mysql.connector

# 连接到 MySQL 服务器
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword"
)

# 创建一个名为 'mydatabase' 的数据库
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")
1
2
3
4
5
6
7
8
9
10
11
12

在上面的示例中，你需要将 host、user 和 password 替换为实际的数据库连接信息。运行这段代码后，将在 MySQL 服务器上创建一个名为 mydatabase 的数据库。

确保你的账户有足够的权限来创建数据库。另外，如果你想指定数据库的字符集和校对规则，可以在创建数据库时加上参数，例如：

mycursor.execute("CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
1

这将创建一个使用 UTF-8 字符集和 utf8mb4_unicode_ci 校对规则的数据库。

创建数据表

在 Python 中使用 MySQL Connector/Python 创建数据表的方法与在 MySQL 命令行中创建数据表的方法类似。以下是一个示例代码，演示如何在 Python 中创建一个名为 users 的数据表：

import mysql.connector

# 连接到 MySQL 数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"  # 已存在的数据库
)

# 创建一个数据表
mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)")
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在上面的示例中，我们连接到了已存在的数据库 mydatabase，然后使用 CREATE TABLE 语句创建了一个名为 users 的数据表，其中包含 id、name 和 age 三个字段。

你可以根据需要定义数据表的字段及其类型。运行这段代码后，将在数据库中创建一个名为 users 的数据表。

注意：确保你具有足够的权限来创建数据表。另外，你也可以为表中的列指定约束、默认值等其他属性，具体语法可以根据需要调整。

主键设置

在 MySQL 中，可以使用 PRIMARY KEY 来定义表中的主键。在 Python 中使用 MySQL Connector/Python，也可以通过以下方式为数据表设置主键：

# 创建一个数据表，并指定主键
mycursor.execute("CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)")
1
2

在上面的示例中，我们在 id 字段上使用了 AUTO_INCREMENT PRIMARY KEY，这意味着 id 是主键，并且会自动递增。

如果你想在多个字段上定义复合主键，可以使用如下方式：

# 创建一个数据表，并指定复合主键
mycursor.execute("CREATE TABLE users (id1 INT, id2 INT, PRIMARY KEY (id1, id2))")
1
2

在这个例子中，我们将 id1 和 id2 作为复合主键。无论是单个字段还是复合主键，都需要确保它们能唯一标识表中的每一行数据。

通过以上方法，你可以在 Python 中使用 MySQL Connector/Python 为数据表设置主键。如果需要更复杂的约束或索引，也可以通过相应的 SQL 语句进行设置。

插入数据

当使用 MySQL Connector/Python 向数据库中的数据表插入数据时，可以使用如下方法：

import mysql.connector

# 连接到 MySQL 数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# 创建一个游标对象
mycursor = mydb.cursor()

# 插入数据
sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
val = ("John", 30)
mycursor.execute(sql, val)

# 提交执行
mydb.commit()

print(mycursor.rowcount, "记录插入成功。")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

在上面的示例中，我们向名为 users 的数据表中插入了一条数据，这条数据包含了 name 和 age 两个字段的值。通过使用 %s 占位符和元组 val 传递数据，我们可以避免 SQL 注入攻击，并使代码更加安全。

在执行 execute() 后，我们调用了 commit() 方法来提交事务。只有在调用 commit() 后，数据才会真正被插入到数据库中。

以上就是使用 MySQL Connector/Python 向数据表插入数据的简单示例。你可以根据需要修改 val 中的数据以及 SQL 语句来插入更多的记录。

批量插入

如果需要批量插入多条数据到数据库中，可以使用 MySQL Connector/Python 的 executemany() 方法。以下是一个示例代码，演示如何批量插入多条数据：

import mysql.connector

# 连接到 MySQL 数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# 创建一个游标对象
mycursor = mydb.cursor()

# 插入多条数据
sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
values = [
    ("Alice", 25),
    ("Bob", 30),
    ("Charlie", 28)
]

mycursor.executemany(sql, values)

# 提交执行
mydb.commit()

print(mycursor.rowcount, "记录插入成功。")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

在上面的示例中，我们定义了一个包含多个元组的 values 列表，每个元组表示要插入的一条数据。然后，我们使用 executemany() 方法将这些数据批量插入到数据库中。

与单条插入相同，执行 commit() 语句来提交事务，确保数据被正确插入到数据库中。

通过这种方式，你可以方便地批量插入多条数据到数据库中。记得根据实际需求调整数据和 SQL 语句。

查询数据

无条件查询

要在 Python 中使用 MySQL Connector/Python 查询数据，你可以通过以下方式实现：

import mysql.connector

# 连接到 MySQL 数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# 创建一个游标对象
mycursor = mydb.cursor()

# 执行查询
mycursor.execute("SELECT * FROM users")

# 获取查询结果
result = mycursor.fetchall()
for row in result:
  print(row)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

在上面的示例中，我们首先连接到了数据库，然后创建了一个游标对象。游标对象允许我们执行 SQL 查询并获取结果。在这个例子中，我们执行了一个简单的 SELECT 查询来获取 users 表中的所有记录，并通过 fetchall() 方法获取查询结果。

最后，我们遍历查询结果，并打印出每一行数据。

条件查询

如果需要添加查询条件或者只获取部分字段的数据，可以根据需要修改 SQL 查询语句。
要在 Python 中进行带有条件的查询，只需在 SQL 查询语句中添加条件即可。以下是一个示例代码，演示如何使用 MySQL Connector/Python 进行条件查询：

import mysql.connector

# 连接到 MySQL 数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# 创建一个游标对象
mycursor = mydb.cursor()

# 执行带有条件的查询
sql = "SELECT * FROM users WHERE age > %s"
val = (25, )  # 查询年龄大于25的用户
mycursor.execute(sql, val)

# 获取查询结果
result = mycursor.fetchall()
for row in result:
  print(row)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

在上面的示例中，我们执行了一个带有条件的查询，通过 WHERE 子句筛选出了年龄大于25的用户。我们将条件值(这里是 25)作为元组传递给 execute() 方法，以避免 SQL 注入攻击。

你可以根据需要修改查询条件，例如更改比较运算符、添加多个条件等。通过在 SQL 查询语句中灵活添加条件，你可以实现不同类型的查询操作。

删除记录

要在 Python 中使用 MySQL Connector/Python 删除记录，你可以按照以下步骤进行操作：

import mysql.connector

# 连接到 MySQL 数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# 创建一个游标对象
mycursor = mydb.cursor()

# 执行删除操作
sql = "DELETE FROM users WHERE name = %s"
val = ("Alice",)  # 删除名为 Alice 的记录
mycursor.execute(sql, val)

# 提交执行
mydb.commit()

print(mycursor.rowcount, "条记录已被删除。")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

在上面的示例中，我们执行了一个删除操作，通过 DELETE FROM 语句删除了名为 “Alice” 的记录。类似于条件查询，我们将需要删除的记录的条件值作为元组传递给 execute() 方法。

在执行 delete 操作后，一定要调用 commit() 方法提交事务，确保删除操作生效。如果不调用 commit()，记录将不会被真正删除。

表记录清空

要在Python中使用MySQL Connector/Python清空表中的所有记录，你可以按照以下步骤进行操作：

import mysql.connector

# 连接到 MySQL 数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# 创建一个游标对象
mycursor = mydb.cursor()

# 执行清空表记录操作
sql = "TRUNCATE TABLE yourtablename"
mycursor.execute(sql)

print("表记录已成功清空")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

在上面的示例中，我们使用TRUNCATE TABLE语句清空了名为"yourtablename"的表中的所有记录。这条语句会立即删除表中的所有记录，但是保留表结构不变。请谨慎操作，因为一旦清空操作完成，将无法恢复数据。

请确保你明确了解清空表记录的后果，并在需要清空表记录时再执行该操作。

更新表数据

在 Python 中使用 MySQL Connector/Python 更新表数据的方法如下所示：

import mysql.connector

# 连接到 MySQL 数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# 创建一个游标对象
mycursor = mydb.cursor()

# 执行更新操作
sql = "UPDATE users SET age = %s WHERE name = %s"
val = (32, "Alice")  # 更新名为 Alice 的记录的年龄为 32
mycursor.execute(sql, val)

# 提交执行
mydb.commit()

print(mycursor.rowcount, "条记录已被更新。")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

在上面的示例中，我们执行了一个更新操作，通过 UPDATE 语句将名为 “Alice” 的记录的年龄更新为 32。我们将更新操作的条件值和新值作为元组传递给 execute() 方法。

和删除操作类似，需要调用 commit() 方法提交事务，确保更新操作生效。

删除表

要在Python中使用MySQL Connector/Python删除表，你可以按照以下步骤进行操作：

import mysql.connector

# 连接到 MySQL 数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# 创建一个游标对象
mycursor = mydb.cursor()

# 执行删除表操作
sql = "DROP TABLE IF EXISTS yourtablename"
mycursor.execute(sql)

print("表已成功删除")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

在上面的示例中，我们执行了一个删除表的操作，通过DROP TABLE语句删除了名为"yourtablename"的表。如果你确信表存在可以不添加IF EXISTS部分。

在执行DROP TABLE操作后，表将被立即删除，所以请谨慎操作。该操作无法撤销，并且会永久删除表及其数据，因此请在明确知道删除目的时再执行该操作。

数据库事务

使用 mysql-connector 库执行事务与使用 pymysql 类似，需要在 Python 中连接到 MySQL 数据库，并利用 connection 对象的方法来执行事务的提交和回滚。

以下是一个示例代码，演示了如何在 mysql-connector 中执行事务：

import mysql.connector

# 连接到数据库
connection = mysql.connector.connect(
    host='your_host',
    user='your_username',
    password='your_password',
    database='your_database_name'
)

try:
    # 开始事务
    connection.start_transaction()

    # 创建游标
    cursor = connection.cursor()

    # 执行多个 SQL 命令，这里仅作示例
    insert_sql_1 = "INSERT INTO table1 (column1, column2) VALUES (%s, %s)"
    insert_sql_2 = "INSERT INTO table2 (column3, column4) VALUES (%s, %s)"

    # 执行第一个插入操作
    cursor.execute(insert_sql_1, ('value1', 'value2'))

    # 执行第二个插入操作
    cursor.execute(insert_sql_2, ('value3', 'value4'))

    # 提交事务
    connection.commit()

except mysql.connector.Error as e:
    # 如果出现异常，回滚事务
    connection.rollback()
    print(f"An error occurred: {e}")

finally:
    # 关闭游标和连接
    cursor.close()
    connection.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

在上述示例中，我们首先通过 connection.start_transaction() 开启了一个事务。然后，在 try 块中执行了两个插入操作。如果所有操作顺利完成，就会通过 connection.commit() 提交事务。如果其中任何一步出现了异常，connection.rollback() 会回滚事务，使所有操作都不会生效。

通过使用事务，可以确保在执行一系列相关操作时，要么全部成功，要么全部失败。这对于确保数据的完整性和一致性非常重要。

关注我，不迷路，共学习，同进步

关注我，不迷路，共学习，同进步