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()