SQL Alchemy Files

assoc-object-demo.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Boolean, Text
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Table
from sqlalchemy.schema import UniqueConstraint
 
import logging
log = logging.getLogger(__name__)
 
################################################################################
# set up logging - see: https://docs.python.org/2/howto/logging.html
 
# when we get to using Flask, this will all be done for us
import logging
log = logging.getLogger(__name__)
log.setLevel(logging.DEBUG)
console_handler = logging.StreamHandler()
console_handler.setLevel(logging.DEBUG)
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
console_handler.setFormatter(formatter)
log.addHandler(console_handler)
 
 
################################################################################
# Domain Model
 
Base = declarative_base()
log.info("base class generated: {}".format(Base) )
 
# define our domain model
class Species(Base):
    """
    domain model class for a Species
    """
    __tablename__ = 'species'
 
    # database fields
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    breeds = relationship('Breed', backref="species")
 
    # methods
    def __repr__(self):
        return self.name                   
 
breed_breedtrait_table = Table('breed_breedtrait', Base.metadata,
    Column('breed_id', Integer, ForeignKey('breed.id'), nullable=False),
    Column('breedtrait_id', Integer, ForeignKey('breedtrait.id'), nullable=False)
)

class Breed(Base):
    """
    domain model class for a Breed
    has a with many-to-one relationship Species
    """
    __tablename__ = 'breed'
 
    # database fields
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    species_id = Column(Integer, ForeignKey('species.id'), nullable=False )            
    pets = relationship('Pet', backref="breed")
    traits = relationship('BreedTrait', secondary=breed_breedtrait_table, backref='breeds')
    # methods
    def __repr__(self):
        return "{}: {}".format(self.name, self.species) 
 
 
#########################################################
#   Add your code for BreedTraits object here			#
#########################################################
class BreedTrait(Base):
    """
    domain model class for a Breed's Trait
    has a many-to-many relationship Breed
    """
    __tablename__ = 'breedtrait'

    # database fields
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    #has a many to many relationship with Breed, backref='breeds'
 
class Shelter(Base):
    __tablename__ = 'shelter'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    website = Column(Text)
    pets = relationship('Pet', backref="shelter")
 
    def __repr__(self):
        return "Shelter: {}".format(self.name) 
 
 
# our many-to-many association table, in our domain model *before* Pet class 
#pet_person_table = Table('pet_person', Base.metadata,
#    Column('pet_id', Integer, ForeignKey('pet.id'), nullable=False),
#    Column('person_id', Integer, ForeignKey('person.id'), nullable=False)
#)
 
 
class Pet(Base):
    """
    domain model class for a Pet, which has a many-to-one relation with Shelter, 
    a many-to-one relation with breed, and a many-to-many relation with person
    """
    __tablename__ = 'pet'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    age = Column(Integer)
    adopted = Column(Boolean)
    breed_id = Column(Integer, ForeignKey('breed.id'), nullable=False ) 
    shelter_id = Column(Integer, ForeignKey('shelter.id') ) 
    
    # no foreign key here, it's in the many-to-many table        
    # mapped relationship, pet_person_table must already be in scope!
    #people = relationship('Person', secondary=pet_person_table, backref='pets')
    
    #methods
    def __repr__(self):
        return "Pet:{}".format(self.name)
    def nicknames(self):
        return [assoc.nickname for assoc in self.person_assocs]
 
class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    first_name = Column(String, nullable=False)
    last_name = Column(String, nullable=False)
    age = Column(Integer)
    _phone = Column(String)
 
    # mapped relationship 'pets' from backref on Pet class, so we don't
    # need to add it here.
 
    @property
    def phone(self):
        """return phone number formatted with hyphens"""
        # get the phone number from the database, mapped to private self._phone
        num = self._phone
        # return a formatted version using hyphens
        return "%s-%s-%s" % (num[0:3], num[3:6], num[6:10])
 
    # phone number writing property, writing to public Person.phone calls this 
    @phone.setter 
    def phone(self, value):
        """store only numeric digits, raise exception on wrong number length"""
        # remove any hyphens
        number = value.replace('-', '')
        # remove any spaces
        number = number.replace(' ', '')
        # check length, raise exception if bad
        if len(number) != 10:
            raise Exception("Phone number not 10 digits long")
        else:
            # write the value to the property that automatically goes to DB
            self._phone = number
 
    def __repr__(self):
        return "Person: {} {}".format(self.first_name, self.last_name) 

