Quickstart: Connect with the mssql-python driver for Python

In this quickstart, you connect a Python script to a database that you created and loaded with sample data. You use the mssql-python driver for Python to connect to your database and perform basic operations, such as reading and writing data.

The mssql-python driver doesn't require any external dependencies on Windows machines. The driver installs everything that it needs with a single pip install, allowing you to use the latest version of the driver for new scripts without breaking other scripts that you don't have time to upgrade and test.

Use the local SQL authentication example in this article only for local development against a SQL Server instance that you control. For Azure SQL Database, SQL database in Fabric, shared development environments, CI, and production deployments, start with Microsoft Entra authentication or another passwordless flow.

mssql-python documentation | mssql-python source code | Package (PyPI) | Visual Studio Code

Prerequisites

Python 3.10 or later.
If you don't already have Python, install the Python runtime and pip package manager from python.org.
Don't want to use your own environment? Follow Container and local development to create a reproducible devcontainer or GitHub Codespaces environment.
(Optional) Azure Command-Line Interface (CLI).

Create or connect to a database on SQL Server, Azure SQL Database, or SQL database in Fabric. Use the following steps to set up a database with the AdventureWorks2025 sample schema, and keep the connection string for later.

Create a SQL database

Create or connect to a SQL database on one of the following platforms:

Create a SQL database in minutes using the Azure portal

Setup

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

Note

This driver uses the Tabular Data Stream (TDS) protocol. SQL Server, SQL database in Fabric, and Azure SQL Database enable TDS by default, so no extra configuration is necessary.

Install operating system prerequisites

Install one-time operating system specific prerequisites. Windows users can skip this step. For full platform details, see Install mssql-python.

apk add libtool krb5-libs krb5-dev libstdc++

apt-get install -y libltdl7 libkrb5-3 libgssapi-krb5-2

dnf install -y libtool-ltdl krb5-libs

zypper install -y libltdl7 libkrb5-3 libgssapi-krb5-2

zypper install -y libltdl7

brew install openssl

Install the mssql-python package

Get the mssql-python package from PyPI.

Open a command prompt in an empty directory.
Install the mssql-python package.
```
pip install mssql-python
```

Install the python-dotenv package

Get the python-dotenv package from PyPI.

In the same directory, install the python-dotenv package.

pip install python-dotenv

Check installed packages

Use pip list to verify that your intended packages are installed.

pip list

Create a new file

Create a new file named app.py.

Add a module docstring.

"""
Connects to a SQL database using mssql-python
"""

Import packages, including mssql-python.

from os import getenv
from dotenv import load_dotenv
from mssql_python import connect

Use the mssql-python.connect function to connect to a SQL database.
```
load_dotenv()
conn = connect(getenv("SQL_CONNECTION_STRING"))
```
In the current directory, create a new file named .env.
Within the .env file, add an entry for your connection string named SQL_CONNECTION_STRING. Use one of the following examples and replace the placeholders with your actual values.

For Azure SQL Database or SQL database in Fabric, start with Microsoft Entra authentication:
```
SQL_CONNECTION_STRING="Server=<server>;Database=<database>;Encrypt=yes;TrustServerCertificate=no;Authentication=ActiveDirectoryInteractive"
```
For local SQL Server during development, start with SQL authentication:
```
SQL_CONNECTION_STRING="Server=localhost,1433;Database=<database>;UID=<user_id>;PWD=<password>;Encrypt=yes;TrustServerCertificate=yes"
```
Caution

Treat .env as a local development convenience, not a deployment mechanism. Never commit it, never reuse this SQL authentication sample in shared or production environments, and keep certificate validation enabled outside local development.

Use Connection strings to adapt the sample for named instances, containers, or advanced settings. If you're connecting to Azure SQL Database or SQL database in Fabric, use Microsoft Entra authentication for passwordless and interactive sign-in options. For broader secret and certificate guidance, see Security best practices.

Tip

On macOS, both ActiveDirectoryInteractive and ActiveDirectoryDefault work for Microsoft Entra authentication. ActiveDirectoryInteractive prompts you to sign in every time you run the script. To avoid repeated sign-in prompts, sign in once with the Azure CLI az login command, and use ActiveDirectoryDefault. ActiveDirectoryDefault reuses the cached credential.

Execute a query

Use a Transact-SQL (T-SQL) query string to execute a query and parse the results.

Create a variable for the T-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 accepts any query and returns a result set. To iterate over the result set, use cursor.fetchone().

Use cursor.fetchall with a for 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

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

A random product number reduces the chance of a duplicate key error when you run this sample more than once.

Create a T-SQL statement string.

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

Execute the statement using cursor.execute.

cursor.execute(
    SQL_STATEMENT,
    {
        'name': f'Example Product {productNumber}',
        'product_number': f'EXAMPLE-{productNumber}',
        'standard_cost': 100,
        'list_price': 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 roll back 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
```

คำติชม

หน้านี้มีประโยชน์หรือไม่

อัปเดตล่าสุดเมื่อ 2026-10-19