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.')
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 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():
# """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 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 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 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 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", "country", "demand_partner_id"],
#"start_date": "2018-02-01",
#"end_date": "2018-02-12",
#"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_private"
cursor.execute(sql)
sql = "CREATE TABLE springserve_market_private (date varchar(25), source_id varchar(10), \
supply_source varchar(255), country varchar(100), demand_partner_name varchar(100), \
tag_requests bigint, ad_impressions bigint, revenue decimal(15, 5), clicks bigint, platform int)"
cursor.execute(sql)
db_updated = True
print(
str(todaytime) +
" Running springserve_market_private. 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]
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 = 7
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 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')
page += 1
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
