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 _get_sql_select_collection_id_by_date(oem_name,category,key,sub_key,table_name): sub_key=helper_mysql.escape_string(sub_key) if table_name.startswith('raw_data'): sql_date=helper_regex.generate_date_range_for_raw_data_query(day_range=1000) sql=r""" select `date`,`value` from `%s` where `oem_name`='%s' and `category`='%s' and `key`='%s' and `sub_key`='%s' %s """ %(table_name,oem_name,category,key,sub_key,sql_date) return sql if table_name.startswith('data_int'): sql_date=helper_regex.generate_date_range_for_data_query(day_range=1000) sql=r""" select concat(substring(`date`,1,4),'-',substring(`date`,5,2),'-',substring(`date`,7,2)) ,`value` from `%s` where `key`=%s and `sub_key`=%s %s """ %(table_name,helper_regex.get_md5_key(oem_name,category,key),sub_key,sql_date) return sql if table_name.startswith('data'): sql_date=helper_regex.generate_date_range_for_data_query(day_range=1000) sql=r""" select concat(substring(`date`,1,4),'-',substring(`date`,5,2),'-',substring(`date`,7,2)) ,`value` from `%s` where `key`=%s and `sub_key`=%s %s """ %(table_name,helper_regex.get_md5_key(oem_name,category,key),helper_regex.get_md5_sub_key(sub_key),sql_date) return sql sql='' return sql
source_rows=helper_mysql.fetch_rows(sql=r''' select * from %s where id>=%s order by id limit %s ''' % (table_name,current_start_id,source_step),db_conn=source_conn) if not source_rows: print '===finished at:',table_name,current_start_id print helper_regex.get_time_str_now() 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()
source_rows=helper_mysql.fetch_rows(sql=r''' select * from %s where id>=%s order by id limit %s ''' % (table_name,current_start_id,source_step),db_conn=source_conn) if not source_rows: print '===finished at:',table_name,current_start_id print helper_regex.get_time_str_now() 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','%s','%s')" % \ (row['oem_name'],row['category'],row['key'],helper_mysql.escape_string(row['sub_key']),row['date'],row['value'],row['created_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)]: break sql='replace into '+table_name+' (oem_name,category,`key`,sub_key,date,`value`,created_on) 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()