数据库之七 PyMySQL模块

数据库之 PyMySQL模块

【一】简介

【1】DB-API

• DB-API 是 Python 中用于访问数据库的标准接口，它定义了一套规范，使得不同的数据库模块可以在相同的接口下工作。
• 这意味着如果你编写的代码符合 DB-API，你可以轻松地切换不同的数据库模块而无需修改太多代码。
• DB-API 提供了一组通用的数据库操作方法和规范，包括连接数据库、执行 SQL 查询、获取结果等。

【2】数据库操作模块

• 数据库操作模块是按照 DB-API 规范实现的模块，用于在 Python 中与特定数据库进行交互。
• 这些模块提供了对数据库的连接、查询、更新等操作的接口。
• 常见的数据库操作模块包括 PyMySQL（用于 MySQL）、psycopg2（用于 PostgreSQL）、sqlite3（用于 SQLite）等。它们允许开发者使用统一的方式来处理不同类型的数据库。

【3】PyMySQL简介

• PyMySQL 是一个符合 DB-API 规范的纯 Python MySQL 客户端库。
• 它允许开发者在 Python 程序中连接到 MySQL 数据库，并执行各种数据库操作。
• PyMySQL 提供了与 MySQL 服务器通信的功能，可以执行查询、插入、更新和删除等操作。
• 通过 PyMySQL，开发者可以方便地与 MySQL 数据库进行交互，而不必直接使用原始的 SQL 语句。

【二】基础操作

【1】安装

• pymsql不是内置模块，需要pip安装

pip install pymysql

【2】连接数据库

• 使用 PyMySQL，首先需要建立与 MySQL 数据库的连接。
• 连接通常需要指定主机、用户、密码和数据库等信息。

# 导入模块
import pymysql
# 创建连接对象
conn = pymysql.connect(
    # 用户名
    user='root',
    # 密码
    password='000',
    # 服务器地址，本地填写localhost或者127.0.0.1
    host='localhost',
    # 端口号，mysql默认是3306
    port=3306,
    # 连接的数据库，如果不指定将在最外层
    database='pymysql_test',
    # 返回的结果集将以字典的形式进行存储
    cursorclass=pymysql.cursors.DictCursor,
)

【3】创建游标

• 建立连接以后，可以创建游标对象
• 用于执行 SQL 查询和获取结果。

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

【4】关闭连接

• 在使用完数据库后，记得关闭连接。

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

【三】常用操作

• 连接时我们指定了数据库，即我们可以当前数据库下进行相关操作
• 以下操作代码都是在创建游标和关闭连接之间

【1】创建表

# sql语句
create_table_sql = '''
create table if not exists employee(
id int primary key auto_increment,
name varchar(10) not null,
sex enum('female', 'male') default 'female',
age int);
'''
# 执行mysql语句
cursor.execute(create_table_sql)

【2】插入数据

（1）说明

• 在 PyMySQL 中执行插入操作时，可以使用占位符 %s 来表示参数的位置。
• 这种方式可以通过元组（tuple）、列表（list）或字典（dictionary）来传递参数值。

转换说明符	解释
%d、%i	转换为带符号的十进制数
%o	转换为带符号的八进制数
%x、%X	转换为带符号的十六进制数
%e	转化为科学计数法表示的浮点数（e 小写）
%E	转化为科学计数法表示的浮点数（E 小写）
%f、%F	转化为十进制浮点数
%g	智能选择使用 %f 或 %e 格式
%G	智能选择使用 %F 或 %E 格式
%c	格式化字符及其ASCII码
%r	使用 repr() 函数将表达式转换为字符串
%s	使用 str() 函数将表达式转换为字符串

（2）元组形式

# sql语句
tuple_insert = """
insert employee(name, sex, age) 
values(%s, %s, %s)"""

# 执行sql语句
cursor.execute(tuple_insert, ('bruce', 'male', 18))

# 提交事务
conn.commit()

（3）列表形式

# sql语句
list_insert = """
insert employee(name, sex, age)
values(%s, %s, %s);"""
# 执行sql语句
cursor.execute(list_insert, ['lily', 'female', 28])
# 提交事务
conn.commit()

（3）字典形式

# sql语句
dict_insert = """
insert employee(name, sex, age)
values(%(name)s, %(sex)s, %(age)s);"""
# 执行sql语句
cursor.execute(dict_insert, {"name": 'tom', "age": 32,  "sex": 'male'})
# 提交事务
conn.commit()

（4）批量插入数据

• 数据整体为列表，具体数据用元组包裹

