接口测试06-python连接数据库

 

 

 

 

1 数据库以及数据库操作概述

1.1 数据库概述

概念:

• 数据存储的仓库,用于保存程序中的数据

• 数据库通过 SQL 语句来创建、管理、维护数据

数据库和变量都是程序中数据的载体，二者有何区别?

• 存储方式不同，数据库将数据存储在磁盘文件，变量将数据存储在内存中，前者可以做到持久化存储

分类:

• 关系型数据库(MySQL、Oracle、SQLite....)

  数据存储结构: database ---> table ---> 行 + 列

• 非关系型数据库(Redis、MongoDB....)

  数据存储结构: 键值对、列表、hash表、文档....

• 二者比较:

  • 关系型数据库数据存储更安全

  • 非关系型数据数据处理更高效(适应于当前 "大数据" 时代)

1.2 Python 操作数据库相关实现

前提: Python(各种工具、各种编程语言) 本身不具备直连数据库的功能，必须导入具备该功能的第三方实现

MySQLdb(了解)

MySQLdb是 Python 连接 MySQL 以前最流行的一个驱动，很多框架都也是基于此库进行开发，遗憾的是它只支持 Python2.x，它是基于C开发的库，和Windows 平台的兼容性不友好，现在基本不推荐使用，取代的是它的衍生版本。

mysqlclient

由于 MySQLdb 年久失修，后来出现了它的 Fork 版本 mysqlclient，完全兼容 MySQLdb，同时支持 Python3.x，是 Django ORM的依赖工具，如果你想使用原生 SQL 来操作数据库，那么推荐此驱动。

PyMySQL(重点)

PyMySQL是纯 Python 实现的驱动，速度上比不上 MySQLdb，最大的特点可能就是它的安装方式没那么繁琐，同时也兼容 MySQLdb。

SQLAlchemy(高级_了解)

SQLAlchemy是一种既支持原生 SQL，又支持 ORM 的工具，它非常接近 Java 中的 Hibernate 框架。

原生SQL: CRUD sql语句

ORM: 使用面向对象的思想替代 SQL 语句

 

2 PyMySQL基本操作概述

2.1 PyMySQL 安装

启动命令行，联网的前提下键入命令: pip install pymysql

2.2 PyMySQL 基本实现流程图解

2.3 案例数据准备

