python 操作mysql数据库之模拟购物系统登录及购物，,python 操作m

python 操作mysql数据库之模拟购物系统登录及购物，,python 操作m

python 操作mysql数据库之模拟购物系统登录及购物，功能包含普通用户、管理员登录，查看商品、购买商品、添加商品，用户充值等。

mysql 数据库shop 表结构创建如下：

create TABLE userinfo (u_id int(10) not null auto_increment,u_name varchar(35),u_passwd varchar(35),money decimal(10,2),role int(5),primary key(u_id))CHARACTER set  utf8 COLLATE utf8_general_ci;CREATE TABLE product(p_id int(10) NOT NULL auto_increment,p_name VARCHAR(35),p_price DECIMAL(10,2),PRIMARY KEY (p_id))CHARACTER SET utf8  COLLATE utf8_general_ci;create table carts(c_id int(10) not null auto_increment,u_id int(10) NOT null,p_id int(10) NOT null,PRIMARY key (c_id))CHARACTER SET utf8 COLLATE utf8_general_ci;

初始化插入的数据如下：

insert into userinfo (u_name,u_passwd,money,role)VALUES(‘sp1‘,‘123456‘,100000.55,1);insert into userinfo (u_name,u_passwd,money,role)VALUES(‘sp3‘,‘111222‘,100000.55,1);insert into userinfo (u_name,u_passwd,money,role)VALUES(‘sp3‘,‘123456‘,100000.55,1);insert into product(p_name,p_price) values(‘MI‘,1999.00);insert into product(p_name,p_price) values(‘MAC‘,1999.00);insert into product(p_name,p_price) values(‘iphoneX‘,1999.00);insert into product(p_name,p_price) values(‘VIVO‘,1999.00);

项目结构如下：

confsetting.py 为配置文件，存放系统常量：

HOST = ‘172.16.2.163‘USER = ‘root‘PASSWD = ‘123456‘DB_NAME = ‘shop‘

lib目录下存放系统功能和常用函数：

mysql.py内容：

def op_mysql(host,user,passwd,db,sql,charset=‘utf8‘,port=3306):    "操作数据库"    import pymysql    from pymysql.cursors import DictCursor    coon = pymysql.connect(host=host,user=user,passwd=passwd,db=db,charset=charset,port=port)    cur = coon.cursor(DictCursor)  # 定义一个游标，返回一个字典类型    cur.execute(sql)    if sql.strip().startswith(‘select‘):        res = cur.fetchall()    else:        coon.commit()        res = ‘ok‘    cur.close()    coon.close()    return res

action.py内容：

