代码收藏家技术教程 2024-12-01

【Python】从零开始掌握PyMySQL数据库与的操作技巧！保姆级教程！

文章目录

为什么选择PyMySQL？

PyMySQL vs MySQLdb

PyMySQL的核心特点

1. 纯Python实现

2. 强大的兼容性

3. 简洁易用的API

环境准备与安装

1. 安装Python

2. 安装MySQL

3. 安装PyMySQL

基础用法详解

连接数据库

参数解释

执行查询

关键点

插入数据

单条插入

多条插入

关键点

更新与删除操作

更新数据

删除数据

关键点

高级功能与优化

事务管理

手动控制事务

关键点

游标类型选择

默认游标（Cursor）

字典游标（DictCursor）

流式游标（SSCursor）

性能优化策略

1. 使用批量操作

2. 连接池

3. 索引优化

4. 查询优化

安全性最佳实践

1. 使用参数化查询

不安全的示例

安全的示例

2. 最小权限原则

3. 输入验证与清理

4. 使用加密连接

常见错误排查

1. 连接错误

2. 数据库不存在

3. 表不存在

4. 数据类型不匹配

5. 事务处理错误

实战案例：构建一个简单的用户管理系统

项目结构

database.py

main.py

数据库准备

运行项目

更多文献

总结

在当今数据驱动的时代，掌握高效的数据库操作技巧对于每一个Python开发者来说都是必不可少的。而MySQL作为最流行的关系型数据库管理系统之一，配合Python使用更是如虎添翼。👨‍💻👩‍💻 本文将深入探讨如何使用PyMySQL，一个纯Python实现的MySQL客户端库，轻松实现Python与MySQL的无缝对接。无论你是数据库新手还是有经验的开发者，这篇指南都将为你提供全面的知识和实用的技巧，助你在项目中游刃有余。

为什么选择PyMySQL？

在众多Python连接MySQL的库中，PyMySQL以其纯Python实现、易于安装和跨平台兼容性脱颖而出。与需要编译C扩展的MySQLdb相比，PyMySQL无需额外的C语言依赖，使得安装过程更加简便，特别是在Windows等环境下尤为明显。🌟

PyMySQL vs MySQLdb

特性	PyMySQL	MySQLdb
语言实现	纯Python	C语言扩展
安装简便性	高	相对复杂
兼容性	支持MySQL 5.x及以上版本，包括MariaDB	类似
性能	略低于MySQLdb，但优化良好	略高

尽管在性能上MySQLdb略占优势，但PyMySQL凭借其轻量级和易用性，特别是在开发和测试环境中，成为了许多开发者的首选。

PyMySQL的核心特点

了解PyMySQL的核心特点，有助于我们在实际应用中更好地发挥它的优势。

1. 纯Python实现

由于PyMySQL完全基于Python编写，不依赖任何C扩展库，这意味着它可以在任何支持Python的平台上无缝运行，包括Windows、Linux和macOS等操作系统。对于开发者而言，这大大简化了安装过程，避免了编译错误和环境配置问题。

2. 强大的兼容性

PyMySQL不仅支持MySQL 5.x和8.x版本，还兼容MariaDB。这使得它在不同的数据库环境中都能发挥作用，提供了极大的灵活性。

3. 简洁易用的API

PyMySQL的API设计与MySQLdb类似，这意味着如果你之前有使用MySQLdb的经验，迁移到PyMySQL将会非常顺利。此外，PyMySQL还提供了丰富的文档和社区支持，帮助开发者快速上手。

环境准备与安装

在开始使用PyMySQL之前，我们需要确保环境中已安装了Python和MySQL数据库。

1. 安装Python

确保你的系统中已安装Python 3.x版本。可以通过以下命令检查Python版本：

python --version

如果尚未安装，可以前往Python官网下载并安装最新版本的Python。

2. 安装MySQL

下载并安装MySQL数据库服务器。可以选择安装社区版（Community Edition），下载链接：MySQL下载页面

安装过程中，请记住设置的root用户密码，后续连接数据库时将会用到。

3. 安装PyMySQL

使用pip安装PyMySQL非常简单。打开终端或命令提示符，执行以下命令：

pip install pymysql

如果使用的是Python 3，可能需要使用pip3：

pip3 install pymysql

