MySQL和python交互

准备工作：
创建数据表
create database jing_dong charset=utf8;
use jing_dong;
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
);
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);

-- 查询每种类型中最贵的电脑信息
-- 1 查找 每种类型 中 最贵的 max_price 价格
select cate_name,max(price) as max_price from goods group by cate_name
-- 2 关联查询 inner join 每种类型 中最贵的物品信息
select * from goods
inner join
(select cate_name,max(price) as max_price from goods group by cate_name) as max_price_goods
on goods.cate_name=max_price_goods.cate_name and goods.price=max_price_goods.max_price;

-- 商品种类换成对应ID
-- 创建"商品分类"表，将商品

第一步 创建表 (商品种类表 goods_cates )

create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);

第二步 同步 商品分类表 数据 将商品的所有 (种类信息) 写入到 (商品种类表) 中

-- 按照 分组 的方式查询 goods 表中的所有 种类(cate_name)
select cate_name from goods group by cate_name;

(注意) 把查询出来的 结果 写入 goods_cates 表里去 ( insert into ) 只插入name
insert into goods_cates(name) (select cate_name from goods group by cate_name);

第三部 同步 商品表 数据 通过 goods_cates 数据表来更新 goods 表

-- 因为要通过 goods_cates表 更新 goods 表 所以要把两个表连接起来
select * from goods inner join goods_cates on goods.cate_name = goods_cates.name;

-- 把 商品表 goods 中的 cate_name 全部替换成 商品分类表中的 商品id ( update ... set )
update (goods inner join goods_cates on goods.cate_name = goods_cates.name) set goods.cate_name = goods_cates.id;

第四部 修改表结构

-- 查看表结构(注意 两个表中的 外键类型需要一致)
-- 修改表结构 alter table 字段名字不同 change,把 cate_name 改成 cate_id int unsigned not null
alter table goods change cate_name cate_id int unsigned not null;

-- 创建 商品品牌表 goods_brands

第一步 创建 "商品品牌表" 表
-- 第一种方式 先创建表
create table goods_brands (
id int unsigned primary key auto_increment,
name varchar(40) not null
);

-- 插入数据 brand_name(分组)
-- 按照 分组 的方式查询 goods 表中的所有 种类(brand_name)
select brand_name from goods group by brand_name;
--(注意) 把查询出来的 结果 写入 goods_brands 表里去 ( insert into ) 只插入name
insert into goods_brands(name) (select brand_name from goods group by brand_name);

-- 第二种方式 创建表的同时插入数据(了解,不建议使用)
create table goods_brands (
id int unsigned primary key auto_increment,
name varchar(40) not null) select brand_name as name from goods group by brand_name;


第二步 同步数据
-- 通过goods_brands数据表来更新goods数据表 g.brand_name=b.id
select * from goods inner join goods_brands on goods.brand_name = goods_brands.name;
update (goods inner join goods_brands on goods.brand_name = goods_brands.name) set goods.brand_name = goods_brands.id;

第三部 修改表结构
-- 通过alter table语句修改表结构 brand_id int unsigned not null
alter table goods change brand_name brand_id int unsigned not null;

-- python与mysql的交互使用
-- 基本流程 1 connection对象 |2 cursor对象 |3 关闭cursor |4 关闭connection

from pymysql import *
conn=connect(参数列表)
# 参数host：连接的mysql主机，如果本机是'localhost'
# 参数port：连接的mysql主机的端口，默认是3306
# 参数database：数据库的名称
# 参数user：连接的用户名
# 参数password：连接的密码
# 参数charset：通信采用的编码方式，推荐使用utf8
cs1=conn.cursor()
sql = "sql语句"
cs1.execute(sql)
cs1.close()
conn.close()

具体案例：

import pymysql
    class JD(object):
        def __init__(self):
            # 建立连接
            self.conn = pymysql.connect(host='localhost', port=3306, database='jing_dong', user='root', password='mysql', charset='utf8')
            # 建立游标
            self.cs1 = self.conn.cursor()

        def __del__(self):
            self.cs1.close()
            self.conn.close()

        def sql_execute(self, sql):
            self.cs1.execute(sql)
            content = self.cs1.fetchall()
            for i in content:
                print(i)
        @staticmethod
        def show_menu():
            print("1. 查看所有的商品信息")
            print("2. 查看所有的商品种类")
            print("3. 查看所有的商品品牌")
            print("4. 添加一个商品种类")
            print("5. 删除一个商品种类")
            print("6. 查看一个商品的信息")
            print("0. 退出")
            num = input("请选择你想要执行的操作：")
            return num

        def show_goods_info(self):
            # 使用sql语句
            sql = "select * from goods;"
            self.sql_execute(sql)

        def show_goods_cates(self):
            sql = "select * from goods_cates;"
            self.sql_execute(sql)

        def show_goods_brands(self):
            sql = "select * from goods_brands;"
            self.sql_execute(sql)

        def add_cate(self):
            name = input("请输入想要添加的商品种类：")
            sql = "insert into goods_cates(name) value('%s')" % name
            self.sql_execute(sql)
            self.conn.commit()
        def del_cate(self):
            name = input("请输入想要删除的商品种类：")
            sql = "delete from goods_cates where name = '%s'" % name
            self.sql_execute(sql)
            self.conn.commit()
        def show_agoods_info(self):
            name = input("请输入想要查询商品的名字：")
            sql = "select * from goods where name = %s"
            # 防注入写法
            self.cs1.execute(sql, [name])
            content = self.cs1.fetchone()
            print(content)


        def run(self):
            while True:
                num = self.show_menu()
                if num == "1":
                    self.show_goods_info()
                elif num == "2":
                    self.show_goods_cates()
                elif num == "3":
                    self.show_goods_brands()
                elif num == "4":
                    self.add_cate()
                elif num == "5":
                    self.del_cate()
                elif num == "6":
                    self.show_agoods_info()
                elif num == "0":
                    break
                else:
                    print("没有这个选项")
    def main():
        jd = JD()

        jd.run()

    if __name__ == '__main__':
        main()