def add_to_list(album_id): with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute('INSERT INTO list (album) VALUES (%s)', (album_id, )) conn.commit() except psycopg2.IntegrityError: raise DatabaseError(f'list item {album_id} already exists') except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def _reset_list(): with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute('DELETE FROM list') conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def reset_users(): with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute("UPDATE albums SET users_json = '[]';") conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def get_album_ids(): with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute('SELECT id FROM albums;') return [c[0] for c in cur.fetchall()] except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def delete_from_list(album_id): with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute('DELETE FROM list where album = %s;', (album_id, )) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def add_many_to_list(album_ids): with closing(get_connection()) as conn: try: cur = conn.cursor() cur.executemany('INSERT INTO list (album) VALUES (%s)', album_ids) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def get_albums_unavailable_count(): with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute('SELECT id FROM albums WHERE available = false;') return cur.rowcount except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def get_list(): with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute('SELECT album FROM list;') return [item[0] for item in cur.fetchall()] except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def get_list_count(): with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute('SELECT album FROM list;') return cur.rowcount except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def create_list_table(): with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute( 'CREATE TABLE IF NOT EXISTS list (id serial PRIMARY KEY, album varchar);' ) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def add_to_albums(album_id, artist, name, url, img='', channel=''): sql = """ INSERT INTO albums ( id, artist, name, url, img, channel, available ) VALUES (%s, %s, %s, %s, %s, %s, %s);""" with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute(sql, (album_id, artist, name, url, img, channel, True)) conn.commit() except psycopg2.IntegrityError: raise DatabaseError(f'album {album_id} already exists') except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def get_albums_with_users(): sql = """ SELECT id, name, artist, url, img, available, channel, added, released, tags_json, users_json FROM albums """ with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute(sql) return Album.albums_from_values(cur.fetchall()) except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def update_album_availability(album_id, status): with closing(get_connection()) as conn: try: sql = """ UPDATE albums SET available = %s WHERE id = %s; """ cur = conn.cursor() cur.execute(sql, (bool(status), album_id)) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def get_album_details_from_ids(album_ids): sql = """ SELECT id, artist, name, url, img, available, channel, added, released FROM albums WHERE id IN %s; """ with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute(sql, (album_ids, )) return Album.albums_from_values(cur.fetchall()) except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def create_albums_index(): sql = """ CREATE INDEX IF NOT EXISTS alb_lo_name ON albums ( LOWER(name) );""" with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute(sql) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def remove_user_from_all_albums(user): with closing(get_connection()) as conn: try: sql = """ UPDATE albums SET users_json = users_json - %s WHERE users_json ? %s; """ cur = conn.cursor() cur.execute(sql, (user, user)) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def set_album_users(album_id, users): with closing(get_connection()) as conn: try: sql = """ UPDATE albums SET users_json = %s WHERE id = %s; """ cur = conn.cursor() cur.execute(sql, (json.dumps(users), album_id)) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def update_album_added(album_id, added): with closing(get_connection()) as conn: try: sql = """ UPDATE albums SET added = %s WHERE id = %s; """ cur = conn.cursor() cur.execute(sql, (added, album_id)) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def remove_tag_from_album(album_id, tag): with closing(get_connection()) as conn: try: sql = """ UPDATE albums SET tags_json = tags_json - %s WHERE id = %s; """ cur = conn.cursor() cur.execute(sql, (json.dumps(tag), album_id)) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def get_albums_by_channel(channel): sql = """ SELECT id, name, artist, url, img, available, channel, added FROM albums WHERE channel = %s """ with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute(sql, (channel, )) return Album.albums_from_values(cur.fetchall()) except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def add_released_to_album(album_id, date): with closing(get_connection()) as conn: try: sql = """ UPDATE albums SET released = %s WHERE id = %s; """ cur = conn.cursor() cur.execute(sql, (date, album_id)) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def add_user_review_to_album(album_id, user, review): with closing(get_connection()) as conn: try: sql = """ UPDATE albums SET reviews_json = reviews_json || %s WHERE id = %s; """ cur = conn.cursor() cur.execute(sql, (json.dumps([{user: review}]), album_id)) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def remove_user_review_from_album(album_id, array_element): with closing(get_connection()) as conn: try: sql = """ UPDATE albums SET reviews_json = reviews_json - %s WHERE id = %s; """ cur = conn.cursor() cur.execute(sql, (array_element, album_id)) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def get_album_details_with_reviews(album_id): sql = """ SELECT id, name, artist, url, img, available, channel, added, released, reviews_json FROM albums WHERE id = %s; """ with closing(get_connection()) as conn: try: cur = conn.cursor(cursor_factory=NamedTupleCursor) cur.execute(sql, (album_id, )) return Album.from_values(cur.fetchone()) except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def get_album_details_by_url(album_url): sql = """ SELECT id, name, artist, url, img, available, channel, added, tags_json FROM albums WHERE url = %s; """ with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute(sql, (album_url, )) return Album.from_values(cur.fetchone()) except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def de_dup(): duplicates = [ (album_id, ) for album_id, count in collections.Counter(get_list()).items() if count > 1 ] with closing(get_connection()) as conn: try: cur = conn.cursor() cur.executemany('DELETE FROM list where album = %s;', duplicates) cur.executemany('INSERT INTO list (album) VALUES (%s)', duplicates) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def find_album_artist_duplicates(): sql = """ SELECT a1.* from (SELECT id, LOWER(name) as name, LOWER(artist) as artist, url, img, available, channel, added, released from albums) a1 LEFT JOIN (SELECT LOWER(artist) as artist, LOWER(name) as name, COUNT(*) as duplicates from albums GROUP BY LOWER(artist), LOWER(name)) a2 ON a1.artist = a2.artist AND a1.name = a2.name WHERE a2.duplicates > 1 ORDER BY a1.artist, a1.name """ with closing(get_connection()) as conn: try: cur = conn.cursor() cur.execute(sql) return Album.albums_from_values(cur.fetchall()) except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def get_random_album(): sql = """ SELECT id, name, artist, url, img, available, channel, added, released, tags_json, reviews_json FROM albums WHERE available = true ORDER BY RANDOM() LIMIT 1; """ with closing(get_connection()) as conn: try: cur = conn.cursor(cursor_factory=NamedTupleCursor) cur.execute(sql) return Album.from_values(cur.fetchone()) except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def search_albums_by_tag(query): sql = """ SELECT id, name, artist, url, img, available, channel, added, released, tags_json, reviews_json FROM albums WHERE tags_json ? %s AND available = true; """ with closing(get_connection()) as conn: try: cur = conn.cursor(cursor_factory=NamedTupleCursor) # term = f'%{query}%' TODO cur.execute(sql, (query, )) return Album.albums_from_values(cur.fetchall()) except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)
def add_many_to_albums(albums): sql = """ INSERT INTO albums ( id, artist, name, url, img ) VALUES (%s, %s, %s, %s, %s);""" with closing(get_connection()) as conn: try: cur = conn.cursor() cur.executemany(sql, albums) conn.commit() except (psycopg2.ProgrammingError, psycopg2.InternalError) as e: raise DatabaseError(e)