python mysql实例

商品数据增删改查

1.进行用户注册登录
2 连接数据库
3. 建立游标
4. 对数据库进行操作
5. 关闭游标
6. 关闭数据库连接

from pymysql import connect


class JD(object):
    user_info = {}
    # 注册
    def register(self):
        user_name = input("请输入用户名：")
        password = input("请输入密码：")
        if user_name in self.user_info.keys():
            print("该用户已注册，请登录")
            self.login()
        else:
            self.user_info[user_name] = password
            print("%s注册成功" % user_name)
            full_info = user_name + "" + password
            fp = open('info.txt', 'a+')
            fp.write(full_info + "\n")
            fp.close()
            x = input("是否使用新用户名登陆？登陆输入1，退出输入q或者Q")
            if x == "1":
                self.login()
            else:
                print("退出，谢谢使用")
                return

    # 登录
    def login(self):
        username = input("请输入用户名：")
        password = input("请输入密码：")
        if username in self.user_info.keys():
            if password == self.user_info[username]:
                print("登陆成功")
                self.run()
            else:
                print("密码输入错误")

    # 退出
    @staticmethod
    def exitp():
        print("退出程序")
        return

        # 初始化

    def __init__(self):

        # 创建connection对象
        self.conn = connect(host='localhost', port=3306, user='root', password='mysql', database='jing_dong',charset='utf8')
        # 获取cursor对象
        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 find_all_goods(self):
        # 显示所有商品
        sql = "select * from goods;"
        self.execute_sql(sql)

    def find_goods_cate(self):
        sql = "select name from goods_cates;"
        self.execute_sql(sql)

    def find_goods_brands(self):
        sql = "select name from goods_brands;"
        self.execute_sql(sql)

    @staticmethod
    def print_menu():
        print("查询所有商品信息请按1")
        print("查询商品分类信息请按2")
        print("查询商品商标信息请按3")
        #  print("查询所有商品信息请按4")
        return input("请输入要操作的编号：")

    def run(self):
        while True:
            opt = self.print_menu()
            if opt == "1":
                self.find_all_goods()
            elif opt == "2":
                self.find_goods_cate()
            elif opt == "3":
                self.find_goods_brands()
            else:
                print("输入有误，请重新输入")


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


if __name__ == "__main__":
    main()