安装完成后，可以通过以下命令验证安装：

import pymysql
print(pymysql.__version__)

若输出版本号，即表示安装成功。

基础用法详解

掌握了环境的准备工作后，让我们深入了解如何使用PyMySQL进行数据库操作。本文将通过实际代码示例，逐步讲解连接数据库、执行查询、插入数据、更新与删除操作等常用功能。

连接数据库

在进行任何数据库操作之前，首先需要建立与MySQL数据库的连接。PyMySQL的连接过程类似于其他数据库连接库，以下是一个基本的连接示例：

import pymysql

# 建立连接
connection = pymysql.connect(
    host='localhost',          # 数据库主机地址
    user='your_username',      # 数据库用户名
    password='your_password',  # 数据库密码
    database='your_dbname',    # 选择的数据库
    charset='utf8mb4',         # 指定字符集
    cursorclass=pymysql.cursors.DictCursor  # 返回字典格式的数据
)

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

# 关闭游标和连接
cursor.close()
connection.close()

参数解释

host: 数据库服务器的主机名或IP地址，通常为localhost。

user: 数据库的用户名。

password: 对应用户的密码。

database: 需要连接的数据库名称。

charset: 字符集，推荐使用utf8mb4，支持更多的Unicode字符。

cursorclass: 指定游标类型，DictCursor用于返回字典格式的数据，便于处理。

执行查询

执行SQL查询是数据库操作中最常见的任务。以下示例展示了如何使用PyMySQL执行SELECT语句并处理结果。

try:
    # 建立连接
    connection = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_dbname',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    
    with connection.cursor() as cursor:
        # 定义SQL查询语句
        sql = "SELECT * FROM users WHERE age > %s"
        cursor.execute(sql, (25,))  # 使用参数化查询防止SQL注入
        
        # 获取所有结果
        results = cursor.fetchall()
        for row in results:
            print(row)
            
except pymysql.MySQLError as e:
    print(f"查询失败: {e}")
finally:
    connection.close()

关键点

参数化查询: 使用%s作为占位符，并通过第二个参数传递实际值，防止SQL注入攻击。

游标上下文管理: 使用with语句自动管理游标的打开与关闭。

错误处理: 捕获MySQLError异常，确保在查询失败时能够得到提示并安全关闭连接。

插入数据

插入数据同样是数据库操作中的基础任务。PyMySQL提供了execute()和executemany()方法，分别用于单条和多条数据的插入。

单条插入

try:
    connection = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_dbname',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    
    with connection.cursor() as cursor:
        sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
        cursor.execute(sql, ('John Doe', 30, 'john.doe@example.com'))
    
    connection.commit()  # 提交事务
    
except pymysql.MySQLError as e:
    print(f"插入失败: {e}")
    connection.rollback()  # 回滚事务
finally:
    connection.close()

多条插入

try:
    connection = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_dbname',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    
    with connection.cursor() as cursor:
        sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
        users = [
            ('Alice', 25, 'alice@example.com'),
            ('Bob', 28, 'bob@example.com'),
            ('Charlie', 22, 'charlie@example.com')
        ]
        cursor.executemany(sql, users)
    
    connection.commit()  # 提交事务
    
except pymysql.MySQLError as e:
    print(f"批量插入失败: {e}")
    connection.rollback()  # 回滚事务
finally:
    connection.close()

关键点

事务管理: 在进行数据修改操作时，记得调用connection.commit()提交事务，确保数据被保存。

错误处理: 在出现异常时，调用connection.rollback()回滚事务，避免部分数据被插入导致数据不一致。

更新与删除操作

更新和删除数据的过程与插入数据类似，都是通过编写SQL语句并使用execute()方法执行。

更新数据

try:
    connection = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_dbname',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    
    with connection.cursor() as cursor:
        sql = "UPDATE users SET email = %s WHERE name = %s"
        cursor.execute(sql, ('new.email@example.com', 'John Doe'))
    
    connection.commit()  # 提交事务
    
except pymysql.MySQLError as e:
    print(f"更新失败: {e}")
    connection.rollback()
finally:
    connection.close()

删除数据

try:
    connection = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_dbname',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    
    with connection.cursor() as cursor:
        sql = "DELETE FROM users WHERE name = %s"
        cursor.execute(sql, ('John Doe',))
    
    connection.commit()  # 提交事务
    
