数据库进阶
一、pymysql模块
pymysql是Python中操作Mysql的模块，其使用的方法和py2的MySQLdb几乎相同。
二、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()
