def get_unrated_movies(self, user_id):
'''
select movie_id
from movies
where movie_id not in
(select movie_id
from ratings where user_id = 1)
'''
session = Session()
try:
subquery = session.query(Rating.movie_id) \
.filter(Rating.user_id == user_id) \
.subquery()
result = session.query(Movie.movie_id) \
.filter(Movie.movie_id.notin_(subquery)) \
.all()
return [int(row[0]) for row in result]
except Exception as e:
print e
finally:
session.close()
def product_train_test_set(self):
session = Session()
product_num = session.query(Product).count()
product_train_num = int(0.95 * int(product_num))
product_test_num = product_num - product_train_num
product_train_list = session.query(Product)\
.order_by(Product.Enrolltime,
Product.ProductNo)\
.slice(0, product_train_num)\
.all()
for product_train in product_train_list:
update_tt = session.query(Product)\
.filter(Product.ProductNo == product_train.ProductNo)\
.one()
update_tt.TrainTest = 'train'
session.commit()
#The latest 5% of products is test set.
product_test_list = session.query(Product)\
.order_by(Product.Enrolltime,
Product.ProductNo)\
.slice(product_train_num, product_num+1)\
.all()
for product_test in product_test_list:
update_tt = session.query(Product)\
.filter(Product.ProductNo == product_test.ProductNo)\
.one()
update_tt.TrainTest = 'test'
session.commit()
session.close()
print 'updated product'
def delete_news(self, link):
try:
session = Session()
session.query(NewsArticle).filter(NewsArticle.Link == link).delete()
#session.commit()
return 'Success'
except Exception as e:
return "Failure"
finally:
session.close()
def atualizar(id, item):
try:
session = Session()
session.query(Item).filter(Item.id == id).update({
Item.description: item.description,
Item.checked: item.description
}, synchronize_session=False)
session.commit()
except Exception as e:
return internal_error(e), 500
finally:
session.close()
def sort_count(self):
session = Session()
product_list = session.query(Product).all()
for product in product_list:
find_count = session.query(Comment)\
.filter(Comment.ProductNo == product.ProductNo)\
.count()
print find_count
update_product = session.query(Product)\
.filter(Product.ProductNo == product.ProductNo)\
.one()
update_product.Review = int(find_count)
session.commit()
session.close()
def get_top_news(self, sort):
session = Session()
if sort == 'new':
result = session.query(NewsArticle).order_by(desc(NewsArticle.ReportDate)).limit(5)
elif sort == 'comment':
result = session.query(NewsArticle.Title, NewsArticle.Content, func.count(CommentList.Id).label('cnt'))\
.join(CommentList, NewsArticle.Link == CommentList.Link)\
.group_by(NewsArticle.Link).order_by('cnt desc').limit(5)
title_content = []
for row in result:
title_content.append({'title': row.Title, 'content': row.Content})
return title_content
def get_product_link(self):
session = Session()
find_product_link = session.query(Product).all()
result = [(row.Link, row.ProductNo) for row in find_product_link]
return result
def check(ids):
try:
session = Session()
if not ids:
return False
else:
for id in ids:
session.query(Item).filter(Item.id == id).update({
Item.checked: True
}, synchronize_session=False)
session.commit()
except Exception as e:
return internal_error(e), 500
finally:
session.close()
def get_user_vectors(self):
user_vectors = {}
session = Session()
try:
result = session.query(Rating) \
.order_by(Rating.user_id, Rating.movie_id) \
.all()
prev_user = None
for row in result:
cur_user = row.user_id
movie_id = row.movie_id
rating = row.rating
if prev_user == None or prev_user != cur_user:
uv = {}
uv[movie_id] = rating
user_vectors[cur_user] = uv
else:
user_vectors[cur_user][movie_id] = rating
prev_user = cur_user
return user_vectors
except Exception as e:
print e
return {}
finally:
session.close()
def get_userid(self, userid):
session = Session()
if session.query(User).filter(User.UserId == userid).all():
return True
else:
return False
session.close()
def update_user_list(self, userid, count):
session = Session()
update_user = session.query(User).filter(User.UserId == userid).one()
update_user.Written = count
update_user.TrainTest = 'train' #ALl of user is train.
session.commit()
session.close()
print 'updated'
def get_user_list(self, tt):
session = Session()
user_list = []
result = session.query(User).filter(User.TrainTest == tt).all()
for row in result:
user_list.append(row.UserId)
session.close()
return user_list
def get_item_list(self, tt):
session = Session()
item_list = []
result = session.query(Product).filter(Product.TrainTest == tt).all()
for row in result:
item_list.append(row.ProductNo)
session.close()
return item_list
def get_somedays_news(self, countdays):
session = Session()
somedays_news = session.query(NewsArticle).filter(NewsArticle.ReportDate >= datetime.date.today() - datetime.timedelta(days=countdays)).all()
result = [row.Link for row in somedays_news]
return result
def get_news_by_keyword_in_content(self, keyword):
data = []
session = Session()
subquery = session.query(News)
result = session.query(News)\
.filter(News.content.like('%' + keyword + '%'))\
.all()
for row in result:
news = {}
news['link'] = row.link
news['title'] = row.title
news['content'] = row.content
news['written_time'] = row.written_time
news['crawl_time'] = row.crawl_time
data.append(news)
return data
def get_recent_news(self):
session = Session()
recent_news = session.query(NewsArticle).order_by(desc(NewsArticle.ReportDate)).limit(10)
for row in recent_news:
CacheNews().cache_recent_news(str(row.Title.decode('utf-8')))
session.close()
def get(self): session = Session() stocks = session.query(Stock).all() res = [] for s in stocks: res.append(s._asdict()) return res
def get_types(self, place_id, type=''):
try:
session = Session()
if not type:
types = list()
rows = session.query(Types).filter(Types.place_id == place_id).all()
for row in rows: types.append(row.types)
# if rows: print '<TYPE>', place_id, types
types = list(set(types))
return types
else:
row = session.query(Types).filter(and_(Types.place_id == place_id, Types.types == type)).first()
if row: print '<TYPE>', row, 'exists.'
return row
except Exception as e:
print e
finally:
session.close()
def get_ratings(self, place_id):
try:
session = Session()
row = session.query(Ratings).filter(Ratings.place_id == place_id).first()
if row: print '<RATING>', row, 'exists.'
return row
except Exception as e:
print e
finally:
session.close()
def get_place_name(self, place_id):
try:
session = Session()
row = session.query(GeoInfos).filter(GeoInfos.place_id == place_id).first()
# if row: print '<GEOINFO>', row, 'exists.'
return row.name
except Exception as e:
print e
finally:
session.close()
def get_news_contents(self, link):
try:
session = Session()
result = session.query(NewsArticle).filter(NewsArticle.Link == link).first()
return result.Content
except Exception as e:
print e
return ''
finally:
session.close()
def get_users(self, user_id, place_id=''):
try:
session = Session()
if not place_id:
places = list()
rows = session.query(Users).filter(Users.user_id == user_id).all()
for row in rows: places.append(row.place_id)
# if rows: print '<USER>', user_id, places
places = list(set(places))
return places
else:
row = session.query(Users).filter(and_(Users.user_id == user_id, Users.place_id == place_id)).first()
if row: print '<USER>', row, 'exists.'
return row
except Exception as e:
print e
finally:
session.close()
def get_comment_by_id(self, comment_id):
try:
session = Session()
row = session.query(comments) \
.filter(comments.ID == comment_id) \
.first()
return row
except Exception as e:
print e
finally:
session.close()
def deletar(id):
try:
session = Session()
item = session.query(Item).get(id)
session.delete(item)
session.commit()
except Exception as e:
return internal_error(e), 500
finally:
session.close()
def update_comment_info(self, productno, comment_writer, comment_grade):
session = Session()
update_comment = session.query(Comment)\
.filter(Comment.ProductNo == productno,
Comment.Writer == comment_writer)\
.one()
if update_comment.Grade != comment_grade:
update_comment.Grade = comment_grade
update_comment.Enrolltime = crawltime
session.commit()
session.close()
def get_movie_by_title(self, movie_title):
try:
session = Session()
row = session.query(movie_info) \
.filter(movie_info.title == movie_title) \
.first()
return row
except Exception as e:
print e
finally:
session.close()
def get_news_by_keyword_in_title(self, keyword):
data = []
session = Session()
result = session.query(News).filter(News.title.like('%' + keyword + '%')).all()
for row in result:
news = {}
news['link'] = row.link
news['title'] = row.title
news['written_time'] = row.written_time
data.append(news)
return data
def get_news_by_id(self, news_id):
try:
session = Session()
row = session.query(News) \
.filter(News.link == news_id) \
.first()
return row
except Exception as e:
print e
finally:
session.close()
def get_news_id(self, news_url):
session = Session()
find_news = session.query(NewsArticle).filter(NewsArticle.Link == news_url).all()
if len(find_news) >= 1:
return False
else:
return True
session.close()
def find_keyword_in_contents(self, keyword):
session = Session()
result = session.query(NewsArticle).filter(NewsArticle.Content.like('%' + keyword + '%')).all()
news_list = []
for row in result:
news_dict = {}
news_dict['link'] = row.Link
news_dict['title'] = row.Title
news_dict['content'] = row.Content
news_list.append(news_dict)
return news_list
