Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment | ASSIST Software Romania
get in touch

LIKE

SHARE

FOLLOW

ASSIST Software - Andrei Roman-2

Python Developer at ASSIST

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

I. The challenge

Recently, I received the task to run an application locally using the Django framework (the version used at that time was version 2.0.7) in combination with Python 3.6.5. The database I used for this runs on MSSQL server.
Opting for an MSSQL database was necessary for this project since an external app, also running on MSSQL, populates databases with data containing objects.
 

II. Problems and solutions to problems

In order to optimize the app in terms of speed and project size, I opted for django-mssql. After checking the documentation, one of the problems I noticed was that the package only supports Django 1.8 and I needed to use version 2.0.7 to my project.
Installing MSSQL server on the operating system was problematic due to setup versioning related to Ubuntu 18. Ubuntu 16.04 is the most recent version of Ubuntu that is currently supported.
 
In order to solve the first problem, we found django-pyodbc-azure, which is “a modern fork of django-pyodbc, a Django Microsoft SQL Server external DB backend that uses ODBC by employing the pyodbc library”. It supports Django 2.0.x and most importantly, it also supports Microsoft SQL Server.
To solve the second problem, we adopted the use of Docker technology so we could create a container based on the microsoft/mssql-server-linux, image provided by Microsoft. 
 

II.1 Docker settings

If you want to install Docker on your Linux system, please check out this link and if you want to find out more about Docker, please go to the official page. In addition, you need to install Docker Compose and this can be done by following this tutorial. After you have installed Docker and Docker Compose on your system, you have to download the image you want and create a new container based on that image.

Inside your Django app, create a new folder called docker_config. In this folder, create a new file called docker-compose.yml with the content below. I prefer keeping all of the Docker settings together in the docker_config folder, but this is not compulsory.

 

version: '3'

services:
  db:
    image: microsoft/mssql-server-linux
    environment:
      SA_PASSWORD: StrongPass123
      ACCEPT_EULA: Y
      MSSQL_PID: Developer
    ports:
      - "1433:1433"

 

In the docker-compose.yml file, you must specify what image to use inside the container and add some variables (such as password and port) in order to set MSSQL inside the container The image I used is microsoft/mssql-server-linux. For more information about what can be configured for this image, click here.
In order to see all of the Docker images, run docker images. A list with all of the images will be shown. In the example below, the list is empty.

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman 
After creating the docker-compose.yml file, run the command below in order to create the container.

 

 docker-compose up -d

 

You will see that the image will be downloaded.
 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
Now, if you run docker images, you will see that microsoft/mssql-server-linux has been downloaded.
 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman Furthermore, if you type docker ps, you will see a list of containers. In order to check if your container is running, type docker ps -a.

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
If the container is not running, docker start <container_id> will start the container.
In the example below, <container_id> is 9f2f18715afe.
 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
If you want to stop running a specific container, you can use:

docker stop <container_id>.

In order to run some commands inside a container, run the following command. If everything is ok, you will be inside the container bash.

docker exec -i -t <container_id> /bin/bash

You can access MSSQL server by running:

 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa

The default username for MSSQL server is sa. After you run this command, type the password written in the docker-compose.yml file. If everything is ok, you will be able to make interrogations inside mssql.

 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

Now that you have configured the Docker container, you can check the connection from the outside of the container. This can be done by running the following script written in Python.

import pyodbc
    server = '127.0.0.1'
    username = 'sa'
    password = 'StrongPass123'
    PORT = '1433'

    cnxn = pyodbc.connect(
        'DRIVER={FreeTDS};SERVER=' +
        server + ';PORT=' + PORT + ';UID=' + username + ';PWD=' + password)
    cursor = cnxn.cursor()

    print ('Using the following SQL Server version:')
    tsql = "SELECT @@version;"
    with cursor.execute(tsql):
        row = cursor.fetchone()
        print (str(row[0]))

 

The results:
 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
This error will occur if you don’t have FreeTDS installed on your system.
 

II.2 FreeTDS settings

“FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases.” 
I had some trouble understanding why it was necessary to install this package and how to install and configure it. The first time I installed a version of FreeTDS, older than version 7.3 (version 4.2, to be more specific) and  I received the following error:
 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
This occurred because django-pyodbc-azure 2.0 requires at least TDS protocol 7.3 (or later versions) because it doesn't support the legacy datetime type anymore. In order to remove FreeTDS version 4.2, I ran the following command:
 
sudo apt-get autoremove freetds-dev freetds-bi

 

In order to install the latest version of FreeTDS, run the following commands:
 

sudo apt-get install wget
sudo apt-get install build-essential
sudo apt-get install libc6-dev


To find the latest version of FreeTDS, check this link. Then run the following command by changing the version of FreeTDS (at the time of writing this article, the latest version was freetds-1.00.92).

wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.00.92.tar.gz
tar -xzf freetds-1.00.92.tar.gz
cd freetds-1.00.92
./configure --prefix=/usr/local --with-tdsver=7.3
sudo make
sudo make install

After FreeTDS is installed, it must be configured. In the /etc/ folder, add or update the following files with the content shown below.

odbcinst.ini

[FreeTDS]
Description = v0.91 with protocol v7.3
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
odbci.ini

[dbserverdsn]
Driver = FreeTDS
Server = 127.0.0.1
Port = 1433
TDS_Version = 7.3
freetds/freetds.conf
	
[global]
    # TDS protocol version, use:
    # 7.3 for SQL Server 2008 or greater
    # 7.2 for SQL Server 2005
    # 7.1 for SQL Server 2000
    # 7.0 for SQL Server 7
    tds version = 7.3
    port = 1433

;   dump file = /tmp/freetds.log
;   debug flags = 0xffff

    # Command and connection timeouts
;   timeout = 10
;   connect timeout = 10

    # If you get out-of-memory errors, it may mean that your client
    # is trying to allocate a huge buffer for a TEXT field.
    # Try setting 'text size' to a more reasonable limit
    text size = 64512

    # A typical Microsoft server
    [dbserverdsn]
        host = 127.0.0.1
        port = 1433
        tds version = 7.3

If you want to check the FreeTDS version, type tsql -C and you will receive an output like the one shown below.
 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
If you try to run the script again, you will receive the following output.
 Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman
You can access the MSSQL from your container by typing:

tsql -S 127.0.0.1 -U sa

II.3 Configure your Django app with MSSQL

In order to configure your Django app to run on MSSQL server, you must have the following packages installed:

django-pyodbc-azure==2.0.4.1
pyodbc==4.0.23

and you must update the settings or local_settings file as shown below.

DB_ENGINE = 'sql_server.pyodbc'
DB_NAME = 'django_mssql_test'
DB_USER = 'sa'
DB_PASSWD = 'StrongPass123'
HOST = '127.0.0.1'
PORT = '1433'
DB_OPTIONS = {
    'driver': 'FreeTDS',
    'unicode_results': True,
    'host_is_server': True,
    'extra_params': 'tds_version=7.3;',
}

DATABASES = {
    'default': {
        'ENGINE': DB_ENGINE,
        'NAME': DB_NAME,
        'USER': DB_USER,
        'PASSWORD': DB_PASSWD,
        'HOST': HOST,
        'PORT': PORT,
        'OPTIONS': DB_OPTIONS
    }
}

After running the migrations, you will see that all of the tables will be created in the MSSQL database from the container.  Integrating MSSQL server with Django 2.0.7 and Python 3.6.5 using Ubuntu 18 as a local environment - ASSIST Software - Andrei Roman

III. Conclusion

Most of the time, updating packages or operating systems to the latest version can create compatibility issues. This becomes a problem when you work on several projects. With the help of Docker technology and the package django-pyodbc-azure, you will be able to connect your Django application to MSSQL database locally using the latest version of Ubuntu (Ubuntu 18), even though MSSQL is not officially supported yet.

Ready to make it happen?

Drop us a line. We’d love to hear from you and see how we can help in solving your digital challenges. As one of the best software outsourcing companies in Romania, Eastern Europe, Europe and the world really, we are sure we can ASSIST.

GET IN TOUCH