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