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

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 )")

表格创建完成就是这样子的

依次是订单明细表
订单表
产品表格,这个表格的产品需要手动添加
用户表