Python - Database Select Records

Retrieving records from a database table is one of the most important operations in Python Database Programming. In these applications, we will learn how to retrieve employee information from the employees table using three different Cursor methods.

• App9 - using fetchone() to retrieve employee records one by one.
• App10 - using fetchall() to retrieve all employee records at once.
• App11 - using fetchmany() to retrieve a specified number of employee records at a time.

Each application first executes a SELECT query using cursor.execute() and then uses a fetch method to retrieve the resulting records.

employees Table
      │
      ▼
SELECT * FROM employees
      │
      ▼
cursor.execute()
      │
      ▼
    Result Set
      │
      ├── fetchone()   → One Row at a Time
      ├── fetchmany()  → Specified Number of Rows
      └── fetchall()   → All Rows at Once
        

The SQL SELECT statement is used to retrieve data from a Database table.

General Syntax

SELECT column1, column2, ...
FROM table_name;
        

To retrieve all columns:

SELECT *
FROM table_name;
        

In our applications, we use:

SELECT * FROM employees;
        

It retrieves all columns and all available rows from the employees table.

Here:

SQL Part	Meaning
SELECT	Retrieve data
*	Select all columns
FROM	Specifies the table
employees	Table from which records are retrieved

In this application, we will learn how to retrieve employee information from the employees table using the fetchone() method.

The program first executes a SELECT query and then repeatedly calls fetchone() to retrieve employee records one by one.

This application demonstrates:

• Using the SQL SELECT statement
• Using cursor.execute()
• Using fetchone()
• Fetching one row at a time
• Using while row is not None
• Processing the complete result set
• Handling Oracle Database errors
• Closing the Cursor and Connection

The program performs the following operations:

Import cx_Oracle
      │
      ▼
Create Connection
      │
      ▼
Create Cursor
      │
      ▼
Execute SELECT Query
      │
      ▼
fetchone()
      │
      ▼
Row Available?
   /         Yes        No
  │          │
  ▼          ▼
Print Row   Stop
  │
  ▼
fetchone()
  │
  └────────────► Repeat
        

No output captured.

The fetchone() method is used to fetch only one row from the result set.

Each time fetchone() is called, it returns the next available row.

row = cursor.fetchone()
        

If another row is available:

fetchone()
    │
    ▼
Next Row
        

If no more rows are available:

fetchone()
    │
    ▼
None
        

Therefore, None indicates that the complete result set has been processed.

Suppose the result set contains four employee records:

Employee 1
Employee 2
Employee 3
Employee 4
        

Repeated calls to fetchone() behave like this:

Call	Returned Value
1st fetchone()	Employee 1
2nd fetchone()	Employee 2
3rd fetchone()	Employee 3
4th fetchone()	Employee 4
5th fetchone()	None

The first row is retrieved using:

row = cursor.fetchone()
        

The fetchone() method retrieves one row from the result set and stores it in the variable row.

For example:

row = (100, 'Sachin', 1000, 'Mumbai')
        

A Database row is normally represented as a tuple.

The values in the row tuple can be understood as:

Index	Column	Example
row[0]	Employee Number	100
row[1]	Employee Name	Sachin
row[2]	Employee Salary	1000
row[3]	Employee Address	Mumbai

The program uses a while loop to process every fetched row:

while row is not None:
    print(row)
    row = cursor.fetchone()
        

The loop continues as long as row contains an employee record. When row becomes None, the condition becomes false and the loop terminates.

We do not know how many employee records may be available in the Database, so the program keeps fetching rows until fetchone() returns None. This makes the program work whether the table contains:

1 employee
10 employees
100 employees
1000 employees
        

The loop ends automatically after the last available row.

How fetchone() Works

SELECT Query
     │
     ▼
fetchone()
     │
     ▼
First Row
     │
     ▼
Print Row
     │
     ▼
fetchone()
     │
     ▼
Second Row
     │
     ▼
Print Row
     │
     ▼
   ...
     │
     ▼
fetchone()
     │
     ▼
None
     │
     ▼
Loop Ends
        

Suppose the employees table contains:

+-----+--------+------+--------+
| eno | ename  | esal | eaddr  |
+-----+--------+------+--------+
| 100 | Sachin | 1000 | Mumbai |
| 200 | Dhoni  | 2000 | Ranchi |
| 300 | Kohli  | 3000 | Delhi  |
+-----+--------+------+--------+
        

