In [31]:
import pymysql
import os
import json
import datetime as dt
from datetime import datetime
from collections import defaultdict
# regions_available = {"QA-Test": "us1"}
regions_available = {"us-east-1": "us1", "us-west-2": "us2", "ap-northeast-1": "ap1", "eu-west-1": "eu1"}
# regions_available = {"us-east-1": "us1"}
# regions_available = {"us-west-2": "us2"}
def traffic_events_summary(start_str="2016-01-01 08:00:00.0", end_str="2017-01-01 08:00:00.0"):
# start_str = "2016-08-02 08:33:27.822648"
if not end_str or len(end_str) < 2:
end_str = "2017-01-01 08:00:00.00"
db_config = {}
# print("[DEBUG] start - end 1", start_str, end_str)
events_summary = {"regions": [], "event_count": 0, "event_count_subttls": defaultdict(int)}
with open(os.path.expanduser('~') + "/.aws/rds.conf") as rds_configfile:
db_config = json.loads(rds_configfile.read())
for region in regions_available.keys():
# print("[DEBUG] Checking {} rds".format(region))
connection = None
try:
connection = pymysql.connect(**db_config[region], cursorclass=pymysql.cursors.DictCursor)
except pymysql.err.OperationalError:
print("[WARN] traffic_events_summary failed to connect to {} rds. Is it down?".format(region))
continue
cursor = connection.cursor()
cursor.execute("show databases;")
dbs = cursor.fetchall()
for db in dbs:
print("[DEBUG] Looping through db {}".format(db))
if db["Database"] not in ['cake', 'mysql', 'performance_schema', 'information_schema']:
try:
format = "%Y-%m-%d %H:%M:%S.%f"
start = dt.datetime.strptime(start_str, format)
start.replace(second=0, microsecond=0)
format = "%Y-%m-%d %H:%M:%S.%f"
end = dt.datetime.strptime(end_str, format)
end.replace(second=0, microsecond=0)
# print("[DEBUG] start, end 2", start, end)
cursor.execute("""
SELECT
SUM(clicks_total) as clicks_count,
SUM(lite_clicks_total) as lite_clicks_count,
SUM(impressions_total) as impressions_count
FROM {}.traffic_events_v6
WHERE request_date > %s
AND request_date < %s;
""".format(db['Database']), (start, end))
results = cursor.fetchall()
for row in results:
for key, val in row.items():
fixed_val = int(val) if val is not None else 0
if fixed_val:
events_summary['regions'].append(region)
events_summary['event_count_subttls'][key] += fixed_val
events_summary['event_count'] += fixed_val
except:
print("[WARN] error querying {} db {}".format(region, db['Database']))
events_summary['regions'] = list(set(events_summary['regions'].copy())) # get unique regions only, as a list
events_summary['event_count_subttls'] = dict(events_summary['event_count_subttls'].copy()) # back to dict fmt
# print("[DEBUG] {} TOTALS:\t\t{}".format(datetime.now(), dict(events_summary)))
return events_summary
if __name__ == "__main__":
traffic_events_summary()