Python SQL Server: Pagination

Summary: in this tutorial, you will learn how to paginate data in SQL Server from a Python program using the FETCH and OFFSET clauses.

This tutorial begins where the Selecting data from a table in the Python program tutorial left off.

Paginating data

Step 1. Define a new function get_customers() in the query.py module:

def get_customers(limit: int, offset: int = 0) -> list[dict] | None:
    # Connect to the SQL Server
    conn = create_connection()
    if conn is None:
        return None

    sql = f"""SELECT * FROM Customers ORDER BY CustomerID 
            OFFSET %s ROWS FETCH FIRST %s ROWS ONLY"""
    
    # Execute the query
    with (conn, conn.cursor(as_dict=True) as cursor):
        cursor.execute(sql, (offset, limit))
        return cursor.fetchall()Code language: Python (python)

How it works.

First, connect to the SQL Server database by calling the create_connection() function. If the connection fails, return None immediately:

conn = create_connection()
if conn is None:
    return NoneCode language: Python (python)

Second, construct an SQL statement that retrieves data from the Customers table:

sql = f"SELECT * FROM Customers ORDER BY CustomerID OFFSET %s ROWS FETCH FIRST %s ROWS ONLY"Code language: Python (python)

In the query, the %s placeholders are parameters that will be replaced by actual values during query execution.

Third, use the with statement to manage the Connection and Cursor objects properly. This eliminates the need to manually close the Cursor and Connection objects and handle exceptions.

with (conn, conn.cursor(as_dict=True) as cursor):Code language: Python (python)

Setting as_dict=True will return the query results as a list of dictionaries, where each dictionary represents a row from the result set.

In this case, the keys of the dictionaries correspond to the column names specified in the SELECT statement.

Fourth, execute the query with the offset and limit variables:

cursor.execute(sql, (offset, limit))Code language: Python (python)

Fifth, fetch all rows of the query result set and return a list of dictionary objects:

return cursor.fetchall()Code language: Python (python)

Step 2. Modify the main.py module to fetch the first five customers from the Customers table:

import logging, sys
from query import get_customers

# config logging to console
logging.basicConfig(
    stream=sys.stdout, 
    encoding='utf-8', 
    format='%(levelname)s:%(message)s',
    level=logging.DEBUG
)

# Get customers
try:
    customers = get_customers(limit=5, offset=0)
    for c in customers:
        print(f'{c["CustomerID"]} - {c["FirstName"]} {c["LastName"]}')
except Exception as e:
    logging.error(f"Error fetching customers: {e}")       Code language: Python (python)

How it works.

First, import the get_customers function from the query module:

from query import get_customersCode language: Python (python)

Second, call the get_customers function to the first five customers:

customers = get_customers(limit=5, offset=0)Code language: Python (python)

Third, display the CustomerID, FirstName, and LastName of retrieved customers:

for c in customers:
   print(f'{c["CustomerID"]} - {c["FirstName"]} {c["LastName"]}')Code language: Python (python)

Step 3. Run the main.py module:

1 - Stephen Johnson
2 - Norma Baxter
3 - Ryan Martin
4 - Eric Fox
5 - Brian MillerCode language: Python (python)

If you want to get the next five customers, you can set the new offset value like this:

customers = get_customers(limit=5, offset=5)Code language: Python (python)

It’ll return the following customers:

6 - Heather Webb
7 - Nicole Smith
8 - Anna Wilson
9 - Katelyn Harris
10 - Justin HayesCode language: Python (python)

Download the project source code

Summary

Execute the SELECT statement with the OFFSET and FETCH clauses to paginate data.

Was this tutorial helpful?