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