pymysql库官网下载 pymysql模块

数据库进阶

一、python关于mysql的API -----pymysql模块

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

二、pymysql模块安装

pip install pymysql

三、执行sql语句

#_*_ coding:utf-8 _*_
# Author:Simon
# Datetime:2019/9/27 8:51
# Software:PyCharm

import pymysql

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

cursor=conn.cursor()

cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)  //#更改获取数据结果的数据类型,默认是元组,可以改为字典等

# sql="CREATE TABLE TEST(id INT, name VARCHAR (20))"
# cursor.execute(sql)
# cursor.execute("INSERT INTO test VALUES (3,'simon1'),(4,'zhurui1')")

//查询
row_affected=cursor.execute("SELECT * FROM test")
# one=cursor.fetchone()
# all=cursor.fetchall()
# many=cursor.fetchmany(2)

print(cursor.fetchone())
print(cursor.fetchall())
print(cursor.fetchmany())

#scroll
# cursor.scroll(-1,mode="relative")  #相对当前位置移动
# cursor.scroll(1,mode="absolute")   #相对绝对位置移动

conn.commit()  //执行完sql，首先要提交
cursor.close() //关闭终端
# conn.close() //关闭连接

四、事务

4.1 事务命令

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

数据库开启事务命令

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

转账实例：

mysql> create table account(id int,name varchar(20),balance double);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test account values(1,"朱锐",16000);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'accou
nt values(1,"朱锐",16000)' at line 1
mysql> insert into account values(1,"朱锐",16000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into account values(2,"simon",46000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from account;
+------+--------+---------+
| id   | name   | balance |
+------+--------+---------+
|    1 | 朱锐   |   16000 |
|    2 | simon  |   46000 |
+------+--------+---------+
2 rows in set (0.00 sec)

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

mysql>
mysql>
mysql> update account set balance=balance-5000 where id=1;  //转账
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+------+--------+---------+
| id   | name   | balance |
+------+--------+---------+
|    1 | 朱锐   |   11000 |
|    2 | simon  |   46000 |
+------+--------+---------+
2 rows in set (0.00 sec)

mysql>

rollback回退:

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from account;
+------+--------+---------+
| id   | name   | balance |
+------+--------+---------+
|    1 | 朱锐   |   16000 |
|    2 | simon  |   46000 |
+------+--------+---------+
2 rows in set (0.00 sec)

mysql>

commit提交事务：

mysql> select * from account;
+------+--------+---------+
| id   | name   | balance |
+------+--------+---------+
|    1 | 朱锐   |   11000 |
|    2 | simon  |   46000 |
+------+--------+---------+
2 rows in set (0.00 sec)

mysql> update account set balance=balance+5000 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+------+--------+---------+
| id   | name   | balance |
+------+--------+---------+
|    1 | 朱锐   |   11000 |
|    2 | simon  |   51000 |
+------+--------+---------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>

savepoint:

create table test2(id int PRIMARY KEY auto_increment,name VARCHAR(20)) engine=innodb;
INSERT INTO test2(name) VALUE ("simon"),
                              ("zhurui"),
                              ("caiyunjie");



start transaction;
insert into test2 (name)values('zhuruirui');
select * from test2;
commit;


-- 保留点

start transaction;
insert into test2 (name)values('huozhu');
savepoint insert_wu;
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

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

import pymysql

#添加数据

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

cursor = conn.cursor()


try:
    insertSQL0="INSERT INTO ACCOUNT2 (name,balance) VALUES ('caiyunjie',60000)"
    insertSQL1="UPDATE account2 set balance=balance-12700 WHERE name='simon'"
    insertSQL2="UPDATE account2 set balance=balance+12700 WHERE name='zhurui'"

    cursor = conn.cursor()

    cursor.execute(insertSQL0)
    conn.commit()

    cursor.execute(insertSQL1)
    raise Exception
    cursor.execute(insertSQL2)
    cursor.close()
    conn.commit()

except Exception as e:

    conn.rollback()
    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;