You have less worries
Yes, the sky is blue

MRO: Map Rows to Objects with web.py

6 January 2010, by Ben    4 comments

MRO is not an ORM. It’s not even the reverse of an ORM. Very simply, MRO maps rows to objects. It’s a thin layer on top of web.py’s equally thin database wrapper.

Why? Well, for a minimalist framework, I do like web.py’s close-to-the-SQL approach. But as soon as you have more than a couple of database operations, you find you’ve got code that repeats itself repeats itself. In the case of Gifty, I was repeating column names.

But I didn’t want a fancy ORM that gave me a new domain-specific language to worry about, or that supported every left, right, inner and outer join under the sun. I’ve found that for simple web apps, all I want is a bit of object with my row: a class that has attributes for columns, and lets you select and save rows.

I ended up with something that looks quite similar to Django’s database layer, albeit much simplified. (In hindsight, I may well have used Django for this project if I was doing it again.)

So here’s what MRO looks like:

# define a User object and its columns (SQL table name is "users")
class User(Table):
    _table = 'users'
    id = Serial(primary_key=True)
    username = String(secondary_key=True)
    hash = String()
    time = Timestamp(not_null=True, default='now()')

# create the users table with its columns and indexes
User.create()

# insert a new user into the database (defaults used for id and time)
bob = User(username='bob', hash='1234')
bob.save()

# fetch an existing user and update its hash column
bob = User('bob')
bob.hash = '4321'
bob.save()

# fetch an existing user (this time by primary key) and delete it
bob = User(42)
bob.delete()

# fetch an existing user (or None if no user called 'bill')
bill = User.get('bill')
if not bill:
    print 'Old Bill seems not to exist'

# get list of Users whose usernames start with 'ab' (also shows interpolation)
abusers = User.select(where='username LIKE $u', vars={'u': 'ab%'})

So, if you use web.py for a small web app, but you want a touch of class (ahem) with your database operations, go ahead and use MRO. Be aware that it’s an in-house tool (for instance, it only supports PostgreSQL at the moment).

Get MRO’s source code

4 comments (oldest first)

Alan 6 Jan 2010, 15:52 link

This looks pretty nice and Python could certainly use another database option. Post it on github!

Ben 6 Jan 2010, 20:01 link

Thanks, Alan. I’m not actually a github user, but you’re welcome to put it up there if you like.

Update: it’s now on GitHub: https://github.com/benhoyt/mro

Leandro Severino 23 Jan 2013, 07:40 link

Friend, Congratulations for this great job.

I have two questions about your code:

First: How to use it ? sorry for my dummie question :( but, do you have a sample code ? for example with a database connection ?

Second: If I need to implement a column of a another Class type, for example, in a Class Menu() I want put a column of the type Page() class, how to implement this ?

class Page():
    id = Serial()
    description = String()

class Menu()
    id = Serial()
    description = String()
    page = Page()

Thank you so much !

Ben 23 Jan 2013, 09:13 link

Regarding your first question, there’s some minimal documentation in the blog entry above (and a bit more in the code), but don’t hesitate to ask if you have further specific questions.

Your second question: what would the underlying SQL look like? It looks like you’re trying to do a foreign key to another table. As MRO is not really an ORM, that’s not something it supports directly, but what I’ve done in my small projects is just to make Menu.page an Integer() column.

For example, at GiftyWeddings, I have registries, and each registry has multiple gifts associated with it. So part of my table structure looks like this:

class Registry(Table):
    _table = 'registries'
    registry_id = Serial(primary_key=True)
    slug = String(secondary_key=True)
    email = String()
    date = Date(not_null=True)

class Gift(Table):
    _table = 'gifts'
    gift_id = Serial(primary_key=True)
    registry_id = Integer(not_null=True, indexed=True)  # the "foreign key"
    name = String(not_null=True, indexed='lower(name)')
    details = String()

Hope that helps.