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()