Python操作MySQL数据库完成简易的增删改查功能

Love The Way You Lie 2022-03-07 14:45 196阅读 0赞

说明：该篇博客是博主一字一码编写的，实属不易，请尊重原创，谢谢大家！

**目录**

一丶项目介绍

二丶效果展示

三丶数据准备

四丶代码实现

五丶完整代码

--------------------

### 一丶项目介绍 ###

**1.**叙述

博主闲暇之余花了10个小时写的小程序，对于python操作数据库不太会的同学，很值得学习参考

通过python与mysql数据库的交互来模拟京东商城，代码逻辑就是对商品进行添加，修改，查询，以及删除操作，非常简单。

**2.**项目环境

操作系统：Linux（ubuntu）

IDE：PyCharm2018

数据库：MySQL

Python版本：3.5

### 二丶效果展示 ###

**说明：**博主这里只是展示了查询功能中的单个商品信息查询，当查询商品不存在时，则会提示用户

运行程序----选择1商品查询功能----选择0查询单个商品信息----显示查询到几条数据----显示详情数据----显示查询功能菜单----选择4返回主功能菜单----跳转到主功能菜单

*******欢迎来到京东商城(主功能菜单)*******
                       1.商品查询功能
                       2.商品添加功能
                       3.商品修改功能
                       4.商品删除功能
                       5.退出程序
                
    请选择功能序号：1
    
                *******欢迎来到京东商城(查询功能)*******
                       0.查询单个商品信息
                       1.查询所有的商品信息
                       2.查询商品分类信息
                       3.查询所有的品牌分类信息
                       4.返回主功能菜单
                
    请选择查询功能序号：0
    请输入要查找商品名字：x
    一共查询到5条记录
    (2, 'x550cc 15.6英寸笔记本', 5, 2, Decimal('2799.000'), b'\x01', b'\x00')
    (3, 'x240 超极本', 7, 7, Decimal('4880.000'), b'\x01', b'\x00')
    (12, 'at7-7414lp 台式电脑 linux ）', 1, 3, Decimal('3699.000'), b'\x01', b'\x00')
    (18, 'x3250 m4机架式服务器', 3, 1, Decimal('6888.000'), b'\x01', b'\x00')
    (21, '华硕 ROG玩家国度GFX72 17英寸游戏 笔记本', 4, 2, Decimal('32998.000'), b'\x01', b'\x00')
    
                *******欢迎来到京东商城(查询功能)*******
                       0.查询单个商品信息
                       1.查询所有的商品信息
                       2.查询商品分类信息
                       3.查询所有的品牌分类信息
                       4.返回主功能菜单
                
    请选择查询功能序号：4
    
                *******欢迎来到京东商城(主功能菜单)*******
                       1.商品查询功能
                       2.商品添加功能
                       3.商品修改功能
                       4.商品删除功能
                       5.退出程序
                
    请选择功能序号：5
    程序正在退出，请稍候....
    
    Process finished with exit code 0

### 三丶数据准备 ###

**1.**创建数据库jing\_dong

create database jing_dong charset = utf8;

**2.**在jing\_dong数据库中创建goods表

create table goods(
        id int unsigned primary key auto_increment not null,
        name varchar(150) not null,
        cate_id int(10) unsigned not null,
        brand_id int(10) unsigned not null,
        price decimal(10,3) not null default 0,
        is_show bit not null default 1,
        is_saleoff bit not null default 0
    );

**3.**向goods商品信息表中插入以下数据

**说明：**其实除了goods商品信息表以外，还有goods\_ctaes商品分类表以及goods\_brands商品品牌表，在goods表中的cate\_id以及brand\_id都是指向这两张表中的主键id，因为博主在这里只是针对goods商品信息表进行增删改查，所以这里不需要用到那两张表

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

### 四丶代码实现 ###

**1.**业务逻辑分为三层，第一层负责显示主功能菜单，第二层分为（增删改查）功能菜单，第三层详细功能实现

**2.**代码块说明：

class JD：具体业务代码逻辑实现，完成连接本地数据库，对数据库中的goods表数据进行增删改查业务操作

class Menu：功能界面选项打印显示

def select\_main：查询功能主业务逻辑

def add\_main：增加功能主业务逻辑

def update\_main：修改功能主业务逻辑

def delete\_main：删除功能主业务逻辑

