def set_state(self, new_state): """ Sets the Pita's state to be the given attribute. """ cur = get_db().cursor() cur.execute("UPDATE pitas SET state = %s WHERE pid = %s", (new_state, self.pid)) cur.close()
def get(aid, key): cur = get_db().cursor(cursor_factory = RealDictCursor) cur.execute('SELECT * FROM accounts WHERE aid = %s AND key = %s', (aid, key)) row = cur.fetchone() acc = Account(row) if cur.rowcount > 0 else None return acc
def create_pita(pita): """ Takes a Pita object and inserts it into the database. It will also update tables related to Pita events. """ cur = get_db().cursor() q = ( "INSERT INTO pitas (aid, state, parent_a, parent_b, name, body_hue, " + "spots_hue, tail_hue, has_spots) " + "VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING pid" ) cur.execute( q, [ pita.aid, pita.state, pita.parent_a, pita.parent_b, pita.name, pita.body_hue, pita.spots_hue, pita.tail_hue, pita.has_spots, ], ) pita.pid = cur.fetchone()[0] cur.close() PitaEvent.record_event(pita, "conception")
def get_by_account(aid): """ Retrieves the Pita associated with the given account id, if any. """ cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute("SELECT * FROM pitas WHERE aid = %s AND (state = 'alive' OR state = 'egg')", (aid,)) row = cur.fetchone() pita = Pita(row) if cur.rowcount > 0 else None return pita
def load(): cur = get_db().cursor() f = open("dictionary.json", "r") words = json.load(f) count = 0 for w, pos in words.iteritems(): cur.execute("INSERT INTO dictionary_words (word, pos) VALUES(%s, %s)", (w, pos)) count = count + 1 f.close() print "Inserted %d words." % count
def save_status(self, status): """ Sets the Pita's status attributes to be the attributes given in the passed dictionary. """ cur = get_db().cursor() cur.execute( "UPDATE pitas SET happiness = %s, hunger = %s, sleepiness = %s WHERE pid = %s", (status["happiness"], status["hunger"], status["sleepiness"], self.pid), ) cur.close()
def update_location(self, lat, lng, when): when = when if when else datetime.datetime.now() cur = get_db().cursor() cur.execute('UPDATE accounts SET latitude=%s, longitude=%s, loc=ST_SetSRID(ST_MakePoint(%s, %s),4326), loc_time = %s WHERE aid = %s', (float(lat), float(lng), float(lat), float(lng), when, self.aid)) # Record the location in the locations table as well if when: cur.execute('INSERT INTO locations (aid, time, latitude, longitude, loc) VALUES(%s, %s, %s, %s, ST_SetSRID(ST_MakePoint(%s,%s),4326))', (self.aid, when, float(lat), float(lng), float(lat), float(lng))) else: cur.execute('INSERT INTO locations (aid, latitude, longitude, loc) VALUES(%s, %s, %s, ST_SetSRID(ST_MakePoint(%s,%s),4326))', (self.aid, float(lat), float(lng), float(lat), float(lng)))
def generate_name(): """ Generates a random pita name. """ cur = get_db().cursor() cur.execute("SELECT word FROM dictionary_words WHERE pos='noun' ORDER BY random() LIMIT 1") second_word = cur.fetchone()[0] first_word_type = random.choice(["adjective", "noun"]) cur.execute("SELECT word FROM dictionary_words WHERE pos = %s ORDER BY random() LIMIT 1", (first_word_type,)) first_word = cur.fetchone()[0] name = first_word + " " + second_word name = name.title() return name
def record_event(pita, event_type, time=None): """ Records an event in a Pita's lifetime. If time is omitted, the current time will be used. """ time = time if time else datetime.datetime.now() cur = get_db().cursor() cur.execute( "INSERT INTO pita_events (pid, aid, event_type, time) " + "VALUES(%s, %s, %s, %s) RETURNING peid", (pita.pid, pita.aid, event_type, time), ) peid = cur.fetchone()[0] cur.close() return PitaEvent({"peid": peid, "pid": pita.pid, "aid": pita.aid, "event_type": event_type, "time": time})
def new(uuid, name, phone, email): # Generate a secret key that the client keeps to refer to the account. rand_bytes = os.urandom(1000) h = hashlib.sha512() h.update(rand_bytes) h.update(uuid) if email: h.update(email) if name: h.update(name) if phone: h.update(phone) account_key = h.hexdigest() cur = get_db().cursor() cur.execute('INSERT INTO accounts (uuid, name, phone, email, key) ' + ' VALUES(%s, %s, %s, %s, %s) RETURNING aid', (uuid, name, phone, email, account_key)) aid = cur.fetchone()[0] d = { 'aid': aid, 'uuid': uuid, 'name': name, 'phone': phone, 'email': email, 'key': account_key } return Account(d) if aid else None
def update_last_seen(self): cur = get_db().cursor() cur.execute('UPDATE accounts SET last_seen = now() WHERE aid = %s', (self.aid,))
def uuid_used(uuid): cur = get_db().cursor() cur.execute('SELECT aid FROM accounts WHERE uuid = %s', (uuid,)) used = cur.rowcount > 0 cur.close() return used
def email_used(email): cur = get_db().cursor() cur.execute('SELECT aid FROM accounts WHERE email = %s', (email,)) used = cur.rowcount > 0 cur.close() return used
def phone_used(phone): cur = get_db().cursor() cur.execute('SELECT aid FROM accounts WHERE phone = %s', (phone,)) used = cur.rowcount > 0 cur.close() return used
def get_nearby_accounts(): cur = get_db().cursor(cursor_factory = RealDictCursor) cur.execute('SELECT accounts.*, ST_Distance_Sphere(ST_SetSRID(ST_MakePoint(%s,%s),4326),accounts.loc) AS dist_meters FROM accounts WHERE loc_time > now() - interval \'5 minutes\' ORDER BY accounts.loc <-> ST_SetSRID(ST_MakePoint(%s,%s),4326) LIMIT 25', (g.account.latitude, g.account.longitude, g.account.latitude, g.account.longitude)) accounts = cur.fetchall() return accounts