New Blogging System with Python/MySQL

I had some free time this weekend, so I thought I would put it to good use and learn how to use SQL. Most people would probably ask "what took you so long?". Unfortunately, Caltech didn't offer a database class while I was there, and I never had a chance to work on an application where a database would be useful. Compilers, kernel drivers, and OpenGL demos don't really handle that much data. The closest thing to a database-driven web app I've worked on was a Starcraft clan website I built in middle school with Perl and Javascript.

So where to begin? I knew basically nothing about relational databases before this weekend. I've learned a bit about them through osmosis at Stack Overflow over the last few months. You basically store data in tables where rows represent objects or records and columns contain attributes for those records. Using SQL, you can manipulate the tables by selecting, filtering, sorting, and combining records. I'm vaguely aware of Object Relational Mapping, which maps between database records and objects in a program, but I don't want to get ahead of myself too quickly.

Knowing so little, it's hard to find a tutorial that combines basic relational database theory, SQL syntax, and MySQL specifics all in one. I eventually found Databases from Scratch by Rose Vines. There's also a very concise reference / tutorial at w3schools.

My first project was to write a simple counter for this site. That's right! You're being tracked! I created a table which stores the IP, page name, referrer, browser, and time that every page is accessed. I wrote a little Python script to gather information and insert it into the table. The script is executed via SSI by every page on the site.

My next task was to move the blog onto the database. This was a little more involved. After creating a new table for posts, I first wrote another Python module that would execute a single, parameterized SQL query given to it. This freed me from having to mess around with a connection and cursor in every script. The MySQLdb Python module is helpful in that it automatically sanitizes the parameters you give it. Second, I wrote a Post class which handles mapping database records to objects and back. Third, I wrote some code to generate pages given a title, date, and body. It works by parsing my normal SHTML template; it even includes the header/footer and invokes the counter script! After that, all that was left was to write a few scripts to list, view, and edit posts.

Everything appears to be completely working now, and I have a system for submitting posts without logging into the server through SSH. All this was done with MySQL and a remarkably small amount of Python, and it only took a few hours over the weekend. I was surprised by how easy it was. I'm aware of frameworks like Django which make it even easier for large apps.

Anyway, if you don't already know some kind of SQL, give it a try! At the very least, it will get you thinking in a new way.