This section shows you how to access the Oracle Database from Python using the cx_Oracle module.
The cx_Oracle 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 cx_Oracle 7.x works perfectly fine with Python version 2.7, and with versions from 3.5 to 3.7. In this section, we will only use the Python version from 3.5 to 3.7. The cx_Oracle can work with Oracle 11.2, 12.1, 12.2, 18.3, and 19.3 client libraries.
We assume that you already know Python programming. If you don’t, we recommend the Python tutorial.
Setting up sample tables
We’ll create two tables
billing_items for the demonstration.
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) );
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) );
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 use 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.