def delete_artist_engine(artist_id: int) -> (bool, str): """ Delete an artist in ENGINE mode :param artist_id: id of the artist to delete """ success = False artist_name = None exists = False try: artist = execute( f'SELECT name from "{ARTIST_TABLE}" WHERE id = {artist_id};') if artist.rowcount != 0: exists = True artist_name = artist.mappings().first().get('name') # when an artist is deleted, need to delete availability, genres & shows as well to keep the db consistent execute_transaction([ f'DELETE FROM "{AVAILABILITY_TABLE}" WHERE artist_id = {artist_id};', f'DELETE FROM "{SHOWS_TABLE}" WHERE artist_id = {artist_id};', f'DELETE FROM "{ARTIST_GENRES_TABLE}" WHERE artist_id = {artist_id};', f'DELETE FROM "{ARTIST_TABLE}" WHERE id = {artist_id};' ]) success = True except: print_exc_info() if not exists: abort(HTTPStatus.NOT_FOUND.value) return success, artist_name
def delete_venue_engine(venue_id: int) -> (bool, str): """ Delete a venue in ENGINE mode :param venue_id: id of the venue to delete """ success = False venue_name = None exists = False try: venue = execute(f'SELECT name from "{VENUE_TABLE}" WHERE id = {venue_id};') if venue.rowcount != 0: exists = True venue_name = venue.mappings().first().get('name') # when an venue is deleted, need to delete genres & shows as well to keep the db consistent execute_transaction([ f'DELETE FROM "{SHOWS_TABLE}" WHERE venue_id = {venue_id};', f'DELETE FROM "{VENUE_GENRES_TABLE}" WHERE venue_id = {venue_id};', f'DELETE FROM "{VENUE_TABLE}" WHERE id = {venue_id};' ]) success = True except: print_exc_info() if not exists: abort(HTTPStatus.NOT_FOUND.value) return success, venue_name
def create_artist_orm(artist: Artist, availability: Availability) -> (bool, str): """ Create an artist in ORM mode :param artist: artist to create :param availability: artist availability """ artist_name = artist.name try: db.session.add(artist) db.session.commit() if availability.is_available(): availability.artist_id = artist.id db.session.add(availability) db.session.commit() success = True except: db.session.rollback() print_exc_info() success = False finally: db.session.close() return success, artist_name
def create_artist_engine(artist: dict, availability: dict): """ Create an artist in ENGINE mode :param artist: artist to create :param availability: artist availability """ success = False artist_name = artist["name"] try: new_artist = execute(artist_insert_sql(artist)) if new_artist.rowcount > 0: # using raw sql so need to query to get new id new_artist = execute( id_name_by_unique_properties_sql( *extract_unique_properties_engine(artist))) if new_artist.rowcount > 0: new_artist = new_artist.fetchone() artist_id = new_artist["id"] stmts = [] # add genres for stmt in genre_changes_engine([], artist["genres"], artist_id, _ARTIST_): stmts.append(stmt) if is_available(availability): stmts.append( availability_insert_sql(artist_id, availability)) execute_transaction(stmts) success = True except: print_exc_info() return success, artist_name
def delete_artist_orm(artist_id: int) -> (bool, str): """ Delete an artist in ORM mode :param artist_id: id of the artist to delete """ artist = Artist.query.filter(Artist.id == artist_id).first_or_404() artist_name = artist.name try: # when an artist is deleted, need to delete availability & shows as well to keep the db consistent availability = Availability.query.filter(Availability.artist_id == artist_id).all() shows = Show.query.filter(Show.artist_id == artist_id).all() for available in availability: db.session.delete(available) for show in shows: db.session.delete(show) db.session.delete(artist) db.session.commit() success = True except: db.session.rollback() print_exc_info() success = False finally: db.session.close() return success, artist_name
def existing_artist_orm(name: str, city: str, state: str): """ Check for existing artist :param name: artist name :param city: artist city :param state: artist state :return: existing artist id and name, or None """ artist_id = None artist_name = None try: existing = Artist.query \ .with_entities(Artist.id, Artist.name) \ .filter(and_(func.lower(Artist.name) == func.lower(name), func.lower(Artist.city) == func.lower(city), func.upper(Artist.state) == func.upper(state))) \ .first() if existing is not None: artist_id = existing.id artist_name = existing.name except: print_exc_info() return artist_id, artist_name
def availability_by_artist_engine( artist_id: int, from_date=datetime, as_type=EntityResult.DICT) -> Union[dict, None]: """ Search for an artist's latest availability :param artist_id: id of artist :param from_date: filtering criterion :param as_type: result """ availability = None try: properties = [ '"' + AVAILABILITY_TABLE + '".' + p for p in get_model_property_list(AVAILABILITY_TABLE) ] properties = ', '.join(properties) result = execute( f'SELECT {properties} FROM "{AVAILABILITY_TABLE}" ' f'INNER JOIN "{ARTIST_TABLE}" ' f'ON {_AVAILABILITY_.fq_column("artist_id")} = {_ARTIST_.fq_column("id")} ' f'WHERE {_AVAILABILITY_.fq_column("artist_id")} = {artist_id} ' f'AND {_AVAILABILITY_.fq_column("from_date")} < TIMESTAMP \'{from_date}\' ' f'ORDER BY {_AVAILABILITY_.fq_column("from_date")} DESC, ' f'{_AVAILABILITY_.fq_column("id")} DESC;') if result.rowcount == 0: availability = None else: entry = result.mappings().first() availability = {k: entry.get(k) for k in entry.keys()} except: print_exc_info() abort(HTTPStatus.INTERNAL_SERVER_ERROR.value) return availability
def update_venue_orm(venue_id: int, form: FlaskForm) -> (bool, str): """ Update a venue in ORM mode :param venue_id: id of the venue to update :param form: form to populate from """ commit_change = False venue = Venue.query.filter(Venue.id == venue_id).first_or_404() venue_name = venue.name updated_venue = populate_venue_orm(Venue(), form) if not updated_venue.equal(venue, IGNORE_ID): # change has occurred update venue populate_venue_orm(venue, form) commit_change = True try: if commit_change: db.session.commit() success = True else: success = None except: db.session.rollback() print_exc_info() success = False finally: db.session.close() return success, venue_name
def bookings_by_venue_orm(venue_id: int, query_date: datetime) -> list: """ Search for a venue's bookings :param venue_id: id of venue :param query_date: date filtering criterion """ bookings = [] try: query = Show.query.join(Venue, Show.venue_id == Venue.id) \ .join(Artist, Show.artist_id == Artist.id) \ .with_entities(Show.start_time, Show.duration, Artist.name) if query_date is not None: query = query.filter( and_(Show.venue_id == venue_id, Show.start_date == cast(query_date, Date))) else: query = query.filter(Show.venue_id == venue_id) \ .order_by(Show.start_date) bookings = query.all() except: print_exc_info() abort(HTTPStatus.INTERNAL_SERVER_ERROR.value) # [{'start_time': ?, 'duration' ?, ...}, {}, ...] } return [{ k: show[BOOKING_BY_VENUE_DICT[k]] for k, v in BOOKING_BY_VENUE_DICT.items() } for show in bookings]
def existing_venue_orm(name: str, address: str, city: str, state: str): """ Check for existing venue :param name: artist name :param address: artist address :param city: artist city :param state: artist state :return: existing venue id and name, or None """ venue_id = None venue_name = None try: existing = Venue.query \ .with_entities(Venue.id, Venue.name) \ .filter(and_(func.lower(Venue.name) == func.lower(name), func.lower(Venue.city) == func.lower(city), func.lower(Venue.address) == func.lower(address), func.upper(Venue.state) == func.upper(state))) \ .first() if existing is not None: venue_id = existing.id venue_name = existing.name except: print_exc_info() return venue_id, venue_name
def bookings_by_venue_engine(venue_id: int, query_date: datetime): """ Search for a venue's bookings :param venue_id: id of venue :param query_date: date filtering criterion """ bookings = [] try: sql = f'SELECT "{SHOWS_TABLE}".start_time, "{SHOWS_TABLE}".duration, "{ARTIST_TABLE}".name ' \ f'FROM (("{SHOWS_TABLE}" ' \ f'INNER JOIN "{VENUE_TABLE}" ON "{SHOWS_TABLE}".venue_id = "{VENUE_TABLE}".id) ' \ f'INNER JOIN "{ARTIST_TABLE}" ON "{SHOWS_TABLE}".artist_id = "{ARTIST_TABLE}".id) ' \ f'WHERE "{SHOWS_TABLE}".venue_id = {venue_id}' if query_date is not None: sql = f'{sql} AND DATE("{SHOWS_TABLE}".start_time) = \'{date_to_str(query_date)}\'' else: sql = f'{sql} ORDER BY "{SHOWS_TABLE}".start_time' sql = sql + ';' bookings = execute(sql).fetchall() except: print_exc_info() abort(HTTPStatus.INTERNAL_SERVER_ERROR.value) # [{'start_time': ?, 'duration' ?, ...}, {}, ...] } return [{k: show[BOOKING_BY_VENUE_DICT[k]] for k, v in BOOKING_BY_VENUE_DICT.items()} for show in bookings]
def create_venue_engine(venue: dict): """ Create an venue in ENGINE mode :param venue: venue to create """ success = False venue_name = venue["name"] try: new_venue = execute(venue_insert_sql(venue)) if new_venue.rowcount > 0: # using raw sql so need to query to get new id new_venue = execute( id_name_by_unique_properties_sql(*extract_unique_properties_engine(venue)) ) if new_venue.rowcount > 0: new_venue = new_venue.fetchone() venue_id = new_venue["id"] stmts = [] # add genres for stmt in genre_changes_engine([], venue["genres"], venue_id, _VENUE_): stmts.append(stmt) execute_transaction(stmts) success = True except: print_exc_info() return success, venue_name
def shows_orm(page: int, filterby: str, mode: str, form: FlaskForm, search_term: str) -> dict: """ List all shows :param page: requested page of search results :param filterby: results filter; one of 'all', 'previous' or 'upcoming' :param mode: one of 'basic', 'advanced' or 'all' :param form: form data for advanced search :param search_term: search_term for basic search """ shows_list = [] pagination = Pagination(None, page, SHOWS_PER_PAGE, 0, shows_list) # advanced search on Venue & Artist, joining class clauses with 'and' and the result of those with 'or' # e.g. if have 'name' do same search on Venue & Artist and 'or' their results search = SearchParams([_ARTIST_, _VENUE_], conjunction=[OR_CONJUNC, AND_CONJUNC]).load_form(form) search.simple_search_term = search_term try: shows_list = Show.query\ .join(Venue, Show.venue_id == Venue.id) \ .join(Artist, Show.artist_id == Artist.id) \ .with_entities(Show.venue_id, Show.artist_id, Show.start_time, Venue.name, Artist.name, Artist.image_link) if filterby == FILTER_PREVIOUS: shows_list = shows_list.filter(Show.start_time < datetime.today()) elif filterby == FILTER_UPCOMING: shows_list = shows_list.filter(Show.start_time > datetime.today()) # get search terms and clauses for both Venue & Artist ncsg_search_clauses(mode, search) if len(search.clauses) > 0: shows_list = entity_search_clauses(shows_list, search, entity_search_expression) pagination = shows_list \ .order_by(Show.start_time) \ .paginate(page=page, per_page=SHOWS_PER_PAGE) shows_list = pagination.items except: print_exc_info() abort(HTTPStatus.INTERNAL_SERVER_ERROR.value) # [{'venue_id': ?, 'artist_id' ?, ...}, {}, ...] } data = [{ k: show[v] if k != 'start_time' else show[v].isoformat() for k, v in SHOWS_DICT.items() } for show in shows_list] return { "count": pagination.total, "data": data, "search_term": ', '.join(search.search_terms), "mode": mode, "pagination": pagination }
def create_show_engine(show: dict): """ Create an show in ENGINE mode :param show: show to create """ success = False try: new_show = execute(show_insert_sql(show)) success = new_show.rowcount > 0 except: print_exc_info() return success
def index(): latest_artists = [] latest_venues = [] try: latest_artists, latest_venues = latest_lists() except: print_exc_info() abort(HTTPStatus.INTERNAL_SERVER_ERROR.value) artist_list = [{'id': a.id, 'name': a.name} for a in latest_artists] venue_list = [{'id': v.id, 'name': v.name} for v in latest_venues] return render_template('pages/home.html', artists=artist_list, venues=venue_list)
def exists_or_404(entity: Union[Model, AnyStr], entity_id: int): """ Check if entity exists, or abort with 404 :param entity: entity model or name of table to search :param entity_id: id of entity to check """ exists = False try: exists = exists_impl(entity, entity_id) except: print_exc_info() abort(HTTPStatus.INTERNAL_SERVER_ERROR.value) if not exists: abort(HTTPStatus.NOT_FOUND.value)
def artists_and_venues_orm(): """ Get artists and venues for show listing """ artists = [] venues = [] try: artists = Artist.query.with_entities(Artist.id, Artist.name).order_by( Artist.name).all() venues = Venue.query.with_entities(Venue.id, Venue.name).order_by( Venue.name).all() except: print_exc_info() return artists, venues
def artists_and_venues_engine(): """ Get artists and venues for show listing """ artists = [] venues = [] try: artists = execute(f'SELECT id, name FROM "{ARTIST_TABLE}" ORDER BY name;') \ .fetchall() artists = [(a["id"], a["name"]) for a in artists] venues = execute(f'SELECT id, name FROM "{VENUE_TABLE}" ORDER BY name;') \ .fetchall() venues = [(a["id"], a["name"]) for a in venues] except: print_exc_info() return artists, venues
def create_show_orm(show: Show): """ Create a show in ORM mode :param show: show to create """ try: db.session.add(show) db.session.commit() success = True except: db.session.rollback() print_exc_info() success = False finally: db.session.close() return success
def create_venue_orm(venue: Venue): """ Create an venue in ORM mode :param venue: venue to create """ venue_name = venue.name try: db.session.add(venue) db.session.commit() success = True except: db.session.rollback() print_exc_info() success = False finally: db.session.close() return success, venue_name
def update_artist_orm(artist_id: int, form: FlaskForm, availability: Availability) -> (bool, str): """ Update an artist in ORM mode :param artist_id: id of the artist to update :param form: form to populate from :param availability: artist availability """ commit_change = False artist = Artist.query.filter(Artist.id == artist_id).first_or_404() artist_name = artist.name updated_artist = populate_artist_orm(Artist(), form) if not updated_artist.equal(artist, IGNORE_ID): # change has occurred update artist populate_artist_orm(artist, form) artist_name = artist.name commit_change = True new_availability = populate_availability_orm(Availability(), form) new_availability.artist_id = artist_id try: if is_available(availability) != is_available(new_availability) or \ not availability.equal(new_availability, IGNORE_ID_DATE): # availability has changed, add new setting db.session.add(new_availability) commit_change = True if commit_change: db.session.commit() success = True else: success = None except: db.session.rollback() print_exc_info() success = False finally: db.session.close() return success, artist_name
def existing_artist_engine(name: str, city: str, state: str): """ Check for existing artist :param name: artist name :param city: artist city :param state: artist state :return: existing artist id and name, or None """ artist_id = None artist_name = None try: existing = execute(id_name_by_unique_properties_sql(name, city, state)) if existing.rowcount > 0: hit = existing.mappings().first() artist_id = hit.get('id') artist_name = hit.get('name') except: print_exc_info() return artist_id, artist_name
def availability_by_artist_orm(artist_id: int, from_date: datetime, as_type=EntityResult.DICT) -> Union[dict, Availability, None]: """ Search for an artist's latest availability :param artist_id: id of artist :param from_date: filtering criterion :param as_type: result """ availability = None try: availability = Availability.query.join(Artist, Availability.artist_id == Artist.id) \ .filter(and_(Availability.artist_id == artist_id), Availability.from_date < from_date) \ .order_by(Availability.from_date.desc(), Availability.id.desc()) \ .first() if availability is not None and as_type != EntityResult.MODEL: availability = availability.get_dict() except: print_exc_info() abort(HTTPStatus.INTERNAL_SERVER_ERROR.value) return availability
def venues_engine(): """ List all venues """ venues = [] try: cities_states = execute(f'SELECT DISTINCT state, city from "{VENUE_TABLE}";') for city_state in cities_states: city = city_state["city"] state = city_state["state"] venue_list = execute( f'SELECT DISTINCT id, name from "{VENUE_TABLE}" WHERE state = \'{state}\' AND city = \'{city}\';') venues.append({ "state": state, "city": city, "venues": entity_shows_count(venue_list, _VENUE_) }) except: print_exc_info() abort(HTTPStatus.INTERNAL_SERVER_ERROR.value) return venues
def venues_orm() -> list: """ List all venues """ venues = [] try: cities_states = Venue.query.with_entities(Venue.state, Venue.city).distinct().all() for city_state in cities_states: venue_list = Venue.query.with_entities(Venue.id, Venue.name) \ .filter(and_(Venue.state == city_state.state, Venue.city == city_state.city)) \ .all() venues.append({ "state": city_state[0], "city": city_state[1], "venues": entity_shows_count(venue_list, _VENUE_) }) except: print_exc_info() abort(HTTPStatus.INTERNAL_SERVER_ERROR.value) return venues
def existing_venue_engine(name: str, address: str, city: str, state: str): """ Check for existing venue :param name: venue name :param address: venue address :param city: venue city :param state: venue state :return: existing venue id and name, or None """ venue_id = None venue_name = None try: existing = execute( id_name_by_unique_properties_sql(name, address, city, state) ) if existing.rowcount > 0: hit = existing.mappings().first() venue_id = hit.get('id') venue_name = hit.get('name') except: print_exc_info() return venue_id, venue_name
def delete_venue_orm(venue_id: int): """ Delete an venue in ORM mode :param venue_id: id of the venue to delete """ venue = Venue.query.filter(Venue.id == venue_id).first_or_404() venue_name = venue.name try: # when an venue is deleted, need to delete shows as well to keep the db consistent shows = Show.query.filter(Show.venue_id == venue_id).all() for show in shows: db.session.delete(show) db.session.delete(venue) db.session.commit() success = True except: db.session.rollback() print_exc_info() success = False finally: db.session.close() return success, venue_name
def shows_engine(page: int, filterby: str, mode: str, form: FlaskForm, search_term: str) -> dict: """ List all shows :param page: requested page of search results :param filterby: results filter; one of 'all', 'previous' or 'upcoming' :param mode: one of 'basic', 'advanced' or 'all' :param form: form data for advanced search :param search_term: search_term for basic search """ shows_list = [] pagination = Pagination(None, page, SHOWS_PER_PAGE, 0, shows_list) # advanced search on Venue & Artist, joining class clauses with 'and' and the result of those with 'or' # e.g. if have 'name' do same search on Venue & Artist and 'or' their results search = \ SearchParams([_VENUE_, _ARTIST_], conjunction=[OR_CONJUNC, AND_CONJUNC], genre_aliases=[None, _ALIAS_GENRE_TABLE_]).load_form(form) search.simple_search_term = search_term try: if filterby == FILTER_PREVIOUS: time_filter = f'"{SHOWS_TABLE}".start_time < \'{datetime_to_str(datetime.today())}\'' elif filterby == FILTER_UPCOMING: time_filter = f'"{SHOWS_TABLE}".start_time > \'{datetime_to_str(datetime.today())}\'' else: time_filter = None # get search terms and clauses for both Venue & Artist ncsg_search_clauses(mode, search) from_term = _GENRE_FROM_JOIN_ if search.searching_on[ SP_GENRES] else _BASIC_FROM_JOIN_ if len(search.clauses) > 0: search_filter = entity_search_clauses('', search, entity_search_expression) else: search_filter = None filters = _combine_filters(search_filter, time_filter) # get total count sql = f'SELECT COUNT("{SHOWS_TABLE}".venue_id) FROM {from_term}{filters};' total = execute(sql).scalar() if total > 0: offset = SHOWS_PER_PAGE * (page - 1) if offset >= total: abort(HTTPStatus.BAD_REQUEST.value) else: offset = 0 # get items for this request sql = f'SELECT {_FIELDS_LIST_} FROM {from_term}{filters} ' \ f'ORDER BY "{SHOWS_TABLE}".start_time LIMIT {SHOWS_PER_PAGE} OFFSET {offset};' shows_list = execute(sql).fetchall() total = len(shows_list) pagination = Pagination(None, page, SHOWS_PER_PAGE, total, shows_list) shows_list = pagination.items except: print_exc_info() abort(HTTPStatus.INTERNAL_SERVER_ERROR.value) # [{'venue_id': ?, 'artist_id' ?, ...}, {}, ...] } data = [{ k: show[v] if k != 'start_time' else show[v].isoformat() for k, v in SHOWS_DICT.items() } for show in shows_list] return { "count": pagination.total, "data": data, "search_term": ', '.join(search.search_terms), "mode": mode, "pagination": pagination }