Python访问Oracle数据库cx_Oracle包介绍

介绍： 

cx_Oracle 包为 Python API 提供了访问 Oracle 数据库的工具。
您可以使用 pip 命令安装此软件包。
Python 程序调用 cx_Oracle 函数。python 和 oracle 之间的连接是使用 cx_oracle inbuild Oracle 客户端库建立的。
用户应在机器上安装 Oracle 客户端库。

cx_Oracle 包安装

给定的命令将用于安装 cx_Oracle 包。
需要 oracle 客户端库来执行与数据库的 python 应用程序连接。

pip install cx_Oracle

我们如何与 Oracle 建立联系？

要访问 oracle 数据库，我们需要导入 cx_Oracle 包。
与 oracle 建立连接需要用户名、密码和连接字符串。
cx_Oracle 连接方法将用于传递您在 tnsnames.ora 文件中配置的用户名、密码和连接字符串。

import cx_Oracle

# connection string 
con = cx_Oracle.connect('pydb/pydb@xe')
print(con.version)
con.close()

为什么要关闭连接？

需要关闭程序占用的资源。
资源需要显式关闭以释放数据库资源。
这是强制关闭所有资源的必要条件。

我们如何使用 cx_Oracle 在 Oracle 数据库中创建表？

从连接创建一个游标对象。
要在数据库中创建雇员表，需要准备 SQL 语句。
要执行 oracle SQL 语句，将使用 cx_Oracle 执行游标方法。

以下是创建表的步骤：

创建连接对象
定义游标
构造查询字符串以创建表
将 oracle 查询传递给游标的执行方法
定义异常以捕获错误。
关闭连接

import cx_Oracle

try:
   # Connecting to DB
   con = cx_Oracle.connect('pydb/pydb@xe')
   # Create a cursor
   cursor = con.cursor()
   sql = """create table emp
                  (emp_id number(3),
                  emp_name varchar(30),
                  salary number(6))"""
   #Execute sql statement
   cursor.execute(sql)
   print("Emp table created successfully")
   
except cx_Oracle.DatabaseError as e:
   print("Problem connecting to Oracle", e)
finally:
    # Close connection
    cursor.close()
    con.close()

我们如何使用 cx_Oracle 将单个和多个记录插入到 oracle 表中？

从连接创建一个游标对象。该游标将执行数据库插入操作。
将构建具有表和列名称的 SQL 插入语句以执行操作。

将单条记录插入数据库表的步骤：

创建连接对象
定义游标
构造 oracle 查询以在表中执行插入记录操作
将 oracle 查询传递给 cx_Oracle 游标的执行方法
执行提交以保存更改

向数据库表中插入多条记录的步骤：

创建要插入数据库的记录列表。
创建一个循环并遍历每条记录。
执行插入语句一一
提交更改

import cx_Oracle

try:
   
   # Connecting to DB
   con = cx_Oracle.connect('pydb/pydb@xe')
   # create a cursor
   cursor = con.cursor()
   # Define sql statement
   sql = "insert into emp values(100,'Martin',200)"
   # Execute the SQL statement
   cursor.execute(sql)
   # Do the commit operation
   con.commit()
   print("Table record created successfully")
   
   # create a cursor
   cursor = con.cursor()
   #Prepare record list
   employee_list = [(103, 'Neck', 5000), (104, 'Jones', 5500), (105,'Charl', 6000)]
   # Iterate loop over a single employee
   for emp_rec in employee_list:
        # Execute the SQL statement
        cursor.execute("""insert into emp(emp_id,emp_name,salary)
           values(:emp_id,:emp_name,:salary)"""
                       , {'emp_id': emp_rec[0], 'emp_name': emp_rec[1],
                       'salary': emp_rec[2]})
   # do the commit
   con.commit()
   print("Multiple records are created successfully")
   
except cx_Oracle.DatabaseError as e:
   print("Problem connecting to Oracle", e)

finally:
    # close connection
    cursor.close()
    con.close()

我们如何使用 cx_Oracle 从 python 中的数据库中获取记录？

以下是将单个记录插入表的步骤：

创建连接对象
定义游标
构造oracle查询以在数据库中执行选择记录操作。
将 oracle 查询字符串传递给 cx_Oracle 游标的执行方法
检索到的数据库记录存储在游标中。
使用 for 循环一次打印一条记录。
fetchall 方法将用于从游标中获取所有记录。
使用 fetchall 方法，所有数据库记录将在一次运行中显示。

import cx_Oracle

try:
   # Connecting to DB
   con = cx_Oracle.connect('pydb/pydb@xe')
   # create a cursor
   cursor = con.cursor()
   # prepare the sql statement
   sql = "select * from emp"
   # execute the sql statement
   cursor.execute(sql)
   # Now print single result at a time
   for rows in cursor:
       print(rows)
   
   # display records using fetchall
   cursor.execute(sql)
   # using fetchall get all records from cursor
   result = cursor.fetchall()
   # display result
   print(result)
   
except cx_Oracle.DatabaseError as e:
   print("Problem in database operation", e)
finally:
   # Close connection
    cursor.close()
    con.close()

如何使用 cx_Oracle 在 python 中执行 oracle 匿名块？

以下是将一条记录插入到 oracle 表中的步骤：

创建一个 cx_Oracle 连接对象定义
一个游标
构造一个匿名块
执行

import cx_Oracle

try:
   # Connecting to DB
   con = cx_Oracle.connect('pydb/pydb@xe')
   # create a cursor
   cursor = con.cursor()
   # develop anonymous block
   statement = """
    begin
        delete from emp;
        insert into emp (emp_id, emp_name,salary)
        values (201, 'Duke',9000);
        insert into emp (emp_id, emp_name,salary)
        values (202, 'Pepe',5000);
        commit;
        end;"""
   # execute commit statement 
   cursor.execute(statement)
   print("Anonymous block executed successfully")
   
except cx_Oracle.DatabaseError as e:
   print("Problem in database operation", e)
finally:
   # Close connection
    cursor.close()
    con.close()

我们如何从 python 调用 oracle 函数？

下面是我们从 emp 表中选择数据并返回员工姓名列值的 oracle 函数。
该函数接受员工 ID 作为输入参数，并根据该数据获取数据。

create or replace function get_emp_name(in_emp_id in number) 
   return varchar2
   is 
   vemp_name varchar2(30);
begin 
      -- fetch employee name based on passed employee id
      Select emp_name 
      into vemp_name
      from emp
      where emp.emp_id = in_emp_id;

      -- return employee name  
      return(vemp_name); 
end;

下面是 oracle 函数，其中员工 id 是输入参数，员工姓名是输出参数，并返回工资列值。
get_emp_details 将 emp 名称作为输出参数，将薪水作为返回参数。

create or replace function get_emp_details(in_emp_id in number,
                                           out_emp_name out varchar2) 
   return number
   is 
   vsalary number(5);
begin 
      -- fetch employee name based on passed employee id
      Select emp_name,salary 
      into out_emp_name,vsalary
      from emp
      where emp.emp_id = in_emp_id;

      -- return employee salary  
      return(vsalary); 
end;

调用oracle函数并显示输出结果的步骤：

创建一个 cx_Oracle 连接对象
定义一个游标
创建一个员工 ID cx_Oracle 对象以将其作为输入参数传递给函数。
创建一个 cx_Oracle 字符串对象变量来存储 oracle 返回的变量。
使用callfunc游标方法以 emp id 作为输入参数调用 oracle 函数，并接收 emp 名称作为输出参数。

以下是调用 oracle 函数并接受输出和返回参数的步骤。

创建一个 cx_Oracle 字符串对象变量来存储 oracle 返回的变量。
调用 get_emp_details 函数并接受函数返回和输出参数值。
我们可以使用 get value 方法显示接收到的输出参数。

import cx_Oracle

try:
   # Connecting to DB
   con = cx_Oracle.connect('pydb/pydb@xe')
   # create a cursor
   cursor = con.cursor()
   # define employee id
   emp_id = 201
   # define object to store string return value
   vemp_name = cursor.var(cx_Oracle.STRING)
   # call procedure with parameter
   cursor.callfunc('get_emp_name',vemp_name,[emp_id])
   # print return employee name
   print('return emp_name:',vemp_name.getvalue())
   
   # define object to store integer return value
   vemp_name = cursor.var(cx_Oracle.STRING)
   # call procedure with parameter
   vemp_salary = cursor.callfunc('get_emp_details',int,[emp_id,vemp_name])
   # print the return employee name and salary
   print('return emp_name:',vemp_name.getvalue(),' and employee salary:',vemp_salary)
   
except cx_Oracle.DatabaseError as e:
   print("Problem in database operation", e)
finally:
    # Close connection
    cursor.close()
    con.close()

我们如何从 python 调用 oracle 过程？

下面是 oracle 过程 proc_get_emp_details 使用它，我们将为给定的 emp id、emp name 和salary 参数执行插入操作。
执行选择操作以获取员工详细信息。

create or replace procedure proc_get_emp_details(in_emp_id in number,
                                            in_emp_name in varchar2,
                                            in_emp_salary in number,
                                            out_emp_name out varchar2) 
is 
begin 
      -- perform the ins operation
      insert into emp(emp_id,emp_name,salary)
      values(in_emp_id,in_emp_name,in_emp_salary);
      commit;

      -- fetch employee name based on passed employee id
      Select emp_name 
      into out_emp_name
      from emp
      where emp.emp_id = in_emp_id;
      
end;

调用 oracle 过程并显示输出的步骤：

