MS SQL Server with Django / SQLAlchemy

After searching some up-to-date information about the state of MSSQL support for Python (web development purposes mainly), I decided to take a closer look just by testing with the most promising libraries. Well, for Django at least there was only one library that I could find that has received updates recently, so let's go with that. In short, we'll use pyodbc as the core library for database connections with both Django and SQLAlchemy. With Django we'll also use a Django app called django-mssql-backend (the only one that has recently received updates).

Here are the steps in short that was used (on Mac OS).

Prerequisites

Run MS SQL Server in Docker

Run mssql server 2017 or 2019 in a docker container:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=GoodP4ss*?bordu!' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest

Note that the password must be quite strong, otherwise the server just exists. Now the server runs in port 1433, and the username to connect to the server is "sa".

Create a test DB

docker exec -it 01577834b033 /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa -P GoodP4ss*?bordu!

1> create database mytest
2> go

Now you can list all databases to see if it worked:

3> select name from sys.databases
4> go

Install Python dependencies

At this point we jump into installing Python dependencies, so make sure you have a virtual environment set up.

Install pyodbc:

The pyodbc library will be used by both Django and SQLAlchemy. Install with

pip install pyodbc

this installed "pyodbc==4.0.30" at the time of writing.

Now, importing pyodbc in Python failed with:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: dlopen(/.../venv/lib/python3.6/site-packages/pyodbc.cpython-36m-
darwin.so, 2): Library not loaded: /usr/local/opt/unixodbc/lib/libodbc.2.dylib
  Referenced from: /.../venv/lib/python3.6/site-packages/pyodbc.cpython-36m-darwin.so
  Reason: image not found

And fixing it requires:

brew install unixodbc

Now attempting to create connection to database with

pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};Server=127.0.0.1;Database=mytest;uid=User;pwd=GoodP4ss*?bordu!')

fails with

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

To fix, I found a Stack Overflow page for a couple of suggestions: https://stackoverflow.com/questions/44527452/cant-open-lib-odbc-driver-13-for-sql-server-sym-linking-issue?rq=1, which includes creating symlinks to ODBC files and installing some more OS level dependencies. The following worked for me:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew install msodbcsql mssql-tools

Now creating the connection should work:

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};Server=127.0.0.1;Database=master;uid=sa;pwd=GoodP4ss*?bordu!')

To test the connection further in plain Python, see e.g. https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15#connect

Django 3.1

Before configuring Django settings, first we have to install the mssql backend package (in addition to Django itself):

pip install django
pip install django-mssql-backend

MSSQL with Django was tested simply by creating a new Django project with django-admin startproject mssql_test, and modifying the DATABASES settings in settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'mytest',
        'HOST': '127.0.0.1',
        'PORT': 1433,
        'USER': 'sa',
        'PASSWORD': 'GoodP4ss*?bordu!',
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        }
    }
}

A good way to test the database connection in Django is to just run the migrations, which didn't quite work with the latest Django (version 3.1). In short, I had to downgrade to Django 2.2 to get some migrations from auth application to work. Hopefully this is just a temporary glitch in django-mssql-backend, but in the meantime you can check out this Github issue for updates: https://github.com/ESSolutions/django-mssql-backend/issues/63.

After figuring out a workaround for the migrations problem, simple ORM operations such as creating new users worked just fine:

from django.contrib.auth.models import User
User.objects.create(username='admin')

SQLAlchemy

MSSQL with SQLAlchemy didn't require any additional libraries (but the pyodbc itself). As I created an "admin" user in the Django section above, I tested the SQLAlchemy engine in the following way:

from sqlalchemy import create_engine
engine = create_engine("mssql+pyodbc://sa:GoodP4ss*?bordu!@127.0.0.1:1433/mytest?driver=ODBC+Driver+17+for+SQL+Server")
with engine.connect() as conn:
    result = conn.execute('select username from auth_user')
    for row in result:
        print(row)

This resulted a nice ('admin',) to be printed. Now, this only demonstrated that the core engine connectiong works in SQLAlchemy, but the real beef would come with testing with mappings and ultimately with Alembic migrations, but that can be the topic for a part two.

Conclusion

  • Django 3.1: doesn't quite work out-of-the-box (see the issue about migrations), and relies heavily on a 3rd party Django application which, if becomes unmaintained, might cause problems in a longer run. Should be quite fine with LTS versions of Django though.
  • SQLAlchemy: feels a bit more seamless since it operates directly with pyODBC. Alembic migrations are untested in scope of this blog post.

-- Teemu