except pymysql.MySQLError as e:
    print(f"删除失败: {e}")
    connection.rollback()
finally:
    connection.close()

关键点

条件语句: 在更新和删除操作中，务必添加适当的条件语句，避免误操作导致大量数据被修改或删除。

参数化查询: 始终使用参数化查询，防止SQL注入。

高级功能与优化

在掌握了基础的数据库操作后，我们可以进一步探索PyMySQL的一些高级功能和优化技巧，以提升应用的性能和稳定性。

事务管理

事务是数据库中一组任务的集合，要么全部执行成功，要么全部回滚，以确保数据的一致性。在PyMySQL中，事务管理可以通过控制自动提交模式来实现。

手动控制事务

try:
    connection = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_dbname',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    
    connection.autocommit(False)  # 关闭自动提交
    
    with connection.cursor() as cursor:
        # 执行多个操作
        cursor.execute("INSERT INTO accounts (user, balance) VALUES (%s, %s)", ('Alice', 1000))
        cursor.execute("INSERT INTO transactions (user, amount) VALUES (%s, %s)", ('Alice', -100))
    
    connection.commit()  # 提交事务
    
except pymysql.MySQLError as e:
    print(f"事务失败: {e}")
    connection.rollback()  # 回滚事务
finally:
    connection.close()

关键点

关闭自动提交: 通过connection.autocommit(False)关闭自动提交模式，手动控制事务的提交与回滚。

多操作原子性: 在一个事务中执行多个相关操作，确保它们要么全部成功，要么全部失败。

游标类型选择

PyMySQL提供了多种游标类型，适用于不同的场景。选择合适的游标类型，可以提升数据处理的效率和灵活性。

默认游标（Cursor）

返回元组格式的结果。适用于对数据结构要求不高的场景。

connection = pymysql.connect(
    ...,
    cursorclass=pymysql.cursors.Cursor
)

字典游标（DictCursor）

返回字典格式的结果，字段名作为键，便于通过键名访问数据。

connection = pymysql.connect(
    ...,
    cursorclass=pymysql.cursors.DictCursor
)

使用示例：

with connection.cursor() as cursor:
    cursor.execute("SELECT id, name FROM users")
    result = cursor.fetchall()
    for row in result:
        print(row['id'], row['name'])

流式游标（SSCursor）

适用于处理大规模数据集时，避免一次性加载所有数据到内存，节省内存资源。

connection = pymysql.connect(
    ...,
    cursorclass=pymysql.cursors.SSCursor
)

使用示例：

with connection.cursor() as cursor:
    cursor.execute("SELECT * FROM large_table")
    for row in cursor:
        process(row)  # 逐行处理数据

性能优化策略

在高并发或大数据量的应用场景中，性能优化尤为重要。以下是一些有效的性能优化策略：

1. 使用批量操作

减少数据库交互次数，使用executemany()方法批量插入或更新数据。

with connection.cursor() as cursor:
    sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
    users = [
        ('User1', 30, 'user1@example.com'),
        ('User2', 25, 'user2@example.com'),
        # 更多用户数据
    ]
    cursor.executemany(sql, users)

2. 连接池

在高并发应用中，频繁建立和关闭数据库连接会带来额外的开销。使用连接池可以复用现有连接，提升性能。虽然PyMySQL本身不提供连接池功能，但可以结合第三方库如DBUtils或SQLAlchemy实现。

安装DBUtils：

pip install DBUtils

使用示例：

from dbutils.pooled_db import PooledDB
import pymysql

