Python全栈 MySQL 数据库 (引擎、事物、pymysql模块、orm)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL 数据库的引擎、事物、锁、调优、与Python的交互、ER、orm

ParisGabriel
 
 
         每天坚持手写  一天一篇  决定坚持几年 为了梦想为了信仰
 
   开局一张图


存储引擎(处理表的处理器)
    基本操作:
         查看所有存储引擎
           show engines
         查看已有 表的存储 引擎
   show create table 表名;
创建表指定引擎:
   create table 表名()engine=myisam;
已有表 添加引擎:
   alter table 表名 engine=innodb;
MySQL锁:(自动加锁)
     目的:
         解决客户端并发访问的冲突问题
    锁分类:
       类型:
           1. 读锁共享锁
             select :加读锁后别人 不能更改表记录,但 可以查询
   2. 写锁互次锁、排他锁
                    insert、delete、update :加写锁后别人 不能查也不能改
    锁粒度:
         1.表级锁myisam
1.行级锁innodb

常用的存储引擎特点
   InnoDB特点:
     1.共享表空间
       表名 .frm:表结构和索引文件
       表名 .idb:表记录、
       支持 级锁
       支持 外键 事物 操作
   Myisam特点:
        独享表空间
         表名 .frm:表结构
 表名 .myd:表记录mydata
 表名 .myi:索引文件 myindex
       支持 级锁
如何决定使用哪个存储引擎?
      执行 查询操作 的表 用MyISAM(使用 InoDB浪费资源
      执行 操作 的表 用InnoDB
MySQL调优:
     1.选择合适的存储引擎
         读操作多:MyISAM
写操作多:InnoDB
      2.创建索引
        在select、where、order by 经常涉及到的字段建立索引
      3.SQL语句优化
        1). where子句 中尽量不使用  != 否则放弃索引 全表扫描
2).尽量避免  NULL 值判断 否则放弃索引 全表扫描
   优化前:
      select number from t1 where number isnull;
           优化后:
      在number列上 设置默认值0确保number列 无NULL值
      select number from t1 where number=0;
3).尽量避免  or 连接条件,否则放弃索引 全表扫描
  优化前:
      select id from t1 where id=0 or id=20;
   优化后:
      select id from t1 where id=10
      union all
      select id from t1 where id=20
      union all
      select id from t1 where id=30;
       union all:
          将查询结果连接起来
        4).模糊查询尽量避免使用 前置%,否者 全表扫描
      select name from t1 where name like “%c%”;
5).尽量避免使用   in 和 not in 否则 全表扫描
   优化前:
      select id from t1 where id in(1,2,3,4);
  优化后:
      select id from t1 where id between 1 and 4;
6).尽量避免使用 *  不要返回任何用不到的字段

事物和事物回滚
     定义:一件事从 开始发生到结束的整个 过程
     作用: 确保数据的一致性
     事物和事物回滚应用
          MySQL中SQL命令 会自动commit到数据库
  show variables likeautocommit
     事物应用:
         开始事物
    begin;此时autocommit被禁用
        提交事物:
           commit;
终止事物
    rollback;

与python交互:
   交互类型
   python3: pymysql 模块
   安装:
     在线:sudo pip3 install pymysql
     离线:pymysql-0.7.11.tar.gz
           tar -zxvf pymysql-0.7.11.tar.gz
   cd pymysql-0.7.11
   sudo python3 setup.py install
    python2: 模块:MySQLdb
       安装:sudo pip install mysql-python

