def save_to_db(conn, date, platform, revenue):
logging.info("=" * 40)
logging.info([date, platform, revenue])
now_string = toolkit.mysql_now()
update_sql = " UPDATE revenue SET \t"
update_sql += " `revenue` = '%s' ,\t" % revenue
update_sql += " `updated_at` = '%s'\t" % now_string
update_sql += ' WHERE\t'
update_sql += " `platform` = '%s' AND\t" % platform
update_sql += " `date` = '%s' ;" % date
stat = toolkit.mysql_execute(conn, update_sql)
if stat > 0:
logging.info("saveToDb:SUCCESS:UPDATE_SQL:[%s][%s]" %
(update_sql, stat))
return True
else:
insert_sql = "INSERT INTO revenue (`date`, `platform`, `revenue`)" \
" VALUES('%s','%s','%s');" \
% (date, platform, revenue)
stat = toolkit.mysql_execute(conn, insert_sql)
if stat > 0:
logging.info("saveToDb:SUCCESS:INSERT_SQL:[%s][%s]" %
(insert_sql, stat))
return True
else:
logging.error("saveToDb:FAIL:INSERT_SQL:[%s][%s]" %
(insert_sql, stat))
return False
def save_to_db(conn, date, appname, country, placement, field_name, value):
logging.info("=" * 40)
logging.info((date, appname, country, placement, field_name, value))
now_string = toolkit.mysql_now()
UPDATE_SQL = "UPDATE ad_summary SET %s = %s ,updated_at = '%s' " \
"WHERE date = '%s' AND platform = '%s' AND app = '%s'" \
" AND country = '%s' ;" % \
(field_name, value, now_string, date, 'facebook', appname, country)
# logging.info("saveToDb:INFO:UPDATE_SQL:[%s]" % (UPDATE_SQL))
stat = toolkit.mysql_execute(conn, UPDATE_SQL)
if stat > 0:
logging.info("saveToDb:SUCCESS:UPDATE_SQL:[%s][%s]" %
(UPDATE_SQL, stat))
return True
elif field_name == 'revenue':
INSERT_SQL = "INSERT INTO ad_summary (date,app,platform,country,%s)" \
" VALUES('%s','%s','facebook','%s',%s);" \
% (field_name, date, appname, country, value)
stat = toolkit.mysql_execute(conn, INSERT_SQL)
if stat > 0:
logging.info("saveToDb:SUCCESS:INSERT_SQL:[%s][%s]" %
(INSERT_SQL, stat))
return True
else:
logging.error("saveToDb:FAIL:INSERT_SQL:[%s][%s]" %
(INSERT_SQL, stat))
return False
else:
logging.info("saveToDb:FAIL:UPDATE_SQL:[%s][%s]" % (UPDATE_SQL, stat))
return False
def save_to_db(conn, mydata):
logging.info("=" * 40)
logging.info(mydata)
now_string = toolkit.mysql_now()
platform = 'googleplay'
update_sql = "UPDATE ad_summary SET\t"
update_sql += "`revenue` = '%s' ,\t" % mydata['revenue']
update_sql += "`updated_at` = '%s' \t" % now_string
update_sql += "WHERE\t"
update_sql += "`date` = '%s' AND \t" % mydata['date']
update_sql += "`app` = '%s' AND \t" % mydata['app']
update_sql += "`country` = '%s' AND\t" % mydata['country']
update_sql += "`platform` = '%s' ;" % platform
stat = toolkit.mysql_execute(conn, update_sql)
if stat > 0:
logging.info("saveToDb:SUCCESS:UPDATE_SQL:[%s][%s]" %
(update_sql, stat))
return True
else:
logging.info("saveToDb:FAIL:UPDATE_SQL:[%s]" % update_sql)
insert_sql = "INSERT INTO ad_summary (`platform`,`country`,`app`,`date`,`revenue`)" \
" VALUES('%s','%s','%s','%s','%s');" \
% (platform, mydata['country'], mydata['app'], mydata['date'], mydata['revenue'])
stat = toolkit.mysql_execute(conn, insert_sql)
if stat > 0:
logging.info("saveToDb:SUCCESS:INSERT_SQL:[%s][%s]" %
(insert_sql, stat))
return True
else:
logging.error("saveToDb:FAIL:INSERT_SQL:[%s][%s]" %
(insert_sql, stat))
return False
def get_update_sql(my_data):
now_string = toolkit.mysql_now()
update_sql = 'UPDATE ad_summary SET \t'
for m in metrics_list:
update_sql += " `%s` = '%s' ,\t" % (map_to_db[m], my_data[m])
update_sql += " `updated_at` = '%s' \t" % now_string
update_sql += " WHERE\t"
for d in dimensions_list:
update_sql += " `%s` = '%s' AND \t" % (map_to_db[d], my_data[d])
update_sql += "`platform` = 'admob' ;"
return update_sql
def save_to_db(conn, date, request, filled, impression, click, filled_rate,
ctr, ecpm, revenue):
logging.info("=" * 40)
logging.info([
date, request, filled, impression, click, filled_rate, ctr, ecpm,
revenue
])
now_string = toolkit.mysql_now()
UPDATE_SQL = "UPDATE ad_summary " \
"SET request = %s , " \
"filled = %s ," \
"impression = %s ," \
"click = %s ," \
"filled_rate = %s ," \
"ctr = %s ," \
"ecpm = %s ," \
"revenue = %s ," \
"updated_at = '%s' " \
"WHERE date = '%s' AND platform = '%s' AND app = '%s' AND country = '%s';" % (
request, filled, impression, click,
filled_rate, ctr, ecpm, revenue, now_string, date, 'baidu', '99', '99')
stat = toolkit.mysql_execute(conn, UPDATE_SQL)
if stat > 0:
logging.info("saveToDb:SUCCESS:UPDATE_SQL:[%s][%s]" %
(UPDATE_SQL, stat))
return True
else:
logging.info("saveToDb:error:UPDATE_SQL:[%s]" % (UPDATE_SQL))
INSERT_SQL = "INSERT INTO ad_summary (platform,country,app,date," \
"request,filled,impression,click,filled_rate,ctr,ecpm,revenue,updated_at)" \
" VALUES('baidu','99','99','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
% (date, request, filled, impression, click, filled_rate, ctr, ecpm, revenue, now_string)
stat = toolkit.mysql_execute(conn, INSERT_SQL)
if stat > 0:
logging.info("saveToDb:SUCCESS:INSERT_SQL:[%s][%s]" %
(INSERT_SQL, stat))
return True
else:
logging.error("saveToDb:FAIL:INSERT_SQL:[%s][%s]" %
(INSERT_SQL, stat))
return False
def get_update_sql(my_data):
now_string = toolkit.mysql_now()
update_sql = 'UPDATE ad_admob SET \t'
for m in metrics_list:
if m == "EARNINGS":
update_sql += " `revenue` = %s ,\t" % my_data[m]
else:
update_sql += " `%s` = '%s' ,\t" % (m, my_data[m])
update_sql += " `updated_at` = '%s' \t" % now_string
update_sql += " WHERE\t"
for d in dimensions_list:
if d == "DATE":
update_sql += "`date` = '%s' AND \t" % my_data[d]
elif d == "COUNTRY_CODE":
update_sql += "`country` = '%s' AND \t" % my_data[d]
elif d == "APP_ID":
update_sql += "`app` = '%s' AND \t" % my_data[d]
update_sql += "`APP_ID` = '%s' AND \t" % my_data[d]
else:
update_sql += " `%s` = '%s' AND \t" % (d, my_data[d])
update_sql += "`platform` = 'admob' ;"
return update_sql