from conf.confsetting import HOST,USER,PASSWD,DB_NAMEfrom lib.mysql import op_mysqldef login(u_name, u_pass):    "用户登录"    name = str(u_name)    passwd = str(u_pass)    res = None    if not name or not passwd:        print("error，用户名或密码不能为空！")    else:        my_sql = "select u.u_id,u.u_name,u.u_passwd,u.money,u.role from userinfo u where u.u_name=‘%s‘;" % name        user_info = op_mysql(host=HOST, user=USER, passwd=PASSWD, db=DB_NAME, sql=my_sql)        if len(user_info) == 0:            print(‘error,用户名不存在‘)        else:            for u in user_info:  # 可能存在同名                if u[‘u_passwd‘] == passwd:                    print(‘用户：{u} 登录成功!‘.format(u=u))                    res = u            if not res:                print(‘error,密码输入错误‘)    return resdef get_goods():    "查询所有商品"    my_sql = "select p.* from product p;"    goods_info = op_mysql(host=HOST, user=USER, passwd=PASSWD, db=DB_NAME, sql=my_sql)    print(‘success，查询所有商品成功,商品信息：‘, goods_info)    return goods_infodef bug_goods(u_info, g_name):    "用户购买商品"    res =None    g_name = str(g_name)    if not g_name :        print(‘购买商品名称不能为空！‘)    else:        goods_info = get_goods()        for i in range(len(goods_info)):            if goods_info[i][‘p_name‘] == g_name:                if u_info[‘money‘] < goods_info[i][‘p_price‘]:                    res = ‘余额不足购买商品失败！‘, goods_info[i]                else:                    new_money = u_info[‘money‘] - goods_info[i][‘p_price‘]                    new_money =round(new_money,2)                    my_sql_1 = "update userinfo set money = ‘%d‘ where u_id = ‘%d‘;"%(new_money,u_info[‘u_id‘])                    my_sql_2 = "INSERT INTO carts (u_id,p_id) VALUES(‘%d‘,‘%d‘);"%(u_info[‘u_id‘],goods_info[i][‘p_id‘])                    op_mysql(host=HOST, user=USER, passwd=PASSWD, db=DB_NAME, sql=my_sql_1)                    op_mysql(host=HOST, user=USER, passwd=PASSWD, db=DB_NAME, sql=my_sql_2)                    res = ‘购买商品成功！‘, goods_info[i]        if not res:            res = ‘error ,购买商品%s不存在‘% g_name    return resdef get_carts_money(u_id):    "查询用户购物车、余额"    my_sql = "select u.money from userinfo u  where u.u_id =‘%d‘;"% u_id    u_money = op_mysql(host=HOST, user=USER, passwd=PASSWD, db=DB_NAME, sql=my_sql)    my_sql = "select p.p_name from userinfo u ,carts c ,product p where u.u_id=‘%d‘and u.u_id =c.u_id and c.p_id= p.p_id; "%u_id    u_cart = op_mysql(host=HOST, user=USER, passwd=PASSWD, db=DB_NAME, sql=my_sql)    return "账户余额：{m} 购物车：{c}".format(m =u_money,c =u_cart)def add_goods(g_name, g_price):    "添加商品"    if not g_name:        return ‘error,添加商品失败，商品名称必须输入‘    price = round(float(g_price), 2)    if price <= 0:        return ‘error,添加商品失败，商品价格须大于0‘    my_sql = "insert into product(p_name,p_price) values(‘%s‘,‘%s‘);"%(g_name,g_price)    op_mysql(host=HOST, user=USER, passwd=PASSWD, db=DB_NAME, sql=my_sql)    return ‘success,添加商品：%s成功‘% g_namedef recharge(u_id, amount):    "输入用户ID给用户充值"    u_id = int(u_id)    u_amount = round(float(amount), 2)    if u_amount <= 0:        return ‘error,充值金额须大于0‘    my_sql = "select u.u_name,u.money from userinfo u where u.u_id =‘%d‘;" % u_id    user_info = op_mysql(host=HOST, user=USER, passwd=PASSWD, db=DB_NAME, sql=my_sql)    if len(user_info) == 0:        return ‘用户id不存在‘    new_money = user_info[0][‘money‘] + u_amount    my_sql = "update userinfo set money = ‘%d‘ where u_id = ‘%d‘;"%(new_money,u_id)    op_mysql(host=HOST, user=USER, passwd=PASSWD, db=DB_NAME, sql=my_sql)    return ‘success,给用户:%s 充值%s 元 成功‘% (user_info[0][‘u_name‘],u_amount)

menu.py 内容：

from lib.action import get_goods,bug_goods,get_carts_money,add_goods,rechargedef role_1(user):    "普通用户菜单"    choice = input(‘请输入你的选择：1：查看所有商品 2：购买商品 3：查看购物车和余额 4:退出\n‘).strip()    if choice == ‘1‘:        print(get_goods())    elif choice == ‘2‘:        goods_name = input("请输入要购买商品名称:\n").strip()        print(bug_goods(user, goods_name))    elif choice == ‘3‘:        print(get_carts_money(user[‘u_id‘]))    elif choice == ‘4‘:        exit(‘退出程序‘)    role_1(user)def role_2(user):    "管理员菜单"    choice = input(‘请输入你的选择：1：添加商品 2：给用户充值 3：退出\n‘).strip()    if choice == ‘1‘:        goods_name = input("请输入你要添加的商品名称：\n").strip()        goods_price = input("请输入添加商品价格：\n").strip()        print(add_goods(goods_name, goods_price))    elif choice == ‘2‘:        u_id = input("请输入你要充值的用户id：\n").strip()        amount = input("请输入充值金额：\n").strip()        print(recharge(u_id, amount))    elif choice == ‘3‘:        exit(‘退出程序‘)    role_2(user)

bin 目录下存放启动文件：

start.py内容：

import os,syscur_path = os.path.abspath(__file__)base_path = os.path.dirname(os.path.dirname(cur_path))sys.path.insert(0,base_path)from lib.action import loginfrom lib.menu import role_1,role_2uName = input("请输入登录用户名：\n").strip()uPass = input("请输入登录密码：\n").strip()user = login(uName, uPass)if user:  # 用户登录成功    # 普通用户:    if user[‘role‘] == 1:        role_1(user)    # 管理员用户：    elif user[‘role‘] == 2:        role_2(user)    else:        print("用户角色不存在")else:    print("用户尚未登录")

运行结果如下：

python 操作mysql数据库之模拟购物系统登录及购物