from datetime import datetime, time, timedelta from mysql.connector import Error from ui_automation_framework.db.mysql_db_client import MySqlClient as MySqlClientBase class MySqlClient(MySqlClientBase): def find_date_added(self, track_isrc, playlist_id): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT pt.EarliestAdded as EarliestAdded FROM tblSpotifyPlaylistTrackList2 pt JOIN tblSpotifyTrack2" " t ON pt.TrackId = t.TrackId WHERE t.ISRC = %s AND PlaylistId = %s" cursor.execute(sql, (track_isrc, playlist_id)) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row def get_sum_of_streams_for_7_days(self, playlist_id, market): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT sum(Streams7days) FROM tblSpotifyPlaylistStreamSummary WHERE PlaylistId = %s AND market = %s" cursor.execute(sql, (playlist_id, market)) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return int(row[0]) def get_date_removed(self, track_isrc, playlist_id): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT MAX(date) from tblSpotifyPlaylistTrackListHistory2 pt JOIN tblSpotifyTrack2 t " "ON pt.TrackId = t.TrackId WHERE t.ISRC = %s AND PlaylistId = %s" cursor.execute(sql, (track_isrc, playlist_id)) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row def get_playlist_ids_spotify(self, isrcs, exit_dates, period): entry_dates = [] for d in exit_dates: entry = (datetime.strptime(d, "%Y-%m-%d") + timedelta(days=period)).date() entry_dates.append(entry) try: conn = self.connect() cursor = conn.cursor() sql = ( "SELECT DISTINCT s.PlaylistId " "FROM dbSony_dbo.tblSpotifyWeeklyTopPlaylistTrackSummary s " "LEFT JOIN tblSpotifyWeeklyTopPlaylist t " "ON t.PlaylistId = s.PlaylistId " "AND t.Date = (SELECT MAX(Date) FROM tblSpotifyWeeklyTopPlaylist) " "AND t.Country IN ('US', 'mx', 'de', 'br', 'gb', 'au', 'ca', 'es', 'it', 'fr', 'ar', 'nl', 'cl', 'se', 'ph','id', 'no', 'dk', 'pe', 'tr', 'pl', 'in','co', 'ch', 'nz', 'my', 'Global')" "LEFT JOIN tblSpotifyCustomTopPlaylist c " "ON c.PlaylistId = s.PlaylistId " "WHERE (c.PlaylistId IS NOT NULL OR t.PlaylistId IS NOT NULL) " "AND ((s.ISRC = '{isrcs[0]}' AND s.ExitDate >= '{exit_dates[0]}' AND s.EntryDate <= '{entry_dates[0]}')" "OR (s.ISRC = '{isrcs[1]}' AND s.ExitDate >= '{exit_dates[1]}' AND s.EntryDate <= '{entry_dates[1]}')" "OR (s.ISRC = '{isrcs[2]}' AND s.ExitDate >= '{exit_dates[2]}' AND s.EntryDate <= '{entry_dates[2]}')" "OR (s.ISRC = '{isrcs[3]}' AND s.ExitDate >= '{exit_dates[3]}' AND s.EntryDate <= '{entry_dates[3]}')" "OR (s.ISRC = '{isrcs[4]}' AND s.ExitDate >= '{exit_dates[4]}' AND s.EntryDate <= '{entry_dates[4]}'))".format(isrcs=isrcs, exit_dates=exit_dates, entry_dates=entry_dates) ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def get_playlist_ids_apple(self, isrcs, exit_dates, period): entry_dates = [] for d in exit_dates: entry = (datetime.strptime(d, "%Y-%m-%d") + timedelta(days=period)).date() entry_dates.append(entry) try: conn = self.connect() cursor = conn.cursor() sql = ( "SELECT DISTINCT s.PlaylistId " "FROM tblAppleMusicWeeklyTopPlaylistTrackSummary s " "JOIN tblAppleMusicWeeklyTopPlaylist t " "ON t.PlaylistId = s.PlaylistId AND t.CountryCode = s.StoreFront " "WHERE t.Date = (SELECT MAX(Date) FROM tblAppleMusicWeeklyTopPlaylist) " "AND ((s.ISRC = '{isrcs[0]}' AND s.ExitDate >= '{exit_dates[0]}' AND s.EntryDate <= '{entry_dates[0]}')" "OR (s.ISRC = '{isrcs[1]}' AND s.ExitDate >= '{exit_dates[1]}' AND s.EntryDate <= '{entry_dates[1]}')" "OR (s.ISRC = '{isrcs[2]}' AND s.ExitDate >= '{exit_dates[2]}' AND s.EntryDate <= '{entry_dates[2]}')" "OR (s.ISRC = '{isrcs[3]}' AND s.ExitDate >= '{exit_dates[3]}' AND s.EntryDate <= '{entry_dates[3]}')" "OR (s.ISRC = '{isrcs[4]}' AND s.ExitDate >= '{exit_dates[4]}' AND s.EntryDate <= '{entry_dates[4]}'))" "AND s.StoreFront IN ('us', 'mx', 'br', 'gb', 'au', 'ca', 'es', 'it', 'fr', 'de', 'global')".format(isrcs=isrcs, exit_dates=exit_dates, entry_dates=entry_dates) ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def get_playlist_ids_spotify_for_market(self, isrcs, exit_dates, period, market): entry_dates = [] for d in exit_dates: entry = (datetime.strptime(d, "%Y-%m-%d") + timedelta(days=period)).date() entry_dates.append(entry) try: conn = self.connect() cursor = conn.cursor() sql = ( "SELECT DISTINCT s.PlaylistId " "FROM dbSony_dbo.tblSpotifyWeeklyTopPlaylistTrackSummary s " "JOIN tblSpotifyWeeklyTopPlaylist t " "ON t.PlaylistId = s.PlaylistId " "WHERE t.Date = (SELECT MAX(Date) FROM tblSpotifyWeeklyTopPlaylist) " "AND ((s.ISRC = '{isrcs[0]}' AND s.ExitDate >= '{exit_dates[0]}' AND s.EntryDate <= '{entry_dates[0]}')" "OR (s.ISRC = '{isrcs[1]}' AND s.ExitDate >= '{exit_dates[1]}' AND s.EntryDate <= '{entry_dates[1]}')" "OR (s.ISRC = '{isrcs[2]}' AND s.ExitDate >= '{exit_dates[2]}' AND s.EntryDate <= '{entry_dates[2]}')" "OR (s.ISRC = '{isrcs[3]}' AND s.ExitDate >= '{exit_dates[3]}' AND s.EntryDate <= '{entry_dates[3]}')" "OR (s.ISRC = '{isrcs[4]}' AND s.ExitDate >= '{exit_dates[4]}' AND s.EntryDate <= '{entry_dates[4]}'))" "AND t.Country IN ('{market}')".format(isrcs=isrcs, exit_dates=exit_dates, entry_dates=entry_dates, market=market) ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def get_playlist_ids_apple_for_market(self, isrcs, exit_dates, period, market): entry_dates = [] for d in exit_dates: entry = (datetime.strptime(d, "%Y-%m-%d") + timedelta(days=period)).date() entry_dates.append(entry) try: conn = self.connect() cursor = conn.cursor() sql = ( "SELECT DISTINCT s.PlaylistId " "FROM tblAppleMusicWeeklyTopPlaylistTrackSummary s " "JOIN tblAppleMusicWeeklyTopPlaylist t " "ON t.PlaylistId = s.PlaylistId AND t.CountryCode = s.StoreFront " "WHERE t.Date = (SELECT MAX(Date) FROM tblAppleMusicWeeklyTopPlaylist) " "AND ((s.ISRC = '{isrcs[0]}' AND s.ExitDate >= '{exit_dates[0]}' AND s.EntryDate <= '{entry_dates[0]}')" "OR (s.ISRC = '{isrcs[1]}' AND s.ExitDate >= '{exit_dates[1]}' AND s.EntryDate <= '{entry_dates[1]}')" "OR (s.ISRC = '{isrcs[2]}' AND s.ExitDate >= '{exit_dates[2]}' AND s.EntryDate <= '{entry_dates[2]}')" "OR (s.ISRC = '{isrcs[3]}' AND s.ExitDate >= '{exit_dates[3]}' AND s.EntryDate <= '{entry_dates[3]}')" "OR (s.ISRC = '{isrcs[4]}' AND s.ExitDate >= '{exit_dates[4]}' AND s.EntryDate <= '{entry_dates[4]}'))" "AND s.StoreFront IN ('{market}')".format(isrcs=isrcs, exit_dates=exit_dates, entry_dates=entry_dates, market=market) ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def get_playlist_ids_spotify_for_track(self, isrc, exit_date, period, country): entry_date = (datetime.strptime(exit_date, "%Y-%m-%d") + timedelta(days=period)).date() try: conn = self.connect() cursor = conn.cursor() sql = ( "SELECT DISTINCT s.PlaylistId " "FROM dbSony_dbo.tblSpotifyWeeklyTopPlaylistTrackSummary s " "LEFT JOIN tblSpotifyWeeklyTopPlaylist t " "ON t.PlaylistId = s.PlaylistId " "AND t.Date = (SELECT MAX(Date) FROM tblSpotifyWeeklyTopPlaylist) " "AND t.Country='{country}' " "WHERE t.PlaylistId IS NOT NULL " "AND s.ISRC = '{isrc}' AND s.ExitDate >= '{exit_date}' AND s.EntryDate <= '{entry_date}'".format(country=country, isrc=isrc, exit_date=exit_date, entry_date=entry_date) ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def get_spotify_playlist_sorting_7days_column(self, isrc, exit_date, period, country, column): entry_date = (datetime.strptime(exit_date, "%Y-%m-%d") + timedelta(days=period)).date() try: conn = self.connect() cursor = conn.cursor() sql = ( "SELECT {column} " "FROM dbSony_dbo.tblSpotifyPlaylistStreamSummary " "where PlaylistId in (SELECT DISTINCT s.PlaylistId " "FROM dbSony_dbo.tblSpotifyWeeklyTopPlaylistTrackSummary s " "LEFT JOIN tblSpotifyWeeklyTopPlaylist t " "ON t.PlaylistId = s.PlaylistId AND t.Date = (SELECT MAX(Date) " "FROM tblSpotifyWeeklyTopPlaylist) AND t.Country='{market}' WHERE t.PlaylistId IS NOT NULL " "AND s.ISRC = '{isrc}' AND s.ExitDate >= '{exit_date}' AND s.EntryDate <= '{entry_date}') " "and Market ='{market}' order by Streams7days desc".format(market=country, isrc=isrc, exit_date=exit_date, entry_date=entry_date, column=column) ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() if str(column).lower() in ["streams7days"]: for i in range(0, len(clean_rows)): clean_rows[i] = int(clean_rows[i]) return clean_rows def get_playlist_ids_apple_for_track(self, isrc, exit_date, period, country): entry_date = (datetime.strptime(exit_date, "%Y-%m-%d") + timedelta(days=period)).date() try: conn = self.connect() cursor = conn.cursor() sql = ( "Select DISTINCT s.PlaylistId " "FROM tblAppleMusicWeeklyTopPlaylistTrackSummary as s " "Left Join tblAppleMusicWeeklyTopPlaylist as t " "ON t.PlaylistId=s.PlaylistId and t.CountryCode=s.StoreFront " "where t.Date = (Select MAX(Date)From tblAppleMusicWeeklyTopPlaylist) " "and s.ISRC='{isrc}' and s.ExitDate >= '{exit_date}' " "AND s.EntryDate <= '{entry_date}' and s.StoreFront='{country}'".format(country=country, isrc=isrc, exit_date=exit_date, entry_date=entry_date) ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def get_apple_playlist_sorting_7days_column(self, isrc, exit_date, period, country, column): entry_date = (datetime.strptime(exit_date, "%Y-%m-%d") + timedelta(days=period)).date() try: conn = self.connect() cursor = conn.cursor() sql = ( "SELECT {column} FROM dbSony_dbo.tblAppleMusicContainerStreamSummary " "where ContainerId in (Select DISTINCT s.PlaylistId FROM tblAppleMusicWeeklyTopPlaylistTrackSummary as s" " Left Join tblAppleMusicWeeklyTopPlaylist as t ON t.PlaylistId=s.PlaylistId " "and t.CountryCode=s.StoreFront where t.Date = (Select MAX(Date)From tblAppleMusicWeeklyTopPlaylist) " "and s.ISRC='{isrc}' and s.ExitDate >= '{exit_date}' " "AND s.EntryDate <= '{entry_date}' and s.StoreFront='{market}') " "and CountryCode = '{market}' order by Streams7Days desc".format(market=country, isrc=isrc, exit_date=exit_date, entry_date=entry_date, column=column) ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() if str(column).lower() in ["streams7days"]: for i in range(0, len(clean_rows)): clean_rows[i] = int(clean_rows[i]) return clean_rows def get_streams_for_apple_playlist_with_id(self, playlist_id, market): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT sum(Streams7Days) " "FROM dbSony_dbo.tblAppleMusicContainerStreamSummary " "WHERE `ContainerId` = '{id}' " "AND `CountryCode` IN ('{market}') LIMIT 200".format(id=playlist_id, market=market) cursor.execute(sql) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return int(row[0]) def get_value_for_track_with_playlist(self, column, id, isrc): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT {column} FROM dbSony_dbo.tblAppleMusicWeeklyTopPlaylistTrackSummary " "WHERE PlaylistId = '{id}' " "AND StoreFront IN ('us', 'mx', 'br', 'gb', 'au', 'ca', 'es', 'it', 'fr', 'de', 'global') " "AND ISRC IN ('{isrc}') " "LIMIT 1".format(column=column, id=id, isrc=isrc) cursor.execute(sql) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row[0] def select_playlists_ids_for_28_days_top_250_apple(self, isrc, exit_date, period): entry_date = (datetime.strptime(exit_date, "%Y-%m-%d") + timedelta(days=period)).date() try: conn = self.connect() cursor = conn.cursor() sql = ( "SELECT DISTINCT s.PlaylistId " "FROM tblAppleMusicWeeklyTopPlaylistTrackSummary s " "JOIN tblAppleMusicWeeklyTopPlaylist t " "ON t.PlaylistId = s.PlaylistId AND t.CountryCode = s.StoreFront " "WHERE t.Date = (SELECT MAX(Date) FROM tblAppleMusicWeeklyTopPlaylist) " "AND (s.ISRC = '{isrc}' AND s.ExitDate >= '{exit_date}' AND s.EntryDate <= '{entry_date}') " "AND s.StoreFront IN ('us', 'mx', 'br', 'gb', 'au', 'ca', 'es', 'it', 'fr', 'de', 'global')".format(isrc=isrc, exit_date=exit_date, entry_date=entry_date) ) self.log.info("full sql for period: {} : {}".format(period, sql)) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_all_spotify_playlists_ids_for_period(self, isrc, exit_date, period): entry_date = (datetime.strptime(exit_date, "%Y-%m-%d") + timedelta(days=period)).date() try: conn = self.connect() cursor = conn.cursor() sql = ( "SELECT DISTINCT s.PlaylistId " "FROM dbSony_dbo.tblSpotifyWeeklyTopPlaylistTrackSummary s " "LEFT JOIN tblSpotifyWeeklyTopPlaylist t " "ON t.PlaylistId = s.PlaylistId " "AND t.Date = (SELECT MAX(Date) FROM tblSpotifyWeeklyTopPlaylist) " "AND t.Country IN ('US', 'mx', 'de', 'br', 'gb', 'au', 'ca', 'es', 'it', 'fr', 'ar', 'nl', 'cl', 'se'," " 'ph','id', 'no', 'dk', 'pe', 'tr', 'pl', 'in','co', 'ch', 'nz', 'my', 'Global') " "LEFT JOIN tblSpotifyCustomTopPlaylist c " "ON c.PlaylistId = s.PlaylistId " "WHERE (c.PlaylistId IS NOT NULL OR t.PlaylistId IS NOT NULL) " "AND s.ISRC = '{isrc}' AND s.ExitDate >= '{exit_date}' AND s.EntryDate <= '{entry_date}'".format(isrc=isrc, exit_date=exit_date, entry_date=entry_date) ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_custom_playlists_spotify(self): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT PlaylistId FROM dbSony_dbo.tblSpotifyCustomTopPlaylist" cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_dates_for_track_history_spotify(self, id, isrc, column): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT DISTINCT {column} FROM dbSony_dbo.tblSpotifyWeeklyTopPlaylistTrackSummary " "Where PlaylistId = '{id}' AND ISRC = '{isrc}'".format(column=column, id=id, isrc=isrc) cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) clean_rows = [] for s in row: clean_rows.append(s[0]) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def get_spotify_streams(self, ids): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT Streams7days FROM dbSony_dbo.tblSpotifyPlaylistStreamSummaryGlobal " "WHERE PlaylistId in({ids}) " "ORDER by Streams7days DESC ".format(ids=str(ids).replace("[", "").replace("]", "")) self.log.info("full sql: {sql}".format(sql=sql)) cursor.execute(sql) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) clean_rows = [] for s in row: clean_rows.append(int(str(s[0]).replace(",", ""))) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_apple_playlists_track_belongs_to(self, country, store, isrc, column, id=""): try: conn = self.connect() cursor = conn.cursor() playlist_id = "" if id != "": playlist_id = "AND dbSony_dbo.tblAppleMusicWeeklyTopPlaylist.PlaylistId = '{id}'".format(id=id) sql = ( "SELECT DISTINCT {column} " "FROM dbSony_dbo.tblAppleMusicWeeklyTopPlaylist " "INNER JOIN dbSony_dbo.tblAppleMusicWeeklyTopPlaylistTrackSummary " "ON dbSony_dbo.tblAppleMusicWeeklyTopPlaylist.PlaylistId = dbSony_dbo.tblAppleMusicWeeklyTopPlaylistTrackSummary.PlaylistId " "WHERE dbSony_dbo.tblAppleMusicWeeklyTopPlaylist.Date IN (Select max(Date) from dbSony_dbo.tblAppleMusicWeeklyTopPlaylist) " "{id} " "AND dbSony_dbo.tblAppleMusicWeeklyTopPlaylist.CountryCode IN ('{country}') " "and dbSony_dbo.tblAppleMusicWeeklyTopPlaylistTrackSummary.StoreFront = '{store}' " "AND dbSony_dbo.tblAppleMusicWeeklyTopPlaylistTrackSummary.ISRC = '{isrc}' " "GROUP BY dbSony_dbo.tblAppleMusicWeeklyTopPlaylist.PlaylistId".format(id=playlist_id, country=country, store=store, isrc=isrc, column=column) ) cursor.execute(sql) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) clean_rows = [] for s in row: clean_rows.append(s[0]) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_apple_track_history_in_playlist(self, id, store, isrc, column): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT {column} FROM tblAppleMusicWeeklyTopPlaylistTrackSummary " "WHERE PlaylistId = '{id}' " "and ISRC = '{isrc}' " "and StoreFront = '{store}'".format(id=id, store=store, isrc=isrc, column=column) cursor.execute(sql) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) clean_rows = [] for s in row: clean_rows.append(s[0]) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_playlist_name_from_top_200_spotify(self, id, country): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT PlaylistName " "FROM dbSony_dbo.tblSpotifyWeeklyTopPlaylist " "WHERE Date IN (Select max(Date) " "from tblSpotifyWeeklyTopPlaylist) " "AND Country = '{country}' " "and PlaylistId = '{id}'".format(id=id, country=country) cursor.execute(sql) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) clean_rows = [] for s in row: clean_rows.append(s[0]) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_dates_track_in_playlist_db(self, track_id, playlist_id): try: conn = self.connect() cursor = conn.cursor() sql = "SELECT `Date` FROM dbSony_dbo.tblSpotifyPlaylistTrackListHistory2 " "WHERE PlaylistId = '{playlist_id}' " "and (TrackId= '{track_id}') " "order by Date ASC".format(track_id=track_id, playlist_id=playlist_id) cursor.execute(sql) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) clean_rows = [] for s in row: clean_rows.append(s[0]) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_spotify_playlists_track_belongs_to(self, country, isrc): try: conn = self.connect() cursor = conn.cursor() sql = ( "SELECT DISTINCT PlaylistId " "FROM dbSony_dbo.tblSpotifyWeeklyTopPlaylistTrackSummary " "WHERE ISRC = '{isrc}' " "and PlaylistId IN (Select dbSony_dbo.tblSpotifyWeeklyTopPlaylist.PlaylistId " "from dbSony_dbo.tblSpotifyWeeklyTopPlaylist " "where dbSony_dbo.tblSpotifyWeeklyTopPlaylist.Country='{country}' " "and dbSony_dbo.tblSpotifyWeeklyTopPlaylist.Date " "IN (Select max(Date) from tblSpotifyWeeklyTopPlaylist))".format(country=country, isrc=isrc) ) cursor.execute(sql) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) clean_rows = [] for s in row: clean_rows.append(s[0]) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_playlist_name_from_top_250_apple(self, id, country): try: conn = self.connect() cursor = conn.cursor() sql = """SELECT PlaylistId FROM dbSony_dbo.tblAppleMusicWeeklyTopPlaylist WHERE Date IN (Select max(Date) from dbSony_dbo.tblAppleMusicWeeklyTopPlaylist) AND CountryCode = '{country}' AND PlaylistId = '{id}'""".format( country=country, id=id ) cursor.execute(sql) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) clean_rows = [] for s in row: clean_rows.append(s[0]) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_date_track_life_circle_playlist_spotify(self, isrc, playlist_id, interval, column): try: conn = self.connect() cursor = conn.cursor() sql = """SELECT {column} FROM tblSpotifyWeeklyTopPlaylistTrackSummary s WHERE PlaylistId = '{playlist_id}' AND ISRC = '{isrc}' AND EntryDate >= (SELECT MIN(EntryDate) FROM tblSpotifyWeeklyTopPlaylistTrackSummary s1 WHERE s1.ISRC = s.ISRC) AND EntryDate <= (DATE_ADD((SELECT MIN(EntryDate) FROM tblSpotifyWeeklyTopPlaylistTrackSummary s1 WHERE s1.ISRC = s.ISRC), INTERVAL {interval} DAY))""".format( playlist_id=playlist_id, isrc=isrc, interval=interval, column=column ) cursor.execute(sql) row = cursor.fetchall() clean_rows = [] for s in row: clean_rows.append(s[0]) self.log.info("rows db: {}".format(clean_rows)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_date_track_life_circle_playlist_apple(self, isrc, playlist_id, interval, column, country): try: conn = self.connect() cursor = conn.cursor() sql = """SELECT DISTINCT {column} FROM tblAppleMusicWeeklyTopPlaylistTrackSummary s WHERE PlaylistId = '{playlist_id}' AND ISRC = '{isrc}' and StoreFront = '{country}' AND EntryDate >= (SELECT MIN(EntryDate) FROM tblAppleMusicWeeklyTopPlaylistTrackSummary s1 WHERE s1.ISRC = s.ISRC) AND EntryDate <= (DATE_ADD((SELECT MIN(EntryDate) FROM tblAppleMusicWeeklyTopPlaylistTrackSummary s1 WHERE s1.ISRC = s.ISRC), INTERVAL {interval} DAY))""".format( playlist_id=playlist_id, isrc=isrc, interval=interval, column=column, country=country ) cursor.execute(sql) row = cursor.fetchall() clean_rows = [] for s in row: clean_rows.append(s[0]) self.log.info("rows db: {}".format(clean_rows)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def select_date_track_life_circle_playlist_apple_global(self, isrc, playlist_id, interval, column): try: conn = self.connect() cursor = conn.cursor() sql = """SELECT {column} FROM tblAppleMusicWeeklyTopPlaylistTrackSummary s WHERE PlaylistId = '{playlist_id}' AND ISRC = '{isrc}' and StoreFront IN ('us', 'mx', 'br', 'gb', 'au', 'ca', 'es', 'it', 'fr', 'de', 'global') AND EntryDate >= (SELECT MIN(EntryDate) FROM tblAppleMusicWeeklyTopPlaylistTrackSummary s1 WHERE s1.ISRC = s.ISRC) AND EntryDate <= (DATE_ADD((SELECT MIN(EntryDate) FROM tblAppleMusicWeeklyTopPlaylistTrackSummary s1 WHERE s1.ISRC = s.ISRC), INTERVAL {interval} DAY))""".format( playlist_id=playlist_id, isrc=isrc, interval=interval, column=column ) cursor.execute(sql) row = cursor.fetchall() clean_rows = [] for s in row: clean_rows.append(s[0]) self.log.info("rows db: {}".format(clean_rows)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def get_playlist_ids_apple_for_track_global(self, isrc, exit_date, period): entry_date = (datetime.strptime(exit_date, "%Y-%m-%d") + timedelta(days=period)).date() try: conn = self.connect() cursor = conn.cursor() sql = ( "Select DISTINCT s.PlaylistId " "FROM tblAppleMusicWeeklyTopPlaylistTrackSummary as s " "Left Join tblAppleMusicWeeklyTopPlaylist as t " "ON t.PlaylistId=s.PlaylistId and t.CountryCode=s.StoreFront " "where t.Date = (Select MAX(Date)From tblAppleMusicWeeklyTopPlaylist) " "and s.ISRC='{isrc}' and s.ExitDate >= '{exit_date}' " "AND s.EntryDate <= '{entry_date}' " "and s.StoreFront IN ('us', 'mx', 'br', 'gb', 'au', 'ca', 'es', 'it', 'fr', 'de', 'global')".format(isrc=isrc, exit_date=exit_date, entry_date=entry_date) ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def get_track_id(self, id): try: conn = self.connect() cursor = conn.cursor() sql = """SELECT TrackId FROM dbSony_dbo.tblSpotifyTrack2 WHERE 'TrackId' = '{id}'""".format( id=id ) cursor.execute(sql) row = cursor.fetchall() self.log.info(row) clean_rows = [] for s in row: clean_rows.append(str(s).replace("(", "").replace(")", "").replace(",", "").replace("'", "")) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def get_spotify_demographics_old(self, market, isrc, start_date, period, column): entry_date = (datetime.strptime(start_date, "%Y-%m-%d") + timedelta(days=period)).date() try: conn = self.connect() cursor = conn.cursor() sql = """SELECT sum({column}) FROM dbSony_dbo.tblSpotifyTrackStreamDemographics where Isrc='{isrc}' and Date between '{start_date}' and '{entry_date}' and CountryCode='{market}'""".format( isrc=isrc, start_date=start_date, entry_date=entry_date, market=market, column=column ) cursor.execute(sql) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row[0] def select_dates_for_track_history_spotify_with_range(self, id, isrc, first_date, column): last_date = (datetime.strptime(first_date, "%Y-%m-%d") + timedelta(days=365)).date() try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT {column} FROM dbSony_dbo.tblSpotifyWeeklyTopPlaylistTrackSummary Where PlaylistId = '{id}' AND ISRC = '{isrc}' AND EntryDate <= '{last_date}'""" cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) clean_rows = [] for s in row: clean_rows.append(s[0]) self.log.info("rows db: {}".format(clean_rows)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return clean_rows def clear_search_history(self, id): try: conn = self.connect() cursor = conn.cursor() sql = """DELETE FROM tblAdminSearchHistory WHERE AdminUserId = '{}'""".format( id ) cursor.execute(sql) conn.commit() except Error as e: self.log.info(e) finally: cursor.close() conn.close() def clear_most_visited_history(self, id): try: conn = self.connect() cursor = conn.cursor() sql = """DELETE FROM tblApolloVisitLog WHERE userid = '{}'""".format( id ) cursor.execute(sql) conn.commit() except Error as e: self.log.info(e) finally: cursor.close() conn.close() def insert_track_most_visited_test_data(self, userid, trackid1, trackid2): date1 = (datetime.today() - timedelta(days=35)).date() date_out_of_range = "{}.0".format(datetime.combine(date1, time())) date2 = (datetime.today() - timedelta(days=28)).date() date_in_range = "{}.0".format(datetime.combine(date2, time())) try: conn = self.connect() cursor = conn.cursor() sql = """INSERT INTO tblApolloVisitLog ( UserId, DspId, TypeId, InstanceId, CreatedAt ) VALUES ( '{userid}', '1', '1', '{trackid1}', '{date_out_of_range}' ), ( '{userid}', '1', '1', '{trackid1}', '{date_out_of_range}' ), ( '{userid}', '1', '1', '{trackid1}', '{date_out_of_range}' ), ( '{userid}', '1', '1', '{trackid1}', '{date_out_of_range}' ), ( '{userid}', '1', '1', '{trackid1}', '{date_out_of_range}' ), ( '{userid}', '1', '1', '{trackid1}', '{date_out_of_range}' ), ( '{userid}', '1', '1', '{trackid1}', '{date_out_of_range}' ), ( '{userid}', '1', '1', '{trackid2}', '{date_in_range}' ), ( '{userid}', '1', '1', '{trackid2}', '{date_in_range}' ), ( '{userid}', '1', '1', '{trackid2}', '{date_in_range}' ), ( '{userid}', '1', '1', '{trackid2}', '{date_in_range}' ), ( '{userid}', '1', '1', '{trackid2}', '{date_in_range}' ), ( '{userid}', '1', '1', '{trackid2}', '{date_in_range}' ), ( '{userid}', '1', '1', '{trackid2}', '{date_in_range}' )""".format( userid=userid, trackid1=trackid1, trackid2=trackid2, date_out_of_range=date_out_of_range, date_in_range=date_in_range ) self.log.info(sql) cursor.execute(sql) conn.commit() except Error as e: self.log.info(e) finally: cursor.close() conn.close() def get_upc_for_album_id(self, param): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT Upc FROM dbSony_dbo.tblSpotifyAlbum WHERE AlbumId = '{param}'""" cursor.execute(sql) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row def get_music_service_id(self, upc, market): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT MusicServiceId FROM dbSony_dbo.tblSonyUPCRegion WHERE upc='{upc}' AND Region='{market}' limit 1;""" cursor.execute(sql) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row def get_music_service_id_for_any_market(self, upc): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT MusicServiceId FROM dbSony_dbo.tblSonyUPCRegion WHERE upc='{upc}'""" cursor.execute(sql) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row def get_latest_update_apple_pl_page(self, id): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT LatestUpdate FROM dbSony_dbo.tblAppleMusicPlaylist x WHERE id = '{id}'""" cursor.execute(sql) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row[0] def get_updated_apple_playlist_page(self, id): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT Case When Timestampdiff (hour, (SELECT LatestUpdate FROM dbSony_dbo.tblAppleMusicPlaylist x WHERE id = '{id}'), CURRENT_TIMESTAMP()) >= 24 Then Concat((Timestampdiff (DAY, (SELECT LatestUpdate FROM dbSony_dbo.tblAppleMusicPlaylist x WHERE id = '{id}'), CURRENT_TIMESTAMP())), ' days ago') Else Concat((Timestampdiff (hour, (SELECT LatestUpdate FROM dbSony_dbo.tblAppleMusicPlaylist x WHERE id = '{id}'), CURRENT_TIMESTAMP())), ' hours ago') End as updated_ago FROM dbSony_dbo.tblAppleMusicPlaylist x WHERE id = '{id}'""" self.log.info(sql) cursor.execute(sql) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row[0] def get_icon_time_apple_pl_page(self, id): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT AppleMusicUpdate FROM dbSony_dbo.tblAppleMusicPlaylist x WHERE id = '{id}'""" self.log.info(sql) cursor.execute(sql) row = cursor.fetchone() self.log.info(row) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row[0] def get_tracklist_histore_dates(self, id, market): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT Timestamp FROM dbSony_dbo.tblAppleMusicPlaylistTracklistHistoryDate where PlaylistId = '{id}' and Storefront = '{market}'""" cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return [r[0] for r in row] def tl_verify_tracks_for_chosen_date(self, date, id, market): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT SongId FROM dbSony_dbo.tblAppleMusicPlaylistTracklistHistory where PlaylistId = '{id}' and StoreFront = '{market}' and Date = '{date}'""" cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return [r[0] for r in row] def tp_td_get_markets(self, playlist_id, apple_id): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT Storefront FROM dbSony_dbo.tblAppleMusicPlaylistTracklist WHERE SongId IN ('{apple_id}') AND PlaylistId ='{playlist_id}' GROUP by Storefront""" cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return [r[0] for r in row] def get_hot_hits_latest_date(self): try: conn = self.connect() cursor = conn.cursor() sql = """select tblApolloKeyValueStorage.Value from tblApolloKeyValueStorage where tblApolloKeyValueStorage.Key = 'hot_hits_latest_date';""" cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchone() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row[0] def get_hot_hits_markets(self, isrc): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT tblSpotifyHotHitsPlaylist.Market, MIN(tblSpotifyHotHitsPlaylistTrackSummary.EntryDate), MAX(tblSpotifyHotHitsPlaylistTrackSummary.ExitDate) FROM tblSpotifyHotHitsPlaylist LEFT JOIN tblSpotifyHotHitsPlaylistTrackSummary ON tblSpotifyHotHitsPlaylistTrackSummary.PlaylistId = tblSpotifyHotHitsPlaylist.PlaylistId AND tblSpotifyHotHitsPlaylistTrackSummary.ISRC IN ('{isrc}') GROUP BY tblSpotifyHotHitsPlaylist.PlaylistId, tblSpotifyHotHitsPlaylistTrackSummary.ISRC""" self.log.info("full sql: {}".format(sql)) cursor.execute(sql) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row def get_nmf_market_ranks(self): try: conn = self.connect() cursor = conn.cursor() sql = """SELECT Market, SUM(TotalStreams) as Stream28Days FROM tblSpotifyAnalyticsAccountStreamInfo WHERE AccountId = 1 AND Date <= DATE_SUB(NOW(), INTERVAL 3 DAY) AND Date >= DATE_SUB(NOW(), INTERVAL 31 DAY) GROUP BY Market ORDER BY Stream28Days DESC""" cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() rows_parsed = {r[0]: int(r[1]) for r in row} self.log.info("rows parsed: {}".format(rows_parsed)) return rows_parsed def get_nmf_featured_markets_count(self, isrc, date): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT COUNT(DISTINCT pl.Market) AS MARKET_COUNT FROM dbSony_dbo.tblSpotifyNewMusicFridayPlaylistTrack AS tl INNER JOIN dbSony_dbo.tblSpotifyNewMusicFridayPlaylist AS pl ON pl.PlaylistId = tl.PlaylistId INNER JOIN dbSony_dbo.tblSpotifyTrack2 AS t ON t.TrackId = tl.trackId WHERE date = '{date}' and t.ISRC = '{isrc}'""" cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchone() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return int(row[0]) def get_nmf_not_featured_markets_count(self, isrc, date): try: conn = self.connect() cursor = conn.cursor() sql = f"""select COUNT(Market) from dbSony_dbo.tblSpotifyNewMusicFridayPlaylist where Market not in ( SELECT pl.Market FROM dbSony_dbo.tblSpotifyNewMusicFridayPlaylistTrack AS tl INNER JOIN dbSony_dbo.tblSpotifyNewMusicFridayPlaylist AS pl ON pl.PlaylistId = tl.PlaylistId INNER JOIN dbSony_dbo.tblSpotifyTrack2 AS t ON t.TrackId = tl.trackId WHERE date = '{date}' and ISRC = '{isrc}')""" cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchone() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return int(row[0]) def get_market_name_by_country_code(self, country_code): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT Name FROM dbSony_dbo.tblApolloMarket where Code = '{country_code}'""" cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchone() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row[0] def get_country_code_by_market_name(self, market_name): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT Code FROM dbSony_dbo.tblApolloMarket where name = '{market_name}'""" cursor.execute(sql) self.log.info("full sql: {}".format(sql)) row = cursor.fetchone() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return row[0] def get_apple_markets_for_playlist_track(self, track_id, playlist_id): try: conn = self.connect() cursor = conn.cursor() sql = f"""SELECT Storefront FROM dbSony_dbo.tblAppleMusicPlaylistTracklist WHERE SongId IN ('{track_id}') AND PlaylistId ='{playlist_id}' GROUP by Storefront""" self.log.info("full sql: {}".format(sql)) cursor.execute(sql) row = cursor.fetchall() self.log.info("rows db: {}".format(row)) except Error as e: self.log.info(e) finally: cursor.close() conn.close() return [r[0] for r in row]