Introduction to database models¶

Lino applications fully use Django’s ORM. Every database table is described by a subclass of django.db.models.Model. Every row of a database table is represented in your Python code as an instance of that class.

The database models of an application are grouped into plugins. Django calls them “applications”, but we prefer the word “plugin”. A plugin is a Python package with at least one file called models.py. Here is the models.py file we are going to use in this tutorial:

from lino.api import dd
from django.db import models
from django.core.exceptions import ValidationError
from .ui import *  # See "Introduction to data tables"


class Author(dd.Model):
    first_name = models.CharField("First name", max_length=50)
    last_name = models.CharField("Last name", max_length=50)
    country = models.CharField("Country", max_length=50, blank=True)

    def __str__(self):
        return f"{self.last_name}, {self.first_name}"


class Book(dd.Model):
    author = dd.ForeignKey(Author, blank=True, null=True)
    title = models.CharField("Title", max_length=200)
    published = models.IntegerField("Published",
                                    help_text="The year of publication")
    price = models.DecimalField("Price", decimal_places=2, max_digits=10)

    def full_clean(self):
        super().full_clean()
        if self.published > 2000 and self.price < 5:
            price = dd.format_currency(self.price)
            msg = "A book from {} for only {}!".format(self.published, price)
            raise ValidationError(msg)

    def __str__(self):
        if self.author is None:
            return self.title
        return f"{self.title} by {self.author.first_name} {self.author.last_name}"

This file is defined in the tables demo project. It exists on your computer, you can open it in your editor by saying:

$ go book
$ e lino_book/projects/tables/models.py

You can try the code snippets on this page from within a Django shell in that project:

$ go tables
$ pm prep

The pm prep command loads the so-called demo fixtures, which are stored in the fixtures/demo.py file. A demo fixture populates the data with example data so that we can play with it.

from lino.api.shell import *
from lino.utils.instantiator import Instantiator


def objects():
    author = Instantiator('tables.Author',
                          'first_name last_name country').build
    adams = author("Douglas", "Adams", "UK")
    yield adams
    camus = author("Albert", "Camus", "FR")
    yield camus
    huttner = author("Hannes", "Huttner", "DE")
    yield huttner

    book = Instantiator('tables.Book', 'title author published price').build
    yield book("Last chance to see...", adams, 1990, '9.90')
    yield book("The Hitchhiker's Guide to the Galaxy", adams, 1978, '19.90')
    yield book("Das Blaue vom Himmel", huttner, 1975, '14.90')
    yield book("L'etranger", camus, 1957, '6.90')
    # yield book("Book", camus, 2001, '4.90')

Open this file also in your editor:

$ e fixtures/demo.py

And now you are ready to play. Open a Django shell:

$ pm shell

This page contains code snippets (lines starting with >>>), which you can try to run on your computer by typing them into your shell. Feel free to use your imagination and try variants of what you see here.

Accessing the database ¶

We import our two models:

>>> from lino_book.projects.tables.models import Author, Book

Every Model has a class attribute objects which is used for operations that access the database.

For example you can count how many authors are stored in our database.

>>> Author.objects.count()
3

Or you can loop over them:

>>> for a in Author.objects.all():
...     print(a)
Adams, Douglas
Camus, Albert
Huttner, Hannes

You can create a new author by saying:

>>> joe = Author(first_name="Joe", last_name="Doe")

That row is not yet stored in the database, but you can already use it. For example you can access the individual fields:

>>> print(joe.first_name)
Joe
>>> print(joe.last_name)
Doe

Or you can simply print your row to the console:

>>> print(joe)
Doe, Joe

Wait, did I say “simply”?! How did your computer know that an instance of Author is to be printed in the format “Last name, first name”? Answer: because of the __str__() method defined on the Author model.

I assume that you know what the lowercase “f” in front of the quotes “” means? If you don’t know, then read the following two pages:

You can change the value of a field:

>>> joe.last_name = "Woe"

>>> print(joe)
Woe, Joe

In order to store our object to the database, we call its save() method:

>>> joe.full_clean()  # see later
>>> joe.save()

Our database now knows a new author, Joe Woe:

>>> Author.objects.count()
4
>>> for a in Author.objects.all():
...     print(a)
Adams, Douglas
Camus, Albert
Huttner, Hannes
Woe, Joe

The all() method of the objects of a Model returns what Django calls a queryset.

queryset¶: A volatile Python object that describes an SQL SELECT statement.

Let’s store that queryset into a variable qs in order to have a more detailed look at it.

>>> qs = Author.objects.all()

Printing a queryset will print all the objects:

>>> print(qs)
<QuerySet [Author #1 ('Adams, Douglas'), Author #2 ('Camus, Albert'), Author #3 ('Huttner, Hannes'), Author #4 ('Woe, Joe')]>

A queryset has an attribute query, which holds the SQL that is sent to the database server in order to retrieve data:

>>> print(qs.query)
SELECT "tables_author"."id", "tables_author"."first_name", "tables_author"."last_name", "tables_author"."country" FROM "tables_author"

You can add filter conditions to a queryset:

>>> qs = qs.filter(first_name="Joe")

This will add the SQL clause WHERE “tables_author”.”first_name” = Joe:

>>> print(qs.query)
SELECT "tables_author"."id", "tables_author"."first_name", "tables_author"."last_name", "tables_author"."country" FROM "tables_author" WHERE "tables_author"."first_name" = Joe

And the resulting queryset now has only one row:

>>> qs.count()
1
>>> qs
<QuerySet [Author #4 ('Woe, Joe')]>

Let’s also create a book:

>>> book = Book(title="The End Of The World", author=joe, published=2025, price="9.90")
>>> book.full_clean()
>>> book.save()
>>> print(book)
The End Of The World by Joe Woe

Validating data ¶

You should always call the full_clean() method of an object before actually calling its save() method. Django does not do this automatically because they wanted to leave this decision to the developer.

For example, we did not specify that the last_name of an author may be empty. So Django will complain if we try to create an author without last_name:

>>> author = Author(first_name="Joe")
>>> author.full_clean()
Traceback (most recent call last):
...
ValidationError: {'last_name': ['This field cannot be blank.']}

Note that Django complains only when we call full_clean() (not already when instantiating the model).

Note that the country field is declared with blank=True, so this field is optional.

The ValidationError is a special kind of exception, which contains a dictionary that can contain one error message for every field. In the Book model we have three mandatory fields: the title, the price and the year of publication (published). Giving only a title is not enough:

>>> book = Book(title="Foo")
>>> book.full_clean()
Traceback (most recent call last):
...
ValidationError: {'price': ['This field cannot be null.'], 'published': ['This field cannot be null.']}

The Book model also shows how you can define custom validation rules that may depend on complex conditions which involve more than one field.

>>> book = Book(title="Foo", published=2001, price='4.2')
>>> book.full_clean()
Traceback (most recent call last):
...
ValidationError: ['A book from 2001 for only $4.20!']

Exercises ¶

Remove the __str__() methods in your models.py file and explore what happens. But do not remove them for good, just comment them out, because you will want them back afterwards.
Add a field birth_date (of type models.DateField) to the Author model and adapt the demo fixture to add these (Albert Camus was born 7 November 1913, Douglas Adams on 11 March 1952 and Hannes Hüttner on 20. June 1932). Then adapt the __str__() method of the Author to add “(* yyyy)” after the name of the author.

More about database models ¶

Tim Kholod wrote a nice introduction for beginners: How to understand Django models the simple way.

Tim Kholod’s tutorial illustrates a difference between Lino and plain Django. It says that you must run python manage.py makemigrations and then python manage.py migrate when you have a change in your database schema (e.g. you added a field). With Lino, you usually say pm prep in that case. See Introduction to demo fixtures.

Now it’s time to also have a look at the official Django documentation about Models and databases. You don’t need to understand every detail now, but I hope that it is no longer complete latin for you: https://docs.djangoproject.com/en/5.2//topics/db/

There is also the Lino topics page The Model class.

Introduction to database models¶

Accessing the database¶

Validating data¶

Exercises¶

More about database models¶

Accessing the database ¶

Validating data ¶

Exercises ¶

More about database models ¶