Databases One-to-Many Relationships

Link to one-to-many branch on Github

Link to one-to-many Repl

Resources

This was pieced together from a wide-range of resources including:

• SQLALchemy: One to many-relationships
• Inserting new records with one-to-many relationship in sqlalchemy
• How to query a one to many/many to many relationship in Flask SQL Alchemy?
• SQLAlchemy+Flask Many to one relationship
• Create One To Many Database Relationship - Flask Fridays #28
• Creating One-To-Many Relationships in Flask-SQLAlchemy

Some things to look out for when you research your own - many tutorials use a db model class structure that looks like this:

message = db.Column('message', db.String)

But we have used a session based structure:

message = Column('message', String)

So far our database has had one-to-one relationships (e.g. one thing has just one owner). Sometimes though we need to make use of one-to-many relationships. For example think of someone using (or borrowing) the things in our database, where someone can use more than one thing at a time. In this case we have a one (person)-to-one (thing) relationship for owning and a one (person)-to-many (things) relationship for using. We can make our database do this using SQLAlchemy.

In this example we are going to create a Column in the People class that holds a list of Things that the person is using. This uses something called relationships.

1. Ensure you have relationship as an import from sqlalchemy.orm import declarative_base, relationship
2. Move the whole of the Thing class so that it is before the Person class. This is because we reference the Thing class from the Person class - and cannot do this if we have it after the Person class!
3. In the Person class add an extra table called things_used as follows: things_used = relationship('Thing', foreign_keys=[Thing.user_id],backref='user', lazy='dynamic') Relationship indicates that what follows is a relationship! Thing means it is a relationship with the Thing class, foreign_keys=[Thing.user_id] indicates that the key is in the user_id column of the Thing (this is needed in our particular case as we also have a foreign key for owner), backref-'user' creates a two-way relationship with the Thing class and lazy=dynamic reloads the database after every query (good for small databases where data might change).
4. In the Thing class add an extra table as follows: user_id = Column(Integer, ForeignKey('people.ssn'))

Your Thing tables should now look like this:

And your Person tables like this:

You will also need to create a route and page for "Who uses what" as well as menu options for this. The query to get the information is simply results=session.query(Person).all() because we want to get all the people. In the 'test' code we use a couple of for loops to go through the people and then the list of things they used (lines are commented to show this).

The HTML page also has a similar loop within a loop to display the things used for each person.

Finally, I added multiple things used to the first two people on lines 136-7 using

p1.things_used = [t1,t3,t5]

p2.things_used = [t2,t4,t6]

This is the output: