Day12 Python操作MySQL详解

第九章 Python操作数据库

9.1. 操作简介

我们在使用到python进行一些业务操作的时候，经常性的要与数据库进行交互。可能要读取到数据库的表中的数据，也可能需要将一些数据写入到表中，完成数据库的数据更新的操作。此时就需要使用python与数据库进行交互了。

python有着非常强大的库，为python丰富了各种各样的功能，其中就包括了对数据库的操作。在python中如果需要操作mysql数据库，我们需要首先安装对应的库 pip install pymysql

9.2. 数据库的基本操作

9.2.1. 连接到数据库

import pymysql

# 建立与数据库的连接对象，需要指定与数据库的连接相关的属性
db_connection = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    password="123456",
    database="mydb1"
)

# 这个数据库连接对象，在使用结束后，需要调用close来释放资源
db_connection.close()

9.2.2. 创建操作对象

# 获取数据库操作对象
# 获取到的是一个光标对象，数据库所有的操作都需要使用这个对象来完成
# 例如：DDL、DML、DQL语句
db_cursor = db_connection.cursor()

# 数据库操作对象在使用结束后，也是需要调用close来释放资源
# 释放需要在数据库连接对象释放之前
db_cursor.close()

9.2.3. 执行DDL、DML操作

# 准备执行的SQL语句
sql = "insert into stu values ('S_1012', 'xiaoMing', 34, 'male')"
# 使用数据库操作对象，执行SQL语句
# 执行的返回值是一个数字，表示多少行数据受影响 (affected rows)
db_cursor.execute(sql)
# 在执行DDL、DML操作的时候，最后需要使用commit来提交，才可以影响到数据库中的数据
# 需要使用数据库连接对象来提交
db_connection.commit()

9.2.4. 执行DQL操作

# 准备执行的SQL语句
sql = "select * from stu"
# 使用数据库操作对象，执行SQL语句
# 执行的返回值是一个数字，表示查询到了多少行的数据
db_cursor.execute(sql)

# 获取查询到的一行数据，将一行数据存入一个元组返回
# 类似于迭代器，重复调用fetchone的时候，会逐行获取到后续的每一行内容
db_cursor.fetchone()

# 获取查询到的N行数据，默认是1行
# 将查询到的每一行的数据存入一个元组，再将这些元组存入一个大的元组返回
# 即返回的结果是一个二维元组
db_cursor.fetchmany(N)

# 获取查询到的所有的数据
# 将查询到的每一行的数据存入一个元组，再将这些元组存入一个大的元组返回
# 即返回的结果是一个二维元组
db_cursor.fetchall()

9.3. SQL注入问题

9.3.1. 什么是SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

9.3.2. SQL注入演示

准备数据

drop table if exists bank_account;
create table bank_account (
	id int primary key auto_increment comment '主键',
	account_id varchar(18) not null comment '用户账号',
	account_balance double(10,2) comment '账户余额',
	user_name varchar(20) not null comment '用户名称',
	user_pwd varchar(128) unique comment '用户密码',
	user_idcard varchar(18) unique comment '身份证',
	oper_time timestamp comment '操作日期',
	gender enum('f','m') comment 'f 表示女性，m表示男性'
);

insert into bank_account values (null,'6225113088436225',200000,'zhugeliang','zgl123456','100000100010101000','2019-01-01 13:10:10','m');
insert into bank_account values (null,'6225113088436226',1000,'zhouyu','zy123456','100000100010101001','2019-03-01 14:10:10','m');
insert into bank_account values (null,'6225113088436227',210000,'caocao','cc123456','100000100010101002','2019-04-01 14:10:10','m');
insert into bank_account values (null,'6225113088436228',500,'niumo','nm123456','100000100010101003','2019-03-01 10:10:10','m');
commit;

登录案例演示

import pymysql.cursors


# 使用登录验证的案例，演示SQL注入
class BankServer:
    @staticmethod
    def login(username, password):
        db = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='123456',
                             database='pydb')
        with db.cursor() as cursor:     # type:'pymysql.cursors.Cursor'
            # 1. 拼接查询的SQL语句
            # sql = f"select * from bank_account where user_name = '{username}' and user_pwd = '{password}'"

            # 1. 拼接查询的SQL语句，但是解决SQL注入
            sql = "select * from bank_account where user_name = %s and user_pwd = %s"
            # 2. 执行查询语句
            #    为了解决SQL注入，我们将需要用户输入的部分，使用%s来占位
            cursor.execute(sql, (username, password))
            # 3. 获取查询到的一条数据
            return cursor.fetchone()


# 模拟客户端操作
username = input("输入用户名: ")
password = input("输入密码: ")
# 登录验证
login_user = BankServer.login(username, password)
if login_user:
    print(login_user)
else:
    print("登录失败")

请输入用户名: root
请输入密码: asdasd’ or 1='1
(1, ‘6225113088436225’, 200000.0, ‘zhugeliang’, ‘zgl123456’, ‘100000100010101000’, datetime.datetime(2019, 1, 1, 13, 10, 10), ‘m’)

可以看到，在我输入密码的时候，就可以使用特定的输入方式，绕过了登录的检查！

9.3.3. 解决SQL注入

SQL注入本质的问题就是没有对用户输入的内容进行严格的校验，导致用户可以通过自己输入的内容，修改SQL语句的查询结构，达到了自己的破解的目的。那么解决方案其实也就简单了，只需要限制用户输入的内容的格式校验即可。在pymysql模块中，使用execute语句执行指定的SQL语句，在这个函数中，第一个参数就是需要执行的SQL，而我们可以在这样的SQL语句中，使用占位符完成对某些部分的占位，而具体对应的数据，可以将其封装入一个元组，传入第二个参数即可。

