For information submitted by users, including accounts, articles, etc., you need to be able to save the data

There are three ways to persist storage:

• Files: shelves (a combination of pickle and DBM), etc., which provide a dictionary-like object interface
• Relational Database (SQL)
• Non-relational Database (NoSQL)
• Other

Usually a database is used to store information and query it for information

SQL, Relational Database

Relational databases store data in tables, which are implemented in programs using Python classes.For example, there may be tables customers, products, and orders in the order manager's database.

The number of columns in a table is fixed and the number of rows is variable.

Column defines the data attributes of the entity represented by the table.For example, the customers table may have columns such as name, address, phone, and so on.Rows in a table define the actual data for each column.

There is a special column in the table, called the primary key, whose value is the unique identifier of the rows in the table.Tables can also have columns called foreign keys that reference the primary key of a row in the same or different tables.This association between rows is called a relationship, which is the basis of a relational database model.

From this example, you can see that relational databases store data efficiently and avoid duplication.Renaming user roles in this database is also easy because role names only appear in one place.Once the role name is modified in the roles table, all users referencing the role through the role_id will immediately see the update.

On the other hand, storing data in separate tables is still complex.A minor problem with generating a list of users with roles is that before that, users and user roles are read from two tables and joined together.The relational database engine provides the necessary support for join operations.

Store data separately in multiple tables and create joins through foreign keys.Reduce data duplication.Queries are cumbersome, but easy to modify.

Relational databases are:

• MySQL

• PostgreSQL

• SQLite

Specifically, it is a database stored in a single file on a hard disk.Save all data for each database in a single file.Python comes with it.However, only one connection can be accessed at a time.Therefore, it is strongly recommended not to be used in a web application in a production environment.

etc.

NoSQL, non-relational database

• Key-Value Pairs

Key-value pair data storage is a data structure based on hash mapping.

• Document-oriented

MongoDB
Riak
Apache CouchDB

Accessing relational databases

Python can access a relational database through a database interface program (DB-API) or object-relational mapping (ORM).

DB-API

Python programs can connect to the target database through API s and read data using SQL statements

connect(), create a connection

close(), close the database connection

commit(), commit

rollback(), rollback/cancel the current

The official specification for Python PEP 0249

MySQL and PostgreSQL are the most common open source databases for storing Python web application data.

• MySQL

The only MySQL API: MySQLdb

• PostgreSQL

There are at least three interface programs

• SQLite

sqlite3

Basic SQL statement

• Create a database, assign database permissions to one/all users
  CREATT DATABASE test;
  GRANT ALL ON test.* to user;

• Select the database to use
  USE test;

• Delete database
  DROP DATABASE test;

• Create Table
  CREAT TABLE users;

• Delete Table
  DROP TABLE users;

• Insert Row
  INSERT INTO users VALUES();

• Update row
  UPDATE users SET XXX;

• Delete rows
  DELETE FROM users ;

ORM

To access a database using DB-API, you need to understand the SQL language and be able to write SQL statements. If you do not want to understand SQL and want to use a relational database, you can use ORM

Object Relational Mapping (ORM)

An ORM with a Database at one end and a Python DataObject object at the other end.With ORM, you can operate on databases by manipulating Python objects without writing SQL statements directly.ORM automatically converts Python code into the corresponding SQL statement.The remaining operations, including data checking, generating SQL statements, transaction control, rollback, and so on, are left to the ORM framework to complete.

DataObject s can be generated by Query execution or by user creation.

Of course, ORM can also execute original SQL statements to perform some complex/special operations.

Find all users with a role of "User":

>>> user_role = Role(name='User')
>>> User.query.filter_by(role=user_role).all()  # 
 [<User u'susan'>, <User u'david'>]

To view the native SQL query statement generated by SQLAlchemy for a query, simply `Convert the query object to a string':

>>> str(User.query.filter_by(role=user_role))
'SELECT users.id AS users_id, users.username AS users_username, users.role_id AS users_role_id FROM users WHERE :param_1 = users.role_id'

The database abstracts many of the functions of SQL as Python objects, so that you can operate on the database without writing SQL.

Define tables in a database in Flask using Python classes

from flask.ext.sqlalchemy import SQLAlchemy # Import SQLAlchemy from flask extension

db = SQLAlchemy()

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.Text) # Blog body, unlimited length
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow) # Time to post
    body_html = db.Column(db.Text) # Store converted HTML Code
    author_id = db.Column(db.Integer, db.ForeignKey('users.id')) # Foreign Key Usage ForeignKey，point User Table id

    comments = db.relationship('Comment', backref='post', lazy='dynamic')

ORM is similar to a standard database interface, but many of the work is handled by ORM instead of directly using the interface.

ORM module for Python: SQLAlchemy, etc.

Some large web development tools/frameworks have their own ORM components.

import os
basedir = os.path.abspath(os.path.dirname(__file__)) # Project Root Directory

SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'app.db') # Path, file name of database file
    # print SQLALCHEMY_DATABASE_URI
    # sqlite:////Users/chao/Desktop/projects/flask/flask_blog/app.db
SQLALCHEMY_MIGRATE_REPO = os.path.join(basedir, 'db_repository') # Folder, save `SQLAlchemy-migrate` data file, also known as migration policy file
    # print SQLALCHEMY_MIGRATE_REPO
    # /Users/chao/Desktop/projects/flask/flask_blog/db_repository

Configuration of the Database Engine

hello.py

from flask.ext.sqlalchemy import SQLAlchemy # Import SQLAlchemy from flask extension

db = SQLAlchemy()  # Create Database Instance`db`

How to choose

There are several factors to consider.

• Usability
  If you compare API directly with ORM, it is clear that the latter wins.Object-Relational Mapper (ORM) converts high-level object-oriented operations into low-level database instructions without the user's knowledge.
• performance
  ORM can be costly in converting object business to database business.In most cases, this reduction in performance is negligible, but not necessarily the same.Ordinarily, ORM improves productivity much more than this slight performance degradation, so the reason for performance degradation is not enough to persuade users to abandon ORM altogether.The real key is how to choose an abstraction layer that directly operates on the lower level of the database in case specific operations require direct optimization using the database's native instructions.
• Portability
  When choosing a database, you must consider whether it can be used on your development and production platforms.For example, if you plan to leverage cloud platform hosting programs, you need to know what databases this cloud service provides for you to choose from.Portability is also for ORM.Although some ORMs only provide an abstraction layer for one database engine, others may make a higher level of abstraction, supporting different database engines and all using the same object-oriented interface.A good example is SQLAlchemy ORM, which supports many relational database engines, including popular MySQL, Postgres, and SQLite.
• FLask Integration
  When choosing frameworks, you don't have to choose frameworks that already have Flask integrated, but choosing these frameworks can save you time writing integration code.Using a framework that integrates Flask simplifies configuration and operation, so extensions specifically developed for Flask are your preferred option.

Based on these factors, the database framework chosen for this book is Flask-SQLAlchemy , this Flask extension wrapper SQLAlchemy Frame.

database model

Define Model

In ORM, a model is generally a Python class that represents a table in the database, and the attributes in the class correspond to columns in the database table.

The database instance created by Flask-SQLAlchemy provides the model with a base class, db.Model, and a series of auxiliary classes and functions that can be used to define the structure of the model/table.

The following example defines two tables, one for user roles and one for user information

hello.py

class Role(db.Model):
        __tablename__ = 'roles'
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(64), unique=True)

        def __repr__(self):
            return '<Role %r>' % self.name

class User(db.Model):
        __tablename__ = 'users'
        id = db.Column(db.Integer, primary_key=True)
        username = db.Column(db.String(64), unique=True, index=True)

        def __repr__(self):
            return '<User %r>' % self.username

The class variable u tablename_u defines the table name used in the database.If u tablename_u is not defined, Flask-SQLAlchemy uses a default name, but the default table name does not follow the Convention of naming in plural form (plus s), so it is best for us to specify the table name ourselves.

The remaining class variables are columns of the model's properties/tables, defined as instances of the db.Column class.

The first argument to the db.Column class constructor is the type of the database table column/model property.

More types

The remaining parameters in db.Column specify the configuration options for the properties.

Other configuration options

Although not mandatory, both models define the u repr() u method, returning a readable string representation model that can be used in debugging and testing.

Database Operation

The best way to learn how to use a model is to actually work in a Python shell.

• Create Table

First, let Flask-SQLAlchemy create the database from the model class.The method is to use the db.create_all() function:

      (venv) $ python hello.py shell  # Enter the Python shell
      >>> from hello import db   # Import the created database instance from `hello.py`
      >>> db.create_all()

If you look at the program directory, a new file named app.db is created.The name of this SQLite database file is specified in the configuration.If the database table already exists in the database, db.create_all() will not recreate or update the table.This feature is inconvenient if changes are made in the model and you want to apply the changes to an existing database.

The rough way to update an existing database table is to delete the old table and recreate it:

      >>> db.drop_all()
      >>> db.create_all()

Unfortunately, this method has a side effect that we don't want to see, it destroys all the data already in the database.At the end, a method called database migration is described for updating databases.

• Insert Row

>>> from hello import Role, User
     >>> admin_role = Role(name='Admin')
     >>> mod_role = Role(name='Moderator')
     >>> user_role = Role(name='User')
     >>> user_john = User(username='john', role=admin_role)
     >>> user_susan = User(username='susan', role=user_role)
     >>> user_david = User(username='david', role=user_role)

The constructor of the model accepts the initial value of the model property specified with the keyword parameter.Note that the role attribute is also available, and although it is not a real database column, it is a high-level representation of a one-to-many relationship.The id property of these newly created objects is not explicitly set because the primary key is managed by Flask-SQLAlchemy.These objects now exist only in Python and have not been written to the database.So the id has not been assigned:

     >>> print(admin_role.id)
     None
     >>> print(mod_role.id)
     None
     >>> print(user_role.id)
     None

Changes made to the database through database session management are represented by db.session in Flask-SQLAlchemy.Before you are ready to write objects to the database, add them to the session:

     >>> db.session.add(admin_role)
     >>> db.session.add(mod_role)
     >>> db.session.add(user_role)
     >>> db.session.add(user_john)
     >>> db.session.add(user_susan)
     >>> db.session.add(user_david)

Or abbreviate it as:

     >>> db.session.add_all([admin_role, mod_role, user_role,
     ...     user_john, user_susan, user_david])

To write objects to the database, we call the commit() method to commit the session:

>>> db.session.commit()

Look at the id attributes again, and now they have been assigned:

>>> print(admin_role.id)
1
>>> print(mod_role.id)
2
>>> print(user_role.id)
3

Database sessions ensure database consistency.The commit operation atomically writes all the objects in the session to the database.If an error occurs during the writing session, the entire session will fail.If you always submit changes in your session, you can avoid database inconsistencies caused by partial updates.Consistency: The data in the database is not consistent with the data actually stored.

• Modify line

Calling the add() method on a database session can also update the model.We continue to work in the previous shell session

The following example renames the Admin role to Administrator:

     >>> admin_role.name = 'Administrator'
     >>> db.session.add(admin_role)
     >>> db.session.commit()

• Delete rows

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

     >>> db.session.delete(mod_role)
     >>> db.session.commit()

Note that deletions, like inserts and updates, do not occur until the database session is committed.

• Query Rows

Flask-SQLAlchemy provides query objects for each model class.The most basic model query is to retrieve all the records in the corresponding table:


>>> Role.query.all()
      [<Role u'Administrator'>, <Role u'User'>]
>>> User.query.all()
      [<User u'john'>, <User u'susan'>, <User u'david'>]

Filters allow you to configure query objects for more accurate database queries.The following example finds all users with a role of "User":

>>> User.query.filter_by(role=user_role).all()  # user_role = Role(name='User'), role=user_role
 [<User u'susan'>, <User u'david'>]

To view the native SQL query statement generated by SQLAlchemy for a query, simply convert the query object to a string:

>>> str(User.query.filter_by(role=user_role))
'SELECT users.id AS users_id, users.username AS users_username, users.role_id AS users_role_id FROM users WHERE :param_1 = users.role_id'

If you exit the shell session, the objects created in the previous examples do not exist as Python objects, but as rows in their respective database tables.If you open a new shell session, read the rows from the database and recreate the Python object.

The following example initiates a query to load a user role named "User":

>>> user_role = Role.query.filter_by(name='User').first()

Filters such as filter_by() are called on a query object to return a more precise query object.Multiple filters can be called together until the desired result is obtained.

Common filters that can be called on query objects.

After applying the specified filter to the query, execute the query by calling all() to return the results as a list.In addition to all(), there are other ways to trigger query execution.

Common Query Execution Functions

Relationships and queries are handled similarly.

For a complete list see SQLAlchemy query

The following example queries a one-to-many relationship between roles and users from both ends of the relationship:

     >>> users = user_role.users
     >>> users
     [<User u'susan'>, <User u'david'>]
     >>> users[0].role
     <Role u'User'>

The user_role.users query in this example has a minor problem.When the user_role.users expression is executed, the implicit query calls all() to return a list of users.The query object is hidden, so you cannot specify a more precise query filter.For this particular example, it might be better to return an alphabetical list of users.

In examples 5-4, we modified the relationship's settings to include the lazy ='dynamic'parameter, which prevents automatic query execution.

     class Role(db.Model):
         # ...
         users = db.relationship('User', backref='role', lazy='dynamic')
         # ...

After configuring the relationship this way, user_role.users returns a query that has not been executed, so you can add a filter to it:

     >>> user_role.users.order_by(User.username).all()
     [<User u'david'>, <User u'susan'>]
     >>> user_role.users.count()
     2

Operating databases in view functions

Once you've practiced in the Python shell, you can work directly with the database in the view function.

@app.route('/', methods=['GET', 'POST'])
def index():
    form = NameForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.name.data).first()
        if user is None:
            user = User(username = form.name.data)
            db.session.add(user)   # Not submitted??There is an option in the configuration object, the SQLALCHEMY_COMMIT_ON_TEARDOWN key, to set it to True, `Changes in the database will be submitted automatically after each request ends`
            session['known'] = False
        else:
            session['known'] = True
        session['name'] = form.name.data
        form.name.data = ''
        return redirect(url_for('index'))
return render_template('index.html', form = form, name = session.get('name'), known = session.get('known', False))

After submitting the form, the program uses the filter_by() query filter to find the submitted name in the database.The variable known is written to the user session, so after redirection, you can pass the data to the template to display a custom welcome message.Note that for the program to work properly, you must create database tables in the Python shell as described earlier.

Corresponding new version of template.This template uses the known parameter to add a second line to the welcome message, displaying different content to known and new users.

{% extends "base.html" %}
{% import "bootstrap/wtf.html" as wtf %}
{% block title %}Flasky{% endblock %}
{% block page_content %} 

<div class="page-header">
<h1>Hello, {% if name %}{{ name }}{% else %}Stranger{% endif %}!</h1> 
{% if not known %}
<p>Pleased to meet you!</p>
{% else %}
    <p>Happy to see you again!</p>
{% endif %} 
</div>

{{ wtf.quick_form(form) }}
{% endblock %}

Object Relations Tutorial

Official SQLAlchemy Documentation

Establish a relationship

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     password = Column(String)
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', password='%s')>" % ( self.name, self.fullname, self.password)

Let's consider the second table associated with User, which can be mapped and queried.Users can store any number of e-mail address associated user names.This means a one-to-many association from users to a new table Addresses that stores e-mail addresses.We use declarations in Address to define the mapping of this table to User:

>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship, backref

>>> class Address(Base):
...     __tablename__ = 'addresses'
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('users.id'))
...
...     user = relationship("User", backref=backref('addresses', order_by=id))
...
...     def __repr__(self):
...         return "<Address(email_address='%s')>" % self.email_address

The above class uses the ForeignKey function, which is a directive applied to Column that indicates that the value of this column should hold the value of a remote column with the specified name.This is a core feature of relational databases, glue, which turns irrelevant tables into collections with rich overlapping relationships.The ForeignKey above indicates that the value of the Addresses.user_id column should be equal to the value in the users.id column, which is the primary key of the users.

The second function, called relationship(), tells ORM that the Address class itself should be linked to the User class using the attribute Address.user.Relationship () uses a foreign key relationship between two tables to determine the nature of this link, in this example, to determine that Address.user will be the many-to-one, many-to-many side.One of the parameters of relationship () is a subfunction of relationship () called backref(), which in turn provides detailed information. That is, add a collection of Address objects corresponding to User to users and save it in User.addresses.The reverse of a many-to-one relationship is always a one-to-many relationship.A complete available relationship() configuration directory is available in Basic Relationship Patterns.

Two complementary relationships, Address.user and User.addresses, are called two-way relationships and are a key feature of SQLAlchemy ORM.Subsection Linking Relationships with Backref The Backref feature is discussed in detail.

In the relationship() parameter, the associated remote class can be specified by a string if the declaring system is in use.In the User class of the example above, once all mappings are completed, these strings are considered Python expressions to generate actual parameters.Allowed names in this evaluation include, among other things, the basis of the declaration that all class names have been created.

Here's an example of using User instead of Address to create the same address/user bidirectional relationship:

class User(Base):
    # ....
    addresses = relationship("Address", order_by="Address.id", backref="user")

Get more details about the parameter style through relationship().

Do you know?

• Most, though not all, relational databases with foreign key constraints can only link to a primary key column or to a column with a unique constraint.
• A foreign key constraint that references multiple primary key columns and has multiple columns of its own is called a composite foreign key.It can also reference a subset of these columns.
• Foreign key columns can be updated automatically to accommodate changes to referenced columns or rows.This is called cascading reference behavior and is a built-in function of a relational database.
• Foreign keys can reference their own tables.This is a foreign key called a "self-reference".
• More information about foreign keys Foreign Key - Wikipedia

Use associated objects

Now, when we create a User object, a blank Addresses collection appears.There are many types of collections, such as sets and dictionaries, which are possible here (more information) Customizing Collection Access ), but by default, a collection is a Python list.

>>> jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
>>> jack.addresses
[]

We are free to add Address objects to User objects.In this example, we assign a complete list directly:

>>> jack.addresses = [
...                 Address(email_address='jack@google.com'),
...                 Address(email_address='j25@yahoo.com')]

When using a two-way relationship, elements are added on one side and automatically appear on the other.This behavior occurs based on property change events, and is judged by Python, without the need for any SQL statements:

>>> jack.addresses[1]
<Address(email_address='j25@yahoo.com')>

>>> jack.addresses[1].user
<User(name='jack', fullname='Jack Bean', password='gjffdd')>

We add Jack Bean to the database session and submit it to the database.Both the jack and two Address members in the corresponding addresses collection are added to the session at once, which uses a process called cascading:

>>> session.add(jack)
>>> session.commit()

INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('jack', 'Jack Bean', 'gjffdd')
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('jack@google.com', 5)
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('j25@yahoo.com', 5)
COMMIT

Query jack, Jack Jack is back.Jack's address is not mentioned in SQL:

>>> jack = session.query(User).filter_by(name='jack').one() 
>>> jack
<User(name='jack', fullname='Jack Bean', password='gjffdd')>

BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name = ?
('jack',)

Let's look at the addresses collection.Watch SQL:

>>> jack.addresses 
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

SELECT addresses.id AS addresses_id,
        addresses.email_address AS
        addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id ORDER BY addresses.id
(5,)

When we accessed the addresses collection, SQL suddenly mentioned it.This is an example of delayed loading.The addresses collection is now loaded and behaves like a normal list.We will discuss how to optimize the loading of this collection.

Basic Relationship Patterns

• One-to-many

A parent adds a foreign key to the child table to hold the value of the corresponding parent.id, referencing the parent. relationship() Specify in parent, reference/save Associated entries in a batch of child tables.

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

In one-to-many mode, establish a two-way relationship where ForeignKey resides in many, specifying the backref option in relationship:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", backref="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

The table child will therefore get a parent attribute whose value is the entry in the corresponding parent table.

• Many-to-one

Multiple parents to a child.More than one foreign key is added to the parent table to hold the value of child.id.relationship() is declared in parent, creating a new attribute child, which holds the entries of the associated child table.

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)

The bidirectional behavior is achieved by setting the backref optional parameter with the value "parents" in the relationship.Generate a collection in the Child class to collect the corresponding entries in the parent table.

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child", backref="parents")

• One-on-one

One-to-one is essentially a two-way relationship in which a number of attributes are set on both sides.To achieve this goal, set a set on the many side of the limited number of properties uselist=False substitution relationship.

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child = relationship("Child", uselist=False, backref="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

Or convert a one-to-many reference to one-to-one and use the backref() function to provide the uselist=False parameter to the reverse end:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child", backref=backref("parent", uselist=False))

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)

• Up to

Many-to-many relationships require adding a relationship Table between two classes.The relationship Table is identified by the relationship() secondary parameter.Usually, tables use MetaData object association announcements from base classes, so ForeignKey's declarations can locate tables at the far end of a link.

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", secondary=association_table)

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

For a two-way relationship, both sides of the relationship contain a set.The backref keyword will automatically use the same secondary parameter for reverse relationships:

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", secondary=association_table, backref="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

The secondary parameter of relationship() also accepts a call that returns the final parameter and is evaluated only when the map is first used.With this, we can define association_table later, as long as all modules can be called after initialization is complete:

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", secondary=lambda: association_table, backref="parents")

By using an extended declaration, the traditional string name of a table is accepted, and matching table names are stored in Base.metadata.tables:

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", secondary="association", backref="parents")

Use Backref Link Relationships

The backref keyword parameter What does it actually do?

Let's start with a standard user and address context:

from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address", backref="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

The above configuration establishes a collection of associated Addresses objects/entries in User named User.addresses.It also establishes a user property in Address to hold associated User entries.

In fact, the backref keyword is just a common shortcut for placing the second relationship() in Address at the other end of the relationship, setting up an event listener on both sides, and mirroring attribute operations on both sides of the relationship.The above configuration is equivalent to:

from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address", back_populates="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship("User", back_populates="addresses")

Above, we explicitly added a relationship named User to Address.On both sides of the relationship, the back_populates parameter tells each relationship the associated peer information, indicating that they should establish "two-way" behavior with each other.The primary purpose of this configuration is to add event handlers to relationships, "When an add or set event occurs, set the incoming property to use this property name."This behavior is illustrated below.From a User and an Address Start of instance.The addresses collection is empty, and the user property is None:

>>> u1 = User()
>>> a1 = Address()
>>> u1.addresses
[]
>>> print a1.user
None

However, once Address is added to the u1.addresses collection, all collection and scalar properties are populated:

>>> u1.addresses.append(a1)
>>> u1.addresses
[<__main__.Address object at 0x12a6ed0>]
>>> a1.user
<__main__.User object at 0x12a6590>

This behavior is of course the same in the reverse delete operation, as is the case with both sides.For example, when the user property is set to None again, the Address object is deleted from the reverse collection:

>>> a1.user = None
>>> u1.addresses
[]

Operations on addresses collections and user properties occur entirely in Python without any interaction with the SQL database.If you don't, you need to update the data to the database and reload it after a commit or expiration operation to see the correct state on both sides.The advantage of the backref/back_populates behavior is that common two-way operations reflect the correct state and do not require a database round trip.

Remember that when you use the backref keyword on one side of a relationship, it is the same as when you use back_populates on both sides of a relationship separately.

Backref parameter

We have established the backref keyword as a shortcut to building two separate relationship() structures to reference each other.Part of the behavior of this shortcut is to determine that the configuration parameters applied to relationship() will also be applied in another direction -- that is, those parameters describe the relationship at the schema level and are unlikely to be different in the opposite direction.Typically, a many-to-many relationship has a secondary parameter, or one-to-many or one-to-many has a primaryjoin parameter.For example, if we restrict the Adress object in the list to start with tony:

from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address",
                    primaryjoin="and_(User.id==Address.user_id, "
                        "Address.email.startswith('tony'))",
                    backref="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

We can observe that by examining the resulting content, the jion condition applies to both sides of the relationship:

>>> print User.addresses.property.primaryjoin
"user".id = address.user_id AND address.email LIKE :email_1 || '%%'
>>>
>>> print Address.user.property.primaryjoin
"user".id = address.user_id AND address.email LIKE :email_1 || '%%'
>>>

Reused parameters should all do the "right thing" - they only use the appropriate parameters, and in many-to-many relationships, primaryjoin and secondaryjoin will be used in reverse for the other end.

Most often, we want to specify the parameters used by the other end of the backref.This includes parameters for relationship(), such as lazy,remote_side, cascade, cascade_backrefs.In this case, we use the backref() function instead of the string:

# <other imports>
from sqlalchemy.orm import backref

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address",
                    backref=backref("user", lazy="joined"))

Above, we only put a lazy="joined" parameter in Address.user, indicating that when a query against Address occurs, the join of a User instance should automatically populate the user attribute of each returned Address.The backref() function is formatted as a parameter that we interpret as an additional parameter applied to the new relationship it creates.

Database Migration

During the process of developing your program, you will find that sometimes you need to modify the database model, such as adding tables, adding columns, and updating the database after the modification, so you need to migrate the database

A better way to update tables is to use the database migration framework.Source version control tools track changes in source files. Similarly, the database migration framework tracks changes in database schemas, and incrementally applies changes to the database.

The main developer for SQLAlchemy wrote a migration framework called Alembic .In addition to using Alembic directly, Flask programs can also be used Flask-Migrate Extension.This extension wraps Alembic lightweight and integrates it into Flask-Script, all with Flask-Script commands.

• Create a migration warehouse

install Flask-Migrate:

(venv) $ pip install flask-migrate

Initialize and configure Flask-Migrate

from flask.ext.migrate import Migrate, MigrateCommand
# ...
migrate = Migrate(app, db)  # Initialization
manager.add_command('db', MigrateCommand) # On the command line, call `MigrateCommand'with `db`

