MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

Python使用MySQLdb库进行数据库操作

2022-07-241.7k 阅读

安装MySQLdb库

在开始使用 MySQLdb 库进行数据库操作之前，首先要确保该库已安装在你的Python环境中。如果你使用的是 pip 包管理器，在命令行中执行以下命令即可完成安装：

pip install MySQL-python

需要注意的是，MySQL-python 是 MySQLdb 的Python接口封装库。在某些操作系统或Python版本下，安装过程可能会遇到一些依赖问题。例如，在基于Debian或Ubuntu的系统上，可能需要先安装一些系统级别的依赖包：

sudo apt-get install python-dev default-libmysqlclient-dev

在CentOS系统上，相应的命令为：

sudo yum install python-devel mysql-devel

安装完成后，你可以在Python脚本中导入 MySQLdb 库，开始进行数据库相关的操作。

连接到MySQL数据库

连接到MySQL数据库是使用 MySQLdb 库进行任何数据库操作的第一步。MySQLdb 库提供了 connect() 函数来实现这一功能。connect() 函数接受多个参数，用于指定数据库的主机地址、用户名、密码、数据库名称等信息。以下是一个基本的连接示例：

import MySQLdb

# 连接到MySQL数据库
db = MySQLdb.connect(
    host="localhost",
    user="yourusername",
    passwd="yourpassword",
    db="yourdatabase"
)

在上述代码中：

  • host 参数指定了MySQL服务器的地址，这里设为 localhost 表示本地主机。如果MySQL服务器运行在远程主机上，需要将其替换为远程主机的IP地址或域名。
  • user 参数是连接数据库所需的用户名。
  • passwd 参数是该用户对应的密码。
  • db 参数指定了要连接的具体数据库名称。

成功连接后，connect() 函数会返回一个数据库连接对象 db，后续对数据库的各种操作都将通过这个对象来进行。

处理连接错误

在实际应用中，连接数据库时可能会遇到各种错误，例如网络问题、用户名密码错误、数据库不存在等。为了确保程序的健壮性，需要对这些可能出现的错误进行处理。可以使用Python的异常处理机制来捕获连接过程中可能抛出的异常。以下是改进后的代码：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    print("成功连接到数据库")
except MySQLdb.Error as e:
    print(f"连接数据库时出错: {e}")

在上述代码中，try 块尝试连接到数据库。如果连接成功，会打印出“成功连接到数据库”的提示信息。如果连接过程中发生错误，except 块会捕获 MySQLdb.Error 异常，并打印出具体的错误信息。

创建游标对象

在连接到数据库之后，需要创建一个游标对象。游标是一个用于执行SQL语句并获取结果的对象。通过游标，可以向数据库发送SQL查询，并获取查询结果。MySQLdb 库中，通过连接对象的 cursor() 方法来创建游标对象。以下是创建游标的示例代码：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()
    print("成功创建游标")
except MySQLdb.Error as e:
    print(f"连接数据库或创建游标时出错: {e}")

上述代码在成功连接到数据库后，通过 db.cursor() 创建了一个游标对象 cursor。游标对象有多种类型，默认情况下创建的是普通游标。MySQLdb 还支持其他类型的游标，如 DictCursor，它会将查询结果以字典形式返回，便于通过字段名来访问数据。要使用 DictCursor，需要导入相应的模块并指定游标类型，示例如下：

import MySQLdb
from MySQLdb.cursors import DictCursor

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor(DictCursor)
    print("成功创建DictCursor游标")
except MySQLdb.Error as e:
    print(f"连接数据库或创建游标时出错: {e}")

使用 DictCursor 游标在处理查询结果时，代码会更加直观和易读，特别是当查询结果字段较多时。

执行SQL查询语句

执行SELECT查询

SELECT查询是数据库操作中最常用的操作之一，用于从数据库中检索数据。通过游标对象的 execute() 方法可以执行SQL查询语句。以下是一个简单的SELECT查询示例，从名为 users 的表中选择所有记录：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()
    sql = "SELECT * FROM users"
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        print(row)
    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行查询时出错: {e}")

在上述代码中：

  • 首先定义了SQL查询语句 sql，这里使用 SELECT * FROM users 表示选择 users 表中的所有字段和记录。
  • 然后通过游标对象 cursor 的 execute() 方法执行该SQL查询。
  • 执行查询后，使用 cursor.fetchall() 方法获取查询结果，该方法会返回一个包含所有查询结果行的元组，每个元组代表一行数据。
  • 最后通过循环遍历结果集并打印每一行数据。

