Quickstart: Connect with the pymssql driver for Python

2025-06-20

In this quickstart, you connect a Python script to a database that you have created and loaded with sample data. You use the pymssql driver for Python to connect to your database and perform basic operations, like reading and writing data.

pymssql documentation | pymssql source code | Package (PyPi)

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.
A database on SQL Server, Azure SQL Database, or SQL database in Fabric with the AdventureWorks2022 sample schema and a valid connection string.

Setting up

Follow these steps to configure your development environment to develop an application using the pymssql Python driver.

Note

This driver uses the TDS protocol, which is enabled by default in SQL Server, SQL database in Fabric and Azure SQL Database. No extra configuration is required.

Install the pymssql package

Get the pymssql package from PyPI.

Open a command prompt in an empty directory.
Install the pymssql package.
```
pip install pymssql
```

Check installed packages

You can use the PyPI command-line tool to verify that your intended packages are installed.

Check the list of installed packages with pip list.
```
pip list
```

Create a SQL database

This quickstart requires the AdventureWorks2022 Lightweight schema, on Microsoft SQL Server, SQL database in Fabric or Azure SQL Database.

Create a SQL database in minutes using the Azure portal

Create a new file

Create a new file named app.py.

Add a module docstring.

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

Import packages, including pymssql.

from os import getenv
from dotenv import load_dotenv
from pymssql import connect

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

load_dotenv()
conn = connect(getenv("SQL_SERVER"),getenv("SQL_USER"),getenv("SQL_PASSWORD"),getenv("SQL_DATABASE"))

In the current directory, create a new file named *.env.
Within the *.env file, add entries for your connection string values named SQL_SERVER, SQL_USER, SQL_PASSWORD, SQL_DATABASE. Replace the placeholders here with your actual connection string values.
```
SQL_SERVER="<server_name>"
SQL_USER="<sql_user_name>"
SQL_PASSWORD="<sql_user_password>"
SQL_DATABASE="<sql_database>"
```
Tip

The connection string used here largely depends on the type of SQL database you're connecting to. For more information on connection strings and their syntax, see connection string syntax reference.

Execute a query

Use a 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(as_dict=True)
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 a foreach loop to get all the records from the database. Then print the records.

for r in cursor:
    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

Here's the expected output.

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

Execute an INSERT statement safely and pass parameters. Passing parameters as values protects your application from SQL injection attacks.

Add an import for randrange from the random library to the top of app.py.
```
from random import randrange
```
At the end of app.py add code to 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
```
Here's the expected output.
```
Inserted Product ID : 1001
```

Next step

Visit the pymssql driver GitHub repository for more examples, to contribute ideas or report issues.

pymssql driver on GitHub

Ibahagi sa