Basic SQLAlchemy ORM Example

Friday, September 21st, 2007

If you are not into DB-API, SQLAlchemy may be for you.

SQLAlchemy is a database "toolkit" for python. In many ways, it is like Hibernate from the Java world. Both kits are focused on providing high-performance object relational mapping, but they also provide some nice database abstraction functions as well. The SQLAlchemy document is bit dense, but then this is serious stuff. Still, after I played with it for awhile, I would recommend it over SQLObject.

The SQLAlchemy Philosophy:

SQL databases behave less and less like object collections the more size and performance start to matter; object collections behave less and less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.

SQLAlchemy doesn't view databases as just collections of tables; it sees them as relational algebra engines. Its object relational mapper enables classes to be mapped against the database in more than one way. SQL constructs don't just select from just tables—you can also select from joins, subqueries, and unions. Thus database relationships and domain object models can be cleanly decoupled from the beginning, allowing both sides to develop to their full potential.

Like I said, it's a bit dense, but check out Robin Munn's article on SQLAlchemy. What appears below is a much summarized version of Munn's sample code:

from sqlalchemy import *
from sqlalchemy.orm import *

#accessing a database
db = create_engine('mysql://root@localhost/test')

#metadata object used for binding
metadata = BoundMetaData(db)

# creating a table
users_table = Table('users', metadata,
	Column('user_id', Integer, primary_key=True),
	Column('user_name', String(40))
)

#metadata.engine.echo = True
try:
	users_table.create()
except exceptions.SQLError:
	print 'TABLE \'users\' already exists.'

# loading definitions automatically
users_table = Table('users', metadata, autoload=True)

# printing a column
print list(users_table.columns)[0].name

#create a holding class
class User(object):
	def __repr__(self):
		return '%s(%r,%r)' % (
			self.__class__.__name__,self.user_name,self.user_id)

	def wager(self):
		return 'betting on it'

# map the holding class to the table definition
mapper(User, users_table)

#create an instance of the class
u1 = User()

#see, it automatically maps class and fields.  Slick.
print u1.user_id
print u1.wager()