How to use Django with Sql Server

Question

I normally use Postgres for my database needs with Django but I recently started at a company which use MSSQL on a Windows environment. Long story short I had to rewrite the database properties in settings.py. Unfortunately, I have NO idea how to connect to a SQL Server using Pyodbc and they're running Python 3.x so I can't use Django-Pyodbc. While trying to run it I'm getting a: "Data source name not found and no default driver specified (0) (SQLDriverConnect)')"

Here is my current db config as it stands. I'm probably doing something wrong but it is very difficult to find resources since most Django+Sql Server results either use FreeTDS or Django-Pyodbc (neither are options).

'default': {
    'ENGINE': 'sql_server.pyodbc',
    'NAME': 'db_name_on_server',
    'USER': 'my_acct',
    'PASSWORD': 'nope',
    'HOST': 'x.x.x.x',
    'PORT': '1433',
    'OPTIONS': {  # Options are not edited
        'driver': 'SQL Server',  # What it displays as on odbc admin   
        'dsn': 'System DSN',  # What it displays as on odbc admin
        'use_legacy_datetime': False
    }

@Ivan these are unique. As I pointed out I'm running Python 3.x so Django-Pyodbc isn't viable. — Will.Beasley, Sep 25 '15 at 01:51

score 7 · Answer 1 · edited Jun 30 '17 at 12:34

Old question, but it might help someone. These are the settings I use in Windows to connect to a SQL Server. As @flipperpa said, I also use django-pyodbc-azure

pyodbc==3.0.10
django-pyodbc-azure==1.10.0.1

However, the pyodbc that pip will download doesn't work for me. As suggested in this answer, go this site http://www.lfd.uci.edu/~gohlke/pythonlibs/#pyodbc and download either depending on the version of Python 3.5 you have installed:

pyodbc‑3.0.10‑cp35‑none‑win32.whl if you have a 32-bit Python 3.5 install
pyodbc‑3.0.10‑cp35‑none‑win_amd64.whl if you have a 64-bit Python 3.5 install

Then use these settings to connect:

'default': {
 'ENGINE': 'sql_server.pyodbc',
 'HOST': "localhost",
 'USER': "--user name--",
 'PASSWORD': "--password--",
 'NAME': "--database name--",
 'PORT': 1433,
 'OPTIONS': {
      'driver' : 'SQL Server Native Client 11.0',
      'MARS_Connection' : True,
      'driver_supports_utf8' : True,
 },
}

For anyone that is using linux, MS has now released official odbc drivers for SQL server and is officially (if more than a little tacitly) supporting the django-pyodbc-azure project. I highly recommend using it and also the native linux SQL Server ODBC driver over FreeTDS.

score 2 · Answer 2 · answered Sep 24 '15 at 22:57

I've had my best luck with the following stack, and we're a Python 3 shop exclusively:

FreeTDS 0.95
pyodbc 3.0.10
django-pyodbc-azure 1.8.3 (assuming Django 1.8+)

Assuming you have odbc.ini, odbcinst.ini, and freetds.conf all squared away, here's an example of settings that work for me. Some of these depend on whether or not you're using SQL Server 2008+:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'HOST': 'yourserver.com',
        'PORT': '1433',
        'NAME': 'your_db',
        'USER': 'your_user',
        'PASSWORD': 'your_pw',
        'AUTOCOMMIT': True,
        'OPTIONS': {
            'driver': 'FreeTDS',
            'unicode_results': True,
            'host_is_server': True,
            'extra_params': 'tds_version=7.2;',
        },
    },
}

You'll also need to include 'use_legacy_datetime': True, if you're running SQL Server 2005 or less, otherwise, it will use the new SQL Server 2008+ date fields. It will also automatically set to true if you're using an outdated driver. Good luck!

Thanks but is Pyodbc dependent on FreeTDS? Is there a Windows equivalent? I believe FreeTDS is only for a unix environment. Thanks! — Will.Beasley, Sep 25 '15 at 01:52

Evandro Pomatti · Answer 3 · 2022-06-18T21:03:30.060

2

Official backend fork from Microsoft: Microsoft MSSQL Django

They also have this reference Azure SQL Database Django sample.

Set 'ENGINE': 'mssql' to use it. Example:

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",
        },
    },
}

edited Jun 18 '22 at 21:03

answered Jun 18 '22 at 20:40

Evandro Pomatti

13,341
16
97
165

1

In 2022 this seems like the most frequently updated package is the best choice compared to django-pyodbc-azure – Alexander Kvist Dec 16 '22 at 13:14

score 1 · Answer 4 · answered Nov 03 '20 at 11:46

1

Meanwhile there is django-mssql-backend for MSSQL support and this how my settings look like (Django 3.0, django-mssql-backend 2.8.1)

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': os.getenv('DB_NAME'),
        'USER': os.getenv('DB_USER'),
        'PASSWORD': os.getenv('DB_PASSWORD'),
        'HOST': os.getenv('DB_HOST'),
        'PORT': os.getenv('DB_PORT'),
        'TEST': {
            'NAME': os.getenv('DB_NAME'),
        },
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
            'extra_params': 'MARS_Connection=Yes'
        },
    }
}

answered Nov 03 '20 at 11:46

lhaferkamp

676
8
10

This is the working solution. Installing `django-pyodbc-azure` will downgrade my Django to 2.x. – Jari Turkia Jul 13 '23 at 16:50
I'll have to walk back my previous comment. `django-pyodbc-azure` will not work for DB migrations, solution is to use `mssql-django`. See: https://stackoverflow.com/a/68985493/1548275 – Jari Turkia Jul 16 '23 at 16:18

How to use Django with Sql Server

4 Answers4

Linked