pymssql: a DB-API driver for SQL Server built on FreeTDS and Cython

A DB-API interface, not an ORM

pymssql is described as a simple database interface for Python that builds on top of FreeTDS to provide a PEP 249 interface to Microsoft SQL Server. PEP 249 is the database API specification that Python's built-in sqlite3 module and most third-party drivers implement, and the point of conforming to it is that swapping the database behind an application does not mean rewriting it. The scope is deliberately narrow. There is no object mapper, no query builder and no schema tool here, just connect, cursor, execute and fetch, and the driver is happy to be used directly by anyone who wants the standard interface. The licence is LGPL-2.1, and the packaging records Damien Churchill as the original author with Mikhail Terekhov listed as a maintainer. The home page is the readthedocs documentation, and the project states that new development happens on GitHub, with a Google Group kept for discussion.

Installing is pip, and the wheels bundle FreeTDS

pymssql wheels are available from PyPI, and the install is two commands:

    pip install -U pip
    pip install pymssql

Most of the time that is all that is needed, and the reason is stated in the README: the official pymssql wheels bundle a static copy of FreeTDS and have SSL support, so they can be used to connect to Azure. That static bundle is the single most important operational fact about this driver. A system FreeTDS installation, if you have one, is neither required nor consulted when you install the wheel, and the SSL support is what lets the same wheel work against a cloud instance with a certificate. There is a warning attached to the pip upgrade step, and it is easy to skim past: on some Linux distributions the pip version is too old to support all the flavours of manylinux wheels, so upgrading pip is necessary, with Ubuntu 18.04 and the Python 3.6 module in RHEL 8 and CentOS 8 given as examples of affected environments.

The basic example is six lines of real code

The usage example in the README is the whole API in miniature. You connect with the server, user, password and a database name, you ask for a cursor with as_dict set to true, and then you execute a parameterised query and iterate the rows:

    conn = pymssql.connect(server, user, password, "tempdb")
    cursor = conn.cursor(as_dict=True)

    cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
    for row in cursor:
        print("ID=%d, Name=%s" % (row['id'], row['name']))

    conn.close()

Two details are worth noting. The as_dict flag is what makes the rows accessible by column name rather than by position, and it is a per-cursor option rather than a global setting, so you can mix styles inside one connection. And the query is parameterised with a placeholder and a separate argument, rather than by formatting the value into the string, which is what keeps the example safe from injection. The rest is PEP 249 behaviour, which is exactly the point of the design.

The build is Cython, and the version comes from the tags

The packaging is where the project shows its age and its current state at once. The build system requires packaging, setuptools above 80.0, setuptools_scm, wheel, Cython 3.1.0 or newer, tomli and, on Python 3.12 and newer, the standard library distutils shim. The version is dynamic, and setuptools_scm derives it from the git tags and writes it into a header file at src/pymssql/version.h using a template that defines a PYMSSQL_VERSION constant, with local_scheme set to no-local-version so a dirty checkout does not produce a version with a local suffix. The packages are found under src, and the package data includes a py.typed marker, so type checkers treat the extension as typed. The project also records a FreeTDS version for the PyPI wheels, 1.5.19, which tells you exactly which FreeTDS the official binaries are built against.

setup.py survives as a build shim, not as an installer

There is still a setup.py in the repository, and it is worth understanding why, because reading it without context suggests the project is stuck in the old packaging world. The file carries the standard LGPL 2.1 notice and the original 2009 copyright, and it imports from distutils and setuptools together. The logic at the top is the key: it checks whether the generated C files exist, the ones named _mssql.c and _pymssql.c, and if they do it takes the plain distutils build_ext path, and if they do not it forces Cython to be installed first through setup_requires and then imports build_ext from Cython.Distutils instead. In other words, the file exists to make an sdist build work when the C sources have not been generated, not to be the primary build path. The project description in the package metadata calls the current implementation the new Cython-based version, which confirms that the C sources are generated rather than checked in.

A Docker image that builds FreeTDS from source with OpenSSL and Kerberos

The Dockerfile in the repository shows how a full build is done, and it is the path you need if you want OpenSSL and Kerberos support rather than the stock wheel. The base image is python:3.12, the working directory is /opt/src/pymssql, and the command that runs when the container starts checks the package, runs twine check on the distributions and then runs pytest with durations enabled. The system packages installed are libssl-dev and libkrb5-dev, and the build itself calls a script in the dev directory, named build.py under dev, with an explicit set of switches:

    --ws-dir=./freetds \
    --dist-dir=./dist \
    --with-openssl=yes \
    --enable-krb5 \
    --sdist \
    --static-freetds

The two switches that matter for an enterprise deployment are --with-openssl, which builds FreeTDS against OpenSSL so the connection can be encrypted, and --enable-krb5, which turns on Kerberos support. The --static-freetds flag links the client library in statically, and --sdist also produces the source distribution. The package is then installed from the locally built dist directory with --no-index, so pip does not reach for PyPI.

The test environment is two manylinux images against a real server

The compose file shows how the test suite is run, and it is a careful setup for a driver. A SQL Server container runs from the microsoft/mssql-server-linux image with the EULA accepted and a strong password, published on port 1433, and given a fixed address of 10.5.0.5 on a bridge network with a 10.5.0.0/16 subnet. Alongside it are two build images, one for x86_64 and one for i686, both from the quay.io pypa manylinux repositories, each depending on the SQL Server service and each mounting the repository at /io. The i686 image is the notable one, because 32-bit builds are still a support commitment for a database driver that gets deployed on older Windows estates. The two build images sit at 10.5.0.6 and 10.5.0.7. The compose file also carries a version key at the top, which is a leftover of the compose v2 format, and the repository has a Dockerfile, an appveyor configuration for Windows builds and a dev directory holding the build script and the development requirements.

Supported Python versions and a stale tag list

The package metadata is more current than the release tags, and the gap is worth noting. The package requires Python 3.9 or newer, and the classifiers enumerate CPython for 3.9 through 3.15, with the operating systems listed as Windows, POSIX and Unix, and the development status marked as Production/Stable. But the published releases named in the project are 2.1.5 from 2020-09-18, 2.1.4 from 2018, and a 2.1.4 release candidate from August 2018. The last push to the repository was on 2026-10-20, so the code is being worked on while the tags are old, and the README points at the readthedocs stable documentation rather than at a version pinned to a release. The version table in the packaging configuration is the better guide to what a current build actually contains. For a driver that many teams have had installed for years, that combination of a stable 1.x-era tag and an active repository is worth verifying against your own environment before you assume a fresh pip install gives you the current code.

Editorial conclusion

pymssql suits a Python service that needs to talk to SQL Server and wants the standard DB-API surface rather than a vendor specific ORM, especially where Azure is involved and the bundled SSL support matters. It does not suit a project that needs the full breadth of the SQL Server feature set, since the driver sits on FreeTDS rather than on the native Windows provider, and it does not suit someone who wants a maintained 1.x line, since the released tags stop at 2.1.5 from 2020 even though the repository itself is being worked on. Before you install it, upgrade pip as the README instructs, and check that the platform you deploy to has a wheel for the Python version you use.

Frequently asked questions

Official sources

1. License: LGPL-2.1
2. Project website
3. pymssql/pymssql on GitHub
4. README
5. Releases