Using Sql Server with Django in production

Question

Has anybody got recent experience with deploying a Django application with an SQL Server database back end? Our workplace is heavily invested in SQL Server and will not support Django if there isn't a sufficiently developed back end for it.

I'm aware of mssql.django-pyodbc and django-mssql as unofficially supported back ends. Both projects seem to have only one person contributing which is a bit of a worry though the contributions seem to be somewhat regular.

Are there any other back ends for SQL Server that are well supported? Are the two I mentioned here 'good enough' for production? What are your experiences?

score 22 · Answer 1 · answered May 09 '09 at 15:47

22

As has been stated, django-pyodbc is a good way to go. PyODBC is probably the most mature SQL Server library for Python there is.

The only thing you may have problems with is that pyodbc doesn't support stored procedures very well (you can call them, but you have no way to get results from them). You can call them using pymssql, but I would avoid it if at all possible as it doesn't support the standard DB-API interface and may be subject to changes. If you need to do this, your best bet is to use adodbapi directly (it's included with the python win32 package, which you'll probably end up installing anyway).

answered May 09 '09 at 15:47

Jason Baker

192,085
135
376
510

Thanks for `adodbapi` - this was a new one for me. – Burhan Khalid Oct 21 '12 at 07:14
django-mssql uses a fork of adodbapi and supports stored procedures. – Manfre Oct 26 '12 at 23:51
4

You can get the results from SQLServer stored procedures by calling them like so: `cursor.execute('DECLARE @results int;EXEC @results=spMyProc;SELECT @results')` – Matthew Plourde Dec 03 '12 at 14:38
Update: Using Django 2.0+ I had success with [django-pyodbc-azure](https://pypi.org/project/django-pyodbc-azure/) but not django-pyodbc. – Dustin Michels Dec 11 '18 at 22:55

score 19 · Answer 2 · edited Sep 11 '18 at 13:24

19

These days

django-mssql: resulted in error "NoneType not callable" upon ./manage.py migrate
avidal/django-pyodbc: unmaintained. Replaced by:
- django-pyodbc: no support for python 3
- django-pyodbc-azure: works for me so far
  - EDIT: Seems to be unmaintained. Filed issue 125 asking about status
  - EDIT: got reply from maintainer. Will be made up-to-date with Django 2.0 soon
  - EDIT: maintainer released version 2.0 for django 2.0
  - EDIT: maintainer released version 2.1 for django 2.1

EDIT: Here are the package versions

Django==1.11.6
django-mssql==1.8
pyodbc==4.0.19
django-pyodbc==1.1.1
django-pyodbc-azure==1.11.0.0

edited Sep 11 '18 at 13:24

Gabriel

734
11
26

answered Oct 31 '17 at 06:47

Shadi

9,742
4
43
65

4

it'd be helpful to provide versions of Django and the libraries you tested, because this information could become quickly out of date. – Josh Smeaton Nov 01 '17 at 01:23
Also note that `django-pyodbc` and its forks run on Linux--unlike `django-mssql`, it has no Windows dependencies. SQL Server doesn't even require Windows anymore, so that will probably become increasingly relevant in the future. – lehiester Apr 22 '18 at 18:43

Cloud Artisans · Answer 3 · 2018-08-28T02:50:11.990

Here's a "modern" answer to this question. I successfully deployed Django 1.11 on a production Ubuntu 16.04 server that connects to MS SQL Server 2017 running on another server.

First, install the native MS ODBC driver "ODBC Driver 17 for SQL Server":

# https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server#ubuntu-1404-1604-and-1710
sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install msodbcsql
apt-get install unixodbc-dev

# test you can actually get to port 1433 on the server that is running MS SQL:
nc -z -v -w5 host.where.sql.server.is.running.com 1433

# add /opt/mssql-tools/bin to your PATH in .bash_profile, e.g.:
# PATH="$HOME/bin:$HOME/.local/bin:/opt/mssql-tools/bin:$PATH"
# source ~/.bash_profile
# now, test that you can actually connect to MS SQL Server:
sqlcmd -S host.where.sql.server.is.running.com -U db_username -P db_password

Second, make sure you pip install these modules:

# https://github.com/michiya/django-pyodbc-azure
django-pyodbc-azure==1.11.9.0

# https://github.com/mkleehammer/pyodbc/wiki
pyodbc==4.0.22

Third, modify the DATABASES entry of your Django settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'db_name',
        'USER': 'db_username',
        'PASSWORD': 'db_password',
        'HOST': 'host.where.sql.server.is.running.com',
        'PORT': '1433',
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
            'isolation_level': 'READ UNCOMMITTED',  # prevent SELECT deadlocks
        },
    },
}

