Python-oracledb 游标与连接管理详解

本文深入探讨 `python-oracledb` 中游标对象（`cursor`）和变量（`cursor.var()`）的工作原理。我们将区分客户端 Python 对象与服务器端数据库会话资源，解释 `cursor.var()` 创建的变量的生命周期，以及它们在数据库连接关闭和重连时的行为。文章还将纠正常见误解，并提供在不同数据库会话间维护数据值的正确方法。

1. python-oracledb 游标与绑定变量概述

在 python-oracledb 模块中，cursor 对象是与数据库进行交互的核心。它允许执行 SQL 语句、调用存储过程和函数。cursor.var() 方法则用于创建绑定变量，这些变量是 Python 程序与 Oracle 数据库之间传递数据的桥梁。

cursor.var() 返回一个 Var 对象，它代表一个客户端 Python 数据结构，用于存储将要发送到数据库或从数据库接收的数据。这些 Var 对象是为 SQL 语句中的绑定参数设计的，能够显著提高性能和安全性，例如防止 SQL 注入。

2. 数据库会话与游标的生命周期

理解 python-oracledb 中变量行为的关键在于区分客户端（Python 应用程序）和服务器端（Oracle 数据库）的概念。

• 数据库会话（Session）：当 oracledb.connect() 建立一个连接时，Oracle 数据库会为该连接创建一个唯一的会话。这个会话是用户与数据库交互的上下文，所有 SQL 语句的执行、游标的打开、事务的管理都发生在这个会话中。
• 服务器端游标：在数据库服务器上，当执行一个 SQL 语句时，Oracle 可能会为它分配一个服务器端游标。这个服务器端游标的生命周期与创建它的数据库会话紧密相关。
• 连接关闭的影响：当调用 connection.close() 时，与该连接关联的数据库会话会被终止。这意味着该会话中所有打开的服务器端游标、未提交的事务以及任何会话级别的状态都会被清除。
• 重新连接：再次调用 oracledb.connect() 会建立一个全新的数据库连接，并创建一个全新的、独立的数据库会话。这个新会话与之前的会话之间没有任何状态共享。

3. cursor.var() 变量的客户端特性

cursor.var() 方法创建的 Var 对象是一个纯粹的 Python 对象，其值存储在 Python 程序的内存空间中。它的生命周期独立于数据库连接的打开或关闭。

• 客户端存储：当您使用 host_variable = cursor.var(str) 创建一个 Var 对象并调用 host_variable.setvalue(0, 'VALUE') 时，这个值仅仅是存储在 Python 程序的 host_variable 对象中。
• 数据传输：只有当这个 Var 对象作为绑定变量被传递给 cursor.execute() 方法时，其值才会被实际发送到数据库服务器。同样，如果它被用作输出绑定变量，数据库返回的值也会被存储回这个 Python Var 对象中。
• 独立于会话：即使数据库连接关闭，host_variable 这个 Python 对象仍然存在于程序的内存中，并保留其最后设置的值。它与任何特定的数据库会话或服务器端游标无关。

4. 实战演示：连接重置对变量的真实影响

为了更清晰地说明这一点，我们来看一个常见的误解和正确的行为演示。

4.1 常见误解：变量值似乎被保留

考虑以下代码片段，它可能导致初学者误认为 cursor.var() 创建的变量值在连接关闭和重开后依然保持：

import oracledb
import connection_config # 假设包含 user, pw, dsn

# 第一次连接和操作
con = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
cursor = con.cursor()

host_variable = cursor.var(str)
host_variable.setvalue(0, 'VALUE_FROM_SESSION_1')
print(f"第一次连接 - host_variable 值: {host_variable.getvalue()}") # 输出: VALUE_FROM_SESSION_1
con.close() # 关闭连接，终止第一个数据库会话

# 第二次连接和操作
con = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
cursor = con.cursor() # 创建新的游标，关联到新的数据库会话

# 此时，host_variable 仍然是第一次连接时创建的那个 Python 对象
print(f"第二次连接后 - host_variable 值: {host_variable.getvalue()}") # 输出: VALUE_FROM_SESSION_1
con.close()

解释： 上述代码中，两次 print(host_variable.getvalue()) 都输出了 VALUE_FROM_SESSION_1。这并非因为值在数据库会话之间得到了保持，而是因为 host_variable 仅仅是一个 Python 对象，它的值在 Python 程序的内存中。当 con.close() 被调用时，数据库连接被关闭，但 host_variable 这个 Python 对象本身并没有被销毁，它仍然存在于 Python 内存中并保留着上次设置的值。第二个 print 语句只是再次读取了这个 Python 对象的当前值，而它与新建立的数据库会话或新游标没有任何关联。

4.2 正确演示：新会话需要新变量

为了证明变量值不会在数据库会话之间自动传递，我们需要为新的连接和游标创建新的 Var 对象：

import oracledb
import connection_config # 假设包含 user, pw, dsn

# 第一个连接和游标
con1 = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
cursor1 = con1.cursor()

host_variable1 = cursor1.var(str)
host_variable1.setvalue(0, 'VALUE_FROM_SESSION_1')
print(f"Session 1 - host_variable1 value: {host_variable1.getvalue()}")
con1.close() # 关闭第一个连接

# 第二个连接和游标
con2 = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
cursor2 = con2.cursor()
host_variable2 = cursor2.var(str) # 为新游标创建新的Var对象

# 打印 host_variable1 的值（仍在Python内存中）
print(f"After con1 closed, host_variable1 value (in Python memory): {host_variable1.getvalue()}")

# 打印 host_variable2 的值（新创建，未设置）
try:
    # 尝试获取新创建但未设置值的Var对象的值，通常会是None或引发错误
    print(f"Session 2 - host_variable2 value (newly created): {host_variable2.getvalue()}")
except oracledb.ProgrammingError as e:
    print(f"Session 2 - host_variable2 value (newly created): Not set, error: {e}")
except Exception as e:
    print(f"Session 2 - host_variable2 value (newly created): Not set, or unexpected error: {e}")

con2.close() # 关闭第二个连接

输出示例：

Session 1 - host_variable1 value: VALUE_FROM_SESSION_1
After con1 closed, host_variable1 value (in Python memory): VALUE_FROM_SESSION_1
Session 2 - host_variable2 value (newly created): Not set, or error: DPY-2005: a value must be set for the variable

这个例子清楚地表明，host_variable2 作为一个新的 Var 对象，在创建时并没有继承 host_variable1 的值。它是一个独立的、未初始化的对象，进一步证实了 cursor.var() 创建的变量是客户端 Python 对象，其值不自动跨数据库会话传递。

5. 跨数据库会话维护数据值的策略

既然 cursor.var() 创建的变量值不会自动跨数据库会话保持，那么如果需要在不同会话之间共享或维护数据，有哪些可行的策略呢？

1. 客户端 Python 变量： 最直接的方式是将数据存储在 Python 脚本的普通变量中。这些变量在 Python 程序的生命周期内都有效，可以被不同连接和游标使用。

   import oracledb
   import connection_config
   
   # 在Python客户端维护一个值
   client_data = 'DATA_TO_PERSIST'
   
   # 第一次连接
   con1 = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
   cursor1 = con1.cursor()
   # 使用 client_data
   # 例如，将其作为绑定变量传递给SQL
   # cursor1.execute("INSERT INTO my_table (col) VALUES (:1)", [client_data])
   print(f"第一次连接使用客户端数据: {client_data}")
   con1.close()
   
   # 第二次连接
   con2 = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
   cursor2 = con2.cursor()
   # 再次使用 client_data
   print(f"第二次连接使用客户端数据: {client_data}")
   con2.close()

   这种方法适用于需要在客户端应用程序中短暂存储数据，并在不同数据库操作中重用的场景。

2. Oracle 数据库包变量： 在 Oracle 数据库服务器端，可以利用 PL/SQL 包的全局变量来维护会话级别的状态。包变量可以在同一个会话内的不同存储过程或函数调用中共享。如果需要跨会话，则需要更复杂的机制（如使用 DBMS_SESSION.SET_CONTEXT 或将包变量持久化到表中）。

   -- 示例：创建PL/SQL包
   CREATE PACKAGE my_package AS
       g_session_data VARCHAR2(100);
       PROCEDURE set_data(p_data IN VARCHAR2);
       FUNCTION get_data RETURN VARCHAR2;
   END my_package;
   /
   
   CREATE PACKAGE BODY my_package AS
       PROCEDURE set_data(p_data IN VARCHAR2) IS
       BEGIN
           g_session_data := p_data;
       END set_data;
   
       FUNCTION get_data RETURN VARCHAR2 IS
       BEGIN
           RETURN g_session_data;
       END get_data;
   END my_package;
   /

   在 Python 中使用：

   # ... (连接建立) ...
   cursor = con.cursor()
   # 设置包变量
   cursor.execute("BEGIN my_package.set_data(:1); END;", ['Persistent Value'])
   # 获取包变量
   result = cursor.execute("SELECT my_package.get_data FROM DUAL").fetchone()
   print(f"从包中获取的值: {result[0]}")
   con.close()
   # 注意：这个值只在该会话中有效，新会话需要重新设置

   这种方法将数据存储在服务器端，但其生命周期通常也局限于当前数据库会话。

3. 数据库表持久化： 最可靠和常用的方法是将数据存储在数据库表中。通过 INSERT、UPDATE 或 DELETE 等 SQL 语句，可以将数据永久地写入数据库，使其在任何时间、任何会话中都可访问。

   import oracledb
   import connection_config
   
   # ... (连接建立) ...
   cursor = con.cursor()
   
   # 将数据插入表中
   data_to_save = 'Important Persistent Data'
   cursor.execute("CREATE TABLE IF NOT EXISTS persistent_data (id NUMBER GENERATED BY DEFAULT AS IDENTITY, value VARCHAR2(255))")
   cursor.execute("INSERT INTO persistent_data (value) VALUES (:1)", [data_to_save])
   con.commit() # 提交事务以确保数据持久化
   
   # 关闭连接
   con.close()
   
   # 重新连接，数据仍然存在
   con_new = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
   cursor_new = con_new.cursor()
   # 从表中读取数据
   result = cursor_new.execute("SELECT value FROM persistent_data WHERE value = :1", [data_to_save]).fetchone()
   print(f"从数据库表中读取的值: {result[0]}")
   con_new.close()

   这种方法提供了最高级别的数据持久性，数据可以跨越应用程序重启、数据库重启甚至长时间间隔。

6. 总结与最佳实践

• 客户端 vs. 服务器端：理解 python-oracledb 的 cursor.var() 创建的是客户端 Python 对象，其值存储在 Python 内存中，与数据库会话无关。只有通过 cursor.execute() 调用，这些值才会在客户端和服务器端之间传输。
• 会话生命周期：Oracle 数据库会话是短暂的。关闭连接会终止当前会话及其所有服务器端资源。重新连接会创建一个全新的会话。
• 数据持久化：如果需要在不同的数据库会话之间维护数据，应避免依赖 cursor.var() 对象来“记住”值。相反，应采用以下策略：
  • 将数据存储在客户端 Python 变量中，适用于在应用程序运行时短暂共享数据。
  • 利用 Oracle 数据库包变量，在单个数据库会话内共享数据，但通常不跨会话。
  • 将数据持久化到数据库表中，这是实现数据永久性、跨任何会话共享的最可靠方式。

通过清晰地认识客户端 Python 对象与服务器端数据库会话之间的界限，开发者可以更有效地管理 python-oracledb 中的数据流和连接，从而构建健壮、高效的数据库应用程序。