数据库进阶

python关于mysql的API－－pymysql模块

pymysql是Python中操作MySQL的模块，其使用方法和py2的MySQLdb几乎相同。

模块安装

pip install pymysql

在Pycharm里执行sql会遇到“SQL dialect is not configured”，解决办法：

在File---->Setting--->Languages & Frameworks--->SQL Dialects中，选择对应的数据库，如MySQL，之后点击保存即可

 

执行sql语句

在Python里写原生sql,就是把原生sql放到了一个接口里去执行

import pymysql

# 1. 连接数据库后返回但是一个连接对象，有这个连接对象，就可以对数据库进行操作
conn = pymysql.connect(
    host = "127.0.0.1",   # 数据库的ip地址
    port = "3306",        # 数据库的端口号
    user = "root",        # 登陆数据库的用户名
    passwd = "123456",    # 登陆数据库的密码
    db = "lesson54"       # 要连接的数据库，必须提前创建好，否则会连接出错
)

# 2.获取游标
# 有了游标，具体的sql语句就可以通过这个游标来执行了
cursor = conn.cursor()  # 返回的结果是一个元组的形式
cursor1 = conn.cursor(cursor1 = pymysql.cursors.DictCursor) # 返回的就是字典形式的光标

# 通过execute执行sql语句
# cursor.execute("具体的sql语句，增删改查")

# 3. 创建表
sql = "create table TEST(id int,name varchar (20))"  # 创建表的语句，复制给sql变量
cursor.execute(sql)   # 调用execute，创建表
cursor.execute()

# 4. 插入数据
ret =cursor.execute("insert into test values (1,'alex'),(2,'alvin')")
print(ret)  # 2, 返回的结果就是影响的行数

# 5. 查询
ret2 = cursor.execute("select * from test")
print(ret2)

# 6.拿到查询到的结果
print(cursor.fetchone())   # 从查询到的结果里取出一条，此时游标已经不在第一行了，而在下一行了
print(cursor.fetchall())   # 取出所有查询到的结果，所以此时取到的是剩下的所有数据
print(cursor.fetchmany(3))  # 指定取出3条查询结果

# 7. 调节游标的位置
# 上面6的步骤，游标的位置发生了变化
# 相对调节：根据当前游标的位置，进行向上或者向下调节几行
cursor.scroll(1,mode="relative") # 正数:向下， 负数：向上

# 绝对调节：不管当前游标在第几行，直接调整到第一行，调到几行数字就写几
cursor.scroll(1,mode="absolute")  



# .创建了连接对象，就要提交，提交之后再把连接关闭
conn.commit()
cursor.close()
conn.close()

如果放到了ORM里，就会完全有一套自己的语法去代替sql语句

 

事务

事务命令

事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。

数据库开启事务命令 

start transaction 开启事务
Rollback 回滚事务,即撤销指定的sql语句(只能回退insert delete update语句)，回滚到上一次commit的位置
Commit 提交事务，提交未存储的事务
savepoint 保留点 ，事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)   

 转账实例：

UPDATE account set balance=balance-5000 WHERE name=”yuan”;
UPDATE account set balance=balance+5000 WHERE name=”xialv”;

-- 创建表
create table test2(id int PRIMARY KEY auto_increment,name VARCHAR(20)) engine=innodb;

-- 插入数据
INSERT INTO test2(name) VALUE ("alvin"),
                              ("yuan"),
                              ("xialv");



start transaction; -- 开启事务
insert into test2 (name)values('silv');
select * from test2;
commit;   -- 提交事务


-- 保留点

start transaction;
insert into test2 (name)values('wu');
savepoint insert_wu; -- 给上面刚才insert的命令起了个名字叫insert_wu,并设置一个保留点，对重要的sql,要紧挨着设置保留点
select * from test2;



delete from test2 where id=4;
savepoint delete1;
select * from test2;


delete from test2 where id=1;
savepoint delete2;
select * from test2;

rollback to delete1;  -- 回滚到某个设置的节点


select * from test2;

savepoint

python中调用数据库启动事务的方式

import pymysql

#添加数据

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='yyy')

cursor = conn.cursor()


