def do(target_date,patch_date_start,patch_date_end,sql):
add_offset_as_a_flag_for_trace='0'
# define data template to guide patch
sql=sql % (patch_date_end,)
db.query(sql)
result_view = db.store_result()
while 1:
row_view = result_view.fetch_row(how=2)
if not row_view:
break
row_view=row_view[0]
print str(row_view)
sql=(r'''
update raw_data
set `key`=concat(`key`,'_expired')
where
`oem_name`='%s'
and `category`='%s'
and `key`='%s'
and `sub_key`='%s'
and `date`='%s'
limit 1
''' % (row_view['raw_data.oem_name'],row_view['raw_data.category'],row_view['raw_data.key'],row_view['raw_data.sub_key'].replace("\'","\\\'"),target_date))
print sql
db.query(sql)
sql=(r'''
insert into raw_data (
`oem_name`
,`category`
,`key`
,`sub_key`
,`date`
,`value`
) select
'%s' as `oem_name`
,'%s' as `category`
,'%s' as `key`
,'%s' as `sub_key`
,'%s' as `date`
,ceil(1.0*sum(if(value is null,0,value))/count(*)) + %s as `value`
from raw_data
where
`oem_name`='%s'
and `category`='%s'
and `key`='%s'
and `sub_key`='%s'
and `date`>='%s' and `date`<='%s'
''' % (row_view['raw_data.oem_name'],row_view['raw_data.category'],row_view['raw_data.key'],row_view['raw_data.sub_key'].replace("\'","\\\'"),target_date,add_offset_as_a_flag_for_trace, \
row_view['raw_data.oem_name'],row_view['raw_data.category'],row_view['raw_data.key'],row_view['raw_data.sub_key'].replace("\'","\\\'"),patch_date_start,patch_date_end))
print sql
db.query(sql)
# coding=utf8
import glob
import re
import helper_regex
from helper_mysql import db
import _mysql
view_ids=[]
db.query(r'''
SELECT *
FROM (
SELECT *
FROM VIEW WHERE name like '%Telk_Armor Daily Paid User%'
) view
ORDER BY id ASC
''')
result_view = db.store_result()
columns_to_rename={
'总有效包日用户数':'Effective Daily Subscribers',
'总有效包周用户数':'Effective Weekly Subscribers',
'总有效包月用户数':'Effective Monthly Subscribers',
'新增包日用户':'New Daily Package Subscriptions',
'退订包日用户':'Daily Package Unsubscription',
'新增包周用户':'New Weekly Package Subscriptions',
'退订包周用户':'Weekly Package Unsubscription',
'新增包月用户':'New Monthly Package Subscriptions',
print date + ':' +str(i)
sql=(r'''
update
raw_data
set `key`=`key`+'_expired_'+'%s'
where `oem_name`='%s'
and `category`='%s'
and `key`='%s'
and `date`='%s'
/*limit 1*/
''' % (today_date,oem_name,category,key,date))
print sql
if do_execute:
db.query(sql)
"""
sql=(r'''
delete
FROM raw_data
where `oem_name`='%s'
and `category`='%s'
and `key`='%s'
and `date`='%s'
limit 1
''' % (oem_name,category,key,date))
print sql
if do_execute:
db.query(sql)
import glob
import re
import helper_regex
from helper_mysql import db
import _mysql
view_ids=[]
db.query(r'''
SELECT *
FROM `virtual_view` WHERE id in (106,104,102,97,96,95,94)
/*name like '%STC User Online Time Average & Top 100'*/
ORDER BY id ASC
''')
result_view = db.store_result()
while 1:
row_view = result_view.fetch_row(how=2)
if not row_view:
break
row_view=row_view[0]
print row_view['virtual_view.name']
view_ids.append(row_view['virtual_view.id'])
print view_ids
for offset in range(0,target_date_length):
current_date=helper_regex.date_add(target_date_start,offset)
print 'current_date: '+current_date
#get reference value of target date
reference_value_target=helper_mysql.get_one_value_string(
sql_reference_columns_tpl
% (current_date,current_date))
print 'reference_value_target: '+reference_value_target
#get base columns
db.query(sql_base_columns)
result_view = db.store_result()
while 1:
row_view = result_view.fetch_row(how=1)
if not row_view:
break
row_view=row_view[0]
print str(row_view)
#exit()
sql=r'''
update `%s`
import glob
import re
import helper_regex
from helper_mysql import db
import _mysql
import helper_mysql
view_ids=[]
db.query(r'''
SELECT *
FROM (
SELECT *
FROM VIEW WHERE /*name like 'STC%' or name like 'Technical STC%'*/ id in (137,1024,1025)
) view
ORDER BY id ASC
''')
result_view = db.store_result()
while 1:
row_view = result_view.fetch_row(how=2)
if not row_view:
break
row_view=row_view[0]
print row_view['view.name']
view_ids.append(row_view['view.id'])
print view_ids