2. Defining Models and Schema with Flask-SQLAlchemy

database models and schema abstraction

Welcome back to our Flask-SQLAlchemy training program! The first post was an introduction to flask-sqlalchemy. In this second post, we will dive into defining models and schema in Flask-SQLAlchemy. We’ll cover creating a model with SQLAlchemy, defining fields and data types, and establishing relationships between models.

Creating a Model with SQLAlchemy

In Flask-SQLAlchemy, a model is a Python class that represents a database table. Each instance of the class is a row in the table. To create a model, you subclass the db.Model class and define the fields as class variables.

Understanding the correlation between a model, class, and table in SQL is crucial when working with an Object-Relational Mapper (ORM) like SQLAlchemy. Here’s a breakdown:

  1. SQL Table: In a relational database, data is structured into one or more tables. A table is a collection of related data entries and consists of columns and rows. Each column in a table represents a particular type of data and each row in the table represents a single record.
  2. Python Class: In object-oriented programming, a class is a code template used to create objects. Objects have member variables and methods associated with them. In the context of an ORM, each class represents a table in the database.
  3. Model: A model in the context of an ORM like SQLAlchemy is a Python class that is tied to a SQL table. The model class defines the structure of the table it represents, including the column names, data types, constraints, etc. Each instance of the class represents a row in the table.

So, in summary:

  • A SQL table is represented in SQLAlchemy as a Python class (model).
  • The columns of the table are defined as class variables in the model.
  • Each row in the SQL table corresponds to an instance of the model.

Here’s an example of a simple model representing a User:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(120), nullable = False)
    is_verified = db.Column(db.Boolean, default = False)
    created_on = db.Column(db.Date)

 
    def __repr__(self):
        return f'<User {self.username}>'

In this code, id, username, and email are columns in the User table. The db.Column class is a constructor for a column, and you pass the data type of the column as the first argument.

The equivalent SQL statement for creating the table that corresponds to this Flask-SQLAlchemy model. Please note that the exact SQL syntax might vary slightly depending on the specific database system you are using. Here’s a generic version:

CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    username VARCHAR(80) NOT NULL UNIQUE,
    email VARCHAR(120) NOT NULL UNIQUE,
    password VARCHAR(120),
    is_verified BOOLEAN DEFAULT FALSE,
    created_on DATE DEFAULT CURRENT_DATE NOT NULL
);

In this SQL statement:

  • CREATE TABLE user begins the statement and gives the table its name (“user”).
  • id INTEGER PRIMARY KEY creates the id column as an integer and sets it as the primary key.
  • username VARCHAR(80) NOT NULL UNIQUE creates the username column as a string (varchar) with a maximum length of 80 characters. It can’t be null (NOT NULL), and it must be unique (UNIQUE).
  • email VARCHAR(120) NOT NULL UNIQUE does the same for the email column, but with a maximum length of 120 characters.
  • password VARCHAR(120) creates the password column as a string with a maximum length of 120 characters.
  • is_verified BOOLEAN DEFAULT FALSE creates the is_verified column as a boolean with a default value of FALSE.
  • created_on DATE DEFAULT CURRENT_DATE NOT NULL creates the created_on column as a date, with a default value of the current date (CURRENT_DATE), and it can’t be null.

This SQL statement represents the same schema as your Flask-SQLAlchemy User model. When you define a model in Flask-SQLAlchemy and run your application, Flask-SQLAlchemy generates SQL statements like this one to create the corresponding tables in the database.

Defining Fields and Data Types

Each column in a table is defined as a db.Column instance, which takes a SQLAlchemy data type as a required argument. SQLAlchemy provides a wide range of data types that abstract the underlying SQL data types. Here are a few examples:

  • db.Integer: represents an integer.
  • db.String(size): represents a string with a maximum length.
  • db.DateTime: represents a datetime object.
  • db.Boolean: represents a boolean value.

Let’s consider a different example, such as an online bookstore. We can have Book and Author models and establish a relationship between them.

Creating Models: Book and Author

Here’s how you might define Book and Author models:

class Author(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    books = db.relationship('Book', backref='author', lazy=True)

    def __repr__(self):
        return f'<Author {self.name}>'

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    publication_date = db.Column(db.DateTime, nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey('author.id'), nullable=False)

    def __repr__(self):
        return f'<Book {self.title}>'

Defining Fields and Data Types

In the Author model, id and name are fields representing an integer and a string respectively. The books field is a relationship field that will link to the Book model.

In the Book model, we have id, title, and publication_date representing an integer, string, and datetime respectively. The author_id field is a foreign key that links each book to an author.

Field Types

SQLAlchemy provides a variety of field types that can be used to define the structure of your database tables. Here are some of the most commonly used ones:

  1. Integer: This type represents an integer and translates to INT in SQL. Example: db.Column(db.Integer)
  2. Float: This type represents a floating point number and translates to FLOAT in SQL. Example: db.Column(db.Float)
  3. Boolean: This type represents a boolean (True or False) value and translates to BOOLEAN in SQL. Example: db.Column(db.Boolean)
  4. String(size): This type represents a string with a maximum length. It translates to VARCHAR(size) in SQL. Example: db.Column(db.String(50))
  5. Text: This type represents a variable length string, without a maximum length. It translates to TEXT in SQL. Example: db.Column(db.Text)
  6. DateTime: This type represents a date and time. It translates to DATETIME in SQL. Example: db.Column(db.DateTime)
  7. Date: This type represents a date. It translates to DATE in SQL. Example: db.Column(db.Date)
  8. Time: This type represents a time. It translates to TIME in SQL. Example: db.Column(db.Time)
  9. Enum: This type represents a list of string-based options. It translates to ENUM in SQL. Example: db.Column(db.Enum('option1', 'option2'))
  10. Binary: This type represents a binary blob. It translates to BLOB in SQL. Example: db.Column(db.Binary)
  11. PickleType: This type represents a Python pickle object. It translates to BLOB in SQL. Example: db.Column(db.PickleType)
  12. LargeBinary: This type represents a binary blob, with size in bytes. It translates to BLOB in SQL. Example: db.Column(db.LargeBinary)
  13. Numeric: This type represents a numeric column for precise values, like monetary values. You can specify the precision and scale. Example: db.Column(db.Numeric(10, 2))

These are just a few of the available types. Each type can also take additional arguments to add constraints or alter behavior. For example, you can make a column non-nullable (i.e., it must have a value) by passing nullable=False.

Remember that the types are translated into SQL types according to the dialect of the database you’re using, so there may be slight variations depending on whether you’re using MySQL, PostgreSQL, SQLite, etc.

Let’s create a model for a fictional “Product” table for an e-commerce application. This will allow us to demonstrate a variety of data types in a practical context:

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Enum
import enum

db = SQLAlchemy()

class ProductCategory(enum.Enum):
    ELECTRONICS = 1
    CLOTHING = 2
    HOME = 3
    BOOKS = 4
    SPORTS = 5

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    price = db.Column(db.Numeric(10, 2), nullable=False)
    quantity = db.Column(db.Integer, nullable=False)
    is_available = db.Column(db.Boolean, default=True)
    added_on = db.Column(db.DateTime, default=db.func.current_timestamp())
    category = db.Column(db.Enum(ProductCategory), nullable=False)
    image = db.Column(db.LargeBinary)

    def __repr__(self):
        return f'<Product {self.name}>'

In this example:

  1. id is an integer column used as the primary key.
  2. name is a string column with a maximum length of 100 characters. It is a required field (i.e., nullable=False).
  3. description is a text column, which can store a longer string compared to the String type.
  4. price is a numeric column, which is ideal for storing precise decimal values like monetary amounts. It is a required field.
  5. quantity is an integer column, also a required field.
  6. is_available is a boolean column with a default value of True.
  7. added_on is a datetime column that defaults to the current timestamp when a product is added.
  8. category is an enum column, which means it can only take one of a limited set of values defined in the ProductCategory enum. It’s also a required field.
  9. image is a large binary column, suitable for storing binary data like image files.

This Product model represents a table in the SQL database, where each column of the table is represented by an instance variable. The type of each column is determined by the SQLAlchemy type given as the first argument to db.Column. Each instance of the Product class corresponds to a row in the Product table.

Establishing Relationships Between Models

In SQLAlchemy, you can represent relationships between tables in the database as relationships between models. There are three main types of relationships that you can use: one-to-many, many-to-one, and many-to-many.

  1. One-to-Many: A one-to-many relationship can be marked by using db.relationship in your model. This type of relationship is like a “contains” relationship. For example, In an e-commerce application, a single Supplier can supply many Products, but each Product is supplied by one Supplier. Here’s how you might define that:
class Supplier(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    products = db.relationship('Product', backref='supplier', lazy=True)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    supplier_id = db.Column(db.Integer, db.ForeignKey('supplier.id'), nullable=False)

In this example, db.relationship in Supplier sets up a one-to-many relationship with Product. backref creates a reverse relationship where a Product can access its Supplier.

  1. Many-to-One: This is essentially the other side of a one-to-many relationship. In the example above, a Product has a many-to-one relationship with a Supplier. The supplier_id field on the Product model establishes the many-to-one relationship.
  2. Many-to-Many: A many-to-many relationship exists when multiple records in a table are associated with multiple records in another table. For example, a Product can belong to several Categorys and a Category can have several Products. Here’s how you might represent that:
# this is the auxiliary table for the many-to-many relationship
product_categories = db.Table('product_categories',
    db.Column('product_id', db.Integer, db.ForeignKey('product.id'), primary_key=True),
    db.Column('category_id', db.Integer, db.ForeignKey('category.id'), primary_key=True)
)

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    products = db.relationship('Product', secondary=product_categories, backref=db.backref('categories', lazy=True))

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    supplier_id = db.Column(db.Integer, db.ForeignKey('supplier.id'), nullable=False)

In this example, db.relationship in Category sets up a many-to-many relationship with Product through the product_categories auxiliary table. backref creates a reverse relationship where a Product can access its Categorys.

In all the relationships, the lazy parameter determines how the data should be loaded. lazy=True means that SQLAlchemy will load the data as necessary in one go. This is known as lazy loading.

Lazy Argument

The lazy argument in SQLAlchemy determines when SQLAlchemy should load data from the database. Here are the possible options:

  1. 'select' or True: This is the default option and loads the data as necessary in one go. This is known as lazy loading.
  2. 'joined' or False: This option will load the relationship in the same query as the parent using a JOIN statement.
  3. 'subquery': Works like ‘joined’ but instead SQLAlchemy will use a subquery.
  4. 'dynamic': This option returns a Query object instead of loading the items. This is useful for relationships with many items because you can apply additional query parameters to it, like limits and offsets.

So, if you set lazy=False (which is equivalent to lazy='joined'), SQLAlchemy will load the data in the same query as the parent using a JOIN statement. This can sometimes be more efficient as it reduces the number of database queries, but it can also be slower if you’re loading a lot of related data that you might not need. It’s important to understand these trade-offs and choose the best option based on the specifics of your application.

Backref

In SQLAlchemy, backref is a convenient feature which allows us to create a new property on the “many” side of a one-to-many relationship. In other words, it creates a back reference from the table being related to the table establishing the relationship.

For instance, if we have a Supplier and Product relationship, where one Supplier can supply many Products, we can use a backref to easily get the Supplier of a Product.

In the Supplier-product model 1:

backref='supplier' in the Supplier model creates a supplier attribute in the Product model. This attribute can be used to easily access the Supplier of a Product. For instance, if you have a Product instance product, you can get its supplier by simply accessing product.supplier.

The benefit of using backref is that it not only adds a supplier property to the Product model to get the Supplier, but it also adds a products property to the Supplier model to get a list of Product instances. This means you can get all products of a supplier using supplier.products.

In summary, backref creates a new virtual column on the related model, allowing for simple access to the related object(s) in your database.

In the next post, we will explore how to perform the basic operations that make up the backbone of any application: Creating, Reading, Updating, and Deleting (CRUD) records.

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *