building web pages with python models

Models

Introducing the Database

Lets start off with the Models first. For those of you unfamiliar to the currently trendy MVC terms, a Model is where your data is stored. In this case, it's the database.

For the Notebook application, I'm going to use three tables: Category, Note, and Revision. Each table is self-explanatory on what it'll contain. Each table will have a one-to-many relationship with the table to the left of it:

Category -< Note -< Revision

Meaning, each Category will contain many Notes and each Note will contain many Revisions.

The SQL Schema will look like this:

CREATE TABLE category (
    category_id integer primary key, 
    category_category text not null
);

CREATE TABLE note (
    note_id integer primary key,
    category_category_id integer not null,
    note_title text not null
);

CREATE TABLE revision (
    revision_id integer primary key,
    note_note_id integer not null,
    revision_body text not null,
    revision_time NOT NULL DEFAULT CURRENT_TIMESTAMP
);

I've made sure each column name is unique to the whole database. I wish I wouldn't have to do this, though. It would have been really nice to have been able to name both note_id and note_note_id just plain note_id. Seeing as both of these values will always be the same in a query, it'd be helpful if SQLAlchemy would have noticed that and combined the two keys. Instead, they clash with an error. Maybe there's a way to do this -- if anyone knows, please fill me in.

Creating the Database

First create a directory for the project:

mkdir notebook.demo

And then a directory for all our SQL stuff:

cd notebook.demo
mkdir sql
cd sql

Inside the sql directory, create a textfile calledtables.sql and add in the schema. Next, create the SQLite database:

sqlite3 notebook.db < tables.sql

You can verify the database was created by going into the SQLite shell:

sqlite3 notebook.db
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE category (
    category_id integer primary key, 
    category_category text not null
);
CREATE TABLE note (
    note_id integer primary key,
    category_category_id integer not null,
    note_title text not null
);
CREATE TABLE revision (
    revision_id integer primary key,
    note_note_id integer not null,
    revision_body text not null,
    revision_time NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMIT;
sqlite>

Next, prime the database with some Categories:

sqlite> insert into category (category_category) values ('Diary');
sqlite> insert into category (category_category) values ('Notes');
sqlite> insert into category (category_category) values ('Articles');

sqlite> select * from category;
1|Diary
2|Notes
3|Articles
sqlite>

Introducing SQLAlchemy

Now we'll create some Python code to interface with the database. Creating the database could have also been done through Python, but I wanted to give a quick introduction to the SQLite Shell.

Since working with the database is also known as working with the model, we'll call the Python file models.py. models.py will be created in the root of the notebook.demo directory. Initially, models.py will look like this:

from sqlalchemy import *

db = create_engine('sqlite:///sql/notebook.db')

metadata = BoundMetaData(db)

category = Table('category', metadata, 
    Column('category_id', Integer, primary_key=True),
    Column('category_category', String(255)),
)

note = Table('note', metadata, 
    Column('note_id', Integer, primary_key=True),
    Column('note_title', String(255)),
    Column('category_category_id', Integer, ForeignKey('category.category_id')),
)

revision = Table('revision', metadata, 
    Column('revision_id', Integer, primary_key=True),
    Column('note_note_id', Integer, ForeignKey('note.note_id')),
    Column('revision_body', String),
    Column('revision_time', DateTime, default=func.current_timestamp()),
)

session = create_session()

That's a lot of initial information, but it's all really self-explanatory. At the top, I'm importing all of the SQLAlchemy modules. Next, a connection to the database is made and is bound to something called metadata.

The metadata comes next -- it looks almost exactly like the contents of the tables.sql file, except it's a bit more strict. This information tells SQLAlchemy all about the table and column names and what types of data it can expect to work with. Finally, a session is created to store some session data. Sessions become more visible when Transactions are used.

Notice the column for revision_time. I'm telling SQLite to insert the return value of the SQL function current_timestamp() as the value of revision_time.

This is enough to get us started with using SQLAlchemy to talk to the database. Start up a Python session and follow along:

$ python
>>> from models import *
>>> select([category]).execute().fetchall()
[(1, u'Diary'), (2, u'Notes'), (3, u'Articles')]
>>> a = select([category]).execute().fetchall()
>>> for column in a:
...     print column['category_id'], column.category_category
... 
1 Diary
2 Notes
3 Articles

And there you have it -- we now have Python connectivity with our database. This is just the basics of the models.py file, there's lot more to be added later on.

SQLAlchemy has a lot of really great documentation. Please take a moment to read up until the Data Mapping chapter as everything discussed before that is going to be used here.