获取查询结果的不同方式

除了使用 fetchall() 方法获取所有查询结果外，MySQLdb 还提供了其他方法来获取查询结果，例如 fetchone() 和 fetchmany()。

fetchone() 方法

fetchone() 方法用于获取结果集中的下一行数据。如果没有更多行，则返回 None。以下是使用 fetchone() 方法的示例：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()
    sql = "SELECT * FROM users"
    cursor.execute(sql)
    row = cursor.fetchone()
    while row:
        print(row)
        row = cursor.fetchone()
    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行查询时出错: {e}")

在上述代码中，通过 while 循环不断调用 fetchone() 方法，逐行获取查询结果并打印，直到没有更多数据（fetchone() 返回 None）。

fetchmany() 方法

fetchmany() 方法用于获取指定数量的结果行。它接受一个参数 size，表示要获取的行数。以下是使用 fetchmany() 方法的示例：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()
    sql = "SELECT * FROM users"
    cursor.execute(sql)
    rows = cursor.fetchmany(5)
    while rows:
        for row in rows:
            print(row)
        rows = cursor.fetchmany(5)
    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行查询时出错: {e}")

在上述代码中，每次调用 fetchmany(5) 方法获取5行数据，然后通过内部循环打印这些数据，接着再次调用 fetchmany(5) 获取下一批数据，直到没有更多数据。这种方式在处理大量数据时可以减少内存占用，适用于需要逐批处理数据的场景。

使用参数化查询

在实际应用中，经常需要根据用户输入或程序变量来构建SQL查询。为了避免SQL注入攻击，应该使用参数化查询。在 MySQLdb 中，可以通过在SQL语句中使用占位符，并将参数作为 execute() 方法的第二个参数传递来实现参数化查询。以下是一个示例，根据用户输入的用户名查询用户信息：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()
    username = "testuser"
    sql = "SELECT * FROM users WHERE username = %s"
    cursor.execute(sql, (username,))
    results = cursor.fetchall()
    for row in results:
        print(row)
    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行查询时出错: {e}")

在上述代码中：

  • SQL语句 sql 中使用 %s 作为占位符，表示要替换的参数。
  • execute() 方法的第二个参数是一个包含参数值的元组，这里只有一个参数 username，所以元组中只有一个元素。MySQLdb 会自动对参数值进行转义处理，从而有效防止SQL注入攻击。

执行INSERT、UPDATE和DELETE操作

INSERT操作

INSERT操作用于向数据库表中插入新的记录。同样通过游标对象的 execute() 方法来执行INSERT语句。以下是一个向 users 表中插入一条新用户记录的示例：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()
    username = "newuser"
    password = "newpassword"
    sql = "INSERT INTO users (username, password) VALUES (%s, %s)"
    cursor.execute(sql, (username, password))
    db.commit()
    print("成功插入一条记录")
    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行INSERT操作时出错: {e}")
    db.rollback()

在上述代码中：

  • 定义了要插入的用户名和密码变量 username 和 password。
  • SQL语句 sql 使用 INSERT INTO...VALUES 语法，指定要插入数据的表名 users 以及对应的字段名 username 和 password，并使用 %s 占位符表示要插入的值。
  • 通过 cursor.execute() 方法执行INSERT语句，并传递参数值。
  • 执行INSERT操作后，需要调用连接对象的 commit() 方法来提交事务，将数据真正插入到数据库中。如果不调用 commit() 方法，插入操作不会生效。
  • 如果执行过程中发生错误，使用 db.rollback() 方法回滚事务，撤销未完成的操作，以确保数据库的一致性。

UPDATE操作

UPDATE操作用于修改数据库表中已有的记录。以下是一个根据用户ID更新用户密码的示例：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()
    user_id = 1
    new_password = "updatedpassword"
    sql = "UPDATE users SET password = %s WHERE id = %s"
    cursor.execute(sql, (new_password, user_id))
    db.commit()
    print("成功更新一条记录")
    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行UPDATE操作时出错: {e}")
    db.rollback()

在上述代码中：

  • 定义了要更新的用户ID user_id 和新密码 new_password。
  • SQL语句 sql 使用 UPDATE...SET...WHERE 语法，指定要更新的表名 users，要更新的字段 password，以及更新的条件 WHERE id = %s。
  • 通过 cursor.execute() 方法执行UPDATE语句，并传递参数值。同样，执行后需要调用 commit() 方法提交事务，若出错则调用 rollback() 方法回滚事务。

DELETE操作