I'm omitting the rest of my configuration (nginx, Gunicorn, Django REST Framework, etc), but that's outside the scope of this answer.

Update: this has been running in production for 6+ months now and hasn't had any issues beyond MS SQL Server-specific deadlocks when multiple connections are doing SELECT queries on the same table, which was fixed with the isolation_level setting. The system gets about 2k new users every day.

@ianbroad - yes, this is still running in production, but it's still the same versions as I listed. I haven't tried it on MacOS Mojave. — Cloud Artisans, Apr 01 '19 at 16:47
@ianbroad did you have to do some symlinks for libs. I am using the ODBC 17 driver but getting this error. Thanks django.core.exceptions.ImproperlyConfigured: Error loading pyodbc module: libodbc.so.2: cannot open shared object file: No such file or directory — ray, Feb 05 '23 at 09:05

score 4 · Answer 4 · edited Aug 29 '15 at 15:12

4

I have seen so many people get the following error after installing django_mssql on Windows:

django.core.exceptions.ImproperlyConfigured: 'sqlserver_ado' isn't an available database backend.
Try using django.db.backends.XXX, where XXX is one of:
  'dummy', 'mysql', 'oracle', 'postgresql_psycopg2', 'sqlite3'
Error was: No module named sqlserver_ado.base

The solution is installing the following plugin:

http://sourceforge.net/projects/pywin32/

edited Aug 29 '15 at 15:12

SaeX

17,240
16
77
97

answered Jun 08 '12 at 19:26

Suresh

41
1

1

I'm getting this exact error right now, the link is no longer valid, do you know where I can get the plugin? – David Zhan Liu Jul 21 '15 at 22:59
1

@DavidZhanLiu: that's Python Win32 Extensions according to the web archive. It moved to http://sourceforge.net/projects/pywin32/. – SaeX Aug 29 '15 at 14:08

score 4 · Answer 5 · answered May 09 '09 at 15:32

4

We are using django-mssql in production at our company. We too had an existing system using mssql. For me personally it was the best design decision I have ever made because my productivity increased dramatically now that I can use django .

I submitted a patch but when I started using django-mssql and did a week or two of testing.Since then (October 2008) we run our system on django and it runs solid. I also tried pyodbc but I did not like to much.

We are running a repair system where all transactions run through this system 40 heavy users. If you have more questions let me know.

answered May 09 '09 at 15:32

Paul

179
1
7

Is django-mssql tied to a particular version of django? – Josh Smeaton May 11 '09 at 02:40
8

django-mssql requires django to be run on Windows. What to do if one is running django on Linux? – IgorGanapolsky Jun 02 '10 at 18:03
@Paul, I have an existing sybase db that I want to connect Django to for select only reporting. Can I connect directly to a DSN with django-mssql? – ctrl-alt-delete Jan 08 '16 at 15:18

score 2 · Accepted Answer · answered Sep 09 '21 at 10:44

Use the below official Microsoft package to connect the SQL server to Django.

pip install mssql-django

Settings

 DATABASES = {
    'default': {
        'ENGINE': 'mssql',
        'NAME': 'mydb',
        'USER': 'user@myserver',
        'PASSWORD': 'password',
        'HOST': 'myserver.database.windows.net',
        'PORT': '',

        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    },
}

# set this to False if you want to turn off pyodbc's connection pooling
DATABASE_CONNECTION_POOLING = False

More Information: https://github.com/microsoft/mssql-django

score 1 · Answer 7 · answered May 09 '09 at 15:12

Haven't used it in production yet, but my initial experiences with django-mssql have been pretty solid. All you need are the Python Win32 extensions and to get the sqlserver_ado module onto your Python path. From there, you just use sql_server.pyodbc as your DATABASE_ENGINE. So far I haven't noticed anything missing, but I haven't fully banged on it yet either.

Using Sql Server with Django in production

7 Answers7

Linked