【database】数据从oracle导入mysql

 

# coding=utf-8
# author:Yan
import cx_Oracle
import pymysql
import os
import time


# 说明：本脚本用于Oracle与MySQL之间的数据迁移
# 注意：源表与目标表字段数量必须一致
# 使用：脚本默认是从MySQL迁移到Oracle,如果想从Oracle迁移到MySQL,修改from_conn,to_conn,from_conn_type,to_conn_type就行
# 此例子是oracle to mysql


# 设置字符集为UTF8，防止中文乱码
os.environ["NLS_LANG"] = "SIMPLIFIED CHINESE_CHINA.UTF8"
from_conn = cx_Oracle.connect("user/passwd@host:1521/orcl", encoding="utf8")
from_cursor = from_conn.cursor()
from_cursor.arraysize = 1000

to_conn = pymysql.connect(user="xxx", passwd="xx", host="host",
                          db="xxx", port=3306, charset="utf8")
to_cursor = to_conn.cursor()
to_cursor.arraysize = 1000

conn_type = "mysql"
column_length = COL_COUNT  # 列数

# MySQL批量插入语法是 insert into tb_name values(%s,%s,%s)
# Oracle批量插入语法是 insert into tb_name values(:1,:2,:3)
val_str = ",".join(["%s" for i in range(column_length)])
if conn_type == "oracle":
    val_str = ",".join([":" + str(i) for i in range(1, column_length + 1)])
insert_sql = "insert into EEDB_PATENT_PB_1 values(" + val_str + ")"

select_sql = """
SELECT COL1,COL2,... from tableName
"""
from_cursor.execute(select_sql)

print("开始执行时间:", time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
# 每次获取500行，由from_cursor.arraysize值决定，MySQL fetchmany 返回的是 tuple 数据类型 所以用list做类型转换
# 批量插入每次500行，需要注意的是 rows 必须是 list [] 数据类型
while True:
    rows = from_cursor.fetchmany(1000)
    to_cursor.executemany(insert_sql, rows)
    to_conn.commit()
    if not rows:
        break
    print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), "：插入1000条......")

from_cursor.close()
to_cursor.close()
from_conn.close()
to_conn.close()
print("执行完成时间:", time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))

测试之后：

不存在CLOB字段的情况下速度很快，3~4百w的数据几分钟导入；

如果存在CLOB字段，且字段很多的情况下速度比较慢；

待优化......