SQLObject: Why write SQL when you can write Python?
Objects in a language like Python and relational databases have very different views of the world. So much so, that there's even a commonly accepted term for this: the object-relational impedance mismatch. When you're writing your code in an object-oriented style, you have to shift gears every time you need to access your database. Unless you use an object-relational mapper, that is.
SQLObject is an easy-to-use object-relational mapper that succeeds in both allowing you to use your data via standard Python objects without hiding the fact that there's a database back there. In order to have an application with acceptable performance, you can't completely forget that there's a database there. By neatly mapping tables to classes and columns to attributes, you won't often be surprised by the SQL that's generated. SQLObject's query logging makes it a snap to verify exactly what SQL statements are being executed.
What you're missing by hand-writing SQL
In addition to reducing or eliminating the need to write SQL by hand, SQLObject offers other advantages over hand-written SQL.
If you develop your software using SQLObject, your software is automatically database-independent! You can start off developing on sqlite for convenience and move your way up to PostgreSQL for final deployment. SQLObject has you covered if you're using MySQL, PostgreSQL, sqlite or Firebird. (Some work has also been done to support Sybase and Oracle, but those are incomplete.)
Another advantage SQLObject gives you is caching. If you have an application that has neatly modular code, you may end up running the same query multiple times in one operation, or you have to think your way through making sure that you don't. SQLObject automatically caches objects retrieved from the database, so you can be comfortable knowing that pulling an object from the database multiple times in a single web request will result in only one query touching your database server.
Enough chit-chat, let's see some code!
What does working with SQLObject look like? Here's a class definition:
And here's what the SQL CREATE statement looks like for MySQL:
CREATE TABLE person (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
middle_initial CHAR(1),
last_name VARCHAR(100) NOT NULL,
last_contact TIMESTAMP NOT NULL
);
One thing to notice from the defaults in the class definition and the generated CREATE statement: in Python, you work with familiar Python objects. You work with None in Python as you normally would, and the database will get NULL. You work with datetime objects in Python, and the database gets whatever is appropriate for storing a date/time there.
What is it like to use these objects? It's just like Python!
>>> p = Person(firstName="John", lastName="Doe")
>>> p
<Person 1 firstName='John' middleInitial=None
lastName='Doe' lastContact='datetime.datetime...)'>
>>> p.lastContact
datetime.datetime(2005, 9, 16, 9, 28, 7)
>>> p.firstName
'John'
>>> p.middleInitial = 'Q'
>>> p.middleInitial
'Q'
>>> p2 = Person.get(1)
>>> p is p2
True
SQLObject automatically gives your class useful methods like get (for retrieving a single object by its ID) and select (a Pythonic wrapper around SQL SELECT). It also gives your instances convenient methods like set (which lets you change a bunch of your object's attributes with a single UPDATE statement to the database).
Once you start using SQLObject, you'll wonder why you've been writing SQL by hand all of these years!