CREATE DATABASE IF NOT EXISTS books default charset utf8;
USE books;
DROP TABLE IF EXISTS t_book;
CREATE TABLE t_book (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(20) NOT NULL COMMENT '图书名称',
pub_date date NOT NULL COMMENT '发布日期',
read int(11) NOT NULL DEFAULT '0' COMMENT '阅读量',
comment int(11) NOT NULL DEFAULT '0' COMMENT '评论量',
is_delete tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='图书表';
INSERT INTO t_book VALUES ('1', '射雕英雄传', '1980-05-01', '12', '34', '0');
INSERT INTO t_book VALUES ('2', '天龙八部', '1986-07-24', '36', '40', '0');
INSERT INTO t_book VALUES ('3', '笑傲江湖', '1995-12-24', '20', '80', '0');
DROP TABLE IF EXISTS t_hero;
CREATE TABLE t_hero (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL COMMENT '姓名',
gender smallint(6) NOT NULL COMMENT '性别',
description varchar(200) DEFAULT NULL COMMENT '描述',
is_delete tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
book_id int(11) NOT NULL COMMENT '所属图书ID',
PRIMARY KEY (id),
KEY t_hero_book_id_fb202ef5 (book_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='英雄人物表';
INSERT INTO t_hero VALUES ('1', '郭靖', '1', '降龙十八掌', '0', '1');
INSERT INTO t_hero VALUES ('2', '黄蓉', '0', '打狗棍法', '0', '1');
INSERT INTO t_hero VALUES ('3', '乔峰', '1', '降龙十八掌', '0', '2');
INSERT INTO t_hero VALUES ('4', '令狐冲', '1', '独孤九剑', '0', '3');
INSERT INTO t_hero VALUES ('5', '任盈盈', '0', '弹琴', '0', '3');

2.4 基本操作之框架代码(核心)

"""
    演示 pymysql 使用的基本代码框架
"""
# 1. 导包
import pymysql
# 2. 创建连接对象,需要传参:数据库登录所需的基本信息
conn = pymysql.Connect(host="127.0.0.1", port=3306, database="books",
                       user="root",password="root", charset="utf8")
print(conn)
# 3. 创建数据载体(cursor)
cursor = conn.cursor()
print(cursor)
# 4. 核心: 操作 SQL (暂不实现)

# 5. 资源释放
cursor.close()
conn.close()

 

2.4 基本操作之框架代码(核心)

# 4. 核心: 发送并执行 sql 语句，接收结果
# 4.1 编写 SQL 语句
sql = "select * from t_book"
# 4.2 发送执行 SQL 语句
cursor.execute(sql)
# 4.3 处理结果
# 获取响应的结果的行数
rc = cursor.rowcount
print("结果行数:",rc)
"""
print("第一行:",cursor.fetchone())
print("第二行:",cursor.fetchone())
print("第三行:",cursor.fetchone())
print("第四行:",cursor.fetchone())
print("第五行:",cursor.fetchone())
"""
# 获取所有行
rows = cursor.fetchall()
print("所有结果:",rows)

for row in rows:
    print(row)
    print("id:",row[0])
    print("书名:",row[1])
    print("日期:",row[2])
    print("阅读量:",row[3])
    print("评论量:",row[4])
    print("是否删除:",row[5])

 

3.2 核心操作之增删改

# 4. 发送 SQL ，接收结果
# 增
"""
sql = "insert into t_book values(5,'神雕侠侣','1990-10-01',100,50,0)" #新增书籍信息
cursour.execute(sql)
print("受影响的行数:",cursour.rowcount)
"""

# 改
"""
sql = "update t_book set title = '碧血剑' where id = 5"
cursour.execute(sql)
print("受影响的行数:",cursour.rowcount)
"""

#删
sql = "delete from t_book where id = 5"
cursour.execute(sql)
print("受影响的行数:",cursour.rowcount)

# 手动提交
conn.commit()

# 增删改实现时，都需要修改数据库数据，默认 SQL 执行是不会修改的，必须执行提交操作，才可以修改数据库
# 方式1: 创建连接时设置 autocommit=True == 自动提交
# 方式2: SQL 执行完毕，调用 conn.commit() == 手动提交

4 PyMySQL核心操作之事务（重点_记住）

4.1 概念

事务: 一套完整的业务逻辑，这套逻辑中可能涉及多条 SQL 语句，这些 SQL 语句要么都成功，要么都失败

举例:

• 转账，张三转账给李四100块, SQL1=修改张三账户 SQL2=修改李四的账户

• 支付，支付后修改订单状态，SQL1=修改账户金额 SQL2=修改订单状态

  .....

4.2 特征(面试常见)

• 原子性(Atomicity)： 不可分割

  事务中包含的操作被看做一个逻辑单元，这个逻辑单元中的操作要么全部成功，要么全部失败

• 一致性(Consistency)：

  事务的结果保留不变，即事务的运行并不改变数据的一致性

• 隔离性(Isolation)：

  又称孤立性，事务的中间状态对其它事务是不可见的

• 持久性(Durability)：

  指一个事务一旦提交成功，它对数据库中数据的改变就应该是永久性的

4.3 事务提交机制

对于增删改操作而言，如果要把改变后的数据写入数据库，必须得执行提交操作

• 方式1: 自动提交 conn=pymysql.Connect(....,autocommit=True)

• 方式2: 手动提交 SQL执行完毕，conn.commit()

事务提交对应的概念：事务的回滚(不能将改变写入数据库)

• 方式1: PyMySQL 默认状态下就是回滚

• 方式2: 手动回滚 conn.rollback()

4.4 事务操作

需求:先往 t_book 表插入书的信息，然后向 t_hero 插入该书主角的信息，要求，要么都成功，要么都失败

"""
    需求：
        先往 t_book 表插入一条书的信息
        再往 t_hero 表插入书籍对应的角色信息
        两条 SQL 语句要么都成功，要么都失败
        典型的事务实现
    编码:
        提交方式: 使用手动提交还是自动提交?
        SQL 执行状态问题: 当两条 SQL 都正常时，才提交，否则，就不提交(回滚)
    方案1:
        自动提交时，SQL 有异常时，事务提交失败，预期结果: 都失败!
    方案2:
        手动提交，如果 SQL 语句都正常，那么就提交，否则，就回滚，怎么实现这个分支?


    try 是一种特殊的分支实现:判断是否代码存在异常
    手动提交控制事务执行的通用代码
    try:
        sql1=...
        sql2=...
        cursor.execute(sql1)
        cursor.exceute(sql2)
        ...
        conn.commit()
    except Exception as e:
        处理异常
        conn.rollback()
    finally:
        释放资源
        cursor.close()
        conn.close()

"""
# 1. 导包
import pymysql
# 2. 创建连接
conn = pymysql.Connect(host="127.0.0.1", port=3306, database="books",
                       user="root", password="root", charset="utf8")
                       #autocommit=True)
# 3. 获取游标
cursor = conn.cursor()
# 4. 核心: SQL 语句
# 4-1. 编写两条 SQL 语句，一条 SQL 语句有异常(语法异常)
try:
    sql1 = "insert into t_book values(5,'西游记','1990-10-01',100,50,0)"
    sql2 = "insert into t_heor values(7,'孙悟空',1,'七十二变',0,5)"
    # 4-2. 执行 SQL 语句
    cursor.execute(sql1)
    cursor.execute(sql2)
    # 事务提交
    conn.commit()
except Exception as e:
    print(e)
    conn.rollback()
    # 4-3. 怎么控制事务的提交?
finally:
    # 5. 资源关闭
    cursor.close()
    conn.close()