3
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()
Loser Coder
  • 2,338
  • 8
  • 42
  • 66

2 Answers2

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/

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • 3
    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 Aug 24 '18 at 18:12
  • 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 Aug 24 '18 at 18:40
  • 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)') – Loser Coder Aug 24 '18 at 18:53
  • 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! – FlipperPA Aug 24 '18 at 18:56
  • 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? – Loser Coder Aug 28 '18 at 20:22
  • @LoserCoder It isn't a recommended practice, but that's a more complex question. I use Django with a PostgreSQL database for the main Django site, but also pull data into views from stored procedures in SQL Server. Something like `from django.db import connections; cursor = connections['mssqldb'].cursor(); res = cursor.execute('EXEC get_user_data @username=%s, user_type=%s', (username, 'staff'))`, then loop over `res` for the results of the stored proc, row by row. For my main Django site, I stick to models and the ORM; it is worth learning and will save you lots of pain! – FlipperPA Aug 28 '18 at 21:10
  • I am open to either approach, as long as it's easy to maintain :) ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000') I get this exception - I was getting another error just before I fixed this: TypeError: not all arguments converted during string formatting – Loser Coder Aug 28 '18 at 23:50
  • I would definitely recommend learning the ORM, in that case! Are you sure you are using the `cursor` from `django.db.connections` and not `pyodbc`? pyodbc uses `?` for its placeholder, whereas Django uses `%s`. – FlipperPA Aug 29 '18 at 01:29
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.

Tim Richardson
  • 6,608
  • 6
  • 44
  • 71