# 未解决SQL注入的时候
cursor.execute("select * from bank_account where username = %s and password = %s" % (username, password))
# 修改如下
cursor.execute("select * from bank_account where username = %s and password = %s", (username, password))

9.4. 事物支持

9.4.1. 事务的介绍

当一个业务需要处理多个DML操作的时候，这个业务需要当作一个整体来处理。在处理的过程中，如果有失败或者异常，我们需要回到业务开始的时候；如果处理成功，我们再将数据持久化到磁盘中。这样一个过程，我们就称为一个“事物”。

事物指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。

事物具有一下特性：

• **原子性（Atomicity）：**指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• **一致性（Consistency）：**事务必须使数据库从一个一致性状态变换到另外一个一致性状态。转账前和转账后的总金额不变。
• **隔离性（Isolation）：**事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
• **持久性（Durability）：**指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

9.4.2. MySQL的事务

默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。

开启事务：start transaction；
结束事务：commit或rollback；

事务开始于：

• 连接到数据库上，并执行一条DML语句insert、update或delete
• 前一个事务结束后，又输入了另一条DML语句

事务结束于：

• 执行commit或rollback语句。
• 执行一条DDL语句，例如create table语句，在这种情况下，会自动执行commit语句。
• 执行一条DDL语句，例如grant语句，在这种情况下，会自动执行commit。
• 断开与数据库的连接。
• 执行了一条DML语句，该语句却失败了，在这种情况中，会为这个无效的DML语句执行rollback语句。

示例:sql语句实现事务支持

1.回滚情况

START TRANSACTION;
UPDATE account SET balance=balance-10000 WHERE id=1;
SELECT * FROM account;
UPDATE account SET balance=balance+10000 WHERE id=2;
ROLLBACK;

2.提交情况

START TRANSACTION;
UPDATE account SET balance=balance-10000 WHERE id=1;
SELECT * FROM account;
UPDATE account SET balance=balance+10000 WHERE id=2;
COMMIT;

9.4.3. python事务案例

案例分析：

一个账号fromAccount向另外一个账号toAccount转账money元钱！

分析：

• 检查两个账号是否存在，不存在的话，结束转账行为
• 检查转出账号里的金额是否充足，不充足的话结束转账行为；充足的话，进行扣款money元
• 转入账号进行增加money元

import pymysql


class PayError(Exception):
    """
    账号异常类，
    flag = 0 : 付款账号不存在
    flag = 1 : 转入账号不存在
    flag = 2 : 付款账号余额不足
    """
    __slots__ = 'flag'

    def __init__(self, flag, message):
        super().__init__(message)
        self.flag = flag


# 设计方法，实现转账的需求
def pay(from_account, to_account, money):
    """
    一个人给另一个人转账
    :param from_account: 付款账号
    :param to_account: 到账账号
    :param money: 转账金额
    :return:
    """
    # 创建数据库连接对象
    db = pymysql.connect(host="localhost", port=3306, user="root", password="123456", database="mydb1")
    # 获取数据库操作对象
    cor = db.cursor()

    # 检查付款账号是否存在
    res1 = cor.execute("select * from bank_account where user_name = %s", from_account)
    if res1 == 0:
        raise PayError(0, "付款账号不存在")

    # 检查余额是否足够
    rest_money = cor.fetchone()[2]
    if rest_money < money:
        raise PayError(2, "账号余额不足")

    # 检查转入账号是否存在
    res2 = cor.execute("select * from bank_account where user_name = %s", to_account)
    if res2 == 0:
        raise PayError(1, "转入账号不存在")

    # 开始转账
    try:
        # 付款账号扣款
        cor.execute("update bank_account set account_balance = account_balance - %s where user_name = %s", (money, from_account))
        # 收款账号增加
        cor.execute("update bank_account set account_balance = account_balance + %s where user_name = %s", (money, to_account))
        # 提交事物
        db.commit()
    except Exception as e:
        print("="*30, e)
        db.rollback()

    cor.close()
    db.close()


from_account = input("请输入需要付款的账号名: ")
to_account = input("请输入需要收款的账号名: ")
money = float(input("请输入需要转账的金额: "))

pay(from_account, to_account, money)

9.5. 数据库操作封装

我们已经可以实现用python来操作数据库中的数据，但是操作的方式比较繁琐，而且重复的操作比较多。因此我们是否可以将一些常见的、重复性功能封装起来，哪里需要用，就在哪里调用呢？

import pymysql


class DB:
    # 构造对象，创建数据库连接对象，并创建数据库操作对象
    def __init__(self, *, host=None, port=None, user=None, password=None, database=None):
        self.db = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
        self.cur = self.db.cursor()

    # 执行DML、DDL操作
    def execute(self, sql, params=None):
        try:
            self.cur.execute(sql, params)
            self.db.commit()
            print("execute: ", sql, params)
        except Exception as e:
            print(e)
            self.db.rollback()

    # 执行DQL操作
    def query_all(self, sql):
        self.cur.execute(sql)
        return self.cur.fetchall()

    def __del__(self):
        self.cur.close()
        self.db.close()


# 将数据库连接属性的配置写到类的外部
db_connection_prop = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "mydb1"
}
# 使用这个封装好的类
db = DB(**db_connection_prop)

# 执行查询操作
# res1 = db.query_all("select * from bank_account")
# for l in res1:
#     print(l)

# 执行DML操作
# db.execute("delete from bank_account where id = %s", 4)
# res2 = db.query_all("select * from bank_account")
# for l in res1:
#     print(l)

db.execute("update bank_account set account_balance = account_balance - %s where user_name = %s", (100, "zhouyu"))
res3 = db.query_all("select * from bank_account")
for i in res3:
    print(i)