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()