➜  flask_blog git:(master) ✗ python run.py
usage: run.py [-?] {shell,db,runserver} ...

positional arguments:
  {shell,db,runserver}
    shell               Runs a Python shell inside Flask application context.
    db                  Perform database migrations
    runserver           Runs the Flask development server i.e. app.run()

optional arguments:
  -?, --help            show this help message and exit

To export database migration commands, Flask-Migrate provides a MigrateCommand class that can be attached to the manager object of Flask-Script.In this example, the MigrateCommand class is attached using the db command.

Before maintaining the database migration, use the init subcommand to create the migration repository:

(venv) $ python hello.py db init    # A `migrations'folder will be added to the application.Files in folders need to be versioned with other source files.➜  

flask_blog git:(master) ✗ python run.py db init
  Creating directory /Users/chao/Desktop/projects/flask/flask_blog/migrations ... done
  Creating directory /Users/chao/Desktop/projects/flask/flask_blog/migrations/versions ... done
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/alembic.ini ... done
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/env.py ... done
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/env.pyc ... done
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/README ... done
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/script.py.mako ... done
  Please edit configuration/connection/logging settings in '/Users/chao/Desktop/projects/flask/flask_blog/migrations/alembic.ini'
  before proceeding.

This command creates the migrations folder in which all migration scripts are stored.

