PYTHON1.day21

Flush PRIVILEGES;   -- 刷新权限并生效
6)查看授权
   - 查看当前用户: show grants;
   -查看其他用户:  show grants for '用户名'@'客户端';
7)吊销权限(取消用户的某个权限)
   - 语法: revoke 权限列表 on 库名.表名 from '用户名'@'客户端地址'
   - 示例: 吊销Daniel用户所有库,所有列表的插入数据权限
           revoke insert on *.* from 'bank_user'@'%';
           revoke insert on bank.* from 'bank_user'@'%';
4. 数据库事务(重点)
   - 事务(Transacyion):
   - 数据库执行一系列操作时，全都执行，全都不执行
     保证数据的一致性、正确性
   - 例如在一笔转账中,执行需要三个操作,
         从转出账户减去相应金额
         在转入账户增加相应金额
         登记一笔转账明细
         以上三个操作,要么全部成功,要么全部失败
   3)使用事务的情况
     - 涉及到多表的增删改操作
     - 执行这些操作时需要保证一致性,正确性
   4)启用事务条件要求:必须是Innodb存储引擎
   5)事务的额特征(ACID)：
      - 原子性(Atomicity)：事务是一个整体,要么全部执行,要么全部不执行
      - 一致性(Consis.
      tency):事务执行完成后,从一个一致状态变成另一个一致状态
      - 隔离性(Isolation)：不同的事务不会相互影响,干扰
      - 持久性(Durability):一旦事务提交,对数据库的修改就必须永久保留下来
   6)如何进行事务操作
    - 开启: start transaction
     - 提交: commit
     - 回滚:rollback
     - 示例：在两个账户间进行转账
             第一步:减去转出账户余额
             第二步:在转入账号上加上相等的余额
             提交事务
             start transaction;
             update acct_new set balance = balance - 100
                    where acct_no = '6223450001';
             update acct_new set balance = balance + 100
                    where acct_no ='622456000020';
             commit;---或rollback
             ----在提交事务前,重新登录一个客户端
             ----查看数据是否变更
    
     7)SQL语句分类
      - 数据查询语言(DQL):查询数据,不改变数据
      - 数据定义语言(DDL):定义数据结构,如建表/库,删除库/表,创建/删除索引,修改表结构
      - 数据操作语言(DML):对数据进行增删改
      - 数据控制语言(DCL):权限管理,事务操作,数据库监视...

   
-------------------------------------------------day25.new---------------------------------------------------
1.锁
   1）锁：对数据库的控制权
   2）目的：解决多个工作单元并发操作数据的正确性
   3）分类：
     a)锁类型
       - 读锁（共享锁）：加锁后，可以读取，但不能修改
       - 写锁（排它锁）：加锁后，不能进行读写

    b)锁粒度
       - 行级锁（细粒度）：锁定一行，并发效率高
         控制较为复杂，消耗资源多
       - 表级锁（粗粒度）：锁定整张表，并发效率低
         控制简单，消耗资源较少

2.存储引擎
   1)什么是存储引擎:表的存储方式,包括存储机制,索引机制,锁定方式等
   2)基本操作:
     - 查看:show engines;
     - 查看某个表的存储引擎方式:show create table 表名

     -建表时指定存储引擎
      create table t1(id int primary key)engine=InnoDB default charset=utf8;
                                         engine 指定存储
     - 修改存储引擎方式
       alter table t1 engine = MyISAM;
   3)常见存储引擎
     a)InooDB
      特点:支持事务,行级锁,外键
      共享表空间
     *.frm    表结构和索引
      *.ibd    表记录
      show global variables like '%datadir'命令查看数据存储位置    
      sudo -i切换到root用户,进入目录查看
     - 使用场合
       更新操作密集的表
       有数据库事务支持的要求
       自动灾备,恢复
       有外键约束要求
       支持自增长字段(auto_increment)

     b)MyISAM
       - 支持表级锁定、不支持事务、不支持外键
         不支持行锁定，访问速度较快
       - 独享表空间
           表名.frm: 表结构
           表名.myd: 表数据
           表名.myi: 表索引
       - 适用场合
           查询操作较多
           数据一致性要求较低（不支持事务）
           没有外键约束

    c)Memory(内存表):
         - 表结构存储在磁盘上(硬盘)，数据存储在内存
           访问速度很快
         - 文件：表名.frm   存储表结构
         - 服务（或机器）重启后，表中的数据丢失
         - 适用场合：
               数据量小、访问速度要求极快、访问频率高、
               数据丢失后不会造成影响 
         - 示例:
               1:创建memory引擎的表
                 create table t2(id int)engine=memory;
               2:插入数据并查询
               3:重启服务,再查询,数据消失
                 sudo /etc/init.d/mysql restart

     3. E-R模型：实体关系模型
           a)实体(Entry): 现实中可以区分的事物称之为实体
           b)关系(Relationship): 实体之间的联系
             - 一对一
             - 一对多
             - 多对多
           c)属性：实体所具有的数据特征
           d)E-R图：用图形化方式，表达实体间的关系
             矩形：表示实体
             椭圆：实体的属性
             菱形：实体间的关系
               一对一：菱形左右各写一个1
               一对多：菱形左右分别用1，N来表示
               多对多：菱形左右分别用M, N来表示
           e）练习：画出账户,交易明细,客户实体之间的E-R图（见ER图.pptx）
     4. 使用Python访问MySQL
       1）pymysql库：Python语言访问MySQL接口
         - 安装：
           在线：sudo pip3 install pymysql
           离线：
             第一步：下载安装包，下载地址：
                 https://pypi.org/project/PyMySQL/
             PyMySQL-0.9.3.tar.gz
         第二步：解压包，并执行安装
                 tar -zxvf PyMySQL-0.9.3.tar.gz
             cd PyMySQL-0.9.3
         第三步:进入目录,安装
             python3 setup.py install
         - 验证：进入Python交互模式，执行导入包操作
                 import pymysql
             如果不报错，则说明安装成功

        # 第一步：导入pymysql模块
import pymysql
host = 'localhost'#服务器地址
user = 'root'#用户名
passwd = '123456'#密码
dbname = 'bank'#库名称

#第二步：建立到数据库服务器的连接，创建连接对象
conn = pymysql.connect(host,user,passwd,dbname)
#第三步：获取游标对象(cursor)，通过调用数据库连接 对象获得游标        
cursor = conn.cursor()
#第四步：利用cursor对象提供的方法，执行SQL语句
cursor.execute("select * from acct_new")
result = cursor.fetchall()#取查询到的数据
for r in result:
     tmp = "帐号：%s,户名：%s,金额:%s"%(r[1],r[1],r[6])
     print(tmp)
