MySQL 事物 读取 更新 封装

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模块

1.什么是PyMySQL？

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

2.安装PyMySQL

pip3 install PyMySQL

pip install pymysql -i https://pypi.douban.com/simple

3.数据库连接

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

• 已经创建了一个数据库：testdb。
• 已经在testdb中创建了一个表:student。
• student表格包含:id，name，age，sex字段。
• MySQL用户“root”和密码“root”可以访问：testdb。
• Python模块PyMySQL已正确安装在您的计算机上。
• 已经通过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.更新操作

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

以下程序将所有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