MySQL数据库和python综合练习(购物车系统)

亦凉 2021-10-31 16:14 296阅读 0赞

1.设计表:  
用户表(users),商品表(product),订单表(orders),订单明细(orderdetail)  
2.核心业务:  
1.用户登录  
2.查询所有的商品, 显示在控制台上  
3.用户输入编号, 把对应的商品放入购物车中  
4.显示购物车的商品信息  
5.下订单, 就是把购物车中商品向订单表和订单明细中插入对应的数据

代码可以直接使用,只需要修改数据库连接相关几个参数  
mains.py  
主程序:

from mysqlss import MysqlHelper
    
    
    class Shop():
        username = None
    
        def denglu(self):
            global username
            while True:
                username = input('请登陆\n用户名: ')
                if len(my.get_all("select uname from users where uname='%s'" % username)) != 0:
                    print('用户名已存在')
                    continue
                password = input('密码: ')
                break
            # 插入用户表
            my.insert("insert into users(uname, upass) values (%s,%s)", [username, password])
            # 查询商品表并输出商品表
            listss = my.get_all("select * from product")
            for row in listss:
                print(row[0], row[1], row[2])
    
        def shopping_car(self):
            global uid, pid, lists, price, username
            lists = []
            price = 0.00
            pid = 0
            uid = my.get_one("select uid from users where uname='%s'" % username)[0]
            # 订单表插入信息
            bianhao = str(uid) + '20190813'
            my.insert("insert into orders(oid, otime, oprice, uid, ostatus) values(%s,now(),%s,%s,1)",
                      [bianhao, price, uid])
            while True:
                pids = (input('请输入商品编号或者输入其他开始下单:'))
                try:
                    if pids.isdigit:
                        pid = int(pids)
                        # 商品加入购物车
                        lists.append(my.get_all("select * from product where pid=%s" % pid)[0])
                        print('已将%s加入购物车' % my.get_all("select * from product where pid=%s" % pid))
                        # 价格相加
                        price += my.get_one("select price from product where pid=%s" % pid)[0]
                        # 订单明细表插入信息
                        pprice = my.get_one("select price from product where pid=%s" % pid)[0]
                        my.insert("insert into orderdetail(pid, oid, price) values(%s,%s,%s)", [pid, str(uid)+'20190813',
                                                                                                   pprice])
                except Exception as e:
                    print('购物成功')
                    break
            # 更新价格
            my.update("update orders set oprice=%d where uid=%s" % (price, uid))
            # 输出用户
            print(uid)
            # 输出购物车
            print(lists)
    
    
    if __name__ == '__main__':
        shop = Shop()
        # 数据库初始化
        my = MysqlHelper('localhost', 3306, 'testdb', 'root', '123456')
        # 登陆 并写入用户表
        shop.denglu()
        # 添加购物车 添加订单表及订单明细表
        shop.shopping_car()

mysqlss.py子程序  
写了几个操作数据库的方法,在主程序里直接调用这里面的函数,非常方便

import pymysql
    
    
    class MysqlHelper:
        # 初始化连接
        def __init__(self, host, port, db, user, passwd):
            self.host = host
            self.port = port
            self.db = db
            self.user = user
            self.passwd = passwd
    
        # 连接和游标
        def connect(self):
            self.conn = pymysql.connect(host=self.host, port=self.port, db=self.db, user=self.user, passwd=self.passwd)
            self.cursor = self.conn.cursor()
    
        # 关闭对象
        def close(self):
            self.cursor.close()
            self.conn.close()
    
        # 查询单条数据
        def get_one(self, sql, params=[]):
            result = None
            try:
                self.connect()
                self.cursor.execute(sql, params)
                result = self.cursor.fetchone()
                # print(type(result))
                # print("\n Database version : %s \n" % result)
                self.close()
            except Exception as e:
                print(e)
            return result
    
        # 查询多条数据
        def get_all(self, sql, params=[]):
            lists = None
            try:
                self.connect()
                self.cursor.execute(sql, params)
                lists = self.cursor.fetchall()
    
                # for row in lists:
                #     sid = row[0]
                #     sname = row[1]
                #     print(sid, sname, row[2])
    
                self.close()
            except Exception as e:
                print(e)
            return lists
    
        # 添加
        def insert(self, sql, params=[]):
            return self.__edit(sql, params)
    
        # 修改
        def update(self, sql, params=[]):
            return self.__edit(sql, params)
    
        # 删除
        def delect(self, sql, params=[]):
            return self.__edit(sql, params)
    
        def __edit(self, sql, params):
            count = 0
            try:
                self.connect()
                # 执行操作,返回受影响行数
                count = self.cursor.execute(sql, params)
                self.conn.commit()
                self.close()
            except Exception as e:
                print(e)
    
            return count

python中直接创建需求表格:  
create\_table.py

import pymysql
    
    
    class CreateTable():
        def __init__(self, sql):
            self.sql = sql
            db = pymysql.connect("localhost", "root", "123456", "testdb")
            # 使用 cursor() 方法创建一个游标对象 cursor
            cursor = db.cursor()
            # 使用 execute() 方法执行 SQL,如果表存在则删除
            # cursor.execute("drop table if exists EMPLOYEE")
            # 使用预处理语句创建表
            cursor.execute(self.sql)
            print("\n creat table successd ! \n")
            # 关闭数据库连接
            db.close()
    
    
    createtable1 = CreateTable("create table users("
                               "uid int primary key auto_increment,"
                               "uname varchar(10),"
                               "upass varchar(10))")
    
    createtable2 = CreateTable("create table product("
                               "pid int primary key auto_increment,"
                               "pname varchar(10),"
                               "price float)")
    
    createtable3 = CreateTable("create  table orders("
                               "oid varchar(30) primary key ,"
                               "otime datetime,"
                               "oprice float,"
                               "uid int,"
                               "CONSTRAINT fk_user_1 "
                               "FOREIGN KEY(uid) REFERENCES users(uid),"
                               "ostatus int )")
    
    createtable4 = CreateTable("create  table orderdetail("
                               "odid int primary key auto_increment,"
                               "pid int ,"
                               "CONSTRAINT fk_product "
                               "Foreign  key(pid) REFERENCES product(pid),"
                               "oid varchar(30) ,"
                               "CONSTRAINT fk_orders "
                               "Foreign  key(oid) REFERENCES orders(oid),"
                               "price float )")

表格创建完成就是这样子的  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI0MzM4_size_16_color_FFFFFF_t_70]  
依次是订单明细表  
订单表  
产品表格,这个表格的产品需要手动添加  
用户表

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI0MzM4_size_16_color_FFFFFF_t_70]: /images/20211031/dc71e3f76bcb4f07ab76b22cece26767.png