try:
    insertSQL0="INSERT INTO ACCOUNT2 (name,balance) VALUES ('oldboy',4000)"
    
    # 下面的是一组事务
    insertSQL1="UPDATE account2 set balance=balance-30 WHERE name='yuan'"
    insertSQL2="UPDATE account2 set balance=balance+30 WHERE name='xialv'"

    cursor = conn.cursor()

    cursor.execute(insertSQL0)
    conn.commit()

    cursor.execute(insertSQL1)
    raise Exception  # 模拟inserSQL2出现异常
    cursor.execute(insertSQL2)
    cursor.close()
    conn.commit()

except Exception as e:

    conn.rollback()  # 上面模拟insertSQL2出现异常了，就会执行回滚，回滚到上一次commit的地方
    conn.commit()


cursor.close()
conn.close()

事务特性

<1> 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

<2> 一致性（Consistency）：事务前后数据的完整性必须保持一致。在事务执行之前数据库是符合数据完整性约束的，无论事务是否执行成功，事务结束后的数据库中的数据也应该是符合完整性约束的。在某一时间点，如果数据库中的所有记录都能保证满足当前数据库中的所有约束，则可以说当前的数据库是符合数据完整性约束的。
比如删部门表前应该删掉关联员工（已经建立外键），如果数据库服务器发生错误，有一个员工没删掉，那么此时员工的部门表已经删除，那么就不符合完整性约束了，所以这样的数据库也就性能太差啦！

<3>隔离性（Isolation）：事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。

<4>持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

三、隔离性：
将数据库设计为串行化程的数据库，让一张表在同一时间内只能有一个线程来操作。如果将数据库设计为这样，那数据库的效率太低了。所以数据库的设计这没有直接将数据库设计为串行化，而是为数据库提供多个隔离级别选项，使数据库的使用者可以根据使用情况自己定义到底需要什么样的隔离级别。

不考虑隔离性可能出现的问题：

脏读

--一个事务读取到了另一个事务未提交的数据,这是特别危险的，要尽力防止。
        a 1000
        b 1000
        a:
            start transaction；
            update set money=money+100 where name=b;
        b:
            start transaction;
            select * from account where name=b;--1100
            commit;
        a:
            rollback;
        b:  start transaction;
            select * from account where name=b;--1000

不可重复读

--在一个事务内读取表中的某一行数据，多次读取结果不同。（一个事务读取到了另一个事务已经提交
-- 的数据--增加记录、删除记录、修改记录），在某写情况下并不是问题，在另一些情况下就是问题。

a:
start transaction;
select 活期账户 from account where name=b;--1000    活期账户：1000
select 定期账户 from account where name=b；--1000   定期账户:1000
select 固定资产 from account where name=b；--1000   固定资产：1000
------------------------------
b:
start transaction;
update set money=0 where name=b;
commit;
------------------------------
select 活期+定期+固定 from account where name=b; --2000 总资产： 2000

虚读

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一个事务读取到了另一个事务已经提交的数据---增加记录、删除记录），在某写情况下并不是问题，在另一些情况下就是问题。

b 1000
c 2000
d 3000
a:
start transaction
select sum(money) from account;---3000       3000
-------------------
d:start transaction;
insert into account values(d,3000);
commit;
-------------------
select count(*)from account;---3                         3
3000/3 = 1000                                            1000

四个隔离级别：
Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）不可以避免虚读
Read committed：可避免脏读情况发生（读已提交）
Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

安全性考虑：Serializable>Repeatable read>Read committed>Read uncommitted
数据库效率：Read uncommitted>Read committed>Repeatable read>Serializable

一般情况下，我们会使用Repeatable read、Read committed mysql数据库默认的数据库隔离级别Repeatable read

mysql中设置数据库的隔离级别语句：

set [global/session] transaction isolation level xxxx;

如果使用global则修改的是数据库的默认隔离级别，所有新开的窗口的隔离级别继承自这个默认隔离级别如果使用session修改，则修改的是当前客户端的隔离级别，和数据库默认隔离级别无关。当前的客户端是什么隔离级别，就能防止什么隔离级别问题，和其他客户端是什么隔离级别无关。
mysql中设置数据库的隔离级别语句：

select @@tx_isolation;