class PetPersonAssociation(Base):
    __tablename__ = 'pet_person_association'

    __table_args__ = (
        UniqueConstraint('pet_id','person_id',name='pet_person_uniqueness_constraint'),
        )

    id = Column(Integer, primary_key=True)
    
    pet_id = Column(Integer, ForeignKey('pet.id'), nullable=False)
    person_id = Column(Integer, ForeignKey('person.id'), nullable=False)

    nickname = Column(String)

    person = relationship('Person', backref=backref('pet_assocs'))
    pet = relationship('Pet', backref=backref('person_assocs'))


################################################################################
def init_db(engine):
    "initialize our database, drops and creates our tables"
    log.info("init_db() engine: {}".format(engine) )
    
    # drop all tables and recreate
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
 
    log.info("  - tables dropped and created")
 
 
if __name__ == "__main__":
    log.info("main executing:")              
 
    # create an engine
    engine = create_engine('sqlite:///:memory:')
    log.info("created engine: {}".format(engine) )
 
    # if we asked to init the db from the command line, do so
    if True:
        init_db(engine)
 
    # call the sessionmaker factory to make a Session class 
    Session = sessionmaker(bind=engine)
    
    # get a local session for the this script
    db_session = Session()
    log.info("Session created: {}".format(db_session) )
   
 
    # create two people: Tom and Sue
    log.info("Creating person object for Tom")
    tom = Person(first_name="Tom",
                last_name="Smith",
                age=52,
                phone = '555-555-5555')
 
    log.info("Creating person object for Sue")
    sue = Person(first_name="Sue",
                last_name="Johson",
                age=54,
                phone = '555 243 9988')
 
 
    # create two animals, and in process, new species, with two breeds.
    # Note how we only explicitly commit spot and goldie below, but by doing so
    # we also save our new people, breeds, and species.
 
    log.info("Creating pet object for Spot, who is a Dalmatian dog")
    spot = Pet(name = "Spot",
                age = 2,
                adopted = True,
                breed = Breed(name="Dalmatian", species=Species(name="Dog")),
                #people = [tom, sue]
                )
 
    # now we set Spot's breed to a variable because we don't want to create
    # a duplicate record for Dog in the species table, which is what would 
    # happen if we created Dog on the fly again when instantiating Goldie
    dog = spot.breed.species
 
    log.info("Creating pet object for Goldie, who is a Golden Retriever dog")
    goldie = Pet(name="Goldie",
                age=9,
                adopted = False,
                shelter = Shelter(name="Happy Animal Place"),
                breed = Breed(name="Golden Retriever", species=dog)
                ) 
 
    log.info("Adding Goldie and Spot to session and committing changes to DB")
    db_session.add_all([spot, goldie])
    db_session.commit()
 
    #assert tom in spot.people
    #spot.people.remove(tom)
    #assert spot not in tom.pets
 
    #################################################
    #  Now it's up to you to complete this script ! #
    #################################################
    
    # Add your code that adds breed traits and links breeds with traits
    # here.
 
    #################################################
    log.info("Adding traits")
    friendly = BreedTrait(name="friendly")
    furry = BreedTrait(name="furry")
    cute = BreedTrait(name="cute")
    db_session.add_all([friendly, furry, cute])
    db_session.commit()
    goldie.breed.traits.append(friendly)
    goldie.breed.traits.append(furry)
    spot.breed.traits.append(friendly)
    cute.breeds.append(spot.breed)
    db_session.commit()

    log.info("Making new connections with PetPersonAssociation Object")
    tom.pet_assocs.append(PetPersonAssociation(pet=spot, nickname="Fireman"))
    spot.person_assocs.append(PetPersonAssociation(person=sue, nickname="Roger"))
    print "Spot has these nicknames: " + str(spot.nicknames())
    goldie.person_assocs.append(PetPersonAssociation(person=tom, nickname="Ms. Peanut Butter"))
    print "Goldie has these nicknames: " + str(goldie.nicknames())




    db_session.close()
    log.info("all done!")

one-to-many-demo.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Boolean
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref

from sqlalchemy.orm import sessionmaker

