python操作MySQL的详细教程_Python

前言

pymysql 是一个纯 python 的库，用于连接 mysql 数据库，并执行 sql 语句。它是 mysqldb 的替代品，但不同于后者，pymysql 不需要 c 语言的依赖，因此更加轻量且易于安装和使用。该库的主要用途是通过 python 代码与 mysql 数据库进行交互，比如执行查询、插入数据、更新数据、删除数据等操作。

一、pymysql的特点

该库的特点主要有以下三点：

纯 python 实现：不需要依赖 c 扩展库，可以轻松在各类系统上使用，如 windows、linux、macos 等。
兼容性强：支持 mysql 5.x 和 mysql 8.x 版本，也兼容 mariadb。
易用性：提供了与 mysqldb 类似的 api，便于用户从 mysqldb 迁移过来。

二、安装

pymysql 可以通过 pip 轻松安装：

pip install pymysql

三、基本用法

（一）连接mysql数据库

在使用 pymysql 之前，你需要先连接到 mysql 数据库。连接数据库时，通常需要提供数据库的主机地址、用户名、密码、数据库名等信息。

示例：

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()

（二）数据查询

使用游标对象来执行 sql 查询并获取数据。

示例：

try:
    # 创建游标
    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)
finally:
    # 关闭连接
    connection.close()

（三）插入数据

执行插入操作时，可以使用 execute() 或 executemany() 方法。

示例：

try:
    with connection.cursor() as cursor:
        # sql 插入语句
        sql = "insert into users (name, age, email) values (%s, %s, %s)"
        # 执行单条插入
        cursor.execute(sql, ('john doe', 30, 'john.doe@example.com'))
        
        # 执行多条插入
        users = [
            ('alice', 25, 'alice@example.com'),
            ('bob', 28, 'bob@example.com')
        ]
        cursor.executemany(sql, users)
    
    # 提交更改
    connection.commit()
finally:
    connection.close()

（四）更新和删除数据

类似于插入数据，更新和删除数都是通过 sql 语句和 execute() 函数来完成。

更新数据

示例：

try:
    with connection.cursor() as cursor:
        # sql 更新语句
        sql = "update users set email = %s where name = %s"
        cursor.execute(sql, ('new.email@example.com', 'john doe'))
    
    # 提交更改
    connection.commit()
finally:
    connection.close()

删除数据

示例：

try:
    with connection.cursor() as cursor:
        # sql 删除语句
        sql = "delete from users where name = %s"
        cursor.execute(sql, ('john doe',))
    
    # 提交更改
    connection.commit()
finally:
    connection.close()

（五）事务管理

mysql 数据库支持事务，pymysql 默认启用了自动提交。如果你需要手动控制事务，可以关闭自动提交并在合适的时机提交或回滚。

示例：

try:
    # 关闭自动提交
    connection.autocommit(false)
 
    with connection.cursor() as cursor:
        # sql 插入语句
        sql = "insert into users (name, age, email) values (%s, %s, %s)"
        cursor.execute(sql, ('chris', 27, 'chris@example.com'))
 
        # 人为错误，测试回滚
        raise exception("人为触发异常")
 
    # 提交事务
    connection.commit()
except exception as e:
    print(f"出现错误: {e}")
    # 回滚事务
    connection.rollback()
finally:
    connection.close()

四、游标类型

pymysql 提供了多种游标类型，适用于不同的场景：

默认游标：返回元组格式的结果。
字典游标 (dictcursor)：返回字典格式的结果，字段名作为键。
sscursor：流式游标，用于处理大数据量时，避免一次性加载大量数据到内存。

使用不同游标类型可以通过 cursorclass 参数指定。例如：

# 字典游标
connection = pymysql.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_dbname',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.dictcursor  # 使用字典游标
)

五、安全性

为了防止 sql 注入攻击，务必使用参数化查询，而不是将参数直接拼接到 sql 字符串中。

示例：

# 不安全的写法
sql = f"select * from users where name = '{name}'"  # 容易导致 sql 注入
 
# 安全的写法
sql = "select * from users where name = %s"
cursor.execute(sql, (name,))

六、常见错误处理

pymysql 中常见的错误处理可以通过捕获异常来完成：

import pymysql
 
try:
    connection = pymysql.connect(host='localhost', user='root', password='', database='test_db')
    cursor = connection.cursor()
except pymysql.mysqlerror as e:
    print(f"数据库连接失败: {e}")