def hotmovie(self):
conn,cur = GlobalFun.ConnectSql()
cur.execute('select movieid,count(1) from movierecommender.ratings group by movieid order by count(1) desc limit 5;')
data = cur.fetchall()#拿到最热门的5部电影
GlobalFun.Closesql(conn,cur)
for tup in data:
t = threading.Thread(target=self.job,args=(self.hotmovieFrame,tup[0]))
t.start()
def get_score(userid, movieid):
conn, cur = GlobalFun.ConnectSql()
# 获取用户打分信息
sql = "select rating,timestamp from movierecommender.ratings where movieid={} and userid={};".format(
movieid, userid)
cur.execute(sql)
data = cur.fetchall()
GlobalFun.Closesql(conn, cur)
return data
def get_new_table_statistically():
warnings.filters
conn, cur = GlobalFun.ConnectSql()
sql_ = ["use MovieRecommender;",
"create table movie_score_info select movieId,avg(rating) score,count(*) times from ratings group by movieId;"
]
for sql in sql_:
cur.execute(sql)
conn.commit()
GlobalFun.Closesql(conn, cur)
def online_rec(self):
conn, cur = GlobalFun.ConnectSql()
cur.execute(
'select movieid from movierecommender.online_recommend where userid = {} limit 5;'.format(
self.userid))
data = cur.fetchall() # 拿到离线用户推荐信息
GlobalFun.Closesql(conn, cur)
print(data)
print(len(data))
for tup in data:
t = threading.Thread(target=self.job, args=(self.online_rec_Frame, tup[0]))
t.start()
def offline_rec_als(self):
conn, cur = GlobalFun.ConnectSql()
cur.execute(
'select recommendid from movierecommender.offline_recommend_als where userid={} order by predictScore desc limit 5;'.format(self.userid))
data = cur.fetchall() # 拿到离线用户推荐信息
GlobalFun.Closesql(conn, cur)
print(data)
print(len(data))
if len(data) > 0:
for tup in data:
t = threading.Thread(target=self.job, args=(self.offline_rec_alsFrame, tup[0]))
t.start()
def insertnewuser(userid):
'''
当新用户注册后，在online_recommend中建立新用户的推荐榜单，初始时，推荐榜单为热门电影的前50
:param userid:
:return:
'''
conn, cur = GlobalFun.ConnectSql()
sql = 'insert into movierecommender.online_recommend select {},movieid from movierecommender.movie_score_info order by times desc limit 50;'.format(
userid)
cur.execute(sql)
conn.commit()
GlobalFun.Closesql(conn, cur)
def offline_rec_svd(self):
conn, cur = GlobalFun.ConnectSql()
cur.execute(
'select recommendid from movierecommender.offline_recommend_svd where userid={} order by predictScore desc limit 5;'.format(self.userid))
data = cur.fetchall() # 拿到离线用户推荐信息
GlobalFun.Closesql(conn, cur)
print(data)
print(len(data))
if len(data) == 0:
tk.Label(self.offline_rec_svdFrame,text="Sorry T^T\nI haven't know you a long time\nGive me some time to recommend for you!",font=('',20)).pack(side='bottom')
else:
for tup in data:
t = threading.Thread(target=self.job, args=(self.offline_rec_svdFrame, tup[0]))
t.start()
def get_search(self):
self.result_Frame.destroy()
self.result_Frame = tk.Frame(self.window, width=800, height=700)
self.result_Frame.pack_propagate(False)
self.result_Frame.place(x=0, y=90, anchor='nw')
searchcontent = self.searchcontent.get()
#从mysql中搜索相关结果
conn,cur = GlobalFun.ConnectSql()
sql = "select movieid from movierecommender.movies where title like '%{}%';".format(searchcontent)
cur.execute(sql)
data = cur.fetchall()#记录用户的打分情况
GlobalFun.Closesql(conn,cur)
#显示搜索结果
tk.Label(self.result_Frame, text="We have found {} movies,and only show the recent 15 movies".format(len(data))).place(x=58,y=0,anchor='nw')
#清空之前的搜索结果
#电影展示页面
if len(data) == 0:
tk.Label(self.result_Frame,text="Sorry,we don't have similar movies",font=('',20)).place(x=250,y=60,anchor="nw")
if len(data) > 0:
#只显示一行
list1_Frame = tk.Frame(self.result_Frame)
list1_Frame.place(x=15,y=50)
movielist1 = data[:5]
for tup in movielist1:
t=threading.Thread(target=self.job,args=(list1_Frame,tup[0]))
t.start()
if len(data) >5:
list2_Frame = tk.Frame(self.result_Frame)
list2_Frame.place(x=15, y=258)
movielist2 = data[5:10]
for tup in movielist2:
t=threading.Thread(target=self.job,args=(list2_Frame,tup[0]))
t.start()
if len(data) >10:
#再显示一行
list3_Frame = tk.Frame(self.result_Frame)
list3_Frame.place(x=15, y=465)
movielist3 = data[10:15]
for tup in movielist3:
t=threading.Thread(target=self.job,args=(list3_Frame,tup[0]))
t.start()
def get_movie_url(movieid):
'''
:param movieid:
:return: url_imdbid
根据movieid得到对应的网页链接
'''
#抓取movieid对应的imdbid
conn, cur = GlobalFun.ConnectSql()
cur.execute(
"select imdbid from movierecommender.links where movieId = {}".format(
movieid))
imdbid = str(cur.fetchall()[0][0])
GlobalFun.Closesql(conn, cur)
imdbid = "0" * (7 - len(imdbid)) + imdbid
url_imdbid = "http://www.imdb.com/title/tt{}/".format(imdbid)
return url_imdbid
def rate(self):
try:
Score = eval(self.Content.get())
userid = self.userid
movieid = self.movieid
if isinstance(Score, int) and Score <= 5 and Score >= 0:
timestamp = int(time.time())
rate_score = Score
conn, cur = GlobalFun.ConnectSql()
if self.type == "edit":
sql = "update movierecommender.ratings set userid={},movieid={},rating={},timestamp={} where userid={} and movieid={};".format(
userid, movieid, Score, timestamp, userid, movieid,
Score)
else:
sql = "insert into movierecommender.ratings values({},{},{},{});".format(
userid, movieid, Score, timestamp)
cur.execute(sql)
conn.commit()
GlobalFun.Closesql(conn, cur)
tk.messagebox.showinfo(title="successed!",
message="Thank you for your rating!",
command=destroy(self.Rating_Frame))
self.Rating_Frame.destroy()
print('problem is destroy')
rated_frame(self.window, rate_score, timestamp, userid,
movieid)
print('prolem is trigger')
#触发在线推荐名单的改变
print("userid is", userid, "movieid is ", movieid)
RecommendationAlogrithm.OnlineRecommend.updateonline(
userid, movieid)
print('prolem is here')
else:
tk.messagebox.showwarning(
title="failed!",
message=
"sorry,the score must be an integer from 1 to 5\nplease modify and rate again."
)
self.Content.set(3)
except:
tk.messagebox.showwarning(
title="failed!",
message=
"sorry,\nthe score must be an integer from 1 to 5\nplease modify and rate again."
)
self.Content.set(3)
def get_score(self):
#获取评分数据
conn, cur = GlobalFun.ConnectSql()
sql = "select score,times from movierecommender.movie_score_info where movieid={};".format(
self.movieid)
cur.execute(sql)
data = cur.fetchall()
score = data[0][0] #电影的平均
times = data[0][1] #电影被点评的次数
return score, times
def get_similar_movie_list(movieId, type="ALS"):
conn, cur = GlobalFun.ConnectSql()
if type == "SVD":
sql = "select similarId,similarDegree from movierecommender.movie_similar_svd where movieId={} order by similarDegree desc limit 5;".format(
movieId)
else:
sql = "select similarId,similarDegree from movierecommender.movie_similar_als where movieId={} order by similarDegree desc limit 5;".format(
movieId)
cur.execute(sql)
data = cur.fetchall()
return data
def get_src(url, movieId):
"""
:param url:
:return: src,title,date,genres
根据网页连接和数据库，抓取电影海报地址，电影名称，电影上映时间，电影类型
"""
html = requests.get(url)
bs = etree.HTML(html.text)
src = bs.xpath('//link[@rel="image_src"]/@href')[0]
conn, cur = GlobalFun.ConnectSql()
sql = "select title,genres from movierecommender.movies where movieid = {}".format(
movieId)
cur.execute(sql)
data = cur.fetchall()
text = bs.xpath('//script[@type="application/ld+json"]')[0].text
briefinfo = re.findall('^.*?"description": "(.*?)",\n "date',
text,
flags=re.S)[0].strip(', "')
title = re.findall('(.*)\(', data[0][0])[0].strip(' ')
date = re.findall('\((\d*)\)', data[0][0])[0].strip(' ')
genres_list = data[0][1].strip('\r').split('|')
genres = "\n".join(genres_list)
GlobalFun.Closesql(conn, cur)
return src, title, date, genres, briefinfo
def comfirm(self):
#判断userid是否异常
try:
name = eval(self.name.get())
except:
tk.messagebox.showerror(message="The user name must be integer")
return
if not isinstance(name, int):
tk.messagebox.showerror(message="The user name must be integer")
return
conn, cur = GlobalFun.ConnectSql()
cur.execute(
"select password from movierecommender.users where userid={}".
format(self.name.get()))
data = cur.fetchall()
if len(data) == 0:
#记录新用户
cur.execute(
"insert into movierecommender.users values({},{})".format(
self.name.get(), self.password.get()))
conn.commit()
tk.messagebox.showinfo(
message="Welcome :>\nNew User:{}".format(self.name.get()))
self.window_sign_up.destroy()
self.userid = eval(self.name.get())
#触发online_recommend添加上新用户的推荐列表
RecommendationAlogrithm.OnlineRecommend.insertnewuser(self.userid)
self.update() #刷新页面
else:
if data[0][0] == self.password.get():
tk.messagebox.showinfo(
message="Welcome :D\n User:{}".format(self.name.get()))
self.window_sign_up.destroy()
self.userid = eval(self.name.get())
self.update()
else:
tk.messagebox.showerror(
message="Sorry :<\nThe password is wrong")
def inputdata():
warnings.filters
#创建连接
conn,cur = GlobalFun.ConnectSql()
#新建MovieRecommender数据库
sql_creatDatabase = ["drop database if exists MovieRecommender;",
"create database MovieRecommender;",
"use MovieRecommender;",
]
for sql in sql_creatDatabase:
cur.execute(sql)
# 把data中的数据导入到mysql中
#导入数据links.csv(links.csv数据在导入前，将空值写为NULL)
sql_inputlinks = ["create table links(movieId int,imdbId int,tmdbId int);",
r'''load data infile '{}' into table links fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' ignore 1 lines;'''.format(GlobalVar.pathlink)
]
for sql in sql_inputlinks:
cur.execute(sql)
#导入数据movies.csv
sql_inputmovies = ["create table movies(movieId int,title varchar(200),genres varchar(100));",
r'''load data infile "{}" into table movies fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n' ignore 1 lines;'''.format(GlobalVar.pathmovie),
]
for sql in sql_inputmovies:
cur.execute(sql)
#导入数据ratings
sql_inputratings = ["create table ratings(userId int,movieId int,rating float,timestamp int)",
r'''load data infile "{}" into table ratings fields terminated by ',' lines terminated by '\n' ignore 1 lines'''.format(GlobalVar.pathrating)
]
for sql in sql_inputratings:
cur.execute(sql)
conn.commit()
#导入数据movie_similar_svd
sql_inputmovie_similar_svd = ["create table movie_similar_svd(movieId int,similarId int,similarDegree float)",
r'''load data infile "{}" into table movie_similar_svd fields terminated by',' lines terminated by '\n' ignore 1 lines'''.format(GlobalVar.pathmovie_similar_svd)]
for sql in sql_inputmovie_similar_svd:
cur.execute(sql)
conn.commit()
#导入数据offline_recommend_svd
sql_inputoffline_recommend_svd = ["create table offline_recommend_svd(userId int,recommendId int,predictScore float)",
r'''load data infile "{}" into table offline_recommend_svd fields terminated by',' lines terminated by '\n' ignore 1 lines'''.format(GlobalVar.pathoffline_recommend_svd)]
for sql in sql_inputoffline_recommend_svd:
cur.execute(sql)
conn.commit()
#导入数据users
sql_inputusers = ['create table users (userid int,password varchar(10))',
r'''load data infile "{}" into table users fields terminated by ',' lines terminated by '\n' ignore 1 lines'''.format(GlobalVar.pathusers)]
for sql in sql_inputusers:
cur.execute(sql)
conn.commit()
#导入数据movie_similar_als
sql_inputmovie_similar_als = ["create table movie_similar_als(movieId int,similarId int,similarDegree float)",
r'''load data infile "{}" into table movie_similar_als fields terminated by ',' lines terminated by '\n' ignore 1 lines'''.format(GlobalVar.pathmovie_similar_als)]
for sql in sql_inputmovie_similar_als:
cur.execute(sql)
conn.commit()
#导入数据offline_recommend_als
sql_inputoffline_recommend_als = [
"create table offline_recommend_als(userId int,recommendId int,predictScore float)",
r'''load data infile "{}" into table offline_recommend_als fields terminated by',' lines terminated by '\n' ignore 1 lines'''.format(
GlobalVar.pathoffline_recommend_als)]
for sql in sql_inputoffline_recommend_als:
cur.execute(sql)
conn.commit()
#导入数据online_recommend
sql_inputonline_recommend = [
"create table online_recommend (userId int,movieId int);",
r'''load data infile "{}" into table online_recommend fields terminated by ',' lines terminated by '\n' ignore 1 lines'''.format(GlobalVar.pathonline_recommend)
]
for sql in sql_inputonline_recommend:
cur.execute(sql)
conn.commit()
GlobalFun.Closesql(conn, cur)
def __init__(
self,
root,
window,
movieid,
userid,
):
self.movieid = movieid
self.userid = userid
self.window = window #铺的纸
self.root = root
navbar_Frame = tk.Frame(self.window, width=800, height=20) # 导航栏
navbar_Frame.pack_propagate(False)
navbar_Frame.place(x=0, y=0, anchor="nw")
TkinterGUI.metaButtons.NavigationBar(self.root,
self.window,
navbar_Frame,
self.movieid,
self.userid,
type="B")
#从mysql中提取用户的浏览记录
conn, cur = GlobalFun.ConnectSql()
sql = "select movieid,rating,timestamp from movierecommender.ratings where userid={} order by timestamp desc limit 15;".format(
self.userid)
cur.execute(sql)
data = cur.fetchall() #记录用户的打分情况
sql = "select count(rating) from movierecommender.ratings where userid={};".format(
self.userid)
cur.execute(sql)
num_rated_movie = cur.fetchall()[0][0]
GlobalFun.Closesql(conn, cur)
#显示用户投票了几部电影
if num_rated_movie <= 1:
tk.Label(
self.window,
text="You have rate {} movie,we only show the recent 15 movies"
.format(num_rated_movie)).place(x=0, y=20, anchor='nw')
else:
tk.Label(
self.window,
text="You have rate {} movies,we only show the recent 15 movies"
.format(num_rated_movie)).place(x=0, y=20, anchor='nw')
#电影展示页面
if num_rated_movie == 0:
tk.Label(self.window,
text="No movies you have rated",
font=('', 20)).place(x=250, y=156, anchor="nw")
if num_rated_movie > 0:
#只显示一行
self.list1_Frame = tk.Frame(self.window)
self.list1_Frame.place(x=15, y=130)
movielist1 = data[:5]
for tup in movielist1:
t = threading.Thread(target=self.job,
args=(self.list1_Frame, tup[0], tup[1],
tup[2]))
t.start()
if num_rated_movie > 5:
self.list2_Frame = tk.Frame(self.window)
self.list2_Frame.place(x=15, y=363)
movielist2 = data[5:10]
for tup in movielist2:
t = threading.Thread(target=self.job,
args=(self.list2_Frame, tup[0], tup[1],
tup[2]))
t.start()
if num_rated_movie > 10:
#再显示一行
self.list3_Frame = tk.Frame(self.window)
self.list3_Frame.place(x=15, y=590)
movielist3 = data[10:15]
for tup in movielist3:
t = threading.Thread(target=self.job,
args=(self.list3_Frame, tup[0], tup[1],
tup[2]))
t.start()
def updateonline(userid, nowmovieid):
'''
当老用户对新电影进行投票后，按投票分数更新online_recommend的值
:param userid:
:param nowmovieid:
:return:
'''
conn, cur = GlobalFun.ConnectSql()
cur.execute(
'select movieid from movierecommender.online_recommend where userid = {}'
.format(userid))
optionallist = cur.fetchall()
cur.execute(
'select similarid from movierecommender.movie_similar_svd where movieid = {}'
.format(nowmovieid))
similarlist = cur.fetchall()
cur.execute(
"select * from movierecommender.ratings where userid = {}".format(
userid))
ratingdata = cur.fetchall()
ratingdata = np.array(ratingdata)
mix_list = set(optionallist) | set(similarlist)
mix_list = np.array([mix[0] for mix in mix_list])
mix_list = np.array(list(set(mix_list) - set(ratingdata[:, 1])))
movieIdlist = pickle.load(open(GlobalVar.pathmovieidlist, 'rb')).values
movieIdlist = [*map(int, movieIdlist)]
cosSim = pickle.load(open(GlobalVar.pathcosSim_svd, 'rb'))
cosSim = pd.DataFrame(cosSim,
columns=movieIdlist,
index=movieIdlist,
copy=True)
recentmovie = ratingdata[np.argsort(
ratingdata[:, -1])[::-1][:5], :] #选取最近评分的五部电影
preVal1 = cosSim.loc[mix_list, recentmovie[:, 1]].values.dot(
recentmovie[:, 2]) / np.sum(
cosSim.loc[mix_list, recentmovie[:, 1]].values, axis=1)
highratemovie = recentmovie[:, 2] > 3
lowratemovie = recentmovie[:, 2] < 3
preVal2 = np.log(
np.sum((cosSim.loc[mix_list, recentmovie[:, 1]].values > 0.8) *
highratemovie,
axis=1) + 1) #增强因子
preVal3 = np.log(
np.sum((cosSim.loc[mix_list, recentmovie[:, 1]].values > 0.8) *
lowratemovie,
axis=1) + 1) #减弱因子
preScore = preVal1 + preVal2 - preVal3
newrecommend = mix_list[np.argsort(preScore)[::-1][:50]]
useridlist = np.array([userid] * len(newrecommend))
newrecommendValue = ','.join(map(str, [*zip(useridlist, newrecommend)]))
sqls = [
'delete from movierecommender.online_recommend where userid = {}'.
format(userid),
'insert into movierecommender.online_recommend values {}'.format(
newrecommendValue)
]
for sql in sqls:
cur.execute(sql)
conn.commit()
GlobalFun.Closesql(conn, cur)
print('online well done!!!')