pymysql使用流程:
    1.建立 数据库链接:(db =  pymysql.connect(....))
    2. 创建游标对象:(c = db. cursor())
    3. 游标方法:(c. execute(“insert .....”))
    4. 提交到数据库:db. commit()
    5. 关闭游标对象:c. close()
    6. 断开数据库链接:db.close()
      connect对象:
         db = pymysql.connect( 参数列表
1. host:主机地址,本地 localhost
2. port:端口,默认3306,8080(可省略)
3. user :用户名
4. password:密码
5. database:库
6. charset:编码方式,推荐使用utf8
数据库 链接对象db的方法:
    1. db.close() 关闭链接
    2. db.commit()提交到数据库
    3. db.rollback()回滚
    4. cur = db.cursor()返回游标对象,用于 执行具体 SQL命令
       游标对象cur的方法
          1. cur. execute(SQL命令,[列表]) 执行SQL命令
  2.cur. close() 关闭游标对象
          3.cur. fetchone() 获取查询结果的 第一条数据
  4.cur. fetchmany(n) 获取n条记录
  5.cur. fetchall() 获取所有记录
示例:

增、删、改、查、参数化

# frist.py
import pymysql

# 创建数据库对象
db = pymysql.connect(host="localhost", user="root",
                     password="123456", database="db4",
                     charset="utf8")
# 利用db方法创建游标对象
cur = db.cursor()

# 利用游标对象的execute()方法执行SQL命令
cur.execute("insert into sheng values\
             (16,300000,'台湾省');")
# 提交到数据库
db.commit()
# 关闭游标对象
cur.close()
# 断开数据库链接
db.close()


# 增 删 改

import pymysql
# 创建数据库链接
# 链接到db4库
db = pymysql.connect(host="localhost", user="root",
                     password="123456", database="db4",
                     charset="utf8")

# 创建游标
cur = db.cursor()
try:
    # 添加记录
    cur.execute("insert into sheng values (17,168800,'浙江');")
    # 修改记录
    cur.execute("update sheng set id=666 where id=17;")
    # 删除记录
    cur.execute("delete from sheng where s_name='浙江';")
# 截获EXception类型错误
except Exception as e:
    # 出现异常后回滚
    db.rollback()
    # 输出错误
    print("Error ", e)
else:
    # 提交数据
    db.commit()


# 关闭游标
cur.close()
# 断开数据库链接
db.close()


# 查询

import pymysql
# 创建数据库链接
db = pymysql.connect(host="localhost", user="root",
                     password="123456", database="db4",
                     charset="utf8")
# 创建游标
cur = db.cursor()
try:
    # 查找
    cur.execute("select * from sheng;")
    # 取出一条记录就少一条
    print("***************************")
    data1 = cur.fetchone()
    print(data1)
    print("***************************")
    data2 = cur.fetchmany(3)
    for i in data2:
        print(i)
    print("***************************")
    # 遍历取出数据
    data3 = cur.fetchall()
    for x in data3:
        print(x)
    # 提交数据
    db.commit()

except Exception as e:
    db.rollback()
    print("Error ", e)

# 关闭游标
cur.close()
# 断开数据库链接
db.close()


# 参数化

import pymysql
# 创建数据库链接
db = pymysql.connect(host="localhost", user="root",
                     password="123456", database="db4",
                     charset="utf8")
# 创建游标
cur = db.cursor()
try:
    s_id = input("请输入省的编号")
    s_name = input("请输入省的名字")
    # 用占位符参数化数据
    sql_insert = "insert into sheng(s_id,s_name) values(%s,%s)"
    # execute方法 传参必须是列表
    cur.execute(sql_insert, [s_id, s_name])

    # 提交数据
    db.commit()

except Exception as e:
    db.rollback()
    print("Error ", e)

# 关闭游标
cur.close()
# 断开数据库链接
db.close()



封装类

# mysqlpython.py

# 导入mysql模块
from pymysql import *


class MysqlPython:
    def __init__(self, database,  # 库
                 host="127.0.0.1",  # ip地址
                 user="root",  # 用户名
                 password="123456",  # 密码
                 port=3306,  # 端口
                 charset="utf8"):  # 字符集
        self.host = host
        self.database = database
        self.user = user
        self.password = password
        self.port = port
        self.charset = charset

    def open(self):  # 创建数据库链接函数
        self.db = connect(host=self.host,
                          database=self.database,
                          user=self.user,
                          password=self.password,
                          port=self.port,
                          charset=self.charset)
        self.cur = self.db.cursor()  # 创建游标对象

    def close(self):  # 创建断开数据库链接 关闭游标函数
        self.cur.close()
        self.db.close()

    def zhixing(self, sql, L=[]):  # 创建pymysql.execute() 方法函数
        try:
            self.open()  # 链接数据库
            self.cur.execute(sql, L)  # 参数化执行SQL命令
            self.db.commit()  # 提交数据
            print("ok")
        except Exception as e:
            self.db.rollback()  # 出错取消提交
            print("Failed", e)
        self.close()  # 断开数据库链接 关闭游标

    def all(self, sql, L=[]):
        try:
            self.open()
            self.cur.execute(sql, L)
            result = self.cur.fetchall()
            return result
        except Exception as e:
            print("Failed", e)
        self.close()

调用模块.类

  # frist.py

from mysqlpython import MysqlPython
# 创建数据库链接
sqlh = MysqlPython("db4")
# 创建数据库对象
sql_update = "update sheng set s_name='辽宁省'\
              where s_name='云南省';"

# 调用xiugai函数 执行SQL命令:sql_update
sqlh.zhixing(sql_update)

sql_select = "select * from sheng where id=%s;"

# 调用all函数 执行SQL命令:sql_select
date = sqlh.all(sql_select, [1])
print(date)

用户登录系统示例:


from mysqlpython import Mysqlpython
from hashlib import sha1

uname = input("请输入用户名:")
pwd = input("请输入密码:")
# 用sha1给pwd加密

s1 = sha1()  # 创建sha1加密对象
s1.update(pwd.encode("utf8"))  # 指定编码
pwd2 = s1.hexdigest()  # 返回16进制加密结果

sqlh = Mysqlpython("db4")
select = "select password from user where \
          username=%s;"
result = sqlh.all(select, [uname])
# print(result)
# (('7c4a8d09ca3762af61e59520943dc26494f8941b',),)

if len(result) == 0:
    print("用户名不存在")
elif result[0][0] == pwd2:
    print("登录成功")
else:
    print("密码错误")




orm(Object Relation Mapping) 对象关系映射
    1.定义
       把对象模型映射到MySQL数据库中
 2、sqlalchemy安装:
    在线 :sudo pip3 install sqlalchemy
    离线 :
      $ tar -zxvf SQLAlchemy-1.2.10.tar.gz
      $ cd SQLAlchemy-1.2.10
      $ sudo python3 setup.py install
    验证:
      $ python3
      >>> import sqlalchemy
      >>> 

示例:


# 创建一张表

# 连接数据库的模块
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

engine = create_engine("mysql+pymysql://root:123456@localhost/db4", encoding="utf8")
Base = declarative_base()  # orm基类

class User(Base):  # 继承Base基类
    __tablename__ = "t123"
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    address = Column(String(40))

Base.metadata.create_all(engine)



ER模型:
    定义: 关系模型 用于数据库设计
三个概念
  1.实体:矩形框
  2.属性:椭圆形
  3.椭圆形:实体之间的关系
       1).一对一关系(1:1)
       2).一对多关系(1:n)
       3).多对多关系(m,n)


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
19天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
6天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
73 42
|
26天前
|
关系型数据库 API 数据库
Python流行orm框架对比
Python中有多个流行的ORM框架,如SQLAlchemy、Django ORM、Peewee、Tortoise ORM、Pony ORM、SQLModel和GINO。每个框架各有特点,适用于不同的项目需求。SQLAlchemy功能强大且灵活,适合复杂项目;Django ORM与Django框架无缝集成,易用性强;Peewee轻量级且简单,适合小型项目;Tortoise ORM专为异步框架设计;Pony ORM查询语法直观;SQLModel结合Pydantic,适合FastAPI;GINO则适合异步环境开发。初学者推荐使用Django ORM或Peewee,因其易学易用。
|
24天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
173 0
|
2月前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
71 3
|
2月前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
109 3
|
2月前
|
SQL 关系型数据库 MySQL
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
109 2
|
2月前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
356 15
|
2月前
|
SQL 关系型数据库 MySQL
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。
|
2月前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。