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 defined 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:
- 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.
Example. Any application with user functionality will need a user table. We will need to store the username, email id, password (hashed), the creation date, status of account verification etc. For starters, we need a table that looks like the one below
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))
is_verified = db.Column(db.Boolean, default = False)
created_on = db.Column(db.Date, default = datetime.utcnow(), nullable=False)
def __repr__(self):
return f"<User {self.username}>"
In this code, id, username, email, password, is_verified, and created_on 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.
For our new User class we have not defined a __init__ method. That’s because SQLAlchemy adds an implicit constructor to all model classes which accepts keyword arguments for all its columns and relationships.
When you define a model in Flask-SQLAlchemy and run your application, Flask-SQLAlchemy generates SQL statements like the one below to create the corresponding tables in the database. This SQL statement represents the same schema as our Flask-SQLAlchemy User model.
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
);
The SQLAlchemy datatypes are mapped to the SQL datatypes.
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:
| SQLAlchemy Datatypes | Translated SQL Datatype |
|---|---|
| Integer | an integer. Translates to INT in SQL. Example: db.Column(db.Integer) |
| String(size) | a string with a maximum length (optional in some databases, e.g. PostgreSQL). It translates to VARCHAR(size) in SQL. Example: db.Column(db.String(50)) |
| Text | some longer unicode text without a maximum length. It translates to TEXT in SQL. Example: db.Column(db.Text) |
| DateTime | date and time expressed as Python <a href="https://docs.python.org/3/library/datetime.html#datetime.datetime">datetime</a> object. It translates to DATETIME in SQL. Example: db.Column(db.DateTime) |
| Date | date expressed as Python Date object. It translates to DATE in SQL. Example: db.Column(db.Date) |
| Float | stores a floating point value |
| Boolean | This type represents a boolean (True or False) value and translates to BOOLEAN in SQL. Example: db.Column(db.Boolean) |
| PickleType | stores a pickled Python object |
| LargeBinary | stores large arbitrary binary data. It translates to BLOB in SQL. Example: db.Column(db.LargeBinary) |
| Enum | This type represents a list of string-based options. It translates to ENUM in SQL. Example: db.Column(db.Enum('option1', 'option2')) |
| Binary | This type represents a binary blob. It translates to BLOB in SQL. Example: db.Column(db.Binary) |
| 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. You can make a column like email unique = True so that there are no duplicate emails.
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.
Establishing Relationships Between Models
In the real world, we obviously need more tables to represent our data and establish the relationship between them. For example, in an Ecommerce application, there are many users, with their different orders, and each order filled with many products. Once we map out the tables we required, we also need to map out the relationship as exists in the real world(mostly).
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.
There are three main types of relationships that you can use: one-to-many, many-to-one, and many-to-many.
A. 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, if you’re building a blog website, one Author can write many Posts but each Post is written by only one Author. Let’s extend our Ecommerce application, as an example for the different types of relationships.
In the 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. “<strong>backref</strong>” creates a reverse relationship where a Product can access its Supplier.
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, in the Supplier and Product relationship, where one Supplier can supply many Products, we use the backref to easily get the Supplier of a Product.
In this case, 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.
You can skip this part about Foreign key if you are conversant with SQL or relational databases.
A Detour: Foreign Key
In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In simpler terms, a foreign key in one table points to a primary key in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
Foreign keys are used to enforce referential integrity within your database. They help establish relationships between tables and prevent actions that would destroy links between tables.
In this case, supplier_id in the Product table is a foreign key that points to the id field in the Supplier table. This creates a relationship where one Supplier can supply many Products. Here’s what’s going on:
- db.ForeignKey(‘supplier.id’) in the
Productmodel is definingsupplier_idas a foreign key that references theidfield in theSuppliertable. - db.relationship(‘Product’, backref=’supplier’, lazy=True) in the
Suppliermodel is then using that foreign key to create a relationship where eachSupplierobject can access its relatedProductobjects through theproductsattribute.
This relationship means that if you have a Supplier instance supplier, you can get all of its products with supplier.products. Similarly, if you have a Product instance product, you can get its supplier with product.supplier.
By enforcing referential integrity, foreign keys ensure that the database remains consistent. For example, you can’t create a product with a supplier_id that doesn’t exist in the Supplier table, and you can’t delete a supplier from the Supplier table if there are products in the Product table that reference its id.
B. 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.
C. 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 Categories 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 Categories.
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. Here are the possible options:
'select'orTrue: This is the default option and loads the data as necessary in one go. This is known as lazy loading.'joined'orFalse: This option will load the relationship in the same query as the parent using a JOIN statement.'subquery': Works like ‘joined’ but instead SQLAlchemy will use a subquery.'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.
That’s it for today. Next we will look at performing basic CRUD operations with flask-sqlalchemy.