1.cx_Oracle概述

cx_Oracle是一个Python 扩展模块，通过使用所有数据库访问模块通用的数据库 API来实现 Oracle 数据库的查询和更新。为使用一些专为 Oracle 设计的特性，还加入了多个通用数据库 API 的扩展。cx_Oracle 的开发历时多年，涵盖了大多数需要在 Python 中访问 Oracle 的客户的需求。

安装cx_Oracle使用pip命令： pip install cx_Oracle

2.连接oracle数据库

cx_Oracle.connect(‘scott’, ‘scott’, ‘127.0.0.1:1521/orcl’)

cx_Oracle.connect(‘scott/scott@127.0.0.1:1521/orcl’)

3.ORACLE的查询

#简单查询

import cx_Oracle

db_conn = cx_Oracle.connect(‘scott’, ‘scott’, ‘127.0.0.1:1521/orcl’)

db_cursor=db_conn.cursor()

sql_cmd=’SELECT * FROM students’

db_cursor.execute(sql_cmd)

for row in db_cursor:

print(row)

db_cursor.close()

db_conn.close()

#带参数查询

db_conn = cx_Oracle.connect(‘scott’, ‘scott’, ‘127.0.0.1:1521/orcl’)

db_cursor=db_conn.cursor()

sql_cmd=’SELECT * FROM students where ID = :id’

sql_p_id={‘id’:2}

db_cursor.execute(sql_cmd,sql_p_id)

for row in db_cursor:

print(row)

db_cursor.close()

db_conn.close()

#获取单行可以使用fetchone函数;获取多行记录，可以使用fetchall函数

import cx_Oracle

db_conn = cx_Oracle.connect(‘scott’, ‘scott’, ‘127.0.0.1:1521/orcl’)

db_cursor=db_conn.cursor()

sql_cmd=’SELECT * FROM students’

db_cursor.execute(sql_cmd)

print(db_cursor.fetchone())

db_cursor.close()

db_conn.close()

4.ORACLE的DML

import cx_Oracle

from datetime import datetime

db_conn = cx_Oracle.connect(‘scott’, ‘scott’, ‘127.0.0.1:1521/orcl’)

db_cursor=db_conn.cursor()

sql_cmd = ‘INSERT INTO students(id, name, age,birth) VALUES(:id, :name, :age,:birth)’

db_cursor.execute(sql_cmd,(11,’王五2′,12,datetime(2017,9,1,12,40,12)))

db_cursor.execute(sql_cmd,(22,’赵六’,12,datetime(2017,9,1,12,40,12)))

db_conn.commit()

db_cursor.close()

db_conn.close()

import cx_Oracle

from datetime import datetime

db_conn = cx_Oracle.connect(‘scott’, ‘scott’, ‘127.0.0.1:1521/orcl’)

db_cursor=db_conn.cursor()

sql_cmd = ‘INSERT INTO students(id, name, age,birth) VALUES(:id, :name, :age,:birth)’

db_cursor.executemany(sql_cmd, [(15, ‘王五2’, 12, datetime(2017, 9, 1, 12, 40, 12)),(16, ‘赵六’, 12, datetime(2017, 9, 1, 12, 40, 12))])

db_conn.commit()

db_cursor.close()

db_conn.close()

5.调用存储过程和函数

–存储过程代码：

CREATE OR REPLACE PROCEDURE P_DEMO(V1 IN VARCHAR2, V2 OUT VARCHAR2) IS

BEGIN

V2 := V1;

END;

#Python代码：

import cx_Oracle

db_conn = cx_Oracle.connect(‘scott’, ‘scott’, ‘127.0.0.1:1521/orcl’)

db_cursor=db_conn.cursor()

str=’abdd’

msg =db_cursor.var(cx_Oracle.STRING)

db_cursor.callproc(‘p_demo’,[str,msg])

db_conn.commit()

print(msg)

print(msg.getvalue())

db_cursor.close()

db_conn.close()

–函数代码：

CREATE OR REPLACE function F_DEMO(V1 VARCHAR2,V2 VARCHAR2) RETURN VARCHAR2

IS

BEGIN

RETURN V1 || v2;

END;

#Python代码：

import cx_Oracle

db_conn = cx_Oracle.connect(‘scott’, ‘scott’, ‘127.0.0.1:1521/orcl’)

db_cursor=db_conn.cursor()

str=db_cursor.callfunc(‘f_demo’,cx_Oracle.STRING,[‘abc’,’ddd’])

print(str)

db_conn.commit()

db_cursor.close()

db_conn.close()