Python cx_Oracle|极客教程

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 = 1
cursor.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_Oracle

try:
    # 连接到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_Oracle
from 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 = 1
name = 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 数据类型、连接字符串和连接上下文管理器等功能。