Welcome | Get started | Dive into Lino | Contribute | Topics | Reference | More

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


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 "%s, %s" % (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(Book, self).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)


from .ui import *

This file is defined in the tables demo project. You can try the code snippets on this page from within a Django shell in that project:

$ go tables
$ pm shell

This page is a tested document and the following instructions are used for initialization:

>>> from lino import startup
>>> startup('lino_book.projects.tables.settings')
>>> from django.conf import settings

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:

>>> obj = 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(obj.first_name)
Joe
>>> print(obj.last_name)
Doe

For example it has a __str__() method:

>>> print(obj)
Doe, Joe

You can change the value of a field:

>>> obj.last_name = "Woe"

>>> print(obj)
Woe, Joe

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

>>> obj.full_clean()  # see later
>>> obj.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.

When you have a queryset object, you can see the SQL that it would generate in order to retrieve data from the database server:

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

>>> qs = Author.objects.filter(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

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

Before going on we tidy up by removing Joe Woe from our demo database:

>>> obj.delete()
>>> Author.objects.count()
3

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!']

More about database models

Tim Kholod wrote a nice introduction for beginners: The simple way to understand Django models

If you want to know more about Django's way to access the database using models, read the Django documentation about Models and databases.

See the reference page The Model class.