The query:

SELECT * FROM employees;
        

creates a result set containing these three rows.

fetchone() Call 1

The first call to fetchone() returns (100, 'Sachin', 1000, 'Mumbai'). The condition row is not None is true, so print(row) prints that record.

fetchone() Call 2

At the end of the first loop iteration, fetchone() returns the second record (200, 'Dhoni', 2000, 'Ranchi'). The loop condition is still true, so the second employee is printed.

fetchone() Call 3

The next call returns (300, 'Kohli', 3000, 'Delhi') and the third employee record is printed.

fetchone() Call 4 — No More Records

After processing the third employee, no more rows are available. row becomes None, so row is not None becomes False and the while loop terminates.

Sample Output

(100, 'Sachin', 1000, 'Mumbai')
(200, 'Dhoni', 2000, 'Ranchi')
(300, 'Kohli', 3000, 'Delhi')
        

Each employee record is displayed one at a time.

What Happens if the Table is Empty?

If the employees table contains no records, the SELECT query returns an empty result set. The first call to fetchone() returns None, so the while condition is false immediately and the loop body does not execute.

SELECT
  │
  ▼
No Rows
  │
  ▼
fetchone()
  │
  ▼
None
  │
  ▼
Loop Does Not Execute
        

The same program with comments for each step:

No output captured.

This application only retrieves data using SELECT. It does not insert, update, or delete employee records.

Therefore, the program does not call:

con.commit()
        

Compare:

Operation	Changes Data?	commit() Normally Required?
INSERT	Yes	Yes
UPDATE	Yes	Yes
DELETE	Yes	Yes
SELECT	No	No

The program handles Oracle Database errors using:

except cx_Oracle.DatabaseError as e:
        

If a Database error occurs, execution moves to the except block. The exception information is stored in e.

The exception block contains a rollback:

if con:
    con.rollback()
        

If a Database error occurs, temporary uncommitted changes associated with the transaction can be rolled back.

The error is displayed using:

print("There is a problem with sql :", e)
        

The actual Oracle Database error information is available in the exception object e.

The finally block executes whether the program succeeds or an exception occurs. Its purpose is to release the Database resources.

finally:
    if cursor:
        cursor.close()

    if con:
        con.close()
        

       try
        │
   ┌────┴────┐
   │         │
Success    Exception
   │         │
   │       except
   │         │
   └────┬────┘
        ▼
     finally
        │
        ▼
Close Resources
        

The Cursor is closed using cursor.close(), which releases the Cursor resource after all employee records have been processed.

The Database connection is closed using con.close(), which releases the connection with Oracle Database.

In the previous application, we used fetchone() to retrieve employee records one by one.

In this application, we will learn how to retrieve all employee records at once from the employees table using the fetchall() method.

The program first executes a SELECT query and then calls fetchall() to retrieve all remaining rows from the result set.

What is fetchall()?

The fetchall() method is used to retrieve all remaining rows from the result set.

rows = cursor.fetchall()
        

After executing a SELECT query, fetchall() collects all available rows and returns them together.

SELECT Query
     │
     ▼
Result Set
     │
     ├── Row 1
     ├── Row 2
     ├── Row 3
     └── Row 4
          │
          ▼
      fetchall()
          │
          ▼
[Row 1, Row 2, Row 3, Row 4]
        

Each individual row is normally represented as a tuple.

Complete Program

The complete program performs the following operations:

Import cx_Oracle
      │
      ▼
Connect to Oracle Database
      │
      ▼
Create Cursor
      │
      ▼
Execute SELECT Query
      │
      ▼
fetchall()
      │
      ▼
Store All Rows in data
      │
      ▼
for row in data
      │
      ▼
Print Each Row
      │
      ▼
Close Resources
        

No output captured.

After executing the SELECT query, the program calls:

data = cursor.fetchall()
        

fetchall() retrieves all remaining rows from the query result and stores the returned collection in data.

For example:

data = [
    (100, 'Sachin', 1000, 'Mumbai'),
    (200, 'Dhoni', 2000, 'Ranchi'),
    (300, 'Kohli', 3000, 'Delhi')
]
        

• The outer collection contains all employee records.
• Each inner tuple represents one employee record.
• Each value inside a tuple represents one column value.

