python-管理MySQL数据库作业

作业1

import pymysql

class The_book:
    def __init__(self,database_name):
        self.database_name = database_name

    def conn_db(self):
        #  连接数据库
        return pymysql.connect('127.0.0.1', 'root', 'root', self.database_name, charset='utf8')

    def create_database_table(self,sql):
        #  创建库,创建表
        cur.execute(sql)

    def insert_book(self):
        #  图书入库
        id = int(input("请输入id:"))
        name = input("请输入图书名称:")
        num = int(input("请输入图书库存:"))
        sql = "insert into book values(%d,'%s',%d)" % (id,name,num)
        count = cur.execute(sql)
        conn.commit()
        if count != 0:
            print("添加了",count,"条数据")
        else:
            print("添加失败!!!")

    def select_book(self):
        #  查找书籍
        name = input("请输入要查找的书名称:")
        sql = "select * from book where name='%s'" % name
        cur.execute(sql)
        list = cur.fetchall()
        if list:
            print("id\t名称\t库存")
            for i in list:
                for k in i:
                    print(k,end="\t")
                print()
        else:
            print("您查找的书籍不存在")

    def update_num(self):
        #  借阅书籍
        name = input("请输入要借阅的书名:")
        num = int(input("请输入要借几本书:"))
        sql = "select num from book where name='%s'" % name
        cur.execute(sql)
        list = cur.fetchone()
        if list:
            if num <= list[0]:
                sql = "update book set num=num-%d where name='%s'" % (num, name)
                count = cur.execute(sql)
            else:
                print("库存不足,借书失败,图书剩余",list[0],"本")
                return
            conn.commit()
            if count != 0:
                print("已成功借书",num,"本\n剩余",list[0]-num,"本")
        else:
            print("找不到该图书")

book = The_book('test3')
conn = book.conn_db()
cur = conn.cursor()
# sql = """create table book(id int not null,name varchar(20),num int)default charset=utf8"""
# book.create_database_table(sql)

while True:
    choose = int(input("请输入:\n1:图书入库\n2:查找书籍\n3:借阅书籍\n"))
    if choose == 1:
        book.insert_book()
    elif choose == 2:
        book.select_book()
    elif choose == 3:
        book.update_num()
    else:
        print("输入错误!!")
        continue
    choose = input("输入'y'退出系统,其他继续:")
    if choose == 'y':
        break

conn.close()
cur.close()

作业2

import pymysql

class Shopping:

    def __init__(self,database_name):
        self.database_name = database_name

    def conn_db(self):
        #  连接数据库
        return pymysql.connect('127.0.0.1','root','root',self.database_name,charset='utf8')

    def create_database_table(self,sql):
        #  创建库,创建表
        cur.execute(sql)

    def insert_shopping(self):
        name = input("请输入商品名称:")
        price = float(input("请输入商品价格:"))
        num = int(input("请输入商品数量:"))
        sql = "insert into shopping values('%s',%f,%d)" % (name, price, num)
        count = cur.execute(sql)
        conn.commit()
        if count != 0:
            print("您添加了", count, "条数据")
        else:
            print("添加失败!!!")

    def select_table(self):
        sql = "select *,price*num 小计 from shopping"
        cur.execute(sql)
        print("商品名称\t价格\t数量\t小计")
        for i in cur.fetchall():
            for k in i:
                print(k,end='\t')
            print()

    def sum_money(self):
        sql = "select sum(price*num) from shopping"
        cur.execute(sql)
        print("需要付的总金额为:", cur.fetchone()[0])

    def del_shopping(self):
        name = input("请输入要删除的商品名:")
        sql = "delete from shopping where name='%s'" % name
        cur.execute(sql)
        conn.commit()
        if cur.rowcount!=0:
            print("删除成功")
        else:
            print("删除失败,请查看商品名称是否正确")


shopping = Shopping('test3')
conn = shopping.conn_db()
cur = conn.cursor()
#创建表
# sql = "create table shopping(name varchar(30) not null,price decimal(10,2),num int)default charset='utf8'"
# shopping.create_database_table(sql)

while True:
    choose = int(input("请输入:\n1:添加商品\n2:查询购物车中所有信息\n3:查看需要付的总金额\n4:删除商品\n"))
    if choose == 1:
        shopping.insert_shopping()
    elif choose == 2:
        shopping.select_table()
    elif choose == 3:
        shopping.sum_money()
    elif choose == 4:
        shopping.del_shopping()
    else:
        print("输入错误!!")
        continue
    choose = input("输入'y'退出系统,其他继续:")
    if choose == 'y':
        break

conn.close()
cur.close()