[python全栈]04.MySQL(5)

最新推荐文章于 2024-07-12 16:16:27 发布

慎三。

最新推荐文章于 2024-07-12 16:16:27 发布

阅读量212

点赞数

分类专栏： [python全栈]4.MySQL 文章标签： SQL python

本文链接：https://blog.csdn.net/weixin_45326041/article/details/99693788

版权

[python全栈]4.MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

储存引擎和锁
MySQL调优
事务和事务回滚
与Python交互
ORM

1. 储存引擎和锁

查看所有的存储引擎 :
	show engines;
		默认引擎
		InnoDB > DEFAULT > Supports transactions, 
							row-level locking, and foreign keys
		常用
		InnoDB
		MyISAM
查看已有表的存储引擎 :
	show create table 表名;
		创建表时指定存储引擎
		create table 表名(......) ENGINE=InnoDB;

锁 :
	解决客户端并发访问的冲突问题(几条语句几乎同时执行)
锁的分类 :
	1.按锁的类型分:
		读锁(共享锁)
			select
				加读锁之后别人不能更改表记录,但可以进行查询
		写锁(互斥锁、排他锁)
			insert ； delete ; update
				加写锁之后别人不能查、不能改
	2.锁粒度:
		表级锁
			myisam
		行级锁
			innodb

常用存储引擎特点:
	InnoDB特点:
		共享表空间
			表名.frm: 表结构和索引文件
			表名.idb: 表记录
		支持行级锁
		支持外键、事务操作
	MyISAM特点
		独享表空间
			表名.frm: 表结构
			表名.myd: 表记录(data)
			表名.myi: 表索引文件(index)
		支持表级锁
如何选择存储引擎:
	执行查询操作多的表使用 MyISAM
		(支持表级锁,锁表速度快,读锁不影响其他用户读表操作)
	执行写操作多的表使用 InnoDB

2. MySQL调优

一、MySQL常用调优方式 :
	选择合适的存储引擎
		读操作多>MyISAM ; 写操作多>InnoDB
二、创建索引
		在select、where、order by 常用字段上加索引
三、基本SQL语句的优化(尽量避免全表扫描)
		1.where子句中不使用 != ,否则放弃索引全表扫描
		2.尽量避免 NULL 值判断 ,否则放弃索引全表扫描
			优化前:select number from t1 where number is null;
			优化后:number列上设置默认值 default = 0;确保该列无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 = 0
					union all 	#将多条语句查询结果放在一起
					select id from t1 where id = 20;
		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.尽量避免使用 select * ... ; 用具体字段代替 * 
			不要返回任何无用字段

3. 事务和事务回滚

事务 : 一件事从开始发生到结束的整个过程
作用 : 确保数据的一致性
事务和事务回滚应用 : 
	1.MySQL中SQL命令会自动执行到数据库中:
		show variables like "autocommit";
	2.事务应用:
		开启事务 :
		mysql > begin;
		mysql > ... 一条或多条SQL语句
			#此时autocommit被自动禁用
		终止事务
		mysql > commit;(执行成功) | rollback; (未全部执行成功,事务不会执行)
eg:
	1.背景
			你:建行卡
			朋友:工行卡
			你在建行自动取款机给你朋友的工行卡转账5000元
	2.建表
		create database bank;
		use bank;
		建行
		create table CCB(name varchar(15),money decimal(20,2));
		insert into CCB values("只手遮天",10000);
		工行
		create table ICBC(name varchar(15),money decimal(20,2));
		insert into ICBC values("为所欲为",1000);	
	3.转账操作
		begin;
		update CCB set money=money-5000 where name = "只手遮天";
		update ICBC set 宕机......;
		rollback;
		#此时你的钱不会减少,你朋友钱也不会增加
		begin;
		update CCB set money=money-5000 where name = "只手遮天";
		update ICBC set money=money+5000 where name = "为所欲为";
		commit;
		#此时转账成功

4. 与Python交互

使用模块: pymysql
				SQLALchemy
安装方式(Linux) : 
	1. 在线安装 sudo pip3 install pymysql
	2. 离线安装 : pymysql-0.7.11.tar.gz
		$tar -zxvf pymysql-0.7.11.tar.gz
		$cd pymysql-0.7.11
		$sudo python3 setup.py install

pymysql使用流程 :
#0.导入模块
import pymysql
#1.创建与数据库连接对象
db = pymysql.connect(host="localhost",user="root",password="123456",
                     database="db4",charset="utf8")                  
