python+SQL loader批量导入csv文件到Oracle（可新建表或追加数据）

最新推荐文章于 2024-01-31 16:39:57 发布

w.ang.jie

最新推荐文章于 2024-01-31 16:39:57 发布

阅读量2k

点赞数 4

分类专栏：数据库 oracle 文章标签： 1024程序员节数据库 oracle python sql

本文链接：https://blog.csdn.net/qq_32392597/article/details/109254630

版权

oracle 同时被 2 个专栏收录

97 篇文章 2 订阅

订阅专栏

数据库

43 篇文章 2 订阅

订阅专栏

一、前言

根据项目开发需要，需要将某个文件夹下excel表中数据批量导入到Oracle数据库，数据有几百万条，字段约三百个，原始数据文件有几个G。

一开始打算用python写一个脚本，用pandas读取数据后形成批量insert语句，再执行SQL将数据插入到数据库表，测试发现数据导入太慢，一直卡住，分析发现频繁读写数据库对数据库压力较大，极不稳定。

后来改为批量形成SQL Loader导入语句进行导入后，速度快很多。

引用《最大化SQL * Loader性能》中的介绍：

传统的路径加载器实质上是通过使用标准插入语句来加载数据的， SQL Loader使用直接路径加载器（direct = true）直接加载到Oracle数据文件中，并以Oracle数据库块格式创建块，使整个过程对数据库的负担减少了很多。

二、SQL*Loader使用

参考《SQL*Loader 详解》，有详细介绍SQL*Loader的使用。

简单示例

这里介绍下使用SQL*Loader导入csv文件的过程：

将要导入的Excel数据文件转为csv文件（不需要标题）

新建控制文件 imp.ctl ，内容如下：

load data

infile 'e:\sql\fsfp.csv' //csv文件位置

append  into table test //向表test中追加记录

fields terminated by ',' //csv文件字段分隔符为','

(NAME,TAX,ENO,CODE,TOTAL,TAXATION,URL,TIMES) //csv文件数据一一对应数据表中的字段，

在DOS窗口下使用SQL*Loader命令实现数据的输入：
```
sqlldr userid=用户名/密码@orcl  control=ctl文件位置 log=日志文件位置（可有可无）
```
默认日志文件名为：imp.log
默认坏记录文件为：imp.bad

三、业务代码详解

3.1 建表

3.1.1 获取文件所在路径

# 获取文件路径
filePath = input('请输入文件所在路径：')
# 跳转到该目录
os.chdir(r'%s' % (filePath))

3.1.2 读取csv文件表头

class ImportOracleCsv(ImportOracle):
    def inoracle(self):
        with open(self.file_name, 'r') as f:
            reader = csv.reader(f)
            for contents in reader:
                    # 获取第一行为列名
                    while '' in contents:
                        contents.remove('')
                    if '' in contents:
                        print(contents)
                        title = contents.remove('')  # 去除空列名
                        print(title)
                    else:
                        title = contents
                    return title

这里因为业务数据中存在首列列名为空 的现象，所以我加了一段判断语句，去除~~空列名~~ 。

3.1.3 生成建表语句

m =0 
title = self.title
while m < len(title):
    if len(title[m]) > 15 :
        title[m] = title[m][0:15]
        m = m+1
    else:
        m = m+1
#给字符数据加上引号
fields = ['\"'+i+'\"'+' varchar2(500)' for i in title]
fields_str = ', '.join(fields)
# 生成建表语句
sql = 'create table %s (%s)' % (self.table_name, fields_str)

业务数据中存在列名长度超长的问题，如果不处理的话，会在建表时报字段长度超出限制的错误；
加入判断语句进行字段截取，限制列名长度在15以内，保证业务需求的同时不影响建表语句的创建和运行。

3.1.4 运行sql

#创建连接
conn = cx_Oracle.connect("c##QUANLIAN", "password", "localhost/orcl")
cursor = conn.cursor()
#执行SQL
cursor.execute(sql)
conn.commit()
#全部提交后关闭连接，释放游标
cursor.close()
conn.close()

3.2 数据导入

根据之前了解到的，SQL loader执行数据导入需要 ctl配置文件

3.2.1 ctl文件自动生成

ctlcontents 主要替换三个地方：

需导入csv文件的位置 ---- file_path
导入的Oracle表名 ---- self.table_name
导入的csv文件的列名和对应的Oracle表列名 ---- contenstr

