Python 连接 MySQL 数据库：完整指南 – wiki大全

作者： /

Python 连接 MySQL 数据库：完整指南

在现代数据驱动的应用开发中，Python 常常需要与数据库进行交互，其中 MySQL 因其开源、稳定和高性能而成为最受欢迎的关系型数据库之一。本文将详细介绍如何使用 Python 官方推荐的 mysql-connector-python 库连接并操作 MySQL 数据库。

1. 前言

mysql-connector-python 是 MySQL 官方为 Python 提供的驱动程序，它允许 Python 应用程序直接访问 MySQL 数据库。该库纯 Python 实现，不依赖于 MySQL 客户端库，易于安装和使用。

2. 环境准备

在开始之前，请确保您的开发环境满足以下条件：

  • Python 安装： 建议使用 Python 3.6 或更高版本。
  • MySQL 服务器： 确保您的 MySQL 服务器已安装并正在运行，且您可以访问。
  • MySQL 认证信息： 您需要知道 MySQL 服务器的 host（主机名/IP地址，如 localhost）、port（端口，默认为 3306）、user（用户名）和 password（密码）。
  • 数据库名（可选）： 您可以预先创建好要连接的数据库，或者在 Python 代码中创建。

3. 安装 mysql-connector-python

使用 pip，Python 的包管理器，安装 mysql-connector-python 库。打开您的终端或命令提示符，执行以下命令：

bash
pip install mysql-connector-python

如果您的环境中同时存在 Python 2 和 Python 3，请确保使用 pip3：

bash
pip3 install mysql-connector-python

4. 建立数据库连接

安装完成后，您可以通过 mysql.connector 模块来建立与 MySQL 数据库的连接。

“`python
import mysql.connector

MySQL 连接配置

db_config = {
“host”: “localhost”,
“user”: “your_username”, # 替换为您的 MySQL 用户名
“password”: “your_password”, # 替换为您的 MySQL 密码
“database”: “test_database” # 替换为您的数据库名，如果还没有可以先不指定
}

mydb = None # 初始化连接对象

try:
# 尝试建立连接
mydb = mysql.connector.connect(**db_config)

if mydb.is_connected():
    print("成功连接到 MySQL 数据库!")

except mysql.connector.Error as err:
# 捕获连接错误
print(f”连接错误: {err}”)

finally:
# 确保连接最终被关闭
if mydb and mydb.is_connected():
mydb.close()
print(“MySQL 连接已关闭。”)

“`

连接参数说明：

  • host: MySQL 服务器的主机名或 IP 地址。
  • user: 用于连接 MySQL 的用户名。
  • password: 对应用户的密码。
  • database: (可选) 默认要连接的数据库名称。如果连接时未指定，您可以在之后通过 USE database_name 语句来选择数据库。
  • port: (可选) MySQL 服务器的端口，默认为 3306。

5. 执行 SQL 查询

建立连接后，您需要创建一个 cursor（游标）对象来执行 SQL 语句。游标是数据库操作的核心，它允许您遍历查询结果。

“`python
import mysql.connector

db_config = {
“host”: “localhost”,
“user”: “your_username”,
“password”: “your_password”,
“database”: “test_database”
}

mydb = None
mycursor = None

try:
mydb = mysql.connector.connect(**db_config)
mycursor = mydb.cursor()

# --- 1. 创建数据库 (如果不存在) ---
# 注意: 如果连接时未指定database，则需要先连接到MySQL服务器，再执行此操作。
# 如果已指定database，则此步可省略或在连接前单独执行。
# 以下示例假设您最初连接时未指定database
# 如果您已经在db_config中指定了database，则可以直接跳到创建表
# 或者连接到一个空的db_config，然后创建数据库再重新连接
if db_config.get("database") is None: # 假设没有在db_config中指定数据库
    temp_mydb = mysql.connector.connect(host=db_config["host"], user=db_config["user"], password=db_config["password"])
    temp_mycursor = temp_mydb.cursor()
    temp_mycursor.execute("CREATE DATABASE IF NOT EXISTS test_database")
    print("数据库 'test_database' 创建成功或已存在。")
    temp_mycursor.close()
    temp_mydb.close()
    # 重新连接到新创建的数据库
    db_config["database"] = "test_database"
    mydb = mysql.connector.connect(**db_config)
    mycursor = mydb.cursor()


# --- 2. 创建表 (如果不存在) ---
create_table_sql = """
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    address VARCHAR(255)
)
"""
mycursor.execute(create_table_sql)
print("表 'customers' 创建成功或已存在。")

# --- 3. 插入单条数据 ---
insert_sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John Doe", "Highway 21")
mycursor.execute(insert_sql, val)
mydb.commit() # 提交事务以保存更改
print(f"插入了 {mycursor.rowcount} 条记录，ID: {mycursor.lastrowid}")

# --- 4. 插入多条数据 (executemany) ---
insert_many_sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
vals = [
    ("Peter", "Lowstreet 4"),
    ("Amy", "Apple street 652"),
    ("Hannah", "Mountain 21")
]
mycursor.executemany(insert_many_sql, vals)
mydb.commit() # 提交事务
print(f"插入了 {mycursor.rowcount} 条多条记录。")

# --- 5. 查询数据 ---
mycursor.execute("SELECT id, name, address FROM customers")
# 使用 fetchall() 获取所有结果
myresult = mycursor.fetchall()
print("\n查询结果:")
for row in myresult:
    print(row)

# 查询带有 WHERE 子句的数据
mycursor.execute("SELECT * FROM customers WHERE address = 'Highway 21'")
one_result = mycursor.fetchone() # 获取一条结果
if one_result:
    print("\n查询 address 为 'Highway 21' 的记录:")
    print(one_result)

# --- 6. 更新数据 ---
update_sql = "UPDATE customers SET address = %s WHERE name = %s"
update_val = ("Valley 345", "John Doe")
mycursor.execute(update_sql, update_val)
mydb.commit() # 提交事务
print(f"\n更新了 {mycursor.rowcount} 条记录。")

# 再次查询以验证更新
mycursor.execute("SELECT * FROM customers WHERE name = 'John Doe'")
print("更新后 'John Doe' 的记录:")
print(mycursor.fetchone())

# --- 7. 删除数据 ---
delete_sql = "DELETE FROM customers WHERE address = %s"
delete_val = ("Lowstreet 4",) # 注意：单元素元组需要逗号
mycursor.execute(delete_sql, delete_val)
mydb.commit() # 提交事务
print(f"\n删除了 {mycursor.rowcount} 条记录。")

# 再次查询所有数据以验证删除
mycursor.execute("SELECT * FROM customers")
print("\n删除后所有记录:")
for row in mycursor.fetchall():
    print(row)

except mysql.connector.Error as err:
print(f”操作错误: {err}”)
# 如果发生错误，可以选择回滚事务
if mydb and mydb.is_connected():
mydb.rollback()
print(“事务已回滚。”)

finally:
# 确保游标和连接最终被关闭
if mycursor:
mycursor.close()
if mydb and mydb.is_connected():
mydb.close()
print(“MySQL 连接已关闭。”)

“`

