12

I'm trying to connect my Django app to microsoft sql database on apache server but I get the following error messages:

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'

I have installed django-pyodbc-azure and it's showing up as part of (pip freeze list):

Django==2.1
django-pyodbc==1.1.3
django-pyodbc-azure==2.1.0.0
pyodbc==4.0.25

Here is settings.py database configuration:

'default': {
    'ENGINE': 'sql_server.pyodbc',
    'NAME': 'name',
    'USER': 'user',
    'PASSWORD': 'password',
    'HOST': 'host',
    'PORT': '',

    'OPTIONS': {
        'driver': 'ODBC Driver 17 for SQL Server',
    },

},

Inside site-packages folder, it's doesn't show the actual django-pyodbc-azure folder but when i run the command (pip show django-pyodbc-azure), it shows the package location (/usr/local/lib/python3.5/dist-packages) which means it's successfully installed.

So I'm not really sure what is the problem.

Mack
  • 343
  • 2
  • 5
  • 12
  • It doesn't appear you're using a virtual environment, which I would highly recommend. `/usr/local/lib/python3.5/` would be the system Python version, not your virtual environment. – FlipperPA Feb 22 '19 at 11:14
  • @FlipperPA Thanks for your reply, so If I use virtual environment and install that django-pyodbc-azure inside it will actually solve my problem? – Mack Feb 23 '19 at 03:34
  • I'm guessing that is the most likely culprit. I always use virtual environments for every Python project; it is worth learning, as it will save you lots of headaches down the road! – FlipperPA Feb 24 '19 at 00:56
  • @FlipperPA Can I create more than one virtual environment within a project? Because I forgot the name of the first one – Mack Feb 24 '19 at 03:35
  • Yes, you absolutely can. I'd recommend checking out the Django Girls tutorial here for a bit of information on virtual environments: https://tutorial.djangogirls.org/en/django_installation/ – FlipperPA Feb 25 '19 at 22:50
  • @FlipperPA I've managed to get the name of the virtual environment, I have installed django-pyodbc-azure in it and that fixed the issue :D Thank you – Mack Feb 26 '19 at 01:20
  • I know you've solved this @Mack but I wanted to stress using virtual environments - they allow you to isolate them so you can keep packages all based on what's needed per project/application. Combining those with a `requirements.txt` file is the best thing you can do for any python/django application! – Hanny Nov 19 '19 at 13:19
  • 3
    I'd love to understand why using a virtual environment fixed this problem, as this doesn't seem logical to me. I get that they are always a great idea, but how can the problem have been solved by just using one? Keep safe, guys... – Jonathan Jun 18 '20 at 11:05
  • 1
    @Jonathan, I think the answer is "cruft" or "clutter". If you don't use a virtual environment, you will inevitably run into namespace collisions, at which point you have no idea which library you're actually pulling modules in from. This definitely seems to be the case for this issue. – Robert Rapplean Apr 05 '23 at 00:19

5 Answers5

9

I had the same problem. Somehow the installation of the azure-backend messed up my project.

I removed the django-pyodbc-azure and django-mssql-backend packages since they only support the older django versions. Afterwards i installed the mssql backend from https://github.com/microsoft/mssql-django

pip uninstall django-pyodbc-azure
pip uninstall django-mssql-backend
pip install mssql-django

Then i configured the DB to use 'ENGINE': 'mssql'

After this i was able to connect to our MSSQL DB using Django 3.2 and newer Versions!

eckad158
  • 385
  • 6
  • 19
2

Try to install pip install django-pyodbc-azure

https://pypi.org/project/django-pyodbc-azure/

I had the same problem and using that it worked.

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'Name_database',
        'USER': 'User',
        'PASSWORD': 'Password',
        'HOST': 'IP',
        'PORT': '1433',
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    }
}
Thaisjt Jt
  • 29
  • 5
2

Use mssql-django with versions of Django >=2.2 and <4.1.
As of today current version of mssql-django==1.1.3 supports Django versions >=2.2 and <4.1 https://github.com/microsoft/mssql-django/blob/c0476cf4e49ab3dcbbab37ccb3e558216841b6dc/setup.py#L41

To use older versions of Django use django-pyodbc-azure.
The support for Django==1.8 was added by https://github.com/microsoft/mssql-django/tree/0b0cee7030795682b37da36a48ebb065a3faa00e and removed by this: https://github.com/microsoft/mssql-django/tree/204d1fc0a4ade0ebe3e1df07a943c03b5ab5cf33.

To use Django==1.8.x use pip install "django-pyodbc-azure<1.9" where it Supports Microsoft SQL Server 2005, 2008/2008R2, 2012, 2014 and Azure SQL Database: https://github.com/microsoft/mssql-django/commit/204d1fc0a4ade0ebe3e1df07a943c03b5ab5cf33#diff-7b3ed02bc73dc06b7db906cf97aa91dec2b2eb21f2d92bc5caa761df5bbc168fR241

Note: if you are using older version of Django (<2.0.0), installing mssql-django will force install/upgrade to newer supported version of django as part of its requirement.
One may need to go through readme.md, setup.py files to know the supported versions.

Achuth Varghese
  • 2,356
  • 1
  • 4
  • 18
  • Thank you for your answer. I already used [this](https://stackoverflow.com/questions/72225169/connecting-to-mssql-from-django-1-8-on-ubuntu) solution. In the comment there is a solution. – xralf May 15 '22 at 16:09
0

I use python:3.9.13-slim-buster, Django 3.2 And install mssql-django

FROM python:3.9.13-slim-buster
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1
ENV DEBIAN_FRONTEND noninteractive
ADD odbcinst.ini /etc/
RUN apt-get update -y && apt-get install -y curl gnupg
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update -y && apt-get install -y unixodbc unixodbc-dev tdsodbc freetds-common freetds-bin freetds-dev postgresql python-scipy python-numpy python-pandas
RUN apt-get update && ACCEPT_EULA=Y apt-get -y install mssql-tools msodbcsql17
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
RUN apt-get update
RUN pip install mssql-django
RUN mkdir /code
COPY . /code/
COPY ./requirements.txt /code/
WORKDIR /code
RUN pip install --no-cache-dir -r requirements.txt
EXPOSE 8000

Create file odbcinst.ini

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

requirements.txt

Django==3.2
django-environ==0.4.5
# psycopg2>=2.8.6,<2.9
uWSGI>=2.0.19.1,<2.1
Pillow >= 9.0.0,<10.0.0

settings.py

DATABASES = {
    'default': {
        'ENGINE': 'mssql',
        'HOST': os.environ.get('DB_HOST_SITO'),
        'NAME': os.environ.get('DB_NAME_SITO'),
        'USER': os.environ.get('DB_USER_SITO'),
        'PASSWORD': os.environ.get('DB_PASS_SITO'),
        'PORT': os.environ.get('DB_PORT_SITO'),
        'OPTIONS': {
            'driver': 'FreeTDS',
            'unicode_results': True,
            'host_is_server': True,
            'driver_supports_utf8': True,
            'extra_params': 'tds_version=7.4',
        }
    },
}
Jose Razo Prieto
  • 1
  • 1
-1

installing django==3.0 would be helpful

Majid Naddafi
  • 1
  • 1