def main：主功能业务逻辑

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70][]

**3.**main方法代码实现

def main():
        """主功能菜单"""
        while True:
            Menu.print_main_menu()
            num = input("请选择功能序号：")
            if num == "1":
                select_main()
            elif num == "2":
                add_main()
            elif num == "3":
                update_main()
            elif num == "4":
                delete_main()
            elif num == "5":
                JD().close_database()
                print("程序正在退出，请稍候....")
                time.sleep(2)
                break
            else:
                print("您的输入不正确，请重新输入！")

**4.**select\_main方法代码实现

def select_main():
        """查询商品信息功能"""
        while True:
            Menu.print_select_menu()
            num = input("请选择查询功能序号：")
            if num == "0":
                find_name = input("请输入要查找商品名字：")
                find_name = "%" + find_name + "%"
                result, count = JD().select_single_good(find_name)
                if result is not None:
                    print("一共查询到%s条记录" % count)
                    for res in result:
                        print(res)
                else:
                    print("对不起！您输入的商品不存在...")
            elif num == "1":
                temps, count = JD().select_all_goods()
                print("一共查询到%s个商品" % count)
                for temp in temps:
                    print(temp)
            elif num == "2":
                result, count = JD().select_goods_class()
                print("一共查询到%s类商品" % count)
                print(result)
            elif num == "3":
                result, count = JD().select_goods_logo()
                print("一共查询到有%s种品牌" % count)
                print(result)
            elif num == "4":
                break
            else:
                print("输入不正确，请重新输入!")

**5.**add\_main方法代码实现

def add_main():
    """添加商品信息功能"""
        while True:
            Menu.print_add_menu()
            num = input("请选择增加功能序号：")
            if num == "1":
                good_name = input("请输入商品名字：")
                while True:
                    Menu.print_goods_cates()
                    good_cate = input("请选择商品分类：")
                    if good_cate in("1", "2", "3", "4", "5", "6", "7", "8", "9", "10"):
                        good_cate = int(good_cate)
                        break
                    else:
                        print("输入不正确，请重新输入！")
                while True:
                    Menu.print_goods_brands()
                    good_brand = input("请选择商品品牌：")
                    if good_brand in("1", "2", "3", "4", "5", "6", "7", "8", "9"):
                        good_brand = int(good_brand)
                        break
                    else:
                        print("输入不正确，请重新输入！")
                good_price = float(input("请输入商品价格："))
                JD().add_single_good(good_name, good_cate, good_brand, good_price)
            elif num == "2":
                break
            else:
                print("输入错误，请重新输入！")

**6.**update\_main方法代码实现

def update_main():
        """修改商品信息功能"""
        while True:
            Menu.print_update_menu()
            num = input("请选择修改功能序号：")
            if num == "1":
                ids = list()
                for data in JD().get_goods_ids():
                    for id in data:
                        ids.append(id)
                good_id_name = input("请输入要修改商品的编号或名字：")
                try:
                    if int(good_id_name) in ids:
                        # 表示输入了正确的商品编号
                        JD().update_single_good(0, good_id_name)  # 0表示用户输入的是商品编号,否则表示商品名字
                    else:
                        while True:
                            new_id = input("您输入的商品编号不存在，请重新输入：")
                            if int(new_id) in ids:
                                break
                        JD().update_single_good(0, new_id)
                except Exception as e:
                    JD().update_single_good(e, good_id_name)
            elif num == "2":
                break
            else:
                print("输入错误，请重新输入！")

**7.**delete\_main方法代码实现

def delete_main():
        """删除商品信息功能"""
        while True:
            Menu.print_delete_menu()
            num = input("请选择删除功能序号：")
            if num == "1":
                ids = list()
                for data in JD().get_goods_ids():
                    for id in data:
                        ids.append(id)
                print("所有商品信息如下：")
                for good in JD().get_goods_id_name():
                    print(good)
                while True:
                    good_id = input("请输入要删除的商品编号：")
                    try:
                        if int(good_id) in ids:
                            JD().delete_single_good(good_id)
                            break
                        else:
                            print("您输入的商品编号不存在，请重新输入：")
                    except Exception as e:
                        print("非法输入", e)
    
            elif num == "2":
                temps, count = JD().select_all_goods()
                print("一共有%s种商品信息" % count)
                for temp in temps:
                    print(temp)
                num = input("1.点错了 2.继续删除：")
                if num == "1":
                    break
                elif num == "2":
                    s = input("数据删除后将无法恢复，确认请输入y：").lower()
                    if s == "y":
                        JD().delete_all_goods()
                else:
                    print("输入错误，操作取消中....")
                    time.sleep(1)
                    break
            elif num == "3":
                break
            else:
                print("输入错误，请重新输入！")

