def remove_listing_from_category(lid, cat_id): sql = """\ DELETE FROM listing_category WHERE lid = ? AND cat_id = ?""" with db.DatabaseCursor() as cursor: cursor.execute(sql, (lid, cat_id))
def listings_count(query): sql = """\ SELECT COUNT(*) AS count FROM listing_search WHERE content MATCH ?""" with db.DatabaseCursor() as cursor: row = cursor.execute(sql, (query + '*', )).fetchone() return row['count']
def get(self, key, user): with db.DatabaseCursor() as cursor: cursor.execute( 'SELECT * FROM `objects` WHERE `key` = %s AND `user` = %s AND `uploading` = %s', (key, user, False)) items = list(cursor.fetchall()) if len(items) != 1: return None try: content = self._retrieveKey(items[0]['key'], 'pri') except Exception as e: self.log.log(msg='Primary retrieval for %s failed: %s' % (items[0]['id'], str(e)), context='GET') try: content = self._retrieveKey(items[0]['key'], 'sec') except Exception as e: self.log.log(msg='Secondary retrieval for %s failed: %s' % (items[0]['id'], str(e)), context='GET') raise return { 'name': items[0]['name'], 'content': base64.b64encode(content).decode('ascii'), 'mimeType': items[0]['mimeType'] }
def update_existing_user(user_id, name, bio): sql = """\ UPDATE user SET name = ?, bio = ? WHERE uid = ?""" with db.DatabaseCursor() as cursor: cursor.execute(sql, (name, bio, user_id))
def update_password(uid, new_password): password_hash = get_password_hash(new_password) sql = """\ UPDATE user SET password_hash = ? WHERE uid = ?""" with db.DatabaseCursor() as cursor: cursor.execute(sql, (password_hash, uid))
def get_comment_info(comment_id): sql = """\ SELECT * FROM comment WHERE cid = ?""" with db.DatabaseCursor() as cursor: obj = cursor.execute(sql, (comment_id,)).fetchone() return obj
def get_listing_info(listing_id): sql = """\ SELECT * FROM listing WHERE lid = ?""" with db.DatabaseCursor() as cursor: obj = cursor.execute(sql, (listing_id, )).fetchone() return obj
def create_category(cat_label): sql = """\ INSERT INTO category (label) VALUES (?)""" with db.DatabaseCursor() as cursor: cursor.execute(sql, (cat_label, )) new_id = cursor.lastrowid return new_id
def create_or_retrieve_category(label): sql = """\ SELECT cat_id FROM category WHERE label = ?""" with db.DatabaseCursor() as cursor: obj = cursor.execute(sql, (label.upper(), )).fetchone() if obj and obj['cat_id']: return obj['cat_id'] return create_category(label.upper())
def update_existing_listing(listing_id, title, description): sql = """\ UPDATE listing SET title = ?, description = ?, last_update_time = ? WHERE lid = ?""" with db.DatabaseCursor() as cursor: now = datetime.datetime.now() cursor.execute(sql, (title, description, now, listing_id))
def cat_ids_to_labels(cat_ids): sql = """\ SELECT label FROM category WHERE cat_id IN (%s) """ % ','.join('?' * len(cat_ids)) with db.DatabaseCursor() as cursor: rows = cursor.execute(sql, cat_ids).fetchall() return [x['label'] for x in rows]
def listing_categories(lid): sql = """\ SELECT c.cat_id, c.label FROM listing_category l, category c WHERE l.lid = ? AND l.cat_id = c.cat_id""" with db.DatabaseCursor() as cursor: rows = cursor.execute(sql, (lid, )).fetchall() return rows
def get_number_of_listings_in_cat_ids(cat_ids): sql = """\ SELECT COUNT(*) as count FROM listing l, listing_category c WHERE l.lid = c.lid AND c.cat_id IN (%s) """ % ','.join('?' * len(cat_ids)) with db.DatabaseCursor() as cursor: row = cursor.execute(sql, cat_ids).fetchone() return row['count']
def listings(query, limit, offset=0): """Returns a list of listing_ids matching the query.""" sql = """\ SELECT lid FROM listing_search WHERE content MATCH ? LIMIT ? OFFSET ?""" with db.DatabaseCursor() as cursor: matching_listings = \ cursor.execute(sql, (query+'*', limit, offset)).fetchall() return [x['lid'] for x in matching_listings]
def maybe_populate_virtual_table(): if has_created(): return # Populate fts table with existing data. sql = """\ INSERT INTO listing_search(lid, content) SELECT lid, title || " " || description FROM listing""" with db.DatabaseCursor() as cursor: cursor.execute(sql)
def create_new_user(name, email, password): password_hash = get_password_hash(password) sql = """\ INSERT INTO user (name, email, password_hash) VALUES (?, ?, ?)""" with db.DatabaseCursor() as cursor: cursor.execute(sql, (name, email, password_hash)) new_user_id = cursor.lastrowid return new_user_id
def get_comments_for_listing(lid, time_ordered=True): sql = """\ SELECT * FROM listing l, comment c, user u WHERE l.lid = c.lid AND l.lid = ? AND c.uid = u.uid""" if not time_ordered: sql += " ORDER BY cid DESC" with db.DatabaseCursor() as cursor: rows = cursor.execute(sql, (lid,)).fetchall() return rows
def verify_login(email, password): sql = "SELECT uid, password_hash FROM user WHERE email = ?" with db.DatabaseCursor() as cursor: user = cursor.execute(sql, (email, )).fetchone() if user is None: return False if check_password(password, user['password_hash']): return user['uid'] return False
def get_lids_by_cat_ids(cat_ids, limit, offset=0): sql = """\ SELECT l.lid FROM listing l, listing_category c WHERE l.lid = c.lid AND c.cat_id IN (%s) ORDER BY l.lid DESC LIMIT ? OFFSET ? """ % ','.join('?' * len(cat_ids)) with db.DatabaseCursor() as cursor: cat_ids.extend([limit, offset]) obj = cursor.execute(sql, cat_ids).fetchall() return [x['lid'] for x in obj]
def filter_lids_by_cat_ids(lids, cat_ids): sql = """\ SELECT l.lid FROM listing l, listing_category c WHERE l.lid = c.lid AND c.cat_id IN (%s) AND l.lid IN (%s) ORDER BY l.lid DESC """ % (','.join('?' * len(cat_ids)), ','.join('?' * len(lids))) with db.DatabaseCursor() as cursor: cat_ids.extend(lids) obj = cursor.execute(sql, cat_ids).fetchall() return [x['lid'] for x in obj]
def create_new_listing(title, description, owner_id): # Check that owner_id exists. if not model.user.get_user_info(owner_id): raise Exception('No such user.') sql = """\ INSERT INTO listing (title, description, owner_id) VALUES (?, ?, ?)""" with db.DatabaseCursor() as cursor: cursor.execute(sql, (title, description, owner_id)) new_id = cursor.lastrowid return new_id
def _generateKey(self): while True: with db.DatabaseCursor() as cursor: key = ''.join( random.choice(string.ascii_letters + string.digits) for x in range(256)) try: cursor.execute( 'INSERT INTO `objects` (`key`, `uploading`) VALUES (%s, %s)', (key, True)) id = cursor.lastrowid() break except Exception as e: pass return (id, key)
def receiveFile(self, name, content, mimeType): (id, key) = self._generateKey() try: self._storeKey(key, content, 'pri') with db.DatabaseCursor() as cursor: cursor.execute( 'UPDATE `objects` SET `name` = %s, `mimeType` = %s, `uploading` = %s, `pri` = %s WHERE `key` = %s', (name, mimeType, False, True, key)) except Exception as e: self.log.log(msg='Primary storage for %s failed: %s' % (id, str(e)), context='RECV') try: self._storeKey(key, content, 'sec') with db.DatabaseCursor() as cursor: cursor.execute( 'UPDATE `objects` SET `name` = %s, `mimeType` = %s, `uploading` = %s, `sec` = %s WHERE `key` = %s', (name, mimeType, False, True, key)) except Exception as e: self.log.log(msg='Secondary storage for %s failed: %s' % (id, str(e)), context='RECV') return None return key
def get_listings_for_user(uid): sql = """\ SELECT * FROM listing l, user u, ( SELECT l.lid, COUNT(c.cid) AS comment_count FROM listing l LEFT JOIN comment c ON l.lid = c.lid GROUP BY l.lid) c WHERE l.owner_id = u.uid AND c.lid = l.lid AND l.owner_id = ? ORDER BY l.lid DESC""" with db.DatabaseCursor() as cursor: obj = cursor.execute(sql, (uid, )).fetchall() return obj
def get_listings_info(listing_ids): sql = """\ SELECT * FROM listing l, user u, ( SELECT l.lid, COUNT(c.cid) AS comment_count FROM listing l LEFT JOIN comment c ON l.lid = c.lid GROUP BY l.lid) c WHERE l.lid IN (%s) AND c.lid = l.lid AND l.owner_id = u.uid """ % ','.join('?' * len(listing_ids)) with db.DatabaseCursor() as cursor: rows = cursor.execute(sql, listing_ids).fetchall() return rows
def get_latest_listings(limit, offset=0): sql = """\ SELECT * FROM listing l, user u, ( SELECT l.lid, COUNT(c.cid) AS comment_count FROM listing l LEFT JOIN comment c ON l.lid = c.lid GROUP BY l.lid) c WHERE l.owner_id = u.uid AND c.lid = l.lid ORDER BY l.lid DESC LIMIT ? OFFSET ?""" with db.DatabaseCursor() as cursor: obj = cursor.execute(sql, (limit, offset)).fetchall() return obj
def create_new_comment(body, uid, lid): # Check that uid exists. if not model.user.get_user_info(uid): raise Exception('No such user.') # Check that listing exists. if not model.listing.get_listing_info(lid): raise Exception('No such listing.') sql = """\ INSERT INTO comment (body, uid, lid) VALUES (?, ?, ?)""" with db.DatabaseCursor() as cursor: cursor.execute(sql, (body, uid, lid)) new_id = cursor.lastrowid return new_id
def get_related_listings(listing_id, limit, offset=0): # TODO(michael): tmp implementation. sql = """\ SELECT * FROM listing l, user u, ( SELECT l.lid, COUNT(c.cid) AS comment_count FROM listing l LEFT JOIN comment c ON l.lid = c.lid GROUP BY l.lid) c WHERE l.owner_id = u.uid AND c.lid = l.lid AND l.owner_id = ( SELECT owner_id FROM listing WHERE lid = ?) AND l.lid <> ? ORDER BY l.lid DESC LIMIT ? OFFSET ?""" with db.DatabaseCursor() as cursor: obj = cursor.execute( sql, (listing_id, listing_id, limit, offset)).fetchall() return obj
def get_number_of_users(): sql = "SELECT COUNT(*) as count FROM user" with db.DatabaseCursor() as cursor: row = cursor.execute(sql).fetchone() return row['count']
def get_all_users(): sql = "SELECT u.uid, u.name, u.email FROM user AS u" with db.DatabaseCursor() as cursor: rows = cursor.execute(sql).fetchall() return rows