最近在做本地同步至云的脚本
使用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)