This topic describes how to connect to and use OceanBase Cloud in Oracle-compatible mode by using the Python cx_Oracle driver. cx_Oracle is an official Python database interface provided by Oracle. It is compatible with Oracle OCI and can seamlessly connect to Oracle-compatible tenants of OceanBase Cloud.
Prerequisites
- You have installed Python 3.7 or later.
- You have installed the libobclient and OBCI components.
- You have registered an OceanBase Cloud account and created a cluster instance and an Oracle-compatible tenant. For more information, see Create a cluster instance and Create a tenant.
Procedure
To connect to OceanBase Cloud in Oracle-compatible mode using the cx_Oracle driver, perform the following steps:
Step 1: Install the Python 3.7 environment
Ensure that Python 3.7 or later is installed on your system. You can check the Python version by running the following command:
python3 --version
If it is not installed, install Python 3.7 according to your operating system.
Step 2: Install the libobclient and OBCI components
Install the OceanBase client components, including libobclient and OBCI:
# Uninstall the earlier version (if any).
rpm -e libobclient
rpm -e obci
# Install the new version
rpm -ivh libobclient-2.2.11-42025062010.el7.x86_64.rpm
rpm -ivh obci-2.1.1-342025070917.el7.x86_64.rpm
Notice
Ensure that the libobclient version is greater than or equal to 2.2.11 and the obci version is greater than or equal to 2.1.1. Lower versions may cause connection failures or functional abnormalities.
Step 3: Install the cx_Oracle driver
Extract and install the cx_Oracle driver:
# Switch to the driver installation directory.
cd /directory/of/cx_Oracle-8.3.0.tar.gz
# Extract the cx_Oracle driver package.
tar -xvf cx_Oracle-8.3.0.tar.gz
# Navigate to the directory where the package is extracted
cd cx_Oracle-8.3.0
# Install the cx_Oracle driver
python3 setup.py install
After installation, the system will generate the cx_Oracle module in the /usr/local/lib/python3.7/site-packages/ directory.
Step 4: Configure environment variables
Set the LD_LIBRARY_PATH environment variable to ensure Python can find the OceanBase client libraries:
export LD_LIBRARY_PATH=/u01/obclient/lib/:$LD_LIBRARY_PATH
We recommend adding this configuration to your shell configuration file (such as ~/.bashrc or ~/.zshrc) for permanent effect.
Step 5: Write the test program
Create a file named test_oracle.py and add the following content:
import cx_Oracle
# Database connection information
username = 'oracle001'
password = '*****'
oracle_connection = 't5******.********.oceanbase.cloud:1521/sys'
# Create a database connection
conn = cx_Oracle.connect(username, password, oracle_connection)
def exec_sql(sql):
"""Execute a SQL statement."""
cur = conn.cursor()
try:
cur.execute(sql)
conn.commit()
print(f"SQL executed successfully: {sql}")
except Exception as e:
print(f"SQL execution failed: {sql}")
print(f"Error message: {e}")
finally:
cur.close()
def print_data(sql):
"""Query and print data."""
cur = conn.cursor()
try:
cur.execute(sql)
data = cur.fetchall()
print(f"Query result: {data}")
return data
except Exception as e:
print(f"Query failed: {sql}")
print(f"Error message: {e}")
return None
finally:
cur.close()
def test_char_types():
"""Test character types."""
print("\n=== Test character types ===")
exec_sql("DROP TABLE test_char")
exec_sql("""
CREATE TABLE test_char (
id INT,
a VARCHAR2(20),
b CHAR(10),
c NCHAR(10),
d NVARCHAR2(10)
)
""")
exec_sql("INSERT INTO test_char VALUES (1, 'hello', 'adffdf', '2df4d', 'dsf44f')")
print_data("SELECT * FROM test_char")
def test_number_types():
"""Test numeric types."""
print("\n=== Testing numeric types ===")
exec_sql("DROP TABLE test_number")
exec_sql("""
CREATE TABLE test_number (
a NUMBER,
b FLOAT(126),
c BINARY_FLOAT,
d BINARY_DOUBLE
)
""")
exec_sql("INSERT INTO test_number VALUES (12.32, 12.34, 14.23, 123.3433)")
print_data("SELECT * FROM test_number")
def test_time_types():
"""Test time types."""
print("\n=== Test time types ===")
exec_sql("DROP TABLE test_time")
exec_sql("""
CREATE TABLE test_time (
a DATE,
b TIMESTAMP,
c TIMESTAMP WITH TIME ZONE,
d TIMESTAMP WITH LOCAL TIME ZONE
)
""")
exec_sql("""
INSERT INTO test_time VALUES (
TIMESTAMP'2022-08-29 14:44:30',
TIMESTAMP'2022-08-29 14:44:30',
TIMESTAMP'2022-08-29 14:44:30',
TIMESTAMP'2022-08-29 14:44:30'
)
""")
print_data("SELECT * FROM test_time")
def test_lob_types():
"""Test LOB types."""
print("\n=== Test LOB type ===")
exec_sql("DROP TABLE test_lob")
exec_sql("""
CREATE TABLE test_lob (
a CLOB,
b BLOB,
c RAW(100)
)
""")
exec_sql("INSERT INTO test_lob VALUES ('sdfdslkfjldsf', '31323334353637', '31323334')")
print_data("SELECT * FROM test_lob")
def main():
"""Run the tests."""
try:
print("Starting test for connecting to OceanBase Cloud Database in Oracle-compatible mode...")
# Test various data types
test_char_types()
test_number_types()
test_time_types()
test_lob_types()
print("\nAll tests completed!")
except Exception as e:
print(f"An error occurred during the test: {e}")
finally:
# Close database connection
if conn:
conn.close()
print("Database connection is closed")
if __name__ == "__main__":
main()
Step 6: Run the test program
Run the test program in the command line:
python3 test_oracle.py
The program will test data operations for character, numeric, time, and LOB types in sequence and output the execution results.
Connection pool support
Connection pool functionality is supported starting from OBCI 2.1.1. You can use a connection pool to improve application performance and concurrent processing capabilities.
import cx_Oracle
from cx_Oracle import SessionPool
def test_simple_pool():
"""Connection Pool Test"""
try:
print("Creating connection pool...")
# Create a connection pool
pool = SessionPool(
user="oracle001",
password="******",
dsn="t5******.********.oceanbase.cloud:1521/sys",
min=2,
max=5,
increment=1
)
print(f"Connection pool created successfully!")
print(f"- Minimum connections: {pool.min}")
print(f"- Maximum connections: {pool.max}")
print(f"- Increment: {pool.increment}")
print("\nTesting connection pool usage...")
# Obtain a connection from the connection pool
with pool.acquire() as connection:
with connection.cursor() as cursor:
# Execute query
cursor.execute("SELECT COUNT(*) FROM test_char")
result = cursor.fetchone()
print(f"Number of records in the test_char table: {result[0]}")
# Execute another query
cursor.execute("SELECT * FROM test_char WHERE id = 1")
data = cursor.fetchone()
print(f"Query result: {data}")
print(f"\nConnection pool status:")
print(f"- Number of connections currently open: {pool.opened}")
print(f"- Number of busy connections: {pool.busy}")
print("\nConnection pool test completed successfully!")
# Note: Do not call pool.close() to avoid dangling pointers.
except Exception as e:
print(f"An error occurred: {e}")
if __name__ == "__main__":
test_simple_pool()