MySQL上雲同步指令碼-Python3

最近在做本地同步至雲的指令碼
使用kettle的話,幾百個表的同步要一一設定,實在是蛋疼的緊
還好python可以解決一部分問題,所以寫了個轉換
由於5.6版本的mysql對null的處理不是很好,因此全部轉為varchar型
blob和longblob,text都單獨處理

#-*- coding: UTF-8 -*-
#獲取對比列表
#表結構同步到雲上mysql
#實現功能:oracle-mysql列轉換/寫入指令碼/傳輸指令碼
#待實現功能:執行指令碼
#james.peng 20170905

import os 
os.environ[`NLS_LANG`] = `SIMPLIFIED CHINESE_CHINA.UTF8`
import cx_Oracle
import pymysql
import datetime
import time

Start_time = time.strftime(`%Y-%m-%d %H:%M:%S`, time.localtime(time.time()))
date_nyr = time.strftime(`%Y%m%d`, time.localtime(time.time()))
#存放結果的txt
os_dir=os.chdir(`/a/e/p/y`)
remote_loc=`/a/e/p/y/`+date_nyr+`/`
try:
    os.mkdir(date_nyr)
    linux_shell=`chmod 777 `+date_nyr
    os.popen(linux_shell)
except:
    print(`folder_existed!`)
os_dir=`/a/e/p/y/`+date_nyr
os.chdir(os_dir)

try:
    f=open(os_dir+`/create_y_script.txt`,`w`)
    f.truncate()
    f.write("use "+Mysql_schema+";
")
except:
    print(`exception!`)
print(os.getcwd())

#存放結果的list
create_yrs_table_list=[]

#parameters--引數設定
jump_server_host=
jump_server_destination=

Mysql_yrs_ip_port=
Mysql_yrs_username=
Mysql_yrs_passwd=
Mysql_yrs_db=
Mysql_yrs_port=
Mysql_yrs_schema=

Mysql_ip_port=
Mysql_username=
Mysql_passwd=
Mysql_db=
Mysql_port=
Mysql_schema=


#connection strings--連線資訊
mysql_yrs_info = pymysql.connect(Mysql_yrs_ip_port,Mysql_yrs_username,Mysql_yrs_passwd,Mysql_yrs_db,Mysql_yrs_port,charset=`utf8`)
mysql_yrs_cursor = mysql_yrs_info.cursor()
mysql_info = pymysql.connect(Mysql_ip_port,Mysql_username,Mysql_passwd,Mysql_db,Mysql_port)
mysql_cursor = mysql_info.cursor()


#開始連線,
#<<<<步驟一>>>>先獲取要同步的表,按表名順序排列
mysql_yrs_cursor.execute("SET group_concat_max_len=10000;")
Mysql_yrs_table_name_sql="select table_name from information_schema.tables where "
Mysql_yrs_table_name_sql+="TABLE_SCHEMA=`"+Mysql_yrs_schema+"` order by table_name asc ;"
mysql_yrs_cursor.execute(Mysql_yrs_table_name_sql)
Mysql_yrs_table_name = mysql_yrs_cursor.fetchall()
#print(Mysql_yrs_table_name)

for i_sync_list in Mysql_yrs_table_name:
    i_sync_list=str(i_sync_list).replace(`(`,``).replace(`,)`,``).replace(```,``)
    print(i_sync_list)
    drop_table_sql="drop table  "+Mysql_schema+"."+i_sync_list+`;`
    """
    construct_table_sql="select  concat(`create table test320.account_credit(`,"
    construct_table_sql+=" group_concat(concat(column_name,` varchar(` ,"
    construct_table_sql+=" case when data_type in (`int`,`decimal`,`bigint`,`tinyint`,`double`,`bit`) then numeric_precision*3"
    construct_table_sql+=" when data_type in (`varchar`,`char`) then round(character_maximum_length*1.2) "
    construct_table_sql+=" when data_type in (`date`,`datetime`,`datetime`,`timestamp`) then `50` "
    construct_table_sql+=" else data_type end ,`)`))) from information_schema.columns "
    construct_table_sql+=" where TABLE_SCHEMA=`test320`and table_name=`account_credit`"
    """
    construct_table_sql="select  concat(`create table "+Mysql_schema+"."+i_sync_list+"(`,"
    construct_table_sql+=" group_concat(concat(column_name,` varchar(` ,"
    construct_table_sql+=" case when data_type in (`int`,`decimal`,`bigint`,`tinyint`,`double`,`bit`) then round(numeric_precision*1.2)"
    construct_table_sql+=" when data_type in (`varchar`,`char`) then round(character_maximum_length*1.2) "
    construct_table_sql+=" when data_type in (`date`,`datetime`,`datetime`,`timestamp`) then `50` "
    construct_table_sql+=" else data_type end ,`)`))) from information_schema.columns "
    construct_table_sql+=" where TABLE_SCHEMA=`"+Mysql_yrs_schema+"`and table_name=`"+i_sync_list+"`"    
    
    #print("構建")
    #print(construct_table_sql)
    #print("構建完成")
    mysql_yrs_cursor.execute(construct_table_sql)
    construct_table_sql_rslt=str(mysql_yrs_cursor.fetchall()).replace("((`","").replace("`))",");")
    construct_table_sql_rslt=construct_table_sql_rslt.replace("varchar(longtext)","longtext").replace("varchar(blob)","blob")
    construct_table_sql_rslt=construct_table_sql_rslt.replace("varchar(text)","text").replace("varchar(longblob)","longblob")
    #print("行:
")
    #print(drop_table_sql)
    #print("行:
")
    #print(construct_table_sql_rslt)
    f.write(drop_table_sql)
    f.write(`
`)
    f.write(construct_table_sql_rslt)
    f.write(`
`)
    #print(mysql_create_sql_build)
f.close()




linux_shell="/usr/bin/scp -P 20 create_yrs_script.txt rds@aliyunsql:/h/r/a/i/y/"
linux_shell_result=os.popen(linux_shell).read()
print(linux_shell)
print(linux_shell_result)


mysql_yrs_cursor.close()
mysql_cursor.close()
End_time = time.strftime(`%Y-%m-%d %H:%M:%S`, time.localtime(time.time()))
print(`開始時間`, Start_time)
print(`完成時間`, End_time)