### 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.