• Create migration scripts

In Alembic, database migration is represented by a migration script.There are two functions in the script, upgrade() and downgrade().The upgrade() function applies the changes in the migration to the database, and the downgrade() function deletes the changes.Alembic has the ability to add and delete changes, so the database can be reset to any point in the modification history.

Alembic migrations can be created manually using the revision command or automatically using the migrate command.

The manually created migration is just a skeleton, the upgrade() and downgrade() functions are empty, and the developer uses the Operations object instructions provided by Alembic to implement the specific operation.

Automatically created migrations generate the contents of upgrade() and downgrade() functions based on the differences between the model definition and the current state of the database.

The migrate subcommand is used to automatically create migration scripts:

     (venv) $ python hello.py db migrate -m "initial migration" # Generate an initial migration
     INFO  [alembic.migration] Context impl SQLiteImpl.
     INFO  [alembic.migration] Will assume non-transactional DDL.
     INFO  [alembic.autogenerate] Detected added table 'roles'
     INFO  [alembic.autogenerate] Detected added table 'users'
     INFO  [alembic.autogenerate.compare] Detected added index 'ix_users_username' on '['username']'
Generating /home/flask/flasky/migrations/versions/1bc 594146bb5_initial_migration.py...done

➜  flask_blog git:(master) ✗ python run.py db migrate -m 'migration'
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'user'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_user_email' on '['email']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_user_username' on '['username']'
INFO  [alembic.autogenerate.compare] Detected added table 'post'
  Generating /Users/chao/Desktop/projects/flask/flask_blog/migrations/versions/0fb164ef6c11_migration.py ... done

• Update database

After reviewing and correcting the migration script, we can use the db upgrade command to apply the migration to the database:

     (venv) $ python hello.py db upgrade
     INFO  [alembic.migration] Context impl SQLiteImpl.
     INFO  [alembic.migration] Will assume non-transactional DDL.
     INFO  [alembic.migration] Running upgrade None -> 1bc594146bb5, initial migration

➜  flask_blog git:(master) ✗ python run.py db upgrade
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 0fb164ef6c11, migration

For the first migration, it works the same as calling the db.create_all() method.However, in subsequent migrations, the upgrade command can apply changes to the database without affecting the data stored in it.