DELETE操作用于从数据库表中删除记录。以下是一个根据用户ID删除用户记录的示例：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()
    user_id = 1
    sql = "DELETE FROM users WHERE id = %s"
    cursor.execute(sql, (user_id,))
    db.commit()
    print("成功删除一条记录")
    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行DELETE操作时出错: {e}")
    db.rollback()

在上述代码中：

  • 定义了要删除的用户ID user_id。
  • SQL语句 sql 使用 DELETE FROM...WHERE 语法，指定要删除记录的表名 users 以及删除的条件 WHERE id = %s。
  • 通过 cursor.execute() 方法执行DELETE语句，并传递参数值。执行后同样要调用 commit() 方法提交事务，出错时调用 rollback() 方法回滚事务。

事务处理

在数据库操作中，事务是一组逻辑上相关的操作，这些操作要么全部成功执行，要么全部不执行。例如，在一个涉及资金转账的操作中，从一个账户扣除金额和向另一个账户增加金额这两个操作必须作为一个事务来处理，以确保数据的一致性。MySQLdb 库通过连接对象提供了事务处理的功能。

手动控制事务

在前面的INSERT、UPDATE和DELETE操作示例中，已经展示了手动提交事务（commit()）和回滚事务（rollback()）的用法。以下是一个更复杂的示例，模拟一个简单的资金转账操作：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()

    # 从账户A扣除100元
    account_a_id = 1
    sql = "UPDATE accounts SET balance = balance - 100 WHERE id = %s"
    cursor.execute(sql, (account_a_id,))

    # 向账户B增加100元
    account_b_id = 2
    sql = "UPDATE accounts SET balance = balance + 100 WHERE id = %s"
    cursor.execute(sql, (account_b_id,))

    db.commit()
    print("转账成功")
    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行事务时出错: {e}")
    db.rollback()

在上述代码中：

  • 首先尝试从 accounts 表中ID为 account_a_id 的账户扣除100元，然后向ID为 account_b_id 的账户增加100元。
  • 如果这两个操作都成功执行，调用 db.commit() 方法提交事务，将更改永久保存到数据库。
  • 如果在执行过程中任何一个操作出现错误，except 块捕获 MySQLdb.Error 异常，调用 db.rollback() 方法回滚事务，撤销之前执行的所有操作，确保数据库状态保持不变。

自动提交模式

默认情况下，MySQLdb 的连接对象处于自动提交模式，即每次执行SQL语句后，更改会立即提交到数据库。可以通过设置连接对象的 autocommit 属性来切换自动提交模式。以下是将连接设置为手动提交模式（关闭自动提交）的示例：

import MySQLdb

db = MySQLdb.connect(
    host="localhost",
    user="yourusername",
    passwd="yourpassword",
    db="yourdatabase"
)
db.autocommit(False)

try:
    cursor = db.cursor()
    # 执行SQL操作
    sql = "INSERT INTO some_table (column1, column2) VALUES (%s, %s)"
    cursor.execute(sql, ("value1", "value2"))
    db.commit()
    print("操作成功提交")
    cursor.close()
except MySQLdb.Error as e:
    print(f"执行操作时出错: {e}")
    db.rollback()
finally:
    db.close()

在上述代码中：

  • 通过 db.autocommit(False) 将连接设置为手动提交模式。
  • 在 try 块中执行插入操作，执行成功后调用 commit() 方法提交事务。
  • 如果执行过程中出现错误，except 块捕获异常并调用 rollback() 方法回滚事务。
  • 无论是否发生错误，finally 块都会执行，关闭数据库连接。

处理数据库连接关闭

在完成数据库操作后，应该及时关闭数据库连接，以释放资源。可以通过连接对象的 close() 方法来关闭连接。在前面的示例中，已经在代码的末尾调用了 db.close() 方法来关闭连接。然而，在实际应用中，可能会遇到各种异常情况导致程序提前终止，从而未能正常关闭连接。为了确保连接始终能被正确关闭，可以使用Python的 try - finally 语句块。以下是一个改进后的示例：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()
    # 执行数据库操作
    sql = "SELECT * FROM users"
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        print(row)
except MySQLdb.Error as e:
    print(f"执行操作时出错: {e}")
finally:
    if 'db' in locals():
        db.close()

在上述代码中：

  • try 块中执行数据库连接、操作等正常流程。
  • 如果出现错误，except 块捕获异常并打印错误信息。
  • 在 finally 块中，首先检查 db 变量是否存在于局部变量中（以确保连接已经成功建立），然后调用 db.close() 方法关闭数据库连接。这样无论在操作过程中是否发生错误，都能保证数据库连接被正确关闭。