# sql语句
more_insert = """
insert employee(name, sex, age)
values(%s, %s, %s);"""
# 数据列表, 具体数据用元组包裹
data_list = [('lala', 'female', 22),
             ('lulu', 'male', 25),
             ('kan', 'male', 22),
             ('liuliu', 'female', 26)]
# 执行sql语句
cursor.executemany(more_insert, data_list)
# 提交事务
conn.commit()

（5）重点补充说明

• 事务主要用于控制数据的一致性和完整性，通常在涉及数据修改的操作（例如插入、更新、删除）时需要使用 commit 来提交事务。
• 而对于表的创建、删除等 DDL 操作，这些操作本身就是原子的，系统会自动处理事务。

【3】查询数据

（1）查询所有结果fetchall

• 多条数据以列表字典的形式返回

# sql语句
search_all = "select * from employee;"
# 执行SQL语句
cursor.execute(search_all)
# 拿到返回结果
res = cursor.fetchall()
# 打印结果
print(res)

[{'id': 1, 'name': 'bruce', 'sex': 'male', 'age': 18}, {'id': 2, 'name': 'lily', 'sex': 'female', 'age': 28}, {'id': 3, 'name': 'tom', 'sex': 'male', 'age': 32}, {'id': 4, 'name': 'lala', 'sex': 'female', 'age': 22}, {'id': 5, 'name': 'lulu', 'sex': 'male', 'age': 25}, {'id': 6, 'name': 'kan', 'sex': 'male', 'age': 22}, {'id': 7, 'name': 'liuliu', 'sex': 'female', 'age': 26}]

（2）获取单行数据fetchone

• 执行sql语句时

  • 是有返回结果的
  • 这里为搜索到数据总数

• 返回字典格式

# sql语句
search_one = "select * from employee;"
# 执行sql语句
row = cursor.execute(search_one)
print(f"总共有{row}条数据")
# 循环获取结果并打印
for i in range(row):
    print(cursor.fetchone())

总共有7条数据
{'id': 1, 'name': 'bruce', 'sex': 'male', 'age': 18}
{'id': 2, 'name': 'lily', 'sex': 'female', 'age': 28}
{'id': 3, 'name': 'tom', 'sex': 'male', 'age': 32}
{'id': 4, 'name': 'lala', 'sex': 'female', 'age': 22}
{'id': 5, 'name': 'lulu', 'sex': 'male', 'age': 25}
{'id': 6, 'name': 'kan', 'sex': 'male', 'age': 22}
{'id': 7, 'name': 'liuliu', 'sex': 'female', 'age': 26}

（3）获取指定量的结果fetchmany

• 获取多条数据将以列表字典的形式范围
• 指定条数超出范围不会保存