import pdb   
import logging
log = logging.getLogger(__name__)

################################################################################
# set up logging - see: https://docs.python.org/2/howto/logging.html

# when we get to using Flask, this will all be done for us
import logging
log = logging.getLogger(__name__)
log.setLevel(logging.DEBUG)
console_handler = logging.StreamHandler()
console_handler.setLevel(logging.DEBUG)
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
console_handler.setFormatter(formatter)
log.addHandler(console_handler)


################################################################################
# Domain Model

Base = declarative_base()
log.info("base class generated: {}".format(Base) )

# define our domain model
class Species(Base):
    """
    domain model class for a Species
    """
    __tablename__ = 'species'

    # database fields
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    breeds = relationship('Breed', backref="species")

    # methods
    def __repr__(self):
        return self.name                   


class Breed(Base):
    """
    domain model class for a Breed
    has a with many-to-one relationship Species
    """
    __tablename__ = 'breed'

    # database fields
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    species_id = Column(Integer, ForeignKey('species.id'), nullable=False ) 
          
    # methods
    def __repr__(self):
        return "{}: {}".format(self.name, self.species) 

class Pet(Base):
    """
    domain model class for a Pet
    has a many-to-one relationship Breed, Shelter
    """
    __tablename__ = 'pet'

    #database fields
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    age = Column(Integer)
    adopted = Column(Boolean)
    breed_id = Column(Integer, ForeignKey('breed.id'), nullable=False)
    shelter_id = Column(Integer, ForeignKey('shelter.id'))

    #methods
    def __repr__(self):
        return "{} is a {} year-old {}; is s/he adopted? {}".format(self.name, self.age, self.breed, self.adopted)

class Shelter(Base):
    """
    domain model class for a Pet Shelter
    """
    __tablename__ = 'shelter'

    #database fields
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    website = Column(String)
    pets = relationship('Pet', backref='shelter')

################################################################################
def init_db(engine):
    "initialize our database, drops and creates our tables"
    log.info("init_db() engine: {}".format(engine) )
    
    # drop all tables and recreate
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

    log.info("  - tables dropped and created")