创建cx_Oracle 连接对象
定义连接游标
定义emp id、emp name 和salary cx_Oracle 对象以在数据库中执行插入操作。
要存储返回输出变量，请创建一个 cx_Oracle 字符串对象。
使用员工 ID、员工姓名和薪水输入参数调用过程 proc_get_emp_details，并接受员工姓名输出参数。

import cx_Oracle
try:
   # Connecting to DB
   con = cx_Oracle.connect('pydb/pydb@xe')
   # create a cursor
   cursor = con.cursor()
   
   # Defined objects for insert statement
   emp_id = 203
   emp_name = 'Pep'
   emp_salary = 6000
   
   # define object to store string return value
   vemp_name = cursor.var(cx_Oracle.STRING)
   
   # add a new employee
   cursor.callproc('proc_get_emp_details',[emp_id, emp_name, emp_salary,vemp_name])
   print('Return emp_name:',vemp_name.getvalue())
   
except cx_Oracle.DatabaseError as e:
   print("Problem in DB operation", e)
finally:
    # close db connections
    cursor.close()
    con.close()

我们如何从 python 调用 oracle 包？

在 oracle 包 pkg_emp_details 中，我们在包规范部分定义了一个过程和一个函数。
在 oracle 包正文中，在一个过程中，我们正在更新给定员工 id 输入参数的薪水。
在函数中，我们根据员工 ID 检索员工姓名。

create or replace package pkg_emp_details
as
procedure update_record (in_emp_id in number,
                         in_emp_salary in number);
function fn_get_emp_name (in_emp_id in number) return varchar2;
end pkg_emp_details;
/

create or replace package body pkg_emp_details IS
  
   procedure update_record (in_emp_id in number,
                            in_emp_salary in number)
      as
   begin
      update emp
        set salary = in_emp_salary
       where emp_id = in_emp_id;

      commit;
   end update_record;

  
   function fn_get_emp_name (
      in_emp_id       in number
   ) return varchar2 
   IS
      vemp_name   varchar2(30);
   begin

      -- fetch employee name based on passed employee id
      Select emp_name
      into vemp_name
      from emp
      where emp_id = in_emp_id;

      -- return employee name  
      return(vemp_name); 

   end fn_get_emp_name;

end pkg_emp_details;
/

调用 oracle 包并显示输出结果的步骤：

创建连接对象
定义游标
定义员工 ID 和薪水对象变量以更新员工数据。
使用emp id 和salary 输入参数调用oracle 过程。
创建一个 cx_Oracle 字符串对象变量来存储函数输出变量。
fn_get_emp_name 函数检索给定 emp id 的 emp 名称。

import cx_Oracle
try:
   # Connecting to DB
   con = cx_Oracle.connect('pydb/pydb@xe')
   # create a cursor
   cursor = con.cursor()
   # Defined objects for update statement
   emp_id = 203
   emp_salary = 25000
   
   # update a employee salary
   cursor.callproc('pkg_emp_details.update_record',[emp_id,emp_salary])
   # define object to store string return value
   vemp_name = cursor.var(cx_Oracle.STRING)
   # call procedure with parameter
   cursor.callfunc('pkg_emp_details.fn_get_emp_name',vemp_name,[emp_id])
   # print return employee name
   print('return emp_name:',vemp_name.getvalue())
   
except cx_Oracle.DatabaseError as e:
   print("Problem in DB operation", e)
finally:
    # close db connections
    cursor.close()
    con.close()

我们如何在python中处理oracle ref cursor？

Oracle 函数 get_emp_data 返回引用游标中的值。
在这个函数中，我们正在获取员工表记录。

create or replace FUNCTION get_emp_data
   RETURN SYS_REFCURSOR
AS
   c_employee SYS_REFCURSOR;
BEGIN

   OPEN c_employee FOR 
   SELECT 
      emp_id, 
      emp_name, 
      salary
   FROM 
      emp 
   ORDER BY 
         emp_id;

   RETURN c_employee;
END;

以下是调用 oracle 包的步骤：

创建一个 cx_Oracle 连接对象
定义一个游标
创建一个 cx_Oracle 游标对象来存储get_emp_data函数返回记录。
创建一个 emp_data 对象来存储返回的员工数据。
使用 for 循环，我们遍历每条记录并显示记录。

import cx_Oracle

try:
   # Connecting to DB
   con = cx_Oracle.connect('pydb/pydb@xe')
   # create a cursor
   cursor = con.cursor()
   
   # define object to store string return value
   vemp_data = cursor.var(cx_Oracle.CURSOR)
   
   # add a new pet
   cursor.callfunc('get_emp_data',vemp_data)
   emp_data = vemp_data.getvalue()
   
   for emp_rec in emp_data:
       print('emp_id:',emp_rec[0],' emp_name: ',emp_rec[1], 'salary:',emp_rec[2])
       
except cx_Oracle.DatabaseError as e:
   print("Problem in database operation", e)
   
finally:
    # close the connection
    cursor.close()
    con.close()