Models are Python classes that define the structure and behavior of your database tables using an ORM, in this case SQLAlchemy.
If you think of Models like Excel spreadsheets, each Model represents a new table or sheet and within each model there are class attributes that represent each column name. This is a way of Pythonically writing SQL and thus highlights how SQLAlchemy will be used in this project.
Step 1: Create models.py
In your project_name folder, create a models.py file.
Step 2: imports
Import the database (db) you created in your __init__.py file in your project_name folder.
Step 3: Create a Model
Create a Class of your Model. For this example I have created one named Category. Then give it class attributes. In this example I have chosen id as the primary key and given examples of other class attributes.
Add a
def __repr__(self) function that returns each table as a String. It can simple
or use Python table methods. Two examples are given here.
class Category(db.Model):
id = db.Column(db.Integer, primary_key = True)
category_name=db.Column(db.String(25), unique = True, nullable = False)
# Example class attributes
# Primary key
id = db.Column(db.integer, primary_key = True)
# String type, max length 50, unique, cannot be Null
task_name = db.Column(db.String(50), unique = True, nullable = False)
# Text type, cannot be Null
task_description = db.Column(db.Text, nullable = False)
# Boolean typle, default value of False, cannot be NUll
is_urgent = db.Column(db.Boolean, default= False, nullable = False)
# Date type, cannot be Null
due_date = db.Column(db.Date, nullable = False)
# Integer type, is a Foreign Key referenced in Category, if it is deleted, so is the Category, cannot be Null
category_id = db.Column(db.Integer, db.ForeignKey("category.id", ondelete = "CASCADE"), nullable = False)
# Simple __repr__():
def __repr__(self):
return self.category_name
# Helpful __repr__():
# {0}, {1}, {2} refer to the index of the the things defined inside format()
def __repr__(self):
return "#{0} - Task: {1} | Urgent: {2}".format(
self.id, self.task_name, self.is_urgent
)
# Output: self.id - Task: task_name | Urgent: self.is_urgent
Step 4: Relational Database
In a relational database one Model (Model 1) will have a class attribute that points to another Model (Model 2).
This relationship is described in Model 1 as a
db.relationship()
and in Model 2 as
db.ForeignKey().
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
#
class Category(db.Model):
id = db.Column(db.Integer, primary_key=True)
category_name = db.Column(db.String(50), unique=True, nullable=False)
# Class Model has a table column referencing the Tasks primary key
tasks = db.relationship(
"Task", backref="category", cascade="all, delete", lazy="select"
)
def __repr__(self):
return self.category_name
class Task(db.Model):
id = db.Column(db.Integer, primary_key=True)
task_name = db.Column(db.String(50), unique=True, nullable=False)
task_description = db.Column(db.Text, nullable=False)
is_urgent = db.Column(db.Boolean, default=False, nullable=False)
due_date = db.Column(db.Date, nullable=False)
# Use a Foreign Key to relate back to the Category Model
category_id = db.Column(
db.Integer,
db.ForeignKey("category.id", ondelete="CASCADE"),
nullable=False
)
def __repr__(self):
return "#{0} - Task: {1} | Urgent: {2}".format(
self.id, self.task_name, self.is_urgent
)
-
In the Category Model, name it something similar but not the same e.g. tasks for Task model. For the syntax
of
tasks = db.relationship("Task", backref="category", cascade="all, delete", lazy="select"):-
Set up the relationship with
db.relationship() -
"Task"references the name of the other Model -
"backref="category""rets up a reverse reference from the Task model back to an instance the Category model. The name "category" (lowercase) relates to an instance of the Category Model rather than the Model itself. -
cascade="all, delete"defines what will happen to the child object (Task) when the parent object (Category) is deleted. In this case it will also be deleted. For this scenario; without this specified, if you try to delete a Category that still has related Tasks, you'd likely get an integrity error, since the tasks would still reference a deleted category via the foreign key attribute defined in Task. -
lazy="select"Sets up lazy loading so it won't load the related tasks from the database until you actually access the .tasks attribute. Meaning a shorter load time at the start since it will only be loaded when needed.
-
Set up the relationship with
-
In the Task Model define a foreign key column linking each Task to a Category with
category_id = db.Column(db.Integer,db.ForeignKey("category.id", ondelete="CASCADE"),nullable=False):-
category_id = db.Column(...)creates a column in the tasks table named category_id. -
db.Integersets the data type of the column as an integer. -
db.ForeignKey("category.id", ondelete="CASCADE")makes the column a foreign key that links to another table.-
"category.id"refers to the id column of the category table (from the Category Model), telling SQLAlchemy that this column must match a valid ID from the category table. -
ondelete="CASCADE"sets up the CASCADE delete so that if a category is deleted, then all tasks with that category_id will also be automatically deleted.
-
-