python oracle转mysql_python将oracle中的数据导入到mysql中。

一.导入表结构。使用工具：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()