-day29ython操作MySQL和实战

day29 Python操作MySQL和实战

课程目标：掌握事务和锁以及Python操作MySQL的各种开发必备知识。

课程概要：

• 事务
• 锁
• 数据库连接池
• SQL工具类
• 其他

1. 事务

innodb引擎中支持事务，myisam不支持。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(32) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

例如：Emma 给 白居易 转账 100，那就会涉及2个步骤。

• Emma账户 减100
• 白居易账户 加 100

这两个步骤必须同时完成才算完成，并且如果第一个完成、第二步失败，还是回滚到初始状态。

事务，就是来解决这种情况的。 大白话：要成功都成功；要失败都失败。

事务的具有四大特性（ACID）：

• 原子性（Atomicity）

  原子性是指事务包含的所有操作不可分割，要么全部成功，要么全部失败回滚。
  

• 一致性（Consistency）

  执行的前后数据的完整性保持一致。
  

• 隔离性（Isolation）

  一个事务执行的过程中,不应该受到其他事务的干扰。
  

• 持久性（Durability）

  事务一旦结束,数据就持久到数据库
  

1.1 MySQL客户端

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | baijuyi |    5    |
|  2 |  emma   |    6    |
+----+---------+---------+
3 rows in set (0.00 sec)

mysql> begin;  -- 开启事务 start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update users set amount=amount-2 where id=1;   -- 执行操作
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update users set amount=amount+2 where id=2;   -- 执行操作
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;  -- 提交事务  rollback; -- 回滚
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | baijuyi |    3    |
|  2 |  emm a  |    8    |
+----+---------+---------+
3 rows in set (0.00 sec)

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | baijuyi |    3    |
|  2 |  emm a  |    8    |
+----+---------+---------+
3 rows in set (0.00 sec)

mysql> begin; -- 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> update users set amount=amount-2 where id=1; -- 执行操作（此时数据库中的值已修改）
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback; -- 事务回滚（回到原来的状态）
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | baijuyi |    3    |
|  2 |  emm a  |    8    |
+----+---------+---------+
3 rows in set (0.00 sec)

1.2 Python代码

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()

# 开启事务
conn.begin()

try:
    cursor.execute("update users set amount=1 where id=1")
    int('asdf')
    cursor.execute("update tran set amount=2 where id=2")
except Exception as e:
    # 回滚
    print("回滚")
    conn.rollback()
else:
    # 提交
    print("提交")
    conn.commit()

cursor.close()
conn.close()

2. 锁

在用MySQL时，不知你是否会疑问：同时有很多做更新、插入、删除动作，MySQL如何保证数据不出错呢？

MySQL中自带了锁的功能，可以帮助我们实现开发过程中遇到的同时处理数据的情况。对于数据库中的锁，从锁的范围来讲有：

• 表级锁，即A操作表时，其他人对整个表都不能操作，等待A操作完之后，才能继续。
• 行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待A操作完之后，才能继续。

MYISAM支持表锁，不支持行锁；
InnoDB引擎支持行锁和表锁。

即：在MYISAM下如果要加锁，无论怎么加都会是表锁。
    在InnoDB引擎支持下如果是基于索引查询的数据则是行级锁，否则就是表锁。

所以，一般情况下我们会选择使用innodb引擎，并且在 搜索 时也会使用索引（命中索引）。

接下来的操作就基于innodb引擎来操作：

CREATE TABLE `L1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



insert into L1(name,count) values("白居易",10000),("杜甫",20000),("emma",100);

在innodb引擎中，update、insert、delete的行为内部都会先申请锁（排它锁），申请到之后才执行相关操作，最后再释放锁。

所以，当多个人同时像数据库执行：insert、update、delete等操作时，内部加锁后会排队逐一执行。

而select则默认不会申请锁。

select * from xxx;

如果，你想要让select去申请锁，则需要配合 事务 + 特殊语法来实现。

• for update，排它锁，加锁之后，其他不可以读写。

  begin; 
  	select * from L1 where name="白居易" for update;    -- name列不是索引（表锁）
  commit;
  

  begin; -- 或者 start transaction;
  	select * from L1 where id=1 for update;			  -- id列是索引（行锁）
  commit;
  

• lock in share mode ，共享锁，加锁之后，其他可读但不可写。

  begin; 
  	select * from L1 where name="白居易" lock in share mode;    -- 假设name列不是索引（表锁）
  commit;
  

  begin; -- 或者 start transaction;
  	select * from L1 where id=1 lock in share mode;           -- id列是索引（行锁）
  commit;
  

2.1 排它锁

排它锁（ for update），加锁之后，其他事务不可以读写。

应用场景：总共100件商品，每次购买一件需要让商品个数减1 。

A: 访问页面查看商品剩余 100
B: 访问页面查看商品剩余 100

此时 A、B 同时下单，那么他们同时执行SQL：
	update goods set count=count-1 where id=3
由于Innodb引擎内部会加锁，所以他们两个即使同一时刻执行，内部也会排序逐步执行。


但是，当商品剩余 1个时，就需要注意了。
A: 访问页面查看商品剩余 1
B: 访问页面查看商品剩余 1

此时 A、B 同时下单，那么他们同时执行SQL：
	update goods set count=count-1 where id=3
这样剩余数量就会出现 -1，很显然这是不正确的，所以应该怎么办呢？


这种情况下，可以利用 排它锁，在更新之前先查询剩余数量，只有数量 >0 才可以购买，所以，下单时应该执行：
	begin; -- start transaction;
	select count from goods where id=3 for update;
	-- 获取个数进行判断
	if 个数>0:
		update goods set count=count-1 where id=3;
	else:
		-- 已售罄
	commit;

基于Python代码示例：

import pymysql
import threading


def task():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    # cursor = conn.cursor() 列名(键):获取的值(值)
	
    # 开启事务
    conn.begin()

    cursor.execute("select id,age from tran where id=2 for update")
    # fetchall 元组中套字典 ( {"id":1,"age":10},{"id":2,"age":10}, )   ((1,10),(2,10))
    # {"id":1,"age":10}    (1,10) 
    # fetchone  ((1,10),(2,10))
    result = cursor.fetchone()
    current_age = result['age']
    
    if current_age > 0:
        cursor.execute("update tran set age=age-1 where id=2")
    else:
        print("已售罄")

    conn.commit()

    cursor.close()
    conn.close()


def run():
    for i in range(5):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

2.2 共享锁

共享锁（ lock in share mode），可以读，但不允许写。

加锁之后，后续其他事物可以可以进行读，但不允许写（update、delete、insert），因为写的默认也会加锁。

Locking Read Examples

Suppose that you want to insert a new row into a table child, and make sure that the child row has a parent row in table parent. Your application code can ensure referential integrity throughout this sequence of operations.

First, use a consistent read to query the table PARENT and verify that the parent row exists. Can you safely insert the child row to table CHILD? No, because some other session could delete the parent row in the moment between your SELECT and your INSERT, without you being aware of it.

To avoid this potential issue, perform the SELECT using LOCK IN SHARE MODE:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

After the LOCK IN SHARE MODE query returns the parent 'Jones', you can safely add the child record to the CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the PARENT table waits until you are finished, that is, until the data in all tables is in a consistent state.

3. 数据库连接池

在操作数据库时需要使用数据库连接池。

pip3.9 install pymysql
pip3.9 install dbutils

import threading
import pymysql
from dbutils.pooled_db import PooledDB

# 连接池参数
MYSQL_DB_POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。
    # 如：0 = None = never 不自动ping检测
    # 1 = default = whenever it is requested, # 每次请求的时候
    # 2 = when a cursor is created,  # 每次创建cursor的时候
    # 4 = when a query is executed, # 每次执行sql的时候
    # 7 = always # 总是(每次设计到sql时)
  
  # 数据库参数
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


def task():
    # 去连接池获取一个连接，有就拿到连接，没有就阻塞
    conn = MYSQL_DB_POOL.connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    cursor.execute('select sleep(2)')
    result = cursor.fetchall()
    print(result)

    cursor.close()
    # 将连接交换给连接池
    conn.close()

def run():
    for i in range(10):
      # 创建十个线程,五个获取到线程，剩下五个阻塞等待
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

4. SQL工具类

基于数据库连接池开发一个公共的SQL操作类，方便以后操作数据库。

4.1 单例和方法

# db.py
import pymysql
from dbutils.pooled_db import PooledDB


class DBHelper(object):

    def __init__(self):
        # TODO 此处配置，可以去配置文件中读取。
        self.pool = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host='127.0.0.1',
            port=3306,
            user='root',
            password='root123',
            database='userdb',
            charset='utf8'
        )

    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

    def close_conn_cursor(self, *args):
        for item in args:
            item.close()

    def exec(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        conn.commit()

        self.close_conn_cursor(conn, cursor)

    def fetch_one(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = cursor.fetchone()

        self.close_conn_cursor(conn, cursor)
        return result

    def fetch_all(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = cursor.fetchall()

        self.close_conn_cursor(conn, cursor)

        return result


db = DBHelper()

在其他模块中引用

from db import db

db.exec("insert into d1(name) values(%(name)s)", name="白居易666")

ret = db.fetch_one("select * from d1")
print(ret)

ret = db.fetch_one("select * from d1 where id=%(nid)s", nid=3)
print(ret)

ret = db.fetch_all("select * from d1")
print(ret)

ret = db.fetch_all("select * from d1 where id>%(nid)s", nid=2)
print(ret)

4.2 上下文管理

如果你想要让他也支持 with 上下文管理。

with 获取连接：
	执行SQL（执行完毕后，自动将连接交还给连接池）

# db_context.py
import threading
import pymysql
from dbutils.pooled_db import PooledDB

# 创建一个全家变量
POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


class Connect(object):
    def __init__(self):
        self.conn = conn = POOL.connection()
        self.cursor = conn.cursor(pymysql.cursors.DictCursor)

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.conn.close()

    def exec(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        self.conn.commit()

    def fetch_one(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchone()
        return result

    def fetch_all(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchall()
        return result

在别的模块中调用

from db_context import Connect

with Connect() as obj:
    # print(obj.conn)
    # print(obj.cursor)
    ret = obj.fetch_one("select * from d1")
    print(ret)

    ret = obj.fetch_one("select * from d1 where id=%(id)s", id=3)
    print(ret)

5.其他

navicat，是一个桌面应用，让我们可以更加方便的管理MySQL数据库。

• mac系统：https://www.macdo.cn/17030.html
• win系统：
  • 链接: https://pan.baidu.com/s/13cjbrBquz9vjVqKgWoCQ1w 密码: qstp
  • 链接: https://pan.baidu.com/s/1JULIIwQA5s0qN98KP8UXHA 密码: p18f

总结

本节内容比较重要，也是开发中经常会使用到的技能。

• 事务，解决批量操作同时成功或失败的问题。
• 锁，解决并发处理的问题。
• 数据库连接池，解决多个人请求连接数据库的问题。
• SQL工具类，解决连接数据库代码重复的问题。
• navicat工具

第四模块总结

1. mysql数据库存储引擎

Innodb
		是MySQL5.5版本之后，默认的存储引擎，存储数据更加的安全。
		Innodb 支持事务，事务：用于将某些操作的多个sql作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据的完整性。
		innodb 支持外键和行级锁，在并发条件下要求数据的一致性，数据操作出来插入和查询之外，还包括很多更新和删除操作，那么innodb存储引擎是比较合适的。
		innodb会创建生成两个文杰，表结构文件和表数据结构

MyIsam 
		既不支持事务，也不支持外键，其优势是访问速度快，但是表级别的锁限制了它在读写负载方面的性能，因此它经常应用于只读或已读为主的数据场景。
		创建表时会生成三个文件，表结构文件，表数据文件，表索引文件。

Memory
		在内存中存储所有数据，应用于对非关键数据有快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用Hash索引，但是一旦服务关闭，表中的数据会丢失。主要用于临时数据存储。
		创建表会生成一个文件夹，表结构文件。

BLACKHOLE
		黑洞存储引擎，可以应用于准备复制中的分发主库。
		创建表时会生成一个文件夹，表结构文件。


# 查看当前数据库支持的存储引擎

show engines \G

2. 简述数据库常见数据类型

数字：
	整数：tinyint int bigint
	小数：float doubler decimal，decimal精准，内部原理已字符串形式去存，如果用小数，则推荐使用decimal。

字符串：
	char： 简单粗暴，浪费空间，存取速度快
	varvchar：精准，节省空间，存储速度慢
	text类型：用于保存变长的大字符串

时间类型：
	最常用，datetime
	
枚举类型：
	sex enum('male','female','保密') # 在指定范围内，多选一
	
集合类型：
	habby set('play','music','read','study') # 在指定范围内，多选多

3. 锁

myisam 支持表锁，不支持行锁 
innodb 引擎支持行锁和表锁


即：在myisam下如果要加锁，无论怎么加都会是表锁。
在innodb引擎下，如果基于索引查询的数据则是行级锁，否则就是表锁。

在innodb引擎中，update，insert，delete等行为内部都会先申请锁(排它锁)，申请到之后才会执行相关操作，最后再释放锁。

如果，需要让select去申请锁，则需要配合，事务+特殊语法来实现。

排它锁，写锁(for update)，加锁之后其他事务不可以读写
共享锁，读锁(lock in share mode),可以读，不允许写



思考：在什么时候需要手动加锁？
当执行select + update，insert，delete等组合操作是，比如抢购，秒杀等活动。

4. 程序和数据库结合使用的三种方式

1. 程序，调用存储过程
2. 程序，纯SQL语句
3. 程序，类和对象，即ORM,本质还是纯SQL

5. 其他注意事项

- 避免使用select *
- count(1)或count(列)代替count(*)
- 创建表时尽量用char代替varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引，经常使用多个条件查询时
- 使用连接查询来代替子查询
-  连表时注意条件类型需要一致
- 索引散列值时(重复少)不适合建索引，例如：性别
- 尽量使用短索引
	- create index 索引名称 on 表名(列名)
	- create index tit_index on tb1(title(20))

短索引说的是，如果某个自动内容特别多，但是比如前面20个文件就可以做一个区分了，那么就用前面20个字符进行索引，这样可以节约索引空间。