pool = PooledDB(
    creator=pymysql,
    maxconnections=20,
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_dbname',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

# 从连接池获取连接
connection = pool.connection()

# 使用连接
with connection.cursor() as cursor:
    cursor.execute("SELECT * FROM users")
    results = cursor.fetchall()

# 连接会自动归还到连接池
connection.close()

3. 索引优化

合理设计数据库索引，可以显著提升查询性能。确保常用的查询条件字段上建立索引，但避免过多的索引导致写操作性能下降。

CREATE INDEX idx_users_age ON users(age);

4. 查询优化

避免SELECT *: 仅查询需要的字段，减少数据传输量。

分页查询: 对于大数据量的查询，使用LIMIT和OFFSET进行分页，避免一次性加载大量数据。

sql = "SELECT name, email FROM users ORDER BY id LIMIT %s OFFSET %s"
cursor.execute(sql, (10, 20))

安全性最佳实践

在数据库操作中，安全性至关重要，特别是防止SQL注入攻击。以下是一些提升PyMySQL应用安全性的最佳实践。

1. 使用参数化查询

始终使用参数化查询，避免将用户输入直接拼接到SQL语句中。

不安全的示例

# 易受SQL注入攻击
sql = f"SELECT * FROM users WHERE name = '{name}'"
cursor.execute(sql)

安全的示例

sql = "SELECT * FROM users WHERE name = %s"
cursor.execute(sql, (name,))

2. 最小权限原则

为数据库用户分配最小的权限，只授予其完成任务所需的权限，避免使用root用户进行日常操作。

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON your_dbname.* TO 'app_user'@'localhost';

3. 输入验证与清理

在处理用户输入时，进行必要的验证和清理，确保数据的合法性和安全性。

def validate_email(email):
    import re
    pattern = r'^[\w\.-]+@[\w\.-]+\.\w+$'
    return re.match(pattern, email) is not None

email = input("Enter your email: ")
if validate_email(email):
    # 进行数据库操作
    pass
else:
    print("无效的邮箱地址")

4. 使用加密连接

在生产环境中，建议使用SSL加密连接，确保数据在传输过程中的安全性。

connection = pymysql.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_dbname',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor,
    ssl={'ca': '/path/to/ca-cert.pem'}
)

主流AI大模型 + 上百种AI助手落地场景 + 兑换码ZXCODE = CodeMoss国内版

常见错误排查

在开发过程中，难免会遇到各种错误和异常。以下是一些常见的PyMySQL错误及其解决方法。

1. 连接错误

错误信息:

pymysql.err.OperationalError: (1045, "Access denied for user 'user'@'localhost' (using password: YES)")

原因:

用户名或密码错误。

用户没有访问指定数据库的权限。

解决方法:

检查数据库连接参数，确保用户名和密码正确。

确认用户拥有相应数据库的访问权限。

2. 数据库不存在

错误信息:

pymysql.err.ProgrammingError: (1049, "Unknown database 'nonexistent_db'")

原因:

指定的数据库不存在。

解决方法:

确认数据库名称是否正确。

如果数据库不存在，创建相应的数据库。

CREATE DATABASE your_dbname;

3. 表不存在

错误信息:

pymysql.err.ProgrammingError: (1146, "Table 'your_dbname.users' doesn't exist")

原因:

指定的表不存在。

解决方法:

确认表名是否正确。

如果表不存在，创建相应的表。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT,
    email VARCHAR(255)
);

4. 数据类型不匹配

错误信息:

pymysql.err.DataError: (1406, "Data too long for column 'name' at row 1")

原因:

插入的数据超过字段定义的长度。

解决方法:

检查数据是否符合字段的长度和类型要求。

调整字段长度或修正数据。

5. 事务处理错误

错误信息:

pymysql.err.InternalError: (1815, 'Deadlock found when trying to get lock; try restarting transaction')

原因:

死锁导致事务无法继续。

解决方法:

分析并优化事务逻辑，避免长时间持有锁。

在发生死锁时，捕获异常并重试事务。

import time
import pymysql

max_retries = 3
for attempt in range(max_retries):
    try:
        with connection.cursor() as cursor:
            # 执行事务操作
            pass
        connection.commit()
        break
    except pymysql.MySQLError as e:
        if e.args[0] == 1815 and attempt < max_retries - 1:
            time.sleep(1)  # 等待后重试
            continue
        else:
            connection.rollback()
            print(f"事务失败: {e}")
            break

实战案例：构建一个简单的用户管理系统

通过一个实际的案例，我们将综合运用上述所学，构建一个简单的用户管理系统，实现用户的添加、查询、更新和删除功能。

项目结构

user_management/
├── main.py
└── database.py

database.py

负责数据库连接和基本操作。

import pymysql

class Database:
    def __init__(self, host, user, password, database, charset='utf8mb4'):
        self.connection = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            charset=charset,
            cursorclass=pymysql.cursors.DictCursor
        )
    
    def execute_query(self, query, params=None):
        with self.connection.cursor() as cursor:
            cursor.execute(query, params)
            return cursor.fetchall()
    
    def execute_action(self, action, params=None):
        with self.connection.cursor() as cursor:
            cursor.execute(action, params)
        self.connection.commit()
    
    def close(self):
        self.connection.close()