常见问题及解决方法

字符编码问题

在处理包含非ASCII字符（如中文、日文、韩文等）的数据时，可能会遇到字符编码问题。MySQL默认的字符编码可能与Python中的编码不一致，导致数据插入或查询时出现乱码。解决这个问题的方法是在连接数据库时指定正确的字符编码。以下是一个指定字符编码为 utf8 的示例：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase",
        charset='utf8'
    )
    cursor = db.cursor()
    # 执行数据库操作
    sql = "INSERT INTO some_table (text_column) VALUES ('中文测试')"
    cursor.execute(sql)
    db.commit()
    print("插入成功")
    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行操作时出错: {e}")

在上述代码中，通过在 connect() 函数中添加 charset='utf8' 参数，确保数据库连接使用 utf8 字符编码，从而避免字符编码相关的问题。

数据库连接池

在高并发的应用场景中，频繁地创建和关闭数据库连接会带来性能开销。数据库连接池是一种解决方案，它预先创建一定数量的数据库连接，并将这些连接保存在池中。当应用程序需要连接数据库时，从池中获取一个连接，使用完毕后再将连接放回池中。MySQLdb 本身不直接提供连接池功能，但可以使用第三方库如 DBUtils 来实现连接池。以下是一个使用 DBUtils 实现数据库连接池的示例：

from dbutils.pooled_db import PooledDB
import MySQLdb

# 创建连接池
pool = PooledDB(
    creator=MySQLdb,
    host="localhost",
    user="yourusername",
    passwd="yourpassword",
    db="yourdatabase",
    port=3306,
    autocommit=True,
    charset='utf8',
    maxconnections=10
)

try:
    # 从连接池获取连接
    db = pool.connection()
    cursor = db.cursor()
    sql = "SELECT * FROM users"
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        print(row)
    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行操作时出错: {e}")

在上述代码中：

  • 使用 PooledDB 类创建了一个数据库连接池，creator 参数指定使用 MySQLdb 库，maxconnections 参数指定连接池的最大连接数为10。
  • 通过 pool.connection() 从连接池获取一个数据库连接，使用完毕后调用 db.close() 方法将连接放回连接池，而不是真正关闭连接。

通过合理使用数据库连接池，可以显著提高应用程序在高并发场景下的性能和资源利用率。

数据库锁

在多用户并发访问数据库时，可能会出现数据一致性问题。数据库锁是一种用于解决这类问题的机制，它可以确保在同一时间只有一个事务可以访问特定的数据。在 MySQLdb 中，可以通过在SQL语句中使用 LOCK TABLES 和 UNLOCK TABLES 语句来手动获取和释放表级锁。以下是一个简单的示例，展示如何使用表级锁来防止并发更新导致的数据冲突：

import MySQLdb

try:
    db = MySQLdb.connect(
        host="localhost",
        user="yourusername",
        passwd="yourpassword",
        db="yourdatabase"
    )
    cursor = db.cursor()

    # 获取表锁
    cursor.execute("LOCK TABLES accounts WRITE")

    try:
        # 执行更新操作
        account_id = 1
        sql = "UPDATE accounts SET balance = balance - 100 WHERE id = %s"
        cursor.execute(sql, (account_id,))
        db.commit()
        print("更新成功")
    finally:
        # 释放表锁
        cursor.execute("UNLOCK TABLES")

    cursor.close()
    db.close()
except MySQLdb.Error as e:
    print(f"执行操作时出错: {e}")

在上述代码中：

  • 首先通过 cursor.execute("LOCK TABLES accounts WRITE") 获取对 accounts 表的写锁，这样其他事务无法同时对该表进行读写操作。
  • 在 try 块中执行更新操作，确保在更新过程中数据的一致性。
  • 无论更新操作是否成功，在 finally 块中通过 cursor.execute("UNLOCK TABLES") 释放表锁，以便其他事务可以访问该表。

需要注意的是，表级锁粒度较大，可能会影响并发性能。在实际应用中，应根据具体需求选择合适的锁粒度（如行级锁）来平衡数据一致性和并发性能。

通过以上对 MySQLdb 库在Python中进行数据库操作的详细介绍，包括安装、连接数据库、执行各种SQL操作、事务处理以及常见问题解决等方面，相信读者已经对如何使用 MySQLdb 库进行高效的数据库开发有了深入的了解。在实际项目中，应根据具体需求合理运用这些知识，构建健壮、高效的数据库应用程序。