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 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 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 get_userid(self, userid):
session = Session()
if session.query(User).filter(User.UserId == userid).all():
return True
else:
return False
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 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(self): session = Session() stocks = session.query(Stock).all() res = [] for s in stocks: res.append(s._asdict()) return res
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_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_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 save_news(self, news_url, news_title, news_contents, news_company, news_reporter_email, news_date):
if self.get_news_id(news_url):
session = Session()
insert_news = NewsArticle(Link = news_url, Title = news_title, Content = news_contents, NewsCompany = news_company, ReporterEmail = news_reporter_email, ReportDate = news_date)
session.add(insert_news)
session.commit()
session.close()
else:
session = Session()
update_news = session.query(NewsArticle).filter(NewsArticle.Link == news_url).one()
update_news.Title = news_title
update_news.Content = news_contents
update_news.NewsCompany = news_company
update_news.ReporterEmail = news_reporter_email
update_news.ReportDate = news_date
session.commit()
session.close()
def save_users(self, user_id, place_id):
session = Session()
if not self.get_users(user_id, place_id):
user = Users(user_id = user_id, place_id = place_id)
session.add(user)
session.commit()
print '<USER>', user_id, place_id, 'saved successfully.'
session.close()
def save_place(self, place_id, city, name, address, lng, lat):
session = Session()
if not self.get_place(place_id):
geo = GeoInfos(place_id = place_id, name = name, city = city, address = address, lng = lng, lat = lat)
session.add(geo)
session.commit()
print '<GEOINFO>', place_id, name, 'saved successfully!'
session.close()
def save_types(self, place_id, types):
session = Session()
if not self.get_types(place_id, types):
type = Types(place_id = place_id, types = types)
session.add(type)
session.commit()
print '<TYPE>', place_id, types, 'saved successfully!'
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 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 save_ratings(self, place_id, gg_reviews, gg_ratings, ta_reviews, ta_ratings):
session = Session()
if not self.get_ratings(place_id):
rate = Ratings(place_id = place_id, gg_reviews = gg_reviews, gg_ratings = gg_ratings,
ta_reviews = ta_reviews, ta_ratings = ta_ratings)
session.add(rate)
session.commit()
print '<RATING>', place_id, 'saved successfully!'
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 criar(item):
try:
session = Session()
session.add(item)
session.commit()
return item
except Exception as e:
return internal_error(e), 500
finally:
session.close()
def session(connection):
'''We need to ensure that there is a fresh session per test run'''
transaction = connection.begin()
session = Session(bind=connection)
# SourceFactory._meta.sqlalchemy_session = session
# TargetFactory._meta.sqlalchemy_session = session
yield session
session.close()
transaction.rollback()
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 save_news(self, news_id, title, content):
session = Session()
if not self.get_news_by_id(news_id):
print news_id
news = News(link=news_id,
title=title,
content=content,
crawl_time=datetime.datetime.now())
session.add(news)
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_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 save_comment(self, id, news_id, content, written_time, sympathy_count, antipathy_count):
session = Session()
if not self.get_comment_by_id(id):
print content
comment = Comment(id = id, news_id = news_id, content = content,
written_time = written_time, sympathy_count = sympathy_count,
antipathy_count = antipathy_count, crawl_time = datetime.datetime.now())
session.add(comment)
session.commit()
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 save_comment_info(self, productno, comment_writer, comment_grade):
if self.get_comment_info(productno, comment_writer):
session = Session()
insert_comment = Comment(ProductNo=productno,
Writer=comment_writer,
Grade=comment_grade,
Enrolltime=crawltime)
session.add(insert_comment)
session.commit()
session.close()
else:
self.update_comment_info(productno, comment_writer, comment_grade)
