Part VI: SQL and Object Relational Mapping

In this part of the book we're going to cover something that doesn't quite fit into the rest of the book's structure, but is a very necessary topic for junior developers to understand. Knowing how to structure data into a SQL database will teach you how to think logically about your data storage requirements. There's a long established method for deconstructing data, storing it efficiently, and accessing it. In recent years the development of NoSQL databases has made this different, but the basic concepts behind Relational Database Design are still useful. Everywhere that you need to store data there's a need to structure it well and understand it.

Most of these exercises will involve you using a SQL database, so I recommend you download the sqlite3 binary from the SQLite3 download database to work with if you do not already have it. We are using Python so this is already installed with most Python installations, but sometimes it is not available. If you can't run this Python code in your python shell:

>>> import sqlite3

Then your python is not installed with sqlite3 by default. You'll need to find out why it's missing, and most likely there's another package you have to install before you can use it inside Python.

Understanding SQL Is Understanding Tables

Before you begin the exercises for this part you'll need to fully understand a single concept that causes many SQL beginners problems:

EVERY SINGLE THING IN A SQL DATABASE IS A TABLE.

Burn this into your mind. By a "table" I mean exactly like an spreadsheet where there are rows on the left and columns across the top. Typically you'll name the columns by some type of data that goes into that column. Then each row represents a single thing you need to put into the table. This could be an account, a list of people and their information, food recipes, or even cars. Each row is a car, and each column is some attribute about that car you need to track.

This causes most programmers problems because we think in terms of tree like structures. An object and another object inside it that has a list that has a dict that has strings mapped to data. We nest things inside, and that style of data structure doesn't fit into a table. To most programmers it seems like these two structures (tables and trees) can't coexist, but a tree and a table are actually very similar. You can take nearly any tree structure and map it onto nearly any matrix, but you have to understand another aspect of SQL databases: the relation.

A relation is how a SQL database becomes more useful than a spreadsheet. A spreadsheet may let you create a full set of sheets and put different types of data inside them, but they make it difficult to link these sheets together. A SQL database's entire purpose is to make you link tables together using either columns or other tables. The genius of a SQL database is it uses one structure (the table) to then construct nearly any kind of data structure you can imagine by letting you link them together.

We'll learn about relations in a SQL database, but the quick answer is that, if you can make a tree of data then you can put that tree into 1 or more tables. At this stage in the book we can abbreviate the process of converting a set of related Python classes into SQL tables like this:

• Make tables for all the classes.

• Set id rows in the children pointing at the parents.

• Create linking tables "between" any two classes that are linked through a list.

It is much more complicated than that, but that's the gist of what you do when converting a set of classes to SQL. In fact, the majority of what a system like Django does is a more complicated version of the above 3 things.

What You'll Learn

The purpose of this section is not to teach you how to be a SQL system administrator. If you want to do that job then I suggest you learn everything you can about Unix and then go get certified by a company that offers certification in their technology. Keep in mind that this is not a very fun job and is similar to babysitting a large nursery of cats. Cats, not kittens.

What you will learn at the end of Part VI is how SQL works on a basic level. This is a quick crash course in SQL which ends with you creating your own Object Relational Mapper (ORM) similar to Django's. This section is only a jumping off point to understanding how SQL works and is intended to give you enough information to know what's going on in a system like Django.

If you would like to go beyond this section in your work I recommend Joe Celko's SQL For Smarties and time. Joe's book is large but it is very complete and he is a master of SQL. Going through that book will make you very capable.