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.

# models.py
import peewee

database = peewee.SqliteDatabase("wee.db")

########################################################################
class Artist(peewee.Model):
    """
    ORM model of the Artist table
    """
    name = peewee.CharField()
    
    class Meta:
        database = database
        
########################################################################
class Album(peewee.Model):
    """
    ORM model of album table
    """
    artist = peewee.ForeignKeyField(Artist)
    title = peewee.CharField()
    release_date = peewee.DateTimeField()
    publisher = peewee.CharField()
    media_type = peewee.CharField()
    
    class Meta:
        database = database
        
        
if __name__ == "__main__":
    try:
        Artist.create_table()
    except peewee.OperationalError:
        print "Artist table already exists!"
    
    try:
        Album.create_table()
    except peewee.OperationalError:
        print "Album table already exists!"

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:

# add_data.py

import datetime
import peewee

from models import Album, Artist

new_artist = Artist.create(name="Newsboys")
album_one = Album(artist=new_artist,
                  title="Read All About It",
                  release_date=datetime.date(1988,12,01),
                  publisher="Refuge",
                  media_type="CD")
album_one.save()

albums = [{"artist": new_artist,
           "title": "Hell is for Wimps",
           "release_date": datetime.date(1990,07,31),
           "publisher": "Sparrow",
           "media_type": "CD"
           },
          {"artist": new_artist,
           "title": "Love Liberty Disco", 
           "release_date": datetime.date(1999,11,16),
           "publisher": "Sparrow",
           "media_type": "CD"
          },
          {"artist": new_artist,
           "title": "Thrive",
           "release_date": datetime.date(2002,03,26),
           "publisher": "Sparrow",
           "media_type": "CD"}
          ]

for album in albums:
    a = Album(**album)
    a.save()

bands = ["MXPX", "Kutless", "Thousand Foot Krutch"]
for band in bands:
    artist = Artist.create(name=band)
    artist.save()

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:

albums = [{"artist": new_artist,
           "title": "Hell is for Wimps",
           "release_date": datetime.date(1990,07,31),
           "publisher": "Sparrow",
           "media_type": "CD"
           },
          {"artist": new_artist,
           "title": "Love Liberty Disco", 
           "release_date": datetime.date(1999,11,16),
           "publisher": "Sparrow",
           "media_type": "CD"
          },
          {"artist": new_artist,
           "title": "Thrive",
           "release_date": datetime.date(2002,03,26),
           "publisher": "Sparrow",
           "media_type": "CD"}
          ]
Album.insert_many(albums).execute()

Now we’re ready to learn how to modify records in the database!

---

Using Basic Queries to Modify Records with peewee

Modifying your records is a pretty common occurance in the database world. The peewee project makes modifying your data pretty easy. Here’s some code to demonstrate how:

# edit_data.py

import peewee

from models import Album, Artist

band = Artist.select().where(Artist.name=="Kutless").get()
print band.name

# shortcut method
band = Artist.get(Artist.name=="Kutless")
print band.name

# change band name
band.name = "Beach Boys"
band.save()

album = Album.select().join(Artist).where(
    (Album.title=="Thrive") & (Artist.name == "Newsboys")
    ).get()
album.title = "Step Up to the Microphone"
album.save()

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:

query = Album.select().join(Artist)
qry_filter = (Album.title=="Step Up to the Microphone") & (Artist.name == "Newsboys")
album = query.where(qry_filter).get()

This is easier to follow and debug. You can use a similar trick with SQLAlchemy’s queries too.

---

How to Delete Records in peewee

Removing a record from a table in peewee takes very little code. Check it out:

# del_data.py

from models import Artist

band = Artist.get(Artist.name=="MXPX")
band.delete_instance()

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!

---

Additional Resources

• Github for the peewee project
• peewee documentation