Welcome to Peewee, a lightweight python ORM

This notebook will provide an introduction to the peewee ORM. We will use an in-memory SQLite database, but peewee comes with support for Postgresql and MySQL as well.

In this notebook we will get familiar with writing and executing queries with peewee.

It is my hope that after reading this notebook you will:

  • Understand how peewee models map to database tables.
  • Understand how peewee executes queries.
  • Be interesting in using peewee for your next project!

To get peewee:

pip install peewee
In [1]:
from peewee import *
database = SqliteDatabase(':memory:')  # Create a database instance.

Model Definition

Models are a 1-to-1 mapping to database tables, and each "field" maps to a column on the table. There are lots of field types suitable for storing various types of data. peewee handles converting between “pythonic” values those used by the database, so you don’t have to worry about it.

In [2]:
class Person(Model):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()

    class Meta:
        database = database # this model uses the in-memory database we just created

Relationships

Things get interesting when we set up relationships between models using foreign keys. A foreign key establishes a connection between two tables. This is easy to do with peewee:

In [3]:
class Pet(Model):
    owner = ForeignKeyField(Person, related_name='pets')
    name = CharField()
    animal_type = CharField()

    class Meta:
        database = database # use the in-memory database
In [4]:
# Let's create the tables now that the models are defined.
Person.create_table()
Pet.create_table()

Storing data

In [5]:
# Let’s store some people to the database, and then we’ll give them some pets.
from datetime import date
uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15), is_relative=True)
uncle_bob.save() # bob is now stored in the database
In [6]:
# We can shorten this by calling `Model.create`.
grandma = Person.create(name='Grandma', birthday=date(1935, 3, 1), is_relative=True)
herb = Person.create(name='Herb', birthday=date(1950, 5, 5), is_relative=False)
In [7]:
# We can make updates and re-save to the database.
grandma.name = 'Grandma L.'
grandma.save()
In [8]:
# Now we have stored 3 people in the database. Let’s give them some pets. 
# Grandma doesn’t like animals in the house, so she won’t have any, but Herb has a lot of pets.
bob_kitty = Pet.create(owner=uncle_bob, name='Kitty', animal_type='cat')
herb_fido = Pet.create(owner=herb, name='Fido', animal_type='dog')
herb_mittens = Pet.create(owner=herb, name='Mittens', animal_type='cat')
herb_mittens_jr = Pet.create(owner=herb, name='Mittens Jr', animal_type='cat')
In [9]:
# After a long full life, Mittens gets sick and dies. We need to remove him from the database.
herb_mittens.delete_instance() # he had a great life
Out[9]:
1
In [10]:
# You might notice that it printed “1” – whenever you call Model.delete_instance() it will return the number of 
# rows removed from the database.
In [11]:
# Uncle Bob decides that too many animals have been dying at Herb’s house, so he adopts Fido.
herb_fido.owner = uncle_bob
herb_fido.save()
bob_fido = herb_fido # rename our variable for clarity

Retrieving Data

Now that we've got some people and pets in the database, let's make some queries. As you will see, the real power of our database comes when we want to retrieve data. Relational databases are a great tool for making ad-hoc queries.

Getting a single record

In [12]:
# Let's retrieve Grandma's record from the database.
grandma = Person.select().where(Person.name == 'Grandma L.').get()
print grandma.name
Grandma L.

In [13]:
# We can also use the following shortcut:
grandma = Person.get(Person.name == 'Grandma L.')
print grandma.name
Grandma L.

Getting multiple records

In [14]:
# Let's list all the people in the database.
for person in Person.select():
    print person.name, '- is relative?', person.is_relative
Bob - is relative? True
Grandma L. - is relative? True
Herb - is relative? False

In [15]:
# Now let's list all the people and their pets.
for person in Person.select():
    print person.name, person.pets.count(), 'pets'
    for pet in person.pets:
        print '    ', pet.name, pet.animal_type
Bob 2 pets
     Kitty cat
     Fido dog
Grandma L. 0 pets
Herb 1 pets
     Mittens Jr cat

In [16]:
# List all the cats and their owner's name.
for pet in Pet.select().where(Pet.animal_type == 'cat'):
    print pet.name, 'owned by', pet.owner.name
Kitty owned by Bob
Mittens Jr owned by Herb

In [17]:
# This one will be a little more interesting and introduces the concept of joins.
# Let’s get all the pets owned by Bob:
for pet in Pet.select().join(Person).where(Person.name == 'Bob'):
    print pet.name
Kitty
Fido

In [18]:
# We can do another cool thing here to get bob’s pets. Since we already have an object to represent Bob, we can do this instead:
for pet in Pet.select().where(Pet.owner == uncle_bob):
    print pet.name
Kitty
Fido

In [19]:
# Use `order_by` to sort the list.
for pet in Pet.select().where(Pet.owner == uncle_bob).order_by(Pet.name):
    print pet.name
Fido
Kitty

In [20]:
# Here are all the people, ordered youngest to oldest.
for person in Person.select().order_by(Person.birthday.desc()):
    print person.name
Bob
Herb
Grandma L.

Performing complex queries

We can use python operators to create complex queries. Peewee supports multiple types of operations.

In [24]:
# Let’s get all the people whose birthday was either:
# * before 1940 (grandma)
# * after 1959 (bob)
d1940 = date(1940, 1, 1)
d1960 = date(1960, 1, 1)
for person in Person.select().where((Person.birthday < d1940) | (Person.birthday > d1960)):
    print person.name
Bob
Grandma L.

In [25]:
# Now let’s do the opposite. People whose birthday is between 1940 and 1960.
for person in Person.select().where((Person.birthday > d1940) & (Person.birthday < d1960)):
    print person.name
Herb

In [26]:
# This will use a SQL function to find all people whose names start with either an upper or lower-case “G”:
for person in Person.select().where(fn.Lower(fn.Substr(Person.name, 1, 1)) == 'g'):
    print person.name
Grandma L.

Conclusion

This is just the basics! Hopefully peewee is starting to make a little sense.

For further reading check out:

In [ ]: