What are some good Python ORM solutions? [closed]

Question

If you're looking for lightweight and are already familiar with django-style declarative models, check out peewee: https://github.com/coleifer/peewee

Example:

import datetime
from peewee import *

class Blog(Model):
    name = CharField()

class Entry(Model):
    blog = ForeignKeyField(Blog)
    title = CharField()
    body = TextField()
    pub_date = DateTimeField(default=datetime.datetime.now)

# query it like django
Entry.filter(blog__name='Some great blog')

# or programmatically for finer-grained control
Entry.select().join(Blog).where(Blog.name == 'Some awesome blog')

Check the docs for more examples.

SQLAlchemy is more full-featured and powerful (uses the DataMapper pattern). Django ORM has a cleaner syntax and is easier to write for (ActiveRecord pattern). I don't know about performance differences.

SQLAlchemy also has a declarative layer that hides some complexity and gives it a ActiveRecord-style syntax more similar to the Django ORM.

I wouldn't worry about Django being "too heavy." It's decoupled enough that you can use the ORM if you want without having to import the rest.

That said, if I were already using CherryPy for the web layer and just needed an ORM, I'd probably opt for SQLAlchemy.

Storm has arguably the simplest API:

from storm.locals import *

class Foo:
    __storm_table__ = 'foos'
    id = Int(primary=True)


class Thing:
    __storm_table__ = 'things'
    id = Int(primary=True)
    name = Unicode()
    description = Unicode()
    foo_id = Int()
    foo = Reference(foo_id, Foo.id)

db = create_database('sqlite:')
store = Store(db)

foo = Foo()
store.add(foo)
thing = Thing()
thing.foo = foo
store.add(thing)
store.commit()

And it makes it painless to drop down into raw SQL when you need to:

store.execute('UPDATE bars SET bar_name=? WHERE bar_id like ?', []) 
store.commit()

I usually use SQLAlchemy. It's pretty powerful and is probably the most mature python ORM.

If you're planning on using CherryPy, you might also look into dejavu as it's by Robert Brewer (the guy that is the current CherryPy project leader). I personally haven't used it, but I do know some people that love it.

SQLObject is a little bit easier to use ORM than SQLAlchemy, but it's not quite as powerful.

Personally, I wouldn't use the Django ORM unless I was planning on writing the entire project in Django, but that's just me.

SQLAlchemy's declarative extension, which is becoming standard in 0.5, provides an all in one interface very much like that of Django or Storm. It also integrates seamlessly with classes/tables configured using the datamapper style:

Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foos'
    id = Column(Integer, primary_key=True)

class Thing(Base):
    __tablename__ = 'things'

    id = Column(Integer, primary_key=True)
    name = Column(Unicode)
    description = Column(Unicode)
    foo_id = Column(Integer, ForeignKey('foos.id'))
    foo = relation(Foo)

engine = create_engine('sqlite://')

Base.metadata.create_all(engine)  # issues DDL to create tables

session = sessionmaker(bind=engine)()

foo = Foo()
session.add(foo)
thing = Thing(name='thing1', description='some thing')
thing.foo = foo  # also adds Thing to session
session.commit()

We use Elixir alongside SQLAlchemy and have liked it so far. Elixir puts a layer on top of SQLAlchemy that makes it look more like the "ActiveRecord pattern" counter parts.

This seems to be the canonical reference point for high-level database interaction in Python: http://wiki.python.org/moin/HigherLevelDatabaseProgramming

From there, it looks like Dejavu implements Martin Fowler's DataMapper pattern fairly abstractly in Python.