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

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()

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

标签：odi   utf-8   eve   write   ==   datetime   encoding   elf   __init__

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/deartear/p/7216961.html