Step 3: Proof of concept connecting to SQL using pymssql

Article
08/23/2023

This sample proof of concept uses pymssql to connect to an SQL database. This sample assumes that you're using the AdventureWorksLT sample database.

Note

This example should be considered a proof of concept only. The sample code is simplified for clarity, and does not necessarily represent best practices recommended by Microsoft.

Prerequisites

Python 3
- If you don't already have Python, install the Python runtime and Python Package Index (PyPI) package manager from python.org.
- Prefer to not use your own environment? Open as a devcontainer using GitHub Codespaces.
  - .
pymssql package from PyPI.
An SQL database and credentials.

Connect and query data

Connect to a database using your credentials.

Create a new file named app.py.

Add a module docstring.

"""
Connects to a SQL database using pymssql
"""

Import the pymssql package.
```
import pymssql
```

Use the pymssql.connect function to connect to an SQL database.

conn = pymssql.connect(
    server='<server-address>',
    user='<username>',
    password='<password>',
    database='<database-name>',
    as_dict=True
)

Execute a query

Use an SQL query string to execute a query and parse the results.

Create a variable for the SQL query string.

SQL_QUERY = """
SELECT 
TOP 5 c.CustomerID, 
c.CompanyName, 
COUNT(soh.SalesOrderID) AS OrderCount 
FROM 
SalesLT.Customer AS c 
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID 
GROUP BY 
c.CustomerID, 
c.CompanyName 
ORDER BY 
OrderCount DESC;
"""

Use cursor.execute to retrieve a result set from a query against the database.
```
cursor = conn.cursor()
cursor.execute(SQL_QUERY)
```
Note

This function essentially accepts any query and returns a result set, which can be iterated over with the use of cursor.fetchone().

Use cursor.fetchall with a foreach loop to get all the records from the database. Then print the records.

records = cursor.fetchall()
for r in records:
    print(f"{r['CustomerID']}\t{r['OrderCount']}\t{r['CompanyName']}")

Save the app.py file.

Open a terminal and test the application.

python app.py

29485   1       Professional Sales and Service
29531   1       Remarkable Bike Store
29546   1       Bulk Discount Store
29568   1       Coalition Bike Company
29584   1       Futuristic Bikes

Insert a row as a transaction

In this example, you execute an INSERT statement safely and pass parameters. Passing parameters as values protects your application from SQL injection attacks.

Import randrange from the random library.
```
from random import randrange
```
Generate a random product number.
```
productNumber = randrange(1000)
```
Tip

Generating a random product number here ensures that you can run this sample multiple times.

Create a SQL statement string.

SQL_STATEMENT = """
INSERT SalesLT.Product (
Name, 
ProductNumber, 
StandardCost, 
ListPrice, 
SellStartDate
) OUTPUT INSERTED.ProductID 
VALUES (%s, %s, %s, %s, CURRENT_TIMESTAMP)
"""

Execute the statement using cursor.execute.

cursor.execute(
    SQL_STATEMENT,
    (
        f'Example Product {productNumber}', 
        f'EXAMPLE-{productNumber}', 
        100,
        200
    )
)

Fetch the single result using cursor.fetchone, print the result's unique identifier, and then commit the operation as a transaction using connection.commit.
```
result = cursor.fetchone()
print(f"Inserted Product ID : {result['ProductID']}")
conn.commit()
```
Tip

Optionally, you can use connection.rollback to rollback the transaction.
Close the cursor and connection using cursor.close and connection.close.
```
cursor.close()
conn.close()
```
Save the app.py file and test the application again
```
python app.py
```
```
Inserted Product ID : 1001
```

Next steps

Python Developer Center.