四：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英寸笔记本','笔记本','华硕','',default,default);

    insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','',default,default);

    insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','',default,default);

    insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','',default,default);

    insert into goods values(0,'x240 超极本','超级本','联想','',default,default);

    insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','',default,default);

    insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','',default,default);

    insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','',default,default);

    insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','',default,default);

    insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','',default,default);

    insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','',default,default);

    insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','',default,default);

    insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','',default,default);

    insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','',default,default);

    insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','',default,default);

    insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','',default,default);

    insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','',default,default);

    insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','',default,default);

    insert into goods values(0,'商务双肩背包','笔记本配件','索尼','',default,default);

    insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','',default,default);

    insert into goods values(0,'商务双肩背包','笔记本配件','索尼','',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)

得到goods-cates的结果

　　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,'索尼','',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 == "":

                self.show_all_items()

            elif ret == "":

                self.show_cates()

            elif ret == "":

                self.show_brands()

            elif ret == "":

                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 ~~~ 本篇介绍到此，下篇介绍数据的视图、事务、索引等相关知识

巴特西

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

一、拆分表操作

二、Python操作MySQL数据库

最新文章

热门文章