SQL入门教程（七）：MySQL与Python交互实例教程

数据库的增删改查

from pymysql import connect
import sys


class JD(object):
    def __init__(self):
        # 创建链接
        self.conn = connect(host="localhost", port=3306, user='root', password="1234", database='jing_dong',
                            charset='utf8')
        # 获取Cursoe对象
        self.cursor = self.conn.cursor()

    def __del__(self):
        # 关闭对象链接
        self.cursor.close()
        self.conn.close()

    def execute_sql(self, 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 brand_cates;'
        self.execute_sql(sql)

    def add_brands(self):
        item = input("输入新商品分类名称")
        sql = """insert into goods_cates (name) values ("%s");""" % item
        self.cursor.execute(sql)
        self.conn.commit()

    def get_info_by_name(self):
        find_name = input("请输入待查询商品名称")
        sql = "select * from goods_cates where name=%s;"
        self.cursor.execute(sql, [find_name])
        print("--------------------------")
        print(self.cursor.fetchall())
        print("---------------------------")

    def exit(self):
        sys.exit()

    def print_menu(self):
        print("---------京东----------")
        print("1：所有的商品")
        print("2：所有的商品分类")
        print("3：所有的商品品牌分类")
        print("4：增加一个新商品分类")
        print("5：查询一个新商品分类")
        return input("输入序号进行选择")

    def run(self):
        while True:
            num = self.print_menu()
            if num == "1":
                self.show_all_items()
            elif num == "2":
                self.show_cates()
            elif num == '3':
                self.show_brands()
            elif num == '4':
                self.add_brands()
            elif num == "5":
                self.get_info_by_name()
            elif num == "q":
                self.exit()
            else:
                print("输入有误，请重新输入")


def main():
    jd = JD()
    jd.run()


if __name__ == '__main__':
    main()