MySQL(Python相关) day05

原创 于 2018-08-03 18:14:27 发布 · 562 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#python #MySQL

本文介绍了MySQL的存储引擎，包括InnoDB和MyISAM的特点，探讨了锁的概念和类型，强调了SQL语句的优化方法。接着讲解了事务及其回滚在确保数据一致性中的作用，并提供了Python与MySQL交互的例子，包括pymysql的使用和ORM(Object-Relational Mapping)的基础概念及sqlalchemy的安装验证。

2018.8.3

目录

1，存储引擎(处理表的处理器)

2，锁

3，MySQL调优

4，事务和事务回滚

5，与python交互

6，orm(Object Relation Mapping 对象关系映射 )

day05

1，存储引擎(处理表的处理器)

  1. 基本操作
    1. 查看所有存储引擎
      mysql > show engines;
    2. 查看已有表的存储引擎
      mysql > show create table 表名;
    3. 创建表指定
      create table 表名(...)engine=myisam;
    4. 已有表
      alter table 表名 engine=innodb;

2，锁

  1. 目的: 解决客户端并发访问的冲突问题
    1. 锁分类
      1. 锁类型
        1. 读锁（共享锁）
          select :加读锁之后别人不能更改表记录，但可以进行查询
        2. 写锁（互斥锁，排他锁）
          insert, delete, update
          加写锁之后别人不能查，不能改
    2. 锁粒度
      1. 行级锁：innodb
      2. 表级锁：myisam
    3. 常用存储引擎特点
      1. InnoDB 特点
        1. 共享表空间
          表名.frm:表结构和索引文件信息
          表名.ibd:表记录
        2. 支持行级锁
        3. 支持外键，事务
      2. MyISAM特点
        1. 独享表空间
          表名.frm：表结构
          表名.myd：表记录 mydate
          表名.myi：索引文件myindex
        2. 支持表级所
    4. 如何决定使用哪个存储引擎
      1. 执行查询操作多的表用MySAM（使用InnoDB浪费资源）
      2. 执行写操作的表的表用InnoDB

3，MySQL调优

  1. 选择合适的存储引擎
    1. 读操作多:MyISAM
    2. 写操作多:InnoDB
       
  2. 创建索引
    在select,where,order by 常涉及到的字段建立索引
  3. SQL语句的优化
    1. where 字句中不使用 != ,否则放弃索引全表扫描
    2. 尽量避免NULL值判断，否则放弃索引全表扫描
      优化前：select number from t1 where number is null;
      优化后：在number 列上设置默认值0，确保number列无null值
      select number from t1 where number=0；
    3. 尽量避免 or 连接条件，否则放弃索引全表扫描
      优化前：select id from t1 where id=10 or id=20;
      优化后：
      select id from t1 where id=10
      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 * ...;用具体字段代替 *，不要返回用不到的任何字段

4，事务和事务回滚

  1. 定义: 一件事从开始发生到结束的整个过程
  2. 作用：确保数据一致性
  3. 事务和事务回滚的应用
    1. MySQL中sql命令会自动commit到数据库
      show variables like 'autocommit';
    2. 事务应用
      1. 开启事务
        mysql>begin;
        mysql> ...一条或者多条sql语句 
        #此时autocommit被禁用
      2. 终止事务
        mysql>commit;  | rollback;
    3. 案例
      1. 背景
        你：建行卡
        你朋友：工商卡
        你在建行自动取款机给你朋友的工商卡转账5000元
      2. 建表
        表1，CCB
        create table CCB(
        name varchar(15),
        money decimal(20,2)
        );
        insert into CCB values('只手遮天',10000);

        表2，ICBC
        create table ICBC(
        name varchar(15),
        money decimal(20,2)
        );
        insert into ICBC values('为所欲为',1000);


        mysql> begin; 
        mysql> update CCB set money=money-5000 where name='只手遮天'; 
        mysql> update ICBC set money=money+5000 where name='为所欲为'; 
        mysql> commit; 转账成功 
        #转账成功

        mysql> begin; 
        mysql> update CCB set money=money-5000 where name='只手遮天'; 
        mysql> update ICBC 拖机
        mysql> rollback;
        #失败

5，与python交互

  1. 交互类型
    1. python3
      模块名:pymysql
      安装：
        在线：sudo pip3 install pymysql
        离线：pymysql-0.7.11.tar.gz
          $tar -zxvf pymyql-0.7.11.tar.gz
          $cd pymysql-0.7.11
          $sudo python3 setup.py install
          示例： 
      #此示例离线安装方法
#首选复制文件到用户目录，一般home目录
#SQLAlchemy-1.2.10.tar.gz（这个是文件名字)

#解压
tar -zxvf SQLAlchemy-1.2.10.tar.gz

#到这个位置
cd tar -zxvf SQLAlchemy-1.2.10

#查看
ls

#安装
sudo python3 setup.py install

