At the Forge - Database Modeling with Django

Let Django and its object-relational model do the SQL database work for you.

The past few months, this column has been examining Django, a popular
open-source Web development framework written in Python. Django
sometimes is described as a rival to Ruby on Rails or a Python version
of Rails, but just as Python and Ruby are distinct languages, each
with its own strengths and weaknesses, Django and Rails are different
frameworks, and each has its own set of trade-offs.

If you have been following this series of columns about Django,
you already have seen how to download and install the Django software,
how to create and configure a site and application, and even how to
create views (Python methods that handle the business logic) and
templates (HTML files with special rules for interpolating
variables and dynamic content). With everything we've looked at so
far, you could presumably create an interesting dynamic Web
application.

However, most modern Web applications have another component, a
relational database, on which they rely for data storage and
retrieval. Sure, you could store everything on the filesystem or
even in memory, but for most of us, a relational database is the
path of least resistance, ensuring the safety of our data while providing
a great deal of flexibility in retrieving it.

This month's column, then, looks at the ways in which Django programmers
can store and retrieve information in a database. If you have
worked with databases only from PHP or CGI programs, you will be
surprised and impressed by the degree of automation Django
provides. If you have worked with Ruby on Rails, you
probably will think the Django programmers are working too hard—to
which Django hackers would say that they want to have full control
over their application, rather than rely on behind-the-scenes magic.

Creating a Model

The term model in the Django world describes a Python object for
which there is a persistent state, presumably stored in a relational
database. We don't need to use models to integrate a
database into Django, but it would be difficult (not to mention
unaesthetic) if we were simply to stick SQL queries into our
templates. So instead, we use Django's built-in object-relational
mapper, working solely with objects from within our views and
templates. The mapper's job is to translate our method calls into
SQL and then translate the resulting database response into Python
objects.

But, before we even can create our model object, we first must have a
database table to which the object will connect. Django requires that
we define our model using Python code, describing the table's name,
fields and even some default values.

If we were interested in keeping the blog application we
started last month, we probably could define our table in PostgreSQL
as follows:

Our model is a Python class, which inherits from
django.db.models.Model. We define each field with a particular type,
using objects that we imported from django.db.models. As shown
above, some of these data types can be restricted or modified from
their defaults by passing parameters. Some are defined
specifically because they have built-in limits, such as EmailField, which must be a
valid e-mail address. By defining columns with ManyToManyField and
ForeignKey objects, it's possible to define a variety of relationships
among tables.

The above code should be placed in models.py, a Python file that sits
within our application's directory (blog in this case), which itself
sits inside our Django site directory (mysite in this case).
Thus, the models for my blog application reside in
mysite/blog/models.py, whereas the models for a poll application
would reside in mysite/poll/models.py.

Notice that we don't have to define a primary key, which
traditionally is called id and is a nonrepeating integer. (In
PostgreSQL, we set it to have a SERIAL data type, which gives the
column a default value taken from a newly created sequence object. In
MySQL, you would set the column to AUTO_INCREMENT, which has some of
the same capabilities as a sequence.) Django creates the id
column for us automatically. Django handles potential namespace
conflicts by prefacing the table name with the application name. So,
the posting table within the blog application becomes the
blog_posting table.

Now, how do we turn our Python code into SQL? First, we have to be
sure Django knows which database to use. If you have been
following along since my first Django article in the July 2007 issue, you already
have added the appropriate lines to settings.py, a site-wide configuration
file in which we define the database type, name, user and password.
Here are the values that I have installed:

Notice the clear namespace distinction between my application
(mysite.blog) and the applications that are included with Django
(django.contrib.*).

Before we turn our Python code into SQL, we first should check to make
sure it passes some basic sanity and validation checks. To do
that, we go to our site's home directory, and type:

python manage.py validate

If all goes well, Django will report that there aren't any errors.
Now that our model has been validated, we can use it to create SQL.
The easiest way to do this is with the sqlall command to manage.py:

python manage.py sqlall blog

This produces the SQL output for our database driver (PostgreSQL, in
this case). For example, this is the output that I see on my system:

To their credit, the Django developers wrap the CREATE TABLE statement
between BEGIN and COMMIT, ensuring that the table creation will
take place in a transaction and will be rolled back if there is a
problem. This isn't an issue when creating only one table, but if we have several models, it's always best to leave the database
in a consistent state.

One way for us to use the output from sqlall to create tables is to
copy it from the terminal window and then paste it, either into a
file or into the psql client program. But, Django provides the
syncdb utility to do this for us:

Trending Topics

Upcoming Webinar

Getting Started with DevOps - Including New Data on IT Performance from Puppet Labs 2015 State of DevOps Report

August 27, 2015
12:00 PM CDT

DevOps represents a profound change from the way most IT departments have traditionally worked: from siloed teams and high-anxiety releases to everyone collaborating on uneventful and more frequent releases of higher-quality code. It doesn't matter how large or small an organization is, or even whether it's historically slow moving or risk averse — there are ways to adopt DevOps sanely, and get measurable results in just weeks.