import os import traceback import config import xlrd import mysql """Imports PPL Performer data This script reads PPL Performer data and generates sql insert script. Dependencies: - set art_relations and royalty_collections credentials in .env ( run pyvenv-3.4 env) - add sheets to import in pplsheet folder(in format replaceMe.xlsx) - set ppl sheets path, row_start_slice, col_start_slice and changeset_author in config (see config.py) """ def main(): try: print '--liquibase formatted sql \n' path = config.PPL_SHEETS_PATH changeset = 1 for filename in os.listdir(path): if filename.endswith('.xlsx') or filename.endswith('.xls'): data_sheet_index = int(config.DATA_SHEET_INDEX) print '\n--filename : ' + filename book = xlrd.open_workbook(path + '/' + filename) data_sheet = book.sheet_by_index(data_sheet_index) row_start_slice = int(config.ROW_START_SLICE) row_end_slice = data_sheet.nrows col_start_slice = int(config.COL_START_SLICE) col_end_slice = data_sheet.ncols tracks_info = [data_sheet.row_slice(rowx=i, start_colx=0, end_colx=2) for i in xrange(row_start_slice, row_end_slice)] tracks_info_data = get_tracks_info(tracks_info) final_data_list = [] for i in xrange(row_start_slice, row_end_slice): k = col_start_slice label_id = int(data_sheet.cell(i, 0).value) isrc = data_sheet.cell(i, 1).value while k < col_end_slice: cells = data_sheet.row_slice(rowx=i, start_colx=k, end_colx=k+4) firstname = cells[0].value.encode('utf-8').strip() if cells[0] else '' lastname = cells[1].value.encode('utf-8').strip() if cells[1] else '' performer_name = (str(firstname) + ' ' + str(lastname)).strip() category = cells[2].value.strip() if cells[2] else '' role = cells[3].value.strip() if cells[3] else '' category_id = str(get_category_id(category)) role_id = str(get_role_id(role)) unique_track_ids_list = [tracks[0] for tracks in tracks_info_data \ if tracks[2] == label_id and tracks[1] == isrc] for track in unique_track_ids_list: data_list = { 'performer_name': str(performer_name), 'role_id': role_id, 'category_id': category_id, 'unique_track_id': str(track), } final_data_list.append(data_list) k = k + 4 if len(final_data_list) > 0: print '--changeset {}:{} \n'.format(config.CHANGESET_AUTHOR, str(changeset)) print format_insert(final_data_list) print'\n' print format_rollback(final_data_list) else: print '--not data found filename : ' + filename changeset = changeset+1 except Exception as ex: print "Error occurred due to: " + str(ex.message) traceback.print_exc() # format insert query def format_insert(data_list): qryList = [] for rows in data_list: if rows['unique_track_id'] != '' and rows['performer_name'] != '' \ and rows['category_id'] != '' and rows['role_id'] != '': qryList.append("(" + rows['unique_track_id'] + ",'" + rows['performer_name'] + "'," + rows['category_id'] + "," + rows['role_id'] + ")") return "INSERT INTO track_performer (unique_track_id,performer_name,ppl_category_id,performer_role_id) \n " \ "VALUES " + (',\n'.join(qryList))+";" if len(qryList) > 0 else 'NOT FOUND' # format rollback query def format_rollback(data_list): unique_track_id_list = [rows['unique_track_id'] for rows in data_list if rows['unique_track_id'] != '' and rows['performer_name'] != '' and rows['category_id'] != '' and rows['role_id'] != ''] unique_track_ids = list(set(unique_track_id_list)) return "--rollback DELETE FROM track_performer WHERE unique_track_id in ({});".format(','.join(unique_track_ids)) if len(unique_track_ids) > 0 else 'NOT FOUND' # fetch tracks info from art_relations db for the given isrc and vendor_id list def get_tracks_info(data_list): qryList = [] for rows in data_list: qryList.append("(t.isrc = '"+str(rows[1].value)+"' AND ai.vendor_id = "+str(rows[0].value)+")") if len(qryList) > 0: track_query = """SELECT t.id,t.isrc,ai.vendor_id FROM track t JOIN releases r ON t.release_id = r.release_id JOIN artist_info ai ON ai.artist_id = r.artist_id WHERE {} ;""".format((' OR '.join(qryList))) art_relations_cursor.execute(track_query) return art_relations_cursor.fetchall() return qryList # map role_name and group to get role_id from the list of all roles def get_role_id(role_name): split_data = role_name.split('-') data = [row[0] for row in roles_list if str(split_data[0]) == str(row[2]) and str(split_data[1]) == str(row[1])] return data[0] if len(data) > 0 else '' # map category_name to get category_id from the list of all categories def get_category_id(category): category = 'Contracted Featured Artist' if category == 'Contracted Feature Artist' else category data = [row[0] for row in categories_list if category == row[1]] return data[0] if len(data) > 0 else '' # fetch list of all roles from royalty_collections db def get_roles_list(): role_query = """SELECT id,role,`group` FROM ppl_performer_role""" royalty_collections_cursor.execute(role_query) return royalty_collections_cursor.fetchall() # fetch list of all categories from royalty_collections db def get_categories_list(): category_query = """SELECT id,category FROM ppl_performer_category""" royalty_collections_cursor.execute(category_query) return royalty_collections_cursor.fetchall() roles_list = {} categories_list = {} art_relations_cursor = mysql.connect_db({ 'host': config.AR_DB_HOST, 'user': config.AR_DB_USERNAME, 'password': config.AR_DB_PASSWORD, 'db': config.AR_DATABASE }) # connect art_relations db royalty_collections_cursor = mysql.connect_db({ 'host': config.RC_DB_HOST, 'user': config.RC_DB_USERNAME, 'password': config.RC_DB_PASSWORD, 'db': config.RC_DATABASE }) # connect royalty_collections db if __name__ == '__main__': roles_list = get_roles_list() # fetch list of all roles from royalty_collections db categories_list = get_categories_list() # fetch list of all categories from royalty_collections db main()