Python 安装使用cx_Oracle操作Oracle数据库

1、安装cx_Oracle

Python中连接Oracle数据库，需要下载安装Oracle客户端驱动(Oracle Instant Client Basic)，然后在安装cx_Oracle，如下，

pip install cx_Oracle

2、连接Oracle数据库

1）使用一个连接字符串

cx_Oracle.connect('admin/admin@192.168.31.11:1521/cjavapyXDB')

2）使用多个参数连接

cx_Oracle.connect('admin', 'admin', '192.168.31.11:1521/cjavapyXDB'')

注意：cjavapyXDB是SERVICE_NAME，在tnsnames.ora文件中可以查看到。

3、Oracle数据库常用操作

1）查询数据

import cx_Oracle

db_conn = cx_Oracle.connect('admin', 'admin', '192.168.31.11:1521/cjavapyXDB')
db_cursor=db_conn.cursor()
sql_cmd='SELECT * FROM OrderInfo'
db_cursor.execute(sql_cmd)
for row in  db_cursor:
    print(row)

db_cursor.close()
db_conn.close()

#带参数查询
db_conn = cx_Oracle.connect('admin', 'admin', '192.168.31.11:1521/cjavapyXDB')
db_cursor=db_conn.cursor()
sql_cmd='SELECT * FROM OrderInfo 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函数获取多行
db_conn = cx_Oracle.connect('admin', 'admin', '192.168.31.11:1521/cjavapyXDB')
db_cursor=db_conn.cursor()
sql_cmd='SELECT * FROM OrderInfo'
db_cursor.execute(sql_cmd)
#获取单行
print(db_cursor.fetchone())
#获取多行
#print(db_cursor.fetchall())
db_cursor.close()
db_conn.close()

2）插入数据

import cx_Oracle
from datetime import datetime

db_conn = cx_Oracle.connect('admin', 'admin', '192.168.31.11:1521/cjavapyXDB')
db_cursor=db_conn.cursor()
sql_cmd = 'INSERT INTO UserInfo(id, name, age,birth) VALUES(:id, :name, :age,:birth)'
#一次插入一条
db_cursor.execute(sql_cmd,(11,'levi',12,datetime(2019,9,2,9,30,12)))
db_cursor.execute(sql_cmd,(22,'lynn',12,datetime(2020,12,1,11,22,12)))
#一次插入多条
#db_cursor.executemany(sql_cmd, [(11,'levi',12,datetime(2019,9,2,9,30,12))), (22,'lynn',12,datetime(2020,12,1,11,22,12))])
db_conn.commit()
db_cursor.close()
db_conn.close()

3）删除数据

import cx_Oracle
from datetime import datetime

db_conn = cx_Oracle.connect('admin', 'admin', '192.168.31.11:1521/cjavapyXDB')
db_cursor=db_conn.cursor()
sql_cmd = 'DELETE FROM UserInfo WHERE id = :id '
db_cursor.execute(sql_cmd,{'id' : 11})
db_conn.commit()
db_cursor.close()
db_conn.close()

4）修改数据

import cx_Oracle
from datetime import datetime

db_conn = cx_Oracle.connect('admin', 'admin', '192.168.31.11:1521/cjavapyXDB')
db_cursor=db_conn.cursor()
sql_cmd = "UPDATE UserInfo SET name = 'John' WHERE id = :id"
db_cursor.execute(sql_cmd,{'id' : 12})
db_conn.commit()
db_cursor.close()
db_conn.close()

4、Oracle数据库调用存储过程和函数

1）创建存储过程

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

2）调用存储过程

import cx_Oracle

db_conn = cx_Oracle.connect('admin', 'admin', '192.168.31.11:1521/cjavapyXDB')
db_cursor=db_conn.cursor()
str='cjavapy'
msg =db_cursor.var(cx_Oracle.STRING)
db_cursor.callproc('myproc',[str,msg])
db_conn.commit()
print(msg)
print(msg.getvalue())
db_cursor.close()
db_conn.close()

3）创建函数

CREATE OR REPLACE function myfunc(V1 VARCHAR2,V2 VARCHAR2) RETURN VARCHAR2 
IS
BEGIN
  RETURN V1 || v2;
END;

4）调用函数

import cx_Oracle

db_conn = cx_Oracle.connect('admin', 'admin', '192.168.31.11:1521/cjavapyXDB')
db_cursor=db_conn.cursor()
str=db_cursor.callfunc('myfunc',cx_Oracle.STRING,['python','cjavapy'])
print(str)
db_conn.commit()
db_cursor.close()
db_conn.close()