一.导入表结构。使用工具:navicate premium 和PowerDesinger
1. 先用navicate premium把oracle中的数据库导出为oracle脚本。
2. 在PowerDesinger里找到 File -->> Reverse Engineer --->> Database 将数据库导入到模型。
3 在.PowerDesinger里找到Database”--->“Change Current DBMS” 将数据库由oracle转换为mysql
4. 在PowerDesinger里 按快捷键ctrl+G导出mysql的sql文件
5 用navicate premium 将脚本文件导入到mysql中。
(如果要导入的是oracle的dmp文件,请参阅 https://my.oschina.net/dolphinboy/blog/739248)
二.导入数据。 使用工具python 2.7 cx_Oracle mysql-python
在本例中oracle字符集为gbk, mysql字符集为 utf-8 在导入过程中进行了字符集转换。
导入程序代码如下:
#encoding:utf-8
importsysimportMySQLdbimportmysqlHelperimportcx_OracleimportoracleHelper
reload(sys)
sys.setdefaultencoding("utf-8")defmig_database():
sql="select table_name from user_tables"oraHelper=oracleHelper.OracleHelper()
rows=oraHelper.queryAll(sql)for row inrows:print row["TABLE_NAME"]
mig_table(row["TABLE_NAME"])defmig_table(table_name):
file_object= open('log.txt','a+')print "开始迁移"+table_name+"......"file_object.write("开始迁移"+table_name+"......\n")
myhelper=mysqlHelper.MySQLHelper()
oraHelper=oracleHelper.OracleHelper()
myhelper.selectDb("nap")
myhelper.query("delete from"+table_name)
rows=oraHelper.queryAll("select * from"+table_name)
n=0try:for row inrows:
dict=convertDict(row)
myhelper.selectDb("nap")
myhelper.insert(table_name,dict)
n=n+1myhelper.commit()print table_name+"迁移完毕,共导入%d条数据。" %n
file_object.write(table_name+"迁移完毕,共导入%d条数据。\n" %n)exceptMySQLdb.Error as e:print table_name+"迁移失败,程序异常!"file_object.write("table_name"+"迁移失败,程序异常!\n" %n)printe
file_object.close()defconvertDict(row):
dict={}for k inrow.keys():#if isinstance(row[k],basestring):
if isinstance(row[k],str) orisinstance(row[k],unicode):
dict[k]=unicode2utf8(gbk2unicode(row[k]))#dict[k]=row[k]
else:
dict[k]=row[k]returndictdefgbk2unicode(s):return s.decode('gbk', 'ignore')defunicode2utf8(s):return s.encode('utf-8')if __name__ == '__main__':
mig_database()
#encoding:utf-8
importMySQLdbfrom datetime import *
classMySQLHelper:#def __init__(self,host,user,password,charset="utf8"):
def __init__(self):
self.host='localhost'self.user='root'self.password='123456'self.charset='utf8'
try:
self.conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.password)
self.conn.set_character_set(self.charset)
self.cur=self.conn.cursor()exceptMySQLdb.Error as e:print("Mysql Error %d: %s" % (e.args[0], e.args[1]))defselectDb(self,db):try:
self.conn.select_db(db)exceptMySQLdb.Error as e:print("Mysql Error %d: %s" % (e.args[0], e.args[1]))defquery(self,sql):try:
n=self.cur.execute(sql)returnnexceptMySQLdb.Error as e:print("Mysql Error:%s\nSQL:%s" %(e,sql))raiseedefqueryRow(self,sql):
self.query(sql)
result=self.cur.fetchone()returnresultdefqueryAll(self,sql):
self.query(sql)
result=self.cur.fetchall()
desc=self.cur.description
d=[]for inv inresult:
_d={}for i inrange(0,len(inv)):
_d[desc[i][0]]=str(inv[i])
d.append(_d)returnddefinsert(self,p_table_name,p_data):for key inp_data:if (isinstance(p_data[key],str) orisinstance(p_data[key],datetime) ):if str(p_data[key])=="None":
p_data[key]='null'
else:
p_data[key]= "'"+str(p_data[key]).replace('%','%').replace('\'','')+"'"
else:
p_data[key]=str(p_data[key])
key='`'+ '`,`'.join(p_data.keys())+'`'value= ','.join(p_data.values())
real_sql= "INSERT INTO" + p_table_name + "(" + key + ") VALUES (" + value + ")"
returnself.query(real_sql)defgetLastInsertId(self):returnself.cur.lastrowiddefrowcount(self):returnself.cur.rowcountdefcommit(self):
self.conn.commit()defclose(self):
self.cur.close()
self.conn.close()
#encoding:utf-8
importcx_Oraclefrom datetime import *
classOracleHelper:def __init__(self):
self.charset='utf8'
try:
self.conn= cx_Oracle.connect('scott/tiger@DESKTOP-IASSOVJ/orcl')
self.cur=self.conn.cursor()exceptcx_Oracle.Error as e:print("Mysql Error %d: %s" % (e.args[0], e.args[1]))defselectDb(self,db):try:
self.conn.select_db(db)exceptcx_Oracle.Error as e:print("Mysql Error %d: %s" % (e.args[0], e.args[1]))defquery(self,sql):try:
n=self.cur.execute(sql)returnnexceptcx_Oracle.Error as e:print("Mysql Error:%s\nSQL:%s" %(e,sql))raiseedefqueryRow(self,sql):
self.query(sql)
result=self.cur.fetchone()returnresultdefqueryAll(self,sql):
self.query(sql)
result=self.cur.fetchall()
desc=self.cur.description
d=[]for inv inresult:
_d={}for i inrange(0,len(inv)):
_d[desc[i][0]]=str(inv[i])
d.append(_d)returnddefinsert(self,p_table_name,p_data):for key inp_data:if (isinstance(p_data[key],str) orisinstance(p_data[key],datetime) ):if str(p_data[key])=="None":
p_data[key]='null'
else:
p_data[key]= "'"+str(p_data[key]).replace('%','%').replace('\'','')+"'"
else:
p_data[key]=str(p_data[key])
key= ','.join(p_data.keys())
value= ','.join(p_data.values())
real_sql= "INSERT INTO" + p_table_name + "(" + key + ") VALUES (" + value + ")"
returnself.query(real_sql)defgetLastInsertId(self):returnself.cur.lastrowiddefrowcount(self):returnself.cur.rowcountdefcommit(self):
self.conn.commit()defclose(self):
self.cur.close()
self.conn.close()