#第五步：提交事务（如果需要）
#第六步：关闭游标对象
cursor.close()
#第七步：关闭数据库连接对象
conn.close()
       2）PyMySQL访问MySQL的流程
         第一步：导入pymysql模块
         第二步：建立到数据库服务器的连接，创建连接对象
         第三步：创建游标对象(cursor)，通过调用数据库连接
                 对象获得游标
         第四步：利用cursor对象提供的方法，执行SQL语句
         第五步：提交事务（如果需要）
         第六步：关闭游标对象
         第七步：关闭数据库连接对象
      
       3）PyMySQL模块的主要方法
         - connect:  连接数据库
             参数：host    服务器地址
               port    服务器端口(省略连默认端)
               user    用户名
               passwd    密码
               db    数据库名称
               charset    指定编码格式

        - connection连接对象支持的方法
            cursor()        获取游标
            commit()        提交事务
            rollback()    回滚事务
            close()        关闭游标

        - cursor游标对象支持的方法
            execute(sql)    执行SQL语句
            fetchall()    取得查询结果集中的所有数据
            fetchone()    取查询结果集中一笔数据
            fetchmany(size)    取得指定笔数数据
            close()        关闭游标对象

           只读属性：
            rowcount        返回修改影响的笔数
       4）示例：
      示例1：查询
         # pymysql查询示例
         import pymysql
         from db_conf import * #导入配置

        #建立到数据库服务器的连接，创建连接对象
         conn = pymysql.connect(host, user, \
                    password, dbname)              

        #创建游标对象(cursor)，通过调用数据库连接
         #对象获得游标
         cursor = conn.cursor()

        #利用cursor对象，执行SQL语句
         sql = "select * from acct"
         cursor.execute(sql)  #执行SQL语句

        #取出查询结果,并打印
         result = cursor.fetchall() #result是元组
         for r in result: #遍历result
             acct_no = r[0] #账号
             acct_name = r[1] #户名
             if r[6]: #判断是否为空值
             balance = float(r[6]) #余额
             else:
             balance = 0.00 #余额为空设置为0
             print("账号:%s, 户名:%s, 余额:%.2f" % \
              (acct_no, acct_name, balance))
         #关闭游标对象
         cursor.close()
         #关闭数据库连接对象
         conn.close()
       
         示例2：插入
         # pymysql的插入示例
         import pymysql
         from db_conf import * #导入配置

        try:       
             conn = pymysql.connect(host, user, \
                    password, dbname)  #连接数据库
             cursor = conn.cursor()  #获取游标
             #执行SQL语句
             sql = '''insert into
               acct(acct_no, acct_name, cust_no,
                acct_type, reg_date, status, balance)
               values('622345000010','Robert', 'C0010',
                1, date(now()), 1, 33.00)'''   
             print(sql)
             cursor.execute(sql) #执行
             conn.commit() #提交事务
             print("Insert OK")
         except Exception as e:
             print("数据库插入异常")
             print(e)
         finally:
             cursor.close() #关闭游标
             conn.close() #关闭连接
           
        示例3：修改
         import pymysql
         from db_conf import * #导入配置

        try:       
             conn = pymysql.connect(host, user, \
                    password, dbname)  #连接数据库
             cursor = conn.cursor()  #获取游标
             #执行SQL语句
             sql = '''update acct
                  set balance = balance + 1000
                 where acct_no='622345000010'
             '''   
             cursor.execute(sql) #执行
             conn.commit() #提交事务
             print("修改笔数:%d" % cursor.rowcount)
         except Exception as e:
             print("数据库修改异常")
             print(e)
         finally:
             cursor.close() #关闭游标
             conn.close() #关闭连接

        示例4：删除
        # pymysql删除示例
         import pymysql
         from db_conf import * #导入配置

        try:       
             conn = pymysql.connect(host, user, \
                    password, dbname)  #连接数据库
             cursor = conn.cursor()  #获取游标
             #执行SQL语句
             sql = '''delete from acct
             where acct_no='622345000010'
             '''   
             cursor.execute(sql) #执行
             conn.commit() #提交事务
             print("删除笔数:%d" % cursor.rowcount)
         except Exception as e:
             print("数据库删除异常")
             print(e)
         finally:
             cursor.close() #关闭游标
             conn.close() #关闭连接

    课堂作业：账户管理修改、删除功能添加上
             
     5. SQL语句优化
       1）在经常查询、排序的字段上建立索引
       2）尽量少使用不等于条件判断，不等于会放弃使用索引
       3）少使用null值判断，null值判断会放弃使用索引
       4）条件判断少使用or连接，in，not in
       5）模糊查询避免%前置
       6）查询时如果select *，直接写成字段名会提高效率
       7）尽量少在where子句中，使用字段进行运算。例如：
          select * from acct where balance / 2 > 5000
       8）数值类型的字段，进行条件比较时，一般比
          字符串比较效率更高

     作业：完善账户管理系统的修改、删除功能
            账户管理系统启动时，增加登录功能
            （原理：打印菜单前，先输入用户名、密码
              并到数据库自建的用户表中查询
          如果用户名、密码匹配，则登录成功）

#账户类
class Acct:
    def __init__(self,acct_no,acct_name,acct_type,balance):
        self.acct_no=acct_no
        self.acct_name=acct_name
        self.acct_type = acct_type
        self.balance = balance
    def __str__(self):
        ret = "账号：%s,户名：%s,类型：%d,余额：%.2f"%(self.acct_no,self.acct_name,self.acct_type,self.balance)
        return ret

