MySQL基本概念、PyMySQL操作及数据库事务操作

数据库	应用	注：关系型数据库核心元素
Oracle	大型项目中使用，例如银行、电信等项目;	·数据行（一条记录） ·数据列（字段） ·数据表（数据行的集合） ·数据库（数据表的集合，一个数据库中能够有n多个数据表
MySQL	web项目中使用最广泛的关系型数据库;
Microsoft SQL Server	微软项目中使用
SQlite	轻量级数据库，主要应用于移动平台

1.2.2.非关系型数据库

不存在数据表的概念，将数据以Key，value、文本、图片等形式存储数据构成；

·Redis

·MongoDB

二、SQL:Structured Query Lanaguage结构化查询语言;

2.1SQL语言分类：

类型	应用	案例
DQL	数据查询语言，用于对数据查询	select
DML	数据操作语音，对数据进行增加、修改、删除	insert、update、delete
DCL	数据控制语音，进行授权与权限收回	grant，revoke
DDL	数据定义语言，进行数据库、表的管理等	create、drop
注意	在MySQL中，默认对SQL语法不区分大小写；SQL语言默认支持操作所有的关系型数据库；

2.2 SQL语句常用数据类型与约束

2.2.1常用数据类型

整数：int 有符号范围；unsigned 无符号范围；

小数：decimal，例如decimal（5,2）表示共存5位小数，小数占2位，整数占3位；

字符串：varchar，范围（0~65533），例如：varchar（3）表示最多存3个字符,一个中文或一个字母都占一个字符。

日期时间：datetime，范围（1000-01-01 00:00:00~9999-12-31 23:59:59），例如：'2020-01-01 12:29:59'

2.2.2 约束

约束	释意
主键（primary key）	能够唯一标志表中每一条记录的属性组
非空（notnull）	此字段不允许填写空值
唯一（unique）	此字段不允许重复
默认值	如果不填写此值会使用默认值，如果填写则以填写为准
外键	一个表中的一个字段引用另一个表的主键

三、python操作数据库的方式

pymysql：纯python开发，支持python2与python3，简单易用；

3.1 pymysql 的安装与验证

安装：pip install PyMsql

验证：pip show PyMySQL

3.2 python操作数据库的流程

3.2.1 pymysql操作流程图

1.创建连接

2.获取游标对象

游标：游标是sql的一种数据访问机制，是结果集的一个指针，其查询的结果都是在上一条结果的基础上产生的，而不是原表格。

3.执行SQL语句

查询操作（select）

非查询操作（insert/delete/update)

·事务提交（连接对象.commit())

·数据回滚(连接对象.rollback())

4.关闭游标cursor

5.关闭连接

3.2.2 代码展示操作流程

案例背景：数据库为books，库里有两张表t_book和t_hero

CREATE DATABASE if not EXISTS books DEFAULT charset utf8;
use books;

Drop TABLE if EXISTS t_book;

