Python调用sqluldr2从Oracle导出指定表数据

沈知远

发布时间：2026-10-13| 3人浏览过

原创

本文介绍如何使用Python脚本连接Oracle数据库，并根据配置文件读取表名列表，批量调用sqluldr2工具将指定表的数据导出为独立文件。文章包含数据库配置、表名列表管理及完整的Python代码实现，展示了从读取配置到执行导出命令的全过程及日志分析。

Python连接Oracle导出数据概述

本文演示如何利用Python脚本连接Oracle数据库，并依据table_list文件中指定的表名列表，将对应数据批量导出为独立的数据文件。

Python主脚本实现

主执行脚本如下所示：

完整Python代码


import os
import logging
import sys
import configparser
import subprocess
import cx_Oracle

#判断输入参数个数
class param():
  def check_para(self):
    if len(sys.argv) != 1:
       print("请输入正确的参数：yyyymmdd")
       exit(1)
    else:
      print("继续执行")

#根据配置文件获取登录信息
class get_dbini():
  def get_db(self):
    config=configparser.ConfigParser()
    filepath="db.ini"
    if os.path.exists(filepath):
      config.read_file(open(filepath))
      dbinfo=[config.get("db_oracle","username"),\
          config.get("db_oracle","password"),\
          config.get("db_oracle","ip"),\
          config.get("db_oracle","dbsid")]
    else:
      loginfo.info("没有那个配置文件")
      sys.exit(4)
    #声明使用全局变量
    global username,password,ip,dbsid
    username=dbinfo[0]
    password=dbinfo[1]
    ip=dbinfo[2]
    dbsid=dbinfo[3]
    loginfo.info(username+password+ip+dbsid)
          
#导出表数据
class exp_date():
  def exp_table(self):
   with open('table_list','r') as f:
    list = f.readlines()
   for i in list:
    tablename = i.rstrip('\n')
    exportquery='sqluldr2 user='+username+'/'+password+'@'+ip+':1521/'+dbsid+' query="select * from '+tablename+';" head=no file='+tablename+'.dat field=0x03 record=0x030x0a safe=yes'
    loginfo.info("开始导出数据: exportquery= "+exportquery)
    flag= subprocess.check_call(exportquery,shell=True)
    loginfo.info(flag)
    
#打印日志
class log_set():
  def logger_set(self):
   logger=logging.getLogger('mylogger')
   logger.setLevel(logging.DEBUG)
   
   fh=logging.FileHandler('a.log','w')
   fh.setLevel(logging.INFO)
   
   ch=logging.StreamHandler()
   ch.setLevel(logging.ERROR)

   formatter = logging.Formatter('%(asctime)s -%(name)s -%(levelname)s - %(message)s')
   
   fh.setFormatter(formatter)
   ch.setFormatter(formatter)
   
   logger.addHandler(fh)
   logger.addHandler(ch)
   return logger
if __name__=='__main__':
  loginfo=log_set().logger_set()
  param().check_para()
  get_dbini().get_db()
  exp_date().exp_table()

数据库配置文件配置

数据库连接配置文件内容如下：

配置文件名为db.ini。

[db_oracle] username=c##scott password=tiger ip=192.168.1.250 dbsid=orcl

表名列表配置

指定要导出的表名列表配置文件如下：

文件名为table_list。

BONUS DEPT EMP LEAD_TABLE SALGRADE T1 TB_USER TEST XGJ XGJ_2

导出运行结果与日志分析

脚本运行后的终端输出结果如下：

