python cx_Oracle简单操作

1,读取文件操作

文件内容：

col1 col2 col3
China 1.299 0.004 4.762
USA 0.832 5.196 2.521

读取脚本

#!/usr/bin/env python

import cx_Oracle
conn = cx_Oracle.connect('loge/china@192.168.1.11/prod')
c = conn.cursor()

# 创建一个列表，后期批量插入
list_line=[]

# 只读打开文件
f = open("/root/test.txt", "r")
# 创建一个变量，用于跳过文件第一行
line_num = 0

while True:
    # 读取每一行
    line1 = f.readline()
    line_num +=1
    if line1:
        #处理每行\n
        if (line_num !=1):
             line = line1.strip('\n')
             # 这里将字符串转换为逗号隔开的字符串
             line = ",".join(line.split())
             # 这里将字符串转为列表
             line = line.split(",")
             print(line)
             #list_line=(','.join(line.split())).split(',')
             #list_line.append((','.join(line.split())).split(','))
             
             area = line[0]
             val1 = float(line[1])
             val2 = line[2]
             val3 = line[3]
             # 这里组装列表
             list_line.append((area,val1,val2,val3))
             print("area:%s,val1:%.3f,val2:%s,val3:%s"%(area,val1,val2,val3))
    else:
        break
# 关闭文件
f.close()

# 执行批量插入
c.executemany('insert into t1(area,v1,v2,v3)  values(:1,:2,:3,:4)',list_line)
# 提交事务
conn.commit()
#关闭游标
c.close()
# 关闭DB连接
cursor.close()

2,其他操作，查询，调用过程，函数

>>> import cx_Oracle
>>> conn=cx_Oracle.connect('loge/china@192.168.1.11/prod') 
>>> c=conn.cursor()
>>> x=c.execute("""select * from t1""")      
>>> x.fetchone()
('China', 1.299, 0.004, 4.762, None)
>>> c.close()
>>> c=conn.cursor()
>>> c.prepare("""select * from t1 where area=:id""")
>>> x=c.execute(None,{'id':'China'})
>>> for row in x:
...   print(row)
... 
('China', 1.299, 0.004, 4.762, None)
>>> c.close()
>>> conn.close()
>>> 
>>> 
>>> #调用过程
... 
>>> conn=cx_Oracle.connect('loge/china@192.168.1.11/prod')
>>> c=conn.cursor()
>>> str1='China'
>>> str2='      '
>>> x=c.callproc('p_demo',[str1,str2])
>>> print(str2)
      
>>> print(str1)  
China
>>> str2=c.var(cx_Oracle.STRING)     
>>> x=c.callproc('p_demo',[str1,str2])
>>> print(str2)                       
<cx_Oracle.STRING with value 'China'>
>>> print(str2.getvalue)
<built-in method getvalue of cx_Oracle.STRING object at 0x7f2ab913e4a8>
>>> print(str2.getvalue())
China
>>> 
>>> 
>>> 
>>> #调用函数
... 
>>> c.close()
>>> c=conn.cursor()
>>> str1='China'
>>> str2=c.callfunc('f_demo',cx_Oracle.STRING,[str1])
>>> print(str2)
China
>>> c.close()
>>> conn.colse()

附，使用的过程函数

CREATE OR REPLACE PROCEDURE P_DEMO(V1 IN VARCHAR2, V2 OUT VARCHAR2) IS
BEGIN
    V2 := V1;
END;



CREATE OR REPLACE FUNCTION F_DEMO(V1 VARCHAR2) RETURN VARCHAR2 IS
BEGIN
    RETURN V1;
END;