Python Django learnings

Moving an app from an extremely outdated code base into Django. Some learnings:

Super quick start into Docker

pip3 install "cookiecutter>=1.7.0"
cookiecutter https://github.com/pydanny/cookiecutter-django

"Cookiecutter creates projects from project templates, e.g. Python package projects." Does what it says on the tin. Managed to get a dockerised Django+Python+MSSQL(!?!) running in next to no time.

MSSQL in Django

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/debian/10/prod.list | tee /etc/apt/sources.list.d/msprod.list
apt-get update
ACCEPT_EULA=Y apt-get install -y  unixodbc  unixodbc-dev  mssql-tools  g++
apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false
rm -rf /var/lib/apt/lists/*

cp /opt/microsoft/msodbcsql17/etc/odbcinst.ini /etc/odbcinst.ini
cat /etc/odbcinst.ini
pip install pyodbc
pip install mssql-django

in base.py

DATABASES = {
    'default': {
...
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    },
}

How to get a Django model file from an existing database

./manage.py inspectdb > models.py

Gotchas:

  • In the latest Django/ Python I've had to add app_label = '<app-name-in-INSTALLED-APPS>' for it to use
  • Not all the tables pulled across had keys. You need one and only one primary key in each table/ view for Django to work. Find the most key like field and add primary_key=True to the column metadata

Basic POST handler

in views.py

from django.core.handlers.wsgi import WSGIRequest
...
def <FUNCTNAME>(request: WSGIRequest):
    if request.method == 'POST':
        action = request.POST.get('action', 'Search')

Simple way of getting the submitted values.

Creating Excel files

pip install XlsxWriter
import io,os
from django.http import HttpResponse
import xlsxwriter
...
            output = io.BytesIO()
            workbook = xlsxwriter.Workbook(output)
            bold_format = workbook.add_format({'bold': True})
            bold_date_format = workbook.add_format({'bold': True, 'num_format': 'dd/mm/yyyy hh:mm'})

            worksheet = workbook.add_worksheet()
            worksheet.set_row(0, 65.25)
            worksheet.insert_image('A1', os.path.dirname(os.path.realpath(__file__)) + '/<IMAGEFILE>')
            worksheet.write('A2', 'Rundate')
            worksheet.write('B2', founds[0].datetime.replace(tzinfo=None), bold_date_format)
            workbook.close()    
            # Rewind the buffer.
            output.seek(0)

            # Set up the Http response.
            filename = 'django_simple.xlsx'
            response = HttpResponse(
                output,
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            response['Content-Disposition'] = 'attachment; filename=%s' % filename

            return response

More specifics

  • XLSXwriter seems to be the Uber-good one if you're not running on Windows. If you are running solely on Windows you can use a COM bridge to get access to all the Excel functionality directly.
  • Dates don't work with XLSXwriter if they have timezones. So you have to do that <FIELD>.replace(tzinfo=NONE) to use it
  • Dates come across as numbers, so you then have to ensure the cell you're writing into has the date format you want.
  • The output = io.BytesIO() captures the output to screen; so once you close the workbook you can then rewind the output and send that raw with the right headers

Filtering result sets

Filtering seems fine

<MODEL>.objects.extra(
    select={
        'NiceNameOne': 'fieldone',
        'NiceNameTwo': 'fieldtwo'
    }
).values(
    'NiceNameOne',
    'NiceNameTwo',
).order_by(
    'NiceNameOne'
).distinct(
)

However I haven't found any way to limit the results in the query itself (e.g. return 20 rows, skipping the first 30). Everything I've read runs the whole query and then does a [30:20] subset nonsense thing. That doesn't save data query time. Anyone?