主流AI大模型 + 上百种AI助手落地场景 + 兑换码ZXCODE = CodeMoss国内版

main.py

实现用户管理功能。

from database import Database

def add_user(db, name, age, email):
    sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
    db.execute_action(sql, (name, age, email))
    print(f"用户{name}添加成功！")

def get_users(db, age_threshold):
    sql = "SELECT * FROM users WHERE age > %s"
    users = db.execute_query(sql, (age_threshold,))
    for user in users:
        print(user)

def update_user_email(db, name, new_email):
    sql = "UPDATE users SET email = %s WHERE name = %s"
    db.execute_action(sql, (new_email, name))
    print(f"用户{name}的邮箱已更新为{new_email}")

def delete_user(db, name):
    sql = "DELETE FROM users WHERE name = %s"
    db.execute_action(sql, (name,))
    print(f"用户{name}已删除")

def main():
    db = Database(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_dbname'
    )
    
    while True:
        print("\n用户管理系统")
        print("1. 添加用户")
        print("2. 查询用户")
        print("3. 更新用户邮箱")
        print("4. 删除用户")
        print("5. 退出")
        
        choice = input("请选择操作（1-5）：")
        
        if choice == '1':
            name = input("请输入用户名：")
            age = int(input("请输入年龄："))
            email = input("请输入邮箱：")
            add_user(db, name, age, email)
        
        elif choice == '2':
            age_threshold = int(input("请输入年龄阈值："))
            get_users(db, age_threshold)
        
        elif choice == '3':
            name = input("请输入用户名：")
            new_email = input("请输入新的邮箱：")
            update_user_email(db, name, new_email)
        
        elif choice == '4':
            name = input("请输入用户名：")
            delete_user(db, name)
        
        elif choice == '5':
            print("退出系统。")
            break
        else:
            print("无效的选择，请重新输入。")
    
    db.close()

if __name__ == "__main__":
    main()

数据库准备

在开始运行项目之前，确保已创建相应的数据库和表。

CREATE DATABASE user_management;

USE user_management;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT,
    email VARCHAR(255)
);

运行项目

在终端中导航至user_management目录，运行以下命令启动用户管理系统：

python main.py

按照提示进行操作，即可实现用户的添加、查询、更新和删除。

总结

通过本文的学习，你已经掌握了使用PyMySQL进行MySQL数据库操作的基本方法和一些高级技巧。从连接数据库、执行CRUD操作，到事务管理和性能优化，每一个环节都为你在实际项目中高效、安全地操作数据库打下了坚实的基础。

作者：ChatGPT-千鑫

物联沃分享整理
物联沃-IOTWORD物联网 » 【Python】从零开始掌握PyMySQL数据库与的操作技巧！保姆级教程！

代码收藏家普通

分享到：

文章目录

为什么选择PyMySQL？

PyMySQL vs MySQLdb

PyMySQL的核心特点

1. 纯Python实现

2. 强大的兼容性

3. 简洁易用的API

环境准备与安装

1. 安装Python

2. 安装MySQL

3. 安装PyMySQL

基础用法详解

连接数据库

参数解释

执行查询

关键点

插入数据

单条插入

多条插入

关键点

更新与删除操作

更新数据

删除数据

关键点

高级功能与优化

事务管理

手动控制事务

关键点

游标类型选择

默认游标（Cursor）

字典游标（DictCursor）

流式游标（SSCursor）

性能优化策略

1. 使用批量操作

2. 连接池

3. 索引优化

4. 查询优化

安全性最佳实践

1. 使用参数化查询

不安全的示例

安全的示例

2. 最小权限原则

3. 输入验证与清理

4. 使用加密连接

常见错误排查

1. 连接错误

2. 数据库不存在

3. 表不存在

4. 数据类型不匹配

5. 事务处理错误

实战案例：构建一个简单的用户管理系统

项目结构

database.py

main.py

数据库准备

运行项目

更多文献

总结

代码收藏家 普通

相关推荐

发表回复 取消回复

代码收藏家普通

发表回复取消回复