CREATE TABLE `t_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(20) NOT NULL COMMENT '图书名称',
  `pub_date` date NOT NULL COMMENT '发布日期',
  `read` int(11) NOT NULL DEFAULT '0' COMMENT '阅读量',
  `comment` int(11) NOT NULL DEFAULT '0' COMMENT '评论量',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='图书表';

INSERT INTO `t_book` VALUES('1','射雕英雄传','1980-05-01','12','34','0');
INSERT INTO `t_book` VALUES('2','天龙八部','1986-07-24','36','40','0');
INSERT INTO `t_book` VALUES('3','笑傲江湖','1995-12-24','20','80','0');


DROP TABLE IF EXISTS `t_hero`;
CREATE TABLE `t_hero`(
	`id` int(11) not null auto_increment,
	`name` VARCHAR(20) not null comment '姓名',
	`gender`SMALLINT(6) not null COMMENT '性别',
	`description`VARCHAR(200) DEFAULT NULL COMMENT '描述',
	`is_delete`TINYINT(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
	`book_id`INT(11) not null comment'所属图书id',
	PRIMARY key(`id`),
	key `t_hero_book_id`(`book_id`) 
)ENGINE = INNODB DEFAULT CHARSET=UTF8 COMMENT='英雄人物表';
INSERT into `t_hero` VALUES('1','郭靖','1','降龙十八掌','0','1');
INSERT into `t_hero` VALUES('2','黄蓉','2','打狗棍法','0','1');
INSERT into `t_hero` VALUES('3','乔峰','1','降龙十八掌','0','2');
INSERT into `t_hero` VALUES('4','令狐冲','1','独孤九剑','0','3');
INSERT into `t_hero` VALUES('5','任盈盈','0','弹琴','0','3');

创建连接操作：

"""
需求：查询当前mysql版本
"""
#导包
import pymysql
# 创建连接
conn=pymysql.connect(host="localhost",
                     port=3306,
                     user="root",
                     password="root",
                     database="books")
# 获取游标
cursor=conn.cursor()
# 执行sql
cursor.execute("select version()")
result=cursor.fetchall()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库select查询操作

"""
1）连接到数据库（host：localhost user：root password：root，database：books
2）查询图书表的数据（包括：图书id，图书名称，阅读量，评论量）
3）获取查询结果的总记录数
4）获取查询结果的第一条数据
5）获取全部的查询结果
"""
# 创建连接
import pymysql

conn = pymysql.Connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books")
# 获取游标
cursor = conn.cursor()
# 执行sql
# 2)查询图书表的数据（包括：图书id，图书名称，阅读量，评论量）
sql = "select id ,title, `read`,`comment`from t_book;"
cursor.execute(sql)
# 3)获取查询结果的总记录数
print("获取的查询结果记录数行数为：", cursor.rowcount)
# 4)获取查询结果的第一条数据
print(cursor.fetchone())
# 5）获取全部的查询结果
# cursor.rownumber = 0
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库的新增操作insert

注意：使用insert into/update/delete等语句时，创建连接时必须有autocommit=true，即自动提交事务。

"""
1）连接到数据库（host：localhost user：root password：root，database：books
2）新增一条图书数据（id：4，title：西游记，pub_date：1986-01-01）
"""
# 创建连接
import pymysql

conn = pymysql.Connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books",
                       autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
# 2）新增一条图书数据（id：4，title：西游记，pub_date：1986-01-01）
sql = "insert into t_book(`id`,`title`,`pub_date`) values(4,'西游记','1986-01-01');"
cursor.execute(sql)
# 3)获取受影响的记录数
print('受影响的记录数为:', cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库的更新update操作

"""
数据库更新操作
1）连接到数据库
2）更新西游记图书名称为东游记（title：东游记）
"""
import pymysql

# 创建连接
conn = pymysql.connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books",
                       autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "update t_book set title = '东游记' where title = '西游记';"
cursor.execute(sql);
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库的删除delete操作

"""
数据库更新操作
1）连接到数据库
2）删除图书名称为东游记（title：东游记）
"""
import pymysql

# 创建连接
conn = pymysql.connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books",
                       autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "delete from t_book where title ='东游记';"
cursor.execute(sql)
# 返回受影响的行数
print(cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

3.2.3 小结：查询与非查询

相同点:基本操作流程一致，创建连接--获取游标--执行sql语句--关闭游标--关闭连接；

不同点：执行的sql语句不同，非查询操作需要开启事务（创建连接时指定参数autocommit）；

四、数据库事务操作

4.1事务的基本概念及特征

4.1.1 事务的基本概念

事务（transaction）：是并发控制的基本单位，是一个序列操作，要么都执行，要么都不执行，是一个不可分割的工作单位。

例：银行转账，从一个账号扣款并使用另一个账号增款，这两个操作要么都执行，要么都不执行。

数据库事务：是指一个逻辑工作单元中执行的一系列操作，要么完全执行，要么完全不执行。

4.1.2 事务的特征（ACID)

原子性（Atomicity）：事务中包含的操作被看做一个逻辑单元，这个逻辑单元要么全部成功，要么全部失败；

一致性（consistency）：事务的保留结果不变，即事务的运行并不改变数据的一致性；

隔离性（isolation）：又称孤立性，事务的中间状态对其他事务是不可见的。

持久性（durability）：指一个事务一旦提交成功，它对数据库中数据的改变应该是永久性的。

事务提交机制：

自动提交

手动提交

4.1.3数据库事务操作方法

conn.autocommit(False):设置是否开启自动提事务，默认不开启

conn.commit():提交数据库事务

conn.rollback():回滚事务

4.2数据库工具封装

4.2.1 封装代码的思路

# 1.创建连接
# 2.创建游标
# 3.执行sql
      try:
          #获取游标对象
          #调用游标对象的execute方法，执行execute.(sql)
            #如果是查询
                #返回所有数据
            #否则：
                #提交事务
                #返回受影响的行数
        except Exception as e:
                #事务回滚
                #抛出异常
        finally:
            #关闭游标
            #关闭连接

# 关闭游标
# 关闭连接

4.2.2 业务方法封装及验证

# 导包
import pymysql


# 创建工具类
class DBUtil():
    # 初始化
    __conn = None
    __cursor = None

    # 创建连接
    @classmethod
    def __get_conn(cls):
        if cls.__conn is None:
            cls.__conn = pymysql.connect(host="localhost",
                                         port=3306,
                                         user="root",
                                         password="root",
                                         database="books")
        return cls.__conn

    # 获取游标
    @classmethod
    def __get_cursor(cls):
        if cls.__cursor is None:
            cls.__cursor = cls.__get_conn().cursor()
        return cls.__cursor

    # 执行sql
    @classmethod
    def exe_sql(cls, sql):
        try:
            # 获取游标对象
            cursor = cls.__get_cursor()
            # 调用游标对象的execute方法，执行sql
            cursor.execute(sql)
            # 如果是查询
            if sql.split()[0].lower() == "select":
                # 返回所有数据
                return cursor.fetchall()
            # 否则：
            else:
                # 提交事务
                cls.__conn.commit()
                # 返回受影响的行数
                return cursor.rowcount
        except Exception as e:
            # 事务回滚
            cls.__conn.rollback()
            # 打印异常信息
            print(e)
        finally:
            # 关闭游标
            cls.__close_cursor()
            # 关闭连接
            cls.__close_conn()

    # 关闭游标
    @classmethod
    def __close_cursor(cls):
        if cls.__cursor:
            cls.__cursor.close()
            cls.__cursor = None

    # 关闭连接
    @classmethod
    def __close_conn(cls):
        if cls.__conn:
            cls.__conn.close()
            cls.__conn = None

结果验证：

from test10_dbutil import DBUtil

# sql = "select * from t_book"
# sql = "insert into t_book(`id`,`title`,`pub_date`) values (4,'西游记','1986-01-01')"
# sql = "update t_book set title = '东游记' where title = '西游记';"
sql = "delete from t_book where title ='东游记';"
result = DBUtil.exe_sql(sql)
print(result)

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

笔记

mysql

数据库

database

python