02. 数据库操作实现
2024年10月28日大约 9 分钟
02. 数据库操作实现
目标
- 掌握操作数据库的基本流程
- 掌握使用PyMySQL对数据库的增、删、改、查
1. 安装PyMySQL
PyMySQL：Python3.x 版本中连接 MySQL 服务器的一个库
安装方法1
pip install PyMySQL安装方法2
pip install PyMySQL -i https://pypi.douban.com/simple/
2. 操作数据库的基本流程
- 代码实现步骤
1. 导包
import pymysql
2. 创建数据库连接
conn = pymysql.connect(host，port, user, password, database, charset)
3. 获取游标对象
cursor = conn.cursor()
4. 执行操作
cursor.execute("sql语句")
1. 查询语句(select）
处理结果集(提取数据 fetch*)
2. 增删改语句(insert、update、delete)
成功：提交事务 conn.commit()
失败：回滚事务 conn.rollback()
5. 关闭游标对象
cursor.close()
6. 关闭数据库连接
conn.close()1. 数据库操作-连接数据库
conn = pymysql.connect(host=None, user=None, password="", database=None, port=3306, charset="")
参数说明
host: 数据库服务器地址
user: 登录用户名
password: 密码
database: 要连接的数据库名称
port: 数据库连接端口(默认值: 3306)
charset: 字符集 (设置为: utf8)
2. 数据库操作-获取游标对象
cursor = conn.cursor()
- 说明
- 调用数据库连接对象的cursor()方法获取游标对象
3. 数据库操作-执行SQL语句
cursor.execute(sql)
说明
调用游标对象的execute()方法来执行SQL语句
sql: 要执行的sql语句
4. 数据库操作-释放资源
关闭游标对象 cursor.close()
关闭数据库连接 conn.close()
- 说明
- 调用数据库连接对象、游标对象的close()方法来释放资源
5. 事务的概念
事务，是关系型数据库（mysql）特有的概念
事务，可以看做一个虚拟的容器，在容器中存放一系列的数据库操作，看做一个整体。内部的所有操作，要么一次性全部成功，只要有一个失败，就全部失败！
事务操作：只有 2 种情况
提交：conn.commit()
回滚: conn.rollback()
6. 案例
查询数据库服务器版本信息
- 需求：
- 连接到数据库（host:211.103.136.244 port：7061 user:student password:iHRM_student_2021 database:test_db）
- 获取数据库服务器版本信息
- 分析：
- 如何获取数据库的连接？
- 获取数据库服务器版本信息的SQL语句是什么？
- sql语句：select version()
- 如何执行SQL语句？
- cursor.execute("select version()")
- 获取查询结果
- cursor.fetchone()
如何释放资源？CREATE DATABASE `py_book` /*!40100 DEFAULT CHARACTER SET utf8mb4 */
use py_book;
CREATE TABLE `bookinfo` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`pub_date` datetime(6) DEFAULT NULL,
`readcount` int(11) NOT NULL,
`commentcount` int(11) NOT NULL,
`is_delete` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=176 DEFAULT CHARSET=utf8mb4;
INSERT INTO `bookinfo`(`id`, `name`, `pub_date`, `readcount`, `commentcount`, `is_delete`) VALUES (1, '射雕英雄传', '1980-05-01 00:00:00.000000', 12, 34, 0);
INSERT INTO `bookinfo`(`id`, `name`, `pub_date`, `readcount`, `commentcount`, `is_delete`) VALUES (2, '天龙八部', '1986-07-24 00:00:00.000000', 36, 40, 0);
INSERT INTO `bookinfo`(`id`, `name`, `pub_date`, `readcount`, `commentcount`, `is_delete`) VALUES (3, '笑傲江湖', '1995-12-24 00:00:00.000000', 20, 80, 0);
INSERT INTO `bookinfo`(`id`, `name`, `pub_date`, `readcount`, `commentcount`, `is_delete`) VALUES (4, '雪山飞狐', '1987-11-11 00:00:00.000000', 58, 24, 0);
INSERT INTO `bookinfo`(`id`, `name`, `pub_date`, `readcount`, `commentcount`, `is_delete`) VALUES (5, '西游记', '1986-01-01 00:00:00.000000', 1, 0, 1);# 1. 导包
import pymysql
# 2. 建立连接
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="admin",
database="py_book",
charset="utf8"
)
# 3. 获取游标
cursor = conn.cursor()
# 4. 执行 sql 语句（查询）
cursor.execute("select version()")
# 5. 获取结果
res = cursor.fetchone()
print("res =", res) # tuple
print("res =", res[0])
# 6. 关闭游标
cursor.close()
# 7. 关闭连接
conn.close()3. 数据库操作实现
1. SQL 语法回顾
查询语法：
select 字段1，字段2，... from 表 where 条件；
-- 示例：select id，title, pub_date from t_book where title = '读者';添加语法：
insert into 表名（字段1， 字段2， ...） values（值1， 值2， ...）;
-- 示例：insert into t_book(id, title, pub_date) values(17, '红楼梦', '2021-11-11');更新语法：
update 表名 set 字段名 = 字段值 where 条件
-- 示例：update t_book set title = '三国' where id = 17;删除语法：
delete from 表名 where 条件
-- 示例：delete from t_book where title = '三国';2. 数据库查询
1. 查询操作流程
2. cursor游标
- cursor对象的execute方法：执行sql并返回结果到客户端
3. 常用方法
- fetchone()：从结果集中，提取一行。返回值： 元组数据
- fetchmany(size)：从结果集中，提取 size 行。返回值： ((),(),...)
- fetchall()：提取所有结果集。返回值： ((),(),...)
- 属性rownumber：可以设置游标位置。
4. fetch*()方法原理
- rownumber属性：标识游标的当前位置（默认初始位置从0开始）
- fetch*()方法是基于游标当前位置逐行向下获取数据
5. 案例
- 要求
①：查询t_book表的全部字段数据
②：获取查询结果集的第一条数据
③：获取查询结果集的第3条和第4条数据
④：获取全部的查询结果集及总记录数
- 分析
1. select * from t_book
2. cursor.rownumber= 0 cursor.fetchone()
3. cursor.rownumber= 2 cursor.fetchmany(2)
4. cursor.rownumber= 0 cursor.fetchall()# 1. 导包
import pymysql
# 2. 建立连接
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="admin",
database="py_book",
charset="utf8"
)
# 3. 获取游标
cursor = conn.cursor() # 指向 0 号位置。
# 4. 执行 sql 语句（查询）--- t_book
cursor.execute("select * from bookinfo;")
# 5. 获取结果 - 提取第一条
res1 = cursor.fetchone()
print("res1 =", res1)
# 修改游标位置：回零
cursor.rownumber = 0
# 5. 获取结果 - 提取前 2 条
res2 = cursor.fetchmany(2)
print("res2 =", res2)
# 修改游标位置：回零
cursor.rownumber = 0
res3 = cursor.fetchall()
print("res3 =", res3)
# 修改游标位置：指向第 2 条记录
cursor.rownumber = 2
res4 = cursor.fetchmany(2)
print("res4 =", res4)
# 6. 关闭游标
cursor.close()
# 7. 关闭连接
conn.close()6. 异常捕获
# 1. 导包
import pymysql
# 定义全局变量，初值为 None
conn = None
cursor = None
try:
# 2. 建立连接
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="admin",
database="py_book",
charset="utf8"
)
# 3. 获取游标
cursor = conn.cursor() # 指向 0 号位置。
# 4. 执行 sql 语句（查询）--- t_book
cursor.execute("select * from bookinfo;")
# 5. 获取结果 - 提取第一条
res1 = cursor.fetchone()
print("res1 =", res1)
# 修改游标位置：回零
cursor.rownumber = 0
# 5. 获取结果 - 提取前 2 条
res2 = cursor.fetchmany(2)
print("res2 =", res2)
# 修改游标位置：回零
cursor.rownumber = 0
res3 = cursor.fetchall()
print("res3 =", res3)
# 修改游标位置：指向第 2 条记录
cursor.rownumber = 2
res4 = cursor.fetchmany(2)
print("res4 =", res4)
except Exception as err:
print("查询语句执行出错:", str(err))
finally:
# 6. 关闭游标
if cursor is not None:
cursor.close()
# 7. 关闭连接
if conn is not None:
conn.close()3. 数据库—insert/update/delete
更新t_book表的指定信息
数据库地址：
host: 211.103.136.244,
port: 7061,
user: student,
password: iHRM_student_2021,
要求单独实现：
①：新增一条图书数据（id:5 title:西游记 pub_date:1986-01-01 ）
②：把图书名称为‘西游记’的阅读量加一
③：删除名称为‘西游记’的图书1. 案例-新增
- **insert_test.py **: 新增一条图书数据（id:5 title:西游记 pub_date:1986-01-01 ）
"""
新增一条图书数据（id:5 title:西游记 pub_date:1986-01-01 ）
insert into t_book(id, title, pub_date) values(5, '西游记', '1986-01-01');
1. 导包
2. 创建连接
3. 获取游标
4. 执行 insert 语句
5. 提交/回滚事务
6. 关闭游标
7. 关闭连接
"""
# 1. 导包
import pymysql
# 定义全局变量
conn = None
cursor = None
try:
# 2. 创建连接
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="admin",
database="py_book",
charset="utf8"
)
# 3. 获取游标
cursor = conn.cursor()
# 4. 执行 insert 语句
cursor.execute(
"insert into bookinfo(id, name, pub_date,readcount,commentcount,is_delete) values(175, '西游记', '1986-01-01',0,0,0);"
)
# 查看 sql执行，影响多少行
print("影响的行数：", conn.affected_rows())
# 5. 提交事务
conn.commit()
except Exception as err:
print("插入数据错误：", str(err))
# 回滚事务
conn.rollback()
finally:
# 6. 关闭游标
cursor.close()
# 7. 关闭连接
conn.close()2. 案例-更新
- update_test.py:把图书名称为‘西游记’的阅读量加一
"""
把图书名称为‘西游记’的阅读量加一
update t_book set `read` = `read` + 1 where id = 6;
1. 导包
2. 建立连接
3. 获取游标
4. 执行 update语句
5. 提交、回滚事务
6. 关闭游标
7. 关闭连接
"""
# 1. 导包
import pymysql
conn = None
cursor = None
try:
# 2. 建立连接
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="admin",
database="py_book",
charset="utf8"
)
# 3. 获取游标
cursor = conn.cursor()
# 4. 执行 update语句。字段名，需要使用 反引号(`)包裹
cursor.execute("update bookinfo set `readcount` = `readcount` + 1 where id = 5;")
print("影响的行数：", conn.affected_rows())
# 5. 提交、回滚事务
conn.commit()
except Exception as err:
print("更新失败：", str(err))
# 回滚事务
conn.rollback()
finally:
# 6. 关闭游标
cursor.close()
# 7. 关闭连接
conn.close()3. 案例-删除
delete_test.py : 删除名称为‘西游记’的图书
"""
删除名称为‘西游记’的图书
delete from t_book where title = '西游记';
1. 导包
2. 建立连接
3. 获取游标
4. 执行 delete 语句
5. 提交、回滚事务
6. 关闭游标
7. 关闭连接
"""
# 1. 导包
import pymysql
conn = None
cursor = None
try:
# 2. 建立连接
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="admin",
database="py_book",
charset="utf8"
)
# 3. 获取游标
cursor = conn.cursor()
# 4. 执行 delete语句。
cursor.execute("delete from bookinfo where id = 175;")
print("影响的行数：", conn.affected_rows())
# 5. 提交、回滚事务
conn.commit()
except Exception as err:
print("更新失败：", str(err))
# 回滚事务
conn.rollback()
finally:
# 6. 关闭游标
cursor.close()
# 7. 关闭连接
conn.close()4. 关于SQL注入
import pymysql
# 输入用户名和密码
id = input("请输入名称：") # ' or 1=1 -- '
name = input("请输入是否删除：") # 0
print(id, name)
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='admin', charset="utf8", db='py_book')
cursor = conn.cursor()
# 基于字符串格式化来 拼接SQL语句
# sql = select * from bookinfo where name='西游记' and is_delete='0'
# sql = select * from bookinfo where name='' or 1=1 -- '' and is_delete='1' # 输入：' or 1=1 -- '; sql 注入
sql = "select * from bookinfo where name='{}' and is_delete='{}'".format(id, name)
print(sql)
cursor.execute(sql)
result = cursor.fetchall()
print(result) # 查询了全部
cursor.close()
conn.close()在Python开发中 如何来避免SQL注入呢？
SQL语句不要在使用python的字符串格式化，而是使用pymysql的execute方法
import pymysql # 输入用户名和密码 id = input("请输入名称：") # ' or 1=1 -- ' name = input("请输入是否删除：") # 0 print(id, name) conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='admin', charset="utf8", db='py_book') cursor = conn.cursor() # 基于字符串格式化来 拼接SQL语句 sql = "select * from bookinfo where name=%s and is_delete=%s" cursor.execute(query=sql, args=(id, name)) result = cursor.fetchall() print(result) # None cursor.close() conn.close()
execute(sql, args) 执行sql语句
- sql：要执行的sql语句
- args：sql语句的参数，可以为数字、字符串、list、tuple、dict
- If args is a list or tuple, %s can be used as a placeholder in the query. 如果参数是数字、字符串、list或者tuple，可以用%s当占位符
- If args is a dict, %(name)s can be used as a placeholder in the query. 如果参数是dict，可以用%(name)s当占位符，name为dict中的key
# sql = "SELECT id, name FROM bookinfo where id < %s;" # n = cur.execute(sql, 10) # sql = "SELECT id, name FROM bookinfo where id < %s and id > %s;" # n = cur.execute(sql, (10, 2)) # sql = "SELECT id, name FROM bookinfo where id < %(endId)s and id > %(startId)s;" # n = cur.execute(sql, {'startId': 1, 'endId': 10})executemany(sql, args) 执行多行sql语句
- 此方法可以提升多行insert或者replace的性能，否则就相当于使用
execute()循环 - sql：要执行的sql语句
- args：sql语句的参数，tuple或者list
sql = "INSERT INTO bookinfo (name, readcount) VALUES (%s, 0);" cur.executemany(sql, ['测试1', '测试2']) # insert两条title分别为 测试1', '测试2' 的数据- 此方法可以提升多行insert或者replace的性能，否则就相当于使用