Python cx_Oracle
什么是cx_Oracle
cx_Oracle 是 Python 语言与 Oracle 数据库进行交互的一个开源模块。它提供了一个 Python DB API 2.0 兼容的接口，可以让开发者在 Python 中连接、查询和操作 Oracle 数据库。
安装cx_Oracle
在开始使用 cx_Oracle 之前，我们需要先安装它。可以通过 pip 来安装 cx_Oracle 模块，命令如下：
pip install cx_Oracle
连接到Oracle数据库
在使用 cx_Oracle 之前，我们需要先建立与 Oracle 数据库的连接。下面是一个简单的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 关闭连接connection.close()在上面的示例中，我们使用 cx_Oracle.connect() 方法来建立与 Oracle 数据库的连接。需要传入用户名、密码和数据库的主机名、端口号以及服务名。
执行SQL查询
一旦建立了与数据库的连接，我们就可以执行 SQL 查询了。下面是一个简单的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 创建游标cursor = connection.cursor()# 执行查询cursor.execute("SELECT * FROM employees")# 获取查询结果for row in cursor: print(row)# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用 cursor.execute() 方法执行了一个简单的查询语句，并使用 for 循环遍历了查询结果。
插入数据
除了查询数据，我们还可以向数据库中插入数据。下面是一个插入数据的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 创建游标cursor = connection.cursor()# 插入数据cursor.execute("INSERT INTO employees (id, name) VALUES (1, 'Alice')")# 提交事务connection.commit()# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用 cursor.execute() 方法插入了一条数据，并使用 connection.commit() 提交了事务。
更新数据
更新数据也是常见的操作之一。下面是一个更新数据的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 创建游标cursor = connection.cursor()# 更新数据cursor.execute("UPDATE employees SET name = 'Bob' WHERE id = 1")# 提交事务connection.commit()# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用 cursor.execute() 方法更新了一条数据，并使用 connection.commit() 提交了事务。
删除数据
最后，我们还可以删除数据库中的数据。下面是一个删除数据的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 创建游标cursor = connection.cursor()# 删除数据cursor.execute("DELETE FROM employees WHERE id = 1")# 提交事务connection.commit()# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用 cursor.execute() 方法删除了一条数据，并使用 connection.commit() 提交了事务。
使用绑定变量
在执行 SQL 查询时，我们可以使用绑定变量来提高性能和安全性。下面是一个使用绑定变量的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 创建游标cursor = connection.cursor()# 使用绑定变量查询数据id = 1cursor.execute("SELECT * FROM employees WHERE id = :id", id=id)# 获取查询结果for row in cursor: print(row)# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用了 :id 绑定变量来查询指定 id 的数据。
使用事务
在操作数据库时，通常会涉及到事务的处理。下面是一个使用事务的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 开始事务connection.begin()# 创建游标cursor = connection.cursor()# 插入数据cursor.execute("INSERT INTO employees (id, name) VALUES (2, 'Charlie')")# 提交事务connection.commit()# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用 connection.begin() 开始了一个事务，并在操作完成后使用 connection.commit() 提交了事务。
错误处理
在操作数据库时，可能会出现各种错误，我们需要进行错误处理。下面是一个简单的错误处理示例代码：
import cx_Oracletry: # 连接到Oracle数据库 connection = cx_Oracle.connect("username", "password", "hostname:port/service_name") # 创建游标 cursor = connection.cursor() # 执行查询 cursor.execute("SELECT * FROM employees") # 获取查询结果 for row in cursor: print(row) # 关闭游标 cursor.close()except cx_Oracle.DatabaseError as e: error, = e.args print("Oracle-Error-Code:", error.code) print("Oracle-Error-Message:", error.message)finally: # 关闭连接 connection.close()在上面的示例中，我们使用 try-except-finally 结构来处理可能出现的数据库错误。
使用连接池
在实际应用中，为了提高性能和资源利用率，我们可以使用连接池来管理数据库连接。下面是一个使用连接池的示例代码：
import cx_Oraclefrom cx_Oracle import Connection, SessionPool# 创建连接池pool = SessionPool("username", "password", "hostname:port/service_name", min=2, max=5, increment=1)# 从连接池获取连接connection: Connection = pool.acquire()# 创建游标cursor = connection.cursor()# 执行查询cursor.execute("SELECT * FROM employees")# 获取查询结果for row in cursor: print(row)# 关闭游标cursor.close()# 释放连接回连接池pool.release(connection)在上面的示例中，我们使用 SessionPool 类创建了一个连接池，并使用 pool.acquire() 方法从连接池中获取连接。
使用批量插入
在需要插入大量数据时，我们可以使用批量插入来提高性能。下面是一个批量插入的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 创建游标cursor = connection.cursor()# 批量插入数据data = [(3, 'David'), (4, 'Eve'), (5, 'Frank')]cursor.executemany("INSERT INTO employees (id, name) VALUES (:1, :2)", data)# 提交事务connection.commit()# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用 cursor.executemany() 方法批量插入了多条数据。
使用存储过程
在 Oracle 数据库中，存储过程是一种预先编译的 SQL 代码块，可以在数据库中执行。下面是一个使用存储过程的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 创建游标cursor = connection.cursor()# 创建存储过程cursor.execute(""" CREATE OR REPLACE PROCEDURE get_employee_name (id IN NUMBER, name OUT VARCHAR2) AS BEGIN SELECT name INTO name FROM employees WHERE id = id; END;""")# 调用存储过程id = 1name = cursor.var(cx_Oracle.STRING)cursor.callproc("get_employee_name", [id, name])# 打印结果print(name.getvalue())# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用 cursor.execute() 方法创建了一个简单的存储过程，并使用 cursor.callproc() 方法调用了这个存储过程。
使用游标变量
在 Oracle 数据库中，游标变量是一种特殊的数据类型，可以用来存储查询结果集。下面是一个使用游标变量的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 创建游标cursor = connection.cursor()# 创建游标变量result = cursor.var(cx_Oracle.CURSOR)# 执行查询cursor.execute("BEGIN OPEN :1 FOR SELECT * FROM employees; END;", [result])# 获取查询结果for row in result.getvalue(): print(row)# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用 cursor.var() 方法创建了一个游标变量，并在查询时将其作为参数传入。
使用LOB数据类型
在 Oracle 数据库中，LOB（Large Object）数据类型用来存储大量的文本数据。下面是一个使用LOB数据类型的示例代码：
import cx_Oracle# 连接到Oracle数据库connection = cx_Oracle.connect("username", "password", "hostname:port/service_name")# 创建游标cursor = connection.cursor()# 创建LOB对象clob_data = cursor.var(cx_Oracle.CLOB)clob_data.setvalue(0, "This is a CLOB data from geek-docs.com")# 插入LOB数据cursor.execute("INSERT INTO lob_table (id, clob_column) VALUES (1, :clob_data)", clob_data=clob_data)# 提交事务connection.commit()# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用 cursor.var() 方法创建了一个 CLOB 对象，并将其插入到数据库中。
使用连接字符串
除了在 cx_Oracle.connect() 方法中传入用户名、密码等参数外，我们还可以使用连接字符串来连接到数据库。下面是一个使用连接字符串的示例代码：
import cx_Oracle# 连接字符串dsn = cx_Oracle.makedsn("hostname", "port", service_name="service_name")connection = cx_Oracle.connect(user="username", password="password", dsn=dsn)# 创建游标cursor = connection.cursor()# 执行查询cursor.execute("SELECT * FROM employees")# 获取查询结果for row in cursor: print(row)# 关闭游标cursor.close()# 关闭连接connection.close()在上面的示例中，我们使用 cx_Oracle.makedsn() 方法创建了一个 DSN（Data Source Name）字符串，并将其作为参数传入 cx_Oracle.connect() 方法。
使用连接上下文管理器
在 Python 中，我们可以使用上下文管理器来管理资源，包括数据库连接。下面是一个使用连接上下文管理器的示例代码：
import cx_Oracle# 连接上下文管理器with cx_Oracle.connect("username", "password", "hostname:port/service_name") as connection: with connection.cursor() as cursor: # 执行查询 cursor.execute("SELECT * FROM employees") # 获取查询结果 for row in cursor: print(row)在上面的示例中，我们使用 with 语句创建了一个连接上下文管理器，确保在退出代码块时自动关闭连接和游标。
总结
在本文中，我们详细介绍了如何使用 cx_Oracle 模块在 Python 中连接、查询和操作 Oracle 数据库。我们学习了如何连接到数据库、执行 SQL 查询、插入、更新和删除数据，以及使用绑定变量、事务、错误处理、连接池、批量插入、存储过程、游标变量、LOB 数据类型、连接字符串和连接上下文管理器等功能。
