Python MySQL数据库操作基础教程

Python 与 MySQL 数据库交互概述

在现代软件开发中，数据的存储与管理至关重要。MySQL 作为一款广泛使用的开源关系型数据库管理系统，与 Python 的结合为开发者提供了强大的数据处理能力。Python 以其简洁的语法和丰富的库，使得与 MySQL 数据库的交互变得相对容易。通过这种结合，开发者可以实现数据的增删改查、数据库的创建与管理等一系列操作。

安装必要的库

在开始 Python 与 MySQL 的交互之前，需要安装 mysql - connector - python 库。这是 MySQL 官方提供的用于 Python 的驱动程序。可以使用 pip 这个 Python 包管理工具来安装：

pip install mysql - connector - python

如果使用的是虚拟环境，确保在激活虚拟环境后执行上述命令。安装完成后，就可以在 Python 代码中导入该库来使用。

连接到 MySQL 数据库

连接到 MySQL 数据库是进行后续操作的基础。下面是一个简单的 Python 代码示例，展示如何连接到 MySQL 数据库：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

print(mydb)

在上述代码中：

首先导入 mysql.connector 库。
使用 mysql.connector.connect() 方法来建立连接。这里需要提供数据库的主机地址（host），通常本地测试时为 localhost；用户名（user）和密码（password），这是登录 MySQL 数据库的凭证；以及要连接的数据库名称（database）。如果数据库不存在，在后续操作涉及到该数据库时会报错，除非先创建该数据库。
最后打印 mydb 对象，若连接成功，会输出关于连接的相关信息。

创建数据库

在 Python 中可以使用 CREATE DATABASE 语句来创建新的数据库。以下是示例代码：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mynewdatabase")

在这段代码中：

同样先导入 mysql.connector 库并建立与 MySQL 数据库的连接，但这里没有指定要连接的数据库，因为我们的目的是创建一个新数据库。
通过连接对象 mydb 创建一个游标对象 mycursor。游标在数据库操作中扮演着重要角色，它用于执行 SQL 语句并获取结果。
使用游标对象的 execute() 方法执行 CREATE DATABASE SQL 语句，这里创建了一个名为 mynewdatabase 的数据库。

在实际应用中，可能需要添加一些错误处理机制，以确保在数据库已存在等情况下程序不会出错。例如：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

mycursor = mydb.cursor()

try:
    mycursor.execute("CREATE DATABASE mynewdatabase")
    print("Database created successfully")
except mysql.connector.Error as err:
    if err.errno == mysql.connector.errorcode.ER_DB_CREATE_EXISTS:
        print("Database already exists")
    else:
        print(f"Error: {err}")

上述代码使用 try - except 块捕获可能的错误。如果错误码表示数据库已存在（ER_DB_CREATE_EXISTS），则打印相应提示；否则打印完整的错误信息。

创建表

在数据库中，表是存储数据的基本结构。创建表同样需要使用 SQL 语句，在 Python 中通过游标对象来执行。

简单表创建示例

假设我们要在刚刚创建的 mynewdatabase 数据库中创建一个 customers 表，用于存储客户信息，包括 id（唯一标识）、name（客户姓名）和 email（客户邮箱）。以下是示例代码：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("""
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
)
""")

在上述代码中：

建立与 mynewdatabase 数据库的连接。
创建游标对象。
使用 CREATE TABLE SQL 语句创建 customers 表。这里定义了 id 列，类型为 INT（整数），并且设置为自动递增（AUTO_INCREMENT），同时作为主键（PRIMARY KEY），确保每一行数据都有唯一标识；name 列和 email 列类型为 VARCHAR，用于存储可变长度的字符串，长度限制为 255 个字符。

表约束

在创建表时，合理使用约束可以保证数据的完整性和一致性。除了前面提到的 PRIMARY KEY 约束，还有其他一些常见约束。

NOT NULL 约束

NOT NULL 约束确保列中不能包含 NULL 值。例如，如果我们希望 name 列不能为空，可以这样修改创建表的语句：

mycursor.execute("""
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255)
)
""")

这样，当插入数据时，如果 name 列没有提供值，数据库会抛出错误。

UNIQUE 约束

UNIQUE 约束确保列中的所有值都是唯一的。如果我们希望 email 列的值唯一，可修改语句如下：

mycursor.execute("""
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
)
""")

这就保证了 email 列中不会出现重复值。

FOREIGN KEY 约束

FOREIGN KEY 约束用于建立表与表之间的关系。假设我们有另一个 orders 表，用于存储订单信息，并且每个订单都关联到一个客户，通过客户的 id 来关联。创建 orders 表并设置外键约束的代码如下：

mycursor.execute("""
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(255),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
)
""")

在这个例子中，orders 表的 customer_id 列设置为外键，它引用了 customers 表的 id 列。这意味着在 orders 表中插入数据时，customer_id 的值必须是 customers 表中已存在的 id 值，从而确保了数据的一致性。

插入数据

表创建完成后，就可以向表中插入数据了。在 Python 中，可以使用 INSERT INTO SQL 语句结合游标对象来实现。

