Python Django learnings
Moving an app from an extremely outdated code base into Django. Some learnings:
Super quick start into Docker
1pip3 install "cookiecutter>=1.7.0"
2cookiecutter 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
1curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
2curl https://packages.microsoft.com/config/debian/10/prod.list | tee /etc/apt/sources.list.d/msprod.list
3apt-get update
4ACCEPT_EULA=Y apt-get install -y unixodbc unixodbc-dev mssql-tools g++
5apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false
6rm -rf /var/lib/apt/lists/*
7
8cp /opt/microsoft/msodbcsql17/etc/odbcinst.ini /etc/odbcinst.ini
9cat /etc/odbcinst.ini
10pip install pyodbc
11pip install mssql-django
in base.py
1DATABASES = {
2 'default': {
3...
4 'OPTIONS': {
5 'driver': 'ODBC Driver 17 for SQL Server',
6 },
7 },
8}
How to get a Django model file from an existing database
1./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=Trueto the column metadata
Basic POST handler
in views.py
1from django.core.handlers.wsgi import WSGIRequest
2...
3def <FUNCTNAME>(request: WSGIRequest):
4 if request.method == 'POST':
5 action = request.POST.get('action', 'Search')
Simple way of getting the submitted values.
Creating Excel files
1pip install XlsxWriter
1import io,os
2from django.http import HttpResponse
3import xlsxwriter
4...
5 output = io.BytesIO()
6 workbook = xlsxwriter.Workbook(output)
7 bold_format = workbook.add_format({'bold': True})
8 bold_date_format = workbook.add_format({'bold': True, 'num_format': 'dd/mm/yyyy hh:mm'})
9
10 worksheet = workbook.add_worksheet()
11 worksheet.set_row(0, 65.25)
12 worksheet.insert_image('A1', os.path.dirname(os.path.realpath(__file__)) + '/<IMAGEFILE>')
13 worksheet.write('A2', 'Rundate')
14 worksheet.write('B2', founds[0].datetime.replace(tzinfo=None), bold_date_format)
15 workbook.close()
16 # Rewind the buffer.
17 output.seek(0)
18
19 # Set up the Http response.
20 filename = 'django_simple.xlsx'
21 response = HttpResponse(
22 output,
23 content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
24 )
25 response['Content-Disposition'] = 'attachment; filename=%s' % filename
26
27 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
1<MODEL>.objects.extra(
2 select={
3 'NiceNameOne': 'fieldone',
4 'NiceNameTwo': 'fieldtwo'
5 }
6).values(
7 'NiceNameOne',
8 'NiceNameTwo',
9).order_by(
10 'NiceNameOne'
11).distinct(
12)
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?