Python Oracle CRUD with python-oracledb

Use Oracle’s python-oracledb driver to perform CRUD (create, read, update, and delete) operations from Python. The examples below use named bind variables and commit each write explicitly.

Install the Python driver

Install the oracledb package in your project’s Python environment:

python -m pip install oracledb

The module is imported as oracledb. In its default Thin mode, the driver connects directly to Oracle Database and does not need Oracle Client libraries. Thin mode supports Oracle Database 12.1 and later. See the official python-oracledb installation guide.

Create a table

Run this statement once in the Oracle schema used by your application. It creates an identity primary key and a unique email address:

CREATE TABLE contacts (
  contact_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  full_name VARCHAR2(100) NOT NULL,
  email VARCHAR2(320) NOT NULL UNIQUE
);

Connect to Oracle Database

Replace the username and Easy Connect string with values for your database. The string localhost:1521/FREEPDB1 means host localhost, listener port 1521, and service name FREEPDB1.

import getpass
import oracledb

connection = oracledb.connect(
    user="app_user",
    password=getpass.getpass("Database password: "),
    dsn="localhost:1521/FREEPDB1",
)

The connection string must identify a database service that your listener exposes. See the official connection guide if your host, port, or service name differs.

Create: insert a row

Pass values separately from the SQL statement. Oracle bind variables use a colon followed by the parameter name:

with connection.cursor() as cursor:
    cursor.execute(
        """
        INSERT INTO contacts (full_name, email)
        VALUES (:full_name, :email)
        """,
        {"full_name": "Ada Lovelace", "email": "ada@example.com"},
    )

connection.commit()

Read: fetch matching rows

Use fetchone() for one row or iterate over the cursor for multiple results:

with connection.cursor() as cursor:
    cursor.execute(
        """
        SELECT contact_id, full_name, email
        FROM contacts
        WHERE email = :email
        """,
        {"email": "ada@example.com"},
    )
    contact = cursor.fetchone()

print(contact)

The result is a tuple in the order of the selected columns. If no row matches, fetchone() returns None.

Update: change an existing row

Use the primary key in the WHERE clause so the update targets one contact:

with connection.cursor() as cursor:
    cursor.execute(
        """
        UPDATE contacts
        SET full_name = :full_name
        WHERE contact_id = :contact_id
        """,
        {"full_name": "Ada Byron", "contact_id": 1},
    )
    rows_updated = cursor.rowcount

connection.commit()
print(rows_updated)

rowcount is 0 when no contact has that ID.

Delete: remove a row

This example deletes the contact by its unique email address:

with connection.cursor() as cursor:
    cursor.execute(
        "DELETE FROM contacts WHERE email = :email",
        {"email": "ada@example.com"},
    )
    rows_deleted = cursor.rowcount

connection.commit()
print(rows_deleted)

Close the connection when the application is finished with it:

connection.close()

Use bind variables and transactions

Do not build SQL by formatting user input into the query string. Use bind variables for values, as in the examples above. This keeps data separate from SQL text and allows Oracle to reuse parsed statements. The python-oracledb bind variable guide covers named and positional binds.

DML changes are not committed automatically. Group related writes in a transaction and roll them back if an operation fails:

try:
    with connection.cursor() as cursor:
        cursor.execute(
            "UPDATE contacts SET full_name = :name WHERE contact_id = :id",
            {"name": "Ada Lovelace", "id": 1},
        )
        cursor.execute(
            "DELETE FROM contacts WHERE email = :email",
            {"email": "old-address@example.com"},
        )
    connection.commit()
except oracledb.Error:
    connection.rollback()
    raise
finally:
    connection.close()

commit() and rollback() are methods on the connection, not the cursor. See the python-oracledb SQL execution and transaction guidance.

For a set-based Oracle insert-or-update pattern, see Oracle UPSERT with MERGE.