SQL 操作注意事项：

  • mycursor.execute(sql, val): 这是执行 SQL 语句的主要方法。val 是一个元组或列表，用于安全地传递参数到 SQL 查询中，防止 SQL 注入。
  • mydb.commit(): 对于 INSERT、UPDATE、DELETE 等修改数据库的操作，必须调用 commit() 方法来提交事务，使更改永久生效。否则，您的更改将不会被保存。
  • mycursor.fetchall(): 获取查询结果集中的所有行。
  • mycursor.fetchone(): 获取查询结果集中的下一行。
  • mycursor.executemany(sql, vals): 当您需要插入或更新多条记录时，使用此方法可以显著提高效率。

6. 错误处理与最佳实践

6.1 错误处理

在实际应用中，数据库操作常常会遇到各种问题，例如网络中断、数据库服务器宕机、SQL 语法错误等。因此，使用 try...except...finally 结构来捕获 mysql.connector.Error 异常是至关重要的，以确保程序健壮性。

在 except 块中，您可以记录错误信息、回滚事务（如果需要），并向用户提供友好的错误提示。

6.2 资源管理 (使用 with 语句)

为了确保数据库连接和游标在使用完毕后能够正确关闭，即使发生错误也不例外，强烈建议使用 with 语句（上下文管理器）。这可以简化代码，并自动处理资源的打开和关闭。

“`python
import mysql.connector

db_config = {
“host”: “localhost”,
“user”: “your_username”,
“password”: “your_password”,
“database”: “test_database”
}

try:
with mysql.connector.connect(**db_config) as mydb:
print(“成功连接到 MySQL 数据库!”)
with mydb.cursor() as mycursor:
# 执行查询操作
mycursor.execute(“SELECT name, address FROM customers”)
for name, address in mycursor:
print(f”Name: {name}, Address: {address}”)

        # 插入数据示例
        insert_sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
        val = ("Alice", "Wonderland 7")
        mycursor.execute(insert_sql, val)
        mydb.commit() # 提交事务
        print(f"插入了 {mycursor.rowcount} 条记录。")

except mysql.connector.Error as err:
print(f”数据库操作错误: {err}”)

print(“程序执行完毕，连接已自动关闭。”)
“`

6.3 防止 SQL 注入

永远不要直接将用户输入拼接进 SQL 语句中。mysql-connector-python 提供的参数化查询（如 VALUES (%s, %s)）是防止 SQL 注入的最佳方式。库会负责正确地转义和处理这些参数。

6.4 敏感信息管理

在生产环境中，不应将数据库的用户名和密码直接硬编码在代码中。最佳实践包括：

  • 环境变量： 将敏感信息存储在环境变量中。
  • 配置文件： 使用专门的配置文件（例如 .ini、.json、.yaml 或 .env 文件），并通过版本控制忽略这些文件。
  • 密钥管理服务： 对于大型应用，可以考虑使用云服务商提供的密钥管理服务。

7. 总结

通过本文，您应该已经掌握了使用 mysql-connector-python 库连接和操作 MySQL 数据库的基本技能。从安装驱动、建立连接，到执行增删改查（CRUD）操作，再到重要的错误处理和安全最佳实践，这些都是您在 Python 项目中与 MySQL 交互的基石。在实际开发中，请始终牢记安全性、健壮性和效率，构建出高质量的数据库应用。

滚动至顶部