from peewee import *
database = SqliteDatabase(':memory:')  # Create a database instance.

class Person(Model):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()

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

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

# 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

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

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

# Let's list all the people in the database.
for person in Person.select():
    print person.name, '- is relative?', person.is_relative

# 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

# 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

# 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

# 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

# Use `order_by` to sort the list.
for pet in Pet.select().where(Pet.owner == uncle_bob).order_by(Pet.name):
    print pet.name

# Here are all the people, ordered youngest to oldest.
for person in Person.select().order_by(Person.birthday.desc()):
    print person.name

# 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

# 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

# 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