python-数据库学习足迹

import /myblog/mysql server

数据库

数据库(database,DB) 是指长期存储在计算机内的，有组织，可共享的数据的集合.数据库中的数据
按一定的数据模型组织丶描述和存储. 具有较小的冗余，较高的数据独立性和易扩展性， 并可为各种用户共享.
.
存储数据的仓库

python 关于 mysql 的 API – pymysql模块

模块安装
pip install pymysql
.
.
执行sql语句
import pymysql

.
.
.
在pycharm上安装pymysql模块
File->settings->Project:extends->Project Interpreter->双击pip->搜索pymysql->Install Package

---

python操作数据库

1.创建一个.py文件

import pymysql

# 执行sql语句

# 以下是我自定义安装mysql的信息 db是我自己创建的数据库database
'''
    在导入pymysql模块时候容易报错，原因是创建的.py文件名不能是
    pymysql 
'''
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root",
                        password ="123456", db='lesson54')


# 系统通过游标调用数据库
cursor = conn.cursor()

# 内容为sql语句
sql = 'CREATE TABLE TEST(id INT, name VARCHAR(32))'
cursor.execute(sql)

2.创建一个SQL文件
右击项目project->new->file->rename->选择SQL

CREATE TABLE TEST(id INT, name VARCHAR(32))

此时为数据库创建了一张表,以上链接数据库语句db=”lesson54”即表示use lesson54进入数据库lesson54

此时数据库表test创建成功

pymysql操作原生SQL语句

pymysql_lesson.py

import pymysql

# 执行sql语句

# 以下是我自定义安装mysql的信息 db是我自己创建的数据库database
'''
    在导入pymysql模块时候容易报错，原因是创建的.py文件名不能是
    pymysql 
'''
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root",
                        password ="123456", db='lesson54')


# 系统通过游标调用数据库
cursor = conn.cursor()

# 将数据以字典的形式赋值
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 内容为sql语句
# sql = 'CREATE TABLE TEST(id INT, name VARCHAR(32))'
# cursor.execute(sql)
cursor.execute("INSERT INTO test VALUES(1,'alex'),(2,'kris')")

cursor.execute('SELECT * FROM TEST')

# 取出一条数据
cursor.fetchone()

# 取出所有数据
cursor.fetchall()

# 取出三条数据
cursor.fetchmany(3)

# 根据当前位置对光标进行移动,1:向下移动一个位置,-1向上移动一个位置
cursor.scroll(1, mode='relative')

# 根据绝对位置对光标进行移动
cursor.scroll(1, mode='absolute')


# 提交数据
conn.commit()
conn.close()

LESSON54(file)

CREATE TABLE TEST(id INT, name VARCHAR(32))

---

事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功
数据库开启事务命令

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

事务实现流程

mysql> select * from accout;
+------+-------+---------+
| id   | name  | balance |
+------+-------+---------+
| 1001 | kris  |    8000 |
| 1002 | chung |    8000 |
+------+-------+---------+
2 rows in set (0.00 sec)

------------------------------------------------->开启事务

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

mysql>
mysql>
mysql> update accout set balance=balance-3000 where id=1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from accout;
+------+-------+---------+
| id   | name  | balance |
+------+-------+---------+
| 1001 | kris  |    5000 |
| 1002 | chung |    8000 |
+------+-------+---------+
2 rows in set (0.00 sec)


--------------------------------------------->回滚事务,撤销操作
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from accout;
+------+-------+---------+
| id   | name  | balance |
+------+-------+---------+
| 1001 | kris  |    8000 |
| 1002 | chung |    8000 |
+------+-------+---------+
2 rows in set (0.00 sec)

mysql> update accout set balance=balance-3000 where id=1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from accout;
+------+-------+---------+
| id   | name  | balance |
+------+-------+---------+
| 1001 | kris  |    5000 |
| 1002 | chung |    8000 |
+------+-------+---------+
2 rows in set (0.00 sec)

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

