python实现 mysql 表批量导成oracle建表以及索引语句

当我们需要进行异构数据库迁移时，对于建表语句就有点头疼了，mysql迁移到oracle，我们可以通过导出mysql 建表语句后，使用powerdesigner来进行mysql DDL语句到ORACLE的转换，但步骤麻烦不说，mysql常用的navicat批量导出表结构不是很友好，pd的转换也有些不可控。下面我们通过python脚本来进行导出转换。

1. 首先我们需要安装操作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