How to Connect to SQL Server from Python

For most of the last decade, connecting to SQL Server from Python meant pyodbc plus a separately installed Microsoft ODBC driver. That changed in late 2025 when Microsoft shipped mssql-python, its first official first-party Python driver, now generally available. pyodbc is still the most widely deployed option and the safest default for existing code, but the recommendation for new projects is shifting. This guide covers all three serious options with working code.

The options

Rules of thumb:

• Existing codebase or maximum ecosystem compatibility: pyodbc. Every tutorial, every SQLAlchemy setup, every Stack Overflow answer assumes it.
• New project: mssql-python. It is Microsoft's official driver, GA since November 2025, installs with a single pip install (the ODBC driver is bundled -- currently 18.6.x), supports Windows, macOS (Intel and Apple Silicon), and Linux, and its API is deliberately pyodbc-compatible, so migration is mostly changing the import.
• You cannot install system packages (locked-down hosts where adding the Microsoft ODBC driver isn't possible) and mssql-python doesn't cover your platform: pymssql. It uses FreeTDS instead of ODBC, so there is no driver to install -- but note its parameter style differs and the project went through a near-death period in 2019 before maintenance resumed.

pyodbc

pyodbc is a generic ODBC bridge: it needs an ODBC driver manager plus the Microsoft ODBC Driver for SQL Server installed on the machine.

The Linux setup trap

pip install pyodbc succeeds on Linux, then the import fails:

ImportError: libodbc.so.2: cannot open shared object file: No such file or directory

The wheel includes pyodbc but not unixODBC, and not the SQL Server driver. You need both:

# Debian/Ubuntu -- unixODBC runtime
sudo apt install unixodbc
 
# Microsoft ODBC Driver 18 (from Microsoft's repo, not Debian's)
curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft.gpg
curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt update
sudo ACCEPT_EULA=Y apt install msodbcsql18

On macOS, use Homebrew (brew install unixodbc plus Microsoft's msodbcsql18 tap). On Windows, the ODBC driver is usually already present or installs from a standalone MSI.

Connecting

import pyodbc
 
conn = pyodbc.connect(
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=db.example.com,1433;"
    "DATABASE=sales;"
    "UID=app_user;"
    "PWD=secret;"
    "Encrypt=yes;"
)
cursor = conn.cursor()
cursor.execute("SELECT id, name FROM customers WHERE region = ?", ("EMEA",))
for row in cursor.fetchall():
    print(row.id, row.name)
conn.close()

Parameters use ? placeholders (qmark style). Note the server syntax: port is appended with a comma (host,1433), not a colon.

The ODBC Driver 18 encryption trap

This is the error that fills the support forums:

SSL Provider: The certificate chain was issued by an authority that is not trusted.

ODBC Driver 18 changed the default from Encrypt=no to Encrypt=yes. Connections that worked for years under Driver 17 break on upgrade, because the server's TLS certificate (often self-signed by default on on-prem SQL Server) fails validation. Your options, in order of preference:

1. Install a properly signed certificate on the server -- the right fix for production.
2. TrustServerCertificate=yes in the connection string -- encrypts the connection but skips certificate validation. Acceptable for dev/test; in production it leaves you open to man-in-the-middle attacks.
3. Encrypt=no -- plaintext. Avoid.

Azure SQL is unaffected (its certificates chain to a public CA), which is why this trap bites on-prem and Docker setups specifically.

Bulk inserts: fast_executemany

By default cursor.executemany() sends one round trip per row, which is painfully slow for large batches. One flag fixes it:

cursor.fast_executemany = True
cursor.executemany(
    "INSERT INTO events (ts, payload) VALUES (?, ?)",
    rows,  # list of tuples
)

This packs rows into a parameter array and can be orders of magnitude faster. Watch for two gotchas: it allocates memory based on the widest value per column, and varchar(max) columns can degrade it back to slow mode.

mssql-python (the official Microsoft driver)

pip install mssql-python

That's the whole setup -- the Microsoft ODBC driver is bundled inside the wheel, so the unixODBC/msodbcsql18 dance above disappears. The API is intentionally pyodbc-shaped:

import mssql_python
 
conn = mssql_python.connect(
    "SERVER=db.example.com,1433;"
    "DATABASE=sales;"
    "UID=app_user;"
    "PWD=secret;"
    "Encrypt=yes;"
)
cursor = conn.cursor()
cursor.execute("SELECT id, name FROM customers WHERE region = ?", ("EMEA",))
for row in cursor.fetchall():
    print(row.id, row.name)
conn.close()

No DRIVER= clause needed -- it always uses its bundled driver. Encryption defaults match ODBC Driver 18 (Encrypt=yes), so the certificate trap above applies identically; TrustServerCertificate=yes works the same way.

Beyond the pyodbc-compatible surface, it adds first-party conveniences: built-in connection pooling (PoolingManager), Microsoft Entra ID authentication (AuthType), and native UUID handling. Since it is the driver Microsoft now actively develops (pyodbc receives community maintenance), expect new SQL Server features to land here first.

The honest caveats: it is barely a year old, third-party ecosystem support is still catching up (SQLAlchemy's bundled dialect targets pyodbc and pymssql; the mssql-python dialect is provided by a separate package), and pinning matters more with a fast-moving 1.x project.

pymssql

pymssql skips ODBC entirely and talks TDS via FreeTDS. Modern wheels bundle FreeTDS with TLS support, so pip install pymssql is genuinely all you need:

import pymssql
 
conn = pymssql.connect(
    server="db.example.com",
    port=1433,
    user="app_user",
    password="secret",
    database="sales",
)
cursor = conn.cursor(as_dict=True)
cursor.execute("SELECT id, name FROM customers WHERE region = %s", ("EMEA",))
for row in cursor.fetchall():
    print(row["id"], row["name"])
conn.close()

Note the parameter style: %s, like the MySQL drivers, not ?. Moving code between pymssql and the ODBC-based drivers means touching every query.

Worth knowing: the project announced its own discontinuation in 2019 (the maintainers recommended pyodbc), then new maintainers revived it in 2021 and it has been actively released since -- 2.3.13 is current. Historically its Azure SQL story was weak because older wheels lacked TLS; current wheels fixed this, but if you find old advice saying "pymssql can't connect to Azure," that's what it refers to.

SQLAlchemy

SQLAlchemy's mssql dialect supports pyodbc (recommended) and pymssql out of the box:

from sqlalchemy import create_engine, text
 
engine = create_engine(
    "mssql+pyodbc://app_user:secret@db.example.com:1433/sales"
    "?driver=ODBC+Driver+18+for+SQL+Server&Encrypt=yes",
    fast_executemany=True,
)
with engine.connect() as conn:
    rows = conn.execute(text("SELECT TOP 5 id, name FROM customers")).fetchall()

The driver name goes in the query string with + for spaces. fast_executemany=True at engine level enables the bulk-insert fast path for all inserts. For mssql-python, install the separate mssql-python-sqlalchemy dialect package and use the mssql+mssql_python:// scheme.

Common connection errors

Querying without writing connection code

If the goal is to explore a SQL Server database rather than build an application, a GUI client skips all of this. Mako connects to SQL Server (along with PostgreSQL, MySQL, and six other engines) and gives you AI-assisted SQL directly -- see our SQL Server GUI client guide for how it compares to SSMS, Azure Data Studio, and DBeaver. For getting data in, see importing CSV to SQL Server.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.