3rd updated version with a lot of input from the GAE group. Thanks to everyone!
ER-Modeling with Google App Engine is somewhat different to "normal" modeling for a relational database.
Here is a small tutorial on how to create well-known relationship models One-to-One (1:1), One-to-Many (1:n), Many-to-Many (m:n) and a special one (Cascading relations) with GAE:
We need the following Model classes for our example. Also if you want to run it, don't forget to import the sys module - we need it for printing to stdout:
import sys #import sys module for printing to stdout
class Car(db.Model):
brand = db.StringProperty(required=True)
wheels = db.ListProperty(db.Key)
class Human(db.Model):
name = db.StringProperty(required=True)
drives = db.ReferenceProperty(reference_class=Car)
spouse = db.SelfReferenceProperty()
owns = db.ListProperty(db.Key)
class Wheel(db.Model):
isBroken = db.BooleanProperty(default=False)
position = db.StringProperty(choices=set(["left_front",
"left_back",
"right_front",
"right_back"]))
One-to-One (1:1)
A simple relationship between two entities.
Let's say a human called Jack drives one car.
We can model this relationship as following:
# one-to-one
jack = Human(name="Jack")
mercedes = Car(brand="Mercedes")
jack.drives = mercedes.put()
jack.put()
print >> sys.stdout, "Jack drives a "+jack.drives.brand
# Jack drives a Mercedes
As you can see, we create a human called "Jack" and a car "Mercedes". After that we assign the car to the drives-property of Jack and save Jack.
Hint (Thanks to Miguel for pointing this out): be careful with 1:1 relationships done with ReferenceProperty-properties - you could easily write something like this:
jack = Human(name="Jack")
mike = Human(name="Mike")
mercedes = Car(brand="Mercedes")
mercedesid = mercedes.put()
jack.drives = mercedesid
jack.put()
mike.drives = mercedesid
mike.put()
which won't be a 1:1 relation any more. So if you really need to make sure that an entity is only referenced once, you need to do this by your code design (by searching within your existing model kinds and throwing exceptions).
Even using a cascading-relationship with a parent entity (see later in this article) does not make sure, that there is only one car per human and therefore includes the same difficulty just the other way round.
Special: self references
Now a special sort of references are self-references. That means a Model references an entity of the same Model class (e.g. a Human references a Human):
# one-to-one self
bob = Human(name="Bob")
jane = Human(name="Jane")
bob.spouse = jane.put()
bob.put()
b_spouse = Human.get(bob.spouse.key())
print >> sys.stdout, "Bob's spouse is "+b_spouse.name
# Bob's spouse is Jane
We created two humans (Bob and Jane) and set Bob's
spouse-property to reference Jane.
Now we can easily find out who Bob's spouse is.
Special: mutual references
But if Bob is married to Jane, Jane is also married to Bob, isn't she?
So let's do this semantically correct:
# one-to-one self mutual
bob = Human(name="Bob")
jane = Human(name="Jane")
bob.spouse = jane.put()
jane.spouse = bob.put()
jane.put()
j_spouse = Human.get(jane.spouse.key())
print >> sys.stdout, "Jane's spouse is "+j_spouse.name
b_spouse = Human.get(bob.spouse.key())
print >> sys.stdout, "Bob's spouse is "+b_spouse.name
# Jane's spouse is Bob
# Bob's spouse is Jane
Be careful to check if reflexive references are semantically allowed or not – in our case it wouldn't be valid if bob's spouse is himself.
Also in a monogamous society, it wouldn't be valid if Bob is the spouse of more than one other Human entity (!)
If you don't want this behavior, you need to prevent it by throwing exceptions. You could use the validator-parameter of every Property class.
One-to-Many (1:n)
Sometimes we need to reference more than one entity from another.
When modeling 1:n relationships, a special of the Model class comes in handy: parent models (or ancestors).
Let's think of a car having four wheels. Those wheels belong exactly to one car, so we define the car as parent for every wheel we create:
# one-to-many using parent
bmw = Car(brand="BMW")
bmw.put()
lf = Wheel(parent=bmw,position="left_front")
lf.put()
lb = Wheel(parent=bmw,position="left_back")
lb.put()
rf = Wheel(parent=bmw,position="right_front")
rf.put()
# uh, snap, the 4th wheel is broken!
rb = Wheel(parent=bmw,position="right_back",isBroken=True)
rb.put()
# from car to wheels
bmwWheels = Wheel.all().ancestor(bmw)
print >> sys.stdout, "The BMW has the wheels: "
for wheel in bmwWheels:
print >> sys.stdout, "- "+wheel.position
# The BMW has the wheels:
# - left_front
# - right_back
# - right_front
# - left_back
# from wheel to car
brokenWheels = Wheel.gql("WHERE isBroken = :broken",
broken=True)
print >> sys.stdout, "The following cars are broken: "
for wheel in brokenWheels:
print >> sys.stdout, "- "+wheel.parent().brand
# - BMW
We can see: it is easy to get the wheels for a car and it is also possible to get the car a certain wheel belongs to. Also an entity can only have one parent at a time - so we made sure, that a wheel is not used by more than one car at the same time. You could even add some spare wheels to a car (there is no way to define a maximum number an entity can be used as parent entity).
The other way round
It is also possible to do this the other way round. Lets say we have an additional Model like this:
class OwnedCar(db.Model):
brand = db.StringProperty(required=True)
owner = db.ReferenceProperty(Human, required=True)
then we could add cars to a person as following:
paul = Human(name="Paul")
paul.put()
pauls_bmw = OwnedCar(brand="BMW", owner=paul)
pauls_bmw.put()
pauls_mercedes = OwnedCar(brand="Mercedes", owner=paul)
pauls_mercedes.put()
pauls_cars = paul.ownedcar_set
print >> sys.stdout, "Paul's cars: "
for car in pauls_cars:
print >> sys.stdout, "- "+car.brand
# Paul's cars:
# - BMW
# - Mercedes
This makes for example sure, that one car is only owned by one human at a time.
Also notice the part on how to get Paul's cars. You don't even need to create a GQL query - the property modelname_set holds the references. You can find more about this in the docs.
Thanks to Aprigio for his input!
Special: One-to-Many using a list
You can also create 1:n relationships using a list:
# one-to-many using list
dodge = Car(brand="Dodge")
w1 = Wheel(position="left_front")
w2 = Wheel(position="left_back")
w3 = Wheel(position="right_front")
w4 = Wheel(position="right_back")
dodge.wheels = [w1.put(),w2.put(),w3.put(),w4.put()]
dodge.put()
dodgeWheels = Wheel.get(dodge.wheels)
print >> sys.stdout, "The Dodge has the wheels: "
for wheel in dodgeWheels:
print >> sys.stdout, "-"+wheel.position
# The Dodge has the wheels:
# -left_front
# -left_back
# -right_front
# -right_back
but: be careful, this model does not make sure, a wheel does belong to exactly one car! You could reference a wheel from more than one car, which would be semantically wrong in our example. So rather use this model for n:m relationships!
Many-to-Many (m:n)
For creating m:n relationships, use the following model:
# many-to-many using list+db.Key
jack = Human(name="Jack")
bob = Human(name="Bob")
vw = Car(brand="VW")
chevy = Car(brand="Chevy")
carpool = [vw.put(),chevy.put()]
jack.owns = carpool
jack.put()
bob.owns = carpool
bob.put()
chrysler = Car(brand="Chrysler")
jack.owns.append(chrysler.put())
jack.put()
jackOwns = Car.get(jack.owns)
print >> sys.stdout, "Jack owns: "
for car in jackOwns:
print >> sys.stdout, "- "+car.brand
# Jack owns:
# - VW
# - Chevy
# - Chrysler
whoOwnsTheChevy = Human.gql("WHERE owns = :car",car=chevy)
print >> sys.stdout, "These humans own the Chevy: "
for who in whoOwnsTheChevy:
print >> sys.stdout, "- "+who.name
# - Jack
# - Bob
First we create our two guys Jack and Bob. Then they decide on sharing cars. Their carpool consists of a VW and a Chevy. After that, Jack decides on buying an additional car for himself (the Chrysler).
When adding to a list like in this example, there is no check if the value already is present within the list - so it would be possible to reference the same entity multiple times. If you want to prevent this, you need to run a function on the list, which makes sure there are only unique entities:
def unique(lst):
d = {}
for item in lst:
d[item] = 1
return d.keys()
jeep = Car(brand="Jeep")
jack.owns = unique(jack.owns + [jeep.put()])
jack.put()
You could also check to content of the list before adding an entity, if it is already referenced.
Most flexible: using mapping entitiesIt might be a better idea to implement a different entity to represent the relationship
between a human and the cars they own.
The reason for this is that you could add more fields to this which may be beneficial later in a query. Lets say we add a bought field that contains the date the car was bough. Then one could get cars owned by Jack that he bought after a certain date.
class CarOwner(db.Model):
car = db.Reference(Car, required=True)
owner = db.Reference(Human, required=True)
bought = db.DateProperty(auto_now_add=True)
@staticmethod
def get_owner_cars(human, bought):
"""Returns the cars that the given human
bought before a specified date"""
if not human: return []
query = db.Query(CarOwner)
query.filter('owner =',human)
query.filter('bought >= ',bought)
return [entry.car for entry in query]
also we'd like to add a function to the Car-Model, so that it looks like this:
class Car(db.Model):
brand = db.StringProperty(required=True)
wheels = db.ListProperty(db.Key)
def human_owns(self, human):
"""Returns true if the given human owns this car."""
if not human: return False
query = db.Query(CarOwner)
query.filter('car =', self)
query.filter('owner =', human)
return query.get()
When we have the Models defined like that, we can go on using them:
max = Human(name="Max")
max.put()
saab = Car(brand="Saab")
saab.put()
ownership = CarOwner(car=saab,owner=max)
ownership.put()
import datetime
delta = datetime.timedelta(days=-1)
yesterday = (datetime.datetime.utcnow() + delta)
maxs_cars = CarOwner.get_owner_cars(max,yesterday)
print >> sys.stdout, "Max's cars since yesterday: "
for car in maxs_cars:
print >> sys.stdout, "- "+car.brand
# Max's cars since yesterday:
# - Saab
max_owns_saab = bool(saab.human_owns(max))
print >> sys.stdout, "Max owns the Saab: "+str(max_owns_saab)
# Max owns the Saab: True
As you can see, the additional property on the mapping entity comes in handy when selecting the cars Max bought since yesterday.
And also the method on the car-Model is pretty neat to bundle functionality related to a model within it.
Note: you can use this sort of referencing also use for 1:1 and 1:n relationships. You just need to make sure, that either one referenced entity (1:n) is unique, or both (the combination of those - for 1:1)
Thanks to Brian, who brought this up!
Cascading relations
With the
parent-property, cascading relationships are possible. This is very useful, if you have a folder-like structure (e.g. categories and products of an online shop). Let's have a look on how to do this:
import sys
class Category(db.Model):
name = db.StringProperty(required=True)
class Product(db.Model):
name = db.StringProperty(required=True)
price = db.FloatProperty()
categories = db.ListProperty(db.Key)
root = Category(name="Products")
root.put()
tech = Category(parent=root,name="Tech stuff").put()
books = Category(parent=root,name="Books")
books.put()
fantasy = Category(parent=books,name="Fantasy")
fantasy.put()
scifi = Category(parent=books,name="Science Fiction")
scifi.put()
scomics = Category(parent=scifi,name="SciFi comics")
scomics.put()
somebook = Product(name="Some book")
somebook.categories.append(books.key())
somebook.price = 9.99
somebook.put()
lotr = Product(name="Lord Of The Rings")
lotr.categories.append(fantasy.key())
lotr.price = 29.99
lotr.put()
allFantasyBooks = Product.gql("WHERE categories = :cat",
cat=fantasy)
print >> sys.stdout, "All fantasy books: "
for book in allFantasyBooks:
print >> sys.stdout, "- "+book.name
path = []
p = scomics
while p.parent():
path.append(p.name)
p = p.parent()
path.reverse()
print >> sys.stdout, " > ".join(path)
# Books > Science Fiction > SciFi comics
Now getting all books in a category is pretty easy, huh? And also generating a breadcrumb-like "where am I" is simple by iterating through all parent entities for a given entity (here it is the science fiction comics Category).
Attention: while the use of parent relationships if fine for the use case here, it would not be very efficient in the case where there are a lot of children associated with a parent.
This is because this results in a very large entity group.
The more entity groups your application has - that is, the more root entities there are - the more efficiently the datastore can distribute the entity groups across datastore nodes.
Thus for efficiency you should avoid the case where there are a lot of children.
According to the docs:
"A good rule of thumb for entity groups is that they should be about the size of a
single user's worth of data or smaller."
Thanks to Ben, Michael and Brian for pointing this out.
Write down your public and private key - we will need them later. If you forget them, you can return to recaptcha.net to find them out. Don't tell anyone your private key.
After having registered for the service, you should get a simple service class I adapted from python.org to GAE. You can download the adapted class here. Rename it to captcha.py and copy it somewhere (I prefer putting it in a subdirectory named recaptcha/client/ - remember to add an empty __init__.py in every directory if you do this) within your App Engine project.