for root, dirs, files in os.walk(r'%s' % (filePath), topdown=False):
 	if '.csv' in file_name:
  		file_path = os.path.join(root, file_name)
	    i = 0
		while i <= len(files)-1 :
			file_name = files[i]
			m =0 
			title = self.title
			while m < len(title):
			    if len(title[m]) > 15 :
			        title[m] = title[m][0:15]
			        m = m+1
			    else:
			        m = m+1
			strcontent = ['\"'+i+'\"'+' char(500)' for i in title]
			contentstr = ','.join(strcontent)
	  ctlcontents = ('OPTIONS (SKIP=1)  \n'
	                  'LOAD DATA \n'
	                  'INFILE \'' + file_path+'\'\n'
	                  'append \n'
	                  'INTO TABLE '+self.table_name + ' \n'
	                  'FIELDS TERMINATED BY \',\' \n'
	                  'OPTIONALLY ENCLOSED BY \'"\' \n'
	                  'trailing nullcols \n'
	                  '(   \n'
	                  +
	                  contentstr
	                  +
	                  '  )'
	      )

这里每个字段都加上了 char(500) 的设置是因为：
若不加，SQL loader默认设置为char(200)，即字段长度限制在200以内，超长将报错；
而实际业务数据确实存在超长的问题，所以必须声明为 char(500);

3.2.2 SQL loader + ctl 配置文件执行导入

根据SQL loader语法，这里只需配置好ctl文件所在路径即可，即
ctl = (" sqlldr "+"c##QUANLIAN/password@orcl "+"control=\'" + new_name+"\' direct=true parallel=true ")
的 new_name 。

#创建脚本文件
file_name = file_name.replace('.csv', '')
new_name = root+'\\'+file_name+'.ctl'
new_fo = open(new_name, 'w+', encoding='gbk')
for ctlcontent in ctlcontents:
   new_fo.write(ctlcontent)
new_fo.close()
ctl = (" sqlldr "+"c##QUANLIAN/password@orcl "+"control=\'" +
   new_name+"\' direct=true   parallel=true   ")
print(ctl)
print('开始导入文件  '+str)
print('正在导入。。。')
ctlresults = os.popen(ctl, "r")
print(str + ' 文件已经导入完成')
ctlresults = ctlresults.read()  # 读文件
print(ctlresults)

四、执行结果

执行python脚本，运行结果如下：

在这里插入图片描述

执行后路径下会有执行过程中产生的ctl文件和log文件

五、完整代码

# encoding=utf-8
import cx_Oracle
import csv
import os
import re
from pymysql import NULL
import os
import sys

class ImportOracle(object):
    def inoracle(self):
        pass

    def ConnOracle(self):
        # 连接数据库
        conn = cx_Oracle.connect("c##QUANLIAN", "password", "localhost/orcl")
        cursor = conn.cursor()
        try:
            # 给字符数据加上引号
            m =0 
            title = self.title
            while m < len(title):
                if len(title[m]) > 15 :
                    title[m] = title[m][0:15]
                    m = m+1
                else:
                    m = m+1
            fields = ['\"'+i+'\"'+' varchar2(500)' for i in title]
            fields_str = ', '.join(fields)
            # 生成建表语句
            sql = 'create table %s (%s)' % (self.table_name, fields_str)
            print(sql)
            cursor.execute(sql)

            conn.commit()
            print('表格'+self.table_name+' 已创建')
        except:
            print('表格'+self.table_name+' 已存在')

        # 全部提交后关闭连接，释放游标
        cursor.close()
        conn.close()


class ImportOracleCsv(ImportOracle):
    def inoracle(self):
        # os.chdir('')
        with open(self.file_name, 'r') as f:
            reader = csv.reader(f)
            # 将科学计数现实的数字显示
            for contents in reader:
                    # 获取第一行为列名
                    while '' in contents:
                        contents.remove('')
                    if '' in contents:
                        print(contents)
                        title = contents.remove('')  # 去除空列名
                        print(title)
                    else:
                        title = contents
                  
            # 获取数据，去掉第一行
            # data = contents[1:]
                    return title



class ImportError(ImportOracle):
    def inoracle(self):
        print('Undefine file type')
        return 0


