Python Oracle

This section shows you how to access the Oracle Database from Python using the python-oracledb package.

The python-oracledb package is designed to conform to the Python database API 2.0 specification. It also provides you with a number of additions designed specifically for the Oracle Database.

The python-oracledb works perfectly fine with Python version 3.9 to 3.13 on Oracle Database 23ai, 21, 19c, 18c, 12c, and 11gR2.

We assume that you already have basic Python programming. If you don’t, we recommend the Python tutorial.

Setting up sample tables #

We’ll create two tables billing_headers and billing_items for the demonstration.

Creating the billing_headers table:

CREATE TABLE billing_headers(
    billing_no NUMBER GENERATED BY DEFAULT AS IDENTITY,
    billing_date DATE NOT NULL,
    amount NUMBER(19,4) DEFAULT 0 NOT NULL,
    customer_id NUMBER NOT NULL,
    note VARCHAR2(100),
    PRIMARY KEY(billing_no)
);Code language: SQL (Structured Query Language) (sql)

Creating the billing_items table:

CREATE TABLE billing_items(
    item_no NUMBER 
        GENERATED BY DEFAULT AS IDENTITY 
        START WITH 10 
        INCREMENT BY 10,
    billing_no NUMBER NOT NULL,
    product_id NUMBER NOT NULL,
    price NUMBER(10,2) DEFAULT 0 NOT NULL,
    PRIMARY KEY(item_no, billing_no),
    FOREIGN KEY(billing_no) 
        REFERENCES billing_headers(billing_no)
);Code language: SQL (Structured Query Language) (sql)

Python Oracle Tutorials #

• Connecting to Oracle Database in Python – learn how to connect to Oracle from Python using stand-alone and pooled connections.
• Querying data using fetchone(), fetchmany(), and fetchall() methods – show you how to query data using various methods of the Cursor object.
• Using bind variables to pass data to and from Oracle Database – learn how to use bind variables to pass data to and get data back from Oracle Database.
• Inserting data – show you how to insert one or more rows into a table.
• Updating data – walk you through the steps of updating data in a table.
• Deleting data – guide you on how to delete data from a table in a Python program.
• Managing Transactions – learn how to manage Oracle Database transactions in Python.
• Calling PL/SQL stored procedures in Python – show you how to call a PL/SQL procedure from a Python program.
• Calling PL/SQL stored functions in Python – learn how to call a stored function in Python.