#验证
python3
>>>import sqlalchemy #如果没有出错，就代表成功

       

    2. python2
      模块名：MySQLdb
      安装：sudo pip install mysql-python
  2. pymysql使用流程
    1. 建立数据库连接(db=pymysql.connect(...))
    2. 创建游标对象(c=db.cursor())
    3. 游标方法:c.execute('insert...')
      c.execute("sql语句")
    4. 提交到数据库:db.commit()
    5. 关闭游标服务:c.close()
    6. 断开数据库连接:db.close()
       
  3. connect对象
    1. db=pymysql.connect(参数列表)
      1. host : 主机地址，本地 localhost
      2. port：mysql端口,默认3306
      3. user：用户名
      4. password：密码
      5. database：库
      6. charset：编码方式，推荐使用utf8
        示例：
        db = pymysql.connect(host='localhost',user='root',
        password='123456',database='db4',
        charset='utf8')
        <li>连接对象(如:db)的方法 
    2. 数据库连接对象(db)的方法
      1. db.close()关闭连接
      2. db.commit()提交到数据库执行
      3. db.rollback()事务回滚操作
      4. db.cursor() 返回游标对象，用于执行具体SQL命令
    3. 游标对象（cur）的方法
      1. cur.execute(SQL命令) 执行SQL命令
      2. 创建游标对象：调用连接对象的cursor()方法 
        示例：cursor1 = db.cursor()
      3. cur.close() 关闭游标对象
      4. fetchone() 获取结果集的第一条记录,返回一个元组
      5. fetchmany(n) 获取结果计的n条记录,返回一个大元组((记录1)，(记录2))
      6. fetchall() 获取结果集的所有记录,返回一个大元组
        错误：
         1、root@"localhost" denied,Using password:YES
         2、"localhostt"
         3、connect object has no attribute "rollbake"
         4、pymysql has no attribute "connect"

        4，5，6示例： 
        import pymysql
#1,创建与数据库连接对象
db = pymysql.connect(host='localhost',user='root',
                     password='123456',database='db4',
                     charset='utf8')
cur = db.cursor()

try:
    sql_select = 'select * from sheng;'
    cur.execute(sql_select)

    
    data1 = cur.fetchone()

    print(data1)
    print('*************')

    data2 = cur.fetchmany(3)
    for n in data2:
        print(n)
    print('*************')

    data3 = cur.fetchall()
    for m in data3:
        print(m)
    print('*************')

    db.commit()
except Exception as e:
    print(e)
cur.close()
db.close()

#1
#130000
#河北省
#*************
#(2, 140000, '陕西省')
#(3, 150000, '四川省')
#(4, 160000, '广东省')
#*************
#(5, 170000, '山东省')
#(6, 180000, '湖北省')
#(7, 190000, '河南省')
#(8, 200000, '海南省')
#(9, 200001, '云南省')
#(10, 200002, '山西省')
#(16, 3000, '台湾省')
#(17, 300001, '云南省')
#*************

#下面是mysql的结果
mysql> select * from sheng;
+----+--------+-----------+
| id | s_id   | s_name    |
+----+--------+-----------+
|  1 | 130000 | 河北省    |
|  2 | 140000 | 陕西省    |
|  3 | 150000 | 四川省    |
|  4 | 160000 | 广东省    |
|  5 | 170000 | 山东省    |
|  6 | 180000 | 湖北省    |
|  7 | 190000 | 河南省    |
|  8 | 200000 | 海南省    |
|  9 | 200001 | 云南省    |
| 10 | 200002 | 山西省    |
| 16 |   3000 | 台湾省    |
| 17 | 300001 | 云南省    |
+----+--------+-----------+
        execute可添加多个示例： 
        import pymysql

# 1.创建数据库连接对象
db = pymysql.connect(host="localhost",user="root",
                password="123456",database="db4",
                charset="utf8")
# 2.创建游标对象
cur = db.cursor()
# 3.执行SQL语句
# 在sheng表中插入1条记录,云南省
try:
    sql_insert = "insert into sheng values\
                  (19,300002,'西藏');"
    cur.execute(sql_insert)
    # 把云南省的 id 号改为 666
    sql_update = "update sheng set id=666 where id=17;"
    cur.execute(sql_update)
    # 把台湾省在 sheng 表中删除
    sql_delete = "delete from sheng where s_name='台湾省';"
    cur.execute(sql_delete)
    print("ok")
    db.commit()
except Exception as e:
    db.rollback()
    print("出现错误,已回滚",e)


# 5.关闭游标对象
cur.close()
# 6.断开数据库连接
db.close()
        sql语句参数化：
          
        '''SQL语句参数化'''

import pymysql

# 1.创建数据库连接对象
db = pymysql.connect(host="localhost",user="root",
                password="123456",database="db4",
                charset="utf8")
# 2.创建游标对象
cur = db.cursor()

s_id = input("请输入省编号:") 
name = input("请输入省名称:")
        
try:
    sql_insert = "insert into sheng(s_id,s_name) \
                  values(%s,%s);"
    cur.execute(sql_insert,[s_id,name])# 列表传参
    print("ok")
    db.commit()
except Exception as e:
    db.rollback()
    print("Failed",e)

cur.close()
db.close()
        用封装求： 
        #封装起来
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,
                          database=self.database,
                          password=self.password,
                          charset=self.charset)
        self.cur = self.db.cursor()

    def close(self):
        self.cur.close()
        self.db.close()

    def zhixing(self,sql,L=[]):    # pymysql.execute(sql)
        try:
            self.open()
            self.cur.execute(sql,L)
            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()
        #然后导入上面的封装
from mysqlpython import Mysqlpython

# 创建数据库连接对象
sqlh = Mysqlpython("db4")

#这个是在mysql查询出来
# sql_update = "update sheng set s_name='辽宁省' \
#               where s_name='云南省';"
# sqlh.zhixing(sql_update)

#这个可以直接打印出来
sql_select = "select * from sheng where id=%s;"
data = sqlh.all(sql_select,[1])
print(data)
        用mysql和sha1,设置密码 
        #需要导入上面的模块
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("密码错误")




         


 

6，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))  

#解释：
#一个类Usur --> 一张表t1
#表中有两个字段:id 和 name

    创建一张表：
      
    # 创建一张表

# 连接数据库的模块
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)

     

 

 

 

 

 

 

 

 

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值