{"id":4113,"date":"2024-07-23T16:16:08","date_gmt":"2024-07-23T09:16:08","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4113"},"modified":"2024-07-24T20:59:34","modified_gmt":"2024-07-24T13:59:34","slug":"python-sql-server-select","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/python-sql-server\/python-sql-server-select\/","title":{"rendered":"Python SQL Server: Selecting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn to query one or multiple rows from a SQL Server table in Python using pymssql<\/code> API.<\/p>\n\n\n\n

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

Selecting one row #<\/a><\/h2>\n\n\n\n

Step 1. Create a new module query.py<\/code> within the project directory.<\/p>\n\n\n\n

Step 2. Define a function that finds a customer by ID:<\/p>\n\n\n

def<\/span> find_customer_by_id<\/span>(customer_id: int)<\/span> -> dict | None<\/span>:<\/span>\r\n    # Connect to the SQL Server<\/span>\r\n    conn = create_connection()\r\n    if<\/span> conn is<\/span> None<\/span>:\r\n        return<\/span> None<\/span>\r\n\r\n    # Find the customer<\/span>\r\n    sql = \"\"\"SELECT \r\n                CustomerID, FirstName, LastName, Email, PhoneNumber, Address\r\n             FROM \r\n                CustomerXs \r\n            WHERE CustomerID = %s\"\"\"<\/span>\r\n    \r\n    # Execute the query<\/span>\r\n    with<\/span> (conn, conn.cursor() as<\/span> cursor):\r\n        cursor.execute(sql, (customer_id,))\r\n        row = cursor.fetchone()\r\n        return<\/span> row<\/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 built-in logging<\/code> module:<\/p>\n\n\n
import<\/span> logging<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
from<\/span> connect import<\/span> create_connection<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, define the find_customer_by_id<\/code> function that returns a customer by customer_id<\/code>:<\/p>\n\n\n
def<\/span> find_customer_by_id<\/span>(customer_id: int)<\/span> -> tuple | None<\/span>:<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, connect to the SQL Server by calling the create_connection()<\/code> function:<\/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
If the connection fails, return True<\/code> immediately.<\/p>\n\n\n\n
Fifth, form a query that finds the customer by ID:<\/p>\n\n\n
sql = \"\"\"SELECT \n            CustomerID, FirstName, LastName, Email, PhoneNumber, Address\n         FROM \n            Customers \n        WHERE CustomerID = %s\"\"\"<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In the query, the %s<\/code> is the placeholder that will be parameterized, which can prevent SQL injection attacks.<\/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
Seventh, execute a SELECT<\/code> statement by calling the execute()<\/code> method of the Cursor<\/code> object with the customer_id<\/code> value:<\/p>\n\n\n
cursor.execute(sql, (customer_id,))<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Eighth, retrieve one row from the query result set using the fetchone()<\/code> method:<\/p>\n\n\n
row = cursor.fetchone()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Ninth, return the fetched row:<\/p>\n\n\n
return<\/span> row<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Modify the main.py<\/code> file to use the find_customer_by_id()<\/code> function:<\/p>\n\n\n
import<\/span> logging, sys\nfrom<\/span> query import<\/span> find_customer_by_id\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\ncustomer = find_customer_by_id(1<\/span>)\nprint(customer)<\/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 find_customer_by_id<\/code> from the query<\/code> module:<\/p>\n\n\n
from<\/span> query import<\/span> find_customer_by_id<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, call the find_customer_by_id()<\/code> function to retrieve the data of the customer id 1:<\/p>\n\n\n
customer = find_customer_by_id(1<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, display the customer data to the terminal:<\/p>\n\n\n
print(customer)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
(1<\/span>, 'Stephen'<\/span>, 'Johnson'<\/span>, 'stephenjohnson@wilson-foster.com'<\/span>, '001-301-795-6575x030'<\/span>, '225 Clark Springs, Sarahhaven, AK 81481'<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The result is a tuple that includes the customer fields.<\/p>\n\n\n\n
To change the result to a dictionary, you set the as_dict<\/code> argument to True<\/code> when creating the Cursor:<\/p>\n\n\n
with<\/span> 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
If you execute the main.py<\/code> module, the output will look like:<\/p>\n\n\n
{'CustomerID'<\/span>: 1<\/span>, 'FirstName'<\/span>: 'Stephen'<\/span>, 'LastName'<\/span>: 'Johnson'<\/span>, 'Email'<\/span>: 'stephenjohnson@wilson-foster.com'<\/span>, 'PhoneNumber'<\/span>: '001-301-795-6575x030'<\/span>, 'Address'<\/span>: '225 Clark Springs, Sarahhaven, AK 81481'<\/span>}<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The result is a dictionary that has keys as the columns in the selection list of the query.<\/p>\n\n\n\n
Selecting multiple rows #<\/a><\/h2>\n\n\n\n
Step 1. Add the find_customers<\/code> function to the query.php<\/code> module, which finds customers by first names using the LIKE<\/code> operator:<\/p>\n\n\n
def<\/span> find_customers<\/span>(term: str)<\/span> -> list[dict] | None<\/span>:<\/span>\r\n    conn = create_connection()\r\n    if<\/span> conn is<\/span> None<\/span>:\r\n        return<\/span> None<\/span>\r\n\r\n    # Find the customer<\/span>\r\n    sql = \"\"\"SELECT * FROM Customers \r\n             WHERE FirstName LIKE %s ORDER BY FirstName\"\"\"<\/span>\r\n    \r\n    name = f'%{term}<\/span>%'<\/span>\r\n    \r\n    with<\/span> (conn, conn.cursor(as_dict=True<\/span>) as<\/span> cursor):\r\n        cursor.execute(sql, (name,))\r\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, define the find_customers<\/code> function that accepts a search term and returns a dictionary containing customer data.<\/p>\n\n\n
def<\/span> find_customers<\/span>(term: str)<\/span> -> dict | None<\/span>:<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, establish a connection to the SQL Server by calling the create_connection()<\/code> function:<\/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
If the connection fails, return None<\/code> immediately.<\/p>\n\n\n\n
sql = \"SELECT * FROM Customers WHERE FirstName LIKE %s ORDER BY FirstName\"<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, add the %<\/code> wildcards to the search term and assign the result to the name<\/code> variable:<\/p>\n\n\n
name = f'%{term}<\/span>%'<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, create a Cursor<\/code> object, execute the SELECT<\/code> statement, call fetchall()<\/code> to fetch all rows, and return them immediately:<\/p>\n\n\n
with<\/span> (conn, conn.cursor(as_dict=True<\/span>) as<\/span> cursor):\n    cursor.execute(sql, (name,))\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> to use the find_customers<\/code> function:<\/p>\n\n\n
import<\/span> logging, sys\nfrom<\/span> query import<\/span> find_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# Find the customers whose first name starts with 'Jos'<\/span>\ncustomers = find_customers('Jos'<\/span>)\nfor<\/span> customer in<\/span> customers:\n    print(f'{customer[\"FirstName\"<\/span>]}<\/span> {customer[\"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 file:<\/p>\n\n\n
python<\/span> main<\/span>.py<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Jose Williams\nJoseph Spencer\nJoseph Wood\nJoseph Lawrence\n...<\/code><\/span><\/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
  • Call the fetchone()<\/code> method to fetch one row from a query.<\/li>\n\n\n\n
  • Call the fetchall()<\/code> method to fetch all rows from a query.<\/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, construct a SELECT<\/code> statement that uses the LIKE<\/a><\/code> operator to match the customer’s first name:<\/p>\n\n\n
Sixth, create a Cursor<\/code> object from the Connection<\/code> object and manage both Connection<\/code> and Cursor<\/code> objects in the with<\/a><\/code> statement:<\/p>\n\n\n
Second, import the create_connection<\/code> from the connect<\/a><\/code> module:<\/p>\n\n\n