What are some good Python ORM solutions?

Question

I'm evaluating and looking at using CherryPy for a project that's basically a javascript front-end from the client-side (browser) that talks to a Python web service on the back-end. So, I really need something fast and lightweight on the back-end that I can implement using Python that then speaks to the PostgreSQL db via an ORM (JSON to the browser).

I'm also looking at Django, which I like, since its ORM is built-in. However, I think Django might be a little more than I really need (i.e. more features than I really need == slower?).

Anyone have any experience with different Python ORM solutions that can compare and contrast their features and functionality, speed, efficiency, etc.?

Answer 1

I think you might look at:

Autumn

Storm

Answer 2

I'd check out SQLAlchemy

It's really easy to use and the models you wrok with aren't bad at all.

Django uses sqlalchemy for it's ORM but using it by itself lets you use it's full power

here's a small example on creating and selecting orm objects

>>> ed_user = User('ed', 'Ed Jones', 'edspassword')
>>> session.add(ed_user)
>>> our_user = session.query(User).filter_by(name='ed').first() 
>>> our_user
    <User('ed','Ed Jones', 'edspassword')>

Answer 3

@Yon

Django uses sqlalchemy for it's ORM but using it by itself lets you use its full power

Django does not use SQLAlchemy, Django has its own ORM.

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.

I wouldn't worry about Django being "too heavy," though. It's decoupled enough that you can use the pieces you need without having to import the rest.

Answer 4

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.

Answer 5

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.

Answer 6

There is no conceivable way that the unused features in Django will give a performance penalty. Might just come in handy if you ever decide to upscale the project.

Answer 7

Storm has arguably the simplest API:

  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()

Answer 8

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()

Answer 9

SQLAlchemy is very, very powerful. However it is not thread safe make sure you keep that in mind when working with cherrypy in thread-pool mode.