def read_rst(rst_id): # 店の詳細情報取得 session = Session() s = ( session.query( Tabelog.Rcd.label("rst_id"), Tabelog.RestaurantName, Tabelog.TabelogMobileUrl, Tabelog.TotalScore, Tabelog.Situation, Tabelog.DinnerPrice, Tabelog.LunchPrice, Tabelog.Category, Tabelog.Station, Tabelog.Address, Tabelog.Tel, Tabelog.BusinessHours, Tabelog.Holiday, gf.x(Tabelog.LatLng).label("lat"), gf.y(Tabelog.LatLng).label("lng"), func.round(func.avg(UserPost.difficulty)).label("difficulty"), func.avg(UserPost.difficulty).label("raw_difficulty"), ) .filter(Tabelog.Rcd == rst_id) .filter(UserPost.rst_id == Tabelog.Rcd) .group_by(UserPost.id) .first() ) # gf.wkt(Tabelog.LatLng).label('Point'))\ # .filter('Rcd = :rcd').params(rcd=rst_id).first() session.commit() session.close() return s
def full_info_of_near_rests(lat=34.985458, lng=135.757755, zoom=1, limit=None): '''引数のlat(緯度)とlng(経度)を中心として、縦横margin*2の正方形ないにある レストランをTabelogテーブルから取得する デフォルト値は京都駅の緯度経度 ''' margin = _get_margin(zoom) left, right = lng - margin, lng + margin bottom, top = lat - margin, lat + margin box = 'POLYGON((%f %f, %f %f, %f %f, %f %f, %f %f))' \ % (left, bottom, right, bottom, right, top, left, top, left, bottom) # % (x+m, y-m, x-m, y-m, x-m, y+m, x+m, y+m, x+m, y-m) session = Session() # s = session.query(Tabelog.RestaurantName, gf.wkt(Tabelog.LatLng))\ s = session.query( Tabelog.id, Tabelog.Rcd, Tabelog.RestaurantName, Tabelog.TabelogMobileUrl, Tabelog.TotalScore, Tabelog.Situation, Tabelog.DinnerPrice, Tabelog.LunchPrice, Tabelog.Category, Tabelog.Station, Tabelog.Address, Tabelog.Tel, Tabelog.BusinessHours, Tabelog.Holiday, func.round(func.avg(UserPost.difficulty)).label('difficulty'),\ gf.wkt(Tabelog.LatLng).label('Point'))\ .filter(Tabelog.Rcd == UserPost.rst_id)\ .filter(Tabelog.LatLng.within(box))\ .group_by(UserPost.id)\ .limit(limit) session.commit() session.close() return s
def execute_sql_to_tabelog(where): """Tabelogテーブルからwhereに一致するデータを取得 LatLngをPointにして出力 """ # s = session.query(*(['id']+TAGNAMES[:-1]+['Point'])).from_statement('select '\ # + ', '.join(['id'] + TAGNAMES[:-1] + ['AsText(LatLng) as Point']) \ # + ' from Tabelog where {where}'.format(where=where)).all() # columns = ['Tabelog.id'] + ['Tabelog.'+tag for tag in TAGNAMES[:-1]] # s = session.query(*(columns+[gf.wkt('Tabelog.' + TAGNAMES[-1])])).filter(where).all() # s = session.query(Tabelog.RestaurantName).filter(where).all() session = Session() s = session.query( Tabelog.id, Tabelog.Rcd, Tabelog.RestaurantName, Tabelog.TabelogMobileUrl, Tabelog.TotalScore, Tabelog.Situation, Tabelog.DinnerPrice, Tabelog.LunchPrice, Tabelog.Category, Tabelog.Station, Tabelog.Address, Tabelog.Tel, Tabelog.BusinessHours, Tabelog.Holiday, gf.wkt(Tabelog.LatLng).label("Point"), ).filter(where) session.commit() session.close() return s
def get_situation(situation): """situationが引数のsituationを含むレストランを取得 """ session = Session() try: situation = situation.encode("utf-8") except: pass s = session.query( Tabelog.id, Tabelog.Rcd, Tabelog.RestaurantName, Tabelog.TabelogMobileUrl, Tabelog.TotalScore, Tabelog.Situation, Tabelog.DinnerPrice, Tabelog.LunchPrice, Tabelog.Category, Tabelog.Station, Tabelog.Address, Tabelog.Tel, Tabelog.BusinessHours, Tabelog.Holiday, gf.wkt(Tabelog.LatLng).label("Point"), ).filter(Tabelog.Situation.like("%{situation}%".format(situation=situation))) session.commit() session.close() return s
def _insert_or_update_title(id=None, rank=None, name=None, requirement=None, stamp=None): """称号の運営用 新しく称号を追加、更新するために使う """ now = datetime.now() session = Session() if not name or not requirement: print "称号名と取得条件を入力してください" return False title = session.query(Title).filter(Title.id == id).limit(1).first() try: if title: title.rank = rank title.stamp = stamp title.modified = now print "title update", else: title = Title(id, rank, name, requirement, stamp, now, now) session.add(title) print "title insert", print name session.flush() session.commit() session.close() return True except Exception, e: print e session.rollback() session.commit() session.close() return False
def read_userstats(user_id): session = Session() userstats = ( UserStats( UserStats.user_id, UserStats.total, UserStats.sequence, UserStats.level_1, UserStats.level_2, UserStats.level_3, UserStats.level_4, UserStats.level_5, UserStats.sakyo, UserStats.ukyo, UserStats.kita, UserStats.kamigyo, UserStats.shimogyo, UserStats.nakagyo, UserStats.higashiyama, UserStats.yamashina, UserStats.saikyo, UserStats.minami, UserStats.fushimi, UserStats.already_acquire, UserStats.created, UserStats.modified, ) .filter("user_id = :user_id") .params(user_id=user_id) .first() ) session.commit() session.close() return userstats
def read_title(title_id): session = Session() title = session.query(Title.id, User.user_name, User.home_place)\ .filter(Title.id == title_id).first() session.commit() session.close() return title
def delete_user_post(user_id, rst_id): """UserPostテーブルから、user_idとrst_idの組をもつ行を削除する """ if not user_id: return False session = Session() try: userpost = ( session.query(UserPost) .filter("user_id = :user_id and rst_id = :rst_id") .params(user_id=user_id, rst_id=rst_id) .first() ) print userpost session.delete(userpost) session.flush() session.commit() session.close() return True except Exception, e: print "delete_user_post :", e session.rollback() session.commit() session.close() return False
def ku(self): """user_statsから各区に何回行ったかを取得し、 それに応じた称号を返す """ session = Session() section_req = re.compile("(.*)区.*?(\d+)件.*") some_sec_req = re.compile("京都(\d+)区.*") id_reqs = session.query(Title.id, Title.requirement).filter(Title.requirement.like("%区%")).all() session.commit() session.close() kyoto_ku_counts = 0 kus = [ "sakyo", "ukyo", "kita", "kamigyo", "shimogyo", "nakagyo", "higashiyama", "yamashina", "saikyo", "minami", "fushimi", ] kus_dic = { "左京": "sakyo", "右京": "ukyo", "北": "kita", "上京": "kamigyo", "下京": "shimogyo", "中京": "nakagyo", "東山": "higashiyama", "山科": "yamashina", "南": "minami", "伏見": "fushimi", "西京": "saikyo", } for _ku in kus: if self.stats[_ku]: kyoto_ku_counts += 1 for id, req in id_reqs: try: sec = section_req.findall(req)[0] except: sec = None try: some_secs = some_sec_req.findall(req)[0] except: some_secs = None if some_secs: if kyoto_ku_counts >= int(some_secs): self.acquire.add(id) print id elif sec: _ku, _req = sec if self.stats[kus_dic[_ku]] >= int(_req): self.acquire.add(id) print id return True
def update_userstats(\ user_id, total=None, sequence=None, level_1=None, level_2=None, level_3=None, level_4=None, level_5=None, \ sakyo=None, ukyo=None, kita=None, kamigyo=None, shimogyo=None, \ nakagyo=None, higashiyama=None, yamashina=None, saikyo=None, \ minami=None, fushimi=None, already_acquire=None): now = datetime.now() session = Session() userstats = session.query(UserStats).filter(user_id=user_id).first() if total: userstats.total = total if sequence: userstats.sequence = sequence if level_1: userstats.level_1 = level_1 if level_2: userstats.level_2 = level_2 if level_3: userstats.level_3 = level_3 if level_4: userstats.level_4 = level_4 if level_5: userstats.level_5 = level_5 if sakyo: userstats.sakyo = sakyo if ukyo: userstats.ukyo = ukyo if kita: userstats.kita = kita if kamigyo: userstats.kamigyo = kamigyo if shimogyo: userstats.shimogyo = shimogyo if nakagyo: userstats.nakagyo = nakagyo if higashiyama: userstats.higashiyama = higashiyama if yamashina: userstats.yamashina = yamashina if saikyo: userstats.saikyo = saikyo if minami: userstats.minami = minami if fushimi: userstats.fushimi = fushimi if already_acquire: userstats.already_acquire = already_acquire userstats.modified = now try: session.flush() session.commit() except Exception ,e: session.rollback() print e session.commit() session.close() return False
def read_user(user_id): '''Userテーブルからid=user_idのユーザーの情報を取得 返り値はuserの情報 ''' session = Session() user = session.query(User.id, User.user_name, User.home_place)\ .filter('id = :user_id')\ .params(user_id = user_id).first() session.commit() session.close() return user
def read_user(user_id): """Userテーブルからid=user_idのユーザーの情報を取得 返り値はuserの情報 """ session = Session() user = ( session.query(User.id, User.user_name, User.home_place).filter("id = :user_id").params(user_id=user_id).first() ) session.commit() session.close() return user
def read_comments(rst_id): '''rst_idの店について、他の人のコメントを取得する ''' session = Session() s = session.query(UserPost.comment, UserPost.difficulty, User.user_name)\ .filter(UserPost.user_id == User.id)\ .filter(UserPost.rst_id == rst_id)\ .order_by(UserPost.modified.desc()).limit(10).all() posts = [_s._asdict() for _s in s] session.commit() session.close() return posts
def avg_difficult(rst_id): """Tabelog.idの店について、平均のdifficultyを返す """ # rst_id = session.query(Tabelog.Rcd)\ # .filter('id = :id').params(id = id).first() if not rst_id: print "no such Restaurant" return False session = Session() avg = session.query(func.avg(UserPost.difficulty)).filter("rst_id = :rst_id").params(rst_id=rst_id).first() session.commit() session.close() return float(avg[0]) if avg[0] else False
def delete_userstats(user_id): session = Session() try: userstats = session.query(UserStats).filter(user_id=user_id).first() print userstats session.delete(userstats) session.flush() session.commit() session.close() return True except Exception, e: print "delete_userstats :", e session.rollback() session.commit() session.close() return False
def delete_title(id): session = Session() try: title = session.query(Title).filter(Title.id == id).first() print title session.delete(title) session.flush() session.commit() session.close() return True except Exception, e: print "delete_user_post :", e session.rollback() session.commit() session.close() return False # }}}
def set_default_difficulty(fname='situation.dict'): print 'load_pickle ... ', dic = load_pickle(fname) print 'done.' scores = calc_default_difficulty(dic) print 'calc_default_difficulty ... done.' del dic con = MySQLdb.connect(db=db, host=host,\ user=user, passwd=passwd, charset=charset) cur = con.cursor() user_id, comment = 19, '' get_rstid = 'select Rcd from tabelog where tabelogurl = %s limit 1' session = Session() i, count = 1, len(scores) for_add = [] for url, difficulty in scores.iteritems(): print '{i} / {count} url'.format(i=i, count=count) difficulty = round(difficulty, 1) i += 1 result = cur.execute(get_rstid, url) rst_id = cur.fetchone()[0] if result else None now = datetime.now() userpost = session.query(UserPost)\ .filter('user_id = :user_id and rst_id = :rst_id')\ .params(user_id=user_id, rst_id=rst_id).first() if userpost: print 'update' userpost.modified = now userpost.difficulty = difficulty userpost.comment = comment else: print 'insert' user_post = UserPost(user_id, rst_id, difficulty, comment, now, now) # session.begin() for_add.append(user_post) try: session.add_all(for_add) session.flush() session.commit() session.close() except Exception ,e: session.rollback() print e session.close() return False
def fetch_title_name(self): """title()とku()でself.acquireに格納したidから 称号の名前を取得し、返す """ self.total() self.ku() session = Session() title_names = [] try: for title_id in self.acquire: _title = session.query(Title.name).filter(Title.id == title_id).first().name title_names.append(_title) except Exception, e: print e session.rollback() session.commit() session.close() return False
def update_title(title_id, rank, name, requirement, stamp): now = datetime.now() session = Session() title = session.query(Title).filter(Title.id == title_id) title.rank = rank title.name = name title.requirement = requirement title.stamp = stamp title.modified = now try: session.flush() session.commit() except Exception, e: session.rollback() print e session.commit() session.close() return False
def read_user_post(user_id, rst_id): '''user_postからuser_idとrst_idの組を持つ行を取得 user_idが無ければ、rst_idについての組を全て取得 ''' session = Session() if user_id: print 'user_id is exists' userpost = session.query(UserPost.user_id, UserPost.rst_id, \ UserPost.difficulty, UserPost.comment)\ .filter('user_id = :user_id and rst_id = :rst_id')\ .params(user_id = user_id, rst_id = rst_id) else: print 'user_id is None' userpost = session.query(UserPost.user_id, UserPost.rst_id, \ UserPost.difficulty, UserPost.comment)\ .filter('rst_id = :rst_id')\ .params(rst_id = rst_id) session.commit() session.close() return userpost
def execute_sql(table='tabelog', where=''): '''args: table => user or user_post where => where clause of sql returns object of sqlalchemy(all(), order_by, ...) ''' session = Session() try: if table in ('tabelog', 'Tabelog'): raise ValueError('use _execute_sql_to_tabelog instead.') elif table in ('user', 'User'): s = session.query(User).filter(where) elif table in ('user_post', 'UserPost'): s = session.query(UserPost).filter(where) except Exception: # print e s = False session.commit() session.close() return s
def execute_sql(table="tabelog", where=""): """args: table => user or user_post where => where clause of sql returns object of sqlalchemy(all(), order_by, ...) """ session = Session() try: if table in ("tabelog", "Tabelog"): raise ValueError("use _execute_sql_to_tabelog instead.") elif table in ("user", "User"): s = session.query(User).filter(where) elif table in ("user_post", "UserPost"): s = session.query(UserPost).filter(where) except Exception: # print e s = False session.commit() session.close() return s
def delete_user(user_id, confirmation=False): """Userテーブルからユーザーを削除 確認(confirmation)を指定する """ if not confirmation: return False session = Session() try: user = session.query(User).filter("id = :user_id").params(user_id=user_id).first() session.delete(user) session.flush() session.commit() session.close() return True except Exception, e: session.rollback() print e session.commit() session.close() return False # }}}
def update_user(user_id, new_name, new_place): """Userテーブルを更新 返り値はTrue or False """ session = Session() now = datetime.now() try: user = session.query(User).filter("id = :user_id").params(user_id=user_id).first() user.user_name = new_name.replace("\n", "") user.home_place = new_place user.modified = now session.flush() session.commit() session.close() return True except Exception, e: session.rollback() print e session.commit() session.close() return False
def delete_user_post(user_id, rst_id): '''UserPostテーブルから、user_idとrst_idの組をもつ行を削除する ''' if not user_id: return False session = Session() try: userpost = session.query(UserPost)\ .filter('user_id = :user_id and rst_id = :rst_id')\ .params(user_id = user_id, rst_id = rst_id).first() print userpost session.delete(userpost) session.flush() session.commit() session.close() return True except Exception, e: print 'delete_user_post :', e session.rollback() session.commit() session.close() return False
def read_user_post(user_id, rst_id): """user_postからuser_idとrst_idの組を持つ行を取得 user_idが無ければ、rst_idについての組を全て取得 """ session = Session() if user_id: print "user_id is exists" userpost = ( session.query(UserPost.user_id, UserPost.rst_id, UserPost.difficulty, UserPost.comment) .filter("user_id = :user_id and rst_id = :rst_id") .params(user_id=user_id, rst_id=rst_id) ) else: print "user_id is None" userpost = ( session.query(UserPost.user_id, UserPost.rst_id, UserPost.difficulty, UserPost.comment) .filter("rst_id = :rst_id") .params(rst_id=rst_id) ) session.commit() session.close() return userpost
def __init__(self, user_id): self.user_id = user_id session = Session() try: self.stats = ( session.query( UserStats.total, UserStats.sequence, UserStats.level_1, UserStats.level_2, UserStats.level_3, UserStats.level_4, UserStats.level_5, UserStats.sakyo, UserStats.ukyo, UserStats.kita, UserStats.kamigyo, UserStats.shimogyo, UserStats.nakagyo, UserStats.higashiyama, UserStats.yamashina, UserStats.saikyo, UserStats.minami, UserStats.fushimi, UserStats.already_acquire, ) .filter(UserStats.user_id == user_id) .first() ._asdict() ) except: print "rollback" session.rollback() self.acquire = set() session.commit() session.close()