def grant_view(view_name, group_id):
view_id = helper_mysql.fetch_row(
sql=r"""
select id from `view` where `name`='%s'
"""
% (view_name,)
)
view_id = view_id["id"] if view_id.has_key("id") else ""
if view_id:
helper_mysql.execute(
sql=r"""
replace INTO `mozat_stat`.`group_to_view` (
`group_id` ,
`view_id`
)
VALUES (
'%s', '%s'
);
"""
% (group_id, view_id)
)
return 1
return 0
def replace_view(view_name, view_sql="", view_description="", charting_javascript=""):
view_id = helper_mysql.fetch_row(
sql=r"""
select id from `view` where `name`='%s'
"""
% (view_name,)
)
view_id = view_id["id"] if view_id.has_key("id") else ""
sql = r"""
replace into `view` (`id`,`name`,`sql`,`description`,`charting_javascript`)
values ('%s','%s','%s','%s','%s')
""" % (
view_id,
view_name,
helper_mysql.escape_string(view_sql),
helper_mysql.escape_string(view_description),
helper_mysql.escape_string(charting_javascript),
)
print sql
return helper_mysql.execute(sql)
def update_view(view_name, func_to_replace_description=None, func_to_replace_sql=None):
print view_name
sql = """
select *
from `view`
where `name`='%s'
""" % (
view_name,
)
print sql
view_row = helper_mysql.fetch_row(sql=sql)
if not view_row:
print view_name, "not found"
return -1
else:
print "view found"
description = view_row["description"]
sql = view_row["sql"]
if func_to_replace_description:
description = func_to_replace_description(description)
if func_to_replace_sql:
sql = func_to_replace_sql(sql)
sql = r"""
update `view`
set `sql`='%s',`description`='%s'
where `name`='%s'
""" % (
helper_mysql.escape_string(sql),
helper_mysql.escape_string(description),
view_name,
)
print sql
helper_mysql.execute(sql)
return 1
def process(client_type):
global whole_collection, element_existance_counters, sql_collection_id, client_dict
current_collection={}
type_number=client_number_dict[client_type]
collection_ids = helper_mysql.fetch_dict(sql_collection_id % (client_type,))
print collection_ids
for date,collection_id in collection_ids.iteritems():
collection=helper_mysql.get_raw_collection_by_id(collection_id)
if not collection_id or not collection:
print 'No collection of',collection_id
continue
whole_collection|=collection
for i in collection:
current_collection[i]=type_number
print len(current_collection)
field_name='client-type'
sql=r'''
update mozat_clustering.user_figure_base
set `%s`='%s'
where `oem_id`=7
and `user_id`='%s'
limit 1
'''
for user_id, t in current_collection.iteritems():
helper_mysql.execute(sql % (field_name,t,user_id))
def process():
global sql_collection_id
current_date=datetime.fromtimestamp(my_date).strftime('%Y-%m-%d')
info_dict=helper_mysql.fetch_dict(sql_collection_id)
print len(info_dict)
sql=r'''
update mozat_clustering.user_figure_base
set `%s`='%s'
where `oem_id`=7
and `user_id`='%s'
limit 1
'''
field_name='client-type'
for user_id,value in info_dict:
helper_mysql.execute(sql % (field_name,value,user_id))
break
print 'length of source:',len(source_rows)
print helper_regex.get_time_str_now()
sql_temp=[]
for row in source_rows:
sql_temp.append("('%s','%s','%s','%s','%s')" % \
(row['id'],row['created_on'],row['element_count'],row['element_string_md5'],helper_mysql.escape_string(row['element_string']),))
current_start_id=max(current_start_id,int(row['id']))
for i in range(0,100000001,target_step):
if i>len(sql_temp) or not sql_temp[i:min(i+target_step,len(sql_temp)+1)]:
break
sql='replace into '+table_name+' (id,created_on,element_count,element_string_md5,element_string) values '+(','.join(sql_temp[i:min(i+target_step,len(sql_temp)+1)]))
print helper_regex.get_time_str_now()+' slice: ',i,', affeted:',helper_mysql.execute(sql,db_conn=target_conn)
helper_mysql.put_raw_data(oem_name='Stat_Portal',category='data_migrate',key='max_transfered_id',sub_key=table_name,value=current_start_id,table_name='raw_data_debug',date='',db_conn=None)
print '===saved max_id:',current_start_id
print helper_regex.get_time_str_now()
exit()
sql_temp=[]
for row in source_rows:
sql_temp.append("('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % \
(row['id'],row['user_id'],row['friend_id'],row['following'],row['followed'],row['blocking'], \
row['blocked'],row['flags'],row['created_on'],row['modified_on'],))
current_start_id=max(current_start_id,int(row['id']))
for i in range(0,100000001,target_step):
if i>len(sql_temp) or not sql_temp[i:min(i+target_step,len(sql_temp)+1)]:
print 'break in replace loop'
break
sql='replace into '+target_table_name+' (id,user_id,friend_id,following,followed,blocking,blocked,flags,created_on,modified_on) values '+ \
(','.join(sql_temp[i:min(i+target_step,len(sql_temp)+1)]))
#print sql
affected=helper_mysql.execute(sql,db_conn=target_conn)
print helper_regex.get_time_str_now()+' slice: ',i,', affeted:',affected
helper_mysql.put_raw_data(oem_name='Stat_Portal',category='data_migrate_friend_relation',key='max_transfered_id',sub_key=table_name,value=current_start_id,table_name='raw_data_debug',date='',db_conn=None)
print '===saved max_id:',current_start_id
print helper_regex.get_time_str_now()
exit()
import config
import glob
import re
import helper_regex
import helper_mysql
from helper_mysql import db
import _mysql
import time
helper_mysql.db_throw_all_exception=True
source_conn=config.conn_stc_1
target_conn=config._conn_stat_portal_142
for i in range(0,100000000,50000):
sql=r'''
replace INTO `production_copy_friendship_stc_2`(`user_id`, `friend_id`, `following`, `followed`, `blocking`, `blocked`, `flags`, `created_on`, `modified_on`)
select `user_id`,`friend_id`,`following`,`followed`,`blocking`,`blocked`,`flags`,`created_on`,`modified_on`
from production_copy_friendship_stc where id>=%s and id<%s
''' % (i,i+50000)
helper_mysql.execute(sql)
print i
#break
def transfer_one_table(table_name):
helper_mysql.execute('truncate table mozat_stat.'+table_name, db_conn=target_mysql_db)
step=10000
current_max_id=0
if __name__=='__main__':
for i in range(200,0,-1):
my_date=time.time()-3600*24*i
process(my_date)
print len(element_existance_counters)
field_name='login-login_days'
#field_name='app-mochat_service_days'
#field_name='app-im_service_days'
#field_name='app-chatroom_service_days'
sql=r'''
update mozat_clustering.user_figure_base
set `%s`='%s'
where `oem_id`=7
and `user_id`='%s'
limit 1
'''
for user_id,count in element_existance_counters.iteritems():
#print sql % (count,user_id)
helper_mysql.execute(sql % (field_name,count,user_id))
print len(element_existance_counters)
sql=r'''
update mozat_clustering.user_figure_base
set %s
where `oem_id`=7
and `user_id`='%s'
limit 1
'''
counter=0
for user_id,apps in element_existance_counters.iteritems():
#print sql % (count,user_id)
set_sql=''
for app,count in apps.iteritems():
if app=='unrecognized':
continue
set_sql+=',`app-'+app+'_days`='+str(count)
if set_sql=='':
continue
#print sql % (set_sql[1:],user_id)
#exit()
helper_mysql.execute(sql % (set_sql[1:],user_id))
counter+=1
print counter
from %s
where date='%s' and `oem_name`="Vodafone" and `category`="website"
''' % (table_name,current_date),db_conn=source_conn)
#exit()
print 'length of source:',len(source_rows)
print helper_regex.get_time_str_now()
sql_temp=[]
for row in source_rows:
sql_temp.append("('%s','%s','%s','%s','%s','%s','%s')" % \
(row['oem_name'],row['category'],row['key'],helper_mysql.escape_string(row['sub_key']),row['date'],row['value'],row['created_on'],))
for i in range(0,100000001,step):
print helper_regex.get_time_str_now()
print 'slice:',i
if i>len(sql_temp):
break
sql='replace into '+table_name+' (oem_name,category,`key`,sub_key,date,`value`,created_on) values '+(','.join(sql_temp[i:min(i+step,len(sql_temp)+1)]))
#print sql
helper_mysql.execute(sql,db_conn=target_conn)
print helper_regex.get_time_str_now()
exit()
