Flask DBMS

Model

The __tablename__ class variable defines the name of the table in the database. FlaskSQLAlchemy assigns a default table name if __tablename__ is omitted. Still, those default names do not follow the popular convention of using plurals for table names, so it is best to name tables explicitly. The remaining class variables are the attributes of the model, defined as instances of the DB

Most common SQLAlchemy column types

 Lists some of the available column types, along with the Python types used in the model.

Relationship

Relational databases establish connections between rows in different tables through
the use of relationships.

The role_id column added to the User model is defined as a foreign key, and that establishes the relationship. The 'roles.id' argument to DB.ForeignKey() specifies that the column should be interpreted as having id values from rows in the roles table. The users attribute added to the model Role represents the object-oriented view of the relationship, as seen from the one side. Given an instance of class Role, the users attribute will return the list of users associated with that role (i.e., the many sides). The first argument to db.relationship() indicates what model is on the other side of the relationship. The model class can be provided as a string if the class is defined later in the module.

In most cases, db.relationship() can locate the relationships foreign key on its own, but sometimes it cannot determine what column to use as a foreign key. For example, if the User model had two or more columns defined as Role foreign keys, then SQLAlchemy would not know which one of the two to use. Whenever the foreign key configuration is ambiguous, additional arguments to db.relationship() need to be given

Database Operations

Creating Tables

The db.create_all() function locates all the subclasses of db.Model and creates corresponding tables in the database for them

Inserting Rows

The constructors for models accept initial values for the model attributes as keyword arguments. Note that the role attribute can be used, even though it is not a real database column but a high-level representation of the one-to-many relationship. The id attribute of these new objects is not set explicitly: the primary keys in many databases are managed by the database itself. The objects exist only on the Python side so far; they have not been written to the database yet. 

Modifying Rows

The add() method of the database session can also be used to update models. Continuing in the same shell session, the following example renames the "Admin" role to

"Administrator":

Deleting Rows

The database session also has a delete() method. The following example deletes the "Moderator" role from the database:

Querying Rows

Flask-SQLAlchemy makes a query object available in each model class. The most basic query for a model is triggered with the all() method, which returns the entire contents of the corresponding table: