def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dp" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS tm_inventorysources" cursor.execute(sql) sql = "CREATE TABLE tm_inventorysources (date varchar(25), inventory_source varchar(255), ad_opportunities bigint, \ ad_attempts bigint, ad_impressions bigint, ad_revenue decimal(15, 5), ecpm decimal(15, 5), platform int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token( "9fadb507-01e9-4a2a-b9c5-c9d65d93396e", "y0QxY3DC7vbczeW7nBOsZg") print(logintoken) result = aol_api.run_existing_report(logintoken, "169500") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: date = x['row'][0] inventory_source = x['row'][1] ad_opportunities = x['row'][2] ad_attempts = x['row'][3] ad_impressions = x['row'][4] ad_revenue = x['row'][5] ecpm = x['row'][6] platform = '5' list = (date, inventory_source, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, platform) #print(list) sql = """INSERT INTO tm_inventorysources VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % \ (date, inventory_source, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_eom" api = "adtrans" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS adtrans_market_public_EOM" cursor.execute(sql) sql = "CREATE TABLE adtrans_market_public_EOM (date varchar(25), inventory_source varchar(255), geo_country varchar(50), \ buyer_organization varchar(100), ad_opportunities varchar(2), ad_attempts bigint, ad_impressions bigint, \ ad_revenue decimal(15, 5), ecpm decimal(6, 4), media_spend decimal(15, 5), completed_views int, clicks int, \ platform int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print(logintoken) result = aol_api.run_existing_report(logintoken, "193905") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: date = x['row'][0] inventory_source = x['row'][1].replace("'", " -") geo_country = x['row'][2].replace(",", " -") buyer_organization = x['row'][3].replace('"', " ") ad_opportunities = '0' ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_revenue = x['row'][7] ecpm = x['row'][8] media_spend = x['row'][9] completed_views = x['row'][10] clicks = x['row'][11].replace(" ", "0") platform = '6' list = (date, inventory_source, geo_country, buyer_organization, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, media_spend, completed_views, clicks, platform) #print(list) sql = """INSERT INTO adtrans_market_public_EOM VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s")""" % (date, inventory_source, geo_country, buyer_organization, ad_opportunities, \ ad_attempts, ad_impressions, ad_revenue, ecpm, media_spend, completed_views, clicks, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" api = "aol" report_book = [190031, 190032, 190147, 190148] # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS v3_core_today_media" cursor.execute(sql) sql = "CREATE TABLE v3_core_today_media (inventory_source varchar(255), media varchar(255), \ ad_opportunities bigint, market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, \ ad_errors bigint, ad_revenue decimal(15, 5), clicks int, iab_viewability_measurable_ad_impressions bigint, \ iab_viewable_ad_impressions bigint)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print(logintoken) for report in report_book: result = aol_api.run_existing_report(logintoken, str(report)) #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: inventory_source = x['row'][0] media = x['row'][1].replace('"', " ") ad_opportunities = x['row'][2] market_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_errors = x['row'][6] ad_revenue = x['row'][7] clicks = x['row'][8] iab_viewability_measurable_ad_impressions = x['row'][9] iab_viewable_ad_impressions = x['row'][10] list = (inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions) #print(list) sql = """INSERT INTO v3_core_today_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s")""" % (inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, \ ad_impressions, ad_errors, ad_revenue, clicks, iab_viewability_measurable_ad_impressions, \ iab_viewable_ad_impressions) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed') except Error as error: print(error) finally: conn.close() print('Connection Closed')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" api = "tm" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS tm_core_yesterday" cursor.execute(sql) sql = "CREATE TABLE tm_core_yesterday (date varchar(25), hour int, inventory_source varchar(255), ad_opportunities bigint, \ market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, ad_revenue decimal(15, 5), \ aol_cost decimal(15, 5), epiphany_gross_revenue decimal(15, 5), tm_revenue decimal(15, 5), \ total_clicks int, iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint, platform int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print logintoken result = aol_api.run_existing_report(logintoken, "169838") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: date = x['row'][0] hour = x['row'][1] inventory_source = x['row'][2] ad_opportunities = x['row'][3] market_opportunities = x['row'][4] ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_errors = x['row'][7] ad_revenue = x['row'][8] aol_cost = x['row'][8] epiphany_gross_revenue = x['row'][8] tm_revenue = x['row'][8] total_clicks = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] platform = '5' list = (date, hour, inventory_source, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, tm_revenue, total_clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) # print(list) sql = """INSERT INTO tm_core_yesterday VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, \ "%s"*.64, "%s"*.24, "%s", "%s", "%s", "%s")""" % (date, hour, inventory_source, ad_opportunities, \ market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, \ epiphany_gross_revenue, tm_revenue, total_clicks, iab_viewability_measurable_ad_impressions, \ iab_viewable_ad_impressions, platform) cursor.execute(sql) cursor.execute('commit') else: print('connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" # Connect to DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS v3_market_last60" cursor.execute(sql) sql = "CREATE TABLE v3_market_last60 (date varchar(50), buyer_organization varchar(255), inventory_source varchar(255), \ market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, ad_revenue decimal(15, 5), \ total_clicks int, iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token( "daf5fa63-56c4-4279-842e-639c9af75750", "C5eBl8aErmCMO2+U85LGpw") print(logintoken) result = aol_api.run_existing_report(logintoken, "150665") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: date = x['row'][0] buyer_organization = x['row'][1] inventory_source = x['row'][2] market_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_errors = x['row'][6] ad_revenue = x['row'][7] total_clicks = x['row'][8] iab_viewability_measurable_ad_impressions = x['row'][9] iab_viewable_ad_impressions = x['row'][10] list = (date, buyer_organization, inventory_source, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, total_clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions) #print(list) sql = """INSERT INTO v3_market_last60 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s")""" % (date, buyer_organization, inventory_source, market_opportunities, \ ad_attempts, ad_impressions, ad_errors, ad_revenue, total_clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed') except Error as error: print(error) finally: conn.close() print('Connections closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dp" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS dna_inventoryreport" cursor.execute(sql) sql = "CREATE TABLE dna_inventoryreport (date varchar(50), inventory_source varchar(255), geo_country varchar(50), \ ad_opportunities bigint, ad_attempts bigint, ad_impressions bigint, \ ad_revenue decimal(15, 5), ecpm decimal(15, 5), platform int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token( "daf5fa63-56c4-4279-842e-639c9af75750", "C5eBl8aErmCMO2+U85LGpw") print(logintoken) result = aol_api.run_existing_report(logintoken, "193231") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: date = x['row'][0] inventory_source = x['row'][1] geo_country = x['row'][2] ad_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_revenue = x['row'][6] ecpm = x['row'][7] platform = '1' list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \ platform) # print(list) sql = """INSERT INTO dna_inventoryreport VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % \ (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \ platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS dc_market_today" cursor.execute(sql) sql = "CREATE TABLE dc_market_today (hour int, buyer_organization varchar(255), inventory_source varchar(255), \ market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, ad_revenue decimal(15, 5), \ total_clicks int, iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint, platform int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token( "0e30062d-6746-4a9b-882a-3f61185479c7", "9O7SnFq/yDbNK+4M2bkSqg") print logintoken result = aol_api.run_existing_report(logintoken, "143818") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: hour = x['row'][0] buyer_organization = x['row'][1] inventory_source = x['row'][2] market_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_errors = x['row'][6] ad_revenue = x['row'][7] total_clicks = x['row'][8] iab_viewability_measurable_ad_impressions = x['row'][9] iab_viewable_ad_impressions = x['row'][10] platform = '2' list = (hour, buyer_organization, inventory_source, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, total_clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) #print(list) sql = """INSERT INTO dc_market_today VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s")""" % (hour, buyer_organization, inventory_source, market_opportunities, \ ad_attempts, ad_impressions, ad_errors, ad_revenue, \ total_clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dl" api = "adsym" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print(logintoken) result = aol_api.run_existing_report(logintoken, "190593") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: rownum = '' date = x['row'][0] inventory_source = x['row'][1].replace("'", " -").replace('"', "") geo_country = x['row'][2].replace("'", "") media = x['row'][3].replace('"', "").replace("'", "") ad_opportunities = x['row'][4] ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_revenue = x['row'][7] ecpm = x['row'][8] ad_errors = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] market_ops = x['row'][12] clicks = x['row'][13].replace(" ", "0") list = (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks) #print(list) sql = """INSERT INTO adsym_InventorySources_v2 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s")""" % (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" # Connect to DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS adtrans_market_last60" cursor.execute(sql) sql = "CREATE TABLE adtrans_market_last60 (date varchar(50), buyer_organization varchar(255), inventory_source varchar(255), \ market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, ad_revenue decimal(15, 5), \ aol_cost decimal(15, 5), epiphany_gross_revenue decimal(15, 5), adtrans_revenue decimal(15, 5), total_clicks int, \ iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint, platform int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token( "629065c0-a967-473b-b62e-cf4353d9f5c7", "3GLSSYb1BxWdlN3Iu8/p7A") print(logintoken) result = aol_api.run_existing_report(logintoken, "169875") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: date = x['row'][0] buyer_organization = x['row'][1] inventory_source = x['row'][2] market_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_errors = x['row'][6] ad_revenue = x['row'][7] aol_cost = x['row'][7] epiphany_gross_revenue = x['row'][7] adtrans_revenue = x['row'][7] total_clicks = x['row'][8] iab_viewability_measurable_ad_impressions = x['row'][9] iab_viewable_ad_impressions = x['row'][10] platform = '6' list = (date, buyer_organization, inventory_source, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, adtrans_revenue, total_clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) #print(list) sql = """INSERT INTO adtrans_market_last60 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, "%s"*.67, \ "%s"*.24, "%s", "%s", "%s", "%s")""" % (date, buyer_organization, inventory_source, market_opportunities, \ ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, adtrans_revenue, \ total_clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed') except Error as error: print(error) finally: conn.close() print('Connections closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dl" api = "dc" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to databse...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS dc_InventorySources_v2" cursor.execute(sql) sql = "CREATE TABLE dc_InventorySources_v2 (rownum INT NOT NULL AUTO_INCREMENT PRIMARY KEY, date varchar(25), inventory_source varchar(255), geo_country varchar(50), \ media varchar(255), ad_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_revenue decimal(15,5), \ ecpm decimal(6,4), ad_errors int, iab_viewability_measurable_ad_impressions bigint, \ iab_viewable_ad_impressions bigint, market_ops varchar(255), clicks int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print(logintoken) result = aol_api.run_existing_report(logintoken, "190586") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: rownum = '' date = x['row'][0] inventory_source = x['row'][1].replace("'", " -").replace('"', "") geo_country = x['row'][2].replace("'", "") media = x['row'][3].replace('"', "").replace("'", "") ad_opportunities = x['row'][4] ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_revenue = x['row'][7] ecpm = x['row'][8] ad_errors = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] market_ops = x['row'][12] clicks = x['row'][13].replace(" ", "0") list = (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks) #print(list) sql = """INSERT INTO dc_InventorySources_v2 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s")""" % (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" report_type = "core_today_media" p_name = sys.argv[1] p_id = platforms[p_name]["id"] gross_rev = platforms[p_name]["fee"] r = fees["aol_platform"] a_cost = fees["aol_cost"] platform_rev = p_name + "_revenue" db_updated = False # Connect To DB: db_config = read_db_config(db) try: #print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): #print('connection established.') cursor = conn.cursor() # calls get_access_token function and starts script logintoken = aol_api.get_access_token(p_name) #print(logintoken) for report in report_book[report_type][p_name]: result = aol_api.run_existing_report(logintoken, str(report)) #print(result) if len(result) == 0: break row_count_value = json.loads(result)['row_count'] if int(row_count_value) >= 1: if db_updated == False: sql = "DROP TABLE IF EXISTS " + p_name + "_core_today_media" cursor.execute(sql) sql = "CREATE TABLE " + p_name + "_core_today_media (date varchar(50), hour int, inventory_source varchar(255), \ media varchar(255), ad_opportunities bigint, market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, \ ad_errors bigint, ad_revenue decimal(15, 5), aol_cost decimal(15, 5), epiphany_gross_revenue decimal(15, 5), \ " + p_name + "_revenue decimal(15, 5), clicks int, iab_viewability_measurable_ad_impressions bigint, \ iab_viewable_ad_impressions bigint, platform int)" cursor.execute(sql) db_updated = True print( str(todaysdate) + " Running " + p_name + "_core_today_media report # " + str(report)) for x in json.loads(result)['data']: date = x['row'][0] hour = x['row'][1] inventory_source = x['row'][2] media = x['row'][3].replace('"', " ") ad_opportunities = x['row'][4] market_opportunities = x['row'][5] ad_attempts = x['row'][6] ad_impressions = x['row'][7] ad_errors = x['row'][8] ad_revenue = x['row'][9] aol_cos = x['row'][9] epiphany_gross_rev = x['row'][9] platform_rev = x['row'][9] clicks = x['row'][10] iab_viewability_measurable_ad_impressions = x['row'][11] iab_viewable_ad_impressions = x['row'][12] platform = str(p_id) list = (date, hour, inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, \ ad_impressions, ad_errors, ad_revenue, aol_cos, epiphany_gross_rev, platform_rev, \ clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) #print(list) if p_name == 'dna': aol_cost = "0" epiphany_gross_revenue = "0" platform_revenue = "0" else: aol_cost = float(float(aol_cos) * float(a_cost)) epiphany_gross_revenue = float( float(epiphany_gross_rev) * float(gross_rev)) platform_revenue = float( float(platform_rev) * float(r)) sql = """INSERT INTO """ + p_name + """_core_today_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s", "%s", "%s", "%s" ,"%s", "%s", "%s", "%s")""" % (date, hour, inventory_source, \ media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, \ aol_cost, epiphany_gross_revenue, platform_revenue, clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed') except Error as error: print(error) finally: conn.close()
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" api = "tm" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('connection established.') cursor = conn.cursor() # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print(logintoken) result = aol_api.run_existing_report(logintoken, "190142") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: inventory_source = x['row'][0] media = x['row'][1] ad_opportunities = x['row'][2] market_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_errors = x['row'][6] ad_revenue = x['row'][7] aol_cost = x['row'][7] epiphany_gross_revenue = x['row'][7] tm_revenue = x['row'][7] clicks = x['row'][8] iab_viewability_measurable_ad_impressions = x['row'][9] iab_viewable_ad_impressions = x['row'][10] platform = '5' list =(inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, tm_revenue, clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) #print(list) sql = """INSERT INTO tm_core_today_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, \ "%s"*.64, "%s"*.24, "%s", "%s", "%s", "%s")""" % (inventory_source, media, ad_opportunities, \ market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, \ epiphany_gross_revenue, tm_revenue, clicks, iab_viewability_measurable_ad_impressions, \ iab_viewable_ad_impressions, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed') except Error as error: print(error) finally: conn.close() print('Connection Closed')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dl" api = "aol" platforms=["dna", "dc", "adsym", "tm", "adtrans"] report_book = { "dna" : { "189983", "189984", "189985", "189986", "189987", "189989"}, "dc" : {"190586", "190587", "190589", "190590", "190591"}, "adsym" : {"190592", "190593", "190594", "190595", "190596", "190597"}, "tm" : {"190598", "190599", "190601", "190602", "190603", "190605"}, "adtrans" : {"190605", "190606", "190607", "190609", "190610"} } # Connect To DB: db_config = read_db_config(db) try: print('Connecting to databse...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established') cursor = conn.cursor() for platform in platforms: sql = "DROP TABLE IF EXISTS " + str(platform) + "_InventorySources_v2" cursor.execute(sql) sql = "CREATE TABLE " + str(platform) + "_InventorySources_v2 (rownum INT NOT NULL AUTO_INCREMENT PRIMARY KEY, date varchar(25), \ inventory_source varchar(255), geo_country varchar(50), media varchar(255), ad_opportunities bigint, \ ad_attempts bigint, ad_impressions bigint, ad_revenue decimal(15,5), ecpm decimal(6,4), ad_errors int, \ iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint, market_ops varchar(255), clicks int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print(logintoken) for report in report_book[platform]: print("Running report " + str(platform) + "_InventorySources_v2. Report # " + str(report)) result = aol_api.run_existing_report(logintoken, str(report)) #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: rownum = '' date = x['row'][0] inventory_source = x['row'][1].replace("'", " -").replace('"', "") geo_country = x['row'][2].replace("'", "") media = x['row'][3].replace('"', "").replace("'", "") ad_opportunities = x['row'][4] ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_revenue = x['row'][7] ecpm = x['row'][8] ad_errors = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] market_ops = x['row'][12] clicks = x['row'][13].replace(" ", "0") list = (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks) #print(list) sql = """INSERT INTO """ + str(platform) + """_InventorySources_v2 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s")""" % (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dl" report_type = "Domain_v2" p_name = sys.argv[1] db_updated = False # Connect To DB: db_config = read_db_config(db) try: #print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): #print('Connection established.') cursor = conn.cursor() # calls get_access_token function and starts script logintoken = aol_api.get_access_token(p_name) #print(logintoken) for report in report_book[report_type][p_name]: result = aol_api.run_existing_report(logintoken, str(report)) #print(result) if len(result) == 0: break if len(result) >= 1: if db_updated == False: sql = "DROP TABLE IF EXISTS " + p_name + "_domain_v2" cursor.execute(sql) sql = "CREATE TABLE " + p_name + "_domain_v2 (date varchar(25), media varchar(255) CHARACTER SET 'utf8', \ ad_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_revenue decimal(15, 5), ecpm decimal(6, 4))" cursor.execute(sql) db_updated = True print( str(todaysdate) + " Running " + p_name + "_domain_v2 with report # " + str(report)) for x in json.loads(result)['data']: date = x['row'][0] media = x['row'][1].replace('"', " ") ad_opportunities = x['row'][2] ad_attempts = x['row'][3] ad_impressions = x['row'][4] ad_revenue = x['row'][5] ecpm = x['row'][6] list = (date, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm) #print(list) sql = """INSERT INTO """ + p_name + """_domain_v2 VALUES ("%s", "%s", "%s", "%s", "%s",\ "%s", "%s")""" % (date, media, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close()
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dl" report_type = "InventorySources_v2" p_name = sys.argv[1] db_updated = False # Connect To DB: db_config = read_db_config(db) try: #print('Connecting to databse...') conn = MySQLConnection(**db_config) if conn.is_connected(): #print('Connection established') cursor = conn.cursor() # calls get_access_token function and starts script logintoken = aol_api.get_access_token(p_name) #print(logintoken) for report in report_book[report_type][p_name]: result = aol_api.run_existing_report(logintoken, str(report)) #print(result) if len(result) == 0: break if len(result) >= 1: if db_updated == False: sql = "DROP TABLE IF EXISTS " + p_name + "_InventorySources_v2" cursor.execute(sql) sql = "CREATE TABLE " + p_name + "_InventorySources_v2 (rownum INT NOT NULL AUTO_INCREMENT PRIMARY KEY, \ date varchar(25), inventory_source varchar(255), geo_country varchar(50), media varchar(255), \ ad_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_revenue decimal(15,5), \ ecpm decimal(6,4), ad_errors int, iab_viewability_measurable_ad_impressions bigint, \ iab_viewable_ad_impressions bigint, market_ops varchar(255), clicks int)" cursor.execute(sql) db_updated = True print( str(todaysdate) + " Running " + p_name + "_InventorySources_v2 Report # " + str(report)) for x in json.loads(result)['data']: rownum = '' date = x['row'][0] inventory_source = x['row'][1].replace("'", " -").replace( '"', "") geo_country = x['row'][2].replace("'", "") media = x['row'][3].replace('"', "").replace("'", "") ad_opportunities = x['row'][4] ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_revenue = x['row'][7] ecpm = x['row'][8] ad_errors = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] market_ops = x['row'][12] clicks = x['row'][13].replace(" ", "0") list = (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, \ market_ops, clicks) #print(list) sql = """INSERT INTO """ + p_name + """_InventorySources_v2 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % (rownum, date, inventory_source, geo_country, \ media, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, ad_errors, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close()