Querying Data in Oracle Database From Python

Summary: in this tutorial, you will learn how to select data from Oracle Database using fetchone() and fetchall() methods.

To select data from the Oracle Database in a Python program, you follow these steps:

• First, establish a connection to the Oracle Database using the oracledb.connect() method.
• Second, create a Cursor object from the Connection object using the Connection.cursor() method.
• Third, execute an SQL statement to select data from one or more tables using the Cursor.execute() method.
• Fourth, fetch rows using the Cursor.fetchone() and Cursor.fetchall() methods.
• Finally, release the Cursor and Connection objects using the Cursor.close() and Connection.Close() method. If you want to release the Cursor and Connection automagically, you can use the with block.

We’ll use the customers table from the sample database:

and the following config.py module:

username = 'OT'
password = 'oracle'
dsn = '192.168.2.18/freepdb1'Code language: Python (python)

Querying data using the Cursor.fetchone() method #

The following fetchone.py illustrates how to select data from the customers table:

from typing import  Optional
import oracledb
import logging
from customer import Customer
from connect import connect


def find_by_id(customer_id: int) -> Optional[Customer]:

    sql = ''' SELECT customer_id, name
              FROM customers
              WHERE customer_id = :customer_id '''

    try:
        with connect() as connection:
            with connection.cursor() as cursor:
                # Pass the parameter using a dictionary
                cursor.execute(sql, {'customer_id': customer_id})
                row = cursor.fetchone()
                if row:
                    # Create a Customer object from the fetched row
                    return Customer(customer_id=row[0], name=row[1])
                return None # Return None if no customer is found
    except oracledb.Error as e:
        logging.error(f"Error executing query to find customer by ID: {e}")
        raiseCode language: Python (python)

Even though the Cursor.fetchone() returns a single row at a time, it always retrieves data from Oracle Database in batches with the batch size defaults to Cursor.arraysize.

To improve the performance, you can tweak the value of Cursor.arraysize before calling the Cursor.execute() method.

Note that increasing the value of Cursor.arraysize help reduces the number of round-trips to the database. However, it increases the amount of memory required.

Querying data using the Cursor.fetchall() method #

If the number of rows is small and can fit into the memory, you can use the Cursor.fetchall() method:

from typing import List
import oracledb
import logging
from customer import Customer
from connect import connect


def find_all_customers() -> List[Customer]:
    sql = ''' SELECT customer_id, name
              FROM customers
              ORDER BY name '''

    customers: List[Customer] = []
    try:
        with connect() as connection:
            with connection.cursor() as cursor:
                cursor.execute(sql)
                # Use fetchall() to get all rows at once
                rows = cursor.fetchall()
                
                if rows:
                    for row in rows:
                        # Create Customer objects from fetched tuple rows
                        customers.append(Customer(customer_id=row[0], name=row[1]))
        return customers
    except oracledb.Error as e:
        logging.error(f"Error executing query to find all customers: {e}")
        raiseCode language: Python (python)

In this tutorial, you have learned how to use the fetchone() and fetchall() methods of the Cursor object to fetch data from the Oracle Database.