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','',100000.55,1);
insert into userinfo (u_name,u_passwd,money,role)VALUES('sp3','',100000.55,1);
insert into userinfo (u_name,u_passwd,money,role)VALUES('sp3','',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 = ''
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_NAME
from lib.mysql import op_mysql def 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 res def 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_info def 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 res def 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_name def 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,recharge def role_1(user):
"普通用户菜单"
choice = input('请输入你的选择：1：查看所有商品 2：购买商品 3：查看购物车和余额 4:退出\n').strip()
if choice == '':
print(get_goods())
elif choice == '':
goods_name = input("请输入要购买商品名称:\n").strip()
print(bug_goods(user, goods_name))
elif choice == '':
print(get_carts_money(user['u_id']))
elif choice == '':
exit('退出程序')
role_1(user) def role_2(user):
"管理员菜单"
choice = input('请输入你的选择：1：添加商品 2：给用户充值 3：退出\n').strip()
if choice == '':
goods_name = input("请输入你要添加的商品名称：\n").strip()
goods_price = input("请输入添加商品价格：\n").strip()
print(add_goods(goods_name, goods_price))
elif choice == '':
u_id = input("请输入你要充值的用户id：\n").strip()
amount = input("请输入充值金额：\n").strip()
print(recharge(u_id, amount))
elif choice == '':
exit('退出程序')
role_2(user)
bin 目录下存放启动文件：
start.py内容：
import os,sys
cur_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 login
from lib.menu import role_1,role_2 uName = 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("用户尚未登录")
运行结果如下：
