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