def update_sql(adset_id, adset_name, status, account_id, bid_strategy, effective_status, start_time, configured_status,
lifetime_budget, budget_remaining, daily_budget, created_time, update_time, date, campaign_id,
campaign_name, ads):
sql = """
REPLACE INTO
adset(adset_id, adset_name, status, account_id, bid_strategy, effective_status, start_time, configured_status,
lifetime_budget, budget_remaining, daily_budget, created_time, update_time, date, campaign_id,
campaign_name, ads)
VALUES('{adset_id}','{adset_name}','{status}','{account_id}','{bid_strategy}','{effective_status}','{start_time}',
'{configured_status}','{lifetime_budget}','{budget_remaining}','{daily_budget}','{created_time}',
'{update_time}','{date}','{campaign_id}','{campaign_name}','{ads}')
""".format(adset_id=adset_id, adset_name=adset_name, status=status, account_id=account_id, bid_strategy=bid_strategy,
effective_status=effective_status, start_time=start_time, configured_status=configured_status,
lifetime_budget=lifetime_budget, budget_remaining=budget_remaining, daily_budget=daily_budget,
created_time=created_time, update_time=update_time, date=date, campaign_id=campaign_id, campaign_name=campaign_name,
ads=ads)
print sql
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
db_mysql.commit()
except:
# 发生错误时回滚
traceback.print_exc(file=sys.stdout)
db_mysql.rollback()
def update_sql(date, platform, campaign_id, campaign_name, days,
retained_users, paying_users, revenue, revenue_events,
update_time):
sql = """
REPLACE INTO
t_adjust_campaign(date,platform,campaign_id,campaign_name,days,retained_users,paying_users,revenue,revenue_events,update_time)
VALUES('{data}','{platform}','{campaign_id}','{campaign_name}',{days},{retained_users},{paying_users},{revenue},{revenue_events},'{update_time}')
""".format(data=date,
platform=platform,
campaign_id=campaign_id,
campaign_name=campaign_name,
days=days,
retained_users=retained_users,
paying_users=paying_users,
revenue=revenue,
revenue_events=revenue_events,
update_time=update_time)
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
db_mysql.commit()
except:
# 发生错误时回滚
db_mysql.rollback()
sql = sql[:-1]
sql += ')\nVALUES('
for k, v in campaign_dic.items():
sql += ("'%s'" % v + ',')
sql = sql[:-1]
sql += ')'
return sql
if __name__ == '__main__':
update_time = time.time()
'''
one = db_mongo['google_campaign'].find_one()
sql = generate_replace_sql(one, update_time)
with open(path.join(current_dir, 'sqls', 'google_campaign_replace_into.sql'), 'w') as fw:
fw.write(sql)
'''
for item in db_mongo['google_campaign'].find():
sql = generate_replace_sql(item, update_time)
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
db_mysql.commit()
except:
# 发生错误时回滚
db_mysql.rollback()