四：MySQL系列之Python交互（四）

 

　　该篇主要介绍MySQL数据库的分表、以及与Python的交互的基本操作等。

一、拆分表操作

　　1.1  准备工作

　　创建数据库 --> 使用数据库 --> 创建数据表 --- 添加记录

-- 1.创建一个名为淘宝的数据库
    create database taobao charset=utf8;

-- 2. 使用该数据库进行操作
    use taobao;

-- 3.创建数据表（商品）goods
    create table goods(
        id int unsigned primary key auto_increment not null,
        name varchar(150) not null,
        cate_name varchar(40) not null,
        brand_name varchar(40) not null,
        price decimal(10,3) not null default 0,
        is_show bit not null default 1,
        is_saleoff bit not null default 0
);

-- 4.插入记录
    - 向goods表中插入数据

    insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); 
    insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
    insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default); 
    insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default); 
    insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default); 
    insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default); 
    insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default); 
    insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
    insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default); 
    insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default); 
    insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default); 
    insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default); 
    insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default); 
    insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default); 
    insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default); 
    insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default); 
    insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default); 
    insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); 
    insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default); 
    insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default); 
    insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

　　从上表中：由于当我们需要修改数据表的cate_name 或者brand_name字段中的 "平板电脑" 或者 "苹果" 的名称时，修改起来会显得十分麻烦，并且如果数据十分庞大时，维护起来也会显得十分的麻烦，故我们可以使用拆分表来更好的维护数据。接下来我们介绍一下如何 拆分表；

　　

　　2.2 拆分表A

　　其主要步骤为：

　　　1、创建分类表A_a

　　　2、将将分组结果插入表A某个字段中去

　　　3、同步原表A的数据

　　　4、修改原表A的结构

　　　5、为原表A添加 外键

　　

       1、创建商品分类表    2、将查询结果插入goods_cates表的name字段中

-- 查分表

    -- 1、创建商品分类表
    create table if not exists goods_cates(
        id int unsigned primary key auto_increment,  
        name varchar(40) not null    
        )；

    -- 查询goods表的分类
    select cate_name from goods group by cate_name;

    --2、将查询结果插入goods_cates表的name字段中
    insert into goods_cates (name) select cate_name from goods group by cate_name;

　得到如下的结果：

mysql> select * from goods_cates;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | 台式机              |
|  2 | 平板电脑            |
|  3 | 服务器/工作站       |
|  4 | 游戏本              |
|  5 | 笔记本              |
|  6 | 笔记本配件          |
|  7 | 超级本              |
+----+---------------------+
7 rows in set (0.00 sec)

 

　　3、通过goods_cates数据表来更新goods表

-- 通过goods_cates数据表来更新goods表

    update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name = c.id;

 

　　4、修改goods表的结构，即修改cate_name 为 cate_id，且数据类型需修改为int

--- 查看 goods 的数据表结构,会发现 cate_name 对应的类型为 varchar 但是存储的都是数字

    alter table goods change cate_name cate_id int unsigned not null,

 

　　5、为goods表增设外键

-- 为表goods_cates添加记录
    insert into goods_cates(name) values ('路由器'),('交换机'),('网卡');

-- 为表goods添加记录
    insert into goods values(0,'皮皮双肩背包',12,'索尼','99',default,default);

-- 查询所有商品的详细信息 (通过左连接)
    select g.*,c.name from goods as g
    left join goods_cates as c on g.cate_id=c.id；d；

　　当我们为两个表分别插入值时，goods表中插入的cate_id值可以是任意整数（12）。那么我们怎么阻止这类无效数据的插入呢？这时候就需要外键了。

　什么是外键：　

• 外键约束:对数据的有效性进行验证

• 关键字: foreign key,只有 innodb数据库引擎 支持外键约束

-- 给brand_id 添加外键约束成功
alter table goods add foreign key (brand_id) references goods_brands(id);

-- 给cate_id 添加外键失败
-- 会出现1452错误

-- 错误原因:已经添加了一个不存在的cate_id值12,因此需要先删除
delete from goods where id=22;


