Querying Data Using fetchone(), fetchmany(), and fetchall() Methods

Summary: in this tutorial, you will learn how to select data from Oracle Database using fetchone(), fetchmany(), 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 cx_Oracle.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(), Cursor.fetchmany(), 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 will use the customers table from the sample database:

and the following config.py module:

username = 'OT'
password = '<password>'
dsn = 'localhost/pdborcl'
port = 1512
encoding = 'UTF-8'
Code language: Python (python)

Querying data using the Cursor.fetchone() method

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

import cx_Oracle
import config

sql = 'select customer_id, name ' \
    'from customers ' \
    'order by name'
try:
    with cx_Oracle.connect(
                config.username,
                config.password,
                config.dsn,
                encoding=config.encoding) as connection:
        with connection.cursor() as cursor:
            cursor.execute(sql)
            while True:
                row = cursor.fetchone()
                if row is None:
                    break
                print(row)
except cx_Oracle.Error as error:
    print(error)
Code 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.fetchmany() method

If you want to process rows in batches, you can use the Cursor.fetchmany() method. In this case, you pass the batch size to the Cursor.fetchmany() method. The batch size defaults to Cursor.arraysize:

import cx_Oracle
import config

sql = 'select customer_id, name ' \
    'from customers ' \
    'order by name'
batch_size = 20
try:
    with cx_Oracle.connect(
            config.username,
            config.password,
            config.dsn,
            encoding=config.encoding) as connection:
        with connection.cursor() as cursor:
            # execute the SQL statement
            cursor.execute(sql)
            while True:
                # fetch rows
                rows = cursor.fetchmany(batch_size)
                if not rows:
                    break
                # display rows
                for row in rows:
                    print(row)
except cx_Oracle.Error as error:
    print(error)
Code language: Python (python)

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:

import cx_Oracle
import config

sql = 'select customer_id, name ' \
    'from customers ' \
    'order by name'

try:
    # connect to the Oracle Database
    with cx_Oracle.connect(
            config.username,
            config.password,
            config.dsn,
            encoding=config.encoding) as connection:
        with connection.cursor() as cursor:
            # execute the SQL statement
            cursor.execute(sql)
            # fetch all rows
            rows = cursor.fetchall()
            if rows:
                for row in rows:
                    print(row)
except cx_Oracle.Error as error:
    print(error)Code language: Python (python)

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

Was this tutorial helpful?