Hey All,
I’m looking for any and all opinions, optimizations and improvements I can make to my current setup.
I’m relatively new to Django and have been loving it so far. For my most recent work project I have connected to Microsoft SQL Server as my primary database and am connecting to tables previously created in SQL via django’s inspectdb generated Models (with adjustments where required).
The Utilization table currently contains 500+ rows and will increase over time, however I have noticed that load times for my tables.html template is very long. I believe this to be due to the large amount of foreign key’s in use for my normalized configuration. I’m looking for porential solutions and also while this post is here, just any general newb mistakes I may have made.
Below is the details to my configuration, I frequently miss details in posts so don’t hesitate to call out anything I’ve missed.
Database Connection:
DATABASES = {
'default': {
'ENGINE': 'sql_server.pyodbc',
'NAME': 'DE_IoT',
'HOST': 'PERWSWF',
'TRUSTED_CONNECTION' : 'Yes',
'OPTIONS': {
'driver': 'ODBC Driver 17 for SQL Server'
}
}
}
Models.py
class Division(models.Model):
id = models.AutoField(db_column='ID', primary_key=True)
division = models.CharField(db_column='Division', max_length=20)
class Meta:
db_table = 'WE_tblDivisions'
def __str__(self):
return self.division
class Manager(models.Model):
id = models.AutoField(db_column='ID', primary_key=True)
manager = models.CharField(db_column='Manager', max_length=50)
class Meta:
db_table = 'WE_tblManagers'
def __str__(self):
return self.manager
class Office(models.Model):
id = models.AutoField(db_column='ID', primary_key=True)
office = models.CharField(db_column='Office', max_length=20)
class Meta:
db_table = 'WE_tblOffices'
def __str__(self):
return self.office
class Phase(models.Model):
id = models.AutoField(db_column='ID', primary_key=True)
phase = models.CharField(db_column='Phase', max_length=50)
class Meta:
db_table = 'WE_tblPhases'
def __str__(self):
return self.phase
class Role(models.Model):
id = models.AutoField(db_column='ID', primary_key=True)
role = models.CharField(db_column='Role', max_length=100)
class Meta:
db_table = 'WE_tblRoles'
def __str__(self):
return self.role
class Project(models.Model):
id = models.CharField(db_column='ID', primary_key=True, max_length=7)
name = models.CharField(db_column='Name', max_length=50)
phase_fk = models.ForeignKey(Phase, models.DO_NOTHING, db_column='Phase_FK', blank=True, null=True)
manager_fk = models.ForeignKey(Manager, models.DO_NOTHING, db_column='Manager_FK', blank=True, null=True)
class Meta:
db_table = 'WE_tblProjects'
def __str__(self):
return self.name
class Staff(models.Model):
id = models.AutoField(db_column='ID', primary_key=True)
firstname = models.CharField(db_column='FirstName', max_length=20)
lastname = models.CharField(db_column='LastName', max_length=20)
ftemax = models.FloatField(db_column='FTEMax', validators = [validators.MinValueValidator(0), validators.MaxValueValidator(1)])
role_fk = models.ForeignKey(Role, models.DO_NOTHING, db_column='Role_FK', blank=True, null=True)
division_fk = models.ForeignKey(Division, models.DO_NOTHING, db_column='Division_FK', blank=True, null=True)
office_fk = models.ForeignKey(Office, models.DO_NOTHING, db_column='Office_FK', blank=True, null=True)
class Meta:
db_table = 'WE_tblStaff'
verbose_name_plural = "Staff"
@property
def full_name(self):
return '%s %s' % (self.firstname, self.lastname)
def __str__(self):
return self.firstname+" "+self.lastname
class Utilization(models.Model):
id = models.BigAutoField(db_column='ID', primary_key=True)
projectid_fk = models.ForeignKey(Project, models.DO_NOTHING, db_column='ProjectID_FK', max_length=7)
staffid_fk = models.ForeignKey(Staff, models.DO_NOTHING, db_column='StaffID_FK')
date = models.DateField(db_column='Date')
utilization = models.FloatField(db_column='Utilization')
class Meta:
db_table = 'WE_tblUtilizationTESTING'
verbose_name_plural = "Utilization"
def __str__(self):
return self.date.strftime('%d-%m-%Y')
Views.py:
from django.shortcuts import render
from django.http import HttpRequest
from app.models import Utilization
def tables(request):
"""Renders the about page."""
assert isinstance(request, HttpRequest)
return render(
request,
'app/tables.html',
{
'utilization': Utilization.objects.all(),
}
)
urls.py
from django.urls import path
from app import views
urlpatterns = [
path('', views.home, name='home'),
path('tables/', views.tables, name='tables')
]
tables.html
{% extends "app/layout.html" %}
{% block content %}
<div class="container">
<div class="row">
<div class="col">
<h2>Staff</h2>
<table width=100%>
<tr>
<th>Project ID</th>
<th>Staff</th>
<th>Date</th>
<th>Utilization</th>
</tr>
{% for object in utilization %}
<tr>
<td class="table-bordered">{{object.projectid_fk}}</td>
<td class="table-bordered">{{object.staffid_fk}}</td>
<td class="table-bordered">{{object.date}}</td>
<td class="table-bordered">{{object.utilization}}</td>
</tr>
{% endfor %}
</table>
</div>
</div>
</div>
{% endblock %}
<p>
</p>