Python Connect to SQL Server with mssql-python Driver

Problem

Python can connect to SQL Server using drivers like pyodbc and pymssql. However, Microsoft recently released a new Python driver called Python Driver for SQL Server or mssql-python. Currently in preview, Microsoft describes it as “the only first-party driver.” So, what’s this new driver all about, and how do you use it? Learn how to configure Python to connect to SQL Server with this new driver.

Solution

Unlike the pyodbc and pymssql Python drivers allowing connection to SQL Server, Microsoft developed mssql-python as a developer-friendly Software Development Kit (SDK) to enhance performance. Microsoft’s focus is on seamless integration between Python and C++, efficient memory management, better state handling, and advanced DBAPI enhancements.

Let’s figure out how to use the new driver in SQL Server.

Initial Steps for Python Connect to SQL Server

Before we begin, make sure to install the latest version of Python (Download). Note: The version 3.13 is the minimum version required to run the driver.

If you already have a version installed, instead of INSTALL NOW, the UPGRADE or REPAIR option will appear. Follow the instructions and remember to set admin privileges and add python to Path.

Checking Python and Packages

Run Python from the command line.

py –version 

Run pip from the command line.

py -m pip –version

Ensure pip, Setup Tools, and Wheel are up to date.

py -m pip install --upgrade pip setuptools wheel

Use pip for installation.

I am installing my packages globally, but you can optionally create a virtual environment to allow Python packages to be installed in an isolated location for a particular application. There is also an option to install a specific version instead of the latest one. See the following link for additional information: Python-Installing packages.

Install SQL Server Python Package

Open a terminal and run:

pip install mssql-python

If you only need to upgrade the installation, use the following code:

py -m pip install --upgrade mssql-python

Install Matplotlib Package

Matplotlib is an all-encompassing Python library for producing static, dynamic, and synergistic graphic representations. For more details, refer to the Matplotlib website.

Open a terminal and run:

pip install matplotlib

Install Pandas Package

Pandas is a fast, powerful, flexible, and easy-to-use open-source data analysis and manipulation tool, built on top of the Python programming language. For more details, check out the Pandas website.

Open a terminal and run:

pip install pandas

Basic Structure to Access, Update, and Select Table Items

After pasting the code below, revise the parameters before executing it.

from mssql_python import connect
 
conn_str = "Server=;Database=;Trusted_Connection=yes;"
conn = connect(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT * FROM ")
rows = cursor.fetchall()
for row in rows:
    print(row)
 
cursor.execute("UPDATE  SET  = 199.35 WHERE  = 1;")
 
cursor.execute("SELECT * FROM ")
rows = cursor.fetchall()
for row in rows:
    print(row)
 
conn.close()

Note: After running the updated code, verify the autocommit setting is set to True which is the default.

The following command shows the status of the autocommit:

print("Autocommit:", conn.autocommit)

In case you need to change it, use this command:

conn.setautocommit(False)

Afterward, you must commit any cursor execution with the following:

conn.commit()

Also, you can use the following connection string if you need to identify the user:

conn str = ("SERVER=;DATABASE=;UID=;PWD=;Encrypt=yes;")

Using the mssql-python Driver

Let’s create a table Sales in my MSSQLTips database that will have the following columns: Transaction Id, Date, Product, Quantity, and Price.

First, run your Python program and open the connection:

from mssql_python import connect
 
conn_str = "Server=;Database=MSSQLTips;Trusted_Connection=yes;"
conn = connect(conn_str)
cursor = conn.cursor()

Now, create the Sales table, running the code below only once. I am assuming that autocommit is set to True.

cursor.execute("""
CREATE TABLE Sales (
    TransactionId int IDENTITY PRIMARY KEY,
    Date date,
    Product nvarchar(50),
    Qty int,
    Price money
)
""")

Now let’s input some data into the table:

cursor.execute("""INSERT INTO dbo.Sales VALUES ('2024-03-01','Widget A',10,5.99)
,('2024-03-01','Widget B',5,12.50)
,('2024-03-02','Widget A',7,5.99)
,('2024-03-02','Widget C',8,7.25)
,('2024-03-03','Widget B',0,12.50)""")

And include a new computed column for Total Sales:

cursor.execute("ALTER TABLE [dbo].[Sales] ADD TotalSales AS [Qty] * [Price];")

The transaction five needs to change the Quantity from 0 to 7:

cursor.execute("UPDATE [dbo].[Sales] SET [Qty] = 7 WHERE [TransactionId] = 5;")

Let’s have a look at the table content:

cursor.execute("SELECT * FROM [dbo].[Sales];")
rows = cursor.fetchall()
for row in rows:
    print(row)

Result:

Plotting the Summary Data

Next, we will create a query to group the Total Sales by Product and then load it to the Pandas read statement:

import pandas as pd
import matplotlib.pyplot as plt
 
query = """
SELECT Product, SUM(TotalSales) TotalSales
FROM dbo.sales
GROUP BY Product
ORDER BY TotalSales DESC
"""
 
df = pd.read_sql(query, conn)

After you try to load the data, the df variable in Pandas has the following error:

Just disregard it for now. The new connection procedure in pandas works as you can see:

print(df)

Next, plot it using matplotlib:

plt.figure(figsize=(8, 5))
plt.bar(df["Product"], df["TotalSales"], color="skyblue")
plt.xlabel("Product")
plt.ylabel("Total Sales ($)")
plt.title("Total Sales by Product")
plt.xticks(rotation=45)
plt.grid(axis="y", linestyle="--", alpha=0.7)
 
plt.show()

Result:

If you would like to save the graph, use the following command:

plt.savefig("sales_chart.webp")
plt.show() 

When finished, run the command to close the connection to SQL Server:

conn.close() 

Explore more details about this new driver on Microsoft’s website.

Next Steps

• Microsoft-Python Driver for SQL Server
• Python-Installing packages
• More Python tips

Sebastiao Pereira

Sebastião Pereira has over 40 years of experience in database development including T-SQL, algorithm design, machine learning and bringing innovative mathematical formulas to SQL Server. He started his career at a transnational fast-moving consumer goods (FMCG) company as an employee then later transitioning into a consultant role. He eventually founded his own company to develop software solutions for the healthcare industry.  Sebastião is a respected award-winning author on MSSQLTips.com extending SQL Server capabilities beyond traditional workloads.

• MSSQLTips Awards
  • Author of the Year – 2025
  • Trendsetter (25+ tips) – 2025
  • Rookie of the Year – 2024