{"id":4126,"date":"2024-07-24T08:55:46","date_gmt":"2024-07-24T01:55:46","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4126"},"modified":"2024-07-24T20:59:31","modified_gmt":"2024-07-24T13:59:31","slug":"python-sql-server-pagination","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/python-sql-server\/python-sql-server-pagination\/","title":{"rendered":"Python SQL Server: Pagination"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to paginate data in SQL Server from a Python program using the FETCH<\/code> and OFFSET<\/code> clauses.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0Selecting data from a table in the Python program<\/a> tutorial left off.<\/p>\n\n\n\n

Paginating data #<\/a><\/h2>\n\n\n\n

Step 1. Define a new function get_customers()<\/code> in the query.py<\/code> module:<\/p>\n\n\n

def<\/span> get_customers<\/span>(limit: int, offset: int = 0<\/span>)<\/span> -> list[dict] | None<\/span>:<\/span>\n    # Connect to the SQL Server<\/span>\n    conn = create_connection()\n    if<\/span> conn is<\/span> None<\/span>:\n        return<\/span> None<\/span>\n\n    sql = f\"\"\"SELECT * FROM Customers ORDER BY CustomerID \n            OFFSET %s ROWS FETCH FIRST %s ROWS ONLY\"\"\"<\/span>\n    \n    # Execute the query<\/span>\n    with<\/span> (conn, conn.cursor(as_dict=True<\/span>) as<\/span> cursor):\n        cursor.execute(sql, (offset, limit))\n        return<\/span> cursor.fetchall()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, connect to the SQL Server database by calling the create_connection()<\/code> function. If the connection fails, return None immediately:<\/p>\n\n\n
conn = create_connection()\nif<\/span> conn is<\/span> None<\/span>:\n    return<\/span> None<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, construct an SQL statement that retrieves data from the Customers<\/code> table:<\/p>\n\n\n
sql = f\"SELECT * FROM Customers ORDER BY CustomerID OFFSET %s ROWS FETCH FIRST %s ROWS ONLY\"<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In the query, the %s<\/code> placeholders are parameters that will be replaced by actual values during query execution.<\/p>\n\n\n\n
with<\/span> (conn, conn.cursor(as_dict=True<\/span>) as<\/span> cursor):<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Setting as_dict=True<\/code> will return the query results as a list of dictionaries, where each dictionary represents a row from the result set.<\/p>\n\n\n\n
In this case, the keys of the dictionaries correspond to the column names specified in the SELECT<\/code> statement.<\/p>\n\n\n\n
Fourth, execute the query with the offset<\/code> and limit<\/code> variables:<\/p>\n\n\n
cursor.execute(sql, (offset, limit))<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, fetch all rows of the query result set and return a list of dictionary objects:<\/p>\n\n\n
return<\/span> cursor.fetchall()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the main.py<\/code> module to fetch the first five customers from the Customers table:<\/p>\n\n\n
import<\/span> logging, sys\nfrom<\/span> query import<\/span> get_customers\n\n# config logging to console<\/span>\nlogging.basicConfig(\n    stream=sys.stdout, \n    encoding='utf-8'<\/span>, \n    format='%(levelname)s:%(message)s'<\/span>,\n    level=logging.DEBUG\n)\n\n# Get customers<\/span>\ntry<\/span>:\n    customers = get_customers(limit=5<\/span>, offset=0<\/span>)\n    for<\/span> c in<\/span> customers:\n        print(f'{c[\"CustomerID\"<\/span>]}<\/span> - {c[\"FirstName\"<\/span>]}<\/span> {c[\"LastName\"<\/span>]}<\/span>'<\/span>)\nexcept<\/span> Exception as<\/span> e:\n    logging.error(f\"Error fetching customers: {e}<\/span>\"<\/span>)       <\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the get_customers<\/code> function from the query<\/code> module:<\/p>\n\n\n
from<\/span> query import<\/span> get_customers<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, call the get_customers<\/code> function to the first five customers:<\/p>\n\n\n
customers = get_customers(limit=5<\/span>, offset=0<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, display the CustomerID<\/code>, FirstName<\/code>, and LastName<\/code> of retrieved customers:<\/p>\n\n\n
for<\/span> c in<\/span> customers:\n   print(f'{c[\"CustomerID\"<\/span>]}<\/span> - {c[\"FirstName\"<\/span>]}<\/span> {c[\"LastName\"<\/span>]}<\/span>'<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Run the main.py<\/code> module:<\/p>\n\n\n
1<\/span> - Stephen Johnson\n2<\/span> - Norma Baxter\n3<\/span> - Ryan Martin\n4<\/span> - Eric Fox\n5<\/span> - Brian Miller<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to get the next five customers, you can set the new offset<\/code> value like this:<\/p>\n\n\n
customers = get_customers(limit=5<\/span>, offset=5<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll return the following customers:<\/p>\n\n\n
6<\/span> - Heather Webb\n7<\/span> - Nicole Smith\n8<\/span> - Anna Wilson\n9<\/span> - Katelyn Harris\n10<\/span> - Justin Hayes<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/a><\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Execute the SELECT<\/code> statement with the OFFSET<\/code> and FETCH<\/code> clauses to paginate data.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Third, use the with<\/code> statement<\/a> to manage the Connection<\/code> and Cursor<\/code> objects properly. This eliminates the need to manually close the Cursor<\/code> and Connection<\/code> objects and handle exceptions.<\/p>\n\n\n