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 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 near_rests(lat=34.985458, lng=135.757755, zoom=1, limit=100, lonely=True): """引数のlat(緯度)とlng(経度)を中心として、縦横margin*2の正方形ないにある レストランをTabelogテーブルから取得する デフォルト値は京都駅の緯度経度 """ session = Session() margin = _get_margin(zoom) left, right = lng - margin, lng + margin bottom, top = lat - margin, lat + margin # lonely = u"一人で" if lonely else "" lonely = u'and t.situation like "%一人で%"' if lonely else "" # 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) # point = 'POINT({lat} {lng})' # # s = session.query(Tabelog.RestaurantName, gf.wkt(Tabelog.LatLng))\ # s = session.query( # Tabelog.Rcd.label('rst_id'), Tabelog.RestaurantName, Tabelog.Category, # # 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'), # gf.x(Tabelog.LatLng).label('lat'), # gf.y(Tabelog.LatLng).label('lng'), # gf.length(LineString(point.format(lat=lat, lng=lng),\ # gf.wkt(Tabelog.LatLng))).label('length'),\ # func.round(func.avg(UserPost.difficulty)).label('difficulty'),\ # func.avg(UserPost.difficulty).label('raw_difficulty'))\ # .filter(UserPost.rst_id == Tabelog.Rcd)\ # .filter(Tabelog.LatLng.within(box))\ # .order_by('length')\ # .group_by(UserPost.id)\ # .limit(limit).all() # # .order_by(gf.distance(point.format(lat=lat, lng=lng),\ # # point.format(lat=gf.x(Tabelog.LatLng), lng=gf.y(Tabelog.LatLng))))\ # # .desc()\ try: s = session.execute( unicode( "select t.Rcd as rst_id, t.RestaurantName, t.Category, " "X(t.LatLng) as lat, Y(t.LatLng) as lng, " "floor(avg(up.difficulty)+0.5) as difficulty," "avg(up.difficulty) as raw_difficulty, " "GLength(GeomFromText(Concat('LineString(" "{lat} {lng}, ', Y(t.LatLng), ' ', X(t.LatLng), ')'))) as distance " "from tabelog as t, user_post as up " "where t.Rcd = up.rst_id and MBRContains(GeomFromText('" "LineString({tr_lng} {tr_lat}, {bl_lng} {bl_lat})'), t.LatLng) " "{lonely} " # 一人で? "group by up.id order by distance asc limit {limit}" ).format(lat=lat, lng=lng, tr_lng=right, tr_lat=top, bl_lng=left, bl_lat=bottom, limit=limit, lonely=lonely) ) results = [dict(result) for result in s.fetchall()] except Exception, e: print "errorin near_rsts", e session.rollback() results = False
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 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 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 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 insert_or_update_user_post(user_id, rst_id, difficulty, comment): """UserPostテーブルにinsert or updateを行う そのrowのidを返す """ session = Session() now = datetime.now() userpost = _check_user_post_is_exists(session, user_id, rst_id) if userpost: print "update user_post" try: userpost.modified = now userpost.difficulty = difficulty userpost.comment = comment inserted_id = userpost.id session.flush() except Exception, e: print e session.rollback() session.close()
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 __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()