纠正格式——import pymysql
import datetime


#
def print_info():
print(“" * 30)
print(" 线上图书商城系统 “)
print(”-" * 30)
print(" 根据操作前数字进行使用 “)
print(”” * 30)
print(" ——用户管理—— “)
print(” -101.用户登录")
print(" -102.用户注册")
print(" -103.用户登出")
print(" -104.用户行为分析")
print(“" * 30)
print(" ——图书管理—— “)
print(” -201.图书逐个录入")
print(" -202.图书批量录入")
print(" -203.图书修改")
print(" -204.图书查询")
print(" -205.图书筛选")
print("” * 30)
print(" ——物车管理—— “)
print(” -301.查询购物车")
print(" -302.加入购物车")
print(" -303.修改购物车")
print(" -304.删改购物车")
print(“" * 30)
print(" ——订单管理—— “)
print(” -401.创建订单")
print(" -402.订单查询")
print("” * 30)
print(" ——图书推荐—— “)
print(” -501.热销图书推荐")
print(" -502.主页推荐")
print(“*” * 30)
print(" ——图书管理—— “)
print(” -601.图书库存报表")
print(" -602.主图书销售报表")
print(" -603.退出系统")
print(“-” * 30)

operation=int(input(“你想进行的操作是：”))
return operation

#
def getConn():
conn=pymysql.connect(host=“127.0.0.1”,port=3306,user=“root”,password=“s4425m4612”,database=“book_manage”)
return conn

def isLogin():
if currentUser.getUid()==‘-1’:
print(‘用户未登录系统，请先登录!’)
return False
else:
return True

def getAdmin():
conn=getConn()
cursor=conn.cursor()
querysql=‘select * from user where user_type=0’

cursor.execute(querysql)
if cursor.rowcount>0:
print(‘成功登录:’,username)
result=cursor.fetchone()
print(‘登录用户信息:’, result)
admin.setUid(result[0])
admin.setUname(result[1])
admin.setPassword(result[2])
admin.setGender(result[3])
admin.setAge(result[4])
admin.setAddress(result[5])
admin.setUserType(result[6])
admin.setBalance(result[7])
print(‘admin info:’,admin.getUid,admin.getUname,admin.getPassword,admin.getGender,admin.getAge,admin.getAddress,admin.getUserType,admin.getBalance)
else:
print(‘无法在数据库中加载管理员信息’)

class User(object):
uid=‘-1’
uname=‘test’
password=‘123456’
gender=‘male’
age = 0
address=‘nanjing’
userType=1
balance=1000

def initial(this):
uid=‘-1’
uname=‘test’
password=‘123456’
gender=‘male’
age = 0
address=‘nanjing’
userType=1
balance=1000

def setUid(this,uid):
this.uid=uid
def getUid(this):
return this.uid

def setUname(this,uname):
this.uname=uname
def getUname(this):
return this.uname

def setPassword(this,password):
this.password=password
def getPassword(this):
return this.password

def setGender(this,gender):
this.gender=gender
def getGender(this):
return this.gender

def setAge(this,age):
this.age=age
def getAge(this):
return this.age

def setAddress(this,address):
this.address=address
def getAddress(this):
return this.address

def setUserType(this,usertype):
this.usertype=usertype
def getUserType(this):
return this.userType

def setBalance(this,balance):
this.balance=balance
def getBalance(this):
return this.balance
#用户管理******************

def userRegister():
print(‘欢迎注册在线图书销售系统’)
uid = input(‘你的uid:’)
uname = input(‘用户姓名:’)
password = input(‘用户密码:’)
gender = input(‘用户性别:’)
age = int(input(‘用户年龄:’))
address = input(‘用户地址:’)
userType = int(input(‘u user type:’))
balance = float(input(‘u balance:’))

currentUser.setUid(uid)
currentUser.setUname(uname)
currentUser.setPassword(password)
currentUser.setGender(gender)
currentUser.setAge(age)
currentUser.setAddress(address)
currentUser.setUserType(userType)
currentUser.setBalance(balance)
print(‘成功注册:’,uname)

conn=getConn()
cursor = conn.cursor()
insertsql = ‘insert into users(uname ,password ,gender,age,address) values(%s,%s,%s,%s,%s)’
cursor.execute(insertsql,(uname,password,gender,age,address))
conn.commit();

def userLogin():
print(‘–欢迎进入用户登录系统–’)
username = input(‘请输入用户名:’)
password = input(‘请输入密码:’)

conn=getConn()
cursor = conn.cursor()
querysql = ‘select * from users where uname=%s and password=%s’
cursor.execute(querysql,(username, password))
if cursor.rowcount>0:
print(‘成功登录:’,username)
result = cursor.fetchone()
print(‘登录用户信息:’, result)
currentUser.setUid(result[0])
currentUser.setUname(result[1])
currentUser.setPassword(result[2])
currentUser.setGender(result[3])
currentUser.setAge(result[4])
currentUser.setAddress(result[5])
currentUser.setUserType(result[6])
currentUser.setBalance(result[7])

insertsql = ‘insert into user_loginfo(uid, uname, logintime) values(%s,%s,%s)’
cursor.execute(insertsql, (result[0], result[1], datetime.datetime.now()))
conn.commit()
else:
print(username,‘未注册到系统，请先注册!’)

def userLogout():
username = currentUser.getUname()
conn=getConn()
cursor=conn.cursor()
updatesql = ‘update user_loginfo set logouttime=%s where uid = %s’
cursor.execute(updatesql,(datetime.datetime.now(), currentUser.getUid()))
conn.commit()
currentUser.initial()
print(username,’ 退出登录’)

def userLoginReport():
if isLogin()==False:
return
conn=getConn()
cursor = conn.cursor()
# 当日有多少登录用户、哪个用户登录次数最多
stasql01 = ‘select count(1) as day_num from user_loginfo where to_days(logintime) = to_days(now())’
cursor.execute(stasql01)
daylycount = cursor.fetchone()[0]

stasql011 = ‘select uname, count(1) as num from user_loginfo where to_days(logintime) = to_days(now()) group by uname order by num desc’
cursor.execute(stasql011)
result = cursor.fetchone()
daylyTopUser = result[0]
daylyTopCount = result[1]
print(‘今日登录 ‘, daylycount,’ 用户登录和用户 ‘, daylyTopUser,’ 登录最多用户: ‘,daylyTopCount)

# 最近7天有多少登录用户、哪个用户登录次数最多
stasql02 = ‘select count(1) as week_num from user_loginfo where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(logintime)’
cursor.execute(stasql02)
weeklycount = cursor.fetchone()[0]

stasql022 = ‘select uname, count(1) as num from user_loginfo where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(logintime) group by uname order by num desc’
cursor.execute(stasql022)
result = cursor.fetchone()
weeklyTopUser = result[0]
weeklyTopCount = result[1]
print(‘最近七天 ‘, weeklycount,’ 用户登录和用户 ‘, weeklyTopUser,’ 登录最多用户: ‘,weeklyTopCount)

# 最近30天有多少登录用户、not哪个用户登录次数最多
stasql03 = ‘select count(1) as month_num from user_loginfo where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(logintime)’
cursor.execute(stasql03)
monthlycount = cursor.fetchone()[0]

stasql033 = ‘select uname, count(1) as num from user_loginfo where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(logintime) group by uname order by num desc’
cursor.execute(stasql033)
result = cursor.fetchone()
monthlyTopUser = result[0]
monthlyTopCount = result[1]
print(‘最近三十天 ‘, monthlycount,’ 用户登录和用户 ‘, monthlyTopUser,’ 登录最多用户: ‘,monthlyTopCount)



#图书管理************
def bookRegister():
print(“欢迎注册(仅供管理员用户使用)”)
adminName=input(“请输入管理员姓名”)
adminpasswd=input(“请输入管理员密码”)

if adminName!=admin.getUname() or adminpasswd!=admin.getPasswd():
print(“管理员名称或密码不正确，请再次输入!”)
else:
bid=input(“请输入序号：”)
bname = input(‘请输入书名：’)
category = input(‘请输入类型：’)
description = input(‘请输入描述：’)
price = input(‘请输入价格：’)
stockNnum = input(“请输入数量:”)

conn=getConn()
cursor = conn.cursor()
insertsql = ‘insert into books(bid,bname,category,description,price,stockNnum) values(%s,%s,%s,%s,%s,%s)’
cursor.execute(insertsql,(bid,bname,category,description,price,stockNnum))
conn.commit()
print(‘添加成功！’)

def bookModify():
print(‘欢迎修改信息（仅限管理员用户）)’)
adminName=input(‘pls input admin name:’)
adminpasswd=input(‘pls input admin passwd:’)

if adminName!=admin.getUname() or adminpasswd!=admin.getPasswd():
print(“管理员名称或密码不正确，请再次输入!”)
else:
orbidname=input(“请输入序号：”)
newbname = input(‘请输入书名：’)
newcategory = input(‘请输入类型：’)
newdescription = input(‘请输入描述：’)
newprice = input(‘请输入价格：’)
newstockNnum = input(“请输入数量:”)

conn=getConn()
cursor = conn.cursor()
insertsql = ‘update books set bname=%s,category=%s,description=%s,price=%s,stockNnum=%s where bid=%s or bname=%s’
cursor.execute(updatesql,(newbname,newcategory,newdescription,newprice,newstockNnum,orbidname,orbidname))
conn.commit()
print(‘修改成功！’)

def queryAllBook():
conn=getConn()
cursor = conn.cursor()
querysql=‘sleect * from books’
cursor.execute(querysql)
results=cursor.fetchll()
for item in results:
print(item)

def searchBook():
conn=getConn()
cursor = conn.cursor()
print(“根据名称或名称的一部分搜索书籍:”)
bookname=input(“请输入书籍名称或名称的一部分”)
booknamergs=“%”+bookname+“%”
querysql=“select * from books where bname like ‘%s’”%booknamergs
cursor.execute(querysql)
results=cursor.fetchll()
print(results)

def filterBook():
conn=getConn()
cursor = conn.cursor()
print(‘根据类别或最高/最低价格筛选图书:’)
bookcategory=input(‘请输入书籍类别:’)
minprice=float(input(’ 最低书本价格:’))
maxprice=float(input( ‘最高书本价格:’))
querysql=“seleet * from bookis where category =%s and pricce between %s and %s”
cursor.execute(querysql,(bookcategory, minprice,maxprice))
results=cursor.fetchll()
print(results)


#购物车
def queryshopcart():
conn=getConn()
cursor=conn.cursor()
querysql=‘select * from shop_cart order by uid,bid’
cursor.execute(querysql)
results=cursor.fetchll()
print(results)

def addtoshopcart():
if isLogin()==False:
return
bid=input(‘您要将哪些书本id添加到购物车:’)
count=int(input(‘你想买多少本书:’))

conn=getConn()
cursor=conn.cursor()
querysql=‘select bname,price from books where bid=%s’
cursor.execute(querysql,(bid))
results=cursor.fetchll()
bookname=result[0]
bookprice=result[1]
insertsql=‘insert into shop_cart(uid,uname,bid,bname,purchase_num,price) value(%s,%s,%s,%s,%s,%s)’
cursor.execute(insertsql,(currentUser.getUid(),currentUser.getUname(),bid,bookname,count,bookprice))
conn.commit()

def shopcartModify():
if isLogin()==False:
return
print(‘欢迎修改购物车商品信息!’)
oribid=input(‘您要修改的出价项:’)
newpurchaseNumint (input (‘新采购数量:’))

conn=getConn()
cursor=conn.cursor()

updatesql=‘update shop_cart set purchase_num=%s where uid=%s and bid=%s’
cursor.execute(updatesql,(newpurchaseNum,currentUser.getUid(),oribid))
conn.commit()

def shopcartdelete():
if isLogin()==False:
return
print(‘欢迎删除购物车商品信息!’)
oribid= input(‘您要删除的出价项:’)
conn=getConn()
cursor=conn.cursor()
updatesql=‘delete from shop_cart where uid=%s and bid=%s’
cursor.execute(updatesql,(currentUser.getUid(),oribid))
conn.commit()


#订单管理
def queryorder():
conn=getConn()
cursor=conn.cursor()

querysql=‘select * from order_book order by uid,bid’
cursor.execute(querysql)
results=cursor.fetchll()
print(results)

oid=1
def ordercreate():
if isLogin()==False:
return
bid=input(‘您要从购物车中支付哪些书籍的id:’)

conn=getConn()
cursor= conn.cursor()
querysql=‘select uid,uname,bname,purchase_num,price from shop_cart where uid=%s and bid=%s’
cursor.execute(querysql,(currentUser.getUid(),bid))
result=cursor.fetchone()
uid=result[0]
uname=result[1]
bname=result[2]
ordernum=result[3]
global oid
totalprice=ordernum * result[4]
if oid!=1:
oid +=1

insertsql=‘inser into order_book(oid,uid,uname,bid,bname,o_num,total_price)valuse(%s,%s,%s,%s,%s,%s,%s)’
cursor.execute(insertsql,(str(oid),uid,uname,bid,ordernum,totalprice))
conn.commit()

#图书推荐
def recomhotbooks():
print(’——推荐热门书籍——’)
conn=getConn()
cursor=conn.cursor()
querysql=‘select bid,bname,order(o_num) numfrom order_book group by bid,bname order by num’
cursor.execute(querysql)
results=cursor.fetchal1()
print (results)

def recombasedonhistory():
print(’——基于你历史购买记录的推荐书籍——’)
conn=getConn()
cursor= conn.cursor()
querysql=‘select distinct bid from order_book where uid=%s’
cursor.execute(querysql,(currentUser.getUid()))
results=cursor.fetchal1()
bidstr=‘’
for item in result:
bidstr+=item[0]+‘,’
bidstr=bidstr[0:len(bidstr)-2]
querybasedoncategory=‘’‘select * from books b where b.category in (select distinct category from books t where t.bid in (%s))’‘’
cursor.execute(querybasedoncategory,(bidstr))
results=cursor.fetchll()
print(results)

#图书管理******
def reportforstock():
print(‘——图书库存报表——’)
conn=getConn()
cursor=conn.courser()
querysql=‘select * from books order by stock_num’
cursor.execute(querysql)
results=cursor. fetchal1()
print(results)

def reportforsaleinfo():
print(‘——主图书销售报表——’)
conn=getConn()
cursor.cursor()
querysql=‘select uid,uname,count(o_num) num,count(total_ price) totalprice from order_book group by uid,unameorder by num’
cursor.execute(querysql)
print(results)

#**************************************************************************
def main():
admin=User()
getAdmin()
currentUser=User()

while True:
operation=print_info()
if operation== 101:
userRegister()
elif operation== 102:
userLogin()
elif operation== 103:
userLogout()
elif operation== 104:
userLoginReport()
elif operation== 201:
bookRegister()
elif operation== 202:
bookModify()
elif operation== 203:
queryAllBook()
elif operation== 204:
searchBook()
elif operation== 205:
filterBook()
elif operation== 301:
queryshopcart()
elif operation== 302:
addtoshopcart()
elif operation== 303:
shopcartModify()
elif operation== 304:
shopcartdelete()
elif operation== 401:
queryorder()
elif operation== 402:
ordercreate()
elif operation== 501:
recomhotbooks()
elif operation== 502:
recombasedonhistory()
elif operation== 601:
reportforstock()
elif operation== 602:
reportforsaleinfo()
elif operation== 603:
print(“退出系统!”)
break
else:
print(“请输入正确的数值！”)

if name==“main”:
main()