-- 再添加外键
alter table goods add foreign key (cate_id) references goods_cates(id);

　　当然在实际开发的过程中很少使用外键，因为这会极大的降低表的更新效率。那么我们这么删除外键呢？还有就是在创建表的时候添加外键呢？

　6、创建时设置外键

--- 创建的时候设置外键
    foreign key(cate_id) references goods_cates(id)

　　外键也是一种约束，通常我们在创建表的时候会指定字段的 名字 数据类型 以及约束；而在设置外键 foreign key 给谁 references 关联谁即可；

　7、删除外键

-- 获取外键约束名称
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;

-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;

　　删除外键也是修改表结构故用alter语句。

 

二、Python操作MySQL数据库

　　首先我们若与MySQL数据库进行交互，需要安装一个模块 pymysql ，安装教程可以百度应该挺多；

　　交互流程大致如下图所示：

　　

即大致流程如下：直接看示例：

from pymysql import connect


# 1、创建conn对象
conn = connect(host= "localhost",port=3306,database="taobao",user="root",password="mysql",charset="utf8",)

# 2、创建cursor 对象
cursor = conn.cursor()

# 查询,获取受影响行数
count = cursor.execute("select * from goods;")
print(count)

# 获取数据
cursor.fetchone()  # 获取一行数据
cursor.fetchmany(3)  # 获取指定行数的数据
cursor.fetchall()  # 获取所有行的数据

# 修改
cursor.execute("insert into goods values('皮皮虾我们走',6,'雷神',"6888",0,0);")
conn.commit()

# 关闭cursor对象
cursor.close()
# 再关闭conn对象
conn.close()

　解析:其实conn对象负责与数据库之间的通信，例如提交commit，而 cursor对象更像是数据库的数据的操作人员，即可以查询、修改数据库，同时也充当容器的作用将取到的数据存储在自身口袋；

　　　　注：修改数据库需使用conn对象进行提交，关闭时先关闭cursor对象再关闭conn对象；

from pymysql import connect

class JingDong(object):
    def __init__(self):
        self.conn = connect(host="localhost",port=3306,user="root",password="mysql",database="jing_dong",charset="utf8")
        self.cursor = self.conn.cursor()

    def __del__(self):
        self.cursor.close()
        self.conn.close()
    
    def execute_sql(self,sql):
        # 执行sql语句
        self.cursor.execute(sql)
        for temp in self.cursor.fetchall():
            print(temp)

    def show_all_items(self):
        # 显示所有商品
        sql = "select * from goods;"
        self.execute_sql(sql)

    def show_cates(self):
        # 显示所有分类名称
        sql = "select name from goods_cates;"
        self.execute_sql(sql)

    def show_brands(self):
        # 显示所有商品的品牌分类
        sql = "select name from goods_brands"
        self.execute_sql(sql)

    def change_goods_name(self,good_name,change_name):
        lis =[change_name,good_name]
        sql = "update goods name set name=%s where name=%s;"
        self.cursor.execute(sql,lis)
        self.conn.commit()
        

    @staticmethod
    def print_menu():
        print("-----welcome to jingdong---")
        print("01-查询所有商品信息")
        print("02-查看所有商品的分类")
        print("03-查看所有商品的品牌分类")
        print("04-修改商品的名称")
        print("q-退出选择")
        ret = input("请输入功能对应的序号:")
        return ret

    def run(self):
        while True:
            ret =self. print_menu()
            if ret == "01":
                self.show_all_items()
            elif ret == "02":
                self.show_cates()
            elif ret == "03":
                self.show_brands()
            elif ret == "04":
                good_name=input("请输入要修改商品的名字:")
                change_name = input("请输入修改后的名字:")
                self.change_goods_name(good_name,change_name)
            elif ret == "q":
                break
            else:
                print("您输入的有误，请重新输入")


def main():
    jd = JingDong()
    jd.run()
if __name__ == "__main__":
    main()

 

 

　　over ~~~ 本篇介绍到此，下篇介绍数据的视图、事务、索引等相关知识