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_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 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 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 = "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_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_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_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_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 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_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()
# Create Table:
sql = "DROP TABLE IF EXISTS tm_core_today"
cursor.execute(sql)
sql = "CREATE TABLE tm_core_today (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("9fadb507-01e9-4a2a-b9c5-c9d65d93396e", "y0QxY3DC7vbczeW7nBOsZg")
print(logintoken)
result = aol_api.run_existing_report(logintoken, "169837")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
hour = 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]
tm_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 = '5'
list = (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_today VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, "%s"*.64, \
"%s"*.24, "%s", "%s", "%s", "%s")""" % (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_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_inventoryreport_EOM"
cursor.execute(sql)
sql = "CREATE TABLE dna_inventoryreport_EOM (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(api)
print(logintoken)
result = aol_api.run_existing_report(logintoken, "193888")
#print(result)
info = json.loads(result)
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_EOM 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_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_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 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_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()
