MySQL数据库学习五——Pycharm中的MySQL事务

绝地灬酷狼 2021-12-09 15:03 318阅读 0赞

#### MySQL事务 ####

> 事务是数据库处理操作，其中执行就好像它是一个单一的一组有序的工作单元。换言之，事务将永远不会是完全的，除非在组内每个单独的操作是成功的。如果事务中的任何操作失败，整个事务将失败。

##### 事务性质 #####

事务具有以下四个标准属性，通常由首字母缩写ACID简称(atomicity,consistent,isolation,durable)：

*  原子性: 确保了工作单位中的所有操作都成功完成; 否则，事务被中止，在失败时会被回滚到事务操作以前的状态。
 *  一致性：可确保数据库在正确的更改状态在一个成功提交事务。
 *  隔离性: 使事务相互独立地操作。
 *  持久性: 确保了提交事务的结果或系统故障情况下仍然存在作用。

在MySQL中，事务以BEGIN WORK语句开始开始工作，并使用COMMIT或ROLLBACK语句结束。SQL命令在开始和结束语句之间构成大量事务。

\#\#\#\#\#提交和回滚

这两个关键字Commit和Rollback主要用于MySQL的事务。

*  当一个成功的事务完成后，COMMIT命令发出的变化对所有涉及的表将生效。
 *  如果发生故障，ROLLBACK命令发出后，事务中引用的每个表将恢复到事务开始之前的状态。

\#\#\#\#\#【拓展】在MySQL的事务安全表类型

不能直接使用事务，可以使用但它们没有安全保障。如果打算使用事务在MySQL编程，那么需要使用一个特殊的方式来创建表。 有许多类型的表其支持事务，但目前最流行的一种是：InnoDB.

支持InnoDB表需要特定的编译参数，在源代码编译MySQL时。如果MySQL版本不支持InnoDB，得要求互联网服务提供商建立一个版本的MySQL的InnoDB表类型的支持，或 下载并安装MySQL-Max二进制分发的Windows版本，或者Linux/UNIX开发环境中工作的表类型。

如果你的MySQL安装支持InnoDB表，把ENGINE= InnoDB的定义添加到表创建语句后面。 链接了解更多有关: [InnoDB][]

例如：

create table tcount_tbl( tutorial_author varchar(40) NOT NULL,tutorial_count  INT) ENGINE=InnoDB;

可以使用其它类型的表 GEMINI 或 BDB, 但它取决于安装MySQL时，是否支持这两种类型。

#### 一、Mysql 与python ####

> Python具有内置的SQLite支持。 在本节中，我们将学习使用MySQL的相关概念和知识。 在早期Python版本一般都使用MySQLdb模块，但这个MySQL的流行接口与*Python 3*不兼容。因此，在教程中将使用[PyMySQL模块][PyMySQL]

##### 1.什么是PyMySQL？ #####

> PyMySQL是从Python连接到MySQL数据库服务器的接口。 它实现了Python数据库API v2.0，并包含一个纯Python的MySQL客户端库

##### 2.安装PyMySQL #####

pip3 install PyMySQL

##### 3.数据库连接 #####

在连接到MySQL数据库之前，请确保以下几点：

*  已经创建了一个数据库：`testdb`。
 *  已经在`testdb`中创建了一个表:`student`。
 *  `student`表格包含:`id`，`name`，`age`，`sex`字段。
 *  MySQL用户“root”和密码“root”可以访问：`testdb`。
 *  Python模块PyMySQL已正确安装在您的计算机上。
 *  已经通过[MySQL教程][MySQL]了解MySQL基础知识。

创建表

