Python SQL Server: SELECT

Summary: in this tutorial, you will learn to query one or multiple rows from a SQL Server table in Python using pymssql API.

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

Selecting one row

Step 1. Create a new module query.py within the project directory.

Step 2. Define a function that finds a customer by ID:

def find_customer_by_id(customer_id: int) -> dict | None:
    # Connect to the SQL Server
    conn = create_connection()
    if conn is None:
        return None

    # Find the customer
    sql = """SELECT 
                CustomerID, FirstName, LastName, Email, PhoneNumber, Address
             FROM 
                CustomerXs 
            WHERE CustomerID = %s"""
    
    # Execute the query
    with (conn, conn.cursor() as cursor):
        cursor.execute(sql, (customer_id,))
        row = cursor.fetchone()
        return rowCode language: Python (python)

How it works.

First, import the built-in logging module:

import loggingCode language: Python (python)

Second, import the create_connection from the connect module:

from connect import create_connectionCode language: Python (python)

Third, define the find_customer_by_id function that returns a customer by customer_id:

def find_customer_by_id(customer_id: int) -> tuple | None:Code language: Python (python)

Fourth, connect to the SQL Server by calling the create_connection() function:

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

If the connection fails, return True immediately.

Fifth, form a query that finds the customer by ID:

sql = """SELECT 
            CustomerID, FirstName, LastName, Email, PhoneNumber, Address
         FROM 
            Customers 
        WHERE CustomerID = %s"""Code language: Python (python)

In the query, the %s is the placeholder that will be parameterized, which can prevent SQL injection attacks.

Sixth, create a Cursor object from the Connection object and manage both Connection and Cursor objects in the with statement:

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

Seventh, execute a SELECT statement by calling the execute() method of the Cursor object with the customer_id value:

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

Eighth, retrieve one row from the query result set using the fetchone() method:

row = cursor.fetchone()Code language: Python (python)

Ninth, return the fetched row:

return rowCode language: Python (python)

Step 3. Modify the main.py file to use the find_customer_by_id() function:

import logging, sys
from query import find_customer_by_id

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

customer = find_customer_by_id(1)
print(customer)Code language: Python (python)

How it works.

First, import find_customer_by_id from the query module:

from query import find_customer_by_idCode language: Python (python)

Second, call the find_customer_by_id() function to retrieve the data of the customer id 1:

customer = find_customer_by_id(1)Code language: Python (python)

Third, display the customer data to the terminal:

print(customer)Code language: Python (python)

Output:

(1, 'Stephen', 'Johnson', '[email protected]', '001-301-795-6575x030', '225 Clark Springs, Sarahhaven, AK 81481')Code language: Python (python)

The result is a tuple that includes the customer fields.

To change the result to a dictionary, you set the as_dict argument to True when creating the Cursor:

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

If you execute the main.py module, the output will look like:

{'CustomerID': 1, 'FirstName': 'Stephen', 'LastName': 'Johnson', 'Email': '[email protected]', 'PhoneNumber': '001-301-795-6575x030', 'Address': '225 Clark Springs, Sarahhaven, AK 81481'}Code language: Python (python)

The result is a dictionary that has keys as the columns in the selection list of the query.

Selecting multiple rows

Step 1. Add the find_customers function to the query.php module, which finds customers by first names using the LIKE operator:

def find_customers(term: str) -> list[dict] | None:
    conn = create_connection()
    if conn is None:
        return None

    # Find the customer
    sql = """SELECT * FROM Customers 
             WHERE FirstName LIKE %s ORDER BY FirstName"""
    
    name = f'%{term}%'
    
    with (conn, conn.cursor(as_dict=True) as cursor):
        cursor.execute(sql, (name,))
        return cursor.fetchall()Code language: Python (python)

How it works.

First, define the find_customers function that accepts a search term and returns a dictionary containing customer data.

def find_customers(term: str) -> dict | None:Code language: Python (python)

Second, establish a connection to the SQL Server by calling the create_connection() function:

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

If the connection fails, return None immediately.

Third, construct a SELECT statement that uses the LIKE operator to match the customer’s first name:

sql = "SELECT * FROM Customers WHERE FirstName LIKE %s ORDER BY FirstName"Code language: Python (python)

Fourth, add the % wildcards to the search term and assign the result to the name variable:

name = f'%{term}%'Code language: Python (python)

Fifth, create a Cursor object, execute the SELECT statement, call fetchall() to fetch all rows, and return them immediately:

with (conn, conn.cursor(as_dict=True) as cursor):
    cursor.execute(sql, (name,))
    return cursor.fetchall()Code language: Python (python)

Step 2. Modify the main.py to use the find_customers function:

import logging, sys
from query import find_customers

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

# Find the customers whose first name starts with 'Jos'
customers = find_customers('Jos')
for customer in customers:
    print(f'{customer["FirstName"]} {customer["LastName"]}')Code language: Python (python)

Step 3. Run the main.py file:

python main.pyCode language: CSS (css)

Output:

Jose Williams
Joseph Spencer
Joseph Wood
Joseph Lawrence
...

Download the project source code

Summary

Call the fetchone() method to fetch one row from a query.
Call the fetchall() method to fetch all rows from a query.

Was this tutorial helpful?