插入单条记录

以下是向 customers 表插入单条客户记录的示例代码：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, email) VALUES (%s, %s)"
val = ("John Doe", "johndoe@example.com")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

在上述代码中：

建立数据库连接并创建游标。
定义 INSERT INTO SQL 语句，其中 VALUES 部分使用占位符 %s。这种使用占位符的方式可以有效防止 SQL 注入攻击。
定义要插入的值，以元组的形式存储在 val 变量中。
使用游标对象的 execute() 方法执行 SQL 语句，将 SQL 语句和值作为参数传递。
使用连接对象的 commit() 方法提交事务，使插入操作生效。在 MySQL 中，默认情况下，操作不会自动提交，需要手动调用 commit() 方法。
最后打印插入的记录数。

插入多条记录

如果要插入多条记录，可以使用 executemany() 方法。示例代码如下：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, email) VALUES (%s, %s)"
vals = [
    ("Jane Smith", "janesmith@example.com"),
    ("Bob Johnson", "bobjohnson@example.com"),
    ("Alice Brown", "alicebrown@example.com")
]

mycursor.executemany(sql, vals)

mydb.commit()

print(mycursor.rowcount, "records inserted.")

这里，vals 是一个包含多个元组的列表，每个元组对应一条要插入的记录。executemany() 方法会遍历这个列表，依次执行插入操作。

查询数据

从数据库中查询数据是常见的操作之一。Python 中通过执行 SELECT SQL 语句来实现。

查询所有记录

以下是查询 customers 表中所有记录的示例代码：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

在上述代码中：

建立数据库连接并创建游标。
执行 SELECT * FROM customers SQL 语句，这里 * 表示选择所有列。
使用游标对象的 fetchall() 方法获取查询结果，结果以二维元组的形式返回，每一个元组代表一行数据。
通过循环遍历结果集并打印每一行数据。

查询特定列

如果只需要查询某些特定的列，可以在 SELECT 语句中指定列名。例如，只查询 name 和 email 列：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT name, email FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

这样就只会返回 name 和 email 列的数据。

使用 WHERE 子句过滤数据

WHERE 子句用于过滤符合特定条件的数据。例如，查询 email 为 johndoe@example.com 的客户记录：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE email = %s"
val = ("johndoe@example.com",)

mycursor.execute(sql, val)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

这里使用了占位符 %s 并结合 val 元组来传递查询条件，同样是为了防止 SQL 注入。

排序查询结果

可以使用 ORDER BY 子句对查询结果进行排序。例如，按 name 列升序排序：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers ORDER BY name")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

如果要按降序排序，可以在列名后加上 DESC，如 ORDER BY name DESC。

更新数据

在数据库中，更新现有数据也是常见操作。通过 UPDATE SQL 语句结合 Python 游标对象来实现。

更新单条记录

以下是更新 customers 表中某条记录的 email 地址的示例代码：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET email = %s WHERE name = %s"
val = ("newemail@example.com", "John Doe")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

在上述代码中：

建立数据库连接并创建游标。
定义 UPDATE SQL 语句，SET 关键字后面指定要更新的列及其新值，WHERE 子句用于指定更新的条件。
使用占位符并通过 val 元组传递参数。
执行 SQL 语句并提交事务，最后打印受影响的记录数。

更新多条记录

如果要更新多条符合条件的记录，同样使用 UPDATE 语句。例如，将所有客户的 email 地址都加上 -updated 后缀：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET email = CONCAT(email, '-updated')"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

这里没有使用 WHERE 子句，所以会更新表中的所有记录。CONCAT 函数用于将原 email 地址和 -updated 字符串连接起来作为新的 email 值。

删除数据

删除数据库中的数据通过 DELETE FROM SQL 语句实现。

删除单条记录

以下是删除 customers 表中 name 为 John Doe 的记录的示例代码：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE name = %s"
val = ("John Doe",)

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

在上述代码中，通过 DELETE FROM 语句和 WHERE 子句指定要删除的记录条件，使用占位符传递参数，执行语句并提交事务后打印删除的记录数。

删除多条记录

若要删除多条符合条件的记录，例如删除所有 email 地址包含 example.com 的记录：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE email LIKE %s"
val = ("%example.com%",)

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

这里使用了 LIKE 关键字和通配符 % 来匹配 email 地址中包含 example.com 的记录并删除。

事务处理

在数据库操作中，事务是一组作为单个逻辑工作单元执行的操作。事务具有 ACID 属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。在 Python 与 MySQL 的交互中，合理使用事务可以确保数据的完整性。

简单事务示例

假设我们要进行两个操作：向 customers 表插入一条记录，同时向 orders 表插入一条关联的订单记录。这两个操作要么都成功，要么都失败，形成一个事务。示例代码如下：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

