What are some good Python ORM solutions? [closed]

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.?

Jegar

ponyORM looks pretty nice. – Bethezel

Object-Relational mapping (ORM) is already very popular in many programming languages and one of the best alternatives for SQL. I was inspired from method chaining style to create CQL for my TRIADB project. healis.eu/triadb/#latest-release – Rabush

You want to look at PonyORM. Also on an unrelated note, Stackoverflow is getting less and less relevant in the light of OpenAPI and GH Co-pilot taking the world by storm. (Ex: marking this question as not relevant despite the fact that a dozen people had answers) – Aquilegia

Answer 1

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.

Intraatomic

But if you don't like Django's ORM, and want to use SA, for example, you lose a lot of django's features, like admin. Not a deal breaker, but a skinned knee. – Monty

True, but irrelevant to the question, which was simply about choosing a Python ORM; not about automatically-generated admin interfaces or other framework components. – Intraatomic

I would argue that SQLAlchemy is anything but lightweight -- it can be quite fast, though. I'll throw my project in the mix, it's called peewee and it talks to postgres. Just recently added support for django-style querying, too! charlesleifer.com/docs/peewee – Hallucinatory

You might also be interested in pypi.python.org/pypi/quick_orm – Bedtime

Don't use SQLAlchemy if you want to write in a ActiveRecord Pattern. Why? Just read the document docs.sqlalchemy.org/en/rel_0_7/orm/query.html and tell me how to order_by in DESC order. Yes, SQLAlchemy's declarative layer has a terrible documentation. – Sideline

Please also note that Django ORM doesn't support the composite primary keys and SQLAlchemy support it. – Momentous

@Sideline I'm confused by your comment. I don't understand the logic. How does "hard to find instructions on ORDER BY DESC in the docs" imply "bad for active record pattern"? – Unlash

@Unlash Because SQLAlchemy has another non-ActiveRecord Pattern (non-declarative layer) here docs.sqlalchemy.org/en/rel_0_7/core/schema.html which is more robust from my PoV and better than its declarative pattern. But that comment was posted more than 1 years ago to SQLAlchemy version 0.7, I haven't used SQLAlchemy a lot after that so use your own judgment :-) – Sideline

Answer 2

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.

Hallucinatory

Answer 3

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

Andizhan

It should be noted that Storm only supports MySQL and PostgreSQL at the current moment. Oracle support is in the works though. – Lycanthrope

It also supports SQLite as the above example suggests – Swithbart

quick_orm is as simple as Storm and it is built upon SQLAlchemy so it is also very powerful: pypi.python.org/pypi/quick_orm. Disclaimer: I am the author of quick_orm – Bedtime

Thanks for the example. I had a question about Storm and many-to-one relationship. I'm followign the steps, and I put the Reference at the end of my file outside of the class, and then import the other class, and in the other class i import the current class, and that creates a ciruclar import, but I don't know how else to do it? – Allout

Storm is unmaintained. I wouldn't use it for new projects. – Patel

Also, it seems there's no Storm for Python 3 – Hideout

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.

Lycanthrope

SQLObject is great - simple-to-use, database-independent and it can actually make the tables for you! (I'm lazy). – Neckband

@Lucas - So can SQLAlchemy... – Lycanthrope

As far as I can remember, I was just generally complimenting SQLObject. It was a long time ago, though... :) – Neckband

@Lucas - I figured as such. Just thought I'd make a note of it. :-) – Lycanthrope

Answer 5

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

Rabiah

:) at Tyler telling the creator of SQLAlchemy that he should use Quick ORM. – Kastroprauxel

:) Reminds me of someone years ago on usenet arguing with dmr@alice that he didn't really understand C. – Uigur

@AnthonyBriggs, check this slide and you will see why quick_orm is better at handling complex relationships than SQLAlchemy: slideshare.net/tyler4long/quickorm – Bedtime

looks like quick_orm is dead since 10 years... github.com/tylerlong/quick_orm – Sedge

Answer 6

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.

Whacking

SQLAlchemy supports OOP and functional styles out of the box, Elixir adds declarative programming style (mostly for model declarations but can be exdended) on top of it. – Reduplicative

Answer 7

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.

Rightist

I was interested and looked at Dejavu. Only a little. Documentation is very sparse (qoute "for the presentation layer you are on your own") so only for advanced users I would say. – Chessa

Answer 8

I think you might look at:

Autumn

Storm

Semipro

Autumn is probably easier than Storm, but Storm includes many features that Autumn doesn't. Both of these options have limited documentation, although Storm is fixing that fast! – Reign

Thank you, Autumn looks very nice and attractive, but has zero documentation, which is a deal breaker for me. – Vaticination

I just tried some of the examples on the Autumn page, and they don't even work with the version of the code my package manager installed. The posts in the google group are also old. Looks like the project is dying a slow death. Would not recommend using it. – Virility

Storm on the other hand, is quickly becoming my ORM of choice. Docs are getting better, and the API is clean and simple, though i am a bit more used to the ActiveRecord pattern employed by the Django ORM, i finding Storm to be easy to navigate. – Virility

Autum doesn't seem to have any activity for a year. groups.google.com/group/autumn-orm – Cesium

Answer 9

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.

Dagostino

there is a concievable way – Couching

Answer 10

I used Storm + SQLite for a small project, and was pretty happy with it until I added multiprocessing. Trying to use the database from multiple processes resulted in a "Database is locked" exception. I switched to SQLAlchemy, and the same code worked with no problems.

Annice

To be fair, SQLite isn't really designed for concurrent accesses. – Chuppah

@Xion +1. SQLITE is a only one file, with no daemon running. – Salmagundi

Answer 11

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.

Doykos

is it true that SQLAlchemy is not threadsafe? Then how is it used in Pyramid apps over WSGI, which mainly people deploy in threaded mode? Any confirmation to this contradictory statement. – Baobaobab

Of course SQLAlchemy is thread-safe. – Patel

Answer 12

I'd check out SQLAlchemy

It's really easy to use and the models you work 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')>

Eminence

Django does not use sqlalchemy for it's ORM. There has been some work done to make sqlalchemy an optional ORM, but it's not complete. – Mohave