因要对客户发送一些推广邮件,需要把email导出到csv,再使用专门邮件发送软件进行发送。
在使用ORACLE 的 utl_file 包,把数据写到文件中。
看到是逐条数据写入文件。速度比较慢。所以想到python来进行处理。写完后,速度有所改观。
在写成BOLG时,SQL 代码有所修改。
以下是两种方法。
一、安装:cx_Oracle-5.1.3
---------------------------------------------------------------
1 下载地址:
/pypi/cx_Oracle/5.1.3
2 解压:
[oracle@dg1 backup]$ tar -xvf cx_Oracle-5.1.3.tar.gz
3.编译并安装。
[oracle@dg1 cx_Oracle-5.1.3]$ python setup.py build
running build
running build_ext
building 'cx_Oracle' extension
creating build
creating build/temp.linux-x86_64-2.4-11g
gcc -pthread -fno-strict-aliasing -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fPIC -I/opt/app/oracle/product/11.2.0/rdbms/demo -I/opt/app/oracle/product/11.2.0/rdbms/public -I/usr/include/python2.4 -c cx_Oracle.c -o build/temp.linux-x86_64-2.4-11g/cx_Oracle.o -DBUILD_VERSION=5.1.3
creating build/lib.linux-x86_64-2.4-11g
gcc -pthread -shared build/temp.linux-x86_64-2.4-11g/cx_Oracle.o -L/opt/app/oracle/product/11.2.0/lib -lclntsh -o build/lib.linux-x86_64-2.4-11g/cx_Oracle.so
[oracle@dg1 cx_Oracle-5.1.3]$ python setup.py install
running install
running build
running build_ext
running install_lib
copying build/lib.linux-x86_64-2.4-11g/cx_Oracle.so -> /usr/lib64/python2.4/site-packages
error: /usr/lib64/python2.4/site-packages/cx_Oracle.so: Permission denied
4.没有权限,换到ROOT用户再试。
[root@dg1 cx_Oracle-5.1.3]# python setup.py build
Traceback (most recent call last):
File "setup.py", line 135, in ?
raise DistutilsSetupError("cannot locate an Oracle software " \
distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation
5.添加ORACLE 目录 设置后,再试
[root@dg1 cx_Oracle-5.1.3]# ORACLE_HOME=/opt/app/oracle/product/11.2.0; export ORACLE_HOME
[root@dg1 cx_Oracle-5.1.3]# echo $ORACLE_HOME
/opt/app/oracle/product/11.2.0
[root@dg1 cx_Oracle-5.1.3]# python setup.py build
running build
running build_ext
[root@dg1 cx_Oracle-5.1.3]# python setup.py install
running install
running build
running build_ext
running install_lib
copying build/lib.linux-x86_64-2.4-11g/cx_Oracle.so -> /usr/lib64/python2.4/site-packages
二、使用python 连接ORACLE 并导出查询数据
--------exp.py -----------------------------------------
import sys
import csv
import cx_Oracle
db=cx_Oracle.connect('tang','tang','192.168.2.100:1521/orcl')
#print db.version
printHeader= True
csv_file ="test.csv"
outputFile =open(csv_file,'w')
output = csv.writer(outputFile,dialect='excel')
sql='select * from (select email from user c where length(email)>3 ) '
curs = db.cursor()
curs.execute(sql)
if printHeader:
cols=[]
for col in curs.description:
cols.append(col[0])
output.writerow(cols)
for row_data in curs:
output.writerow(row_data)
outputFile.close()
db.close()
三、原使用 ORACLE dbms_output 包导出数据的方法:
------------exp.tst------------------
declare
P_FILENAME VARCHAR2(20);
v_value varchar2(800); --记录的数据
v_sql varchar2(4000);
v_fname varchar2(50);
type t_cursor is ref cursor ;
mycursor t_cursor;
outf utl_file.file_type;
begin
DBMS_OUTPUT.ENABLE(1000000); --加大缓存
v_fname:='test.csv';
v_sql:=
'select email from user c '||' where length(c.email)>3 )';
dbms_output.put_line(v_sql);
outf:=utl_file.fopen('DIR_ORACLE_USER',v_fname,'w');
open mycursor for v_sql;
loop
fetch mycursor into v_value;
exit when mycursor%notfound;
utl_file.put_line(outf,v_value);
end loop;
utl_file.fclose(outf);
end;
如果觉得《用两种不同的方法导出ORACLE 查询数据为CSV 文件 (python 代码 与 使用 utl_file 包)》对你有帮助,请点赞、收藏,并留下你的观点哦!