def Recommend_db(userid, page, keyword, search):
recommend_list = {'error': 0, 'data': [], 'max_page': 1}
count = {'error': 1, 'data': 0}
try:
# if(not search):
if (keyword == 0):
if (page == 1):
sqlstr = "select count(*) from goodlist left join recommend_list on goodlist.id=recommend_list.goodid left join collect on goodlist.id=collect.goodid and collect.userid='%d' where recommend_list.userid='%d' order by recommend_list.sum desc" % (
userid, userid)
count = sql.select(sqlstr)
sqlstr = "select goodlist.id,title,url,img_url,price,keyword,page,collect_num,comment_num,click_num,collect.userid userid from goodlist left join recommend_list on goodlist.id=recommend_list.goodid left join collect on goodlist.id=collect.goodid and collect.userid='%d' where recommend_list.userid='%d' order by recommend_list.sum desc limit %d,16" % (
userid, userid, (page - 1) * 16)
else:
if (page == 1):
sqlstr = "select count(*) from goodlist left join recommend_list on goodlist.id=recommend_list.goodid left join collect on goodlist.id=collect.goodid and collect.userid='%d' where recommend_list.userid='%d' and keyword='%s' order by recommend_list.sum desc" % (
userid, userid, keyword)
count = sql.select(sqlstr)
sqlstr = "select goodlist.id,title,url,img_url,price,keyword,page,collect_num,comment_num,click_num,collect.userid userid from goodlist left join recommend_list on goodlist.id=recommend_list.goodid left join collect on goodlist.id=collect.goodid and collect.userid='%d' where recommend_list.userid='%d' and keyword='%s' order by recommend_list.sum desc limit %d,16" % (
userid, userid, keyword, (page - 1) * 16)
list = sql.select(sqlstr)
recommend_list['error'] = list['error']
recommend_list['data'] = list['data']
if (count['error'] == 0):
count = count['data'][0]['count(*)']
else:
count = 0
recommend_list['max_page'] = int((count - 1) / 16) + 1
print(recommend_list['max_page'])
except Exception as e:
traceback.print_exc()
# print(recommend_list)
return recommend_list
def recommend_tag_good(tag_list, page, keyword):
k = 0
recommend_list = {'error': 0, 'data': [], 'max_page': 1}
count = {'error': 1, 'data': 0}
tagstr = ''
for tag in tag_list:
k = 1
tagstr = tagstr + " tag='" + tag + "' or"
print(tagstr[0:-3])
page = int(page)
if (k == 1):
try:
if (keyword == 0):
if (page == 1):
sqlstr = "select count(*) from recommend_good_tag_count where " + tagstr[
0:
-3] + " group by goodid ORDER BY sum(count/log(count+1)) desc"
count = sql.select(sqlstr)
print('count1', count)
sqlstr = "select goodlist.* from recommend_good_tag_count,goodlist where goodlist.id=recommend_good_tag_count.goodid and (" + tagstr[
0:
-3] + ") group by goodid ORDER BY sum(count/log(count+1)) desc limit %d,16" % (
(page - 1) * 16)
else:
if (page == 1):
sqlstr = "select count(*) from recommend_good_tag_count,goodlist where goodlist.id=recommend_good_tag_count.goodid and (" + tagstr[
0:
-3] + ") and keyword='%s' group by goodid ORDER BY sum(count/log(count+1)) desc" % (
keyword)
count = sql.select(sqlstr)
print('count2', count)
sqlstr = "select goodlist.* from recommend_good_tag_count,goodlist where goodlist.id=recommend_good_tag_count.goodid and (" + tagstr[
0:
-3] + ") and keyword='%s' group by goodid ORDER BY sum(count/log(count+1)) desc limit %d,16" % (
keyword, (page - 1) * 16)
list = sql.select(sqlstr)
recommend_list['error'] = list['error']
recommend_list['data'] = list['data']
print('count', count)
if (count['error'] == 0):
count = count['data'][0]['count(*)']
else:
count = 0
recommend_list['max_page'] = int((count - 1) / 16) + 1
print(recommend_list['max_page'])
except Exception as e:
traceback.print_exc()
# print(recommend_list)
return recommend_list
def login(request):
re = {'error': 0, 'data': ()}
try:
# print(request)
print(request.POST)
username = request.POST.get('username')
pwd = request.POST.get('pwd')
sqlstr = ("select id,name,password from user where name='%s';" %
username)
# print(sqlstr)
user = sql.select(sqlstr)
print(user)
re = user
response = HttpResponse(json.dumps(re))
if (re['error'] == 0):
if (len(user['data']) == 0):
re['error'] = 1
re['data'] = '用户名错误'
response = HttpResponse(json.dumps(re))
elif (user['data'][0]['password'] != pwd):
re['error'] = 1
re['data'] = '密码错误'
response = HttpResponse(json.dumps(re))
else: #登陆成功
response.set_cookie('userid', re['data'][0]['id'])
except Exception as e:
re['error'] = 1
re['data'] = '后台错误'
response = HttpResponse(json.dumps(re))
print(e)
traceback.print_exc()
return response
def get_goodlist_by_freqtag(page, tag):
recommend_list = {'error': 0, 'data': [], 'max_page': 1}
count = {'error': 1, 'data': 0}
try:
if (page == 1):
sqlstr = "select count(*) from recommend_good_tag_count where tag='%s' order by count desc"
count = sql.select(sqlstr)
if (count['error'] == 0):
count = count['data'][0]['count(*)']
else:
count = 0
sqlstr = "select goodlist.* from recommend_good_tag_count,goodlist where goodlist.id=recommend_good_tag_count.goodid and tag='%s' order by count desc limit %d,16)" % (
tag, (page - 1) * 16)
list = sql.select(sqlstr)
except Exception as e:
traceback.print_exc()
recommend_list['data'] = list['data']
recommend_list['max_page'] = int((count - 1) / 16) + 1
return recommend_list
def register(request):
re = {'error': 0, 'data': ()}
try:
# print(request)
print(request.POST)
username = request.POST.get('username')
pwd = request.POST.get('pwd')
sqlstr = ("select id,name,password from user where name='%s';" %
username)
# print(sqlstr)
user = sql.select(sqlstr)
re = user
response = HttpResponse(json.dumps(re))
if (re['error'] == 0):
if (len(user['data']) == 0):
sqlstr = "insert into user(name,password) values('%s','%s')" % (
username, pwd)
re = sql.insert(sqlstr)
response = HttpResponse(json.dumps(re))
if (re['error'] == 0):
sqlstr = (
"select id,name,password from user where name='%s';" %
username)
# print(sqlstr)
re = sql.select(sqlstr)
# obj.set_cookie()
response.set_cookie('userid', re['data'][0]['id'])
else:
re['error'] = 1
re['data'] = '该用户名已存在'
response = HttpResponse(json.dumps(re))
except Exception as e:
re['error'] = 1
re['data'] = '后台错误'
print(e)
traceback.print_exc()
response = HttpResponse(json.dumps(re))
return HttpResponse(json.dumps(re))
def good_tag(good):
sqlstr = "select goodid,tag from recommend_good_tag_count where goodid='%s' and tag not in (select keyword from goodlist where recommend_good_tag_count.goodid=goodlist.id) order by count desc limit 0,3" % (
good['id'])
taglist = sql.select(sqlstr)
if (len(taglist['data']) >= 3):
good.update(
{'tag1': taglist['data'][0]['tag'], 'tag2': taglist['data'][1]['tag'], 'tag3': taglist['data'][2]['tag']})
elif ((len(taglist['data']) == 2)):
good.update({'tag1': taglist['data'][0]['tag'], 'tag2': taglist['data'][1]['tag'], 'tag3': "没有标签"})
elif ((len(taglist['data']) == 1)):
good.update({'tag1': taglist['data'][0]['tag'], 'tag2': "没有标签", 'tag3': "没有标签"})
else:
good.update({'tag1': "没有标签", 'tag2': "没有标签", 'tag3': "没有标签"})
def sim_user_good_recommend(login_flag, page, keyword):
recommend_list = {'error': 0, 'data': [], 'max_page': 1}
try:
sqlstr = "select userid2,sum from sim_user_recommend_list where userid1='%d'" % (
login_flag)
user_list = sql.select(sqlstr)
recommend_list['max_page'] = len(user_list['data'])
# print(user_list)
if (page < recommend_list['max_page']):
list = Recommend_db(user_list['data'][page]['userid2'], 1, keyword)
# print(list)
#
except Exception as e:
traceback.print_exc()
recommend_list['data'] = list['data']
# print(user_list)
return recommend_list
def InitStat(self):
sqlstr = "select * from recommend_tag"
re_tag = sql.select(sqlstr)
print(re_tag)
if (re_tag['error'] == 0):
for data in re_tag['data']:
user = str(data['userid'])
item = data['goodid']
tag = data['tag']
if random.random() > 0.1: # 将90%的数据作为训练集，剩下10%的数据作为测试集
self.addValueToMat(self.user_tags, user, tag, 1)
self.addValueToMat(self.tag_items, tag, item, 1)
self.addValueToMat(self.user_items, user, item, 1)
self.addValueToMat(self.item_tags, item, tag, 1)
else:
self.addValueToMat(self.user_items_test, user, item, 1)
print("user_tags", self.user_tags)
print("tag_items", self.tag_items)
print("user_items", self.user_items)
print("item_tags", self.item_tags)
print("user_items_test", self.user_items_test)
def user_collectlist(request):
# 先去cookie中找凭证
id = request.COOKIES.get('userid')
print(id)
login_flag = id
if (not id):
login_flag = 0
return redirect('/index/')
login_flag = int(login_flag)
page = request.GET.get('page')
print(page)
if not page:
page = 1
sqlstr = "select count(*) from collect where userid=%d" % (login_flag)
count = sql.select(sqlstr)
if (count['error'] == 0):
count = count['data'][0]['count(*)']
else:
count = 0
max_page = int(count / 16) + 1
else:
page = int(page)
max_page = request.GET.get('max_page')
max_page = int(max_page)
start = page - 5
end = page + 5
if (start <= 0):
start = 1
end = 10
if (end > max_page):
end = max_page
if (end - 9 > 0):
start = end - 9
# 找不到再显示
sqlstr = "select * from goodlist,collect where goodlist.id=collect.goodid and collect.userid='%d' order by collect_num desc limit %d,16" % (
login_flag, (page - 1) * 16)
message = sql.select(sqlstr)
# print(message)
if (message['error'] == 0):
goodlist = message['data']
else:
goodlist = []
for good0 in goodlist:
good.good_tag(good0)
print(start, end)
count_lis = list(range(start, end + 1))
print('/collect/')
print(count_lis)
return render(
request, 'user/collect.html', {
'error': 0,
'goodlist': goodlist,
'login_flag': login_flag,
'count_lis': count_lis,
'page': page,
'max_page': max_page,
'goto': '/user_collectlist/?'
})
def tag_CosineSim(tag):
sqlstr = "select * from cos where tag1='%s' order by cosine desc" % (tag)
sim_list = sql.select(sqlstr)
print(sim_list)
return sim_list
def get_freqtag():
sqlstr = "select * from tagfreq order by count desc limit 0,100"
freqtag_list = sql.select(sqlstr)
print(freqtag_list)
return freqtag_list['data']
def good_detail_tag(goodid):
sqlstr = "select tag,count from recommend_good_tag_count where goodid='%s' and tag not in (select keyword from goodlist where recommend_good_tag_count.goodid=goodlist.id) order by count desc" % (
goodid)
taglist = sql.select(sqlstr)
return taglist
def get_goodlist(id,login_flag,keyword,page,max_page,key,tag,search):
if not page:
page = 1;
if not keyword:
sqlstr = "select count(*) from goodlist"
else:
sqlstr = "select count(*) from goodlist where keyword='%s'" % (keyword)
count = sql.select(sqlstr)
if (count['error'] == 0):
count = count['data'][0]['count(*)']
else:
count = 0;
max_page = int((count - 1) / 16) + 1
if(tag):
if (not keyword):
keyword = 0
tag_list = tag.split(',')
message = tag_recommend.recommend_tag_good(tag_list, page, keyword)
if page==1:
max_page = message['max_page']
else:
if((not id) or key==1):
if(key==0):
login_flag=0;
if(not search):
if(not keyword):
sqlstr = "select * from goodlist order by collect_num desc limit %d,16"%((int(page)-1)*16)
else:
sqlstr = "select * from goodlist where keyword='%s' order by collect_num desc limit %d,16" % (
keyword, (int(page) - 1) * 16)
message = sql.select(sqlstr)
else:
search.replace(' ','%')
search.replace(' ', '%')
search='%'+search+'%'
print(search)
if (not keyword):
sqlstr = "select * from goodlist where title like '%s' or keyword like '%s' order by collect_num desc limit %d,16" % (search,search,(int(page) - 1) * 16)
message = sql.select(sqlstr)
slist = list(search)
search = '%'.join(slist)
sqlstr = "select * from goodlist where title like '%s' or keyword like '%s' order by collect_num desc limit %d,16" % (
search, search, (int(page) - 1) * 16)
message1 = sql.select(sqlstr)
else:
sqlstr = "select * from goodlist where keyword='%s' and title like '%s' order by collect_num desc limit %d,16" % (
keyword, search,(int(page) - 1) * 16)
message = sql.select(sqlstr)
slist = list(search)
search = '%'.join(slist)
sqlstr = "select * from goodlist where keyword='%s' and title like '%s' order by collect_num desc limit %d,16" % (
keyword, search,(int(page) - 1) * 16)
message1 = sql.select(sqlstr)
if(message['error']!=0):
message=message
else:
if(message1['error']==0):
message['data']=message['data']+message1['data']
print(sqlstr)
else:
login_flag=int(id)
if(not keyword):
keyword=0
if(key==2):
message=tag_recommend.sim_user_good_recommend(login_flag,page,keyword)
else:
message = tag_recommend.Recommend_db(login_flag, page, keyword)
if page==1:
max_page=message['max_page']
page = int(page)
max_page = int(max_page)
start = page - 5
end = page + 5
if (start <= 0):
start = 1;
end = 10;
if (end > max_page):
end = max_page;
if (end - 9 > 0):
start = end - 9;
print(start, end)
count_lis = list(range(start, end + 1))
print(count_lis)
#找不到再显示
# sqlstr="select * from goodlist left join collect on goodlist.id=collect.goodid and collect.userid='%d' order by collect_num desc limit %d,16"%(login_flag,(page-1)*16)
# print(sqlstr)
print(message)
if(message['error']==0):
goodlist=message['data']
for good in goodlist:
# print(good)
if(good['img_url'].find("http")<0):
good['img_url']="/static/img/shoucang.png"
else:
goodlist=[]
for good in goodlist:
good_tag(good)
return goodlist, login_flag, count_lis, page, max_page
def good_detail(request):
goodid=request.GET.get('goodid')
id = request.COOKIES.get('userid')
# print(id)
login_flag = id;
if (not id):
login_flag = 0;
else:
login_flag = int(id)
# try:
# sqlstr="select * from collect where goodid='%s' and userid='%d'"%(goodid,login_flag)
# collect=sql.select(sqlstr)
# # if len(collect['data'])<=0:
# # login_flag=-1;
#
# except Exception as e:
# print(e)
# traceback.print_exc()
if not goodid:
return render(request, 'good/index.html')
else:
try:
# print(goodid)
# sqlstr="select * from goodlist where goodlist.id='%s'"%(goodid)
# goodlist=sql.select(sqlstr)
# print(goodlist)
if(not id):
sqlstr="select * from sim_good_recommend_list,goodlist where goodlist.id=goodid2 and goodid1='%s' order by sum desc limit 0,16"%(goodid)
else:
sqlstr = "select * from sim_good_recommend_list,goodlist left join collect on goodlist.id=collect.goodid and collect.userid='%d' where goodlist.id=goodid2 and goodid1='%s' order by sum desc limit 0,16" % (
login_flag,goodid)
goodlist=sql.select(sqlstr)
for good in goodlist['data']:
good_tag(good)
# print("goodlist0",goodlist0)
# if(goodlist0['error']==0):
# if(goodlist['error']!=0):
# goodlist=goodlist0
# else:
# goodlist['data']=goodlist0['data']+goodlist['data']
sqlstr = "select * from gooddetaillist where gooddetaillist.id='%s'" % (goodid)
good_detail = sql.select(sqlstr)
print(good_detail)
sqlstr = "select goodcomments.id,name,avatarurl,comments,name from goodcomments,user where goodcomments.goodid='%s' and user.id=goodcomments.userid order by id desc" % (goodid)
good_comments = sql.select(sqlstr)
print(good_comments)
except Exception as e:
print(e)
traceback.print_exc()
qq=render(request,'good/good_detail.html',{'goodlist':goodlist['data'],'good_detail':good_detail['data'],'good_comments':good_comments['data'],'goto':'/good_detail/?goodid='+goodid,'login_flag':login_flag,'taglist':good_detail_tag(goodid)})
# str=qq.serialize()
#
#
# str.replace('<b/>','')
# str.replace('<i class="sprite-size"/>','')
with open("./11.html",'wb') as f:
f.write(qq.content)
str=qq.content.decode('utf-8')
str=str.replace('<b/>','')
str = str.replace('<i ', '<text')
str = str.replace('i>', 'text>')
return HttpResponse(str)
from app import sql
import random
import traceback
sqlstr = "select * from goodtag"
taglists = sql.select(sqlstr)
for taglist in taglists['data']:
print(taglist)
taglist_spilt = taglist['taglist'].split(')')
print(taglist_spilt)
sqlstr = "select userid from goodcomments where goodid='%s'" % (
taglist['id'])
s = sql.select(sqlstr)
print(s)
for tag in taglist_spilt:
try:
print(tag)
tag0 = tag.split('(')
print(tag0)
tag = tag0[0]
num = tag0[1]
for i in range(0, int(num)):
userid = random.choice(s['data'])
userid = int(userid['userid'])
print(userid)
sqlstr = "insert into recommend_tag(goodid,userid,tag) values('%s','%d','%s')" % (
taglist['id'], userid, tag)
sql.insert(sqlstr)
except Exception as e:
print(e.__traceback__)
traceback.print_exc()