python-入门篇-day08-PyMySQL

PyMySQL

简介

概述

它是一个第三方包, 主要用于实现: 通过Python代码, 操作MySQL数据库的.

安装

方式1: 导包时, 自动安装.方式2: 通过Anaconda数据科学库进行安装.方式3: 通过Anaconda的PowerShell窗口, 以管理员的身份运行, 并安装

操作步骤

流程

获取连接对象.
获取游标对象.
执行SQL语句, 获取结果集.[加一步: 提交事务, 只针对于更新语句(增删改)有效]
操作结果集.
释放资源.

解释

你来到学校, 找到: 前台小姐姐.

前台小姐姐 = 连接对象.
前台小姐姐帮你找到: 任课老师.

任课老师 = 游标对象.
每天上课, 会获取当前的资料.

每日资料 = 结果集.
处理我(任课老师)给你的资料(结果集).

视频 => 看, 代码 => 练, 图 => 画, 笔记 => 总结, 思考
跟老师说再见.

说再见 => 关闭游标(任课老师), 关闭连接对象(前台小姐姐)

演示

# 导包.
import pymysql

# main
if __name__ == '__main__':# 1. 获取连接对象.conn = pymysql.connect(host='localhost',       # 主机地址, 默认是本机, 可以写localhost 或者 127.0.0.1port=3306,              # 端口号user='root',            # 账号password='123456',      # 密码database='day09',       # 要连接到的数据库名称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()

查询相关

函数

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.1port=3306,              # 端口号user='root',            # 账号password='123456',      # 密码database='day09',       # 要连接到的数据库名称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='day09', 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='day09',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='day09', 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='day09', 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()


# 在main方法中测试.
if __name__ == '__main__':# 调用 fun01() 函数, 实现: PyMysql 增 的功能演示.fun01()
# 调用 fun02() 函数, 实现: PyMysql 删 的功能演示.fun02()
# 调用 fun03() 函数, 实现: PyMysql 改 的功能演示.fun03()
# 调用 fun04() 函数, 实现: PyMysql 查 的功能演示.fun04()

自定义工具类

自定义的模块(充当: 工具类), 里边定义的都是 PyMysql相关的最基本的操作, 用于简化刚才 PyMysql-CURD中大量重复代码的.

因为受到目前所学知识点的限制, 很多内容例如: 面向对象, 魔法方法, 封装很多内容都没学, 所以我们仅仅是: 模拟工具模块(工具类)的思维.

utils.py

# 导包.
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

测试.py

# 案例: 测试刚才自定义的 工具模块.
from my_utils import *

# 在main函数中测试.
if __name__ == '__main__':# 1. 测试执行 查询SQL 操作.sql = "select id, name from student where id <= 5;"# results = my_execute_query('day09', sql)results = my_execute_query('day10', sql)print(results)

# 2. 测试执行 更新SQL 操作.sql = "update student set name='小明' where id = 1;"my_execute_update('day10', sql)
# 3. 获取连接对象.conn = get_conn('day09')print(conn)
# 4. 获取游标对象.cursor = get_cursor('day10')print(cursor)

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';

演示

数据库准备

-- 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  录入的密码: xyzselect * from users where username='abc' and password='xyz';     -- 合法的SQL
-- 场景2: 懂点技术的用户. 录入的账号: abc  录入的密码: xyz ' or '1=1select * 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()

★ MySQL事物(ACID)

事务介绍

概述

事务(transaction)指的是逻辑上的一组操作, 组成该操作的各个逻辑单元, 要么全部执行成功, 要么全部执行失败.

大白话记忆: 同生同死.

例如: 转账, 乔峰 => 虚竹转 1000 元

动作1: 乔峰账号 - 1000元
动作2: 虚竹账号 + 1000元

上述的两个动作 = 2个逻辑单元, 合起来, 就是完整的转账动作.

特点

原子性

指的是: 组成事务的各个逻辑单元已经是最小单位, 不可分割.
一致性

指的是: 事务执行前后, 数据应该保持一致.

隔离性(Isolation)

指的是: 事务之间是相互隔离的, 一个事务执行期间不应该受到其它事务的干扰.

否则容易出现: 脏读, 不可重复读, 虚读(幻读)等情况...

-- 其实考虑隔离性, 就是设置不同的隔离级别, 具体如下:
-- 隔离级别, 安全性, 从低到高分别是:
read uncommitted < read committed < repeatable-read < serializable

-- 效率, 从高到低分别是:
read uncommitted > read committed > repeatable-read > serializable

-- MySQL数据库的默认隔离级别是: repeatable-read
-- Oracle数据库的默认隔离级别是: read committed

-- 四个隔离级别解释
read uncommitted: 会发生: 脏读, 不可重复读, 虚读已解决: 0read 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 隔离级别;

演示脏读

-- 脏读: 也叫读-未提交, 指的是1个事务读取到了另1个事务还没有提交的数据, 导致查询结果不一致.具体动作:

开启两个DOS窗口, 称之为: A, B窗口.

设置A窗口的隔离级别为: read uncommitted(可能发生: 脏读, 不可重读, 虚读)

B窗口的隔离级别不变: 默认还是 repeatable-read 3. 在A, B窗口中分别开启事务. 4. 在A窗口中先查询一下账务表(account)的数据. 5. 在B窗口中完成乔峰 => 虚竹转账1000的动作, 注意: 不要提交. 6. 在A窗口中再次查询一下账务表(account)的数据, 发现和刚才查询的结果: 不一样.

此时出现了: A窗口读取到了 B窗口未提交的数据, 这就是 => 脏读.

-- 不可重复读: 也叫读-已提交(修改)

-- 虚读: 也叫读-已提交(插入)

-- 串行化读法 => Serializable

python操作事务

无事务

"""
需求: 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()