数据库进阶
一、pymysql模块
pymysql是Python中操作Mysql的模块,其使用的方法和py2的MySQLdb几乎相同。
二、pymysql模块安装
pip install pymysql
三、执行sql语句
#_*_ coding:utf-8 _*_
# Author:Simon
# Datetime:2019/9/27 8:51
# Software:PyCharm
importpymysql
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.03sec)
mysql> insert into test account values(1,"朱锐",16000);
ERROR 1064 (42000): You have an error inyour 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.01sec)
mysql> insert into account values(2,"simon",46000);
Query OK, 1 row affected (0.01sec)
mysql> select * fromaccount;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | 朱锐 | 16000 |
| 2 | simon | 46000 |
+------+--------+---------+
2 rows in set (0.00sec)
mysql>start transaction; //开启事务
Query OK, 0 rows affected (0.00sec)
mysql>mysql>mysql> update account set balance=balance-5000 where id=1; //转账
Query OK, 1 row affected (0.01sec)
Rows matched: 1 Changed: 1Warnings: 0
mysql> select * fromaccount;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | 朱锐 | 11000 |
| 2 | simon | 46000 |
+------+--------+---------+
2 rows in set (0.00sec)
mysql>
rollback回退:
mysql>rollback;
Query OK, 0 rows affected (0.01sec)
mysql> select * fromaccount;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | 朱锐 | 16000 |
| 2 | simon | 46000 |
+------+--------+---------+
2 rows in set (0.00sec)
mysql>
commit提交事务:
mysql> select * fromaccount;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | 朱锐 | 11000 |
| 2 | simon | 46000 |
+------+--------+---------+
2 rows in set (0.00sec)
mysql> update account set balance=balance+5000 where id=2;
Query OK, 1 row affected (0.00sec)
Rows matched: 1 Changed: 1Warnings: 0
mysql> select * fromaccount;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | 朱锐 | 11000 |
| 2 | simon | 51000 |
+------+--------+---------+
2 rows in set (0.00sec)
mysql>commit;
Query OK, 0 rows affected (0.00sec)
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 * fromtest2;
commit;
--保留点
start transaction;
insert into test2 (name)values('huozhu');
savepoint insert_wu;
select * fromtest2;
delete from test2 where id=4;
savepoint delete1;
select * fromtest2;
delete from test2 where id=1;
savepoint delete2;
select * fromtest2;
rollback to delete1;
select * fromtest2;
savepoint
4.2 python中调用数据库启动事务的方式
importpymysql
#添加数据
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)
raiseException
cursor.execute(insertSQL2)
cursor.close()
conn.commit()
exceptException as e:
conn.rollback()
conn.commit()
cursor.close()
conn.close()