# sql语句
search_many = "select * from employee;"
# 执行sql语句
get_row = cursor.execute(search_many)
# 循环打印，每次获取两条结果
for i in range((get_row + 1) // 2):
    print(cursor.fetchmany(2))

[{'id': 1, 'name': 'bruce', 'sex': 'male', 'age': 18}, {'id': 2, 'name': 'lily', 'sex': 'female', 'age': 28}]
[{'id': 3, 'name': 'tom', 'sex': 'male', 'age': 32}, {'id': 4, 'name': 'lala', 'sex': 'female', 'age': 22}]
[{'id': 5, 'name': 'lulu', 'sex': 'male', 'age': 25}, {'id': 6, 'name': 'kan', 'sex': 'male', 'age': 22}]
[{'id': 7, 'name': 'liuliu', 'sex': 'female', 'age': 26}]

（4）移动光标scroll

• 语法操作

  • 数据库的结果集通常是单向的，即从头到尾的顺序读取

  • # 相对于起始位置位置往后移动
    cursor.scroll(1, 'absolute')
    

  • # 相对当前位置往后移动
    cursor.scroll(1, 'relative')
    

• 移动范围

  • 移动范围不能超过管道大小，不然会报错

• 返回的结果可以看成是一个管道

  • 读取出的数据就会从管道中删除

• 光标就是正要输出的数据的下表

  • 可以控制光标的位置
  • 来实现要读取哪些数据

• 使用fetchone和scoll实现打印偶数编号员工

# sql语句
search_scroll = 'select * from employee;'
# 执行sql语句
row_sum = cursor.execute(search_scroll)
# 超出范围会报错
# cursor.scroll(row_sum+1)
# 使用光标移动打印出编号为偶数的员工信息
i = 1
while i < row_sum:
    cursor.scroll(1, 'relative')
    print(cursor.fetchone())
    i += 2

{'id': 2, 'name': 'lily', 'sex': 'female', 'age': 28}
{'id': 4, 'name': 'lala', 'sex': 'female', 'age': 22}
{'id': 6, 'name': 'kan', 'sex': 'male', 'age': 22}

【4】更新操作

（1）要求

• 将名字是四位字符的员工年龄改成18岁

（2）操作

• 打印修改前的四位字符的员工信息

# 修改前的信息
search_sql1 = """
select * from employee
where char_length(name) = 4;"""
cursor.execute(search_sql1)
print("修改前的信息：")
print(cursor.fetchall())

修改前的信息：
[{'id': 2, 'name': 'lily', 'sex': 'female', 'age': 28}, {'id': 4, 'name': 'lala', 'sex': 'female', 'age': 22}, {'id': 5, 'name': 'lulu', 'sex': 'male', 'age': 25}]

• 执行修改操作

# sql语句
update_sql = """
update employee
set age = %s
where name regexp '....';"""
# 执行sql语句
cursor.execute(update_sql, 18)
# 提交修改事务
conn.commit()

• 查看修改后的信息

search_sql2 = """
select * from employee
where name like '____';"""
cursor.execute(search_sql2)
print("修改后的信息：")
print(cursor.fetchall())

修改后的信息：
[{'id': 2, 'name': 'lily', 'sex': 'female', 'age': 18}, {'id': 4, 'name': 'lala', 'sex': 'female', 'age': 18}, {'id': 5, 'name': 'lulu', 'sex': 'male', 'age': 18}]

【5】删除操作

（1）要求

• 删除年龄超过22岁的员工信息

（2）操作

• 查询年龄大于22岁的员工信息

# 查询sql
search_sql = """
select * 
from employee
where age > %s;"""
cursor.execute(search_sql, 22)
print(f"要删除的员工信息：")
print(cursor.fetchall())

要删除的员工信息：
[{'id': 3, 'name': 'tom', 'sex': 'male', 'age': 32}]

• 执行删除操作

# 删除sql
delete_sql = """
delete from employee
where age > %s;"""
# 执行sql语句
cursor.execute(delete_sql, 22)
# 提交事务
conn.commit()

【四】SQL注入问题

【1】简单实现登录注册

• 需要补充新的传递参数办法

login_sql = "select * from u_p where username='%s' and password = '%s'" % (username, password)

• 实现登录注册代码

# 导入模块
import pymysql

# 创建连接
conn = pymysql.connect(
    user='root',
    password='000',
    host='localhost',
    port=3306,
    cursorclass=pymysql.cursors.DictCursor
)
cursor = conn.cursor()

# 创建user_info库
cursor.execute('create database if not exists user_info;')
conn.commit()

# 切换到数据库
cursor.execute('use user_info;')
conn.commit()

# 创建用户密码表
cursor.execute('create table if not exists u_p(username varchar(20) unique, password varchar(10));')
conn.commit()


# 获取用户名和密码
def get_username_password():
    while True:
        username = input("用户名:>>>").strip()
        password = input("密码:>>>").strip()
        # 检查长度是否符合要求
        if (0 < len(username) < 20) and (0 < len(password) < 20): break
        print("用户名或密码不能为空也不能超过20个字符")
        continue
    return username, password


# 注册
def register():
    # 获取用户名和密码
    username, password = get_username_password()
    # 查看是否已经注册
    get_info_sql = 'select * from u_p;'
    num = cursor.execute(get_info_sql)
    tag = False
    for i in range(num):
        if username == cursor.fetchone()["username"]:
            tag = True
            break
    if tag: return f"{username} 已经注册，请更换用户名或者登录"
    # 填写注册信息
    register_sql = "insert u_p values(%s, %s);"
    cursor.execute(register_sql, (username, password))
    conn.commit()
    return f"{username} 注册成功"


# 登录
def login():
    # 先判断是否有人注册
    row = cursor.execute('select * from u_p;')
    if not row: return "当前还没有用户注册，请先注册"
    # 获取登录信息
    username, password = get_username_password()
    # 登录sql
    login_sql = "select * from u_p where username='%s' and password = '%s'" % (username, password)
    print(login_sql)
    row = cursor.execute(login_sql)
    if not row: return f"{username} 还没有注册，请先注册或者更换登陆信息"
    return f"{username} 登录成功"


# 功能字典
func_dict = {
    "1": register,
    "2": login
}
while True:
    print("登陆注册模拟器".center(40, "="))
    # 输入选择
    choice = input("输入【1】注册，【2】登录，【q】退出:>>>").strip()
    # 选择退出
    if choice.lower() == 'q': break
    # 选择错误
    if not choice.isdigit() or not func_dict.get(choice): continue
    res = func_dict.get(choice)()
    print(res)

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

【2】问题演示

（1）正常结果

• 直接登录提示注册，正常

================登陆注册模拟器=================
输入【1】注册，【2】登录，【q】退出:>>>2
当前还没有用户注册，请先注册

• 注册，正常

================登陆注册模拟器=================
输入【1】注册，【2】登录，【q】退出:>>>1
用户名:>>>bruce
密码:>>>000
bruce 注册成功

• 重复注册检测，正常

================登陆注册模拟器=================
输入【1】注册，【2】登录，【q】退出:>>>1
用户名:>>>bruce
密码:>>>000
bruce 已经注册，请更换用户名或者登录

• 登录，正常

================登陆注册模拟器=================
输入【1】注册，【2】登录，【q】退出:>>>2
用户名:>>>bruce
密码:>>>000
bruce 登录成功

• 退出，正常

================登陆注册模拟器=================
输入【1】注册，【2】登录，【q】退出:>>>q

（2）出现的问题一

• 知道用户名不知道密码的情况下，登录成功
  • 登录的时候输入以下内容会登录成功
  • 注意一定要在–后面空格

bruce' -- 任意内容

================登陆注册模拟器=================
输入【1】注册，【2】登录，【q】退出:>>>2
用户名:>>>bruce' -- aaa
密码:>>>456
bruce' -- aaa 登录成功

（3）出现的问题二

• 不知道用户名也不知道密码的情况下，登陆成功
  • 登录的时候输入以下内容会登录成功
  • 注意一定要在–后面空格

任意字符' or 1=1 -- 任意内容

================登陆注册模拟器=================
输入【1】注册，【2】登录，【q】退出:>>>2
用户名:>>>tom' or 1=1 -- a
密码:>>>456
tom' or 1=1 -- a 登录成功

【3】解释问题

• 让我们在代码登录sql语句后面，添加输出内容

# 登录sql
login_sql = "select * from u_p where username='%s' and password = '%s'" % (username, password)
print(login_sql)

（1）问题一解释

• 打印的信息如下

select * from u_p where username='bruce' -- aaa' and password = 'aaa'

• 可以很容易的发现在username='bruce'的后面
• 紧跟着注释符号--
• 这就导致了无需验证密码
• 即只需要知道注册过的用户名就可以登录

（2）问题二解释

• 打印信息如下

select * from u_p where username='tom' or 1=1 -- a' and password = '456'

• 同样可以很容易的发现在username='tom'的后面
• 紧跟着一个恒成立条件1=1
• 然后同样跟着注释符号--
• 这就导致了无需验证用户名和密码都可以登录
• 因为有一个恒成立条件在里面1=1

【4】解决办法

（1）注意

• 使用参数化传递参数
  • 即之前说的元组、列表、字典
• 不要拼接
  • 拼接也极容易出问题

（2）解决代码

• 将登录sql部分代码修改如下

# 登录
def login():
    # 先判断是否有人注册
    row = cursor.execute('select * from u_p;')
    if not row: return "当前还没有用户注册，请先注册"
    # 获取登录信息
    username, password = get_username_password()
    # 登录sql
    login_sql = "select * from u_p where username=%s and password = %s"
    row = cursor.execute(login_sql, (username, password))
    if not row: return f"{username} 还没有注册，请先注册或者更换登陆信息"
    return f"{username} 登录成功"

【五】回滚

【1】说明

• 在数据库操作中，事务是一系列的数据库操作，要么全部执行成功，要么全部失败，保持数据库的一致性。
• 回滚是事务的一部分，它是用来撤销事务中已经执行的操作，将数据库恢复到事务开始之前的状态。

【2】使用

• 如果在执行数据库操作过程中发生了错误或者其他异常情况，可以通过执行 ROLLBACK 语句或者使用数据库连接对象的 rollback() 方法来回滚事务。
• 回滚会撤销事务中所有未提交的操作，将数据库状态还原到事务开始之前的状态。

import pymysql

# 创建数据库连接
connection = pymysql.connect(）

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

try:
    # 开始事务
    connection.begin()

    # 执行一些数据库操作
    cursor.execute("", (value, ))

    # 提交事务
    connection.commit()

except Exception as e:
    # 发生异常时回滚事务
    print(f"Error: {e}")
    connection.rollback()

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