35. PyMySQL
1. PyMySQL模块介绍
1.1 什么是DB-API
Python标准数据库规范为 DB-API， DB-API定义了一系列必须的对象和数据库操作方式，以便为各种数据库系统和数据库访问程序提供一致的访问接口。
1.2 数据库操作模块
DB-API接口封装成数据库操作模块，PyMySQL是python操作MySQL数据库的一种模块。
PyMySQL官网
https://pypi.org/project/PyMySQL/
2. PyMySQL安装
PyMySQL不是从官网安装，python解释器的安装路径已经被添加到系统环境变量，直接在全局pip install PyMySQL即可
3. PyMySQL使用
3.1 操作前准备
import pymysql
from pymysql.cursors import DictCursor, Cursor
# 建立数据库连接
conn_obj = pymysql.connect(
user='root',
password='123456789',
database='test01', # 数据库名称
host='127.0.0.1',
port=3306,
charset='utf8mb4', # 数据库的编码格式
cursorclass=DictCursor # DictCursor返回的数据是字典类型(带有列名)，Cursor返回的数据是元组类型(不带列名)
)
# 产生游标对象
cursor_obj = conn_obj.cursor()
3.2 查询数据
[1]查询所有数据 fetchall
DictCursor返回的数据是字典类型
sql = "select * from dep;" # SQL语句会被高亮显示
cursor_obj.execute(sql) # 执行SQL语句
result = cursor_obj.fetchall()
print(result)
[2]查询一条数据 fetchone
每一个fetchone只获取一条数据，下一个fetchone基于上一个fetchone向后获取
sql = "select * from dep;" # SQL语句会被高亮显示
cursor_obj.execute(sql) # 执行SQL语句
result = cursor_obj.fetchone()
print(result)
result2 = cursor_obj.fetchone() # 基于上一个fetchone向后获取
print(result2)
[3]查询指定条数数据 fetchmany
sql = "select * from dep;" # SQL语句会被高亮显示
cursor_obj.execute(sql) # 执行SQL语句
result = cursor_obj.fetchmany() # 默认获取1条数据
print(result)
result2 = cursor_obj.fetchmany(2) # 获取指定条数2条
print(result2)
[4]移动光标 scroll
cursor_obj.scroll(value, mode)
value---移动位置的数量
mode---relative：相对位置移动，absolute：以开头为基准绝对位置移动
relative应用：
sql = "select * from dep;" # SQL语句会被高亮显示
cursor_obj.execute(sql) # 执行SQL语句
res1 = cursor_obj.fetchone()
print(res1)
cursor_obj.scroll(1, 'relative') # 光标向后移动了1个位置
res2 = cursor_obj.fetchone()
print(res2)
absolute应用：
sql = "select * from dep;" # SQL语句会被高亮显示
cursor_obj.execute(sql) # 执行SQL语句
res1 = cursor_obj.fetchone()
print(res1)
cursor_obj.scroll(0, 'absolute') # 相对于开头移动了0个位置
res2 = cursor_obj.fetchone()
print(res2)
cursor_obj.scroll(1, 'absolute') # 相对于开头移动了1个位置
res3 = cursor_obj.fetchone()
print(res3)
3.3 插入数据
[1]表结构
[2]尝试插入数据
sql = "insert into dep(name) values ('电脑')"
cursor_obj.execute(sql) # 执行该sql语句之后在数据库中并没有插入数据
[3]需要commit提交事务才能成功插入数据
sql = "insert into dep(name) values ('电脑')"
cursor_obj.execute(sql)
conn_obj.commit()
由于步骤3是在pycharm中在步骤2的基础上新增一行代码实现，缓存存在，所以步骤3的id是在缓存的基础上增加
手动conn_obj.commit( )的效果与connect( )的默认参数autocommit=False改为True一致，二选一即可
[4]插入字符串类型，一定要给值的中括号加引号' '
字符串类型{ }不加引号会报错
value1 = 205
value2 = '电脑'
sql = f"insert into dep(id, name) values({value1}, {value2})"
cursor_obj.execute(sql)
conn_obj.commit()
字符串类型{ }加上引号正常插入数据
value1 = 205
value2 = '电脑'
sql = f"insert into dep(id, name) values({value1}, '{value2}')"
cursor_obj.execute(sql)
conn_obj.commit()
[5]为了解决步骤4给字符串类型{ }加引号问题，pymysql提供了sql的格式化输出语法
执行sql语句关键字execute的解释
def execute(self, query, args=None):
:param query: Query to execute. query为需要执行的sql语句，字符串类型
:type query: str
:param args: Parameters used with query. (optional) args可以是元组、列表、字典
:type args: tuple, list or dict
If args is a list or tuple, %s can be used as a placeholder in the query.
如果args是列表或元组，%s可以被用于占位 按位置传参
If args is a dict, %(key)s can be used as a placeholder in the query.
如果args是字典，则%（key）s可以用作查询中的占位符。 按关键字传参
方法一：按位置传入参数
value1 = 207
value2 = 'internet'
sql = f"insert into dep(id, name) values(%s, %s)"
cursor_obj.execute(sql, [value1, value2])
conn_obj.commit()
转换说明符 解释
%d、%i 转换为带符号的十进制数
%o 转换为带符号的八进制数
%x、%X 转换为带符号的十六进制数
%e 转化为科学计数法表示的浮点数（e 小写）
%E 转化为科学计数法表示的浮点数（E 小写）
%f、%F 转化为十进制浮点数
%g 智能选择使用 %f 或 %e 格式
%G 智能选择使用 %F 或 %E 格式
%c 格式化字符及其ASCII码
%r 使用 repr() 函数将表达式转换为字符串
%s 使用 str() 函数将表达式转换为字符串
通常使用%s也能将python的int类型转换为MySQL中的int类型
方法二：按关键字传入参数
sql = f"insert into dep(id, name) values(%(v1)s, %(v2)s)"
cursor_obj.execute(sql, {'v1': 208, 'v2': 'develop'})
conn_obj.commit()
3.4 更新数据
sql = "update dep set name='research' where name='develop'"
cursor_obj.execute(sql)
conn_obj.commit()
3.5 删除数据
sql = "delete from dep where id=207"
cursor_obj.execute(sql)
conn_obj.commit()
3.6 使用executemany一次插入多条数据
[1]按位置传入
sql = "insert into dep(id, name) values(%s, %s);"
cursor_obj.executemany(sql, [(212, 'd'), (213, 'e'), (214, 'f')])
conn_obj.commit()
[2]按关键字传入
sql = "insert into dep(id, name) values(%(v1)s, %(v2)s);"
cursor_obj.executemany(sql, [{'v1': 215, 'v2': 'g'}, {'v1': 216, 'v2': 'h'}, {'v1': 217, 'v2': 'i'}])
conn_obj.commit()
3.7 SQL注入问题
[1]字符串格式化输出+引号进行占位传值
模拟登录：输入用户名和密码
login_name = input('请输入用户名:')
login_pwd = input('请输入密码:')
sql = f"select * from info where username='{login_name}' and password='{login_pwd}';"
print(f'sql语句为{sql}')
cursor_obj.execute(sql)
res = cursor_obj.fetchall()
print(res)
if res:
print('登录成功')
else:
print('登录失败')
[2]正常输入用户名密码进行登录
[3]SQL注入问题：-- 空格后面的内容全部为注释内容
输入用户名时在用户名后面加上-- ，--后面的内容全部变成了注释，导致sql语句失效
[4]解决SQL注入问题的本质是处理特殊符号
execute方法自带校验SQL注入问题，自动处理特殊符号
login_name = input('请输入用户名:')
login_pwd = input('请输入密码:')
sql = f"select * from info where username=%s and password=%s;"
print(f'sql语句为{sql}')
cursor_obj.execute(sql, (login_name, login_pwd))
res = cursor_obj.fetchall()
print(res)
if res:
print('登录成功')
else:
print('登录失败')
