Alex's Notes

cm3035: ORM Lecture Summaries

From CM3035 Topic 02: Database Schemas and ORMs

2.3 ORMs and Django Models

2.302 ORMs Pros and Cons

How should a web app communicate with a db?

Perhaps the simplest way is to open a connection and then issue SQL commands directly in code, then parse any returned data into some structure.

This happens so often that most web frameworks automate the process as much as possible. Python is object oriented, so Django uses an OO approach.

But the OO approach to modelling (class = data + methods) is quite different from the relational one, which doesn’t encapsulate actions.

OO deals with non-scalar entities typically, a class instance would have a heterogenous set of type fields, but the object instance is treated as the data entity as a whole.

EG a user profile in a social media app, the instance for a user might include atomic fields like name and dob, but also a list of friends, where each item in the list is itself an object instance with the friend’s name, relationship to the user etc.

The user is still the single entity, composed of compound and atomic properties. But in the db system we deal only with scalar entities.

So we need to map somehow between the data held in an object to an appropriate set of scalar values that can be stored in the db. Retrieval is the inverse mapping.

in pseudo code we would be doing something like this:

query = `SELECT name, age FROM users WHERE uid=15`
result = connection.query(query)
dataArray = parseToArray(result)
user = new User(dataArray)
name = user[`name`]

This is so common that web frameworks provide classes that wrap all the operations for you.

In Django the class is Model. The code above becomes:

result = User.get_by_id(user_id)
name = result.name

The process of translating between database entities and objects in an OO system is called Object Relational Mapping or ORM.

ORM does have shortcomings:

  • Objects are hierarchical, tables are not

  • Objects are flexible to model graphs

  • Type mismatches between programming languages and RDBMS (eg strings vs VARCHAR)

  • Differing degrees of atomicity (updating a row returns the whole row, but in OO world we often want to just update a single prop).

Typically these aren’t fatal though. If you’re really having issues you might consider non-relational data stores (document stores or graph dbs).

2.304 Writing Django Models

Walks through creating the models. We map each table to a class. Each will inherit from models.Model.

By convention class names are singular (so class Gene(models.Model))

Any class variables that are instances of classes that represent a field constructor will be mapped to attributes in the database. eg (active = models.CharField(max_length=1))

Those constructors can describe aspects of the attribute, like whether it should be indexed, default values, whether it can be null or blank.

By default Django adds an id column for the primary key which is then used for mapping foreign keys.

We can declare foreign keys like this:

ec = models.ForeignKey(EC, on_delete=models.DO_NOTHING)

By convention we give the foreign key attribute the same name as the table referenced. The on_delete is mandatory.

You can create methods on the class to define special actions on the database. Often we override the default __str__ method to provide a better string representation of a row.

2.306 Migrations

Django models are a description of an underlying data resource. They consist of a Python class with class variables representing the data that we can use in our app by instantiating instances of these objects.

Another way of thinking about them is as a representation of our database schema, one we can use to build the database itself.

By abstracting from the underlying DB, modern web frameworks mean we often don’t need to write SQL directly. The logic can be expressed in Python, and in a way that is agnostic to the db implementation. We can develop on SQLite, and deploy on Postgres for example.

A central concept in this abstraction is migration, short for schema migration. A schema migration is an incremental, reversible set of instructions that takes our underlying data store schema from one state to another.

Our process is to take the classes in our models file as instructions to create migrations. Then we apply those migrations to the database to create or alter the tables.

The database itself is set up in the settings.py file which will be in the nested project folder (<project>/<project>/settings.py). Lecture shows postgres setup.

Migrations can be shown with python manage.py showmigrations and made with python manage.py makemigrations.

In complex apps you might have to manually look at the migration files if they end up conflicting, but it’s rare.

Applying the migrations to the db itself is the manage.py migrate command.

2.308 Admin interface

Django provides an admin interface to seed some data.

First create a superuser: python manage.py createsuperuser.

Then run the server with python manage.py runserver login to see the admin interface.

You need to register your classes with the admin portal for them to show up here. Do that in the admin.py file within the app where you defined the model.

It looks like this:

from django.contrib import admin
from .models import *


class ProductAdmin(admin.ModelAdmin):
    list_display = ('type', 'product')


admin.site.register(Product, ProductAdmin)

The admin site is powerful, you can create dashboards etc if you want to.

2.310 Seeding Data

Walks through creating a script to seed data. No particularly helpful tips.

2.4 Queries Using the ORM

Interactions with the database will typically be written in a controller (in the MVC lingo) which in Django are called views.

To use a model in a view, we first import the model class. Then we can use class methods to run SQL queries under the hood. For example: MyClass.objects.all() is like SELECT * FROM MyClass.

It returns a query set an iterable that is lazily evaluated, the operations aren’t run until we use the object (ie we iterate over it).

This laziness means that each call to the iterator is a new db operation.

to filter you can use MyClass.objects.get(field=value) or chain filters as we’ll see.

2.404 Joins and Filters

1:1 joins on foreign keys work by just accessing the foreign key prop, Django will then go and get the relevant data. You can do it in the templating language like this:

{% with seq=gene.sequencing %}
<p> {{ seq.value }} </p>
{% endwith %}

Shows filters and filter chaining: MyClass.objects.filter(prop=value).filter(other_prop__gt=5)

2.406 Deleting and Updating

Shows updating a field, modifying the property on the class instance. Note you have to save the record to commit the change.

Shows deleting a record get the query set and then just call its delete method.

Also shows redirecting after a delete, via the HttpResponseRedirect("path") method from the django.http package.

Links to this note