An Intro to peewee – Another Python ORM

I thought it would be fun to try out a few different Python object relational mappers (ORMs) besides SQLAlchemy. I recently stumbled across a project known as peewee. For this article, we will take the examples from my SQLAlchemy tutorial and port it to peewee to see how it stands up. The peewee project supports sqlite, postgres and MySQL out of the box, which isn’t as flexible as SQLAlchemy, but it’s not bad either. You can also use peewee with the Flask web framework, via a handy Flask-peewee plugin.

Anyway, let’s start playing around with this fun little library!

Getting Started

First of all, you will need to go get peewee. Fortunately it’s really easy if you have pip installed:

pip install peewee

Once that’s installed, we’re good to go!

Creating the Database

Creating a database with peewee is extremely easy. In fact, it’s easier to create the database in peewee than it is in SQLAlchemy. All you need to do is call peewee’s SqliteDatabase method and pass it the path of the file or “:memory:” if you want an in-memory database. Let’s create a database to hold information about our music collection. We’ll create two tables: Artist and Album.

This code is pretty straight-forward. All we’re doing here is creating two classes that define our tables. We set the fields (or columns) and we connect the database to the models via the nested class, Meta. Then we call the class directly to create the tables. This is a little strange as you normally do not call a class directly like this, but instead create an instance of the class. However, this is the recommended procedure according to peewee’s documentation and it works quite well. Now we’re ready to learn how to add some data to our database.

How to Insert / Add Data to Your Tables

Inserting data into our database proves to be pretty easy as well. Let’s take a look:

Here we call the class’s create method to add a band or record. The class supports an insert_many method too, but whenever I tried to save the data via the save() method, I received an OperationalError message. If you happen to figure out how to do this, drop me a line in the comments and I’ll update this article. As a workaround, I just looped over a list of dictionaries and added the records that way.

Update: The author of peewee got back to me on reddit and gave me this solution for adding many records at once:

Basically we just have to query the tables to get the artist or album that we want to modify. The first two queries do the same thing, but one is shorter than the other. This is because peewee provides a shortcut method for doing queries. To actually change the record, we just set the returned object’s properties to something else. In this case, we changed the band’s name from “Kutless” to “Beach Boys”.

The last query demonstrates how to create a SQL join that allows us get a match across two tables. This is great if you happen to own two CDs with the same title but you only want the query to return the album associated with the band called “Newsboys”.

These queries a little hard to follow, so you can break them up into smaller pieces. Here’s one example:

How to Delete Records in peewee

All we had to do was query for the record we wanted to delete. Once we had the instance, we just called its delete_instance method and that removed the record. It’s really that simple!

Wrapping Up

The peewee project is pretty cool. It’s biggest downside is the limited number of database backends that it supports. However, the project is simpler to use than SQLAlchemy, which I think is amazing. The peewee project’s documentation is quite good and well worth a read to understand all the other functionality that’s not covered in this tutorial. Give it a try and see what you think!

SQLAlchemy is certainly the most flexible. I thought PeeWee was neat though.

shayneo

Feels a lot like the Django ORM. Which is great, because Django’s ORM is fantastic. Only problem is, its welded deep into Django itself, making it an innapropriate choice for non django apps. This seems a pretty good alternative

Joakim_N

Thanks for this article! I guess it is a question of what fits your level of knowledge. I was banging my head against the wall trying to learn SQLAlchemy, then I discovered Peewee and that made sense from the start. I am not taking anything away from SQLAlchemy, it must be great given how strong a following it has in the Python community. But at least for me it was overwhelming as a first introduction to ORM (with just some experience of working with SQLite databases). Peewee is a match for my need, especially given the Flask extension. Some day I look forward to graduating to SQLAlchemy and other databases. Keep up the great work!