The program uses a for loop to process every record:

for row in data:
    print(row)
        

During every iteration, one employee tuple from data is assigned to row. The loop automatically stops after processing the final employee record.

Suppose the employees table contains:

+-----+--------+------+--------+
| eno | ename  | esal | eaddr  |
+-----+--------+------+--------+
| 100 | Sachin | 1000 | Mumbai |
| 200 | Dhoni  | 2000 | Ranchi |
| 300 | Kohli  | 3000 | Delhi  |
| 400 | Rohit  | 4000 | Mumbai |
+-----+--------+------+--------+
        

After data = cursor.fetchall(), the conceptual value of data is:

[
    (100, 'Sachin', 1000, 'Mumbai'),
    (200, 'Dhoni', 2000, 'Ranchi'),
    (300, 'Kohli', 3000, 'Delhi'),
    (400, 'Rohit', 4000, 'Mumbai')
]
        

The for loop executes four iterations, printing each employee record. The output will be similar to:

(100, 'Sachin', 1000, 'Mumbai')
(200, 'Dhoni', 2000, 'Ranchi')
(300, 'Kohli', 3000, 'Delhi')
(400, 'Rohit', 4000, 'Mumbai')
        

Individual column values can be accessed using indexes:

Expression	Meaning	Example Value
row[0]	Employee Number	100
row[1]	Employee Name	Sachin
row[2]	Employee Salary	1000
row[3]	Employee Address	Mumbai

What Happens if the Table is Empty?

If the employees table does not contain any records, fetchall() returns an empty collection:

[]
        

The for loop does not execute even once.

Instead of printing the complete tuple with print(row), we can access and display the individual column values of each employee:

Employee Number : 100
Employee Name   : Sachin
Employee Salary : 1000
Employee Address: Mumbai

The complete App10 program with comments for each step:

No output captured.

In the previous applications, we learned how to retrieve Database records using fetchone() and fetchall().

In this application, we will learn how to retrieve a specified number of employee records at a time using the fetchmany() method.

The fetchmany() method is useful when we do not want to retrieve all records at once.

What is fetchmany()?

The fetchmany() method is used to retrieve a specified number of rows from the result set.

cursor.fetchmany(size)
        

Here, size represents the maximum number of rows that should be fetched.

Example:

data = cursor.fetchmany(3)
        

This asks the Cursor to fetch up to 3 rows from the current position of the result set.

Statement	Maximum Rows Requested
fetchmany(1)	1
fetchmany(2)	2
fetchmany(5)	5
fetchmany(10)	10

Why do we use fetchmany()?

fetchmany() provides a middle approach between fetchone() and fetchall().

fetchone()
    │
    ▼
One Row

fetchmany(n)
    │
    ▼
Specified Number of Rows

fetchall()
    │
    ▼
All Remaining Rows
        

If a query returns many records, we may not want to load all of them at once. With fetchmany(), records can be processed in smaller batches.

Complete Program

The program performs the following operations:

Import cx_Oracle
      │
      ▼
Connect to Oracle Database
      │
      ▼
Create Cursor
      │
      ▼
Execute SELECT Query
      │
      ▼
fetchmany(3)
      │
      ▼
Fetch Up to 3 Records
      │
      ▼
Store Records in data
      │
      ▼
for row in data
      │
      ▼
Print Each Row
      │
      ▼
Close Resources
        

No output captured.

Suppose the query result contains six employee records:

Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
        

When we execute:

data = cursor.fetchmany(3)
        

the first three rows are returned:

Row 1
Row 2
Row 3
        

The Cursor position moves forward.

Before fetchmany(3)

Cursor
  │
  ▼
Row 1
Row 2
Row 3
Row 4
Row 5
Row 6


After fetchmany(3)

Fetched:
Row 1
Row 2
Row 3

Remaining:
Row 4
Row 5
Row 6
        

A very important point is that fetching records changes the current Cursor position in the result set. The next fetch operation starts from Row 4.

Row 1  ✓ Fetched
Row 2  ✓ Fetched
Row 3  ✓ Fetched

------------------

Row 4  ← Next Cursor Position
Row 5
Row 6
        

Calling fetchmany() Multiple Times

fetchmany() can be called multiple times on the same result set:

data1 = cursor.fetchmany(3)
data2 = cursor.fetchmany(3)
        