[oracle@master2 tmp]$ python3 c.py 继续执行 0 rows exported at 2019-01-22 17:51:51, size 0 MB. output file BONUS.dat closed at 0 rows, size 0 MB. 0 rows exported at 2019-01-22 17:51:52, size 0 MB. 4 rows exported at 2019-01-22 17:51:52, size 0 MB. output file DEPT.dat closed at 4 rows, size 0 MB. 0 rows exported at 2019-01-22 17:51:52, size 0 MB. 12 rows exported at 2019-01-22 17:51:52, size 0 MB. output file EMP.dat closed at 12 rows, size 0 MB. 0 rows exported at 2019-01-22 17:51:52, size 0 MB. 10 rows exported at 2019-01-22 17:51:52, size 0 MB. output file LEAD_TABLE.dat closed at 10 rows, size 0 MB. 0 rows exported at 2019-01-22 17:51:52, size 0 MB. 5 rows exported at 2019-01-22 17:51:52, size 0 MB. output file SALGRADE.dat closed at 5 rows, size 0 MB. 0 rows exported at 2019-01-22 17:51:52, size 0 MB. 5 rows exported at 2019-01-22 17:51:52, size 0 MB. output file T1.dat closed at 5 rows, size 0 MB. 0 rows exported at 2019-01-22 17:51:52, size 0 MB. 10 rows exported at 2019-01-22 17:51:52, size 0 MB. output file TB_USER.dat closed at 10 rows, size 0 MB. 0 rows exported at 2019-01-22 17:51:52, size 0 MB. 8 rows exported at 2019-01-22 17:51:52, size 0 MB. output file TEST.dat closed at 8 rows, size 0 MB. 0 rows exported at 2019-01-22 17:51:52, size 0 MB. 9 rows exported at 2019-01-22 17:51:52, size 0 MB. output file XGJ.dat closed at 9 rows, size 0 MB. 0 rows exported at 2019-01-22 17:51:52, size 0 MB. 8 rows exported at 2019-01-22 17:51:52, size 0 MB. output file XGJ_2.dat closed at 8 rows, size 0 MB.

查看程序运行生成的日志文件a.log内容如下：

[oracle@master2 tmp]$ more a.log 2019-01-22 17:51:51,858 -mylogger -INFO - c##scotttiger192.168.1.250orcl 2019-01-22 17:51:51,858 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from BONUS;" head=no file=BON US.dat field=0x03 record=0x030x0a safe=yes 2019-01-22 17:51:51,949 -mylogger -INFO - 0 2019-01-22 17:51:51,949 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from DEPT;" head=no file=DEPT .dat field=0x03 record=0x030x0a safe=yes 2019-01-22 17:51:52,038 -mylogger -INFO - 0 2019-01-22 17:51:52,038 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from EMP;" head=no file=EMP.d at field=0x03 record=0x030x0a safe=yes 2019-01-22 17:51:52,129 -mylogger -INFO - 0 2019-01-22 17:51:52,129 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from LEAD_TABLE;" head=no fil e=LEAD_TABLE.dat field=0x03 record=0x030x0a safe=yes 2019-01-22 17:51:52,299 -mylogger -INFO - 0 2019-01-22 17:51:52,300 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from SALGRADE;" head=no file= SALGRADE.dat field=0x03 record=0x030x0a safe=yes 2019-01-22 17:51:52,401 -mylogger -INFO - 0 2019-01-22 17:51:52,402 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from T1;" head=no file=T1.dat field=0x03 record=0x030x0a safe=yes 2019-01-22 17:51:52,490 -mylogger -INFO - 0 2019-01-22 17:51:52,490 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from TB_USER;" head=no file=T B_USER.dat field=0x03 record=0x030x0a safe=yes 2019-01-22 17:51:52,578 -mylogger -INFO - 0 2019-01-22 17:51:52,578 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from TEST;" head=no file=TEST .dat field=0x03 record=0x030x0a safe=yes 2019-01-22 17:51:52,665 -mylogger -INFO - 0 2019-01-22 17:51:52,665 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from XGJ;" head=no file=XGJ.d at field=0x03 record=0x030x0a safe=yes 2019-01-22 17:51:52,771 -mylogger -INFO - 0 2019-01-22 17:51:52,771 -mylogger -INFO - 开始导出数据: exportquery= sqluldr2 user=c##scott/tiger@192.168.1.250:1521/orcl query="select * from XGJ_2;" head=no file=XGJ _2.dat field=0x03 record=0x030x0a safe=yes 2019-01-22 17:51:52,856 -mylogger -INFO - 0

以上即为通过Python连接Oracle并导出数据文件的完整流程与代码示例，希望能为相关技术学习提供参考。