#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Fri Jun 5 17:00:45 2020
@author: chencong
"""
import json
import random
import requests
import time
from datetime import datetime
import pymysql
import token_list as tl
from concurrent.futures import ThreadPoolExecutor
max_workers=5
from util import date_util
def adcreatives_get(access_token,account_id,fields) : #获取创意
interface = 'adcreatives/get'
url = 'https://api.e.qq.com/v1.1/' + interface
page =1
list1 = []
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"page": page,
"page_size": 100,
"is_deleted": False
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters).json()
if 'data' in r.keys():
list1 = list1+r['data']['list']
total_page=r['data']['page_info']['total_page']
if total_page>1:
for page in range(2,total_page+1):
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"page": page,
"page_size": 100,
"is_deleted": False
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters).json()
if 'data' in r.keys():
list1 = list1+r['data']['list']
return list1
#print(adcreatives_get('3bbbae77bed9fcde94cc0f1742a18c6e',11436446,('campaign_id','adcreative_id','adcreative_name','adcreative_elements','promoted_object_type','page_type','page_spec','link_page_spec','universal_link_url','promoted_object_id')))
def ads_get(access_token,account_id,fields) : #获取广告
interface = 'ads/get'
url = 'https://api.e.qq.com/v1.1/' + interface
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"page": 1,
"page_size": 10,
"is_deleted": False
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters)
return r.json()
#print(ads_get('2a674bef201314d338be30420369671f',14985162,('ad_id','ad_name','adcreative_id','adcreative')))
def wechat_pages_get(access_token,account_id,page_id,fields) : #获取微信原生页
interface = 'wechat_pages/get'
url = 'https://api.e.qq.com/v1.1/' + interface
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"filtering":
[
{
"field": "page_id",
"operator": "EQUALS",
"values":
[
page_id
]
}
],
"page": 1,
"page_size": 10
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters)
return r.json()
#print(wechat_pages_get('2a674bef201314d338be30420369671f',14985162,1900495593,('page_id','page_name','created_time','last_modified_time','page_template_id','preview_url','page_type','source_type')))
def adgroups_get(access_token,account_id,fields) : #获取广告组
interface = 'adgroups/get'
url = 'https://api.e.qq.com/v1.1/' + interface
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"page": 4,
"page_size": 100,
"is_deleted": False
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters)
return r.json()
#print(adgroups_get('2a674bef201314d338be30420369671f',14985162,('campaign_id','adgroup_id','adgroup_name','optimization_goal','billing_event','bid_amount','daily_budget','targeting','begin_date','end_date','time_series','bid_strategy','cold_start_audience','auto_audience','expand_enabled','expand_targeting','deep_conversion_spec','deep_optimization_action_type','conversion_id','deep_conversion_behavior_bid','deep_conversion_worth_rate','system_status')))
def images_get(access_token,account_id,fields) : #获取图片信息
import json
import random
import requests
import time
interface = 'images/get'
url = 'https://api.e.qq.com/v1.1/' + interface
page = 1
list1 = []
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"page": page,
"page_size": 100
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters).json()
if 'data' in r.keys():
list1 = list1+r['data']['list']
total_page=r['data']['page_info']['total_page']
if total_page>1:
for page in range(2,total_page+1):
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"page": page,
"page_size": 100
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters).json()
if 'data' in r.keys():
list1 = list1+r['data']['list']
return list1
#print(images_get('2a674bef201314d338be30420369671f',14985162,('image_id','preview_url')))
def campaigns_get(access_token,account_id,fields) : #获取推广计划
import json
import random
import requests
import time
interface = 'campaigns/get'
url = 'https://api.e.qq.com/v1.1/' + interface
page = 1
list1 = []
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"page": page,
"page_size": 100,
"is_deleted": False
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters).json()
if 'data' in r.keys():
list1 = list1+r['data']['list']
total_page=r['data']['page_info']['total_page']
if total_page>1:
for page in range(2,total_page+1):
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"page": page,
"page_size": 100,
"is_deleted": False
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters).json()
if 'data' in r.keys():
list1 = list1+r['data']['list']
return list1
#aa=tl.token_list_vx[-2]
#print(campaigns_get(aa[2],aa[0],('campaign_id','campaign_name','configured_status','campaign_type','promoted_object_type','daily_budget','budget_reach_date','created_time','last_modified_time','speed_mode','is_deleted')))
def daily_reports_get(access_token,account_id,level,start_date,end_date,fields) : #获取wx投放计划日报数据
interface = 'daily_reports/get'
url = 'https://api.e.qq.com/v1.1/' + interface
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"level": level,
"date_range":
{
"start_date": start_date,
"end_date": end_date
},
"page": 1,
"page_size": 1000,
"fields":
[
]
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters)
return r.json()
def daily_qq_reports_get(access_token,account_id,compaign_id,level,start_date,end_date,fields) : #获取gdt投放计划日报数据
interface = 'daily_reports/get'
url = 'https://api.e.qq.com/v1.1/' + interface
common_parameters = {
'access_token': access_token,
'timestamp': int(time.time()),
'nonce': str(time.time()) + str(random.randint(0, 999999)),
'fields':fields
}
parameters = {
"account_id": account_id,
"filtering":
[
{
"field": "campaign_id",
"operator": "EQUALS",
"values":
[
compaign_id
]
}
],
"level": level,
"date_range":
{
"start_date": start_date,
"end_date": end_date
},
"page": 1,
"page_size": 1000,
"fields":
[
]
}
parameters.update(common_parameters)
for k in parameters:
if type(parameters[k]) is not str:
parameters[k] = json.dumps(parameters[k])
r = requests.get(url, params = parameters)
return r.json()
def mysql_insert_daily_vx_campaign(data):
db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com','superc','Cc719199895','quchen_text')
cursor = db.cursor()
time1 = time.time()
sql = 'insert ignore into daily_vx_campaign (account_id,date,campaign_id,view_count,cost,ctr,cpc,order_roi,thousand_display_price,valid_click_count,official_account_follow_count,conversions_count,official_account_follow_rate,conversions_rate,order_count,order_rate,order_unit_price,first_day_order_amount) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
try:
cursor.executemany(sql,data)
db.commit()
cost_time =round((time.time()-time1)/60,1)
print('insert_daily_vx_campaign access',len(data),'cost_minutes:',cost_time)
except:
db.rollback()
print('insert_daily_vx_campaign defeat')
def mysql_insert_daily_qq_campaign(data):
db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com','superc','Cc719199895','quchen_text')
cursor = db.cursor()
time1 = time.time()
sql = 'insert ignore into daily_qq_campaign (account_id,date,campaign_id,view_count,thousand_display_price,valid_click_count,ctr,cpc,cost,order_roi) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
try:
cursor.executemany(sql,data)
db.commit()
cost_time =round((time.time()-time1)/60,1)
print('insert_daily_qq_campaign access',len(data),'cost_minutes:',cost_time)
except:
db.rollback()
print('insert_daily_qq_campaign defeat')
def mysql_insert_campaign_vx(data,data2):
db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com','superc','Cc719199895','quchen_text')
cursor = db.cursor()
time1 = time.time()
sql = 'insert ignore into campaign_vx (campaign_id,campaign_name,configured_status,campaign_type,promoted_object_type,daily_budget,created_time,last_modified_time,account_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)'
sql2 = 'delete from campaign_vx where campaign_id =%s '
try:
cursor.executemany(sql2,data2)
db.commit()
print('delete campaign_vx access',len(data2))
except:
db.rollback()
print('delete campaign_vx defeat')
try:
cursor.executemany(sql,data)
db.commit()
cost_time =round((time.time()-time1)/60,1)
print('insert_campaign_vx access',len(data),'cost_minutes:',cost_time)
except:
db.rollback()
print('insert_campaign_vx defeat')
def mysql_insert_adcreative(data):
db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com','superc','Cc719199895','quchen_text')
cursor = db.cursor()
time1 = time.time()
sql = 'insert ignore into adcreative (campaign_id,adcreative_id,adcreative_name,image_id,title,promoted_object_type,page_type,page_id,link_page_id,promoted_object_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
try:
cursor.executemany(sql,data)
db.commit()
cost_time =round((time.time()-time1)/60,1)
print('insert_adcreative access',len(data),'cost_minutes:',cost_time)
except:
db.rollback()
print('insert_adcreative defeat')
def mysql_insert_image(data):
db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com','superc','Cc719199895','quchen_text')
cursor = db.cursor()
time1 = time.time()
sql = 'insert ignore into image (image_id,preview_url,account_id) values (%s,%s,%s)'
try:
cursor.executemany(sql,data)
db.commit()
cost_time =round((time.time()-time1)/60,1)
print('insert image access',len(data),'cost_minutes:',cost_time)
except:
db.rollback()
print('insert image defeat')
def v_data(x,st,et,q,r,p):
account_id = x[0]
access_token = x[2]
start_date = time.strftime("%Y-%m-%d", time.localtime(st))
end_date = time.strftime("%Y-%m-%d", time.localtime(et))
l = campaigns_get(access_token, account_id, (
'campaign_id', 'campaign_name', 'configured_status', 'campaign_type', 'promoted_object_type', 'daily_budget',
'budget_reach_date', 'created_time', 'last_modified_time', 'speed_mode', 'is_deleted'))
if len(l) > 0:
for ll in l:
ll['account_id'] = account_id
if ll['created_time'] > st or ll['last_modified_time'] > st:
q.append(ll['campaign_id'])
lt = tuple(ll.values())
p.append(lt)
data_list = daily_reports_get(access_token, account_id, 'REPORT_LEVEL_CAMPAIGN_WECHAT', start_date, end_date, (
'account_id', 'date', 'campaign_id', 'view_count', 'cost', 'ctr', 'cpc', 'order_roi', 'thousand_display_price',
'valid_click_count', 'official_account_follow_count', 'conversions_count', 'official_account_follow_rate',
'conversions_rate', 'order_count', 'order_rate', 'order_unit_price', 'first_day_order_amount'))
if 'data' in data_list.keys():
for y in data_list['data']['list']:
y['account_id'] = account_id
y = tuple(y.values())
r.append(y)
def get_daily_vx_campaign(st,et): #获取投放计划、日报数据
token_list_v = tl.token_list_vx
r = []
p = []
q=[]
executor = ThreadPoolExecutor(max_workers=max_workers)
for x in token_list_v:
executor.submit(v_data,x,st,et,q,r,p)
executor.shutdown()
mysql_insert_daily_vx_campaign(r)
mysql_insert_campaign_vx(p,q)
def get_daily_qq_campaign(st,et):
token_list_q = tl.token_list_qq
r=()
for x in token_list_q:
account_id = x[0]
access_token = x[2]
start_date = st
end_date = et
l = campaigns_get(access_token,account_id,('campaign_id','campaign_name','configured_status','campaign_type','promoted_object_type','daily_budget','budget_reach_date','created_time','last_modified_time','speed_mode','is_deleted'))
for ll in l:
campaign_id =ll['campaign_id']
data_list = daily_qq_reports_get(access_token,account_id,campaign_id,'REPORT_LEVEL_CAMPAIGN',start_date,end_date,('account_id','date','campaign_id','view_count','thousand_display_price','valid_click_count','ctr','cpc','cost','order_roi'))
if len(data_list['data']['list'])>0:
print(data_list)
# print(l)
if 'data' in data_list.keys():
for y in data_list['data']['list']:
y = tuple(y.values())
r=r+((y),)
def get_campaign_update_list():
db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com','superc','Cc719199895','quchen_text')
cursor = db.cursor()
sql = 'select distinct advertiser_vx.account_id,access_token from campaign_vx left join advertiser_vx on advertiser_vx.account_id = campaign_vx.account_id where created_time>=%s or last_modified_time>=%s'
data = (int((time.time()+8*3600)//86400*86400-8*3600-86400),int((time.time()+8*3600)//86400*86400-8*3600-86400))
try:
cursor.execute(sql,data)
db.commit()
x=cursor.fetchall()
# print('access get campaign update list',x)
except:
db.rollback()
# print('defeat get campaign update list')
a = []
if len(x)>0:
for t in x:
a.append(t[0])
sql2 = 'delete from adcreative where campaign_id=%s'
try:
cursor.executemany(sql2,a)
db.commit()
y=cursor.fetchall()
# print('access delete adcreative',y)
except:
db.rollback()
# print('defeat delete adcreative')
return x
def adcreative_vx_data(x,r):
account_id = x[0]
access_token = x[1]
l = adcreatives_get(access_token, account_id, (
'campaign_id', 'adcreative_id', 'adcreative_name', 'adcreative_elements', 'promoted_object_type', 'page_type',
'page_spec', 'link_page_spec', 'universal_link_url', 'promoted_object_id'))
if len(l) > 0:
for ll in l:
if 'image_list' in ll['adcreative_elements'].keys():
for image_id in ll['adcreative_elements']['image_list']:
a = {}
a['campaign_id'] = ll['campaign_id']
a['adcreative_id'] = ll['adcreative_id']
a['adcreative_name'] = ll['adcreative_name']
a['image_id'] = image_id
a['title'] = ll['adcreative_elements']['title']
a['promoted_object_type'] = ll['promoted_object_type']
a['page_type'] = ll['page_type']
if 'page_spec' in ll.keys():
if 'page_id' in ll['page_spec'].keys():
a['page_id'] = ll['page_spec']['page_id']
else:
a['page_id'] = None
else:
a['page_id'] = None
if 'link_page_spec' in ll.keys():
if 'page_id' in ll['link_page_spec'].keys():
a['link_page_id'] = ll['link_page_spec']['page_id']
else:
a['link_page_id'] = None
else:
a['link_page_id'] = None
a['promoted_object_id'] = ll['promoted_object_id']
y = tuple(a.values())
r = r + ((y),)
elif 'image' in ll['adcreative_elements'].keys():
a = {}
a['campaign_id'] = ll['campaign_id']
a['adcreative_id'] = ll['adcreative_id']
a['adcreative_name'] = ll['adcreative_name']
a['image_id'] = ll['adcreative_elements']['image']
if 'title' in ll['adcreative_elements']:
a['title'] = ll['adcreative_elements']['title']
else:
a['title'] = ''
a['promoted_object_type'] = ll['promoted_object_type']
a['page_type'] = ll['page_type']
if 'page_spec' in ll.keys():
if 'page_id' in ll['page_spec'].keys():
a['page_id'] = ll['page_spec']['page_id']
else:
a['page_id'] = None
else:
a['page_id'] = None
if 'link_page_spec' in ll.keys():
if 'page_id' in ll['link_page_spec'].keys():
a['link_page_id'] = ll['link_page_spec']['page_id']
else:
a['link_page_id'] = None
else:
a['link_page_id'] = None
a['promoted_object_id'] = ll['promoted_object_id']
y = tuple(a.values())
r.append(y)
def get_adcreative_vx():
token_list_vx=get_campaign_update_list()
r = []
executor = ThreadPoolExecutor(max_workers=max_workers)
for x in token_list_vx:
executor.submit(adcreative_vx_data,x,r)
executor.shutdown()
mysql_insert_adcreative(r)
def get_image_imformation():
token_list_vx = tl.token_list_vx
r = ()
for x in token_list_vx:
account_id = x[0]
access_token = x[2]
l = images_get(access_token,account_id,('image_id','preview_url'))
if len(l)>0:
for ll in l:
ll['account_id']=account_id
y=tuple(ll.values())
r=r+((y),)
mysql_insert_image(r)
def start_all_job():
start_time = date_util.get_n_day(n=-1,is_timestamp=1)
end_time = date_util.get_n_day(n=-1,is_timestamp=1)
print("run[{0}] data".format(date_util.stamp_to_str(start_time)[:10]))
get_daily_vx_campaign(start_time,end_time)
print("get_daily_vx success----------")
get_adcreative_vx()
print("get_adcreative success--------------")
get_image_imformation()
if __name__ == '__main__':
print("============start at " + str(datetime.today()) + "===================")
start_all_job()