Creating relationships using SQLAlchemy in Python

SQLAlchemy can help with many tasks required in Python when trying to create SQL tables one of those tasks is creating relationships.

Creating relationships with SQLAlchemy is made much easier than using just SQL. It streamlines the process with easier to follow syntax and less steps.

SQLAlchemy is imported into Python and all of the short cut syntax can be used.

 from flask_sqlalchemy import SQLAlchemy

To create a relationship you must start by creating a table. SQLAlchemy makes this easy as well by shorting the syntax to:

class Owner(db.Model, SerializerMixin):
    __tablename__ = "owners"
    id = db.Column(db.Integer, primary_key=True)

This will create an owners table with an id that is a primary key. Other code can be used to create the column attributes for owner. These will help with connecting the relationship. Once the columns are created you can work on the connecting the Dog table that we will create later. The code for the relationship is:

dogs = db.relationship('Dog', back_populates="owner")

This connects dog to owner where an owner can have many dogs but a dog has one owner as seen by the back populates being singular.

Then you can create the dog class and table. It can have a number of column attributes. The important one for the relationship is it should have an owner id with a foreign key. This is written in SQLAlchemy as:

owner_id = db.Column(db.Integer, db.ForeignKey('owners.id')) 

Then like owner it will have a relationship. This one will be the opposite and connect dog to owner with the below code:

owner = db.relationship('Owner', back_populates="dogs")

Here instead of back populates being dog it is dogs as an owner can have many dogs while the previous code was owner because a dog has one owner.

Now the two tables will have a relationship. As stated before this juristically decreases the amount of code required and makes the syntax simple then having to write it in SQL.

More code can be added to add an association proxy and create serialization rules which will help an infinite loop but the above will create the initial relationship a lot faster than without SQLAlchemy.