Meerkats! SQLAlchemy! Mob magic?

-ben tryon

Of all the relationships you encounter in life, the most important, by far, is the one between you and your colony. As a meerkat, I have a responsibility to stand atop photographer's heads, tree stumps, other 'kats... it's a life to... look out for.

One-to-many relationships and many-to-many relationships are pivotal to the life of a meerkat when they interact with Flask, Python (not that type!) and SQLAlchemy. These three languages are how we had our first house and all it's tables built.

A one-to-many relationship is when one item in a table is associated with multiple items in another table. My colony can have many meerkats, multiple meerkats. A many-to-many relationship is when multiple items in one table are associated with multiple items in another table. One meerkat can dig many tunnels in our home colony, and one colony can have multiple meerkats contributing to digging one tunnel.

To represent these relationships in Flask, we can use SQLAlchemy. We can create two tables, one for colonies and one for members - these are one-to-many relationships - and use a foreign key to link the two:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///meerkats.db'
db = SQLAlchemy(app)

class Colony(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)
    tunnels = db.relationship('Tunnel', backref='colony')

class Tunnel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    length = db.Column(db.Float, nullable=False)
    colony_id = db.Column(db.Integer, db.ForeignKey('colony.id'), nullable=False)

Two classes, 'Colony' and 'Tunnel' are defined and correspond with the tables in the database. The 'Colony' class has a 'tunnels' attribute that creates a one-to-many relationship with the 'Tunnel' class using the db.relationship() function. I also defined a foreign key 'colony_id' in the 'Tunnel' class to link it back to the 'Colony' class. It is amazing what one can do with curved claws and a propensity to watch. We, the mob, saw a researcher from NatGeo typing away on their Lenovo. We one-upped her. She was like 'whoa! Meerkats! Coding in Python! And with claws-adapted keyboards!' That was a fun day.

Let's move on to many-to-many relationships. I created a third table, called a join table (they wouldn't call it a 'mob' table! Owls and their parliament, wildebeests and their confusions get all the good group names...)

class Meerkat(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)
    tunnels = db.relationship('Tunnel', secondary='meerkat_tunnel', backref='meerkats')

meerkat_tunnel = db.Table('meerkat_tunnel',
    db.Column('meerkat_id', db.Integer, db.ForeignKey('meerkat.id'), primary_key=True),
    db.Column('tunnel_id', db.Integer, db.ForeignKey('tunnel.id'), primary_key=True)
)

My NEW 'Meerkat' class was created and I updated the 'Tunnel' class to use a many-to-many relationship. These relationships are mad real for us. Without them, we'd be a bunch of house cats. I also defined a join table called 'meerkat_tunnel' that has the foreign keys to both the 'Meerkat' and 'Tunnel' tables. This is the magic of tables - the join and the relationships allow an interplay of activity and code. The join table is the bridge to the two tables being linked - it stores the foreign keys of both tables and maps the associations of each. The defining of the foreign keys - the column of integers to identify row-embedded information for meerkat_id and tunnel_id - allowed the queen to query all the tunnels that a particular meerkat had dug or gave her the tools to find that the mob had worked on a particular tunnel. She had admonished us for laziness a few days earlier. Little did she know, she was soon to have a midnight meeting with a hyena, not our fault she took a wrong turn!

I am next on lookout, so gotta wrap it up: designing and building robust databases are dependent upon an acute understanding of one-to-many and many-to-many relationships. Our tunnels would not have been built without the planning of our python crew. It helped us visualize and scale the growth to make our colony an interconnected maze of a subterranean city. Whether you're a NatGeo researcher or a wildebeest, SQLAlchemy is a powerful tool that can help you efficiently manage your data and relationships.