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
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.更新操作
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