if __name__ == "__main__":
    log.info("main executing:")              

    # create an engine
    # TODO: allow passing in a db connection string from a command line arg
    engine = create_engine('sqlite:///:memory:')
    log.info("created engine: {}".format(engine) )

    # if we asked to init the db from the command line, do so
    if True:
        init_db(engine)

    # call the sessionmaker factory to make a Session class 
    Session = sessionmaker(bind=engine)
    
    # get a local session for the this script
    db_session = Session()
    log.info("Session created: {}".format(db_session) )
   
    # count our starting species & breeds
    num_species = db_session.query(Species).count()
    num_breeds = db_session.query(Breed).count()
    log.info("starting with {} species and {} breeds".format(num_species, num_breeds) )

    log.info("Creating Species: Cat and Dog (Transient)")
    cat = Species(name="Cat")
    dog = Species(name="Dog")
    
    # we can verify that cat and dog do not have ids yet.
    assert cat.id == None
    assert dog.id == None
    log.info("Type of cat.id is {}. Type of dog.id is {}".format(type(cat.id), type(dog.id) ))


    # add them to the session and commit to save to db
    log.info("Adding dog and cat to session and committing.")    
    db_session.add_all( [cat, dog] )
    db_session.commit()

    # now they have ids
    assert cat.id != None
    assert dog.id != None
    log.info("Now the type of cat.id is {} and type of dog.id is {}".format(type(cat.id), type(dog.id) ))
 
    # let's use that id to make a breed
    log.info("Creating three new dog breeds: Poodle, Labrador Retriever, and Golden Retriever")
    poodle = Breed(name='Poodle', species_id=dog.id)
    lab = Breed(name='Labrador Retriever', species_id=dog.id)
    golden = Breed(name='Golden Retriever', species_id=dog.id)

    # dog.breeds is still empty, as we have saved the above
    log.info("New breeds created, but still transient. There are {} dog breeds in the database".format(
        len(dog.breeds)))
    assert dog.breeds == []
    db_session.add_all( [poodle, lab, golden] )
    db_session.commit()

    # now dog.breeds contains lab, poodle, & golden
    # we can use set to assert on membership disregarding order in list
    assert set( [poodle,lab,golden] ) == set( dog.breeds )
    log.info("New breeds saved. There are now {} dog breeds in the database".format(
        len(dog.breeds)))
   
    # Now let's see how SQLAlchemy let's us assign objects as foreign keys even if those
    # objects have not yet been saved.   l

    # Let's make a new species, parrot and a new breed, Norwegian Blue.
    # But instead of building our relationship with the 'species_id' field,
    # we will use the 'species' relationship. We can use the species parrot, 
    # even though it has no ID and has not yet been persisted. 
    # SQLAlchemy's Identity Map can manage all of this

    log.info("/n/n/Demonstrating the smarts of the Identity Map")
    log.info("There are currently {} parrot species in the db".format(
        db_session.query(Species).filter(Species.name=='Parrot').count() ) )
    log.info("Creating new breed, Norwegian Blue, which is of the Parrot Species")
    norwegian_blue = Breed(name="Norwegian Blue",
        species=Species(name="Parrot")  )
    parrot = norwegian_blue.species
    
    log.info("Type of parrot.id is {} and norwegian_blue.id is {}".format(
        type(parrot.id), type(norwegian_blue.id) ) )
    log.info("But norwegian_blue.species is: {}".format(norwegian_blue.species))
    log.info("And parrot.breeds is {}".format(parrot.breeds))
    assert norwegian_blue.species == parrot
    assert norwegian_blue in parrot.breeds 

    log.info('Now lets add parrot to the session and commit, and we\'ll get' +\
        ' our new breeds for free.')
    log.info('adding and committing parrot to session') 
    db_session.add(parrot)
    db_session.commit()

    log.info("Now parrot.id is {} and norwegian_blue.id is {}".format(
        parrot.id, norwegian_blue.id))    

    log.info("Now creating two shelters")
    nyc = Shelter(name="NYC Pet Orphanage", website="http://www.nyc-pet-orphanage.com")
    nawlins = Shelter(name="New Orleans Pet Hotel", website="http://www.neworleanspethotel.com")
    log.info("Adding shelters and committing changes")
    db_session.add_all([nyc, nawlins])
    db_session.commit()

    log.info("Creating two pets for our database")
    thomas = Pet(name="Thomas", age=5, adopted=False, breed_id=golden.id,
        shelter_id=nyc.id)
    sue = Pet(name="Sue", age=8, adopted=True, breed_id= poodle.id, shelter_id=None)
    log.info("Committing pets")
    db_session.add_all([thomas, sue])
    db_session.commit()

    log.info("We have {} pets in the database".format(db_session.query(Pet).count()))
    log.info("We have {} breeds in the database".format(db_session.query(Breed).count()))
    log.info("We have {} species in the database".format(db_session.query(Species).count()))

    #################################################
    #  Now it's up to you to complete this script ! #
    #################################################
    
    # For each of the following steps, be sure to use log to print out feedback
    # about what's happening.


    # Define a Pet class up above in this script with the other class definitions.
    # Pet should have a name, age, adopted attribute (which is boolean), breed_id, and shelter_id. 

    # Also define a Shelter class. Shelters should have a name and website address.
    # Keep in mind that a pet *must* have a breed, but it may or may not have a shelter.

    # Next, add two shelters to the database. The first shelter's name is "NYC Pet Orphanage",
    # and its website is "http://www.nyc-pet-orphanage.com. The second shelter's name is "New Orleans Pet Hotel" and 
    # its website is "http://www.neworleanspethotel.com". 
    # Add the shelters to the db and commit.


    # Next make two pets. First make a Golden Retriever named "Thomas" who is 5, not adopted,
    # and at the NYC pet orphanage. Then, make a poodle named "Sue" who is 8, adopted, and
    # has no shelter.
    
    # Finally, print a log message that indicates how many pets, breeds, and species 
    # we have in the database   
  
    db_session.close()
    log.info("all done!")

self-referentiality-demo2.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Boolean, Text
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Table
from sqlalchemy.schema import UniqueConstraint
 
import logging
log = logging.getLogger(__name__)
 
################################################################################
# set up logging - see: https://docs.python.org/2/howto/logging.html
 