**8.**Menu类代码实现

class Menu(object):
        """界面信息打印显示"""
        @staticmethod
        def print_main_menu():
            print(
                """
                *******欢迎来到京东商城(主功能菜单)*******
                       1.商品查询功能
                       2.商品添加功能
                       3.商品修改功能
                       4.商品删除功能
                       5.退出程序
                """
            )
    
        @staticmethod
        def print_select_menu():
            print(
                """
                *******欢迎来到京东商城(查询功能)*******
                       0.查询单个商品信息
                       1.查询所有的商品信息
                       2.查询商品分类信息
                       3.查询所有的品牌分类信息
                       4.返回主功能菜单
                """
            )
    
        @staticmethod
        def print_add_menu():
            print(
                """
                *******欢迎来到京东商城(增加功能)*******
                       1.添加商品信息
                       2.返回主功能菜单
                """
            )
    
        @staticmethod
        def print_update_menu():
            print(
                """
                *******欢迎来到京东商城(修改功能)*******
                       1.修改单个商品信息
                       2.返回主功能菜单
                """
            )
    
        @staticmethod
        def print_delete_menu():
            print(
                """
                *******欢迎来到京东商城(删除功能)*******
                       1.删除单个商品信息
                       2.删除所有的商品信息
                       3.返回主功能菜单
                """
            )
    
        @staticmethod
        def print_goods_cates():
            print(
                """
            ******商品分类******
                1.台式机             
                2.平板电脑           
                3.服务器/工作站       
                4.游戏本              
                5.笔记本             
                6.笔记本配件         
                7.超级本             
                8.硬盘               
                9.光盘               
                10.显示器       
                """
            )
    
        @staticmethod
        def print_goods_brands():
            print(
                """
         ******商品品牌******
                1.ibm
                2.华硕  
                3.宏碁 
                4.惠普
                5.戴尔
                6.索尼
                7.联想 
                8.苹果  
                9.雷神        
                """
            )

**9.**JD类代码实现