#2.利用db方法创建游标对象
cur = db.cursor()
#3.利用游标对象的execute()方法执行SQL命令
try:
	cur.execute("SQL语句")
#4.提交到数据库执行,若出错事务回滚
	db.commit()
except Exception as e :
	db.rollback()
#5.关闭游标对象
cur.close()
#6.断开数据库连接
db.close()

connect对象
	1.db = pymysql.connect(参数列表)
		参数说明:
			host: 主机地址,本地 localhost
			port: 端口号 default = 3306
			user: 用户名
			password: 用户密码
			database:可以直接指定要连接的库
			charset: 编码方式,一般使用utf8
	 2.数据库连接对象(db)的方法
      	1)db.close() 关闭连接
      	2)db.commit() 提交到数据库执行
      	3)db.rollback() 回滚
      	4)cur = db.cursor() 返回游标对象,用于执行具体SQL命令
    3.游标对象(cur)的方法
      	1)cur.execute(sql命令,[列表]) 执行SQL命令
      	2)cur.close() 关闭游标对象
     	3)cur.fetchone() 获取查询结果集的第一条数据
                        返回元组;(1,100001,"河北省")
     	4)cur.fetchmany(n) 获取n条
                        返回元组的元组;((记录1),(记录2))
     	5)cur.fetchall() 获取所有记录
     					同上

练习:写一个函数用于往表内插入数据

import pymysql

def add_data():
    s_id = input('请输入省份编号:\n')
    s_name=input('请输入省份名称:\n')
    sql = '''insert into sheng values(%s,%s);''' 
	#创建数据库连接对象
    db = pymysql.connect(host="localhost",user="root",password="123456",
                     database="db4",charset="utf8")
    #创建数据库游标对象
    cur = db.cursor()
    #执行SQL语句
    try:
        cur.execute(sql,[s_id,s_name]) #execure的列表传参方法(推荐使用,安全)
        db.commit()
        print('execute succeed.')
    except Exception as e:
        db.rollback()
        print('execute failed,has rollbaked','\n',e)
    #断开数据库连接
    cur.close()
    db.close()

if __name__ == "__main__" :
    add_data()

练习:将以上步骤封装成模块

from pymysql import *

class Mysqlpython :
    def __init__(self,database,
                 host = 'localhost',
                 user = 'root',
                 password = '123456',
                 port = 3306,
                 charset = 'utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.port = port
        self.charset = charset
        self.database = database
        
    def open(self):
        self.db = connect(host = self.host,
                        user = self.user,
                        port = self.port,
                        charset = self.charset,
                        password = self.password,
                        database = self.database)
        self.cur = self.db.cursor()
        
    def close(self):
        self.cur.close()
        self.db.close()
    
    def zhixing(self,sql,L=[]):
        try:
            self.open()
            self.cur.execute(sql,L)
            self.db.commit()
            print('execute success')
        except Exception as e :
            self.db.rollback()
            print('Error:',e)
        self.close()

    def all(self,sql,L=[]):
        try:
            self.open()
            self.cur.execute(sql,L)
            result = self.cur.fetchall()
            self.db.commit()
            print('execute success')
            return result
        except Exception as e :
            self.db.rollback()
            print('Error:',e)
        self.close()        

# test
sqlh = Mysqlpython('db4')
sqlh.zhixing('''create table test(id int); ''')
sql_c = '''select * from sheng;'''
data = sqlh.all(sql_c)
for i in data :
    print(i)

#建立一个用户登录界面，从数据库中得到用户名密码信息

#在数据库中建立用户密码表
use db4;
create table user(
username varchar(20),
password char(40));
insert into user values("SHE","7c4a8d09ca3762af61e59520943dc26494f8941b");
#使用sha1加密 明文123456
-------------------------------------
#Python
from mysqlpython import Mysqlpython
#导入上练习模块
from hashlib import sha1

uname = input("请输入用户名 :\n")
pwd = input("请输入密码 :\n")

#使用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])
if len(result) == 0:
    print('用户名不存在!')
elif result[0][0] == pwd2 :
    print('登录成功')
else:
    print('密码错误')

5. ORM

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
      >>> 
  3)示例
    class User(Base):
        __tablename__ = "t1"  #声明要创建的表名
		id = Column(Integer,primary_key=True)
		name = Column(String(20))
    解释：
      一个类User --> 一张表 t1
      表中有两个字段 ：id 和 name

eg:#使用ORM方法创建一张表
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"  #创建表123
    id = Column(Integer,primary_key=True)
    name = Column(String(20))
    address = Column(String(40))

Base.metadata.create_all(engine)