# when we get to using Flask, this will all be done for us
import logging
log = logging.getLogger(__name__)
log.setLevel(logging.DEBUG)
console_handler = logging.StreamHandler()
console_handler.setLevel(logging.DEBUG)
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
console_handler.setFormatter(formatter)
log.addHandler(console_handler)
 
 
################################################################################
# Domain Model
 
Base = declarative_base()
log.info("base class generated: {}".format(Base) )
 
# define our domain model
class Species(Base):
    """
    domain model class for a Species
    """
    __tablename__ = 'species'
 
    # database fields
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    breeds = relationship('Breed', backref="species")
 
    # methods
    def __repr__(self):
        return self.name                   
 
breed_breedtrait_table = Table('breed_breedtrait', Base.metadata,
    Column('breed_id', Integer, ForeignKey('breed.id'), nullable=False),
    Column('breedtrait_id', Integer, ForeignKey('breedtrait.id'), nullable=False)
)

class Breed(Base):
    """
    domain model class for a Breed
    has a with many-to-one relationship Species
    """
    __tablename__ = 'breed'
 
    # database fields
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    species_id = Column(Integer, ForeignKey('species.id'), nullable=False )            
    pets = relationship('Pet', backref="breed")
    traits = relationship('BreedTrait', secondary=breed_breedtrait_table, backref='breeds')
    # methods
    def __repr__(self):
        return "{}: {}".format(self.name, self.species) 
 
 
#########################################################
#   Add your code for BreedTraits object here			#
#########################################################
class BreedTrait(Base):
    """
    domain model class for a Breed's Trait
    has a many-to-many relationship Breed
    """
    __tablename__ = 'breedtrait'

    # database fields
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    #has a many to many relationship with Breed, backref='breeds'
 
class Shelter(Base):
    __tablename__ = 'shelter'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    website = Column(Text)
    pets = relationship('Pet', backref="shelter")
 
    def __repr__(self):
        return "Shelter: {}".format(self.name) 
 
 
# our many-to-many association table, in our domain model *before* Pet class 
#pet_person_table = Table('pet_person', Base.metadata,
#    Column('pet_id', Integer, ForeignKey('pet.id'), nullable=False),
#    Column('person_id', Integer, ForeignKey('person.id'), nullable=False)
#)
 
 
class Pet(Base):
    """
    domain model class for a Pet, which has a many-to-one relation with Shelter, 
    a many-to-one relation with breed, and a many-to-many relation with person
    """
    __tablename__ = 'pet'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    age = Column(Integer)
    adopted = Column(Boolean)
    breed_id = Column(Integer, ForeignKey('breed.id'), nullable=False ) 
    shelter_id = Column(Integer, ForeignKey('shelter.id') ) 
    
    # no foreign key here, it's in the many-to-many table        
    # mapped relationship, pet_person_table must already be in scope!
    #people = relationship('Person', secondary=pet_person_table, backref='pets')
    
    #adding in self-referential parent-child relationships
    parent_id = Column(Integer, ForeignKey(id), nullable=True)
    parent = relationship('Pet', remote_side=id, backref="children")

    #methods
    def __repr__(self):
        return "Pet:{}".format(self.name)
    def nicknames(self):
        return [assoc.nickname for assoc in self.person_assocs]
 
class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    first_name = Column(String, nullable=False)
    last_name = Column(String, nullable=False)
    age = Column(Integer)
    _phone = Column(String)
 
    # mapped relationship 'pets' from backref on Pet class, so we don't
    # need to add it here.
 
    @property
    def phone(self):
        """return phone number formatted with hyphens"""
        # get the phone number from the database, mapped to private self._phone
        num = self._phone
        # return a formatted version using hyphens
        return "%s-%s-%s" % (num[0:3], num[3:6], num[6:10])
 
    # phone number writing property, writing to public Person.phone calls this 
    @phone.setter 
    def phone(self, value):
        """store only numeric digits, raise exception on wrong number length"""
        # remove any hyphens
        number = value.replace('-', '')
        # remove any spaces
        number = number.replace(' ', '')
        # check length, raise exception if bad
        if len(number) != 10:
            raise Exception("Phone number not 10 digits long")
        else:
            # write the value to the property that automatically goes to DB
            self._phone = number
 
    def __repr__(self):
        return "Person: {} {}".format(self.first_name, self.last_name) 

