Updates to peewee, including atomic updates, select related and basic transactions

Over the last two months I've spent a lot of time working on improvements to peewee, a lightweight ORM written in python.

Some of these features are present in Django and were added for better parity, some I found a need for while working on other projects, and others were requested by opening an issue on GitHub or bringing it up on IRC (#peewee on freenode). If you're interested in trying peewee out, it ships with an example app which is described here.

Here's a rundown on what has been added recently:

• atomic updates
• select related models
• basic transaction support

Atomic updates (django-style F() objects)

Peewee now supports querying and selecting values relative to the given row being queried. Before, you could only compare against constant values, for queries like:

• "Give me all employees whose salary is less than X"
• "Give me all users who have not commented since 2010"

Now, it is possible to query against other columns:

• "Give me all employees whose salary is less than their desired salary"
• "Give me all users who have not commented since they last logged-in"

This also makes it possible to do atomic updates:

• "Update the user's activity count by incrementing the current value by 1"

Those familiar with django will recognize the F() object. It works similarly in peewee. Here are how the above queries would look:

# employees whose salary is less than their "desired" salary
Employee.select().where(salary__lt=F('desired_salary'))

# users who haven't commented since their last login
User.select().where(last_comment__lt=F('last_login'))

# atomic update
User.update(activity_count=F('activity_count') + 1).where(id=some_id)

Check the documentation for more information.

Select related models

It is now possible to grab populated model instances related by foreign key using a single query. Suppose you're writing a blogging site and want to show the latest 10 entries along with the blog they appeared on -- this could be written using a simple for loop:

for entry in Entry.select().order_by(('pub_date', 'desc')).limit(10):
    print '%s, posted on %s' % (entry.title, entry.blog.title)

Looking at the query log, though, this will cause 11 queries:

• 1 query for the entries
• 1 query for every related blog (10 total)

This can be optimized into one query very easily, though:

entries = Entry.select({
    Entry: ['*'],
    Blog: ['*'],
}).order_by(('pub_date', 'desc')).join(Blog)

for entry in entries.limit(10):
    print '%s, posted on %s' % (entry.title, entry.blog.title)

Querying like this will cause only one query that looks something like this:

SELECT t1.pk, t1.title, t1.content, t1.pub_date, t1.blog_id, t2.id, t2.title
FROM entry AS t1
INNER JOIN blog AS t2
    ON t1.blog_id = t2.id
ORDER BY t1.pub_date desc
LIMIT 10

peewee will handle constructing the objects and you can access them as you would normally. Check the documentation for more info.

Basic transaction support

Peewee, by default, is configured to automatically commit every transaction. It does this for simplicity, but sometimes that can cause problems. What if you run an online bank and need to transfer $100 from one account to another:

1. $100 is taken from Joe's account
2. $100 is added to Bob's account

If step 2 errors for any reason, step 1 will have already been committed to the database and $100 has just vanished. By running both queries inside a transaction, which is managed by the database, if a single query fails you are not left in an indeterminate state.

There are two ways to use the new transaction support in peewee:

• Decorate a function with commit_on_success
• Manually turn on and off autocommit and explicitly commit / rollback as needed

@database.commit_on_success

This function decorator wraps a given function in a transaction. If no errors are raised, the queries are committed. In the event an error occurs inside the function, the transaction will be rolled back and the error will be bubbled up so you can handle it in your code.

Here's how it looks:

money_db = SqliteDatabase('all_the_money.db')

@money_db.commit_on_success
def transfer_money(from_acct, to_acct, amt):
    from_acct.charge(amt)
    to_acct.pay(amt)
    return amt

Autocommit

For more granular control, you can manually configure whether to use autocommit. This setting can be configured when you initialize your database, or can be set on a per-connection basis:

my_db = SqliteDatabase('control_freak.db', autocommit=False)

You can also modify this at run-time:

def do_some_things():
    my_db.set_autocommit(False)

    try:
        perform_some_queries_in_transaction()
    except:
        my_db.rollback()
    else:
        my_db.commit()
    finally:
        my_db.set_autocommit(True)

Reading more

If you're interested in reading more, the following links may be of interest:

• documentation
• Github repo
• Google group
• Example site

Thanks for reading!

Comments (1)

• Alex Grsy | January 2012, at 03:35

  i REALLY like your PEEWEE port.. and the admin…. I really want to use it on a project I am working on… but I am too wary of Flask's portability to be able to brave basing a product on it… i'm instead using an embedded php hack job…. just one file.. but i know its all there.. the dependency complexity of python is daunting… i wish there was a simple orm like this for php though!

  ---

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