class JD(object):
        def __init__(self):
            """初始化 连接数据库"""
            self.conn = connect(host="localhost",
                                port=3306,
                                user="root",
                                password="mysql",
                                database="jing_dong",
                                charset="utf8"
                                )
            self.cursors = self.conn.cursor()
    
        def select_single_good(self, find_name):
            """查询单个商品信息"""
            count = self.cursors.execute("select * from goods where name like '%s'" % find_name)
            if count != 0:
                return self.cursors.fetchall(), count
            else:
                return None
    
        def select_all_goods(self):
            """查询所有商品信息"""
            count = self.cursors.execute("select * from goods")
            return self.cursors.fetchall(), count
    
        def select_goods_class(self):
            """查询商品分类"""
            count = self.cursors.execute("select name from goods_cates")
            return self.cursors.fetchall(), count
    
        def select_goods_logo(self):
            """查询商品品牌"""
            count = self.cursors.execute("select name from goods_brands")
            return self.cursors.fetchall(), count
    
        def add_single_good(self, name, cate, brand, price):
            """添加单个商品信息"""
            # sql = ("insert into goods(name,cate_id,brand_id,price)values(%s,%d,%d,%s) " % (name, cate, brand, price))
            count = self.cursors.execute("insert into goods (name,cate_id,brand_id,price) values ('%s',%d,%d,%f) " % (name, cate, brand, price))
            if count == 1:
                print("添加商品信息成功....")
                self.conn.commit()
                self.cursors.execute("select * from goods where name = '%s'" % name)
                print(self.cursors.fetchone())
            else:
                print("添加商品信息失败....")
    
            # self.cursors.execute("insert into goods()")
    
        def get_goods_ids(self):
            """获取所有商品的id编号"""
            self.cursors.execute("select id from goods")
            return self.cursors.fetchall()
    
        def get_goods_id_name(self):
            """获取所有商品的编号以名称"""
            self.cursors.execute("select id,name from goods order by id")
            return self.cursors.fetchall()
    
        @staticmethod
        def get_user_update_data():
            """获取用户修改的商品信息"""
            flag = True  # 设置开关 当用户输入的商品信息项不存在时则为False
            data = dict()
            index = list()
            while True:
                print("1.商品名称 2.商品分类 3.商品品牌 4.商品价格")
                user_choose = input("请选择要修改的商品信息项：")
                if not len(user_choose) > 4 or len(user_choose) < 1:
                    break
            for i in user_choose:
                if i == "1":
                    u_name = input("请输入修改后的商品名称：")
                    data["name"] = u_name
                    index.append(i)
                elif i == "2":
                    Menu.print_goods_cates()
                    try:
                        u_cate = int(input("请输入修改后的商品分类编号:"))
                        data["cate_id"] = u_cate
                        index.append(i)
                    except Exception as e:
                        print("您输入的商品分类编号错误", e)
                        return
                elif i == "3":
                    Menu.print_goods_brands()
                    try:
                        u_brand = int(input("请输入修改后的商品品牌编号："))
                        data["brand_id"] = u_brand
                        index.append(i)
                    except Exception as e:
                        print("您输入的商品品牌编号错误", e)
                        return
                elif i == "4":
                    try:
                        u_price = float(input("请输入修改后的商品价格："))
                        data["price"] = u_price
                        index.append(i)
                    except Exception as e:
                        print("您输入的商品价格错误", e)
                        return
                else:
                    flag = False
                    print("您输入的商品信息项目含有非法输入（%s）" % i)
            if flag:
                return index, data  # {'brand_id': 2, 'cate_id': 2, 'name': 'ssss'}
    
        def update_single_good(self, sign, name_id):
            """修改商品信息"""
            # 1.根据用户输入的查询条件，获取商品信息并显示到客户窗口上
            if 0 == sign:
                good_id = name_id
                self.cursors.execute("select * from goods where id = '%s'" % good_id)
                # 因为在update_main方法中已经对商品编号进行判断，用户输入的商品编号在数据库中能找到才能调用此方法
                print(self.cursors.fetchone())
                index, data = JD().get_user_update_data()
                if index:
                    for i in index:
                        if i == "1":
                            key1 = "name"
                            count = self.cursors.execute(
                                "update goods set %s = '%s' where id = '%s'" % (key1, data[key1], good_id))
                            if count == 1:
                                print("修改商品名称成功！")
                                self.conn.commit()
                            else:
                                print("修改商品名称失败！")
                                return
                        elif i == "2":
                            key2 = "cate_id"
                            count = self.cursors.execute(
                                "update goods set %s = '%s' where id = '%s'" % (key2, data[key2], good_id))
                            if count == 1:
                                print("修改商品分类成功！")
                                self.conn.commit()
                            else:
                                print("修改商品分类失败！")
                                return
                        elif i == "3":
                            key3 = "brand_id"
                            count = self.cursors.execute(
                                "update goods set %s = '%s' where id = '%s'" % (key3, data[key3], good_id))
                            if count == 1:
                                print("修改商品品牌成功！")
                                self.conn.commit()
                            else:
                                print("修改商品品牌失败！")
                                return
                        elif i == "4":
                            key4 = "price"
                            count = self.cursors.execute(
                                "update goods set %s = '%s' where id = '%s'" % (key4, data[key4], good_id))
                            if count == 1:
                                print("修改商品价格成功！")
                                self.conn.commit()
                            else:
                                print("修改商品价格失败！")
                                return
                    # 2.根据用户选择商品修改项对其进行修改，将修改成功后的商品信息展现出来
                    self.cursors.execute("select * from goods where id = '%s'" % good_id)
                    print(self.cursors.fetchone())
                else:
                    print("用户未选择")
                    return
            else:
                good_name = name_id
                count = self.cursors.execute("select * from goods where name = '%s'" % good_name)
                good_id = int  # 用于保存商品编码，因为编码是唯一不能修改的
                if count != 0:
                    for i in self.cursors.fetchone():
                        good_id = i
                        break
                    index, data = JD().get_user_update_data()
                    if index:
                        for i in index:
                            if i == "1":
                                key1 = "name"
                                count = self.cursors.execute(
                                    "update goods set %s = '%s' where name = '%s'" % (key1, data[key1], good_name))
                                if count == 1:
                                    print("修改商品名称成功！")
                                    self.conn.commit()
                                else:
                                    print("修改商品名称失败！")
                                    return
                            elif i == "2":
                                key2 = "cate_id"
                                count = self.cursors.execute(
                                    "update goods set %s = '%s' where name = '%s'" % (key2, data[key2], good_name))
                                if count == 1:
                                    print("修改商品分类成功！")
                                    self.conn.commit()
                                else:
                                    print("修改商品分类失败！")
                                    return
                            elif i == "3":
                                key3 = "brand_id"
                                count = self.cursors.execute(
                                    "update goods set %s = '%s' where name = '%s'" % (key3, data[key3], good_name))
                                if count == 1:
                                    print("修改商品品牌成功！")
                                    self.conn.commit()
                                else:
                                    print("修改商品品牌失败！")
                                    return
                            elif i == "4":
                                key4 = "price"
                                count = self.cursors.execute(
                                    "update goods set %s = '%s' where name = '%s'" % (key4, data[key4], good_name))
                                if count == 1:
                                    print("修改商品价格成功！")
                                    self.conn.commit()
                                else:
                                    print("修改商品价格失败！")
                                    return
                        self.cursors.execute("select * from goods where id = '%s'" % good_id)
                        print(self.cursors.fetchone())
                    else:
                        print("用户未选择")
                        return
                else:
                    new_name = input("您输入的商品名称不存在，请重新输入：")
                    self.update_single_good(1, new_name)
    
        def delete_single_good(self, good_id):
            """删除单个商品信息"""
            count = self.cursors.execute("delete from goods where id = '%s'" % good_id)
            if count == 1:
                print("删除商品信息成功！")
                self.conn.commit()
            else:
                print("删除商品信息失败！")
                return
    
        def delete_all_goods(self):
            """删除所有商品信息"""
            count = self.cursors.execute("truncate table goods")
            if count == 0:
                print("所有商品信息已全部删除")
    
        def close_database(self):
            """关闭数据库连接对象以及游标对象"""
            self.cursors.close()
            self.conn.close()