class PetPersonAssociation(Base):
    __tablename__ = 'pet_person_association'

    __table_args__ = (
        UniqueConstraint('pet_id','person_id',name='pet_person_uniqueness_constraint'),
        )

    id = Column(Integer, primary_key=True)
    
    pet_id = Column(Integer, ForeignKey('pet.id'), nullable=False)
    person_id = Column(Integer, ForeignKey('person.id'), nullable=False)

    nickname = Column(String)

    person = relationship('Person', backref=backref('pet_assocs'))
    pet = relationship('Pet', backref=backref('person_assocs'))


################################################################################
def init_db(engine):
    "initialize our database, drops and creates our tables"
    log.info("init_db() engine: {}".format(engine) )
    
    # drop all tables and recreate
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
 
    log.info("  - tables dropped and created")
 
 
if __name__ == "__main__":
    log.info("main executing:")              
 
    # create an engine
    engine = create_engine('sqlite:///:memory:')
    log.info("created engine: {}".format(engine) )
 
    # if we asked to init the db from the command line, do so
    if True:
        init_db(engine)
 
    # call the sessionmaker factory to make a Session class 
    Session = sessionmaker(bind=engine)
    
    # get a local session for the this script
    db_session = Session()
    log.info("Session created: {}".format(db_session) )
   
 
    # create two people: Tom and Sue
    log.info("Creating person object for Tom")
    tom = Person(first_name="Tom",
                last_name="Smith",
                age=52,
                phone = '555-555-5555')
 
    log.info("Creating person object for Sue")
    sue = Person(first_name="Sue",
                last_name="Johson",
                age=54,
                phone = '555 243 9988')
 
 
    # create two animals, and in process, new species, with two breeds.
    # Note how we only explicitly commit spot and goldie below, but by doing so
    # we also save our new people, breeds, and species.
 
    log.info("Creating pet object for Spot, who is a Dalmatian dog")
    spot = Pet(name = "Spot",
                age = 2,
                adopted = True,
                breed = Breed(name="Dalmatian", species=Species(name="Dog")),
                #people = [tom, sue]
                )
 
    # now we set Spot's breed to a variable because we don't want to create
    # a duplicate record for Dog in the species table, which is what would 
    # happen if we created Dog on the fly again when instantiating Goldie
    dog = spot.breed.species
 
    log.info("Creating pet object for Goldie, who is a Golden Retriever dog")
    goldie = Pet(name="Goldie",
                age=9,
                adopted = False,
                shelter = Shelter(name="Happy Animal Place"),
                breed = Breed(name="Golden Retriever", species=dog)
                ) 
 
    log.info("Adding Goldie and Spot to session and committing changes to DB")
    db_session.add_all([spot, goldie])
    db_session.commit()
 
    #assert tom in spot.people
    #spot.people.remove(tom)
    #assert spot not in tom.pets
 
    #################################################
    #  Now it's up to you to complete this script ! #
    #################################################
    
    # Add your code that adds breed traits and links breeds with traits
    # here.
 
    #################################################
    log.info("Adding traits")
    friendly = BreedTrait(name="friendly")
    furry = BreedTrait(name="furry")
    cute = BreedTrait(name="cute")
    db_session.add_all([friendly, furry, cute])
    db_session.commit()
    goldie.breed.traits.append(friendly)
    goldie.breed.traits.append(furry)
    spot.breed.traits.append(friendly)
    cute.breeds.append(spot.breed)
    db_session.commit()

    log.info("Making new connections with PetPersonAssociation Object")
    tom.pet_assocs.append(PetPersonAssociation(pet=spot, nickname="Fireman"))
    spot.person_assocs.append(PetPersonAssociation(person=sue, nickname="Roger"))
    print "Spot has these nicknames: " + str(spot.nicknames())
    goldie.person_assocs.append(PetPersonAssociation(person=tom, nickname="Ms. Peanut Butter"))
    print "Goldie has these nicknames: " + str(goldie.nicknames())

    log.info("Building parent-child relationships")
    silver = Pet(name="Silver",age=5,breed_id=goldie.breed_id,parent=goldie)
    bronze = Pet(name="Bronze",age=1,breed_id=goldie.breed_id,parent=goldie)
    titanium = Pet(name="Titanium",age=1,breed_id=goldie.breed_id,parent=silver)
    #import pdb
    #pdb.set_trace()




    db_session.close()
    log.info("all done!")