当我们需要进行异构数据库迁移时，对于建表语句就有点头疼了，mysql迁移到oracle，我们可以通过导出mysql 建表语句后，使用powerdesigner来进行mysql DDL语句到ORACLE的转换，但步骤麻烦不说，mysql常用的navicat批量导出表结构不是很友好，pd的转换也有些不可控。下面我们通过python脚本来进行导出转换。
- 首先我们需要安装操作mysql的模块 MySQLdb，可自行搜索 MySQL_python-1.2.5-cp27-none-win_amd64 来安装
以下是脚本内容，只需将 db1或者db2 替换成自己的数据库信息，然后配置sc的值指向 db1,或者db2 运行脚本即可
# -*- coding: UTF-8 -*-
import re
import MySQLdb
import datetime
def create_table(mysql_result,tablename):
colline=""
pk=""
count=0
print "CREATE TABLE "+tablename.upper()+" ("
fileobj.write("CREATE TABLE "+tablename.upper()+" (\n")
for onecol in mysql_result:
colname=onecol[0]
if colname.upper() == "DESC" or colname.upper() == "COMMENT" or colname.upper() =="LEVEL" \
or colname.upper() =="ID" :#or colname.upper() =="REMARK":
colname='"'+colname+'"'
#int
if onecol[1]=="bigint":
colline= colname+ " number(20,0),"
elif onecol[1]=="int":
colline= colname+ " number(10,0),"
elif onecol[1]=="mediumint":
colline= colname+ " number(7,0),"
elif onecol[1]=="smallint":
colline= colname+ " number(5,0),"
elif onecol[1]=="tinyint":
colline= colname+ " number(3,0),"
elif onecol[1]=="decimal" or onecol[1]=="numeric" or onecol[1]=="float":
a=onecol[3]
b=onecol[4]
if a >= 38:
a=38
if b >= 5:
b = 5
colline= colname+ " number("+str(a)+","+str(b)+"),"
#date
elif onecol[1]=="date" or onecol[1]=="datetime" or onecol[1]=="time":
colline= colname+ " date,"
#varchar
elif onecol[1]=="char" or onecol[1]=="varchar" :
if onecol[2]<=4000:
if onecol[2]==0:
colline= colname+ " varchar2(10),"
else:
colline= colname+ " varchar2("+str(onecol[2])+"),"
else:
#colline= colname + " clob,"
colline= colname + " varchar2(4000),"
#text
elif onecol[1]=="text" or onecol[1]=="mediumtext" or onecol[1]=="longtext":
colline= colname+ " clob,"
elif onecol[1]=="tinytext":
colline= colname+ " varchar2(4000),"
#blob
elif onecol[1]=="tinyblob" or onecol[1]=="blob" or onecol[1]=="mediumblob" or onecol[1]=="longblob":
colline= colname+ " blob,"
else:
colline= colname+" "+onecol[1]+","
if onecol[5] == "PRI":
if pk != "":
pk=pk+","+colname
else:
pk=colname
count+=1
if count == len(mysql_result):
if pk != "":
print " ",colline.upper()
print " PRIMARY KEY(",pk.upper(),")"
fileobj.write(" "+colline.upper()+"\n")
fileobj.write(" PRIMARY KEY("+pk.upper()+")\n")
else:
print " ",colline.strip(',').upper()
fileobj.write(" "+colline.strip(',').upper()+"\n")
print ") ;"
fileobj.write(") ;\n")
else:
print " ",colline.upper()
fileobj.write(" "+colline.upper()+"\n")
def test1(ipadd,portnum,dbname,dbuser,dbpassword,tablenamelist):
try:
conn = MySQLdb.connect(host=ipadd,port=portnum,user=dbuser,passwd=dbpassword,db=dbname)
indexcur=conn.cursor()
cur=conn.cursor()
#可以不使用hint，连哪个库时，那个information_schema必然包含了此库的标准库
if len(tablenamelist) == 0:
cur.execute('select /* !HINT({"dn":["'+dbname+'"]})*/ distinct table_name from information_schema.columns where TABLE_SCHEMA="'+dbname+'"')
results=cur.fetchall()
for item in results:
tablenamelist.append(item[0])
for tablename in tablenamelist:
if tablename.upper() == "_SYS_GLOBAL_SEQUENCE_":
continue
cur.execute('select /* !HINT({"dn":["'+dbname+'"]})*/column_name,data_type,character_octet_length,NUMERIC_PRECISION,NUMERIC_SCALE,COLUMN_KEY from information_schema.columns where table_name="'+tablename+'" and TABLE_SCHEMA="'+dbname+'" order by ordinal_position')
results=cur.fetchall()
create_table(results,tablename)
createindex(tablename,indexcur,dbname)
indexcur.close()
cur.close()
conn.close()
except Exception as e:
print 'error:',e
dupInx={}; #distinct from full env (all db in config every time )
def createindex(tablename,indexcur,dbname):
indexcur.execute(' select index_name,table_name, GROUP_CONCAT(column_name) from information_schema.statistics '\
'where table_schema="'+dbname+'" and table_name="'+tablename+'" and index_name != "PRIMARY" group by index_name,table_name order by seq_in_index')
results = indexcur.fetchall()
for item in results:
indexname=item[0]
#if the indexname is exists,then set name string+number
indexname=indexname.upper()
if indexname[0:3] == "IDX":
indexname="IDX_"+dbname.upper()+"_"+indexname[3:]
else:
indexname="IDX_"+dbname.upper()+"_"+indexname
if dupInx.has_key(indexname):
dupInx[indexname] += 1
#print "*****************************************"
indexname=indexname+str(dupInx[indexname])
#print indexname
else:
dupInx[indexname] = 0
cre="CREATE INDEX "+indexname+" ON "+item[1].upper()+"("+item[2].upper()+") ;"
#print cre
fileobjinx.write(cre+"\n")
db1=[
('xxx.xxx.xxx.xxx', 8999, 'cusdb',"user","password",[]),
('xxx.xxx.xxx.xxx', 8998, 'orddb',"user","password",[])
]
db2= [
('127.0.0.1', 3306, 'testdb',"root","m123",[])
]
sc=2
nowTime=datetime.datetime.now().strftime('%Y%m%d%H%M%S')
if sc==1:
for x in db1:
print "\n"
print "---------------shengchan database: ",x[2]," ----------------------"
fileobj=open('E:/createtable/shengchan/'+x[2]+nowTime, 'w')
fileobjinx=open('E:/createtable/shengchan/idx__'+x[2]+nowTime, 'w')
test1(x[0],x[1],x[2],x[3],x[4],x[5])
fileobjinx.close()
fileobj.close()
print "over. script stored in E:/createtable/shengchan/"
elif sc==2:
try:
for x in db2:
print "\n"
print "---------------ceshi database: ",x[2]," ----------------------"
fileobj=open(unicode('E:\从mysql导出oracle建表语句脚本\本机',"utf-8")+x[2]+nowTime, 'w')
fileobjinx=open(unicode('E:\从mysql导出oracle建表语句脚本\本机',"utf-8")+x[2]+nowTime, 'w')
test1(x[0],x[1],x[2],x[3],x[4],x[5])
fileobjinx.close()
fileobj.close()
print "over. script stored in \结算中心化\从mysql导出oracle建表语句脚本"
except Exception as e:
print 'error:',e