用python导入文本和Oracle数据到MySQL
用python导入文本和Oracle数据到MySQL
目录
一、了解数据
二、文本数据导入到MySQL
三、Oracle数据导入到MySQL
三、总结
【工具】
Anaconda+Python 3.6+jupyterlab
MySQL
Oracle 10
【注】
本文假设你已安装好MySQL数据库
本文背景：项目中有一个数据库，存放了历史的用户订购数据，原数据库为Oracle,感觉性能不好，为了更好配合后续的数据自动化导入和python分析及报表生成，把原来oracle数据迁移到mysql数据库，文中重点介绍方法，数据源均为测试源，读者可以自行修改为自己的数据。
一、了解数据
要处理的数据格式为文本文件，以”|“分割，字段为（各字段含义，百度IPTV c3规范）：
样例：
userid001|20171231123820|20171231123820|20180130123820|2|28元包月订购|productIDa123|800|5|疯狂的外星人|mediacode001|
userid002|20171231145639|20171231145639|20180130145639|2|6元整部订购|productIDa456|600|5|流浪地球|mediacode002|
userid003|20171231152518|20171231152518|20180130152518|2|6元整部订购|productIDa123|600|5|海王|mediacode003|
userid004|20171231184705|20171231184705|20180130184705|2|6元整部订购|productIDa123|600|5|西虹市首富|mediacode004|
userid005|20171231194250|20171231194250|20180130194250|2|28元包月订购|productIDa456|800|5|红海行动|mediacode005|
userid006|20171231215439|20171231215439|20180130215439|2|28元包月订购|productIDa456|500|5|龙猫|mediacode006|
userid007|20171231215219|20171231215219|20180130215219|2|6元整部订购|productIDa123|600|5|战狼|mediacode007|
二、文本文件导入数据库
1.建表：在MySQL数据库中建立orderlog_demo的表，字段参考上文，方法略去
2.导入数据
表已经创建成功，第一部分从orderlog_demo.txt中导入数据到MySQL
a.导入需要使用的库，oracle使用的也提前导入
#导入需要使用到的库
from sqlalchemy import create_engine #连接mysql使用
import pandas as pdfrom sqlalchemy.types
import Integer,NVARCHAR,Float
import cx_Oracle #连接oracle使用
import datetime
import time
b.#设置读取文件的表头名称
col_names = ['USERID','PURCHASETIME','VALIDTIME','EXPIREDTIME','PRODUCTTYPE','PRODUCTNAME','PRODUCTID','PRICE','ORDERTYPE','CONTENTNAME','MEDIACODE','Info']
c.从orderlog导入数据到df
#从orderlog导入数据到df df = pd.read_csv('./res/Orderlog_demo.txt',sep='|',dtype = 'str',encoding = 'GB2312',names=col_names)
d.#较验读取的文件长度，检查文件是否读取完整
#较验读取的文件是否完成 print(len(df))
e.设置mysql连接引擎
#设置mysql连接引擎 engine = create_engine('mysql+pymysql://orderlog:password@192.168.1.99:3306/orderlog?charset=utf8')
f.df文本格式和数据库文本格式转换函数
#df文本格式和数据库文本格式转换函数 def mapping_df_types(df): dtypedict = {} for i, j in zip(df.columns, df.dtypes): print(i,j) if "object" in str(j): dtypedict.update({i: NVARCHAR(length=255)}) #print("True") if "float" in str(j): dtypedict.update({i: Float(precision=2, asdecimal=True)}) if "int" in str(j): dtypedict.update({i: Integer()}) return dtypedict
g.把df列格式转换成数据库格式
#把df列格式转换成数据库格式 dtypedict = mapping_df_types(df)
h.df保存到mysql，并打印执行时间
#df保存到mysql，并打印执行时间 starttime = datetime.datetime.now() df.to_sql('orderlog_demo',engine,dtype=dtypedict,index=False,if_exists='append') endtime = datetime.datetime.now() print((endtime - starttime).seconds)
i.较验MySQL库导入是否成功,文本文件导入MySQL完成
三、Oracle数据导入MySQL
获取Oracle数据
1.设置oracle连接参数
#以下部分为从oracle数据库中读取数据，并导入到mysql中#设置oracle连接参数 dsn=cx_Oracle.makedsn("192.168.254.166",1521,"orcl")#ip，端口，库名 conn=cx_Oracle.connect("system","password",dsn)
#设置要导入数据，为防止执行时间过长，分月导入 month_lst=(['201901%','201902%'])print(len(month_lst))
2.按月导入数据到MYSQL,并打印执行时间
#按月导入数据到MYSQL for month in month_lst: starttime = datetime.datetime.now() #从oracle数据库里查询对应月份数据，保存到df中 sqlcmd="select * from orderlog where purchasetime like " +'\''+ month+'\'' df=pd.read_sql(sqlcmd,conn) #df保存到mysql df.to_sql('orderlog_demo',engine,dtype=dtypedict,index=False,if_exists='append') endtime = datetime.datetime.now() print("导入"+month[:-1]+"耗时"+str((endtime - starttime).seconds)+"秒") time.sleep(10) #休息10s 个人调试加入，生产可以不需要print("All Finished")
3.最后较验源数据库和目标数据库数据是否正确（以201902为例）a.源库
b.目标库
至此文本文件导入和Oracle数据导入完成
四、总结
本文介绍了数据库系统的优势，如何用Python连接数据库并导入文本数据和Oracle数据库数据。
TIPS:
写demo的过程中遇到过两个问题，也写在总结里供参考：
-
文本文件读取时的字符集，本文为GB2312,python默认为utf-8,本文未做特殊处理，本人也是遇到问题后，百度的。
-
Oracle连接时遇到”Cannot locate a 64-bit Oracle Client library“ \xxx\bin\oci.dll找不到的问题，是因为缺少64位版本，百度后解决。
-
从Oracle数据库导入时，如果数据量很大最好分月或分天导入，这样程度才不会因为一次执行时间很长卡死。
遇到问题，百度一下
