环境
- python3.7
- Oracle12.2
- C盘下已有一个ctl的示例文档
- 文件夹下有多个超大csv文件需要录入
背景
oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的特点。
比如说exp和imp可以对数据库中的数据进行导出和导出的工作,是一种很好的数据库备份和恢复的工具,因此主要用在数据库的热备份和恢复方面。
有着速度快,使用简单,快捷的优点;同时也有一些缺点,比如在不同版本数据库之间的导出、导入的过程之中,总会出现这样或者那样的问题,这个也许是oracle公司自己产品的兼容性的问题吧。
sql loader 工具却没有这方面的问题,它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中,是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢,另外对blob等类型的数据就有点麻烦了。
sqlldr用法
sql loader使用例子
a)SQLLoader将 Excel 数据导出到 Oracle
- 创建SQL*Loader输入数据所需要的文件,均保存到C:\,用记事本编辑:
控制文件:input.ctl,内容如下:
load data --1、控制文件标识
infile 'test.txt' --2、要输入的数据文件名为test.txt
append into table test --3、向表test中追加记录
fields terminated by X'09' --4、字段终止于X'09',是一个制表符(TAB)
(id,username,password,sj) -----定义列对应顺序
a、insert,为缺省方式,在数据装载开始时要求表为空
b、append,在表中追加新记录
c、replace,删除旧记录,替换成新装载的记录
d、truncate,同上
在DOS窗口下使用SQL*Loader命令实现数据的输入
C:\>sqlldr userid=system/manager control=input.ctl
默认日志文件名为:input.log
默认坏记录文件为:input.bad
具体可参照官网链接《SQL*Loader Command-Line Reference》
和这篇文章《sqlldr用法》
ctl示例文档
目的:
将 ‘C:\1.csv’ 文件录入到Oracle的TC1表,由于表中有特殊字符和空值,所以要用OPTIONALLY ENCLOSED BY '"' -和
trailing nullcols` ,保证字段空值可以录入;跳过不需要的第一行和第一列,如果字段中有长度超过200的字符,需声明。
代码:
OPTIONS(SKIP=1) --跳过第一行
LOAD DATA
INFILE 'C:\1.csv' --录入文档所在位置
replace
--APPEND 原先的表有数据 就加在后面
--INSERT 装载空表 如果原先的表有数据 sqlloader会停止 默认值
--REPLACE 原先的表有数据 原先的数据会全部删除
--TRUNCATE 指定的内容和replace的相同 会用truncate语句删除现存数据
INTO TABLE TC1 --所要录入的表
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' -- 数据中每个字段用 '"' 框起
trailing nullcols -----保证空值可以录入
(
"id" filler,-- 跳过文件第一列不录入
"ziduan1" ,
"ziduan2" ,
"ziduan3" ,
"ziduan4" ,
"ziduan5" char(500) -- 长度超过200的字符需声明,ctl文档默认输入字符长度为200;不声明录入会报错
)
根据示例文档生成多个ctl文件
目的:
- 读取测试ctl文档内容,替换脚本中的
INFILE 'C:\1.csv'
(即录入文档所在位置)为我们指定的excel文件所在位置; - 再将替换后的ctl脚本分别按照读取的excel文件名存储到指定文件夹下;
- 输出根据ctl文件名确定的 sqlldr脚本代码,便于批量执行。
代码:
# encoding: gbk
import os
import re
fo = open(r'C:\test.ctl', 'r+',encoding='UTF-8')
filePath = 'C:\\' #生成ctl文件所在路径
name = os.listdir(filePath)
results = []
link=fo.readlines()
for i in name:
#生成批量执行sqlldr代码
print("sqlldr "+"user/password@orcl "+"\"control="+i.replace(".csv","")+".ctl\""+" direct=true parallel=true ")
for fileLine in link:
#利用正则替换掉test.ctl 中infile的文件名
a=re.sub(r'(?= \\)(.*?).csv',i,fileLine)
results.append(a)
# print(a)
fo.close()
#获取的新数据写入新文件中
new_name='C:\\'+i.replace(".csv","")+'.ctl'
new_fo = open(new_name, 'w',encoding='UTF-8')
for str in results:
new_fo.write(str)
new_fo.close()
执行后生成脚本
复制后直接执行即可
注意
- 注意文档编码格式,encoding报错时,修改编码格式:'UTF-8’或‘GBK’;
- 注意文档所在路径,特别是执行sqlldr 脚本时,我这里写的是相对路径,如有需要,可将 control 后的文件路径改为绝对路径;或者将执行路径放到文件所在位置;