#账户管理类(业务逻辑层)
#实现账户新增,修改,删除等逻辑处理
#账户类，仅用于数据传输
from db_oper import *
from acct import *
class AccMange:#账户管理类
    def __init__(self,db_oper):
        self.db_oper = db_oper#数据访问对象
    #查询所有庄户信息
    def query_all_acct(self):
        accts =[]#返回的Acct对象列表,可能有多个对象
        #拼装所需要的SQL
        sql = "select * from acct_new"
        #执行查询
        result = self.db_oper.do_query(sql)
        if not result:
            print("查询结果为空")
            return None
        #返回结果：实例化一个Acct对象列表返回
        for r in result:#便利数据集
            acct_no = r[0]#账户
            acct_name = r[1]#姓名
            acct_type =int(r[3])#类型
            balance = r[6]#金额
            accts.append(Acct(acct_no,acct_name,acct_type,balance))
        return accts#返回对象列表
    #根据账户查询,最多返回一个Ａcct对象

    def query_by_id(self,acct_no):
        sql = "select * from acct_new where acct_no = %s" % acct_no
        result = self.db_oper.do_query(sql)#执行查询
        if not result:
            print("查询返回空对象")
            return None
        #提取查询结果,实例化一个Acct对象返回
        r = result[0]#取得第一行数据
        acct_no = r[0]#账号
        acct_name = r[1]#户名
        acct_type = int(r[3])#类型
        balance = r[6]#余额
        return Acct(acct_no,acct_name,acct_type,balance)

    def insert_acct(self,acct_new):#插入
        sql = '''insert into acct_new(acct_no,acct_name,acct_type,balance)values('%s','%s',%d,%.2f)'''%(acct_new.acct_no,acct_new.acct_name,acct_new.acct_type,acct_new.balance)
        print("\nsql:%s\n"%sql)
        result =self.db_oper.do_update(sql)
        print("执行结构,影响行数：%d"%result)
        return

    def update_acct(self,acct_new):#更新
        sql="update acct set acct_type =%d,balance = %.2f where acct_no='%s'"%(acct_new.acct_type,acct_new.balance,acct_new.acct_no)
        print("\nsql:%s\n"%sql)
        result = self.db_oper.do_update(sql)
        print("执行结束,影响行数:%d"%result)
        return

    def query_by_name(self,acct_name):#模糊？
        if not acct_name:
            print("户名对象非法")
            return None
        if acct_name =="":
            print("用户名不能为空")
            return None

        accts =[]
        sql = "select * from acct_new where acct_name ='%s'" % acct_name

        result = self.db_oper.do_query(sql)
        if not result:
            print("查询返回空对象")
            return None

        for acct_info in result:
            acct_no = acct_info[0]
            acct_name = acct_info[1]
            acct_type = int(acct_info[3])
            balance = acct_info[6]
            accts.append(Acct(acct_no,acct_name,acct_type,balance))
        return accts

if  __name__=="__main__":
    db_oper = DBOper()#实例化访问对象
    db_oper.open_conn()#打开数据库连接

    am = AccMange(db_oper)#实例化AcctMange对象
    db_oper.close_conn()

#ui
from db_oper import *
from acct_manager import *
from acct import *

#全局变量
db_oper = None#打印主菜单
am =None
def print_menu():#打印主菜单
    #根据用户输入,进行不同操作
    menu = '''－－－－－－－－－－请选取要执行的操作－－－－－－－－－
    　　　　　　　　　　　　　　　　　１-查询所有　　　　　　　　　　　　　　４-新增账户
    　　　　　　　　　　　　　　　　　２-根据用户查询　　　　　　　　　　　５-修改账户
    　　　　　　　　　　　　　　　　　３-根据用户名查询　　　　　　　  ６-删除账户
    '''
    print(menu)
def query_all():#查询所有账户
    accts = am.query_all_acct()
    for a in accts:
        print(a)

def query_by_id():
    acct_no = input("请输入要查询的账号：")
    if not acct_no:
        print("输入账号不合法")
        return

    acct= am.query_by_id(acct_no)
    print("\n查询结果：")
    print(acct)#打印账户信息
    print("\n")

def query_by_name():#根据用户名查询
    acct_name = input("请输入要查询的户名：")
    if not acct_name:
        print("输入账号不合法")
        return

    accts =am.query_by_name(acct_name)
    print("\n查询结果：")
    for acct in accts:
        print(acct)#打印账户信息
    print("\n")

def add_acct():
    try:
        acct_no = input("请输入账号：")
        acct_name = input("请输入户名:")
        acct_type = int(input("请输入账户类型(1-借记卡 2-理财卡  3-代缴代扣卡)："))
        balance = float(input("请输入开户金额:"))
        assert acct_type in [1, 2, 3]
        assert balance >= 0.000001

        new_acct =Acct(acct_no,acct_name,acct_type,balance)
        am.insert_acct(new_acct)
    except Exception as e:
        print("操作错误")
        print(e)
    return

