2. Defining Models and Schema with Flask-SQLAlchemy

Welcome back to our Flask-SQLAlchemy training program! 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.

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)

    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.

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:

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.

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.

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 a simple example:

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.

Here is an example of a model with various data types:

class BlogPost(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    is_published = db.Column(db.Boolean, default=False)

In this BlogPost model, id is an integer that serves as the primary key, title is a string of maximum length 100, content is a text field, created_at is a datetime, and is_published is a boolean.

Establishing Relationships Between Models

Flask-SQLAlchemy also allows you to define relationships between models. This is done using db.relationship, which returns a new property that can do multiple things depending on the relationship.

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.

Establishing Relationships Between Models

The db.relationship in the Author model creates a virtual column that SQLAlchemy will use to load data from the Book model. The backref argument adds a author attribute to the Book instances. This will load the author of a book whenever we access the author attribute.

The lazy argument defines when SQLAlchemy will load the data from the database. A value of True means that SQLAlchemy will load the data as necessary in one go. This is known as lazy loading.

In the Book model, the author_id field is defined as a foreign key. This establishes a link from each book to an author.

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.

And that’s it for now! In the next post, we’ll explore how to perform CRUD operations using Flask-SQLAlchemy. Stay tuned!