Exemple #1
0
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`
Exemple #6
0
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