基于上下文管理的Mysql实现数据库相关功能

基于上下文管理实现的数据库相关功能，代码相对比较简洁，还有改进的空间。

# Author: Hong
import pymysql


class JDServer(object):
    def __init__(self, target_db, username, password):
        self.target_db = target_db
        self.username = username
        self.password = password
        self.db_connect = pymysql.connect(
            host='localhost', port=3306, database=self.target_db, user=self.username, passwd=self.password,
            charset='utf8')

    def __del__(self):
        self.db_connect.close()

    def __enter__(self):
        self.cur = self.db_connect.cursor()
        return self.cur

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            self.db_connect.rollback()
            print("输入有误")
        self.cur.close()

    @staticmethod
    def __print_menu():
        print("-------京东商城后台--------")
        print("1. 查询所有商品信息")
        print("2. 查询所有包含商品的分类")
        print("3. 添加商品分类")
        print("4. 修改所有商品价格")
        print("5. 将某类商品的商品类型修改成指定的商品")
        print("6. 根据id查询商品信息")
        print("7. 根据id查询商品信息安全方式")
        print("8. 退出")

    # 打印结果方法
    @staticmethod
    def __show_query_result(result):
        for row in result:
            print(row)

    def excutesql(self, sql, parm=[]):
        with self as cr:
            n = cr.execute(sql, parm)
            print("影响的行数：", n)
            result = cr.fetchall()
        if result:
            self.__show_query_result(result)
        else:
            self.db_connect.commit()

    # 1. 查询所有商品信息
    def __fetch_all_info(self):
        sql_str = "select * from goods "
        self.excutesql(sql_str)

    # 2. 查询所有包含商品的分类
    def __fetch_cate_of_goods(self):
            sql_str = '''select distinct gc.name from goods_cates as gc
            inner join goods as g on g.cate_id=gc.id'''
            self.excutesql(sql_str)

    # 3. 添加商品分类(输入多个空格问题还没解决)
    def __add_new_cate(self):
        # 设置AUTO_INCREMENT=0防止ID中间有空隙
        sql_str1 = '''alter table goods_cates AUTO_INCREMENT=0
        '''
        new_cate = input("请输入一个新商品分类：")
        if new_cate == " ":
            print("输入为空")
            self.db_connect.rollback()
            self.close()
            return
        sql_str = '''insert into goods_cates(name) values("%s")''' % new_cate
        self.excutesql(sql_str)

    # 4.修改所有商品价格（修改）
    def __update_price(self):
        with self as cr:
            input_str = input("您要修改的价格数量：")
            sql_str = '''update goods set price=price+%s '''
            self.excutesql(sql_str,input_str)

    # 5. 将某类商品的商品类型修改成指定的商品（修改）
    def __update_cate(self):
        with self as cr:
            input_str1 = input("输入您要修改的商品类型：")
            input_str2 = input("输入您要修改的商品：")
            input_str3 = '%'+input_str2+'%'
            sql_str = '''update goods set cate_id = (select id from goods_cates where name =%s) 
            where name like %s;'''
            self.excutesql(sql_str, [input_str1, input_str3])

    # 6. 根据id查询商品信息
    def __fetch_info_with_id(self):
        with self as cr:
            s_id = input("输入您要查询的商品ID：")
            sql_str = '''select * from goods where id=%s ''' %(s_id)
            self.excutesql(sql_str)

    # 7. 根据id查询商品信息安全方式
    def __fetch_info_with_id_safe(self):
        with self as cr:
            s_id = input("输入您要查询的商品ID：")
            sql_str = '''select * from goods where id=%s '''
            self.excutesql(sql_str, s_id)

    # 服务器运行方法,实现主体逻辑
    def run(self):
        while True:
            self.__print_menu()
            i = input("请输入您要进行的操作：")
            if i == '1':
                self.__fetch_all_info()
            elif i == '2':
                self.__fetch_cate_of_goods()
            elif i == '3':
                self.__add_new_cate()
            elif i == '4':
                self.__update_price()
            elif i == '5':
                self.__update_cate()
            elif i == '6':
                self.__fetch_info_with_id()
            elif i == '7':
                self.__fetch_info_with_id_safe()
            elif i == '8':
                break
            else:
                print("您的输入有误，请重新输入")


jdserver = JDServer("jing_dong","root","000")
jdserver.run()