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+";\n")
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("行:\n")
    #print(drop_table_sql)
    #print("行:\n")
    #print(construct_table_sql_rslt)
    f.write(drop_table_sql)
    f.write('\n')
    f.write(construct_table_sql_rslt)
    f.write('\n')
    #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)