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()

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 None

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"

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):

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))

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

return cursor.fetchall()

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}")

How it works.

First, import the get_customers function from the query module:

from query import get_customers

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

customers = get_customers(limit=5, offset=0)

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

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

Step 3. Run the main.py module:

1 - Stephen Johnson
2 - Norma Baxter
3 - Ryan Martin
4 - Eric Fox
5 - Brian Miller

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)

It’ll return the following customers:

6 - Heather Webb
7 - Nicole Smith
8 - Anna Wilson
9 - Katelyn Harris
10 - Justin Hayes

Download the project source code #

Download the project source code

Summary #

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

Was this tutorial helpful?