Python访问Oracle数据库cx_Oracle包介绍

清疚 2023-09-29 10:27 40阅读 0赞

**介绍：**

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()