def get_data_by_date(self,
page=0,
page_size=DEFAULT_PAGE_SIZE,
datestr=None):
session = self.db_helper.Session()
try:
news = session.query(News).filter(
News.news_id == self.news_id).first()
comment_query = session.query(Comments).filter(
Comments.news_id == self.news_id).filter(
Comments.comment_time == Helper.get_date(
datestr)).order_by(Comments.comment_id.desc())
comments = self.db_helper.query(comment_query,
page=page,
page_size=page_size)
sentiment_nums = session.query(
Comments.comment_time,
func.sum(
sql.case([(sql.column('sentiment') >=
self.DEFAULT_POSITIVE_THRESHOLD, 1)],
else_=0)).label('positive'),
func.sum(
sql.case([(sql.column('sentiment') <
self.DEFAULT_POSITIVE_THRESHOLD, 1)],
else_=0)).label('negative')
).filter(Comments.news_id == self.news_id).filter(
Comments.comment_time == Helper.get_date(datestr)).group_by(
Comments.comment_time)
total_comments = int(
session.query(func.count('*').label('total')).
filter(Comments.news_id == self.news_id).filter(
Comments.comment_time == Helper.get_date(datestr))[0][0])
session.close()
pages = int(total_comments /
page_size) if total_comments % page_size == 0 else int(
total_comments / page_size) + 1
return {
'news': news.to_dict(),
'comments': [comment.to_dict() for comment in comments],
'date': sentiment_nums[0][0].strftime("%Y-%m-%d"),
'positive': int(sentiment_nums[0][1]),
'negative': int(sentiment_nums[0][2]),
'total': int(sentiment_nums[0][1]) + int(sentiment_nums[0][2]),
'pages': pages
}
except Exception as ex:
self.logger.error("Exception occurred when getting data by date. ",
ex)
return {
'news': {},
'comments': [],
'date': '',
'positive': 0,
'negative': 0,
'total': 0
}
finally:
session.close()
def search_comments(self,
q,
page=0,
page_size=DEFAULT_PAGE_SIZE,
startdate=None,
enddate=None):
session = self.db_helper.Session()
try:
news = session.query(News).filter(
News.news_id == self.news_id).first()
query = session.query(Comments).filter(
Comments.news_id == self.news_id).filter(
Comments.comment.contains(q))
t_query = session.query(func.count('*').label('total')).filter(
Comments.news_id == self.news_id).filter(
Comments.comment.contains(q))
if startdate and startdate != '':
query = query.filter(
Comments.comment_time >= Helper.get_date(startdate))
t_query = t_query.filter(
Comments.comment_time >= Helper.get_date(startdate))
if enddate and enddate != '':
query = query.filter(
Comments.comment_time <= Helper.get_date(enddate))
t_query = t_query.filter(
Comments.comment_time <= Helper.get_date(enddate))
query = query.order_by(Comments.comment_time.desc())
results = self.db_helper.query(query,
page=page,
page_size=page_size)
total_comments = int(t_query[0][0])
pages = int(total_comments /
page_size) if total_comments % page_size == 0 else int(
total_comments / page_size) + 1
return {
'dates': {
'start': (Helper.get_date(startdate).strftime('%Y-%m-%d')
if startdate and startdate != '' else ''),
'end': (Helper.get_date(enddate).strftime('%Y-%m-%d')
if enddate and enddate != '' else '')
},
'pages': pages,
'comments': [result.to_dict() for result in results],
'news': news.to_dict()
}
except Exception as ex:
self.logger.error("Exception occurred when searching comments. ",
ex)
return {'pages': 0, 'comments': []}
finally:
session.close()