Why you should give PonyORM a chance
Pony is a neat new ORM on the block. You likely haven't heard of it, it doesn't seem like anybody has.
It is similar to other ORMs such as SQLAlchemy in the sense that you can define your models, and query them using convenient syntax. However the syntax for Pony is a lot more pythonic in my opinion. It lets you write complex SQL queries using the python generator syntax, and then converts and executes this as SQL for you.
# Example from Pony homepage select(c for c in Customer if sum(c.orders.price) > 1000)
Look at that syntax and how visually appealing and easy to write it is. It almost seems like magic, and that's because it pretty much is. The developer explains here in detail how it "performs its tricks". It essentially decompiles the generator into its bytecode using the dis module, and then converts the bytecode into an AST.
The clear benefit of pony is simplicity of writing fairly complex queries, here are a few examples:
# Select product name and amount of orders select((p.name, count(p.orders)) for p in Product)
# Select the oldest age out of every person max(p.age for p in Person)
# Using SQL AVG aggregation query to work out avg GPA avg(s.gpa for s in Student if s.group.dept.number == 44)
Transaction / Session Handling
My next favourite feature about Pony is the transaction/db session handling. It eliminates the need to use a function like commit() at the end of all your functions where you update a record. Pony provides a simple @db_session decorator method that starts automatically starts a database session upon your first SQL query, and then keeps track of any changes you make to your data within the function. If you have edited any of your models at the end of your function, then PonyORM will make sure to sync these changes to the DB for you. You can of course commit changes at anytime using the commit() function if you really need to.
It supports everything you have come to love about your current ORM; default column values, specifying custom column types, relationships, etc. It has support for One-To-One, One-To-Many and Many-To-Many relationships. Here's quick example of how a basic relationship looks:
class Post(db.Entity): title = Required(unicode, 500) content = Required(LongUnicode) category = Required("Category") last_updated = Required(datetime, default=lambda: datetime.now()) class Category(db.Entity): name = Required(unicode) posts = Set("Post")
Unfortunately, Pony doesn't yet have migrations. So if you're used to migrations with something like South then you're out of luck. The good news is that the Pony team have confirmed they are working on this.
It also has a Dual-License, which means in some circumstances you may have to purchase a license. But it's a measly $100, so it isn't going to make a difference to your project in the long run.
Overall, I'd say Pony is definitely worth giving a chance. I use it in all of my new projects and have never found a problem with it. It speeds up my development for sure. Check out the PonyORM website for more details.