CREATE TABLE student (
      id int(10) NOT NULL AUTO_INCREMENT,
      name char(20) NOT NULL,
      age int(11) DEFAULT NULL,
      sex char(1) DEFAULT NULL,
      score float DEFAULT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

##### 4.连接pymsql #####

#!/usr/bin/python3
    #coding=utf-8
    
    #导入包
    import pymysql
    
    # 打开数据库连接
    db = pymysql.connect("localhost","root","root","testdb" )
    
    # 使用cursor()方法获取一个游标
    cursor = db.cursor()
    
    # 执行sql语句中查询版本信息的方法
    cursor.execute("SELECT VERSION()")
    
    # 使用 fetchone() 方法获取一条数据
    data = cursor.fetchone()
    
    #打印版本信息
    print ("Database version : %s " % data)
    
    # 关闭连接
    db.close()

##### 5.创建数据库表 #####

#!/usr/bin/python3
    #coding=utf-8
    
    import pymysql
    
    # 打开数据库连接
    db = pymysql.connect("localhost","root","root","testdb" )
    
    # 准备一个游标
    cursor = db.cursor()
    
    # 若表存在则删除
    cursor.execute("DROP TABLE IF EXISTS student")
    
    # 准备建表的语句
    sql = """CREATE TABLE student ( id int(10) NOT NULL AUTO_INCREMENT, name char(20) NOT NULL, age int(11) DEFAULT NULL, sex char(1) DEFAULT NULL, score float DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;;"""
    #执行sql语句
    cursor.execute(sql)
    print("Created table Successfull.")
    # 断开连接
    db.close()

##### 6.插入操作 #####

当要将记录创建到数据库表中时，需要执行`INSERT`操作。

以下示例执行SQL的insert语句以在student表中创建一条(多条)记录

#!/usr/bin/python3
    #coding=utf-8
    
    import pymysql
    
    db = pymysql.connect("localhost","root","root","testdb" )
    
    cursor = db.cursor()
    
    # 准备sql语句
    sql = """INSERT INTO student(name,
       age, sex, score)
       VALUES ('Mac', 18, 0,90)"""
    try:
       # 执行sql语句
       cursor.execute(sql)
       # 提交事务
       db.commit()
    except:
       # 若出现错误，则回滚
       db.rollback()
    
    db.close()

动态创建SQL查询

#!/usr/bin/python3
    #coding=utf-8
    
    import pymysql
    
    # Open database connection
    db = pymysql.connect("localhost","root","123456","test" )
    
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    
    # Prepare SQL query to INSERT a record into the database.
    sql = "INSERT INTO EMPLOYEE(name, age, sex, scroe) VALUES ('%s', '%d', '%c', '%f')" % ('Max', 25, 0, 89)
    try:
       # 执行sql语句
       cursor.execute(sql)
       # 提交事务
       db.commit()
    except:
       # 回滚
       db.rollback()
    db.close()

##### 7.读取操作 #####

任何数据库上的读操作表示要从数据库中读取获取一些有用的信息。

在建立数据库连接后，就可以对此数据库进行查询了。 可以使用`fetchone()`方法获取单条记录或`fetchall()`方法从数据库表中获取多个值。

*  `fetchone()` \- 它获取查询结果集的下一行。 结果集是当使用游标对象来查询表时返回的对象。
 *  `fetchall()` \- 它获取结果集中的所有行。 如果已经从结果集中提取了一些行，则从结果集中检索剩余的行。
 *  `rowcount` \- 这是一个只读属性，并返回受`execute()`方法影响的行数。

#!/usr/bin/python3
    #coding=utf-8
    
    import pymysql
    
    
    db = pymysql.connect("localhost","root","root","testdb" )
    
    cursor = db.cursor()
    # 按字典返回 
    # cursor = db.cursor(pymysql.cursors.DictCursor)
    
    sql = "SELECT * FROM student WHERE age > %d" % (20)
    #print (sql)
    try:
       cursor.execute(sql)
       #获取查找到的所有行数
       results = cursor.fetchall()
    	#遍历结果集
       for row in results:
          #print (row)
          name = row[1]
          age = row[2]
          sex = row[3]
          score = row[4]
          print ("name = %s,age = %s,sex = %s,score = %f" %(name, age, sex, score))
    except:
       import traceback
       #打印异常
       traceback.print_exc()
    
    db.close()

##### 8.更新操作 #####

PDATE语句可对任何数据库中的数据进行更新操作，它可用于更新数据库中已有的一个或多个记录。

以下程序将所有`SEX`字段的值为“`0`”的记录的年龄(`age`字段)更新为增加一年。

#!/usr/bin/python3
    #coding=utf-8
    
    import pymysql
    
    db = pymysql.connect("localhost","root","root","testdb" )
    
    cursor = db.cursor()
    
    sql = "UPDATE student SET AGE = AGE + 1 WHERE SEX = '%c'" % ('0')
    try:
       cursor.execute(sql)
       db.commit()
    except:
     
       db.rollback()
    db.close()

##### 9.删除操作 #####

当要从数据库中删除一些记录时，那么可以执行`DELETE`操作。 以下是删除student中`AGE`超过`40`的所有记录的程序 。

#!/usr/bin/python3
    #coding=utf-8
    
    import pymysql
    
    db = pymysql.connect("localhost","root","root","testdb" )
    
    cursor = db.cursor()
    
    sql = "DELETE FROM student WHERE AGE > '%d'" % (40)
    try:
       cursor.execute(sql)
       db.commit()
    except:
      
       db.rollback()
    
    db.close()

#### 二、工具类【封装mysql】 ####

import pymysql
    
    class DBHelper():
        def __init__(self,host, user, passwd, dbName):
            self.host = host
            self.user = user
            self.passwd = passwd
            self.dbName = dbName
        def connet(self):
            self.db = pymysql.connect(self.host,self.user,self.passwd,self.dbName)
            self.cursor = self.db.cursor()
            
        def close(self):
            self.cursor.close()
            self.db.close()
    
        def get_one(self, sql):
            res = None
            try:
                self.connet()
                self.cursor.execute(sql)
                res = self.cursor.fetchone()
                self.close()
            except:
                print("查询失败")
            return res
        
        
        def get_all(self, sql):
            res = ()
            try:
                self.connet()
                self.cursor.execute(sql)
                res = self.cursor.fetchall()
                self.close()
            except:
                print("查询失败")
            return res
    
    
    
        def insert(self, sql):
            return self.__edit(sql)
        
        def update(self, sql):
            return self.__edit(sql)
        
        
        def delete(self, sql):
            return self.__edit(sql)
    
        def __edit(self,sql):
            count = 0
            try:
                self.connet()
                count = self.cursor.execute(sql)
                self.db.commit()
                self.close()
            except:
                print("事物提交失败")
                self.db.rollback()
            return count

[InnoDB]: http://www.mysql.com/doc/I/n/InnoDB.html
[PyMySQL]: http://github.com/PyMySQL/PyMySQL/
[MySQL]: http://www.yiibai.com/mysql