def update_acct():
    try:
        acct_no = input("请输入账号：")
        acct_name = input("请输入户名:")
        acct_type = int(input("请输入账户类型(1-借记卡 2-理财卡  3-代缴代扣卡)："))
        balance = float(input("请输入开户金额:"))

        assert acct_type in [1, 2, 3]
        assert balance >= 0.000001

        new_acct = Acct(acct_no,acct_name,acct_type,balance)
        am.update_acct(new_acct)
    except Exception as e:
        print("操作错误")
        print(e)
    return
#主函数
def main():
    global db_oper
    global am
    db_oper = DBOper()#实例化数据访问对象
    db_oper.open_conn()#打开数据库连接
    am = AccMange(db_oper)#实例化AccMange对象

    while True:
        print_menu()
        oper = input("请选则要执行的操作:")
        if oper == "1":  #查询所有账户
            query_all()
        elif oper == "2":  #根据账号查询
            query_by_id()
        elif oper == "3":  #根据户名查询
            query_by_name()
        elif oper == "4":  #新增账户
            add_acct()
        elif oper == "5":  #修改账户
            update_acct()
        elif oper == "6":  #删除账户
            pass
        else:
            break
    db_oper.close_conn()

if __name__ == "__main__":
    main()

#数据库配置
host = 'localhost'#服务器地址
user = 'root'#用户名
passwd = '123456'#密码
dbname = 'bank'#库名称

#数据库访问类
import pymysql
import db_conf
class DBOper:
    #构造方法
    def __init__(self):
        self.host =db_conf.host
        self.user =db_conf.user
        self.passwd =db_conf.passwd
        self.dbname =db_conf.dbname
        self.db_conn = None#数据库的连接对象

    #连接数据库
    def open_conn(self):
        try:
            self.db_conn = pymysql.connect(self.host,self.user,self.passwd,self.dbname)
        except Exception as e:
            print("连接数据库失败")
            print(e)
        else:
            print("连接数据库成功")
    #关闭连接
    def close_conn(self):
        try:
            self.db_conn.close()
        except Exception as e:
            print("连接数据库失败")
            print(e)
        else:
            print("关闭数据库成功")

    #执行查询返回结果集
    def do_query(self,sql):
        if not sql:#参数合法性判断
            print("sql语句不合法")
            return None
        if sql == "":#参数合法性判断
            print("sql语句不合法")
            return None

        try:
            cursor = self.db_conn.cursor()#获取游标
            cursor.execute(sql)#执行sql语句
            result = cursor.fetchall()#获取数据
            cursor.close()#关闭游标
            return result#返回查询数据集
        except Exception as e:
            print("查询出错")
            print(e)
            return None


    #执行增删改等变更操作
    def do_update(self,sql):
        if not sql:#参数合法性判断
            print("sql语句不合法")
            return None
        if sql == "":#参数合法性判断
            print("sql语句不合法")
            return None

        try:
            cursor = self.db_conn.cursor()#获取游标
            result = cursor.execute(sql)#执行ＳＱＬ语句
            self.db_conn.commit()#提交事物
            cursor.close()
            return result#返回受影响的笔数
        except Exception as e:
            print("执行sql语句出错")
            print(e)
            return None

#测试
if __name__=="__main__":
    dboper = DBOper()
    dboper.open_conn()
    ret = dboper.do_query("select * from acct_new")
    for x in ret:
        print(x)

    ret = dboper.do_update("update acct_new set balance = balance + 100 where acct_no = '6223456000 '")

    print(ret)
    #查询
    # result = dboper.do_query("select * from acct_new")
    # for x in result:
    #     print(x)

    # dboper.close_conn()#关闭数据库连接

    #修改数据测试
    # sql = '''update acct_new set balance = balance +1000 where acct_no ="6223456000"'''
    # ret = dboper.do_update(sql)
    # if not ret:#返回空对象,出错
    #     print("执行修改错误")
    # #非空对象,执行成功
    # else:
    #     print("影响笔数：%d"% ret)
    # dboper.close_conn()#关闭数据库连接