try:
    # 插入客户记录
    sql1 = "INSERT INTO customers (name, email) VALUES (%s, %s)"
    val1 = ("New Customer", "newcustomer@example.com")
    mycursor.execute(sql1, val1)
    customer_id = mycursor.lastrowid

    # 插入订单记录，关联刚刚插入的客户
    sql2 = "INSERT INTO orders (order_number, customer_id) VALUES (%s, %s)"
    val2 = ("12345", customer_id)
    mycursor.execute(sql2, val2)

    mydb.commit()
    print("Both operations committed successfully")
except mysql.connector.Error as err:
    mydb.rollback()
    print(f"Error: {err}, Transaction rolled back")

在上述代码中：

建立数据库连接并创建游标。
使用 try - except 块来包含事务操作。
在 try 块中，首先插入客户记录，通过 mycursor.lastrowid 获取刚刚插入客户记录的 id。然后使用这个 id 插入关联的订单记录。
如果所有操作都成功，调用 mydb.commit() 提交事务。
如果在执行过程中出现错误，except 块捕获异常并调用 mydb.rollback() 回滚事务，撤销之前执行的所有操作，确保数据的一致性。

事务隔离级别

MySQL 支持不同的事务隔离级别，如 READ - UNCOMMITTED、READ - COMMITTED、REPEATABLE - READ 和 SERIALIZABLE。可以通过 SET SESSION TRANSACTION ISOLATION LEVEL 语句来设置事务隔离级别。在 Python 中，可以使用游标执行该语句。例如，设置事务隔离级别为 READ - COMMITTED：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mynewdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED")

# 后续进行事务操作
try:
    # 事务操作代码
    mydb.commit()
    print("Transaction committed successfully")
except mysql.connector.Error as err:
    mydb.rollback()
    print(f"Error: {err}, Transaction rolled back")

不同的隔离级别在并发访问数据库时会对数据的一致性和性能产生不同的影响，需要根据具体应用场景来选择合适的隔离级别。

数据库连接池

在实际的应用开发中，频繁地创建和销毁数据库连接会带来性能开销。数据库连接池技术可以解决这个问题。连接池在初始化时创建一定数量的数据库连接，并将这些连接保存在池中。当应用需要连接数据库时，从池中获取一个连接；使用完毕后，将连接放回池中，而不是销毁。

使用 `mysql - connector - python - pooling` 库实现连接池

首先需要安装 mysql - connector - python - pooling 库：

pip install mysql - connector - python - pooling

以下是使用连接池的示例代码：

import mysql.connector.pooling

config = {
    "host": "localhost",
    "user": "your_username",
    "password": "your_password",
    "database": "mynewdatabase"
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        pool_name="mypool",
        pool_size=5,
        **config
    )

    connection = pool.get_connection()
    mycursor = connection.cursor()

    mycursor.execute("SELECT * FROM customers")
    myresult = mycursor.fetchall()

    for x in myresult:
        print(x)

    mycursor.close()
    connection.close()
except mysql.connector.Error as err:
    print(f"Error: {err}")

在上述代码中：

导入 mysql.connector.pooling 模块。
定义数据库连接配置 config。
创建 MySQLConnectionPool 对象，指定连接池名称（pool_name）和连接池大小（pool_size），并传入数据库配置。
从连接池中获取一个连接，创建游标执行查询操作，获取并打印结果。
使用完毕后，关闭游标和连接，将连接放回连接池。

通过使用连接池，可以大大提高数据库操作的性能，特别是在高并发的应用场景中。

总结与最佳实践

通过以上内容，我们详细介绍了 Python 与 MySQL 数据库的基本操作，包括连接数据库、创建数据库和表、插入、查询、更新、删除数据，以及事务处理和连接池的使用。在实际开发中，还需要注意以下一些最佳实践：

错误处理：在执行数据库操作时，始终要进行错误处理，以确保程序的稳定性和健壮性。
SQL 注入防范：使用占位符传递参数，避免直接将用户输入拼接到 SQL 语句中，防止 SQL 注入攻击。
事务合理使用：对于需要保证数据一致性的多个操作，要合理使用事务，确保要么所有操作都成功，要么都回滚。
连接管理：在高并发场景下，使用连接池来管理数据库连接，提高性能并减少资源消耗。
数据库设计：在创建数据库和表时，要进行合理的设计，考虑数据的完整性、一致性和查询效率。

希望通过本教程，读者能够熟练掌握 Python 与 MySQL 数据库的操作，并应用到实际项目开发中。

Python MySQL数据库操作基础教程

Python 与 MySQL 数据库交互概述

安装必要的库

连接到 MySQL 数据库

创建数据库

创建表

简单表创建示例

表约束

NOT NULL 约束

UNIQUE 约束

FOREIGN KEY 约束

插入数据

插入单条记录

插入多条记录

查询数据

查询所有记录

查询特定列

使用 WHERE 子句过滤数据

排序查询结果

更新数据

更新单条记录

更新多条记录

删除数据

删除单条记录

删除多条记录

事务处理

简单事务示例

事务隔离级别

数据库连接池

使用 mysql - connector - python - pooling 库实现连接池

总结与最佳实践

使用 `mysql - connector - python - pooling` 库实现连接池