数据库编程概述、pymysql基本操作方法总结、参数化列表防止SQL注入总结
2.6 Python数据库编程
学习目标
1. 能够说出数据库编程的5个步骤
2. 能够使用python从数据库取出数据操作
--------------------------------------------------------------------------------
2.6.1 数据库编程概述
数据库编程就是针对数据库的操作，通过编写程序的方式，让程序做为数据库的客户端进行数据库操作。
通过使用程序代码的方式去连接数据库服务器，通过和服务器进行交互完成对数据库的增删改查的方式，就称为数据库编程。
数据库概念包含 数据库文件、服务器和数据库客户端，pymysql就是一种客户端。
pymysql的安装：
1. 联网pip命令安装
a. pip3 install PyMySQL
b. 在使用该命令的时候可能会提示需要管理员权限进行安装，只需要在前面加上sudo即可；
2. 使用 git 命令下载安装包安装(你也可以手动下载)：
a. git clone https://github.com/PyMySQL/PyMySQL
b. cd PyMySQL/
c. python3 setup.py install
i. 如果有安装包直接切换路径，和setup.py同级，再安装
1>pymysql基本操作方法总结：
1>Python 中操作数据库，要经过五个步骤
■ 1.连接数据库，2.获取游标，3.数据库操作，4.关闭游标，5.关闭数据库连接
2>数据库对象
■ 获取游标
cursor()
■ 提交数据
commit()
■ 撤销操作
rollback()
■ 关闭连接
close()
3>游标对象
■ 执行SQL语句
execute()
■ 获取一条查询 结果
fetchone()
■ 获取指定条数查询结果
fetchmany(n)
■ 获取所有查询 结果
fetchall()
■ 关闭游标
close()
2.参数化列表防止SQL注入总结
1>什么是SQL注入？
■ 是指在连接SQL语句时，用户传入了非法的数据，使SQL语句的意义发生变化，导至数据泄露
2>产生原因:
■ 后台对用户提交的带有恶意的数据和 SQL 进行字符串方式的拼接，得到了脱离原意的 SQL 语句，从而影响了 SQL 语句的语义，最终产生数据泄露的现象。
3>如何防止:
■ SQL 语句的参数化, 将 SQL 语句的所有数据参数存在一个列表中传递给 execute 函数的第二个参数
4>注意:
■ sql语句中 -- 代表注释的意思
■ 此处不同于python的字符串格式化，必须全部使用%s占位
2.6.2 Python 中操作 MySQL 步骤
在 Python 中操作数据库，要经过五个步骤，分别是连接数据库，获取游标，数据库操作，关闭游标，关闭数据库连接。
这个过程和实际仓库操作相同，比如现在要去一个实际的仓库。
2.6.3 pymysql 操作
在 Pyhton 中，使用 Pymysql 模块来对数据库进行编程。
导入模块(connect = Connection = Connect)
from pymysql import Connect
<1>创建连接对象 conn=Connect(参数列表)
目的：用于建立代码与数据库的连接
* 参数host：连接的mysql主机，如果本机是'localhost'
* 参数port：连接的mysql主机的端口，默认是3306
* 参数database：数据库的名称
* 参数user：连接的用户名
* 参数password：连接的密码
* 参数charset：通信采用的编码方式，推荐使用utf8
<2>通过连接对象获取游标对象 cur = conn.cursor()
创建Cursor游标对象 目的: 用于执行sql语句并获得结果
<3>使用游标执行SQL语句 cur.execute(operation , [parameters])
1>sql_str = '''select * from students;'''
■ 注意：以字符串形式书写SQL语句,因为SQL语句中也会出现字符串，所以建议使用三引号 ``` 引号形式将SQL诗句引起来
2>执行SQL语句row_count = cur.execute(sql_str)
■ 注意：执行SQL语句，返回受影响的行数，主要用于执行insert、update、delete语句
打印row_count输出受影响的行数
3>取出结果：
■ 注意：因为在获取数据时，游标是移动的，所以前面取过的数据，后面不会再取了。
■ 1.获取结果集中的一条 row_one = cur.fetchone()
返回一个元组 如 (1,'妲己',18)
■ 2.获取结果集中指定条数 row_many = cur.fetchmany(2)
返回一个元组 如 ((1,'妲己',18),(2,'公孙离',20)）
cur.fetchmany(n)获取结果集中的n条，返回一个元组
■ 3.获取结果集中的所有 row_all = cur.fetchall()
执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回. 如((1,'妲己',18),（2,'公孙离',20),(3,'姜子牙',28))
4>提交数据 conn.commit()
■ 撤销数据 conn.rollback()
<4>关闭游标 cur.close()
<5>关闭连接 conn.close()
2.6.4 使用 pymsql 完成数据查询
准备数据
1>创建数据库
■ create database python_db charset=utf8;
2>使用数据库
■ use python_db;
3>students表
■ 注意：unsigned表示无符号的意思，也就是非负数，只用于整型
■ create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,
height decimal(5,2),
gender enum('男','女','中性','保密') default '保密',
cls_id int unsigned default 0,
is_delete int default 0);
4>classes表
■ create table classes (
id int unsigned auto_increment primary key not null,
name varchar(30) not null);
5>向students表中插入数据
■ insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);
6>向classes表中插入数据
■ insert into classes values (0, "python_01期"), (0, "python_02期");
查询数据
# 导入模块
from pymysql import connect
# 连接数据库
conn = connect(host='localhost', port=3306, database='python_db', user='root', password='123123',charset='utf8')
# 获取游标
cur = conn.cursor()
# 以字符串形式书写SQL语句,因为SQL语句中也会出现字符串，所以建议使用 ``` 引号形式将SQL诗句引起来
sql_str = '''select * from students;'''
# 执行SQL语句
row_count = cur.execute(sql_str)
# 显示执行 SQL 语句影响的行数
print(row_count)
# 获取一条记录
row_one = cur.fetchone()
# 显示获取的记录
print(row_one)
# 获取多条记录
row_many = cur.fetchmany(4)
# 遍历输出所有的结果
for t in row_many:
print(t)
# 获取所有的数据
row_all = cur.fetchall()
# 遍历输出所有的结果
for t in row_all:
print(t)
# 关闭游标
cur.close()
# 关闭数据库
conn.close()
注意：因为在获取数据时，游标是移动的，所以前面取过的数据，后面不会再取了。
增删改
# 导入模块
from pymysql import connect
# 连接数据库
conn = connect(host='localhost', port=3306, database='python_db', user='root', password='123123',charset='utf8')
# 获取游标
cur = conn.cursor()
# 以字符串形式书写SQL语句
# sql_str = '''insert into students values(0,'新来的',20,180,'男',1,1)'''
# sql_str = '''update students set name = '王钢蛋' where name = '新来的'; '''
sql_str = '''delete from students where name='王钢蛋'; '''
# 执行SQL语句
row_count = cur.execute(sql_str)
# 在执行增删改操作时，需要向数据库提交操作，否则操作不成功
conn.commit()
# 关闭游标
cur.close()
# 关闭数据库
conn.close()
回滚(取消操作)
# 导入模块
from pymysql import connect
# 连接数据库
conn = connect(host='localhost', port=3306, database='python_db', user='root', password='123123',charset='utf8')
# conn.autocommit(True)
# 获取游标
cur = conn.cursor()
# 以字符串形式书写SQL语句
sql_str = '''insert into students values(0,'新来的',20,180,'男',1,1)'''
#插入10条数据
for i in range(10):
# 执行SQL语句
row_count = cur.execute(sql_str)
# 在执行增删改操作时，如果不想提交前面的修改操作，可以使用 rollback 回滚取消操作
conn.rollback()
# 关闭游标
cur.close()
# 关闭数据库
conn.close()
2.6.5 参数化列表防止SQL注入
什么是SQL注入？
是指在连接SQL语句时，用户传入了非法的数据，使SQL语句的意义发生变化，导至数据泄露
产生原因:
后台对用户提交的带有恶意的数据和 SQL 进行字符串方式的拼接，得到了脱离原意的 SQL 语句，从而影响了 SQL 语句的语义，最终产生数据泄露的现象。
如何防止:
SQL 语句的参数化, 将 SQL 语句的所有数据参数存在一个列表中传递给 execute 函数的第二个参数
注意:
sql语句中 -- 代表注释的意思
此处不同于python的字符串格式化，必须全部使用%s占位
# 导入模块
from pymysql import connect
find_name = input("请输姓名：")
# 连接数据库
conn = connect(host='localhost', port=3306, database='python_db', user='root', password='123123', charset='utf8')
# 获得Cursor对象
cur = conn.cursor()
# # # 非安全的方式
# # # 输入 "小明" or 1
# sql = '''select * from students where name=%s''' % find_name
# print("""sql===>%s<====""" % sql)
# # 执行select语句，并返回受影响的行数：查询所有数据
# count = cur.execute(sql)
# 安全的方式
# 构造参数列表
params = [find_name]
sql = '''select * from students where name=%s;'''
print("""sql===>%s<====""" % sql)
# 执行select语句，execute方法在内部实现了防SQL注入的功能，但具体如何实现并不清楚，隐藏了细节
count = cur.execute(sql, params)
# 注意：
# 如果要是有多个参数，需要进行参数化
# 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可
# 打印受影响的行数
print(count)
# 获取查询的结果
result = cur.fetchall()
# 打印查询的结果
print(result)
# 关闭Cursor对象
cur.close()
# 关闭Connection对象
conn.close()
2.6.6 数据库编程练习
准备数据
创建数据库
■ create database JDDB charset=utf8;
■ use JDDB
导入数据
■ source JDDB.sql
代码实现
1>判断当前是否是主程序
if __name__ == '__main__':
main()
2>实现主函数
def main():
# 创建 JD 类的对象
jd = JD()
# 运行 run 方法
jd.run()
3>实现 JD 类 因为所有的操作都要操作数据库，为了避免代码冗余，所以将连接和关闭操作设计到类中
对象创建成功，数据库就连接成功，对象销毁时，数据库关闭
class JD(object):
"""JD 类，提供商品查询服务"""
# 将数据库连接操作放到初化方法中，对象创建时，自动连接数据库
def __init__(self):
# 连接数据库
self.__conn = connect(host='localhost', port=3306, database='JDDB', user='root', password='123123', charset='utf8')
# 获取游标
self.__cur = self.__conn.cursor()
# 将数据库关闭操作放到 __del__方法中，当对象销毁时，自动关闭数据库
def __del__(self):
# 关闭游标
self.__cur.close()
# 关闭数据库
self.__conn.close()
4>实现 run 方法 因为需要重复选择，所以要用死循环
# run 方法，提供显示接口
def run(self):
while True:
print("1查询所有商品信息")
print("2查询所有商品在种类信息")
print("3查询所有商品在品牌信息")
print("4添加商品种类")
print("5根据id查询商品信息")
print("6根据id查询商品信息安全方式")
selectID = input('请输入要执行的功能编号：')
if selectID == '1':
# 查询所有商品信息
self.fetch_all_info()
elif selectID == '2':
# 查询种类信息
self.fetch_cate()
elif selectID == '3':
# 查询品牌信息
self.fetch_brand()
elif selectID == '4':
# 添加一个商品类型
self.add_info()
elif selectID == '5':
# 通过ID 查找商品
self.find_info()
elif selectID == '6':
# 通过ID 查找商品 防SQL注入
self.find_info_safe()
else:
print('输入行号不正确！')
5>实现一个显示方法，用来输出结果
# 用来显示结果的方法，私有，对外不可见
def __show_result(self, result):
for t in result:
print(t)
6>实现查询所有商品方法
# 查询所有商品信息
def fetch_all_info(self):
sql_str = ''' select * from goods;'''
self.__cur.execute(sql_str)
self.__show_result(self.__cur.fetchall())
7>查询种类信息
# 查询种类信息
def fetch_cate(self):
sql_str = ''' select * from goods_cates;'''
self.__cur.execute(sql_str)
self.__show_result(self.__cur.fetchall())
8>查询品牌信息
# 查询品牌信息
def fetch_brand(self):
sql_str = ''' select * from goods_brands;'''
self.__cur.execute(sql_str)
self.__show_result(self.__cur.fetchall())
9>添加商品类型
# 添加商品类型
def add_info(self):
new_type = input('请输入商品类型:')
sql_str = ''' insert into goods_cates values(0,"%s");'''% new_type #注意,这里占位符要加引号
self.__cur.execute(sql_str)
self.__conn.commit() # 修改操作要手动提交
10>根据id 查找商品
# 根据id查找商品
def find_info(self):
id = input('请输入ID：')
sql_str = ''' select * from goods where id=%s;'''%id
self.__cur.execute(sql_str)
self.__show_result(self.__cur.fetchall())
11>根据id 查找商品,案例防注入
# 根据id查找商品,案例防SQL注入
def find_info_safe(self):
id = input('请输入ID：')
sql_str = ''' select * from goods where id=%s;'''
self.__cur.execute(sql_str,(id,))
self.__show_result(self.__cur.fetchall())