### 五丶完整代码 ###

from pymysql import *
    import time
    
    
    class JD(object):
        def __init__(self):
            """初始化 连接数据库"""
            self.conn = connect(host="localhost",
                                port=3306,
                                user="root",
                                password="mysql",
                                database="jing_dong",
                                charset="utf8"
                                )
            self.cursors = self.conn.cursor()
    
        def select_single_good(self, find_name):
            """查询单个商品信息"""
            count = self.cursors.execute("select * from goods where name like '%s'" % find_name)
            if count != 0:
                return self.cursors.fetchall(), count
            else:
                return None
    
        def select_all_goods(self):
            """查询所有商品信息"""
            count = self.cursors.execute("select * from goods")
            return self.cursors.fetchall(), count
    
        def select_goods_class(self):
            """查询商品分类"""
            count = self.cursors.execute("select name from goods_cates")
            return self.cursors.fetchall(), count
    
        def select_goods_logo(self):
            """查询商品品牌"""
            count = self.cursors.execute("select name from goods_brands")
            return self.cursors.fetchall(), count
    
        def add_single_good(self, name, cate, brand, price):
            """添加单个商品信息"""
            # sql = ("insert into goods(name,cate_id,brand_id,price)values(%s,%d,%d,%s) " % (name, cate, brand, price))
            count = self.cursors.execute("insert into goods (name,cate_id,brand_id,price) values ('%s',%d,%d,%f) " % (name, cate, brand, price))
            if count == 1:
                print("添加商品信息成功....")
                self.conn.commit()
                self.cursors.execute("select * from goods where name = '%s'" % name)
                print(self.cursors.fetchone())
            else:
                print("添加商品信息失败....")
    
            # self.cursors.execute("insert into goods()")
    
        def get_goods_ids(self):
            """获取所有商品的id编号"""
            self.cursors.execute("select id from goods")
            return self.cursors.fetchall()
    
        def get_goods_id_name(self):
            """获取所有商品的编号以名称"""
            self.cursors.execute("select id,name from goods order by id")
            return self.cursors.fetchall()
    
        @staticmethod
        def get_user_update_data():
            """获取用户修改的商品信息"""
            flag = True  # 设置开关 当用户输入的商品信息项不存在时则为False
            data = dict()
            index = list()
            while True:
                print("1.商品名称 2.商品分类 3.商品品牌 4.商品价格")
                user_choose = input("请选择要修改的商品信息项：")
                if not len(user_choose) > 4 or len(user_choose) < 1:
                    break
            for i in user_choose:
                if i == "1":
                    u_name = input("请输入修改后的商品名称：")
                    data["name"] = u_name
                    index.append(i)
                elif i == "2":
                    Menu.print_goods_cates()
                    try:
                        u_cate = int(input("请输入修改后的商品分类编号:"))
                        data["cate_id"] = u_cate
                        index.append(i)
                    except Exception as e:
                        print("您输入的商品分类编号错误", e)
                        return
                elif i == "3":
                    Menu.print_goods_brands()
                    try:
                        u_brand = int(input("请输入修改后的商品品牌编号："))
                        data["brand_id"] = u_brand
                        index.append(i)
                    except Exception as e:
                        print("您输入的商品品牌编号错误", e)
                        return
                elif i == "4":
                    try:
                        u_price = float(input("请输入修改后的商品价格："))
                        data["price"] = u_price
                        index.append(i)
                    except Exception as e:
                        print("您输入的商品价格错误", e)
                        return
                else:
                    flag = False
                    print("您输入的商品信息项目含有非法输入（%s）" % i)
            if flag:
                return index, data  # {'brand_id': 2, 'cate_id': 2, 'name': 'ssss'}
    
        def update_single_good(self, sign, name_id):
            """修改商品信息"""
            # 1.根据用户输入的查询条件，获取商品信息并显示到客户窗口上
            if 0 == sign:
                good_id = name_id
                self.cursors.execute("select * from goods where id = '%s'" % good_id)
                # 因为在update_main方法中已经对商品编号进行判断，用户输入的商品编号在数据库中能找到才能调用此方法
                print(self.cursors.fetchone())
                index, data = JD().get_user_update_data()
                if index:
                    for i in index:
                        if i == "1":
                            key1 = "name"
                            count = self.cursors.execute(
                                "update goods set %s = '%s' where id = '%s'" % (key1, data[key1], good_id))
                            if count == 1:
                                print("修改商品名称成功！")
                                self.conn.commit()
                            else:
                                print("修改商品名称失败！")
                                return
                        elif i == "2":
                            key2 = "cate_id"
                            count = self.cursors.execute(
                                "update goods set %s = '%s' where id = '%s'" % (key2, data[key2], good_id))
                            if count == 1:
                                print("修改商品分类成功！")
                                self.conn.commit()
                            else:
                                print("修改商品分类失败！")
                                return
                        elif i == "3":
                            key3 = "brand_id"
                            count = self.cursors.execute(
                                "update goods set %s = '%s' where id = '%s'" % (key3, data[key3], good_id))
                            if count == 1:
                                print("修改商品品牌成功！")
                                self.conn.commit()
                            else:
                                print("修改商品品牌失败！")
                                return
                        elif i == "4":
                            key4 = "price"
                            count = self.cursors.execute(
                                "update goods set %s = '%s' where id = '%s'" % (key4, data[key4], good_id))
                            if count == 1:
                                print("修改商品价格成功！")
                                self.conn.commit()
                            else:
                                print("修改商品价格失败！")
                                return
                    # 2.根据用户选择商品修改项对其进行修改，将修改成功后的商品信息展现出来
                    self.cursors.execute("select * from goods where id = '%s'" % good_id)
                    print(self.cursors.fetchone())
                else:
                    print("用户未选择")
                    return
            else:
                good_name = name_id
                count = self.cursors.execute("select * from goods where name = '%s'" % good_name)
                good_id = int  # 用于保存商品编码，因为编码是唯一不能修改的
                if count != 0:
                    for i in self.cursors.fetchone():
                        good_id = i
                        break
                    index, data = JD().get_user_update_data()
                    if index:
                        for i in index:
                            if i == "1":
                                key1 = "name"
                                count = self.cursors.execute(
                                    "update goods set %s = '%s' where name = '%s'" % (key1, data[key1], good_name))
                                if count == 1:
                                    print("修改商品名称成功！")
                                    self.conn.commit()
                                else:
                                    print("修改商品名称失败！")
                                    return
                            elif i == "2":
                                key2 = "cate_id"
                                count = self.cursors.execute(
                                    "update goods set %s = '%s' where name = '%s'" % (key2, data[key2], good_name))
                                if count == 1:
                                    print("修改商品分类成功！")
                                    self.conn.commit()
                                else:
                                    print("修改商品分类失败！")
                                    return
                            elif i == "3":
                                key3 = "brand_id"
                                count = self.cursors.execute(
                                    "update goods set %s = '%s' where name = '%s'" % (key3, data[key3], good_name))
                                if count == 1:
                                    print("修改商品品牌成功！")
                                    self.conn.commit()
                                else:
                                    print("修改商品品牌失败！")
                                    return
                            elif i == "4":
                                key4 = "price"
                                count = self.cursors.execute(
                                    "update goods set %s = '%s' where name = '%s'" % (key4, data[key4], good_name))
                                if count == 1:
                                    print("修改商品价格成功！")
                                    self.conn.commit()
                                else:
                                    print("修改商品价格失败！")
                                    return
                        self.cursors.execute("select * from goods where id = '%s'" % good_id)
                        print(self.cursors.fetchone())
                    else:
                        print("用户未选择")
                        return
                else:
                    new_name = input("您输入的商品名称不存在，请重新输入：")
                    self.update_single_good(1, new_name)
    
        def delete_single_good(self, good_id):
            """删除单个商品信息"""
            count = self.cursors.execute("delete from goods where id = '%s'" % good_id)
            if count == 1:
                print("删除商品信息成功！")
                self.conn.commit()
            else:
                print("删除商品信息失败！")
                return
    
        def delete_all_goods(self):
            """删除所有商品信息"""
            count = self.cursors.execute("truncate table goods")
            if count == 0:
                print("所有商品信息已全部删除")
    
        def close_database(self):
            """关闭数据库连接对象以及游标对象"""
            self.cursors.close()
            self.conn.close()
    
    
    class Menu(object):
        """界面信息打印显示"""
        @staticmethod
        def print_main_menu():
            print(
                """
                *******欢迎来到京东商城(主功能菜单)*******
                       1.商品查询功能
                       2.商品添加功能
                       3.商品修改功能
                       4.商品删除功能
                       5.退出程序
                """
            )
    
        @staticmethod
        def print_select_menu():
            print(
                """
                *******欢迎来到京东商城(查询功能)*******
                       0.查询单个商品信息
                       1.查询所有的商品信息
                       2.查询商品分类信息
                       3.查询所有的品牌分类信息
                       4.返回主功能菜单
                """
            )
    
        @staticmethod
        def print_add_menu():
            print(
                """
                *******欢迎来到京东商城(增加功能)*******
                       1.添加商品信息
                       2.返回主功能菜单
                """
            )
    
        @staticmethod
        def print_update_menu():
            print(
                """
                *******欢迎来到京东商城(修改功能)*******
                       1.修改单个商品信息
                       2.返回主功能菜单
                """
            )
    
        @staticmethod
        def print_delete_menu():
            print(
                """
                *******欢迎来到京东商城(删除功能)*******
                       1.删除单个商品信息
                       2.删除所有的商品信息
                       3.返回主功能菜单
                """
            )
    
        @staticmethod
        def print_goods_cates():
            print(
                """
            ******商品分类******
                1.台式机             
                2.平板电脑           
                3.服务器/工作站       
                4.游戏本              
                5.笔记本             
                6.笔记本配件         
                7.超级本             
                8.硬盘               
                9.光盘               
                10.显示器       
                """
            )
    
        @staticmethod
        def print_goods_brands():
            print(
                """
         ******商品品牌******
                1.ibm
                2.华硕  
                3.宏碁 
                4.惠普
                5.戴尔
                6.索尼
                7.联想 
                8.苹果  
                9.雷神        
                """
            )
    
    
    def select_main():
        """查询商品信息功能"""
        while True:
            Menu.print_select_menu()
            num = input("请选择查询功能序号：")
            if num == "0":
                find_name = input("请输入要查找商品名字：")
                find_name = "%" + find_name + "%"
                result, count = JD().select_single_good(find_name)
                if result is not None:
                    print("一共查询到%s条记录" % count)
                    for res in result:
                        print(res)
                else:
                    print("对不起！您输入的商品不存在...")
            elif num == "1":
                temps, count = JD().select_all_goods()
                print("一共查询到%s个商品" % count)
                for temp in temps:
                    print(temp)
            elif num == "2":
                result, count = JD().select_goods_class()
                print("一共查询到%s类商品" % count)
                print(result)
            elif num == "3":
                result, count = JD().select_goods_logo()
                print("一共查询到有%s种品牌" % count)
                print(result)
            elif num == "4":
                break
            else:
                print("输入不正确，请重新输入!")
    
    
    def add_main():
        """添加商品信息功能"""
        while True:
            Menu.print_add_menu()
            num = input("请选择增加功能序号：")
            if num == "1":
                good_name = input("请输入商品名字：")
                while True:
                    Menu.print_goods_cates()
                    good_cate = input("请选择商品分类：")
                    if good_cate in("1", "2", "3", "4", "5", "6", "7", "8", "9", "10"):
                        good_cate = int(good_cate)
                        break
                    else:
                        print("输入不正确，请重新输入！")
                while True:
                    Menu.print_goods_brands()
                    good_brand = input("请选择商品品牌：")
                    if good_brand in("1", "2", "3", "4", "5", "6", "7", "8", "9"):
                        good_brand = int(good_brand)
                        break
                    else:
                        print("输入不正确，请重新输入！")
                good_price = float(input("请输入商品价格："))
                JD().add_single_good(good_name, good_cate, good_brand, good_price)
            elif num == "2":
                break
            else:
                print("输入错误，请重新输入！")
    
    
    def update_main():
        """修改商品信息功能"""
        while True:
            Menu.print_update_menu()
            num = input("请选择修改功能序号：")
            if num == "1":
                ids = list()
                for data in JD().get_goods_ids():
                    for id in data:
                        ids.append(id)
                good_id_name = input("请输入要修改商品的编号或名字：")
                try:
                    if int(good_id_name) in ids:
                        # 表示输入了正确的商品编号
                        JD().update_single_good(0, good_id_name)  # 0表示用户输入的是商品编号,否则表示商品名字
                    else:
                        while True:
                            new_id = input("您输入的商品编号不存在，请重新输入：")
                            if int(new_id) in ids:
                                break
                        JD().update_single_good(0, new_id)
                except Exception as e:
                    JD().update_single_good(e, good_id_name)
            elif num == "2":
                break
            else:
                print("输入错误，请重新输入！")
    
    
    def delete_main():
        """删除商品信息功能"""
        while True:
            Menu.print_delete_menu()
            num = input("请选择删除功能序号：")
            if num == "1":
                ids = list()
                for data in JD().get_goods_ids():
                    for id in data:
                        ids.append(id)
                print("所有商品信息如下：")
                for good in JD().get_goods_id_name():
                    print(good)
                while True:
                    good_id = input("请输入要删除的商品编号：")
                    try:
                        if int(good_id) in ids:
                            JD().delete_single_good(good_id)
                            break
                        else:
                            print("您输入的商品编号不存在，请重新输入：")
                    except Exception as e:
                        print("非法输入", e)
    
            elif num == "2":
                temps, count = JD().select_all_goods()
                print("一共有%s种商品信息" % count)
                for temp in temps:
                    print(temp)
                num = input("1.点错了 2.继续删除：")
                if num == "1":
                    break
                elif num == "2":
                    s = input("数据删除后将无法恢复，确认请输入y：").lower()
                    if s == "y":
                        JD().delete_all_goods()
                else:
                    print("输入错误，操作取消中....")
                    time.sleep(1)
                    break
            elif num == "3":
                break
            else:
                print("输入错误，请重新输入！")
    
    
    def main():
        """主功能菜单"""
        while True:
            Menu.print_main_menu()
            num = input("请选择功能序号：")
            if num == "1":
                select_main()
            elif num == "2":
                add_main()
            elif num == "3":
                update_main()
            elif num == "4":
                delete_main()
            elif num == "5":
                JD().close_database()
                print("程序正在退出，请稍候....")
                time.sleep(2)
                break
            else:
                print("您的输入不正确，请重新输入！")
    
    
    if __name__ == '__main__':
        main()

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70]: /images/20220307/bd45f8a2093f4bbcba2add56d806c96d.png