一、简介
概述
它是一个第三方包, 主要用于实现: 通过Python代码, 操作MySQL数据库的
环境搭建
方式1: 导包时, 自动安装.
方式2: 通过Anaconda数据科学库进行安装.
方式3: 通过Anaconda的PowerShell窗口, 以管理员的身份运行, 并安装
操作步骤
- 获取连接对象.
- 获取游标对象.
- 执行SQL语句, 获取结果集.
[加一步: 提交事务, 只针对于更新语句(增删改)有效] - 操作结果集.
- 释放资源.
准备动作
-- 查看所有的数据库.
show databases ;
-- 建库, 切库
create database practice charset 'utf8';
use practice;
-- 查看所有的数据表
show tables;
-- 建表, 添加表数据.
create table student(
id int primary key auto_increment, # id, 主键自增.
name varchar(20), # 学生姓名.
gender varchar(10) # 学生性别
);
-- 添加表数据
insert into student values(null, '乔峰', '男'), (null, '虚竹', '男'), (null, '王语嫣', '女');
-- 查看表数据.
select * from student;
基本流程
# 导包.
import pymysql
# main
if __name__ == '__main__':
# 1. 获取连接对象.
conn = pymysql.connect(
host='localhost', # 主机地址, 默认是本机, 可以写localhost 或者 127.0.0.1
port=3306, # 端口号
user='root', # 账号
password='123456', # 密码
database='practice', # 要连接到的数据库名称
charset='utf8' # 码表
)
# print(conn)
# 2. 获取游标对象.
cursor = conn.cursor()
# 3. 执行SQL语句, 获取结果集.
sql = 'select * from student;'
# 执行SQL语句.
cursor.execute(sql)
# 获取结果集, 即: 把每行数据封装成元组, 然后整体放到1个元组中, 即: 元组嵌套元组的形式.
results = cursor.fetchall()
# 4. 操作结果集.
print(type(results))
print(results)
# 5. 释放资源.
cursor.close()
conn.close()
二、相关函数
格式
PyMysql 查询 相关的函数, 即: cursor(游标对象)的函数
fetchall() 获取(查询)SQL语句的所有内容, 每行数据会封装成1个元组, 然后再把所有数据封装到1个元组中, 即: 元组嵌套元组的形式.
fetchmany(n) 一次读取n条数据, 不写默认读取 1条 数据.
fetchone() 一次获取(第)一条数据.
例如
# 导包.
import pymysql
# main
if __name__ == '__main__':
# 1. 获取连接对象.
conn = pymysql.connect(
host='localhost', # 主机地址, 默认是本机, 可以写localhost 或者 127.0.0.1
port=3306, # 端口号
user='root', # 账号
password='123456', # 密码
database='practice', # 要连接到的数据库名称
charset='utf8' # 码表
)
# print(conn)
# 2. 获取游标对象.
cursor = conn.cursor()
# 3. 执行SQL语句, 获取结果集.
sql = 'select * from student;'
# 执行SQL语句.
cursor.execute(sql)
# 获取结果集, 即: 把每行数据封装成元组, 然后整体放到1个元组中, 即: 元组嵌套元组的形式.
# results = cursor.fetchall() # 一次获取所有的数据.
# results = cursor.fetchmany() # 不写数量, 默认: 一次读取一条数据.
# results = cursor.fetchmany(2) # 读取(前)2条数据.
results = cursor.fetchone() # 一次读取(第)一条数据
# 4. 操作结果集.
print(type(results))
print(results)
# 5. 释放资源.
cursor.close()
conn.close()
三、CURD
细节
-
PyMysql操作SQL语句, 如果是 查询操作, 则: 需要通过 fetchall(), fetchone(), fetchmany(n) 来获取数据.
-
PyMysql操作SQL语句, 如果是 更新操作(增删改), 则: 直接返回的是 受影响的函数.
-
PyMysql操作更新语句, 则: 必须提交事务, 结果才会写到 数据库中.
(一)、增
例如
import pymysql
# PyMysql 增
def fun01():
# 1. 获取连接对象. 主机地址 端口号 账号 密码 要连接到的数据库名 码表
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='practice', charset='utf8')
# print(conn)
# 2. 获取游标对象.
cursor = conn.cursor()
# 3. 执行SQL语句, 获取结果集.
sql = "insert into student values(null, '刘亦菲', '女'), (null, '赵丽颖', '女');"
result = cursor.execute(sql) # 返回的是 受影响的行数
# print(f"受影响的行数: {result}")
# 4. 提交事务, 只针对于 更新语句(增删改) 有效.
conn.commit()
# 5. 操作结果集.
print('添加成功!' if result > 0 else '添加失败!')
# 6. 释放资源.
cursor.close()
conn.close()
(二)、删
例如
# PyMysql 删
def fun02():
# 1. 获取连接对象. 主机地址 端口号 账号 密码 要连接到的数据库名 码表
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='practice',charset='utf8')
# 2. 获取游标对象.
cursor = conn.cursor()
# 3. 执行SQL语句, 获取结果集.
sql = "delete from student where id=4;"
result = cursor.execute(sql) # 返回的是 受影响的行数
# 4. 提交事务, 只针对于 更新语句(增删改) 有效.
conn.commit()
# 5. 操作结果集.
print('删除成功!' if result > 0 else '删除失败!')
# 6. 释放资源.
cursor.close()
conn.close()
(三)、改
例如
# PyMysql 改
def fun03():
# 1. 获取连接对象. 主机地址 端口号 账号 密码 要连接到的数据库名 码表
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='practice', charset='utf8')
# 2. 获取游标对象.
cursor = conn.cursor()
# 3. 执行SQL语句, 获取结果集.
sql = "update student set name='乔峰' where id = 1;"
result = cursor.execute(sql) # 返回的是 受影响的行数, 如果未修改数据, 则返回: 0
# 4. 提交事务, 只针对于 更新语句(增删改) 有效.
conn.commit()
# 5. 操作结果集.
print('修改成功!' if result > 0 else '修改失败!')
# 6. 释放资源.
cursor.close()
conn.close()
(四)、查
例如
# PyMysql 查
def fun04():
# 1. 获取连接对象. 主机地址 端口号 账号 密码 要连接到的数据库名 码表
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='practice', charset='utf8')
# 2. 获取游标对象.
cursor = conn.cursor()
# 3. 执行SQL语句, 获取结果集.
sql = "select id, name from student where id <= 5;"
cursor.execute(sql)
results = cursor.fetchall() # 获取结果集中所有的数据.
# 4. 提交事务, 只针对于 更新语句(增删改) 有效. 针对于查询语句 无效.
# 这里无需提交事务.
# 5. 操作结果集.
print(f'结果集: {results}') # 格式: ((第1行数据), (第2行数据), ...)
# 6. 释放资源.
cursor.close()
conn.close()
四、抽取公共函数
概述
把重复的动作定义到工具类中。
例如
# 导包.
import pymysql
# 1. 自定义函数, 获取连接对象.
def get_conn(database_name):
"""
获取连接对象的
:param database_name: 要连接到的数据库名
:return: PyMysql的连接对象.
"""
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
database=database_name,
charset='utf8')
return conn
# 2. 自定义函数, 获取游标对象.
def get_cursor(database_name):
"""
获取游标对象的
:param database_name: 要连接到的数据库名
:return: 游标对象
"""
# 2.1 调用自定义的 get_conn()函数, 获取连接对象.
conn = get_conn(database_name)
# 2.2 获取游标对象
cursor = conn.cursor()
# 2.3 返回游标对象.
return cursor
# return cursor, conn # 同时返回游标对象, 连接对象.
# 3.自定义函数, 用于释放资源.
def release(cursor, conn):
"""
关闭连接 和 游标对象, 释放资源
:param cursor: 游标对象
:param conn: 连接对象
:return: 无
"""
# 如果对象不为空, 就关闭.
if cursor:
cursor.close()
if conn:
conn.close()
# 4. 定义通用的函数, 用于执行 更新语句.(增删改)
def my_execute_update(database_name, sql):
# 4.0 获取连接对象.
conn = get_conn(database_name)
# 4.1 获取游标对象.
# 方式1: 通过自定义函数, 获取游标.
# cursor = get_cursor(database_name)
# 方式2: 通过连接对象, 获取游标.
cursor = conn.cursor()
# 4.2 执行sql语句, 获取结果集(受影响行数).
rows = cursor.execute(sql)
# 4.3 提交事务.
conn.commit()
# 4.4 打印结果.
print('操作成功!' if rows > 0 else '操作失败!')
# 4.5 释放资源
release(cursor, conn)
# 5. 定义通用的函数, 用于执行 查询语句.
def my_execute_query(database_name, sql):
# 5.1 获取游标对象.
cursor = get_cursor(database_name)
# 5.2 执行sql语句.
cursor.execute(sql)
# 5.3 获取查询结果.
results = cursor.fetchall()
# 5.4 释放资源
release(cursor, None)
# 5.5 返回查询结果.
return results
五、注入攻击
(一)、模拟
介绍
实际开发中, 如果我们的SQL语句中 部分内容是由用户键盘录入的, 此时如果用户录入一些非法值, 就很有可能改变了我们SQL语句的结构, 从而引发一系列的安全问题, 这些安全问题就是: SQL注入攻击
模拟登录
-- 模拟登陆, 语法格式如下
select * from users where username='用户录入的账号' and password='用户录入的密码';
-- 可能发生的情况
-- 场景1: 小白用户. 录入的账号: abc 录入的密码: xyz
select * from users where username='abc' and password='xyz'; -- 合法的SQL
-- 场景2: 懂点技术的用户. 录入的账号: abc 录入的密码: xyz ' or '1=1
select * from users where username='abc' and password='xyz ' or '1=1';
准备动作
-- 1. 建库, 切库, 查表.
create database if not exists day10;
use day10;
show tables;
-- 2. 建表, 添加表数据, 并查看数据.
create table users(
id int primary key auto_increment, # id, 主键自增
username varchar(20), # 账号(用户名)
password varchar(20) # 密码
);
insert into users values(null, 'admin01', 'pwd111'), (null, 'admin02', 'pwd222');
select * from users;
-- 场景1: 小白用户. 录入的账号: abc 录入的密码: xyz
select * from users where username='abc' and password='xyz'; -- 合法的SQL
-- 场景2: 懂点技术的用户. 录入的账号: abc 录入的密码: xyz ' or '1=1
select * from users where username='abc' and password='xyz ' or '1=1';
python代码
"""
SQL注入攻击介绍:
概述:
实际开发中, 如果我们的SQL语句中 部分内容是由用户键盘录入的, 此时如果用户录入一些非法值,
就很有可能改变了我们SQL语句的结构, 从而引发一系列的安全问题, 这些安全问题就是: SQL注入攻击.
例如: 模拟登陆
-- 模拟登陆, 语法格式如下
select * from users where username='用户录入的账号' and password='用户录入的密码';
-- 可能发生的情况
-- 场景1: 小白用户. 录入的账号: abc 录入的密码: xyz
select * from users where username='abc' and password='xyz'; -- 合法的SQL
-- 场景2: 懂点技术的用户. 录入的账号: abc 录入的密码: xyz ' or '1=1
select * from users where username='abc' and password='xyz ' or '1=1';
"""
# 导包
import pymysql
# 1. 提示用户录入他/她的账号和密码, 并接收.
uname = input('请录入您的账号: ')
pwd = input('请录入您的密码: ')
# 2. 创建连接对象.
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='day10', charset='utf8')
# 3. 创建游标对象.
cursor = conn.cursor()
# 4. 拼接 模拟登陆的 SQL语句.
# 写法1: 普通写法, 直接拼接即可.
# sql = "select * from users where username='"+ uname +"' and password='"+ pwd +"';"
# 写法2: 占位符思想
# sql = "select * from users where username='%s' and password='%s';" % (uname, pwd)
# 写法3: 格式化思想, 插值表达式.
sql = f"select * from users where username='{uname}' and password='{pwd}';"
# print(sql)
# 5. 执行(查询)SQL语句, 获取结果集.
cursor.execute(sql)
result = cursor.fetchall()
# 6. 处理(操作)结果集. 即: 结果集有数据就提示登录成功, 无数据则提示登录失败.
# 方式1: 根据 结果集的长度 判断.
# if.else 语句写法
# if len(result) > 0:
# print(f'登陆成功, 欢迎您, {uname}!')
# else:
# print('登陆失败!')
# 三元表达式写法.
# print(f'登陆成功, 欢迎您, {uname}!' if len(result) > 0 else '登陆失败!')
# 方式2: 根据 结果集的内容 判断.
print(f'登陆成功, 欢迎您, {uname}!' if result else '登陆失败!')
# 7. 释放资源.
cursor.close()
conn.close()
(二)、解决
思想
采用**预编译思想(占位符)**来解决, 即: 预先对SQL语句进行编译, 之后无论传入什么, 都只会当做普通字符来处理.
例如
"""
PyMysql解决SQL注入攻击的思想:
采用预编译思想(占位符)来解决, 即: 预先对SQL语句进行编译, 之后无论传入什么, 都只会当做普通字符来处理.
"""
# 导包
import pymysql
# 1. 提示用户录入他/她的账号和密码, 并接收.
uname = input('请录入您的账号: ')
pwd = input('请录入您的密码: ')
# 2. 创建连接对象.
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='day10', charset='utf8')
# 3. 创建游标对象.
cursor = conn.cursor()
# 4. 拼接 模拟登陆的 SQL语句.
# 预编译思想 结合占位符, 即: 这一步已经把SQL语句的格式给确定了(预编译过了), 之后无论传入什么, 都只会当做普通字符来处理.
# sql = "select * from users where username='%s' and password='%s';" % (uname, pwd) # 这种是 普通的 占位符思想, 没有预编译, 不会解决SQL注入攻击问题.
# 预编译的时候, 用 %s 来占位, 在执行SQL语句的时候, 可以给这些占位符传值..
sql = "select * from users where username=%s and password=%s;" # 这种是 预编译思想 + 占位符, 能解决SQL注入攻击问题.
# print(sql)
# 5. 执行(查询)SQL语句, 获取结果集.
cursor.execute(sql, (uname, pwd)) # 在执行SQL语句的时候, 用容器类型(列表, 元组等)给 预编译时的占位符传值.
result = cursor.fetchall()
# 6. 处理(操作)结果集. 即: 结果集有数据就提示登录成功, 无数据则提示登录失败.
# 方式1: 根据 结果集的长度 判断.
# print(f'登陆成功, 欢迎您, {uname}!' if len(result) > 0 else '登陆失败!')
# 方式2: 根据 结果集的内容 判断.
print(f'登陆成功, 欢迎您, {uname}!' if result else '登陆失败!')
# 7. 释放资源.
cursor.close()
conn.close()
六、操作事务
(一)、事务介绍
概述
事务**(transaction)**指的是逻辑上的一组操作, 组成该操作的各个逻辑单元, 要么全部执行成功, 要么全部执行失败.
大白话记忆: 同生同死.
例如
转账, 乔峰 => 虚竹 转 1000 元
-
动作1: 乔峰账号 - 1000元
-
动作2: 虚竹账号 + 1000元
上述的两个动作 = 2个逻辑单元, 合起来, 就是完整的 转账动作
特点
- 原子性
指的是: 组成事务的各个逻辑单元已经是最小单位, 不可分割.
- 一致性
指的是: 事务执行前后, 数据应该保持一致.
- 隔离性
指的是: 事务之间是相互隔离的, 一个事务执行期间不应该受到其它事务的干扰.
否则容易出现: 脏读, 不可重复读, 虚读(幻读)等情况…
-- 其实考虑隔离性, 就是设置不同的隔离级别, 具体如下:
-- 隔离级别, 安全性, 从低到高分别是:
read uncommitted < read committed < repeatable-read < serializable
-- 效率, 从高到低分别是:
read uncommitted > read committed > repeatable-read > serializable
-- MySQL数据库的默认隔离级别是: repeatable-read
-- Oracle数据库的默认隔离级别是: read committed
-- 四个隔离级别解释
read uncommitted:
会发生: 脏读, 不可重复读, 虚读
已解决: 0
read committed:
会发生: 不可重复读, 虚读
已解决: 脏读
repeatable-read:
会发生: 虚读
已解决: 脏读, 不可重复读
serializable:
会发生: 0
已解决: 脏读, 不可重复读, 虚读
- 持久性
指的是: 无论事务执行成功与否, 结果都会永久的写到数据表中.
涉及SQL语句
-- MySQL默认开启了事务的自动提交功能, 每个SQL语句都是一个单独的事务, 执行完都会自动提交.
-- 查看事务的自动提交功能.
show variables like '%commit%';
-- 查看事务的隔离级别
select @@transaction_isolation; -- 这个是: MySQL8.X的写法.
select @@tx_isolation; -- 这个是: MySQL5.X的写法.
-- 提交事务
commit -- 相当于: 把事务的执行结果给永久保存了.
-- 事务回滚
rollback -- 相当于: 撤销本次执行, 即: 把数据还原到事务没有开始执行前的状态.
-- 开启事务
begin 或者写 start transaction
-- 临时的设置事务的隔离级别为指定级别, 会话结束, 本次设置失效.
set session transaction isolation level 隔离级别;
(二)、模拟转账–无事务
"""
需求: PyMysql演示 转账, 无事务版, 看看会发生什么问题.
遇到的问题:
账号A 扣钱 成功, 但是 账号B 收钱 失败.
产生原因:
没有把 账号A扣钱 和 账号B收钱 这两个操作放在一起, 所以它们是两个独立的动作, 各不相关.
解决方案:
把 账号A扣钱 和 账号B收钱 这两个动作绑定到一起, 要么全部成功, 要么全部失败.
这就可以通过 事务 来实现.
"""
# 导包
import pymysql
# 1. 获取连接对象.
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='day10', charset='utf8')
# 2. 获取游标对象.
cursor = conn.cursor()
# 3. 执行SQL语句, 获取结果集.
# 3.1 账户A 扣钱
sql1 = "update account set money = money - 1000 where id = 1;"
row1 = cursor.execute(sql1) # 返回的是: 受影响的行数.
conn.commit() # 提交事务.
# 3.2 模拟程序出Bug.
print(10 / 0)
# 3.3 账户B 收钱
sql2 = "update account set money = money + 1000 where id = 2;"
row2 = cursor.execute(sql2) # 返回的是: 受影响的行数.
conn.commit() # 提交事务.
# 4. 操作结果集, 看: 转账是否成功, 并提示.
print('转账成功!' if row1 == 1 and row2 == 1 else '转账失败!')
# 5. 释放资源.
cursor.close()
conn.close()
(三)、模拟转账–有事务
"""
需求: PyMysql演示 转账, 无事务版, 看看会发生什么问题.
遇到的问题:
账号A 扣钱 成功, 但是 账号B 收钱 失败.
产生原因:
没有把 账号A扣钱 和 账号B收钱 这两个操作放在一起, 所以它们是两个独立的动作, 各不相关.
解决方案:
把 账号A扣钱 和 账号B收钱 这两个动作绑定到一起, 要么全部成功, 要么全部失败.
这就可以通过 事务 来实现.
"""
# 导包
import pymysql
# 1. 获取连接对象.
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='day10', charset='utf8')
# 2. 获取游标对象.
cursor = conn.cursor()
try:
# 3. 执行SQL语句, 获取结果集.
# 3.0 开启事务, 即: 在提交或者回滚前, 所有的动作都是1个整体.
conn.begin() # 开启事务, 其实你不写, 也不影响, 但是按照规范写法, 要写上.
# 3.1 账户A 扣钱
sql1 = "update account set money = money - 1000 where id = 1;"
row1 = cursor.execute(sql1) # 返回的是: 受影响的行数.
# 3.2 模拟程序出Bug.
# print(10 / 0)
# 3.3 账户B 收钱
sql2 = "update account set money = money + 1000 where id = 2;"
row2 = cursor.execute(sql2) # 返回的是: 受影响的行数.
except:
# 3.4 走这里, 说明出现问题, 即: 先事务回滚, 然后提示 转账失败.
conn.rollback()
print('转账失败!')
else:
# 3.5 走这里, 说明无问题, 转账成功.
conn.commit() # 提交事务
print('转账成功!')
finally:
# 4. 释放资源.
cursor.close()
conn.close()
(四)、扩展-演示脏读
-- 脏读: 也叫 读-未提交, 指的是1个事务读取到了另1个事务还没有提交的数据, 导致查询结果不一致.
具体动作:
1. 开启两个DOS窗口, 称之为: A, B窗口.
2. 设置A窗口的隔离级别为: read uncommitted(可能发生: 脏读, 不可重读, 虚读)
B窗口的隔离级别不变: 默认还是 repeatable-read
3. 在A, B窗口中分别开启事务.
4. 在A窗口中先查询一下 账务表(account)的数据.
5. 在B窗口中完成 乔峰 => 虚竹 转账1000的动作, 注意: 不要提交.
6. 在A窗口中再次查询一下 账务表(account)的数据, 发现和刚才查询的结果: 不一样.
7. 此时出现了: A窗口 读取到了 B窗口 未提交的数据, 这就是 => 脏读.
-- 不可重复读: 也叫 读-已提交(修改)
-- 虚读: 也叫 读-已提交(插入)
-- 串行化读法 => Serializable