【接口测试】Day5-使用pymysql库对mysql数据库进行增删改查操作
2023-09-11 14:17:06 时间
目录
今日目标
- 能够使用pymysql库对mysql数据库进行增删改查操作
一、数据库介绍
- 概念:一个存放数据的仓库(Database)，这个仓库按照一定的数据结构组织、存放、管理数 据。
- 分类:
- 关系型数据库:mysql、sql server、oracle、DB2等
- 非关系型数据库:redis等
- python操作数据库的方式
- pymysql:纯python开发，支持python2和python3，简单易用
二、数据库基本操作
1、安装
安装: pip install PyMySQL
Mac安装报错
% pip install PyMySQL
Traceback (most recent call last):
File "/usr/local/bin/pip", line 6, in <module>
from pip._internal.cli.main import main
ModuleNotFoundError: No module named 'pip._internal.cli.main'解决办法（不行）
python3 -m ensurepip
python3 -m pip install --upgrade pip setuptools wheel
% python -m ensurepip
Looking in links: /var/folders/07/28dx__gx1cxd91yc031krr7r0000gn/T/tmpXYVVyr
Requirement already satisfied: setuptools in /System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python (41.0.1)
Requirement already satisfied: pip in /Library/Python/2.7/site-packages/pip-21.0.1-py2.7.egg (21.0.1)
pip requires Python '>=3.6' but the running Python is 2.7.16
% python3 -m ensurepip
Looking in links: /var/folders/07/28dx__gx1cxd91yc031krr7r0000gn/T/tmprnsdid3m
Requirement already satisfied: setuptools in /Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.8/lib/python3.8/site-packages (41.2.0)
Requirement already satisfied: pip in /Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.8/lib/python3.8/site-packages (19.2.3)
% python3 -m pip install --upgrade pip setuptools wheel
Collecting pip
Downloading https://files.pythonhosted.org/packages/f3/77/23152f90de45957b59591c34dcb39b78194eb67d088d4f8799e9aa9726c4/pip-22.1-py3-none-any.whl (2.1MB)
|████████████████████████████████| 2.2MB 52kB/s
Collecting setuptools
Downloading https://files.pythonhosted.org/packages/e9/1c/ec080fde54ab30a738c92f794eab7f5d2f354f2b619ee95b2efe353e0766/setuptools-62.3.2-py3-none-any.whl (1.2MB)
|████████████████████████████████| 1.2MB 102kB/s
Collecting wheel
Downloading https://files.pythonhosted.org/packages/27/d6/003e593296a85fd6ed616ed962795b2f87709c3eee2bca4f6d0fe55c6d00/wheel-0.37.1-py2.py3-none-any.whl
Installing collected packages: pip, setuptools, wheel
Found existing installation: pip 19.2.3
Uninstalling pip-19.2.3:
ERROR: Could not install packages due to an EnvironmentError: [Errno 13] Permission denied: 'RECORD'
Consider using the `--user` option or check the permissions.
WARNING: You are using pip version 19.2.3, however version 22.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.解决办法（可以）
curl https://bootstrap.pypa.io/get-pip.py | python3
pip3 install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
% curl https://bootstrap.pypa.io/get-pip.py | python3
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 2617k 100 2617k 0 0 23111 0 0:01:55 0:01:55 --:--:-- 34580
Defaulting to user installation because normal site-packages is not writeable
Collecting pip
Using cached pip-22.1-py3-none-any.whl (2.1 MB)
Installing collected packages: pip
WARNING: The scripts pip, pip3, pip3.10 and pip3.8 are installed in '/Users/zxf/Library/Python/3.8/bin' which is not on PATH.
Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.
Successfully installed pip-22.1
WARNING: There was an error checking the latest version of pip.
% pip3 install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
Defaulting to user installation because normal site-packages is not writeable
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Collecting pymysql
Downloading https://pypi.tuna.tsinghua.edu.cn/packages/4f/52/a115fe175028b058df353c5a3d5290b71514a83f67078a6482cff24d6137/PyMySQL-1.0.2-py3-none-any.whl (43 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 43.8/43.8 kB 311.3 kB/s eta 0:00:00
Installing collected packages: pymysql
Successfully installed pymysql-1.0.2验证: pip show PyMySQL
Name: PyMySQL
Version: 1.0.2
Summary: Pure Python MySQL Driver
Home-page: https://github.com/PyMySQL/PyMySQL/
Author: yutaka.matsubara
Author-email: yutaka.matsubara@gmail.com
License: "MIT"
Location: /Users/zxf/Library/Python/3.8/lib/python/site-packages
Requires:
Required-by:另外也可以安装个pandas，pandas是python的扩展程序库，用于数据分析
pip3 install pandas -i https://pypi.tuna.tsinghua.edu.cn/simple --default-timeout=100
% pip3 install pandas -i https://pypi.tuna.tsinghua.edu.cn/simple --default-timeout=100
Defaulting to user installation because normal site-packages is not writeable
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Collecting pandas
Downloading https://pypi.tuna.tsinghua.edu.cn/packages/47/a5/79156a83c133b5d049a38f444e11eacabab8b3ad00814d8c6811fe9850e2/pandas-1.4.2-cp38-cp38-macosx_10_9_x86_64.whl (11.0 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 11.0/11.0 MB 2.2 MB/s eta 0:00:00
Collecting pytz>=2020.1
Downloading https://pypi.tuna.tsinghua.edu.cn/packages/60/2e/dec1cc18c51b8df33c7c4d0a321b084cf38e1733b98f9d15018880fb4970/pytz-2022.1-py2.py3-none-any.whl (503 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 503.5/503.5 kB 2.4 MB/s eta 0:00:00
Collecting numpy>=1.18.5
Downloading https://pypi.tuna.tsinghua.edu.cn/packages/52/d0/d7a200f2c3d6c6a879dbdc6d762c7dbed542527333ac9a6a72c8ffab9814/numpy-1.22.3-cp38-cp38-macosx_10_14_x86_64.whl (17.6 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 17.6/17.6 MB 3.2 MB/s eta 0:00:00
Collecting python-dateutil>=2.8.1
Downloading https://pypi.tuna.tsinghua.edu.cn/packages/36/7a/87837f39d0296e723bb9b62bbb257d0355c7f6128853c78955f57342a56d/python_dateutil-2.8.2-py2.py3-none-any.whl (247 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 247.7/247.7 kB 7.8 MB/s eta 0:00:00
Requirement already satisfied: six>=1.5 in /Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.8/lib/python3.8/site-packages (from python-dateutil>=2.8.1->pandas) (1.15.0)
Installing collected packages: pytz, python-dateutil, numpy, pandas
WARNING: The scripts f2py, f2py3 and f2py3.8 are installed in '/Users/zhengxiaofang/Library/Python/3.8/bin' which is not on PATH.
Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.
Successfully installed numpy-1.22.3 pandas-1.4.2 python-dateutil-2.8.2 pytz-2022.12、操作流程(重点)
1. 创建连接
2. 获取游标
3. 执行sql
- 1. 查询操作(select)
- 2. 非查询操作(insert/update/delete)
- 1. 事务提交(连接对象.commit())
- 2. 事务回滚(连接对象.rollback())
4. 关闭游标
5. 关闭连接
什么是游标?(了解)
- 游标是SQL 的一种数据访问机制 ，游标是一种处理数据的方法。
众所周知，使用SQL的select查询操作返回的结果是一个包含一行或者是多行的数据集，如果 我们要对查询的结果再进行查询，比如(查看结果的第一行、下一行、最后一行、前十行等 等操作)简单的通过select语句是无法完成的，因为这时候索要查询的结果不是数据表，而是 已经查询出来的结果集。
- 游标就是针对这种情况而出现的。
我们可以将“ 游标 ”简单的看成是结果集的一个指针，可以根据需要在结果集上面来回滚动， 浏览我需要的数据。
3、数据准备
注意事项:
直接使用本地localhost数据库即可
- 1. 请先本地安装mysql数据库
- 2. 然后通过Navicat连接本地数据库
- 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` ( `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' );4、数据库基本操作
4.1 连接数据库
1).连接到数据库(host:localhost user:root password:root database:books)
2).获取数据库服务器版本信息
# 导包
import pymysql
# 创建连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
password="root",
database="books")
# 获取游标
cursor = conn.cursor()
# 执行sql
cursor.execute("select version()")
result = cursor.fetchall()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()4.2 数据库查询操作
1).连接到数据库(host:localhost user:root password:root database:books)
2).查询图书表的数据(包括:图书id、图书名称、阅读量、评论量)
3).获取查询结果的总记录数
4).获取查询结果的第一条数据5).获取全部的查询结果
# 导包
import pymysql
# 创建连接
# 1).连接到数据库(host:localhost user:root password:root database:books)
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
password="root",
database="books")
# 获取游标
cursor = conn.cursor()
# 执行sql
# 2).查询图书表的数据(包括:图书id、图书名称、阅读量、评论量)
sql = "select id, title, `read`, `comment` from t_book;"
cursor.execute(sql)
# 3).获取查询结果的总记录数
print("获取的查询结果记录行数为:", cursor.rowcount)
# # 4).获取查询结果的第一条数据 #
print(cursor.fetchone())
# 5).获取全部的查询结果
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
conn.close()4.3 数据库插入操作
1).连接到数据库(host:localhost user:root password:root database:books autocommit:True)
2).新增一条图书数据(id:4 title:西游记 pub_date:1986-01-01 )
# 导包
import pymysql
# 创建连接
# 1).连接到数据库(host:localhost user:root password:root database:books)
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
password="root",
database="books",
autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
# 新增一条图书数据(id:4 title:西游记 pub_date:1986-01-01 )
sql = "insert into t_book(id, title, pub_date) values(4, '西游记', '1986-01- 01');"
cursor.execute(sql)
# 3).获取受影响的结果记录数
print("影响的结果记录数为:", cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()4.4 数据库更新操作
1).连接到数据库(host:localhost user:root password:root database:books autocommit:True)
2).更新[西游记]图书名称为(title:东游记)
# 导包
import pymysql
# 创建连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
password="root",
database="books",
autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "update t_book set title='东游记' where title = '西游记';" cursor.execute(sql)
print(cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()4.5 数据库删除操作
1).连接到数据库(host:localhost user:root password:root database:books autocommit:True)
2).删除图书(title:东游记)
"""
1).连接到数据库(host:localhost user:root password:root database:books autocommit:True)
2).删除图书(title:东游记)
"""
# 导包
import pymysql
# 创建连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
password="root",
database="books",
autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "delete from t_book where title = '东游记';" cursor.execute(sql)
print(cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()查询与非查询(插入、更新、删除)操作小结:
相同点:基本操作流程是一样
- 创建连接
- 获取游标
- 执行sql
- 关闭游标
- 关闭连接
不同点：
- 要执行sql语句不一样
- 非查询操作需要开启事务(在创建连接时，指定参数autocommit=True)
三、数据库事务操作
1、引入案例
1).连接到数据库(host:localhost user:root password:root database:books)， 并开启自动提交事务
2).新增一条图书数据(id:4 title:西游记 pub_date:1986-01-01 )3).故意抛出一个异常(模拟代码出现异常)
4).新增一条英雄人物数据(name:孙悟空 gender:1 book_id:4)
"""
1).连接到数据库(host:localhost user:root password:root database:books)， 并开启自动提交事务
2).新增一条图书数据(id:4 title:西游记 pub_date:1986-01-01 )
3).故意抛出一个异常(模拟代码出现异常)
4).新增一条英雄人物数据(name:孙悟空 gender:1 book_id:4)
"""
# 导包
import pymysql
# 创建连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
password="root",
database="books",
autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "insert into t_book(id, title, pub_date) values(4, '西游记', '1986-01- 01');"
cursor.execute(sql)
print(cursor.rowcount)
print("-" * 200)
# 主动抛出异常
raise Exception("程序出错啦。。。。。。")
# 4).新增一条英雄人物数据(name:孙悟空 gender:1 book_id:4)
sql = "insert into t_hero(name,gender,book_id) values('孙悟空', 1, 4)" cursor.execute(sql)
print(cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()2、概念【理解】
- 基于代码的角度:一段实现了具体业务单元功能的代码，这段代码要么都执行，要么都不执 行
- 基于业务的角度:最小的业务单元，要么都成功，要么都失败
3、特点【了解】ACID
- 原子性:事务中的一系列操作、他是最基本的工作单元。
- 一致性:在数据库看到的结果要么是执行之前的结果，要么是执行之后的结果。
- 隔离性:事务的内部状态对其他事务是不可见的。
- 持久性:通过事务对数据库中数据做的改变，永久有效。
4、操作【理解】
- 自动提交(不推荐): autocommit=True
- 手动提交(推荐):
- 提交事务: conn.commit()
- 回滚事务: conn.rollback()
5、解决导入案例中的问题:数据不一致性问题
1).连接到数据库(host:localhost user:root password:root database:books)， 并开启自动提交事务
2).新增一条图书数据(id:4 title:西游记 pub_date:1986-01-01 )3).故意抛出一个异常(模拟代码出现异常)
4).新增一条英雄人物数据(name:孙悟空 gender:1 book_id:4)
1.思路
1.导包
try:
程序前期，需要执行的代码
2.创建连接对象
3.获取游标对象
4.执行sql
+ 在图书表中插入一行数据
+ 主动抛出异常
+ 在英雄人物表中插入一行数据
调用提交事务:conn.commit()
except:
程序出现异常后，处理代码
调用事务回滚:conn.rollback()
finally:
程序结束时，需要执行的代码
5.关闭游标
6.关闭连接2.代码实现
# 导包
import pymysql
# 初始化
conn = None
cursor = None
# 业务处理
try:
# 创建连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
password="root",
database="books",
autocommit=False)
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "insert into t_book(id, title, pub_date) values(4, '西游记', '1986- 01-01');"
cursor.execute(sql)
print(cursor.rowcount)
print("-" * 200)
# 主动抛出异常
raise Exception("程序出错啦。。。。。。")
# 4).新增一条英雄人物数据(name:孙悟空 gender:1 book_id:4)
sql = "insert into t_hero(name,gender,book_id) values('孙悟空', 1, 4)" cursor.execute(sql)
print(cursor.rowcount)
# 提交事务
conn.commit()
except Exception as e:
# 回滚数据
conn.rollback()
# 打印异常信息
print(e)
finally:
# 关闭游标
if cursor:
cursor.close()
# 关闭连接
if conn:
conn.close()四、数据库工具封装
1、需求
需求分析:
1. sql = "select * from t_book" 2. 调用数据库工具方法
result = exe_sql(sql) print("结果:", result)
2、思路
1、创建连接
2、创建游标
3、执行sql
try:
# 获取游标对象
# 调用游标对象.execute(sql)
# 如果是 查询:
# 返回所有数据
# 否则:
# 提交事务
# 返回受影响的行数
except:
# 回滚事务
# 抛出异常
finally:
# 关闭游标
# 关闭连接
4、关闭游标
5、关闭连接
3、实现
# 导包
import pymysql
# 创建工具类
class DBUtil():
# 初始化
__conn = None
__cursor = None
# 创建连接
@classmethod
def __get_conn(cls):
if cls.__conn is None:
cls.__conn = pymysql.connect(host="localhost",
port=3306,
user="root",
password="root",
database="books")
return cls.__conn
# 获取游标
@classmethod
def __get_cursor(cls):
if cls.__cursor is None:
cls.__cursor = cls.__get_conn().cursor()
return cls.__cursor
# 执行sql
@classmethod
def exe_sql(cls, sql):
try:
# 获取游标对象
cursor = cls.__get_cursor()
# 调用游标对象的execute方法，执行sql
cursor.execute(sql)
# 如果是查询
if sql.split()[0].lower() == "select":
# 返回所有数据
return cursor.fetchall() # 否则:
else:
# 提交事务
cls.__conn.commit()
# 返回受影响的行数
return cursor.rowcount
except Exception as e:
# 事务回滚
cls.__conn.rollback()
# 打印异常信息
print(e)
finally:
# 关闭游标
cls.__close_cursor()
# 关闭连接
cls.__close_conn()
# 关闭游标
@classmethod
def __close_cursor(cls):
if cls.__cursor:
cls.__cursor.close()
cls.__cursor = None
# 关闭连接
@classmethod
def __close_conn(cls):
if cls.__conn:
cls.__conn.close()
cls.__conn = None4、验证
from test10_dbutil import DBUtil
# sql = "select * from t_book"
# sql = "insert into t_book(id, title, pub_date) values(4, '西游记', '1986- 01-01');"
# sql = "update t_book set title='东游记' where title = '西游记';"
sql = "delete from t_book where title = '东游记';"
result = DBUtil.exe_sql(sql)
print(result)今日总结
- 能够说出pymysql操作数据库的基本流程
- 能够通过游标对象实现数据库的增删改查操作
- 能够结合具体事例(如转账)说出数据库事务的概念
- 知道pymysql处理数据库事务的主要方法
相关文章
- MySQL数据库OLTP基准测试（ sysbench）
- Mysql远程连接数据库报错排查：pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '**.**.**.**' (timed out)")
- mysql创建数据库
- 【MySQL】数据库性能测试
- 【MySQL】解决mysql的 1594 错误
- 【MySql】mysql 数据库数据订正
- 【MySql】mysql 字段个数的限制
- 面向对语法读取mysql数据库数据例:$db->query($sql)、$result->fetch_array()
- MySQL数据库数据类型之集合类型SET测试总结
- Mysql一个非常有用的内置函数今天碰到要把MySQL数据库中的varchar转换成date类型进
- 每天定时备份mysql数据库任务
- PHP连接MySQL数据库的三种方式(mysql、mysqli、pdo)
- 性能测试：监控mysql数据库
- MySQL数据库数据类型之集合类型SET测试总结
- Mysql 如果有多个可选条件怎么加索引_MySQL|mysql-索引
- mysql数据库中数据类型的长度
- Linux重置Mysql密码_解决MySQL for Linux错误 ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost
- MYsql 数据库密码忘记（Window）-2(mysql 5.7)
- Atitit mysql数据库自定义异常在java里面的捕获与处理推荐标准与规范
- nodejs连接mysql数据库
- 实践案例丨Pt-osc工具连接rds for mysql 数据库失败
- MYSQL导入数据报错|MYSQL导入超大文件报错|MYSQL导入大数据库报错：2006 - MySQL server has gone away
- MYSQL提权之反弹SHELL——数据库提权属于webshell到管理员的纵向提权，本质还是利用udf提权，无非是在mysql自定义函数中使用了反弹shell而已
- 安全测试===Mysql 注入技巧学习 MySQL注入技巧（1）
- 安全测试===Mysql 注入技巧学习 MySQL注入技巧（2）
- MySQL----MySQL数据库数据表增删改查命令行基础操作初体验
- MySQL数据库篇之mysql的快速启动和停止
- mysql直接操作数据库表里的json字段的单个值？答案在这里
- 【MySQL数据库原理】MySQL 数据库环境变量设置问题完整解决与数据库删除测试