Peewee, a lightweight Python ORM - Original Post

Edit

I rewrote peewee from the ground up. The query examples in this post are no longer supported.

Edit, Jul 24, 2011

added support for Postgresql and MySQL (in addition to SQLite).

Edit, June 8, 2011

added support for MySQL

For the past month or so I've been working on writing my own ORM in Python. The project grew out of a need for a lightweight persistence layer for use in Flask web apps. As I've grown so familiar with the Django ORM over the past year, many of the ideas in Peewee are analagous to the concepts in Django. My goal from the beginning has been to keep the implementation simple without sacrificing functionality, and to ultimately create something hackable that others might be able to read and contribute to.

Weighing in at about 1000 lines of code, Peewee doesn't come close to matching Django's ORM (15K LOC) in terms of API cleanliness or functionality, but it does hit many of the basic use-cases for an app that needs lightweight persistence and querying. This has definitely been one of the most rewarding projects I've worked on!

Benchmarks

In terms of speed, peewee is generally 25% faster than django when creating rows or grabbing simple lists of objects. Peewee is 77% faster than Django for simple one-row calls to ".get()", and almost 50% faster when doing ".get_or_create()". When doing a single join, peewee is only 23% faster, but when executing a comparable query using a subquery instead of a join, peewee is almost 60% faster than django!

. | django_bench | peewee_bench | djang diff | test_creation | 11.441360 | 9.212356 | 19.481986% | test_get_user_count | 0.048042 | 0.023086 | 51.946383% | test_list_users | 2.612983 | 2.037555 | 22.021881% | test_list_users_ordered | 3.022387 | 2.397535 | 20.674121% | test_get_user | 0.232575 | 0.053378 | 77.049199% | test_get_or_create | 0.789696 | 0.406243 | 48.557027% | test_list_blogs_for_user | 1.423771 | 0.646669 | 54.580533% | test_list_entries_for_user | 1.684540 | 1.292385 | 23.279645% | test_list_entries_subquery | 2.413966 | 0.979424 | 59.426772% |

Benchmark code can be viewed here. The benchmarks were run against Django 1.2.3 final. Both benchmarks used an on-disk SQLite database.

Aspects of the design

Cribbed from Django

Declarative model definitions

Arbitrarily complex querying with "Q" objects

Using a double-underscore to denote a special query lookup

Exposing the reverse side of a ForeignKey relationship as a descriptor

Iterating over a query result causes evaluation

Off in left-field

Joins are denoted explicitly

Where clauses

Pagination as opposed to limiting/offsetting/slicing

Only support for SQLite (at the moment)

Examples of some queries

# a simple query selecting a user User . get ( username = 'charles' ) # get the staff and super users editors = User . select () . where ( Q ( is_staff = True ) | Q ( is_superuser = True )) # get tweets by editors using a subquery Tweet . select () . where ( user__in = editors ) # get tweets by editors using a join Tweet . select () . join ( User ) . where ( Q ( is_staff = True ) | Q ( is_superuser = True )) # how many active users are there? User . select () . where ( active = True ) . count () # paginate the user table and show me page 3 (users 41-60) User . select () . order_by (( 'username' , 'asc' )) . paginate ( 3 , 20 ) # order users by number of tweets User . select ({ User : [ '*' ], Tweet : [ Count ( 'id' , 'num_tweets' )] }) . group_by ( 'id' ) . join ( Tweet ) . order_by (( 'num_tweets' , 'desc' ))

Example App

I originally wrote peewee to provide some lightweight persistence for flask apps. As an example app, I've written a (cheesy) twitter-alike -- the entire app is under 250 lines of code and exemplifies quite a few of peewee's features.

For instructions on running the example app yourself, or for an in-depth walkthrough, check the example app docs.

Documentation

If you're interested in readming more, please check out the documentation. The docs are currently broken up into 3 main sections:

Conclusion

I've had a great time working on this code and plan on continuing to develop on it. Please feel free to contribute if you're interested! I'll close with a quote from the Art of Unix Programming:

"Software design and implementation should be a joyous art, a kind of high-level play."

EDIT:

Based on reader interest, I put together a set of benchmarks for SQLAlchemy's ORM. The only benchmark I had trouble replicating was the test_list_entries_subquery one, but the other ones should be right. Any SQLAlchemy users out there, I'd appreciate if you could sanity check my benchmarking code! SQLAlchemy outperformed Peewee when listing the blogs for a user (selecting on the back side of a ForeignKey), but performance was quite a bit slower in all other tests:

. |django_bench |peewee_bench |sqlalc_bench | djang diff | sqlal diff | test_creation | 10.585136 | 9.183795 | 16.681107 | 13.238762% | 44.944931% | test_get_user_count | 0.040043 | 0.019856 | 0.085400 | 50.413509% | 76.749471% | test_list_users | 2.769415 | 2.124650 | 3.540615 | 23.281629% | 39.992065% | test_list_users_ordered | 3.154166 | 2.533434 | 3.901886 | 19.679759% | 35.071555% | test_get_user | 0.236253 | 0.053331 | 0.221075 | 77.426364% | 75.876565% | test_get_or_create | 0.644203 | 0.434922 | 0.618659 | 32.486832% | 29.699242% | test_list_blogs_for_user | 1.683750 | 0.684124 | 0.661601 | 59.369032% | -3.404306% | test_list_entries_for_user | 1.866349 | 1.330273 | 2.662108 | 28.723246% | 50.029331% | test_list_entries_subquery | 2.621584 | 1.029036 | 0.002033 | 60.747545% | N/A |

Commenting has been closed, but please feel free to contact me