CODEWITHSUNDEEP
pythonsql-serverpython-3.xdjangodjango-mssql
user1424311
user1424311

Reputation: 417

Connecting Django with MSSQL server

I'm trying to connect my Django app to SQL Server 2016. I've tried using django-pyodbc but it doesn't support Django 1.11. Instead I installed django-mssql 1.8. When I try to run the application I get this error.

TypeError was unhandled by user code
Message: 'NoneType' object is not callable

At execute_from_command_line(sys.argv) in manage.py.

Here is my DATABASES from settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'sqlserver_ado',
        'NAME': 'TEST2',
        'HOST': 'PCNAME\SQLEXPRESS',
        'USER': '',
        'PASSWORD': '',
        'OPTIONS' : {
            'provider': 'SQLOLEDB',
            'use_mars': True,
        },
    }
}

I've tried both the default and SQLOLEDB provider but always get the same error. I've also tried with and without user and password set but the error remains the same. I am able to connect to a local MySQL DB just fine.

I'm running Windows 10, Visual Studio 2015, SQL Server Express 2016

Edit:

Here's the output from pip freeze:

appdirs==1.4.3
Django==1.11
django-mssql==1.8
mysqlclient==1.3.10
packaging==16.8
pyodbc==4.0.16
pyparsing==2.2.0
pytz==2017.2
six==1.10.0

Here's my requirements.txt:

django==1.11
mysqlclient==1.3.10
django-mssql==1.8

Upvotes: 8

Views: 41333

Answers (6)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

Using mssql-django, we can connect Django to MSSQL(SQL Server) with Windows Authentication and SQL Server Authentication. I use SQL Server 2019 Express.

With Windows Authentication, to connect Django to MSSQL using mssql-django, set the code below to "settings.py". This code below is the example of Django and MSSQL in the same Windows Computer(localhost), the database setting must be configured as follows:

  • ENGINE must be "mssql"
  • NAME is for Database name "test"
  • HOST is the Windows Computer Name(Device name) in this case "DESKTOP-QVRCPTA"
  • PORT should be kept blank because there will be error if setting any port number e.g. "2244", "9877" or even "1433" which is the default port number of MSSQL
# "settings.py"

DATABASES = {
    'default':{
        'ENGINE':'mssql',                    # Must be "mssql"
        'NAME':'test',                       # DB name "test"
        'HOST':'DESKTOP-QVRCPTA\SQLEXPRESS', # <server>\<instance>
        'PORT':'',                           # Keep it blank
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    }
}

In addition, "DESKTOP-QVRCPTA" can be replaced with "localhost" and "USER" and "PASSWORD" can be put with empty string and "PORT" can be removed as shown below to connect Django to MSSQL with Windows Authentication:

# "settings.py"

DATABASES = {
    'default':{
        'ENGINE':'mssql',
        'NAME':'test',
        'USER':'',                     # Keep it blank
        'PASSWORD':'',                 # Keep it blank
        'HOST':'localhost\SQLEXPRESS', # "localhost" is also possible
        # 'PORT':'',                   # Can be removed
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    }
}

With SQL Server Authentication, to connect Django to MSSQL using mssql-django, set the code for Windows Authentication as shown above with "USER" and "PASSWORD" to "settings.py". The difference between the code for Windows Authentication and SQL Server Authentication is only "USER" and "PASSWORD":

# "settings.py"

DATABASES = {
    'default':{
        'ENGINE':'mssql',  
        'NAME':'test',           
        'USER':'john',                       # Username "john"
        'PASSWORD':'johnpw',                 # Password "johnpw"
        'HOST':'DESKTOP-QVRCPTA\SQLEXPRESS',
        'PORT':'',                           
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    }
}

In addition, same as the code for Windows Authentication, "DESKTOP-QVRCPTA" can be replaced with "localhost" and "PORT" can be removed as shown below to connect Django to MSSQL with SQL Server Authentication:

# "settings.py"

DATABASES = {
    'default':{
        'ENGINE':'mssql',
        'NAME':'test',
        'USER':'john',        
        'PASSWORD':'johnpw',
        'HOST':'localhost\SQLEXPRESS', # "localhost" is also possible
        # 'PORT':'',                   # Can be removed   
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    }
}

Next, install the latest package mssql-django:

pip install mssql-django

Then, make migrations and migrate:

python manage.py makemigrations && python manage.py migrate

Then, create superuser:

python manage.py createsuperuser

Now, we could connect Django to MSSQL with Windows Authentication and SQL Server Authentication, then create the tables for "test" database.

Upvotes: 6

shmnff
shmnff

Reputation: 739

Try to install pyodbc, django-mssql-backend modules and set sql_server.pyodbc engine settings.

Upvotes: 0

bhargav3vedi
bhargav3vedi

Reputation: 619

Please check this link. First you need to install mssql-django package and database configuration should be like

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "Todo",
        "USER": "",
        "PASSWORD": "",
        "HOST": "127.0.0.1",
        "PORT": "1433",
        "OPTIONS": {"driver": "ODBC Driver 17 for SQL Server",
                    },
    },
}

Upvotes: 1

Robert Lujo
Robert Lujo

Reputation: 16371

Following official django documentation (currently django 3.1) django-mssql-backend should be used. Django-MSSQL-backend django database adapter is a fork of django-pyodbc-azure which:

  • Supports Django 2.2, 3.0
  • Supports Microsoft SQL Server 2008/2008R2, 2012, 2014, 2016, 2017, 2019
  • Compatible with Micosoft ODBC Driver for SQL Server, SQL Server Native Client, and FreeTDS ODBC drivers

Other solutions django-pyodbc-azure, django-sqlserver and django-mssql as of 2020-11 look to be obsolete.

Upvotes: 4

xalien
xalien

Reputation: 143

You can use django-pyodbc-azure because it has support up to current versions of django 2.0. After installation you need to edit in your settings file like below:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': DB_NAME,
        'USER': USER,
        'PASSWORD': PASSWORD,
        'HOST': HOST,
        'PORT': PORT,
        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',
            'unicode_results': True,

        },
    }
}

If you install TDS library as driver then your driver will be 'driver':'Free TDS' Here 13 is the default version. If your installed version is different from that then use that version number instead of 13

Upvotes: 3

rparent
rparent

Reputation: 628

As stated in the django-mssql documentation, the latest release only supports Django 1.8, so it won't work with Django 1.11.

You will have to wait until the package supports newer versions of django to upgrade. That is the problem when using Django with non-supported database backends, you depend on the third party packages maintenance, and this one seems to have trouble staying up to date with Django.

Upvotes: 6

Related Questions