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