mysql> select * from accout;
+------+-------+---------+
| id   | name  | balance |
+------+-------+---------+
| 1001 | kris  |    5000 |
| 1002 | chung |   11000 |
+------+-------+---------+
2 rows in set (0.00 sec)

------------------------------------------------->提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from accout;
+------+-------+---------+
| id   | name  | balance |
+------+-------+---------+
| 1001 | kris  |    5000 |
| 1002 | chung |   11000 |
+------+-------+---------+
2 rows in set (0.00 sec)

mysql>

pymysql实现事务

import pymysql

conn = pymysql.connect(host="127.0.0.1", port=3306, user='root',
                        password="123456", db='accout')

cursor = conn.cursor()



try:
    insertSQL0 = 'INSERT INTO ACCOUT2(name,balance) VALUE("krischung" ,8000)'
    insertSQL1 = "UPDATE accout2 set balance=balace-2000 WHERE name='oldboy'"
    insertSQL2 = "UPDATE accout2 set balance=balance+2000 WHERE name='yueqi'"

    cursor = conn.cursor()

    cursor.execute(insertSQL0)
    conn.commit()

    cursor.execute(insertSQL1)
    # 如果此步骤出现错误则执行except语句
    raise Exception
    cursor.execute(insertSQL2)
    cursor.close()
    conn.commit()

except Exception as e:

    # 如果insertSQL2执行发生错误则回滚
    conn.rollback()
    conn.commit()

事务-savepoint

rollback回滚撤销的 都撤销到开启事务的位置，这是弊端
自定义回滚到想要撤销到的位置
savepoint 保留点,事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)

保留点savepoint的应用

mysql> create table test2(id int PRIMARY KEY auto_increment,name VARCHAR(32)) engine=innodb
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> insert test2(name) values('alvin'),('kris'),('xialv');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  3 | xialv |
+----+-------+
3 rows in set (0.00 sec)

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

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  3 | xialv |
+----+-------+
3 rows in set (0.00 sec)

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

mysql> delete from test2;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from test2;
Empty set (0.00 sec)

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

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  3 | xialv |
+----+-------+
3 rows in set (0.00 sec)

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

mysql> insert test2(name) value('chs');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  3 | xialv |
|  4 | chs   |
+----+-------+
4 rows in set (0.00 sec)

----------------------------------------------->提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  3 | xialv |
|  4 | chs   |
+----+-------+
4 rows in set (0.00 sec)

mysql>
mysql>

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

mysql> insert test2(name) value('chs2');
Query OK, 1 row affected (0.00 sec)


------------------------------------------->设定一个保留点，可供回滚
mysql> savepoint insert1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert test2(name) value ('chs3');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  3 | xialv |
|  4 | chs   |
|  5 | chs2  |
|  6 | chs3  |
+----+-------+
6 rows in set (0.00 sec)

mysql> delete from test2 where id=3;
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  4 | chs   |
|  5 | chs2  |
|  6 | chs3  |
+----+-------+
5 rows in set (0.00 sec)

mysql> delete from test2 where id=2;
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  4 | chs   |
|  5 | chs2  |
|  6 | chs3  |
+----+-------+
4 rows in set (0.00 sec)


--------------------------------------->回滚到delete1保留点,此保留点以下的所有操作都被撤销
mysql> rollback to delete1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  4 | chs   |
|  5 | chs2  |
|  6 | chs3  |
+----+-------+
5 rows in set (0.00 sec)

mysql> rollback to insert2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  3 | xialv |
|  4 | chs   |
|  5 | chs2  |
|  6 | chs3  |
+----+-------+
6 rows in set (0.00 sec)

mysql> rollback to insert1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  3 | xialv |
|  4 | chs   |
|  5 | chs2  |
+----+-------+
5 rows in set (0.00 sec)

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

mysql> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | alvin |
|  2 | kris  |
|  3 | xialv |
|  4 | chs   |
+----+-------+
4 rows in set (0.00 sec)

mysql>