Python Utils.query方法代码示例

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
	def add(self):
		body = json.loads( cherrypy.request.body.read() )
    
		check = Utils.arg_check(body, ['token', 'latitude', 'longitude'])	
		if (check[0]):
			return check[1]
		
		user_check = Utils.validate_user(body["token"])
		if(user_check[0]):
			return user_check[1]
		user_id = user_check[1]
		
		base = "http://maps.googleapis.com/maps/api/geocode/json?"
		params = "latlng={lat},{lon}&sensor={sen}".format(
			lat=body['latitude'],
			lon=body['longitude'],
			sen=True
		)
		url = "{base}{params}".format(base=base, params=params)
		response = requests.get(url)
		
		if (response):
			content = response.json()
			if (content and content['results'] and content['results'][0] and content['results'][0]['formatted_address']):
				savedLocations = Utils.query("""SELECT * FROM Locations WHERE address = %s""",
							    (content['results'][0]['formatted_address']))
				#print savedLocations
				if (len(savedLocations) == 1):
					location_id = savedLocations[0]["location_id"]
				elif (len(savedLocations) > 1):
					return json.JSONEncoder().encode( Utils.status_more( 34, "Inconsistet database" ) )
				else:
					location_id  = Utils.execute_id("""INSERT INTO Locations(latitude, longitude, address, place) 
							 VALUES(%s, %s, %s, %s)""", 
							(body["latitude"], body["longitude"], content['results'][0]['formatted_address'], "I don't know"))
				if (location_id  != -1):
					previousUserLocation = Utils.query("""SELECT location_id FROM Users_Locations 
													WHERE user_id = %s 
													ORDER BY time DESC LIMIT 1""", (user_id))
					is_route = False
					if previousUserLocation:
						previousLocation = Utils.query("""SELECT * FROM Locations WHERE location_id = %s""", (previousUserLocation[0]["location_id"]))
					
						if len(previousLocation) == 1 and self.checkDistance(previousLocation[0]["latitude"], previousLocation[0]["longitude"],body["latitude"],body["longitude"]) == 1: 
							is_route = True
					Utils.execute("""INSERT INTO Users_Locations(user_id, location_id, time, is_route) 
							VALUES(%s, %s, %s, %s)""",
							(user_id, location_id, datetime.now(), is_route))
					return json.JSONEncoder().encode( Utils.status_more( 0, "OK" ) )
			return json.JSONEncoder().encode( Utils.status_more( 35, "Could not save to database" ) )

		return json.JSONEncoder().encode( Utils.status_more( 33, "Could not retrieve location information" ) )

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
  def login(self):

    body = json.JSONDecoder().decode( cherrypy.request.body.read() )
    
    check = Utils.arg_check(body, ["username","password"])
    if (check[0]):
		return check[1]

    matchingUsers = Utils.query("SELECT * FROM Users u WHERE u.username=%s AND u.password=SHA1(CONCAT(%s, u.salt))", (body["username"], body["password"]))
    
    if(len(matchingUsers) == 0):
      return json.JSONEncoder().encode({
        "status" : Utils.status(324,"Could not locate user")
      })
    elif (len(matchingUsers) == 1):
      sessionID = str(uuid.uuid4())
      Utils.execute("INSERT INTO User_Sessions(user_id, session_token) VALUES(%s, %s)",(matchingUsers[0]["user_id"], sessionID))
      
      return json.JSONEncoder().encode({
        "token": sessionID,
        "status" : Utils.status(0, "OK")
      })
    else:
      return json.JSONEncoder().encode({
        "status" : Utils.status(323,"The system is in an invalid state.")
      })

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
	def getLocationID(self, location):
		
		loc_id = Utils.query("SELECT location_id FROM Locations WHERE place = %s", (location[3]))
		
		if len(loc_id) > 0:
			return loc_id[0]["location_id"]
		else:
			return Utils.execute_id("""INSERT INTO Locations(latitude, longitude, address, place) 
			VALUES(%s, %s, %s, %s)""", (location[0], location[1], location[2], location[3]))

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
 def login(self, username):
   #body = json.loads( cherrypy.request.body.read() )
   matchingUsers = Utils.query("SELECT team_id FROM Team WHERE teamname=%s", username)
   
   if(len(matchingUsers) == 0):
     return "BAD"
   elif (len(matchingUsers) == 1):
     return "OK;%s" % matchingUsers[0]['team_id']
   else:
     return "ERORR"

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
  def getPlayers(self):
      players = Utils.query("""SELECT player_name, t.bteam_name, team_id
                                FROM player p
                                LEFT JOIN owns o
                                ON p.pid = o.pid
                                JOIN bteam t
                                ON t.bteam_id = p.bteam_id""", ())

      playersString = ""
      for player in players:
        playersString += ("%s,%s,%s;" % (player["player_name"],player["bteam_name"],player["team_id"]))
    
      return playersString[:-1]

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
def add_data2(o,occurances):
  location_id = Utils.execute_id("""INSERT INTO Locations(address) VALUES('The place I am')""",())
  user_id = Utils.query("""SELECT user_id
                            FROM User_Sessions
                            WHERE session_token = %s;""", (o.loginResult['token']))

  event_id= Utils.execute_id("""INSERT INTO Events(event_type,user_id,name,location_id,locked,deleted)
                                VALUES ('cycle',%s,'test cycle',%s,TRUE,FALSE);""",
                                (user_id[0]["user_id"], location_id))

  Utils.execute("""INSERT INTO Cyclical_Events(event_id, cycle_type, occurances)
                          VALUES(%s,'weekly',%s)""", (event_id, json.JSONEncoder().encode(occurances)))

  return {"location_id" : location_id, "event_id" : event_id }

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
 def getTeamPlayers(self, teamid):
   players = Utils.query("""SELECT player_name, pos_name, cost, p.pos_id
                   FROM owns o
                   JOIN Player p
                   ON o.pid = p.pid
                   JOIN Position pos
                   ON p.pos_id = pos.pos_id
                   WHERE team_id = %s""", teamid)
   
   playersString = ""
   for player in players:
     playersString += ("%s,%s,%s;" % (player["player_name"],player["pos_name"],player["cost"]))
   
   return playersString[:-1]

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
  def request(self): 
    body = json.JSONDecoder().decode( cherrypy.request.body.read() )
    check = Utils.arg_check(body, ["token","buddy"])

    if (check[0]): 
      return check[1]

    # Find the user ID of the person making the request.
    user_check = Utils.validate_user(body["token"])
    if(user_check[0]):
      return user_check[1]
    user_id = user_check[1]

    matching_users = Utils.query("""SELECT * FROM Users WHERE username=%s OR email=%s""", 
              (body["buddy"],body["buddy"]))

    if len(matching_users) == 0:
      return json.JSONEncoder().encode( Utils.status_more( 112, "Could not locate user" ) )
    
    matching_user = matching_users[0]
    
    if matching_user["user_id"] == user_id:
      return json.JSONEncoder().encode( Utils.status_more( 115, "Cannot be buddies with yourself" ) )

    in_buddies = Utils.query("""SELECT * FROM Buddies WHERE (requester_id = %s AND requestee_id = %s) OR (requester_id = %s AND requestee_id = %s)""", (user_id,matching_user["user_id"],matching_user["user_id"],user_id))

    if any(in_buddies):
      return json.JSONEncoder().encode( Utils.status_more( 115, "Cannot request buddy when you are already buddies or there is already a pending request between you." ) )

    try:
        Utils.execute("""INSERT INTO Buddies(requester_id, requestee_id, approved) VALUE(%s,%s,0)""",
          (user_id,matching_user["user_id"]))
    except Exception:
        return json.JSONEncoder().encode({"status": Utils.status(3981,"Could not request buddy")})

    return json.JSONEncoder().encode(Utils.status_more(0, "OK"))

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
  def get_movies(user_id):
    last_location = Utils.query("SELECT * FROM Users_Locations ul JOIN Locations l ON ul.location_id = l.location_id WHERE user_id = %s ORDER BY time DESC LIMIT 1",(user_id))
    if not last_location:
      return None

    print("http://www.google.com/movies?near=" + urllib.quote_plus(last_location[0]["address"]))
    response = urlopen("http://www.google.com/movies?near=" + urllib.quote_plus(last_location[0]["address"]))
    html = response.read()
    soup = BeautifulSoup(html)
    theaters = soup.find_all("div", {"class": "theater"})
    theater_movie = [(i,len(i.find_all("div",{"class":"movie"}))) for i in theaters]
    
    theater = max(theater_movie,key = lambda x: x[1])[0] #use the theater that has the most movies playing.  It seems like a good idea at least

    s = theater.find("div",{"class":"info"}).get_text()
    m = re.search("(.*) - \(\d\d\d\) \d\d\d-\d\d\d\d",s)
    address = m.group(1)
    now = datetime.now()
    begin = (now + timedelta(minutes=30)).time()
    end = (now + timedelta(hours=2)).time()

    movies = theater.find_all("div",{"class":"movie"})
    movie_results = []
    for movie in movies:
      name = movie.find("div",{"class":"name"}).get_text()
      times = movie.find("div",{"class":"times"}).get_text()
      times = unicodedata.normalize('NFKD', times).encode('ascii','ignore')
      am = []
      pm = []
      if "am" in times:
        times = times.split("am")
        am = times[0].strip().split("  ")
        pm = times[1].strip().replace("pm","").split("  ")
      else:
        pm = times.replace("pm","").split("  ")
      am = [datetime.strptime(a + " AM","%I:%M %p").time() for a in am]
      pm = [datetime.strptime(a + " PM","%I:%M %p").time() for a in pm]
      times = am + pm
      soon_times = [i for i in times if i > begin and i < end]
      if soon_times:
        m = {"name":name,"near_times":[i.isoformat() for i in soon_times]} 
        movie_results.append(m)

    if movie_results:
      results = {"location":address,"movies":movie_results}
    else:
      results = None
    return results

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
  def auction(self):
      bid = Utils.query("""SELECT player_name, teamname, bid, image, bteam_name, GROUP_CONCAT(DISTINCT pos_name ORDER BY pos_name DESC SEPARATOR '# ') as pos_name
                                      From bid b
                                      JOIN player p
                                      ON b.pid = p.pid
                                      JOIN team t
                                      ON t.team_id = b.team_id
                                      JOIN bteam bt
                                      ON bt.bteam_id = p.bteam_id
                                      LEFT JOIN canplay cp
                                      ON cp.pid = b.pid
                                      LEFT JOIN position pos
                                      ON pos.pos_id = cp.pos_id
				      GROUP BY player_name""", ())

      if(len(bid) == 0):
        return "OK;Auction Will Resume Shortly,,-,http://www.clker.com/cliparts/K/s/M/V/6/T/timer-md.png,,,placheholder"
      return "OK;%s,%s,%s,%s,%s,%s" % (bid[0]['player_name'],bid[0]['teamname'],bid[0]['bid'],
                                        bid[0]['image'],bid[0]['bteam_name'],bid[0]['pos_name'])

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
  def pending_requests(self): 
    body = json.JSONDecoder().decode( cherrypy.request.body.read() )
    check = Utils.arg_check(body, ["token"])

    if (check[0]): 
      return check[1]

    # Find the user ID of the person making the request.
    user_check = Utils.validate_user(body["token"])
    if(user_check[0]):
      return user_check[1]
    user_id = user_check[1]

    requests = Utils.query("""SELECT user_id,username,email 
                              FROM Users u
                              JOIN Buddies b ON u.user_id = b.requester_id
                              WHERE b.requestee_id=%s AND b.approved=0""", 
              (user_id))
              
    return json.JSONEncoder().encode( {"status": Utils.status(0,"OK"),"requests":requests } )

# 需要导入模块: from utils import Utils [as 别名]
# 或者: from utils.Utils import query [as 别名]
  def team(self, teamid):
    playersOnTeam = Utils.query("""SELECT p.player_name, o.cost, GROUP_CONCAT(DISTINCT pos.pos_name ORDER BY pos.pos_name DESC SEPARATOR '# ') as pos_name, o.seasonsHeld
                                    FROM Player p
                                    JOIN bteam b
                                    ON b.bteam_id = p.bteam_id
                                    JOIN canplay cp
                                    ON cp.pid = p.pid
                                    JOIN position pos
                                    ON pos.pos_id = cp.pos_id
                                    JOIN Owns o
                                    ON p.pid = o.pid
                                    JOIN Team t
                                    ON t.team_id = o.team_id AND t.team_id =%s
                                    GROUP BY p.player_name
                                    ORDER BY pos.sortOrder""", teamid)

    team = ""
    for player in playersOnTeam:
      team += ("%s,%s,%s,%s;" % (player["player_name"],player["cost"],player["pos_name"], player["seasonsHeld"]))
    
    return team