def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sa" report_type = "site_additions" p_name = sys.argv[1] # 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 " + p_name + "_site_addition" cursor.execute(sql) sql = "CREATE TABLE " + p_name + "_site_addition (date varchar(50), media varchar(255), ad_revenue decimal(15, 5))" cursor.execute(sql) # 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]: print( str(todaytime) + " Running " + p_name + "_site addition with report # " + str(report)) result = aol_api.run_existing_report(logintoken, str(report)) #print(result) for x in json.loads(result)['data']: date = x['row'][0] media = x['row'][1] ad_revenue = x['row'][2] list = (date, media, ad_revenue) #print(list) sql = """INSERT INTO """ + p_name + """_site_addition VALUES ("%s", "%s", "%s")""" % ( date, media, ad_revenue) 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_sa" 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_site_addition" cursor.execute(sql) sql = "CREATE TABLE adtrans_site_addition (media varchar(255), ad_revenue decimal(15, 5))" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api_R.get_access_token(api) print(logintoken) result = aol_api_R.run_existing_report(logintoken, "197863") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: media = x['row'][0] ad_revenue = x['row'][1] list = (media, ad_revenue) #print(list) sql = """INSERT INTO adtrans_site_addition VALUES ("%s", "%s")""" % ( media, ad_revenue) 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 Springserve data and writes to MySQL table""" db = "mysql_eom" api = "springs" page = 1 # 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() # Create Table: sql = "DROP TABLE IF EXISTS springserve_market_private_EOM" cursor.execute(sql) sql = "CREATE TABLE springserve_market_private_EOM (date varchar(25), source_id varchar(10), supply_source varchar(255), \ country varchar(100), demand_partner varchar(100), tag_requests bigint, ad_impressions bigint, \ revenue decimal(15, 5), clicks bigint, platform int)" cursor.execute(sql) # call to get logintoken logintoken = springs_api.get_logintoken(api) print(logintoken) for page in range(1, 20): jsoninfo = { #"date_range": "Yesterday", "interval": "day", "timezone": "America/Los_Angeles", "dimensions": ["supply_tag_id", "country", "demand_partner_id"], #"reportFormat": "JSON", "start_date": "2017-10-01", "end_date": "2017-10-31", #"sort": [{ "field": "ad_revenue", "order": "desc"}] "page": 1 } result = springs_api.get_data(logintoken, jsoninfo) #print(result.text) info = json.loads(result.text) #print(info) # use default to populate null data default = 'Not Applicable' for x in info: date1 = x['date'] date = date1[:10] source_id = x['supply_tag_id'] supply_source = x['supply_tag_name'] country = x.get('country_code', default) demand_partner1 = x['demand_partner_name'] demand_partner_name = demand_partner1[:-4] tag_requests = x['demand_requests'] ad_impressions = x['impressions'] revenue = x['revenue'] clicks = x['clicks'] platform = 1 list = (date, source_id, supply_source, country, demand_partner_name, tag_requests, ad_impressions, \ revenue, clicks, platform) #print(list) sql = """INSERT INTO springserve_market_private_EOM VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s")""" % (date, source_id, supply_source, country, demand_partner_name, \ tag_requests, ad_impressions, revenue, 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_dl" api = "aol" # 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 dna_InventorySources_v2" cursor.execute(sql) sql = "CREATE TABLE dna_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, "189983") #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 dna_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" 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() sql = "DROP TABLE IF EXISTS adsym_market_last60" cursor.execute(sql) sql = "CREATE TABLE adsym_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), adsym_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, "161195") #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] adsym_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 = '4' list = (date, buyer_organization, inventory_source, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, adsym_revenue, total_clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) #print(list) sql = """INSERT INTO adsym_market_last60 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, "%s"*.56, \ "%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, adsym_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_dp" 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_inventoryreport" cursor.execute(sql) sql = "CREATE TABLE adtrans_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_R.get_access_token(api) print(logintoken) result = aol_api_R.run_existing_report(logintoken, "169697") #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 = '6' list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \ platform) #print(list) sql = """INSERT INTO adtrans_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 Springserve data and writes to MySQL table""" db = "mysql_sl" api = "springs" page = 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() # call to get logintoken logintoken = springs_api.get_logintoken(api) #print(logintoken) while True: # Dictionary holding parameters jsoninfo = { #"date_range": "Yesterday", "interval": "day", "timezone": "America/Los_Angeles", "dimensions": ["supply_tag_id"], "start_date": last60, "end_date": todaysdate, #"sort": [{ "field": "ad_revenue", "order": "desc"}] "page": str(page) } result = springs_api.get_data(logintoken, jsoninfo) print(result.text) info = json.loads(result.text) if len(info) == 0: break if len(info) >= 1: if db_updated == False: sql = "DROP TABLE IF EXISTS springserve_core_last60" cursor.execute(sql) sql = "CREATE TABLE springserve_core_last60 (DATE VARCHAR(25), source_id VARCHAR(10), \ supply_source VARCHAR(255), Total_Requests BIGINT, usable_requests BIGINT, \ opportunities BIGINT, ad_impressions BIGINT, revenue DECIMAL(15, 5), clicks BIGINT)" cursor.execute(sql) db_updatd = True print( str(todaytime) + " Running springserve_core_last60. Page # " + str(page) + " Count " + str(len(info))) for x in info: date1 = x['date'] date = date1[:10] source_id = x['supply_tag_id'] supply_source = x['supply_tag_name'] Total_Requests = x['total_requests'] usable_requests = x['usable_requests'] opportunities = x['opportunities'] ad_impressions = x['total_impressions'] revenue = x['revenue'] clicks = x['clicks'] list = (date, source_id, supply_source, Total_Requests, usable_requests, \ opportunities, ad_impressions, revenue, clicks) #print(list) sql = """INSERT INTO springserve_core_last60 VALUES ("%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s", "%s")""" % (date, source_id, supply_source, \ Total_Requests, usable_requests, opportunities, ad_impressions, revenue, clicks) cursor.execute(sql) cursor.execute('commit') page += 1 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" 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, "190603") #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 tm_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 Springserve data and writes to MySQL table""" db = "mysql_sl" api = "springs" # 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() # Create Table: sql = "DROP TABLE IF EXISTS springserve_core_last60" cursor.execute(sql) sql = "CREATE TABLE springserve_core_last60 (DATE VARCHAR(25), source_id VARCHAR(10), supply_source VARCHAR(255), \ Total_Requests BIGINT, usable_requests BIGINT, opportunities BIGINT, ad_impressions BIGINT, \ revenue DECIMAL(15, 5), clicks BIGINT)" cursor.execute(sql) # call to get logintoken logintoken = springs_api.get_logintoken(api) print(logintoken) jsoninfo = { #"date_range": "Yesterday", "interval": "day", "timezone": "America/Los_Angeles", "dimensions": ["supply_tag_id"], "start_date": last60, "end_date": todaysdate, #"sort": [{ "field": "ad_revenue", "order": "desc"}] #page": 2 } result = springs_api.get_data(logintoken, jsoninfo) #print(result.text) info = json.loads(result.text) #print(info) # use default to populate null data default = 'Not Applicable' for x in info: date1 = x['date'] date = date1[:10] source_id = x['supply_tag_id'] supply_source = x['supply_tag_name'] Total_Requests = x['total_requests'] usable_requests = x['usable_requests'] opportunities = x['opportunities'] ad_impressions = x['total_impressions'] revenue = x['revenue'] clicks = x['clicks'] list = (date, source_id, supply_source, Total_Requests, usable_requests, opportunities, ad_impressions, revenue, clicks) #print(list) sql = """INSERT INTO springserve_core_last60 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % \ (date, source_id, supply_source, Total_Requests, usable_requests, opportunities, ad_impressions, revenue, 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_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 dc_market_public" cursor.execute(sql) sql = "CREATE TABLE dc_market_public (date varchar(25), inventory_source varchar(255), geo_country varchar(50), \ ad_opportunities varchar(2), ad_attempts bigint, ad_impressions bigint, ad_revenue decimal(15, 5), \ media_spend decimal(15, 5), ecpm decimal(6, 4), completed_views int, clicks int, 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, "143155") #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(",", " -") ad_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_revenue = x['row'][6] ecpm = x['row'][7] media_spend = x['row'][8] completed_views = x['row'][9] clicks = x['row'][10].replace(" ", "0") platform = '2' list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, media_spend, completed_views, clicks, platform) #print(list) sql = """INSERT INTO dc_market_public VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s")""" % (date, inventory_source, geo_country, 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_sa" platform = "adsym" report = "197789" platforms = ["adsym", "adtrans", "dc", "tm"] #platform = ("adsym", "adtrans", "dc", "tm") report_book = [197789, 197863, 197788, 197791] # 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() for platform, report in zip(platforms, report_book): print("Running " + str(platform) + "_site addition with report # " + str(report)) sql = "DROP TABLE IF EXISTS " + str( platform) + "_site_addition" cursor.execute(sql) sql = "CREATE TABLE " + str( platform ) + "_site_addition (media varchar(255), ad_revenue decimal(15, 5))" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token(platform) print(logintoken) result = aol_api.run_existing_report(logintoken, str(report)) #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: media = x['row'][0] ad_revenue = x['row'][1] list = (media, ad_revenue) #print(list) sql = """INSERT INTO """ + str( platform ) + """_site_addition VALUES ("%s", "%s")""" % (media, ad_revenue) 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" # 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_today" cursor.execute(sql) sql = "CREATE TABLE v3_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)" 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, "143113") #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] 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) #print(list) sql = """INSERT INTO v3_market_today VALUES ("%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) 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_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 dc_inventorysources" cursor.execute(sql) sql = "CREATE TABLE dc_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("0e30062d-6746-4a9b-882a-3f61185479c7", "9O7SnFq/yDbNK+4M2bkSqg") print(logintoken) result = aol_api.run_existing_report(logintoken, "145230") #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 = '2' list = (date, inventory_source, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, platform) #print(list) sql = """INSERT INTO dc_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 Springserve data and writes to MySQL table""" db = "mysql_dp" api = "springs" page = 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() # Call to get logintoken logintoken = springs_api.get_logintoken(api) #print(logintoken) while True: jsoninfo = { "date_range": "Yesterday", "interval": "day", "timezone": "America/Los_Angeles", "dimensions": ["supply_tag_id"], #"start_date": begin, #"end_date": yesterday, #"sort": [{ "field": "ad_revenue", "order": "desc"}] "page": str(page) } result = springs_api.get_data(logintoken, jsoninfo) #print(result.text) info = json.loads(result.text) if len(info) == 0: break if len(info) >= 1: if db_updated == False: sql = "DROP TABLE IF EXISTS springserve_inventorysources" cursor.execute(sql) sql = "CREATE TABLE springserve_inventorysources (date varchar(25), inventory_source varchar(255), \ ad_opportunities bigint, ad_impressions bigint, ad_revenue decimal(15, 5), platform int)" cursor.execute(sql) db_updated = True print( str(todaytime) + " Running springserve_inventorysources. Page # " + str(page) + " Count " + str(len(info))) # use default to populate null data default = 'Not Applicable' for x in info: date1 = x['date'] date = date1[:10] inventory_source = x['supply_tag_name'] ad_opportunities = x['total_requests'] ad_impressions = x['total_impressions'] ad_revenue = x['revenue'] platform = 7 list = (date, inventory_source, ad_opportunities, ad_impressions, ad_revenue, platform) #print(list) sql = """INSERT INTO springserve_inventorysources VALUES ("%s", "%s", "%s", "%s", "%s", "%s")""" % \ (date, inventory_source, ad_opportunities, ad_impressions, ad_revenue, platform) cursor.execute(sql) cursor.execute('commit') page += 1 else: print('Connection failed.') except Error as error: print(error) finally: conn.close()
def connect(): #"""Gets Springserve data and writes to MySQL table""" db = "mysql_sl" api = "springs" page = 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() # call to get logintoken logintoken = springs_api.get_logintoken(api) #print(logintoken) while True: jsoninfo = { "date_range": "Today", "interval": "hour", "timezone": "America/Los_Angeles", "dimensions": ["supply_tag_id", "demand_partner_id"], #"start_date": Today, #"endDate": Today, #"sort": [{ "field": "ad_revenue", "order": "desc"}] "page": str(page) } result = springs_api.get_data(logintoken, jsoninfo) #print(result.text) info = json.loads(result.text) if len(info) == 0: break if len(info) >= 1: if db_updated == False: sql = "DROP TABLE IF EXISTS springserve_market_today" cursor.execute(sql) sql = "CREATE TABLE springserve_market_today (date varchar(25), hour varchar(2), \ demand_partner_name varchar(255), source_id varchar(10), supply_source varchar(255), \ total_requests bigint, ad_opportunities bigint, ad_impressions bigint, \ clicks bigint, revenue decimal(15, 5))" cursor.execute(sql) db_updated = True print( str(todaytime) + " Running springserve_market_today. Page # " + str(page) + " Count " + str(len(info))) # use default to populate null data default = '0' for x in info: date1 = x['date'] date = date1[:10] time1 = x['date'] time2 = time1[11:-8].replace("00", "*0").lstrip("0") hour = time2.replace("*0", "0") demand_partner1 = x['demand_partner_name'] demand_partner_name = demand_partner1[:-4] source_id = x['supply_tag_id'] supply_source = x['supply_tag_name'] total_requests = x['demand_requests'] ad_opportunities = x['demand_requests'] ad_impressions = x['impressions'] clicks = x['clicks'] revenue = x['revenue'] list = (date, hour, demand_partner_name, source_id, supply_source, total_requests, \ ad_opportunities, ad_impressions, clicks, revenue) #print(list) sql = """INSERT INTO springserve_market_today VALUES ("%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s", "%s")""" % (date, hour, demand_partner_name, source_id, \ supply_source, total_requests, ad_opportunities, ad_impressions, clicks, revenue) cursor.execute(sql) cursor.execute('commit') page += 1 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" # 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_yesterday" cursor.execute(sql) sql = "CREATE TABLE adtrans_market_yesterday (date varchar(25), 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), 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, "169877") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: date = x['row'][0] hour = x['row'][1] buyer_organization = x['row'][2] inventory_source = 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] adtrans_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 = '6' list = (date, hour, 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_yesterday VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, \ "%s"*.67, "%s"*.24, "%s", "%s", "%s", "%s")""" % (date, hour, 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('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" api = "aol" # 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_yesterday_media" cursor.execute(sql) sql = "CREATE TABLE v3_core_yesterday_media (date varchar(25), inventory_source varchar(255), media varchar(255) \ CHARACTER SET 'utf8',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 # Make Calls And Write To DB: result = aol_api.run_existing_report(logintoken, "143992") #print(result) info = json.loads(result) #print(info) # Make Calls And Write To DB: for x in json.loads(result)['data']: date = x['row'][0] inventory_source = x['row'][1] media = x['row'][2].replace('"', " ") 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] clicks = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] list = (date, 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_yesterday_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s")""" % (date, 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 Springserve data and writes to MySQL table""" db = "mysql_dl" api = "springs" page = 1 # 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() # Create Table: sql = "DROP TABLE IF EXISTS springserve_InventorySources_v2" cursor.execute(sql) sql = "CREATE TABLE springserve_InventorySources_v2(rownum INT NOT NULL AUTO_INCREMENT PRIMARY KEY, date varchar(25), \ inventory_source varchar(255), media varchar(255), ad_opportunities bigint, \ ad_attempts bigint, ad_impressions bigint, ad_revenue decimal(15,5), clicks int)" cursor.execute(sql) # call to get logintoken logintoken = springs_api.get_logintoken(api) print(logintoken) while page < 20: jsoninfo = { "date_range": "Yesterday", "interval": "day", "timezone": "America/Los_Angeles", "dimensions": ["supply_tag_id", "declared_domain"], #"reportFormat": "JSON", #"startDate": yesterday, #"endDate": yesterday, #"sort": [{ "field": "ad_revenue", "order": "desc"}] "page": str(page) } print(str(page)) result = springs_api.get_data(logintoken, jsoninfo) #print(result.text) info = json.loads(result.text) #print(info) # Use default ot populate null data default = 'Not Available' for x in info: rownum = '' date1 = x['date'] date = date1[:10] inventory_source = x['supply_tag_name'].replace( '"', "").replace("'", "") media = x.get('declared_domain', default).replace("'", "").replace('"', "") ad_opportunities = x['total_requests'] ad_attempts = x['opportunities'] ad_impressions = x['total_impressions'] ad_revenue = x['revenue'] clicks = x['clicks'] list = (rownum, date, inventory_source, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, clicks) print(list) sql = """INSERT INTO springserve_InventorySources_v2 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % \ (rownum, date, inventory_source, media, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, clicks) cursor.execute(sql) cursor.execute('commit') page += 1 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_media" cursor.execute(sql) sql = "CREATE TABLE tm_core_yesterday_media (date varchar(25), 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), tm_revenue decimal(15, 5), \ clicks bigint, 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, "169835") #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] media = x['row'][3] 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_cost = x['row'][9] epiphany_gross_revenue = x['row'][9] tm_revenue = x['row'][9] clicks = x['row'][10] iab_viewability_measurable_ad_impressions = x['row'][11] iab_viewable_ad_impressions = x['row'][12] platform = '5' list = (date, hour, 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_yesterday_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s"*.20, "%s"*.64, "%s"*.24, "%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, 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 Springserve data and writes to MySQL table""" db = "mysql_dp" api = "springs" # Connect to DB: db_config = read_db_config(db) try: print('Connecting to databsae...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() # Create Table: sql = "DROP TABLE IF EXISTS springserve_inventoryreport" cursor.execute(sql) sql = "CREATE TABLE springserve_inventoryreport (date varchar(25), sourceID varchar(10), supply_source varchar(255), \ country varchar(255), ad_opportunities bigint, ad_impressions bigint, revenue decimal(15, 5), platform int)" cursor.execute(sql) # call to get logintoken logintoken = springs_api.get_logintoken(api) print(logintoken) for page in range(1, 300): jsoninfo = { "date_range": "Yesterday", "interval": "day", "timezone": "America/Los_Angeles", "dimensions": ["supply_tag_id", "country"], #"reportFormat": "JSON", #"start_date": "2017-12-01", #"end_date": "2017-12-11", #"sort": [{ "field": "ad_revenue", "order": "desc"}] "page": str(page) } result = springs_api.get_data(logintoken, jsoninfo) #print(result.text) info = json.loads(result.text) #print(info) # use default to populate null data default = 'Not Applicable' for x in info: date1 = x['date'] date = date1[:10] source_id = x['supply_tag_id'] supply_source = x['supply_tag_name'] country = x.get('country_code', default) ad_opportunities = x['total_requests'] ad_impressions = x['total_impressions'] revenue = x['revenue'] platform = 7 list = (date, source_id, supply_source, country, ad_opportunities, ad_impressions, revenue, platform) #print(list) sql = """INSERT INTO springserve_inventoryreport VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % \ (date, source_id, supply_source, country, ad_opportunities, ad_impressions, revenue, 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(): # """Get Springserve data and write to MySQL table""" db = "mysql_sl" api = "springs" # 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() # Create Table: sql = "DROP TABLE IF EXISTS springserve_core_today" cursor.execute(sql) sql = "CREATE TABLE springserve_core_today (date varchar(25), hour int(2), source_id varchar(10), supply_source varchar(255), \ total_requests bigint, usable_requests bigint, opportunities bigint, ad_impressions bigint, revenue decimal(15, 5), clicks bigint)" cursor.execute(sql) # call to get logintoken logintoken = springs_api.get_logintoken(api) print(logintoken) for page in range(1, 10): jsoninfo = { "date_range": "Today", "interval": "hour", "timezone": "America/Los_Angeles", "dimensions": ["supply_tag_id"], #"start_date": Today, #"endDate": Today, #"sort": [{ "field": "ad_revenue", "order": "desc"}] "page": str(page) } result = springs_api.get_data(logintoken, jsoninfo) #print(result.text) info = json.loads(result.text) # print(info) # use default to populate null data default = '0' for x in info: date1 = x['date'] date = date1[:10] time1 = x['date'] time2 = time1[11:-8].replace("00", "*0").lstrip("0") hour = time2.replace("*0", "0") source_id = x['supply_tag_id'] supply_source = x['supply_tag_name'] total_requests = x['total_requests'] usable_requests = x['usable_requests'] opportunities = x['opportunities'] ad_impressions = x['total_impressions'] revenue = x['revenue'] clicks = x['clicks'] list = (date, hour, source_id, supply_source, total_requests, usable_requests, opportunities, ad_impressions, revenue, clicks) #print(list) sql = """INSERT INTO springserve_core_today VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % \ (date, hour, source_id, supply_source, total_requests, usable_requests, opportunities, ad_impressions, revenue, clicks) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
from mysql.connector import MySQLConnection, Error from python_dbconfig import read_db_config from datetime import datetime, timedelta from opcua import Client from opcua import ua from Config import * if __name__ == '__main__': query = ("INSERT INTO plc_time2(time) " "VALUES(%s)") client = Client("opc.tcp://*****:*****@" + str(HMI_IP) + ":48010/") try: db_config = read_db_config() conn = MySQLConnection(**db_config) client.connect() cursor = conn.cursor() nodes = [client.get_node("ns=2;s=Tags." + tag) for tag in Node_Tags] while True: dataRow = [node.get_value() for node in nodes] time = datetime.fromtimestamp(dataRow[0]) cursor.execute(query, time) print(time) conn.commit() time.sleep(1)
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_core_last60" cursor.execute(sql) sql = "CREATE TABLE dc_core_last60 (date varchar(50), 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), dc_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, "150659") #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] 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] dc_revenue = x['row'][7] total_clicks = x['row'][8] iab_viewability_measurable_ad_impressions = "0" iab_viewable_ad_impressions = "0" platform = '2' list = (date, inventory_source, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, dc_revenue, total_clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) # print(list) sql = """INSERT INTO dc_core_last60 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, "%s"*.56, \ "%s"*.24, "%s", "%s", "%s", "%s")""" % (date, inventory_source, ad_opportunities, market_opportunities, \ ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, dc_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_dp" report_type = "market_private" p_name = sys.argv[1] p_id = platforms[p_name]["id"] m_id = platforms[p_name]["market_id"]["private"] 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('Connected 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 + "_market_private" cursor.execute(sql) sql = "CREATE TABLE " + p_name + "_market_private (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), media_spend decimal(15, 5), \ ecpm decimal(6, 4), completed_views int, clicks int, platform int, market_id int)" cursor.execute(sql) db_updated = True print( str(todaysdate) + " Running " + p_name + "_market_private id_" + str(p_id) + " with report # " + str(report)) 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] media_spend = x['row'][8] ecpm = x['row'][9] completed_views = x['row'][10] clicks = x['row'][11].replace(" ", "0") platform = str(p_id) market_id = str(m_id) list = (date, inventory_source, geo_country, buyer_organization, ad_opportunities, ad_attempts, \ ad_impressions, ad_revenue, media_spend, ecpm, completed_views, clicks, platform, market_id) #print(list) sql = """INSERT INTO """ + p_name + """_market_private VALUES ("%s", "%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, media_spend, \ ecpm, completed_views, clicks, platform, market_id) 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" report_type = "market_last60" 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 + "_market_last60" cursor.execute(sql) sql = "CREATE TABLE " + p_name + "_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), " + p_name + "_revenue decimal(15, 5), total_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 + "_market_last60 id_ " + str(p_id) + " report # " + str(report)) 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_cos = x['row'][7] epiphany_gross_rev = x['row'][7] platform_rev = x['row'][7] total_clicks = x['row'][8] iab_viewability_measurable_ad_impressions = x['row'][9] iab_viewable_ad_impressions = x['row'][10] platform = str(p_id) list = (date, buyer_organization, inventory_source, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, aol_cos, epiphany_gross_rev, platform_rev, total_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 + """_market_last60 VALUES ("%s", "%s", "%s", "%s", "%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, aol_cost, \ epiphany_gross_revenue, platform_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()
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dp" report_type = "inventoryreport" p_name = sys.argv[1] p_id = platforms[p_name]["id"] 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 + "_inventoryreport" cursor.execute(sql) sql = "CREATE TABLE " + p_name + "_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) db_updated = True print( str(todaysdate) + " Running " + p_name + "_inventoryreport id_" + str(p_id) + " with report # " + str(report)) 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 = str(p_id) list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, \ ecpm, platform) #print(list) sql = """INSERT INTO """ + p_name + """_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()
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_eom" api = "aol" # 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_market_private_EOM" cursor.execute(sql) sql = "CREATE TABLE dna_market_private_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), media_spend decimal(15, 5), ecpm decimal(6, 4), 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, "193889") #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] media_spend = x['row'][8] ecpm = x['row'][9] completed_views = x['row'][10] clicks = x['row'][11].replace(" ", "0") platform = '1' list = (date, inventory_source, geo_country, buyer_organization, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, media_spend, ecpm, completed_views, clicks, platform) #print(list) sql = """INSERT INTO dna_market_private_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, media_spend, ecpm, 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 = "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, "190143") #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_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_core_yesterday" cursor.execute(sql) sql = "CREATE TABLE v3_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), \ 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, "143989") #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] total_clicks = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] list = (date, hour, inventory_source, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, total_clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions) #print(list) sql = """INSERT INTO v3_core_yesterday VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s")""" % (date, hour, inventory_source, ad_opportunities, 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('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.')