Django connect to SQL Server - django/sql_server - pyodbc

Question

I've tried to install django_pyodbc but when I try make migrations got the error:

django.core.exceptions.ImproperlyConfigured: Django 2.1 is not supported.

My setttings.py:

'Test_DB': {
    'ENGINE': 'django_pyodbc',
    'NAME': 'TEST',
    'HOST': '127.0.0.1',
    'USER': 'sa',
    'PASSWORD': '123456',
    'OPTIONS': {
        'driver': 'ODBC Driver 12 for SQL Server',
    },
},

When I try to install django-pyodbc-azure, I got the other error:

Try using 'django.db.backends.XXX', where XXX is one of: 'mysql', 'oracle', 'postgresql', 'sqlite3'

My setttings.py:

'Test_DB': {
    'ENGINE': 'sql_server.pyodbc',
    'NAME': 'TEST',
    'HOST': '127.0.0.1',
    'USER': 'sa',
    'PASSWORD': '123456',
    'OPTIONS': {
        'driver': 'ODBC Driver 12 for SQL Server',
    },
},

So what should I do so that I can connect the SQL Server 2012?

Answer 1

I did search for this issue for a long time.

Really feeling piss that no one really tells the details that's why I want to write down to help those are about to face this question.

I found out that I should do the following produces so that I can run pyodbc in Django.

1. First Install "ODBC Driver 11 for SQL Server & Install pyodbc"

1. Since my server are using ODBC Driver 11 to extract to data, I should switch it from 17 to 11

2. Run pip install pyodbc in terminal

2. settings.py:

DATABASES = {
'MSSQL':
{
    'ENGINE': 'sql_server.pyodbc',
    'NAME': 'DB_NAME',
    'USER': 'USER',
    'PASSWORD': 'PWD',
    'HOST': 'IP',
    'PORT': '1433',
    'OPTIONS':{
        'driver': 'ODBC Driver 11 for SQL Server',
    },
}

}

3. views.py:

import pyodbc
from django.conf import settings

connection = pyodbc.connect(
                            "Driver={" + settings.DATABASES['MSSQL']['OPTIONS']['driver'] + "};"
                            "Server=" + settings.DATABASES['MSSQL']['HOST'] + ";"
                            "Database=" + settings.DATABASES['MSSQL']['NAME'] + ";"
                            "UID=" + settings.DATABASES['MSSQL']['USER'] + ";"
                            "PWD=" + settings.DATABASES['MSSQL']['PASSWORD'] + ";"
                            "Trusted_Connection=no;"
                            )

cursor = self.connection.cursor()
query = """SELECT * FROM DB_NAME;"""
cursor.execute(query)
rows = cursor.fetchall()
columns = [column[0] for column in cursor.description]
data = []