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:
To get peewee:
pip install peewee
from peewee import *
database = SqliteDatabase(':memory:') # Create a database instance.
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.
class Person(Model):
name = CharField()
birthday = DateField()
is_relative = BooleanField()
class Meta:
database = database # this model uses the in-memory database we just created
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:
class Pet(Model):
owner = ForeignKeyField(Person, related_name='pets')
name = CharField()
animal_type = CharField()
class Meta:
database = database # use the in-memory database
# Let's create the tables now that the models are defined.
Person.create_table()
Pet.create_table()
# 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
# 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)
# We can make updates and re-save to the database.
grandma.name = 'Grandma L.'
grandma.save()
# 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')
# 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
1
# You might notice that it printed “1” – whenever you call Model.delete_instance() it will return the number of
# rows removed from the database.
# 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
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.
# Let's retrieve Grandma's record from the database.
grandma = Person.select().where(Person.name == 'Grandma L.').get()
print grandma.name
Grandma L.
# We can also use the following shortcut:
grandma = Person.get(Person.name == 'Grandma L.')
print grandma.name
Grandma L.
# 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
# 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
# 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
# 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
# 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
# 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
# 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.
We can use python operators to create complex queries. Peewee supports multiple types of operations.
# 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.
# 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
# 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.
This is just the basics! Hopefully peewee is starting to make a little sense.
For further reading check out: