PyPIUtilsupdated 06 Aug 2026

pymssql

Item: pymssql
Rating: 3.5
Author: Keyoor

A DB-API 2.0 driver for Microsoft SQL Server. It is a Cython extension wrapped around FreeTDS, the open source implementation of the TDS wire protocol that SQL Server speaks, so you get connect(), cursor(), execute(), fetchall(), and commit() the same way you would with psycopg or sqlite3. The published wheels statically bundle FreeTDS with SSL, which is the practical selling point: pip install pymssql and you can talk to SQL Server or Azure SQL without installing an ODBC driver manager, a Microsoft driver package, or editing any DSN files. It also ships pymssql extensions such as dict rows, context managers, and a bulk copy method.

pymssql · v2.3.13 · 12.8M/wk · ★ 886 · pushed 27 Jul 2026 · pymssql/pymssql · docs · reviewed by Keyoor

Verdict

The path of least resistance for Python code that has to reach SQL Server with a username and password, mostly because the wheels remove the ODBC install entirely. Move to pyodbc or Microsoft's mssql-python once you need enterprise authentication, async, or a licence and support story you can defend.

API stability4/5The DB-API surface has been steady across the whole 2.x line and code from years ago still runs; the point off is for 2.3.0, which renamed the cursor batchsize attribute to arraysize and changed several datetime columns to return datetime.datetime objects

Docs3/5readthedocs covers connect() arguments, a genuinely useful FAQ, and separate FreeTDS and Azure pages, but newer features live only in the type stubs and changelog (bulk_copy options, callproc output parameters) and some pages still print SQL Server 2012 sample output

Maintenance3/5Releases are frequent and current (2.3.13 in February 2026, repo pushed in July 2026, Python 3.14 wheels already shipped), yet almost all commits come from one maintainer and about 150 issues stay open; the work is keeping the build alive rather than growing the driver

Ecosystem4/5Around 12.8M downloads a week and a first-class SQLAlchemy dialect (mssql+pymssql), but Microsoft's documentation, Django backends, and most Stack Overflow answers assume pyodbc, so you often translate examples

Use it if

You want a SQL Server driver that installs from pip alone, with no unixODBC, no msodbcsql18 apt or yum repository, and no odbcinst.ini to maintain on every machine
You build slim Docker images: there are manylinux, musllinux (Alpine), aarch64, macOS arm64, and Windows x86-64 wheels for CPython 3.9 through 3.14, so no compiler runs during the build
You use SQLAlchemy and want the mssql+pymssql dialect, which needs nothing installed beyond this package
You load large volumes into SQL Server and want BCP-style speed from Python: conn.bulk_copy(table, rows) beats row-by-row inserts by a wide margin
You authenticate with a plain SQL Server username and password against on-prem SQL Server or Azure SQL

Skip it if

You need Windows integrated authentication, Kerberos, or Microsoft Entra ID tokens: pymssql logs in through FreeTDS db-lib with a username and password, and the bundled FreeTDS build is not the place to solve enterprise SSO. pyodbc with Microsoft's ODBC driver, or Microsoft's own mssql-python, handle those paths
You expect real server-side bind parameters: execute() substitutes your values into the SQL text on the client after escaping them, so there is no prepared statement and no plan reuse for parameterised queries. Only callproc() goes over the RPC interface with actual bound parameters
You need asyncio: there is no async API at all. The only concurrency hook is set_wait_callback() for gevent-style green threads, so FastAPI and asyncio codebases have to push every call into a thread pool
You want more than one query in flight per connection: TDS has no client-side cursors here, so a second cursor on the same connection steals the first one's result set. You need one connection per concurrent query, or fetchall() before starting the next
Your legal review objects to copyleft in shipped artifacts: pymssql is LGPL-2.1 and the wheels statically link FreeTDS. pyodbc is MIT and mssql-python is MIT
You are betting on long-term support: this is effectively a one-maintainer project with roughly 150 open issues, and recent releases are mostly wheel and build plumbing rather than driver features

Setup reality

