现状:
目前开发的一个程序,因为数据的提供方是第三方,所用到的数据库是 Oracle, 而我们此项目所应用的数据库是 MySQL。而且对方给的数据库我们只有查询的权限,无法修改相应的数据,
所以我们需要将两个不同的数据库进行同步操作! 因为我们需要对数据进行增、删、改、查操作。所以不得已而为之!
解决思路:
1、将 Oracle 数据的全部数据导出已 csv 的格式导出, 然后在 MySQL 数据中导入。
该方法遇到的问题有:
a、导出时,如果是身份证字段,导出的结果会以科学计数法表示,导入后,无法复原原始数据(有解决方法)
b、如果对方数据增加,我们导出数据的时候,无法做到增量更新!
2、将 Oracle 数据的数据以 sql 语句的形式导出,然后 MySQL 用 SQL 语句导入。
该方法比较推荐,因为可以保证数据的完整性。但是也不太好实现数据的增量更新
3、利用 Python 实现 Oracle 数据库 与 MySQL 数据库的增量更新。
实现步骤:
一、环境:
Python 3.7 + pymysql + cx_Oracle 模块
二、实现思路:
我们通过 cx_Oracle 模块获取Oracle 数据库中的数据, 然后通过 pymysql 模块将数据插入 MySQL 数据库中。
插入过程中,通过判断某些字段,来实现对数据库的增量更新操作。
三、实施:
a、安装 pymysql 模块 和 cx_Oracle 模块
在cmd 窗口输入: pip install pymysql 和 pip install cx_Oracle
b、连接 Oracle 数据库的类
import cx_Oracle
class LinkOracle(object):
# 构造函数,初始化
def __init__(self,user,passwd,host,port,db):
self.user = user
self.passwd = passwd
self.host = host
self.port = port
self.db = db
# 连接数据库
def con_Oracle(self):
# 连接数据库,获取一个连接对象
linkObj = self.user + "/" + self.passwd + "@" + self.host + ":" + str(self.port) + "/" + self.db
conn = cx_Oracle.connect( linkObj )
# 获取游标
cursor = conn.cursor()
return cursor ,conn
# 执行sql 语句
def search_Oracle(self,sql):
cursor, conn = self.con_Oracle()
# 执行sql 语句
try:
# 执行 SQL
cursor.execute(sql)
res = cursor.fetchall()
# 提交事务
conn.commit()
return res
except:
# 发生错误时回滚
conn.rollback()
# 断开连接
def close_Oracle(self):
cursor, conn = self.con_Oracle()
# 断开连接
cursor.close()
conn.close()
c、连接 MySQL 数据库的类
import pymysql
class ConnectSql():
def __init__(self,host,user,passwd,db,port ):
self.host = host
self.user = user
self.passwd = passwd
self.db = db
self.port = port
# 连接数据库
def connect_sql(self):
# 连接数据库
db = pymysql.connect( host=self.host, user=self.user,password= self.passwd, database=self.db, port=self.port )
# 获取游标
cursor = db.cursor()
return db,cursor
# 执行sql 语句
def command_sql(self,sql):
db,cursor = self.connect_sql()
# 执行sql 语句
try:
cursor.execute(sql)
# 提交到数据库执行
db.commit()
except:
# 发生错误时回滚
db.rollback()
# 查询
def searchDB(self,sql):
db, cursor = self.connect_sql()
# 执行sql 语句
try:
cursor.execute(sql)
# 查询数据库中所有的数据
res_data = cursor.fetchall()
return res_data
except:
# 发生错误时回滚
db.rollback()
def close(self):
db, cursor = self.connect_sql()
db.close()
cursor.close()
【备注】
1、可以将两个类合并为一个类, 因为大部分函数是一样的! 代码可以复用
2、连接 Oracle 数据库时, 你可能无法连接成功。需要安装 Oracle 数据库。
d、实例化对象操作
import time
from linkOracle import LinkOracle
from linkMysql import ConnectSql
# 定义数据库信息 Mysql
Mhost = "mysql 数据库的ip地址 "
Muser = "数据库用户名"
Mpasswd = "数据库密码"
Mdb = "所连接的数据库"
Mport = 端口(默认情况下 3306)
# 连接数据库, Oracle
Ouser = "Oracle 数据库用户名"
Opasswd = "数据库密码"
Ohost = "数据库地址"
Oport = 端口
Odb = "实例名"
# 实例化
# Oracle 数据库连接实例化
Odb = LinkOracle( Ouser, Opasswd, Ohost, Oport, Odb )
# 连接 Oracle 数据库
Ocursor, Oconn = Odb.con_Oracle()
# 获取数据,所有数据
sql = "select * from 表名"
# OresList = Odb.execute_Oracle(sql)[0:50] # 获取50 条数据
OresList = Odb.execute_Oracle(sql)
# Mysql 数据库连接实例化
MresStuIdList = []
Mdb = ConnectSql( Mhost,Muser,Mpasswd,Mdb,Mport )
# 连接 MMySQL 数据库
Mconn,Mcursor = Mdb.connect_sql()
# 获取数据
sql = "select * from test0001"
# 执行 sql 命令
MresList = Mdb.searchDB(sql)
# 循环遍历,将学号加入到列表中
for MresStuId in MresList:
MresStuIdList.append( MresStuId[1] )
e、向 MySQL 数据库中插入数据
# 逻辑判断,如果Oracle 数据库的数据在 mysql 数据库中,直接pass , 如果不在,则mysql 数据库更新
# 循环 Oracle 数据库数据
for Ores in OresList:
# 学号, 如果该学号在mysql 数据库里面,则无操作
if Ores[0] in MresStuIdList:
pass
# 将 Oracle 数据库的数据添加进 mysql 中
else:
insert_sql = 'INSERT INTO test0001 VALUES (%d,"%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")' % (0, Ores[0], Ores[1], Ores[2], Ores[3], Ores[4], Ores[5], Ores[6], Ores[7], Ores[8], Ores[9], Ores[10], Ores[11], Ores[12], Ores[13], Ores[14], Ores[15], Ores[16] )
# 执行sql 语句
Mdb.command_sql( insert_sql )
f、完成
运行程序,则可以实现 Oracle 数据与 MySQL 数据库的增量更新操作
【备注】:
1、SQL 语句需与实际情况为准
2、建表语句没有在代码中体现,可以用 SQL 语句进行创建
3、此代码如果遇到数据量比较大,可能耗时比较长和占用内存较大
a、时间可以通过 线程优化
b、内存占用问题,我们可以不需要将mysql 数据库的某个字段信息加入到列表中,而是直接读取数据库
4、可以增加日志操作。哪些数据更新,哪些数据没有更新都可以记录日志
本人菜鸟一个,希望大佬看到可以指出相应问题,如能给出问题的优化方案更好! 万分感谢!