Tutorials for using Flask-sqlalchemy to manage databases in the Python flask framework

Managing Databases with Flask-sqlalchemy
Flask-sqlalchemy is a Flask extension that simplifies the use of SQLAlchemy in Flask applications. SQLAlchemy is a powerful relational database framework that supports some database backend. Provides advanced ORM and local SQL capabilities for the underlying Access database.
As with other extensions, install Flask-sqlalchemy with PIP:

(venv) $ pip Install Flask-sqlalchemy

In Flask-sqlalchemy, the database is designated as a URL. The table lists the three most popular database engine URL formats:

In these URLs, hostname refers to the server hosting the MySQL service, either locally (localhost) or a remote server. The database server can host multiple databases, so database indicates the names of the databases to be used. The database requires authentication, and username and password are database user credentials.
Note:> SQLite database does not have a service, so hostname, username, and password can default and the database is a disk file name.
The application database URL must be configured in the Sqlalchemy_database_uri key in the Flask configuration object. Another useful option is sqlalchemy_commit_on_teardown, which can be set to true to enable autocommit database changes in each request. Consult the Flask-sqlalchemy documentation for additional configuration options.

From flask.ext.sqlalchemy Import sqlalchemybasedir = Os.path.abspath (Os.path.dirname (__file__)) app = Flask (__name__) app.config[' Sqlalchemy_database_uri ' =\  ' sqlite:///' + os.path.join (basedir, ' data.sqlite ') app.config[' Sqlalchemy_commit_on_teardown '] = Truedb = SQLALCHEMY (APP)

The DB object instantiated by SQLAlchemy represents the database and provides all the functionality to access the Flask-sqlalchemy.

Model definition
A model is a persisted entity that is used by an application. In the context of ORM, a model is usually a Python class with attributes whose properties match the columns of the database table. The Flask-sqlalchemy DB instance provides a base class and a set of auxiliary classes and functions to define its structure.

Class Role (db. Model):  __tablename__ = ' roles '  id = db. Column (db. Integer, primary_key=true)   name = db. Column (db. String (Unique=true),  def __repr__ (self):    return '
 
  
   
  % self.nameclass User (db. Model):  __tablename__ = ' users '  id = db. Column (db. Integer, primary_key=true)  username = db. Column (db. String (Unique=true, index=true) def __repr__ (self):  return '
  
   
    
   % self.username
  
   
 
  

The __TABLENAME__ class variable defines the name of the table in the database. If __tablename__ default flask-sqlalchemy specifies a default table name, but these default names do not follow conventions that use plural names, it is a good idea to explicitly name the table names. The remaining variables are the properties of the model and are defined as db. An instance of the column class.
Passed to DB. The first parameter of the column constructor is the type of the database columns, which is the data type of the model properties. Table 5-2 lists the types of available columns and also the type of Python used in the model.

Most common types of sqlalchemy columns
Db. The remaining parameters of column specify configuration options for each property.

The most common sqlalchemy column options
Note: Flask-sqlalchemy needs to define a primary key column for all models, usually named ID.
The two models contain the REPR () method to show them a readable string, which is not entirely necessary, but is good for debugging and testing.

Relationship
A relational database establishes a connection in a different table by using a relationship. A diagram expresses a simple relationship between a user and a user role. This role and the user are a one-to-many relationship, because a role can belong to more than one user, and a user can only have a single role.
The following model classes show a one-to-many relationship expressed in.

Class Role (db. Model):   # ...  Users = db.relationship (' user ', backref= ' role ') class User (db. Model):   # ...  role_id = db. Column (db. Integer, Db. ForeignKey (' roles.id '))

A relationship connects two rows by using a foreign key. The role_id column that is added to the user model is defined as a foreign key, and the relationship is established. Db. The ForeignKey () parameter roles.id the specified column should be interpreted as the column that holds the ID value in the row of the roles table.
The Users property added to the role model shows the object-oriented view of the relationship. Given an instance of the role class, the user property returns a set of users connected to the role. The first parameter assigned to Db.relationship () indicates the other side of the relationship in the model. If the class is not yet defined, the model can be provided as a string.
Note: Previously encountered in the Segmentdefault problems, and then roughly read the SQLAlchemy source. The column of the ForeignKey class receives three types of parameters, one is "model name. Property name"; one is "table name. Column Name", the last one is not clear, next time try to use.
The Backref parameter of Db.relationship () defines the inverse relationship by adding the role property to the user model. This property can be used instead of role_id to access the role model as an object rather than as a foreign key.
In most cases db.relationship () can locate its own foreign key relationship, but sometimes it is not possible to determine which column is being used as a foreign key. For example, if the user model has two or more columns defined as a foreign key for role, SQLAlchemy will not know which of the two are used. The extra parameter db.relationship () must be used whenever the foreign key configuration is ambiguous. The subscript lists some common configuration options for defining relationships:

Common SQLAlchemy Relationship Options

Recommendation: If you have cloned apps on GitHub, you can now run git checkout 5a to switch to this version of the app.
There are other kinds of relationships in addition to a one-to-many relationship. A one-to-many relationship can be expressed as the first-to-most relationship described earlier, as long as the uselist option in Db.relationship () is set to False, "many" becomes "a". A many-to-one relationship can also be expressed as a a-to-many relationship after a table is reversed, or as a foreign key and a db.relationship () defined on the "many" side. The most complex type of relationship, many-to-many, requires an additional table called the associated table. You will learn many-to-many relationships in the 12th chapter.

Database operations
The best way to learn how to use a model is to use a python shell. The following sections describe the most common database operations.

Create a table

The first thing to do first is to instruct Flask-sqlalchemy to create a database based on the model class. The Db.create_all () function accomplishes these:

(venv) $ python hello.py shell >>> from Hello import db>>> db.create_all ()

If you check the application directory, you will find a new file named Data.sqlite, the SQLite database name is given in the configuration. If the database already exists, the Db.create_all () function does not recreate or update the database tables. This can be very inconvenient when the model is modified and changes need to be applied to an existing database. The brute force solution for updating an existing database table is to first delete the old table:

>>> Db.drop_all () >>> Db.create_all ()

Unfortunately, the undesirable side effect of this approach is to destroy all the data in the old database. The solution to updating the database problem will be introduced at the end of this chapter.

Insert Row

The following example creates a new role and user:

>>> from Hello import role, user>>> admin_role = role (name= ' admin ') >>> mod_role = role (Name= ' M Oderator ') >>> 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 as a keyword parameter. Note that you can even use the role property, even if it is not a true database column, but rather a high-level representation of a one-to-many relationship. The ID property of these new objects is not explicitly set: The primary key is managed by Flask-sqlalchemy. So far, objects exist only in Python, and they have not yet been written to the database. Because their ID values are not yet assigned:

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

The operation to modify the database is managed by the Db.session database session provided by Flask-sqlalchemy. Objects that are ready to be written to the database must be added 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, More concise:

>>> Db.session.add_all ([Admin_role, Mod_role, User_role,...   User_john, User_susan, User_david])

In order to write an object to the database, the session needs to be submitted through its commit () method:

>>> Db.session.commit ()

Check the ID properties again; At this point they are all set up:

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

Note: There is no connection between the Db.session database session and the flask session discussed in chapter fourth. A database session is also called a transaction.
Database sessions are very useful for database consistency. The commit Operation Atomically writes all the objects added to the session to the database. If an error occurs during the write process, the entire session is discarded. If you always submit related changes in a session, you must ensure that you avoid database inconsistencies caused by partial updates.

Note: Database sessions can also be rolled back. If you call Db.session.rollback (), any objects added to the database session will revert to their previous state in the database.
Modify rows

The Add () method in a database session can also be used to update the model. Continue in the same shell session, the following example renames the "Admin" role to "Administrator":

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

Note: However, it seems that we do not use Db.session.add () when doing the update operation, but instead use Db.session.commit () to commit the transaction directly.
Delete Row

The database session also has the Delete () method. The following example removes the "moderator" role from the database:

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

Note that deletion, like inserting an update, is performed after a database session has been committed.

return rows

Flask-sqlalchemy creates a query object for each model class. The most basic query model is to return the entire contents of the corresponding table:

>>> Role.query.all () [
 
  
   
  , 
  
   
    
   ]>>> User.query.all () [
   
    
     
    , 
    
     
      
     , 
     
      
       
      ]
     
      
    
     
   
    
  
   
 
  

Using filters, you can configure query objects to perform more specific database searches. The following example finds all users who are assigned the "user" role:

>>> User.query.filter_by (Role=user_role). All () [
 
  
   
  , 
  
   
    
   ]
  
   
 
  

For a given query, you can also examine the native SQL query generated by SQLAlchemy and 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:p aram_1 = users.role_id '

If you quit the shell session, the objects created in the previous example will not exist as Python objects, but can continue to exist as Row records in their own database tables. If you start a new shell session, you must recreate the Python object from their database rows. The following example executes a query to load a user role named "User".

>>> user_role = Role.query.filter_by (name= ' user '). First ()

The filter, such as filter_by (), is invoked through the query object and returns the extracted query. Multiple filters can be called sequentially until the desired query configuration is complete.

The filters used in some queries are shown below.

When the required filter has all been applied to query, calling all () triggers query execution and returns a set of results, but there are other ways to trigger execution besides all (). Common SQLAlchemy Query Actuators:

The principle of a relationship is similar to a query. The following example queries a one-to-many relationship between roles and users from both sides:

>>> users = user_role.users>>> users[
 
  
   
  , 
  
   
    
   ]>>> users[0].role
   
    
  
   
 
  

There is a small problem with the user_role.users query here. When the user_role.users expression calls all () internally, the list of users is returned by implicit query execution. Because the query object is hidden, it is not possible to extract it further by additional query filters. In this particular case, it may be used to return the list of users in alphabetical order. In the following example, a query configured with a relationship modified by the lazy = ' Dynamic ' parameter is not automatically executed.

app/models.py: Dynamic Relationships

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

By configuring the relationship in this way, the User_roles.user query has not been executed, so you can add a filter to it:

>>> user_role.users.order_by (User.username). All () [
 
  
   
  , 
  
   
    
   ]>>> user_ Role.users.count (2
  )
   
 
  