On a mainstream platform it really is pip install pymssql and nothing else, because the wheel carries its own FreeTDS with SSL. Off the beaten path it stops being pleasant: no wheel means building the sdist, which needs a C compiler, Cython, and FreeTDS development headers, and that is where 32-bit Linux, Windows on ARM, and old glibc distributions land (the wheels target manylinux_2_28 and musllinux_1_2). Then come the connection quirks. tds_version defaults to None, so you either pass it explicitly (7.4 for anything modern) or configure it in freetds.conf, and picking the wrong one shows up as garbled types rather than a clear error. pymssql always dials port 1433 unless you pass port yourself, even if freetds.conf says otherwise. Azure SQL wants the short user@servername login form, not user@servername.database.windows.net. And the timeout and login_timeout arguments are process-wide because of how the underlying db-lib API works, so you cannot give one connection a different query timeout from another in the same process.

Patterns

Connect and query with context managersconnect-basic

import os
import pymssql

with pymssql.connect(
    server="db.internal",
    port=1433,
    user="reporting",
    password=os.environ["MSSQL_PASSWORD"],
    database="sales",
    tds_version="7.4",
    login_timeout=15,
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT TOP 5 id, name FROM persons")
        for row in cur:
            print(row[0], row[1])

Context managers on connections and cursors are a pymssql extension, not DB-API. Pass port explicitly: pymssql defaults to 1433 and ignores the port in freetds.conf.

Get rows back as dictionariesrows-as-dicts

with conn.cursor(as_dict=True) as cur:
    cur.execute("SELECT id, name FROM persons WHERE salesrep = %s", ("John Doe",))
    for row in cur:
        print(row["id"], row["name"])

# unnamed columns raise, so alias them
cur.execute("SELECT MAX(amount) AS max_amount FROM orders")

With as_dict=True, any column without a name raises pymssql.ColumnsWithoutNamesError, which bites on aggregates and computed columns. Alias every expression in the SELECT list.

Pass parameters safelyparameterized-query

cur.execute("SELECT * FROM persons WHERE salesrep = %s", ("John Doe",))
cur.execute("SELECT * FROM persons WHERE id = %d", (7,))

cur.execute(
    "SELECT * FROM persons WHERE name LIKE %(pattern)s AND active = %(active)s",
    {"pattern": "J%", "active": 1},
)

# literal percent signs must be doubled once params are supplied
cur.execute("SELECT * FROM persons WHERE name LIKE 'J%%'", ())

paramstyle is pyformat and %s and %d behave the same, since pymssql escapes and interpolates client-side rather than binding. A single-argument execute() treats % literally; the moment you pass params, escape real percent signs as %%.

Insert, read the identity, commit or roll backinsert-and-commit

conn = pymssql.connect(server, user, password, "sales")
cur = conn.cursor()
try:
    cur.execute(
        "INSERT INTO orders (customer_id, amount) VALUES (%s, %s)",
        (42, 199.50),
    )
    cur.execute("SELECT CAST(SCOPE_IDENTITY() AS INT)")
    order_id = cur.fetchone()[0]
    conn.commit()
except pymssql.DatabaseError:
    conn.rollback()
    raise

Autocommit is off by default, so nothing persists without commit(). Read the new key with SCOPE_IDENTITY() in the same batch; conn.autocommit(True) or autocommit=True in connect() turns transactions off entirely.

Insert many rows in batchesexecutemany-batch

rows = [(1, "John Smith", "John Doe"), (2, "Jane Doe", "Joe Dog")]

cur.executemany(
    "INSERT INTO persons VALUES (%d, %s, %s)",
    rows,
    batch_size=500,
)
conn.commit()

executemany builds one text batch per group of rows. batch_size defaults to the cursor arraysize, an actual batch stops early if the generated SQL passes 127 MiB, and rowcount is only meaningful when batch_size is 1.

Load a large table with bulk copybulk-copy

rows = [(i, i * 2) for i in range(1_000_000)]

conn.bulk_copy(
    "example",
    rows,
    batch_size=10_000,
    tablock=True,
    check_constraints=False,
    fire_triggers=False,
)
conn.commit()

This is the BCP path and it is far faster than INSERT loops, but it does not validate column types: a wrong Python type per column surfaces as a server error or bad data, not a helpful exception.

Call a stored procedure with an output parametercall-stored-procedure

with conn.cursor(as_dict=True) as cur:
    args = cur.callproc(
        "CreateOrder",
        (42, 199.50, pymssql.output(int)),
    )
    new_id = args[2]
    print("return code", cur.returnvalue)

    for row in cur:  # any result sets the proc selected
        print(row)
    conn.commit()

callproc() uses the TDS RPC interface, so this is the one place parameters are really bound server-side. It returns the full argument tuple with output slots filled in, and cur.returnvalue holds the procedure's RETURN code.

Work around one active query per connectionsingle-active-query

# broken: c2 steals c1's result set
c1 = conn.cursor()
c1.execute("SELECT * FROM persons")
c2 = conn.cursor()
c2.execute("SELECT * FROM persons WHERE salesrep = %s", ("John Doe",))
print(c1.fetchall())  # returns c2's rows

# fix 1: drain before the next query
all_rows = c1.fetchall()
# fix 2: use a second connection for genuinely concurrent work

There is no MARS and no client-side cursor here; the protocol requires the client to flush one result set before starting another. Connection pools that hand the same connection to two code paths hit this in production.

Connect to Azure SQL with encryptionconnect-azure-sql

conn = pymssql.connect(
    server="myserver.database.windows.net",
    user="appuser@myserver",  # short form, not @myserver.database.windows.net
    password=os.environ["AZURE_SQL_PASSWORD"],
    database="appdb",
    port=1433,
    tds_version="7.4",
    encryption="require",
    login_timeout=30,
)

The long user@server.database.windows.net login form fails; use user@servername. Azure needs TDS 7.1 or newer and an SSL-capable FreeTDS, which the official wheels include.

Catch the DB-API exception classeshandle-errors

try:
    cur.execute("INSERT INTO persons (id, name) VALUES (%d, %s)", (1, "Dup"))
    conn.commit()
except pymssql.IntegrityError as exc:
    conn.rollback()
    print("constraint violated:", exc)
except pymssql.OperationalError as exc:
    print("connection or login problem:", exc)
except pymssql.DatabaseError as exc:
    conn.rollback()
    raise

The hierarchy is the DB-API one (DataError, OperationalError, IntegrityError, InternalError, ProgrammingError, NotSupportedError all under DatabaseError). Login and network failures arrive as OperationalError carrying the raw FreeTDS message, which is where most first-run debugging happens.

Step through multiple result setsmultiple-result-sets

cur.execute("SELECT id FROM persons; SELECT id FROM orders")
print(cur.fetchall())

while cur.nextset():
    print(cur.fetchall())

nextset() returns None when there is nothing left, so a while loop is the safe shape. Procedures that mix PRINT or row counts with SELECTs can produce sets you did not expect.

Use pymssql behind SQLAlchemysqlalchemy-engine

from sqlalchemy import create_engine, text
from urllib.parse import quote_plus

pwd = quote_plus(os.environ["MSSQL_PASSWORD"])
engine = create_engine(
    f"mssql+pymssql://appuser:{pwd}@db.internal:1433/sales?charset=utf8",
    pool_pre_ping=True,
)

with engine.connect() as conn:
    print(conn.execute(text("SELECT @@VERSION")).scalar())

URL-encode the password or a single @ or / in it breaks the DSN. pool_pre_ping matters here because idle TDS connections dropped by a firewall otherwise surface as an OperationalError on the next query.

Alternatives

Package	Registry	Pick it when
pyodbc	PyPI	You need Windows or Entra ID authentication, MARS, or the driver Microsoft's own docs assume
mssql-python	PyPI	You want Microsoft's officially maintained driver, MIT licensed, without an ODBC driver manager
aioodbc	PyPI	An asyncio codebase that needs SQL Server access without blocking the event loop