The first call retrieves Rows 1, 2 and 3. The second call continues from the current Cursor position and retrieves Rows 4, 5 and 6.

Result Set
   │
   ├── Row 1 ─┐
   ├── Row 2  ├── First fetchmany(3)
   ├── Row 3 ─┘
   │
   ├── Row 4 ─┐
   ├── Row 5  ├── Second fetchmany(3)
   └── Row 6 ─┘
        

First Batch:
(100, 'Sachin', 1000, 'Mumbai')
(200, 'Dhoni', 2000, 'Ranchi')
(300, 'Kohli', 3000, 'Delhi')

Second Batch:
(400, 'Rohit', 4000, 'Mumbai')
(500, 'Rahul', 5000, 'Bangalore')
(600, 'Hardik', 6000, 'Baroda')

The number passed to fetchmany() represents the maximum number of rows requested.

Suppose only two rows remain:

Row 5
Row 6
        

and we execute:

data = cursor.fetchmany(3)
        

Only the two remaining records are returned.

Requested = 3
Available = 2

Returned = 2
        

fetchmany() does not create an error simply because fewer rows are available.

Example — Fewer Rows than Requested

Suppose five employees exist and we fetch records in groups of three.

Total Records = 5
Batch Size    = 3
        

First Call

cursor.fetchmany(3)

Returns:
Row 1
Row 2
Row 3
        

Second Call

cursor.fetchmany(3)

Returns:
Row 4
Row 5
        

Although three records were requested, only two records remained.

What Happens When No Records Remain?

After all records have been fetched, another call to fetchmany() returns an empty collection:

data = []
        

Example — Complete Batch Fetching

Suppose there are seven employee records and the batch size is three:

fetchmany(3) → Row 1, Row 2, Row 3
fetchmany(3) → Row 4, Row 5, Row 6
fetchmany(3) → Row 7
fetchmany(3) → []
        

This shows how fetchmany() can be used to process a result set in batches.

We can repeatedly call fetchmany() until it returns an empty collection:

while True:
    data = cursor.fetchmany(3)

    if not data:
        break

    for row in data:
        print(row)
        

This approach retrieves employee records in groups of three until the complete result set has been processed.

                    START
                      │
                      ▼
             Execute SELECT Query
                      │
                      ▼
               fetchmany(3)
                      │
                      ▼
               data Available?
                 /                         Yes           No
                │             │
                ▼             ▼
          for row in data    break
                │             │
                ▼             │
            print(row)        │
                │             │
                ▼             │
         Process All Rows     │
           in Current Batch   │
                │             │
                └─────┐       │
                      │       │
                      ▼       │
               fetchmany(3)   │
                      │       │
                      └───────┘
                          │
                          ▼
                         END
        

No output captured.

The complete App11 program with comments for each step:

No output captured.

All three methods work with records produced by a SELECT query, but they differ in how many rows they retrieve at one time.

Feature	fetchone()	fetchmany()	fetchall()
Purpose	Fetch one row	Fetch specified number of rows	Fetch all remaining rows
Example	fetchone()	fetchmany(3)	fetchall()
Return	Single row	Collection of rows	Collection of rows
No More Rows	None	Empty collection	Empty collection
Batch Size	1	Specified by programmer	All remaining rows
Large Result Sets	Can process one by one	Useful for batch processing	Can require more memory

Visual Comparison

Result Set:

R1  R2  R3  R4  R5  R6  R7
│
├── fetchone()
│      │
│      └── R1
│
├── fetchmany(3)
│      │
│      └── R1 R2 R3
│
└── fetchall()
       │
       └── R1 R2 R3 R4 R5 R6 R7
        

fetchall() is simple when we want to retrieve all records together. However, it retrieves all remaining rows into memory. If a query returns a very large number of records, processing records in smaller batches with fetchmany() or one by one with fetchone() can be more memory-friendly.

Methods Used

Method	Purpose
connect()	Establishes a connection with Oracle Database
cursor()	Creates a Cursor object
execute()	Executes the SELECT query
fetchone()	Retrieves one row at a time from the result set
fetchmany(size)	Retrieves up to the specified number of rows
fetchall()	Retrieves all remaining records
rollback()	Rolls back uncommitted changes when applicable
close()	Closes Database resources