class ChooseFactory(object):
    choose = {}
    choose['csv'] = ImportOracleCsv()

    def choosefile(self, ch):
        if ch in self.choose:
            op = self.choose[ch]
        else:
            op = ImportError()
        return op


class CreateContents:
    def CtlContents(self):
        # 创建导入语句
        for root, dirs, files in os.walk(r'%s' % (filePath), topdown=False):
            i = 0
            while i <= len(files)-1 :
            # for file_name in files:
                file_name = files[i]
                m =0 
                title = self.title
                while m < len(title):
                    if len(title[m]) > 15 :
                        title[m] = title[m][0:15]
                        m = m+1
                    else:
                        m = m+1
                strcontent = ['\"'+i+'\"'+' char(500)' for i in title]
                contentstr = ','.join(strcontent)
                if '.csv' in file_name:
                    str = os.path.join(root, file_name)
                    ctlcontents = ('OPTIONS (SKIP=1)  \n'
                                    'LOAD DATA \n'
                                    'INFILE \'' + str+'\'\n'
                                    'append \n'
                                    'INTO TABLE '+self.table_name + ' \n'
                                    'FIELDS TERMINATED BY \',\' \n'
                                    'OPTIONALLY ENCLOSED BY \'"\' \n'
                                    'trailing nullcols \n'
                                    '(   \n'
                                    +
                                    contentstr
                                    +
                                    '  )'
                        )

                    # 创建脚本文件
                    file_name = file_name.replace('.csv', '')
                    new_name = root+'\\'+file_name+'.ctl'
                    new_fo = open(new_name, 'w+', encoding='gbk')
                    for ctlcontent in ctlcontents:
                        new_fo.write(ctlcontent)
                    new_fo.close()
                    ctl = (" sqlldr "+"c##QUANLIAN/password@orcl "+"control=\'" +
                        new_name+"\' direct=true   parallel=true   ")
                    print(ctl)
                    print('开始导入文件  '+str)
                    print('正在导入。。。')
                    ctlresults = os.popen(ctl, "r")
                    print(str + ' 文件已经导入完成')
                    ctlresults = ctlresults.read()  # 读文件
                    print(ctlresults)
                i = i+1


   
if __name__ == "__main__":

    while True:
 
        # 输入文件路径
        filePath = input('请输入文件所在路径：')
        if filePath == 'exit' :
            break 
        
        # 获取输入表名称
        table_name = input('请输入表格名称：')
        if  table_name == 'exit':
            break
       
        i=0

        #跳转到该目录
        os.chdir(r'%s' % (filePath))

        for root, dirs, files in os.walk(r'%s' % (filePath), topdown=False):
            for file_name in files:
                if '.csv' in file_name:
                    os.chdir(root);  
                    op = file_name.split('.')[-1]
                    factory = ChooseFactory()
                    cal = factory.choosefile(op)
                    cal.file_name = file_name

                    # 建表
                    imp = ImportOracle()
                    ics = ImportOracleCsv()
                    ics.file_name  = file_name
                    ics.title = ics.inoracle()
                    imp.title = ics.title
                    imp.table_name = table_name
                    imp.ConnOracle()

                    # 修改当前工作目录
                    os.chdir( filePath )

                    # 导入
                    contents = CreateContents()
                    contents.file_name = file_name
                    contents.file_path = filePath
                    contents.title = ics.title
                    contents.table_name = table_name
                    contents.CtlContents()

                    x=0
                    i=i+1

                if (i == 1):
                    print('Done!')
                    break
            if (i ==1):
                print('完成！')
                break

w.ang.jie

关注

4
点赞
踩
24

收藏

觉得还不错? 一键收藏
0
评论
python+SQL loader批量导入csv文件到Oracle（可新建表或追加数据）

一、前言根据项目开发需要，需要将某个文件夹下excel表中数据批量导入到Oracle数据库，数据有几百万条，字段约三百个，原始数据文件有几个G。一开始打算用python写一个脚本，用pandas读取数据后形成批量insert语句，再执行SQL将数据插入到数据库表，测试发现数据导入太慢，一直卡住，分析发现频繁读写数据库对数据库压力较大，极不稳定。后来改为批量形成SQL Loader导入语句进行导入后，速度快很多。引用《最大化SQL * Loader性能》中的介绍：传统的路径加载器实质上是通过使用标
复制链接

扫一扫