import argparse import config import connection import datetime def get_argument(): parser = argparse.ArgumentParser(prefix_chars='+') parser.add_argument( 'product_id', nargs='+', help='Please enter the product ids' ) args = parser.parse_args() return args.product_id def get_upc(product_id): product_id_string = str(product_id) product_select = f"""SELECT upc FROM releases WHERE release_id = {product_id_string}""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(product_select) product = cursor.fetchone() db_connection.commit() db_connection.close() upc = product[0] return upc def get_track_ids(product_id): product_id_string = str(product_id) track_select = f"""SELECT id FROM track WHERE release_id ={product_id_string}""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(track_select) tracks = cursor.fetchall() db_connection.commit() db_connection.close() track_ids = [] for track in tracks: track_ids.append(track[0]) return track_ids def get_product_rollback(product_id): product_id_string = str(product_id) product_select = f"""SELECT release_id, last_updated, upc, display_upc, artist_id, subaccount_id, vendor_release_identifier, release_name, label, description, release_date, listprice, sellprice, display, deletions, distribution, applfees, referral_id, date_added_REMOVE, genre_id, format, distribution_format_id, orig_release_year, promocode, c_line, manufacturer_upc, album_download, download_price, priority, territory, sale_start_date, vod_start_date, original_release_date, vendor_catalog_number, language_id, promobox_no, additional_id, dracatalog, channel_id, product_type_id, trackdown_black_list, not_for_distribution, europe_public_domain, karaoke, unknown_artist, new_release, product_format, product_code, special_instructions, promos_number, promo_upc, sync_only, country_of_origin, short_synopsis, keywords, production_co, preorder_date, amd_id, wholesale_price_tier, season, episode, production_number, network, genre_note, physical_release_date, theatrical_release_date, original_digital_release_date, est, vod, rsd_comment, int_mkt_manager, tms_id, brand, digital_only, product_subtype_id, version, ingestion_completed, release_status, meta_language, itunes_previewable, subtitle_language_id, compilation, project_id, p_line, delivered_version FROM releases WHERE release_id = {product_id_string}""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(product_select) product = cursor.fetchone() db_connection.commit() db_connection.close() # replace None with null for SQL insert and make sure strings, dates and datetimes are quoted product_fields = ["'" + str(x) + "'" if type(x) in [str, datetime.datetime, datetime.date] else 'null' if x is None else x for x in product] product_rollback_insert = f"""--rollback INSERT INTO releases (release_id, last_updated, upc, display_upc, artist_id, subaccount_id, vendor_release_identifier, release_name, label, description, release_date, listprice, sellprice, display, deletions, distribution, applfees, referral_id, date_added_REMOVE, genre_id, format, distribution_format_id, orig_release_year, promocode, c_line, manufacturer_upc, album_download, download_price, priority, territory, sale_start_date, vod_start_date, original_release_date, vendor_catalog_number, language_id, promobox_no, additional_id, dracatalog, channel_id, product_type_id, trackdown_black_list, not_for_distribution, europe_public_domain, karaoke, unknown_artist, new_release, product_format, product_code, special_instructions, promos_number, promo_upc, sync_only, country_of_origin, short_synopsis, keywords, production_co, preorder_date, amd_id, wholesale_price_tier, season, episode, production_number, network, genre_note, physical_release_date, theatrical_release_date, original_digital_release_date, est, vod, rsd_comment, int_mkt_manager, tms_id, brand, digital_only, product_subtype_id, version, ingestion_completed, release_status, meta_language, itunes_previewable, subtitle_language_id, compilation, project_id, p_line, delivered_version) VALUES\n""" product_rollback_insert += f"""--rollback ({product_fields[0]}, {product_fields[1]}, {product_fields[2]}, {product_fields[3]}, {product_fields[4]}, {product_fields[5]}, {product_fields[6]}, {product_fields[7]}, {product_fields[8]}, {product_fields[9]}, {product_fields[10]}, {product_fields[11]}, {product_fields[12]}, {product_fields[13]}, {product_fields[14]}, {product_fields[15]}, {product_fields[16]}, {product_fields[17]}, {product_fields[18]}, {product_fields[19]}, {product_fields[20]}, {product_fields[21]}, {product_fields[22]}, {product_fields[23]}, {product_fields[24]}, {product_fields[25]}, {product_fields[26]}, {product_fields[27]}, {product_fields[28]}, {product_fields[29]}, {product_fields[30]}, {product_fields[31]}, {product_fields[32]}, {product_fields[33]}, {product_fields[34]}, {product_fields[35]}, {product_fields[36]}, {product_fields[37]}, {product_fields[38]}, {product_fields[39]}, {product_fields[40]}, {product_fields[41]}, {product_fields[42]}, {product_fields[43]}, {product_fields[44]}, {product_fields[45]}, {product_fields[46]}, {product_fields[47]}, {product_fields[48]}, {product_fields[49]}, {product_fields[50]}, {product_fields[51]}, {product_fields[52]}, {product_fields[53]}, {product_fields[54]}, {product_fields[55]}, {product_fields[56]}, {product_fields[57]}, {product_fields[58]}, {product_fields[59]}, {product_fields[60]}, {product_fields[61]}, {product_fields[62]}, {product_fields[63]}, {product_fields[64]}, {product_fields[65]}, {product_fields[66]}, {product_fields[67]}, {product_fields[68]}, {product_fields[69]}, {product_fields[70]}, {product_fields[71]}, {product_fields[72]}, {product_fields[73]}, {product_fields[74]}, {product_fields[75]}, {product_fields[76]}, {product_fields[77]}, {product_fields[78]}, {product_fields[79]}, {product_fields[80]}, {product_fields[81]}, {product_fields[82]}, {product_fields[83]}, {product_fields[84]});\n""" return product_rollback_insert def get_track_rollback(product_id): product_id_string = str(product_id) track_select = f"""SELECT track_name, upc, cd, track_id, orchdmclip, sample, dmclips, isrc, id, length_minute, length_seconds, writer, credit, mechanical, publisher, publish_company, dpd_license, explicit_lyrics, version, purchasable, download_price, p_line, hidden, offer_type, dig_distribution_type, third_party_publisher, royalty_collection, publishing_admin, sync_admin, additional_id, track_type, mp3_url, stereo_or_mono, bonus, recommended, youtube_matches, youtube_uploads, preorder_only, original_file_name, release_id, last_updated, meta_language, preview_start_time, closed_caption_exists, closed_caption_reason_id, vendor_track_identifier, recording_country, recording_year, original_track_id, us_publishing_obligation FROM track WHERE release_id = {product_id_string}""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(track_select) tracks = cursor.fetchall() db_connection.commit() db_connection.close() track_rollback_insert = f"""--rollback INSERT INTO track (track_name, upc, cd, track_id, orchdmclip, sample, dmclips, isrc, id, length_minute, length_seconds, writer, credit, mechanical, publisher, publish_company, dpd_license, explicit_lyrics, version, purchasable, download_price, p_line, hidden, offer_type, dig_distribution_type, third_party_publisher, royalty_collection, publishing_admin, sync_admin, additional_id, track_type, mp3_url, stereo_or_mono, bonus, recommended, youtube_matches, youtube_uploads, preorder_only, original_file_name, release_id, last_updated, meta_language, preview_start_time, closed_caption_exists, closed_caption_reason_id, vendor_track_identifier, recording_country, recording_year, original_track_id, us_publishing_obligation) VALUES\n""" for track in tracks: # replace None with null for SQL insert and make sure string, dates and datetimes are quoted track_fields = ["'" + str(x) + "'" if type(x) in [str, datetime.datetime, datetime.date] else 'null' if x is None else x for x in track] track_rollback_insert += f"""--rollback ({track_fields[0]}, {track_fields[1]}, {track_fields[2]}, {track_fields[3]}, {track_fields[4]}, {track_fields[5]}, {track_fields[6]}, {track_fields[7]}, {track_fields[8]}, {track_fields[9]}, {track_fields[10]}, {track_fields[11]}, {track_fields[12]}, {track_fields[13]}, {track_fields[14]}, {track_fields[15]}, {track_fields[16]}, {track_fields[17]}, {track_fields[18]}, {track_fields[19]}, {track_fields[20]}, {track_fields[21]}, {track_fields[22]}, {track_fields[23]}, {track_fields[24]}, {track_fields[25]}, {track_fields[26]}, {track_fields[27]}, {track_fields[28]}, {track_fields[29]}, {track_fields[30]}, {track_fields[31]}, {track_fields[32]}, {track_fields[33]}, {track_fields[34]}, {track_fields[35]}, {track_fields[36]}, {track_fields[37]}, {track_fields[38]}, {track_fields[39]}, {track_fields[40]}, {track_fields[41]}, {track_fields[42]}, {track_fields[43]}, {track_fields[44]}, {track_fields[45]}, {track_fields[46]}, {track_fields[47]}, {track_fields[48]}, {track_fields[49]}),\n""" track_rollback_insert = track_rollback_insert.rstrip('\n,') track_rollback_insert += f""";\n""" return track_rollback_insert def get_release_status_rollback(product_id): product_id_string = str(product_id) release_status_select = f"""SELECT release_status_id, release_id, status, date, changed_by, changed_by_type, upc_REMOVED, updated_timestamp FROM release_status WHERE release_id = {product_id_string}""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(release_status_select) release_statuses = cursor.fetchall() db_connection.commit() db_connection.close() release_status_rollback_insert = f"""--rollback INSERT INTO release_status (release_status_id, release_id, status, date, changed_by, changed_by_type, upc_REMOVED, updated_timestamp) VALUES\n""" for release_status in release_statuses: # replace None with null for SQL insert and make sure strings, dates and datetimes are quoted release_status_fields = ["'" + str(x) + "'" if type(x) in [str, datetime.datetime, datetime.date] else 'null' if x is None else x for x in release_status] release_status_rollback_insert += f"""--rollback ({release_status_fields[0]}, {release_status_fields[1]}, {release_status_fields[2]}, {release_status_fields[3]}, {release_status_fields[4]}, {release_status_fields[5]}, {release_status_fields[6]}, {release_status_fields[7]}),\n""" release_status_rollback_insert = release_status_rollback_insert.rstrip('\n,') release_status_rollback_insert += f""";\n""" return release_status_rollback_insert def get_release_subgenre_rollback(product_id): product_id_string = str(product_id) release_subgenre_select = f"""SELECT id, upc, subgenre_id, release_id FROM release_subgenre WHERE release_id = {product_id_string}""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(release_subgenre_select) release_subgenres = cursor.fetchall() db_connection.commit() db_connection.close() release_subgenre_rollback_insert = f"""--rollback INSERT INTO release_subgenre (id, upc, subgenre_id, release_id) VALUES\n""" for release_subgenre in release_subgenres: # replace None with null for SQL insert and make sure strings, dates and datetimes are quoted release_subgenre_fields = ["'" + str(x) + "'" if type(x) in [str, datetime.datetime, datetime.date] else 'null' if x is None else x for x in release_subgenre] release_subgenre_rollback_insert += f"""--rollback ({release_subgenre_fields[0]}, {release_subgenre_fields[1]}, {release_subgenre_fields[2]}, {release_subgenre_fields[3]}),\n""" release_subgenre_rollback_insert = release_subgenre_rollback_insert.rstrip('\n,') release_subgenre_rollback_insert += f""";\n""" return release_subgenre_rollback_insert def get_release_artist_rollback(product_id): product_id_string = str(product_id) release_artist_select = f"""SELECT release_artist_id, upc, role, release_id, artist_name, url, artist_info_id FROM release_artist WHERE release_id = {product_id_string}""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(release_artist_select) release_artists = cursor.fetchall() db_connection.commit() db_connection.close() release_artist_rollback_insert = f"""--rollback INSERT INTO release_artist (release_artist_id, upc, role, release_id, artist_name, url, artist_info_id) VALUES\n""" for release_artist in release_artists: # replace None with null for SQL insert and make sure strings, dates and datetimes are quoted release_artist_fields = ["'" + str(x) + "'" if type(x) in [str, datetime.datetime, datetime.date] else 'null' if x is None else x for x in release_artist] release_artist_rollback_insert += f"""--rollback ({release_artist_fields[0]}, {release_artist_fields[1]}, {release_artist_fields[2]}, {release_artist_fields[3]}, {release_artist_fields[4]}, {release_artist_fields[5]}, {release_artist_fields[6]}),\n""" release_artist_rollback_insert = release_artist_rollback_insert.rstrip('\n,') release_artist_rollback_insert += f""";\n""" return release_artist_rollback_insert def get_release_logging_rollback(product_id): product_id_string = str(product_id) release_logging_select = f"""SELECT id, release_id, changed_by_type, changed_by, updated_timestamp FROM release_logging WHERE release_id = {product_id_string}""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(release_logging_select) release_logging_rows = cursor.fetchall() db_connection.commit() db_connection.close() release_logging_rollback_insert = f"""--rollback INSERT INTO release_logging (id, release_id, changed_by_type, changed_by, updated_timestamp) VALUES\n""" for row in release_logging_rows: fields = ["'" + str(x) + "'" if type(x) in [str, datetime.datetime, datetime.date] else 'null' if x is None else x for x in row] release_logging_rollback_insert += f"""--rollback ({fields[0]}, {fields[1]}, {fields[2]}, {fields[3]}, {fields[4]}),\n""" release_logging_rollback_insert = release_logging_rollback_insert.rstrip('\n,') release_logging_rollback_insert += f""";\n""" return release_logging_rollback_insert def get_release_approval_queue_rollback(product_id): product_id_string = str(product_id) release_approval_queue_select = f"""SELECT release_approval_id, release_id, release_correction_id, checked_out_by, admin_approval, status, approved_by, last_updated, date_submitted, initiate_errorcorrection, submitted_by, changed_by_type, changed_by FROM release_approval_queue WHERE release_id = {product_id_string}""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(release_approval_queue_select) release_approval_queue_rows = cursor.fetchall() db_connection.commit() db_connection.close() release_approval_queue_rollback_insert = f"""--rollback INSERT INTO release_approval_queue (release_approval_id, release_id, release_correction_id, checked_out_by, admin_approval, status, approved_by, last_updated, date_submitted, initiate_errorcorrection, submitted_by, changed_by_type, changed_by) VALUES\n""" for row in release_approval_queue_rows: fields = ["'" + str(x) + "'" if type(x) in [str, datetime.datetime, datetime.date] else 'null' if x is None else x for x in row] release_approval_queue_rollback_insert += f"""--rollback ({fields[0]}, {fields[1]}, {fields[2]}, {fields[3]}, {fields[4]}, {fields[5]}, {fields[6]}, {fields[7]}, {fields[8]}, {fields[9]}, {fields[10]}, {fields[11]}, {fields[12]}),\n""" release_approval_queue_rollback_insert = release_approval_queue_rollback_insert.rstrip('\n,') release_approval_queue_rollback_insert += f""";\n""" return release_approval_queue_rollback_insert def get_track_master_rights_rollback_sql(track_ids): track_ids_string = ', '.join(str(t) for t in track_ids) track_master_rights_select = f"""SELECT master_rights_id, track_id, is_owner, rights_level FROM track_master_rights WHERE track_id IN ({track_ids_string})""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(track_master_rights_select) track_master_rights = cursor.fetchall() db_connection.commit() db_connection.close() track_master_rights_rollback_insert = f"""--rollback INSERT INTO track_master_rights (master_rights_id, track_id, is_owner, rights_level) VALUES\n""" for track_master_rights_item in track_master_rights: # replace None with null for SQL insert and make sure strings, dates and datetimes are quoted track_master_rights_field = ["'" + str(x) + "'" if type(x) in [str, datetime.datetime, datetime.date] else 'null' if x is None else x for x in track_master_rights_item] track_master_rights_rollback_insert += f"""--rollback ({track_master_rights_field[0]}, {track_master_rights_field[1]}, {track_master_rights_field[2]}, {track_master_rights_field[3]}),\n""" track_master_rights_rollback_insert = track_master_rights_rollback_insert.rstrip('\n,') track_master_rights_rollback_insert += f""";\n""" return track_master_rights_rollback_insert def get_track_artist_rollback_sql(track_ids): track_ids_string = ', '.join(str(t) for t in track_ids) track_artist_select = f"""SELECT id, track_id, type, name, artist_info_id FROM track_artist WHERE track_id IN ({track_ids_string})""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(track_artist_select) track_artists = cursor.fetchall() db_connection.commit() db_connection.close() track_artist_rollback_insert = f"""--rollback INSERT INTO track_artist (id, track_id, type, name, artist_info_id) VALUES\n""" for track_artist in track_artists: # replace None with null for SQL insert and make sure strings, dates and datetimes are quoted track_artist_field = [ "'" + str(x) + "'" if type(x) in [str, datetime.datetime, datetime.date] else 'null' if x is None else x for x in track_artist] track_artist_rollback_insert += f"""--rollback ({track_artist_field[0]}, {track_artist_field[1]}, {track_artist_field[2]}, {track_artist_field[3]}, {track_artist_field[4]}),\n""" track_artist_rollback_insert = track_artist_rollback_insert.rstrip('\n,') track_artist_rollback_insert += f""";\n""" return track_artist_rollback_insert def get_track_writer_rollback(upc): upc_string = str(upc) track_writer_select = f"""SELECT track_writer_id, writer_name, upc, cd, track_id, unique_track_id, artist_info_id, label_participant_id FROM track_writer WHERE upc = {upc_string}""" db_connection = connection.database_connection() cursor = db_connection.cursor() cursor.execute(track_writer_select) track_writers = cursor.fetchall() db_connection.commit() db_connection.close() track_writer_rollback_insert = f"""--rollback INSERT INTO track_writer (track_writer_id, writer_name, upc, cd, track_id, unique_track_id, artist_info_id, label_participant_id) VALUES\n""" for track_writer in track_writers: # replace None with null for SQL insert and make sure strings, dates and datetimes are quoted track_writer_fields = ["'" + str(x) + "'" if type(x) in [str, datetime.datetime, datetime.date] else 'null' if x is None else x for x in track_writer] track_writer_rollback_insert += f"""--rollback ({track_writer_fields[0]}, {track_writer_fields[1]}, {track_writer_fields[2]}, {track_writer_fields[3]}, {track_writer_fields[4]}, {track_writer_fields[5]}, {track_writer_fields[6]}, {track_writer_fields[7]}),\n""" track_writer_rollback_insert = track_writer_rollback_insert.rstrip('\n,') track_writer_rollback_insert += f""";\n""" return track_writer_rollback_insert def get_deletion_sql_for_products(product_id_list, logger): if len(product_id_list) <= 1000: deletion_sql = '' for product_id in product_id_list: upc = get_upc(product_id) track_ids = get_track_ids(product_id) track_artist_deletion_sql = '' track_master_rights_deletion_sql = '' for track_id in track_ids: track_artist_deletion_sql += f"""DELETE FROM track_artist WHERE track_id = {track_id};\n""" track_master_rights_deletion_sql += f"""DELETE FROM track_master_rights WHERE track_id = {track_id};\n""" product_rollback_sql = get_product_rollback(product_id) track_rollback_sql = get_track_rollback(product_id) release_status_rollback_sql = get_release_status_rollback(product_id) release_subgenre_rollback_sql = get_release_subgenre_rollback(product_id) release_logging_rollback_sql = get_release_logging_rollback(product_id) release_artist_rollback_sql = get_release_artist_rollback(product_id) release_approval_queue_rollback_sql = get_release_approval_queue_rollback(product_id) track_master_rights_rollback_sql = get_track_master_rights_rollback_sql(track_ids) track_artist_rollback_sql = get_track_artist_rollback_sql(track_ids) track_writer_rollback_sql = get_track_writer_rollback(upc) deletion_sql += f"""DELETE FROM track_writer WHERE upc = {upc}; {track_artist_deletion_sql} {track_master_rights_deletion_sql} DELETE FROM release_artist WHERE release_id = {product_id}; DELETE FROM release_subgenre WHERE release_id = {product_id}; DELETE FROM release_status WHERE release_id = {product_id}; DELETE FROM track WHERE release_id = {product_id}; DELETE FROM release_approval_queue WHERE release_id = {product_id}; DELETE FROM release_logging WHERE release_id = {product_id}; DELETE FROM releases WHERE release_id = {product_id}; {product_rollback_sql} {track_rollback_sql} {release_status_rollback_sql} {release_subgenre_rollback_sql} {release_logging_rollback_sql} {release_artist_rollback_sql} {release_approval_queue_rollback_sql} {track_master_rights_rollback_sql} {track_artist_rollback_sql} {track_writer_rollback_sql} """ return deletion_sql else: logger.info('Number of product IDs should not be more than 1000') def main(correlation_id=None): logger = config.get_current_logger(correlation_id) logger.info('Running script to get SME product deletion queries') product_id_list = get_argument() deletion_sql = get_deletion_sql_for_products(product_id_list, logger) print(deletion_sql) if __name__ == '__main__': main()