python实现数据割接(把oracle中数据转到mysql中)
公司需要把oracle中的一些老的数据库中数据割接到MySQL中,写的一个脚本。
- **思路:**利用python先将oracle中数据到处到excel中;然后再将excel中数据导入到MySQL
- **需用到:**pymysql(python操作mysql) openpyxl(python操作excel) cx_Oracle(python操作oracle)
- 代码:
oracle导出excel:
import cx_Oracle
import openpyxl
import time
import pymysql
def export_excel(sql, fileName):
# 插入字段名到第一行
ws.cell(1, 1, value="cust_id")
for c in range(1, len(title)):
ws.cell(row=1, column=c + 1, value=title[c - 1])
# 写入查询数据
for r in range(len(rows)):
for c in range(1, len(rows[r])):
if rows[r][c]: # 值不为空时写入,空值不写入
ws.cell(r + 2, c + 1, value=str(rows[r][c - 1])) # str()防止用科学计数法写入造成信息丢失
#从mysql中取出的序列加入到excel作为新表的id
excel_val()
wb.save(fileName)
wb.close()
curs.close()
def excel_val():
# 从mysql中取出的序列加入到excel作为新表的id
conn = pymysql.Connection(host="136.25.62.167", user='zqxsc_test', password='Zqxsc_test@123', database='ZQBCCDB',
port=8919, charset="utf8")
cour = conn.cursor()
#取出序列
sql = ("select SEQ_CUST_INFO.nextval")
cour.execute(sql)
for r in range(len(rows)):
result = cour.fetchone()
for i in result:
ws.cell(r + 2, 1, value=str(i)) # str()防止用科学计数法写入造成信息丢失
print(i)
conn.commit()
cour.close()
conn.close()
if __name__ == '__main__':
conn = cx_Oracle.connect('uap/Abcd#1234@136.6.142.2:1521/crmdb', encoding='utf-8') # utf-8显示中文
curs = conn.cursor()
# 打开sql文件获取sql语句
with open('text.sql', "r", encoding="utf-8") as sql_0:
sql = sql_0.read()
rr = curs.execute(sql)
rows = curs.fetchall()
# 获取字段名
title = [i[0] for i in curs.description]
# 创建excel表
wb = openpyxl.Workbook()
ws = wb.active
cur_date = time.strftime("%Y-%m-%d-%H%M%S", time.localtime())
export_excel(sql, cur_date + '.xlsx')
conn.close()
print("导出成功")
将excel导入到mysql中:
import pymysql
from openpyxl import load_workbook
filename = '营业执照busi_code.xlsx'
workbook = load_workbook(filename, read_only=False, data_only=True)
ws = workbook.active
def import_txt():
data = []
for row in ws.iter_rows(min_row=0, max_row=1, values_only=True):
data.append(row)
print(data[0])
f1 = open('营业执照busi_code.txt', 'w+', encoding='utf8')
j = 1
f1.write("INSERT INTO cust_certi(")
for i in data[0]:
print(i)
f1.write(i)
if j < len(data[0]):
f1.write(",")
j = j + 1
f1.write(") values(")
k = 1
for i in data[0]:
f1.write("%s")
if k < len(data[0]):
f1.write(",")
k = k + 1
f1.write(")")
print(len(data[0]))
print(k)
f1.close()
def sql_insert():
data=[]
comlon=ws.max_row
print("cou:",comlon)
for row in ws.iter_rows(min_row=2, max_row=comlon, values_only=True):
print(row)
data.append(row)
conn = pymysql.Connection(host="136.25.62.167", user='zqxsc_test', password='Zqxsc_test@123', database='ZQBCCDB',
port=8919, charset="utf8")
cour = conn.cursor()
with open('营业执照busi_code.txt', 'r') as f:
sql_str = f.read()
print(sql_str)
cour.executemany(sql_str, data)
conn.commit()
cour.close()
conn.close()
print("插入成功")
if __name__ == '__main__':
import_txt()
sql_insert()
最后:其实代码还可以优化,比如新表中合并excel组成新的表等等
先这样吧