def fetch_row(conn_config,sql):
start_time=helper_regex.get_time_clock_time()
start_time_str=helper_regex.get_time_str_now()
script_file_name=helper_regex.get_script_file_name()
try:
conn = get_db(conn_config)
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg='',start_time_str=start_time_str)
return conn.execute_row(sql)
except Exception,e:
print 'failed:'+str(e)
print "Unexpected error:", sys.exc_info()[0]
print sql
print 'SQL Server error happened !!!'
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg=sys.exc_info()[0],start_time_str=start_time_str)
return -1
def execute(conn_config,sql,log_sql=True):
conn = get_db(conn_config)
start_time=helper_regex.get_time_clock_time()
start_time_str=helper_regex.get_time_str_now()
script_file_name=helper_regex.get_script_file_name()
#print dir(conn)
conn.execute_non_query(sql)
if log_sql:
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg='',start_time_str=start_time_str)
return conn.identity or conn.rows_affected
try:
pass
except Exception,e:
print 'failed:'+str(e)
print "Unexpected error:", sys.exc_info()[0]
print sql
print 'SQL Server error happened !!!'
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg=sys.exc_info()[0],start_time_str=start_time_str)
return -1
def fetch_joined_array(conn_config,sql,seperator=''):
start_time=helper_regex.get_time_clock_time()
start_time_str=helper_regex.get_time_str_now()
script_file_name=helper_regex.get_script_file_name()
try:
conn = get_db(conn_config)
rows=conn.execute_query(sql)
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg='',start_time_str=start_time_str)
array=[]
for row in conn:
array.append(row[0])
return seperator.join(array)
except Exception,e:
print 'failed:'+str(e)
print "Unexpected error:", sys.exc_info()[0]
print sql
print 'SQL Server error happened !!!'
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg=sys.exc_info()[0],start_time_str=start_time_str)
return -1
def fetch_rows_dict(conn_config,sql,key_name=0):
start_time=helper_regex.get_time_clock_time()
start_time_str=helper_regex.get_time_str_now()
script_file_name=helper_regex.get_script_file_name()
try:
conn = get_db(conn_config)
rows=conn.execute_query(sql)
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg='',start_time_str=start_time_str)
dict={}
for row in conn:
dict[str(row[key_name])]=row
return dict
except Exception,e:
print 'failed:'+str(e)
print "Unexpected error:", sys.exc_info()[0]
print sql
print 'SQL Server error happened !!!'
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg=sys.exc_info()[0],start_time_str=start_time_str)
return -1
def fetch_set(conn_config,sql):
start_time=helper_regex.get_time_clock_time()
start_time_str=helper_regex.get_time_str_now()
script_file_name=helper_regex.get_script_file_name()
conn = get_db(conn_config)
rows=conn.execute_query(sql)
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg='',start_time_str=start_time_str)
ret=set([])
for row in conn:
ret.add(row[0])
return ret
try:
pass
except Exception,e:
print 'failed:'+str(e)
print "Unexpected error:", sys.exc_info()[0]
print sql
print 'SQL Server error happened !!!'
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg=sys.exc_info()[0],start_time_str=start_time_str)
return -1
def fetch_dict(conn_config,sql,key_name=0,value_name=1,use_interger_key=False):
start_time=helper_regex.get_time_clock_time()
start_time_str=helper_regex.get_time_str_now()
script_file_name=helper_regex.get_script_file_name()
sql=str(sql)
conn = get_db(conn_config)
rows=conn.execute_query(sql)
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg='',start_time_str=start_time_str)
dict={}
counter=0
if use_interger_key:
for row in conn:
dict[row[key_name]]=row[value_name]
#counter+=1
#if counter % 100000 ==0:
# print counter
else:
for row in conn:
dict[str(row[key_name])]=row[value_name]
#counter+=1
#if counter % 100000 ==0:
# print counter
return dict
try:
pass
except Exception,e:
print 'failed:'+str(e)
print "Unexpected error:", sys.exc_info()[0]
print sql
print 'SQL Server error happened !!!'
helper_mysql.log_execution_info(script_file_name,sql,start_time,error_msg=sys.exc_info()[0],start_time_str=start_time_str)
return -1
from helper_mysql import db
import _mysql
helper_mysql.db_throw_all_exception=True
source_conn=config._conn_stat_portal_158
target_conn=config._conn_stat_portal_142
config.conn_stat_portal=config._conn_stat_portal_158
target_tables=[
'collection',
]
print helper_regex.get_time_str_now()
for table_name in target_tables:
#current_max_id=helper_mysql.get_raw_data(oem_name='Stat_Portal',category='data_migrate',key='max_transfered_id',sub_key=table_name,default_value=0,table_name='raw_data_debug',date='',db_conn=None)
current_start_id=helper_mysql.get_raw_data(oem_name='Stat_Portal',category='data_migrate',key='max_transfered_id',sub_key=table_name,default_value=0,table_name='raw_data_debug',date='',db_conn=None)
source_step=10000
target_step=10000
for i in range(0,100000000):
current_start_id+=1
print '===now at:',table_name,current_start_id
source_rows=helper_mysql.fetch_rows(sql=r'''
import config
import helper_regex
import helper_mysql
import time
import helper_redis
helper_mysql.db_throw_all_exception=True
source_conn=config.conn_stc_1
target_tables={
'friendship.friendship':'production_copy_friendship_stc',
}
print helper_regex.get_time_str_now()
for table_name,target_table_name in target_tables.iteritems():
current_start_id=helper_mysql.get_raw_data(oem_name='Stat_Portal',category='data_migrate_friend_relation',key='max_transfered_id',sub_key=table_name,default_value=0,table_name='raw_data_debug',date='',db_conn=None)
source_step=50000
#target_step=50000
#current_start_id=20000000
for i in xrange(0,100000001):
time.sleep(3)
current_start_id+=1
print '===now at:',table_name,current_start_id
sql=r'''
select id,user_id,friend_id,0+following as following,0+followed as followed,0+blocking as blocking,0+blocked as blocked,0+flags as flags,created_on,modified_on
import re
import helper_regex
import helper_mysql
from helper_mysql import db
import _mysql
source_conn=config._conn_stat_portal_158
target_conn=config._conn_stat_portal_142
target_tables=[
'raw_data_url_pattern'
]
print helper_regex.get_time_str_now()
for table_name in target_tables:
#current_max_id=helper_mysql.get_raw_data(oem_name='Stat_Portal',category='data_migrate',key='max_transfered_id',sub_key=table_name,default_value=0,table_name='raw_data_debug',date='',db_conn=None)
begin_date='2011-07-11'
step=1000
for i in range(0,1000):
current_date=helper_regex.date_add(begin_date,-i)
print table_name,current_date
r'''
select *
from %s
