Given an ODBC connection string for a database, how can I modify my settings.py such that my Django app will connect to it?

Question

I have been given an ODBC connection string to connect to a database. However, I am having difficulty in having my Django app to connect to it. What are the proper steps to have my app to connect to it? Am I missing anything in my code?

This is my connection string:

Driver={SQL Anywhere 17};uid=user;pwd=secret;server=databasename;astart=No;host=127.0.0.1;port=1234

Using a test script that uses pyodbc I am able to connect to the database. However, when I attempt to modify the settings.py file for my app, it fails to connect to the database.

Requirements.py

django~=2.1.4
pyodbc~=4.0.25
django-pyodbc-azure~=2.1.0.0

Settings.py

#First Attempt
DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'HOST': '127.0.0.1,1234',
        'USER': 'user',
        'PASSWORD': 'secret',
        'NAME': 'databasename',
        'PORT': '1234',
        'OPTIONS': {
            'driver' : 'SQL Anywhere 17',
            'host_is_server': 'True',
        },
    }
}

#Second Attempt
DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'HOST': '',
        'NAME': 'databasename;',
        'PORT': '',
        'OPTIONS': {
            'host_is_server': 'True',
            'dsn': 'Driver={SQL Anywhere 17};uid=user;pwd=secret;server=databasename;astart=No;host=127.0.0.1;port=1234'
        },
    }
}

For my first attempt, I get the following error:

django.db.utils.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

For my second attempt, I am getting the following error:

django.db.utils.Error: ('IM010', '[IM010] [Microsoft][ODBC Driver Manager] Data source name too long (0) (SQLDriverConnect)')

I am unsure what I am doing wrong. Any help would be greatly appreciated.

Answer 1

Welcome to Stack Overflow, Benjamin! Give this a try:

DATABASES = {
    "default": {
        "ENGINE": "sql_server.pyodbc",
        "HOST": "127.0.0.1",
        "PORT": "1234",
        "NAME": "databasename",
        "USER": "user",
        "PASSWORD": "secret",
        "AUTOCOMMIT": True,
        "OPTIONS": {
            "driver": "SQL Anywhere 17",
            "unicode_results": True,
            "host_is_server": True,
        },
    },
}

One key here is the "driver" setting. It has to match the title of the driver section in your odbcinst.ini file. For example, I have the following in my /etc/odbcinst.ini:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1

In my case, I use: "driver": "ODBC Driver 17 for SQL Server", in the OPTIONS dictionary, so it matches the heading title in the odbcinst.ini configuration file. This is where I've seen most people get tripped up. Good luck!