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

  1. 云栖社区>
  2. 博客列表>
  3. 正文

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

巴黎香榭 2018-08-03 22:58:18 浏览750 评论0

摘要: 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
      >>> 
ab2d07a79d975322b7ae414b386475b20a8655ad
示例:


# 创建一张表

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

4eb9d5ecfdcc956a33c2789a85345115ab70a5df
【云栖快讯】你想见的Java技术专家都在这了,向大佬提问,有问题必答  详情请点击

网友评论

巴黎香榭
文章101篇 | 关注92
关注
用于实时预测用户对物品偏好,支持企业定制推荐算法,支持A/B Test效果对比 查看详情
MySQL 是全球最受欢迎的开源数据库,阿里云MySQL版 通过深度的内核优化和独享实例提供... 查看详情
支持 PB 级数据存储的海量分布式关系型数据库。它支持 MySQL 数据库接口,采用可扩展的... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
阿里云总监课正式启航

阿里云总监课正式启航