python数据库模块_Python之路【第二十五篇】:数据库之pymysql模块

数据库进阶

一、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()