Django 2.1 SQL Server 17 error - isn't an available database backend. Try using 'django.db.backends.XXX'

Question

django.core.exceptions.ImproperlyConfigured: 'django-pyodbc-azure' isn't an available database backend.
Try using 'django.db.backends.XXX', where XXX is one of:
    'mysql', 'oracle', 'postgresql', 'sqlite3'

I have tried switching to the following DATABASE settings, with no luck.

DATABASES = {
    'default': {
        'ENGINE': 'django-pyodbc-azure',  #None of these work either: 'django_pyodbc' 'sqlserver_ado', 'sql_server.pyodbc', 'django-pyodbc-azure','pyodbc'
        'HOST': 'test-server-local',
        'NAME': 'db-name',
        'USER': 'sql_username',
        'PASSWORD': 'password',
        'PORT': '1433'
            # 'OPTIONS': {
            # 'driver': 'ODBC Driver 17 for SQL Server'
            # 'driver': 'SQL Server Native Client 11.0',
            # 'provider': 'SQLOLEDB' # Have also tried 'SQLCLI11' and 'SQLOLEDB'
            # 'extra_params': 'DataTypeCompatibility=80'
        # }
    }
}

I was able to hit a SQLLite database, and am able to run a pyodbc import (directly via Python) but am not able to use this in Django.

I referred to the following questions on stack overflow already (with no luck). Solution 1 Solution 2 and the Django/SQL Server versions they have in place are quite older than what I'm set up with. I wouldn't want to downgrade from Django v2.1 to a lower one, just to support the SQL Server database without checking if there are other options.

I verified and added the following site-packages into my environment/system path variables:

    C:\Users\\AppData\Local\Programs\Python\Python37-32\Lib\site-packages
    - django_pyodbc
    - django_pyodbc-2.0.0a1.dist-info
    - django_mssql-1.8.dist-info
    - django_pyodbc_azure-2.1.0.0.dist-info

Running the following code does hit the database and get data back, so I know it's not just Python, but probably Django.

import pyodbc 
server = 'test-server-local' 
database = 'TEST-DB'
username = 'sql_username'
password = 'password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

cursor.execute("SELECT DepartmentName, DepartmentID from dbo.Departments;") 
row = cursor.fetchone() 
while row: 
    print(row.DepartmentID, row.DepartmentName)
    row = cursor.fetchone()

FlipperPA · Accepted Answer · 2018-08-24 01:46:58Z

4

Here's a full setup that is verified as working, as long as django-pyodbc-azure is installed in your current virtualenv:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'HOST': 'dbserver.your-domain.com',
        'PORT': '1433',
        'NAME': 'project',
        'USER': 'project_user',
        'PASSWORD': 'project_password',
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
            'unicode_results': True,
        },
    },
}

Full instructions are here: https://pyphilly.org/django-and-sql-server-2018-edition/

answered Aug 24, 2018 at 1:46

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Loser Coder Over a year ago

I get the same error even when I use this setting. django.core.exceptions.ImproperlyConfigured: 'sql_server.pyodbc' isn't an available database backend. Try using 'django.db.backends.XXX', where XXX is one of: 'mysql', 'oracle', 'postgresql', 'sqlite3'

Loser Coder Over a year ago

I tried adding in the 'INSTALLED_APPS' 'django-pyodbc-azure' but there's a different error now - ModuleNotFoundError: No module named 'django-pyodbc-azure'

Loser Coder Over a year ago

UPDATE: I added in 'INSTALLED_APPS': 'sql_server' and 'pyodbc' and progressed to the next steps/error: So it does look like it's trying to hit the db now. The user is setup as an admin/all access user to this database, so I'm not sure what's missing...django.db.utils.InterfaceError: ('28000', '[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user \'project_user\'. (18456); Cannot open database "db-test" requested by the login. The login failed. (4060); [28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0)')

FlipperPA Over a year ago

Are you using a virtualenv? It sounds like it isn't finding it in your PYTHON_PATH. You shouldn't have to add them to INSTALLED_APPS if you've got your virtualenv set up properly. The error you're getting now is because you're using the wrong credentials (provided in the example)... but I'd still check your pathing and virtualenv. Good progress!

Loser Coder Over a year ago

I got this to work. Is there a way to run some sql stored procedures instead of using the ORM model mapping? Is is okay to do this, or not a recommended practice?

|

Tim Richardson · Accepted Answer · 2019-11-24 02:42:47Z

1

I had the same problem as OP, exactly. I am using PyCharm. Finally I concluded that my virtual environment was not working properly. I could not see what the problem was ... from a python console I could import sql_server, so it was on the path, but my tests in pycharm did not agree, giving the same error as above.

So I made a new virtual environment, this time from within pycharm, reinstalled requirements.txt and it worked.

Perhaps I had some subtle error in pycharm configuaration that was somehow fixed by created a venv from within pycharm. Maybe some weird Windows thing.

answered Nov 24, 2019 at 2:42

Tim Richardson

7,3516 gold badges52 silver badges85 bronze badges

Collectives™ on Stack Overflow

Django 2.1 SQL Server 17 error - isn't an available database backend. Try using 'django.db.backends.XXX'

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related