Flask--relationship

This article mainly describes how to use the flask to manipulate the database.

The database saves the program data according to certain rules, and the program initiates the query to retrieve the required data. Web programs are most commonly based on relational model databases, which are also called SQL data because they use structured query statements. In recent years, however, document databases and key values have become a popular alternative to databases, both of which are called NoSQL databases.

Most database engines have Python packages, including open source and business packages. Flask does not restrict what type of database packages you use, so you can choose to use MySQL, Postgres, Sqlite,redis, MongoDB, or couchdb to your liking.

If none of this satisfies the requirements, there are some database abstraction layer code packages to choose from, such as SQLAlchemy and Mongoengine. Instead of working with database entities such as tables, documents, or query languages, you can use these abstract packages to work directly with high-level Python objects.

When choosing a framework, you don't necessarily have to choose a framework that already integrates flask, but choosing these frameworks can save you time writing your integration code. With the flask-Integrated Framework to simplify configuration and operation, extensions developed specifically for flask are your first choice.

For the database framework that you chose to use, select Flask-sqlalchemy.

0. Installing Flask-sqlalchemy

install flask-sqlalchemy

1. Configure Flask-sqlalchemy

In Flask-sqlalchemy, the database is specified using a URL.

Mysql:mysql://username:[email protected]/database
Postgres:postgresql://username:[email protected]/database
Sqlite:sqlite:////absolute/path/to/database

In these URLs, hostname represents the host on which the MySQL service resides, either a local host (localhost) or a remote server. Multiple databases can be hosted on the database server, so database represents the name of the databases to use. If the database requires authentication, username and password represent the user name and password for the database.

The SQLite database does not require a server, so you do not have to specify hostname, username, and password. The database in the URL is the file name of the file on your hard disk.

The database URL used by the program must be saved to the Sqlalchemy_database_uri key of the Flask configuration object. There is also a useful option in the configuration object, the Sqlalchemy_on_reardown key, which, when set to True, automatically commits changes in the database after each request is completed.

We use the SQLite database with the following configuration code:

2. from flask import Flask
4. from flask.ext.sqlalchemy import SQLAlchemy
6. import os
10. baseDir = os.path.abspath(os.path.dirname(__file__))
14. app = Flask(__name__)
16. app.config[‘SQLALCHEMY_DATABASE_URI‘] =\
18. ‘sqlite:////‘ + os.path.join(baseDir,‘data.sqlite‘)
20. app.config[‘SQLALCHEMY_COMMIT_ON_TEARDOWN‘] = True
24. db = SQLAlchemy(app)

2. Defining the Model

Define a user table users and a role table roles, with the following code:

2. class User(db.Model):
4. __tablename__ = ‘users‘
6. id = db.Column(db.Integer,primary_key=True)
8. username = db.Column(db.String(64),unique=True,index=True)
12. def __repr__(self):
14. return ‘<User %r>‘ % self.username
20. class Role(db.Model):
22. __tablename__ = ‘roles‘
24. id = db.Column(db.Integer,primary_key=True)
26. name = db.Column(db.String(64),unique=True)
30. def __repr__(self):
32. return ‘<Role %r>‘ % self.name

Explained below:

0. Two classes of user and role inherit from Db.model
1. __tablename__ used to specify the table name
2.db. The column function specifies the type of field in the database, whether it is a primary key (Primary_key), whether it is unique (unique), indexed (index), and others such as: whether it can be empty (nullable=true), default

3. Relationship

Using a relational database, do not specify how the relationship can be done. We do not specify a relationship in the definition model, a user has a role, a role can belong to multiple users, a typical one-to-many relationship, how to define relationships in Flask-sqlalchemy?

On the code:

2. class User(db.Model):
4. __tablename__ = ‘users‘
6. id = db.Column(db.Integer,primary_key=True)
8. username = db.Column(db.String(64),unique=True,index=True)
12. role_id = db.Column(db.Integer,db.ForeignKey(‘roles.id‘))
16. def __repr__(self):
18. return ‘<User %r>‘ % self.username
24. class Role(db.Model):
26. __tablename__ = ‘roles‘
28. id = db.Column(db.Integer,primary_key=True)
30. name = db.Column(db.String(64),unique=True)
34. users = db.relationship(‘User‘,backref=‘role‘)
40. def __repr__(self):
42. return ‘<Role %r>‘ % self.name

Added two lines of code:

The first line, in the user class:

role_id = db.Column(db.Integer,db.ForeignKey(‘roles.id‘))

This sentence is better understood, and similar to the above common variables, is the user class added a role_id variable, data type db. Integer, the second parameter specifies which ID of the table the foreign key is in.

The second line, in the role class:

 users = db.Relationship(‘User‘,backref=‘role‘)

This sentence is more complicated, read the following words carefully:

0. The users attribute added to the role model represents the object-oriented perspective of the relationship. For an instance of a role class, its Users property returns a list of the users associated with the role.
1. The db.Relationship() first parameter indicates which model (class) The other end of the relationship is. If the model class has not yet been defined, it can be specified in string form.
2. The db.Relationship() second parameter, Backref, adds a role attribute to the user class, which defines the inverse relationship. This property replaces the ROLE_ID access role model, when you get the model object instead of the foreign key value.

The above relationship is the representation of a one-to-many relationship.
Call DB. Relationship () You need to set the UserList parameter to False. As follows:

1. db.Relationship(‘User‘,backref=‘role‘,uselist=False)

As for the many-to-many relationship, it will be introduced slowly.

4. Database operations

Database operation, some basic: database, table creation or deletion, data deletion and modification.

0. Creating databases and Tables

1. db.create_all()

The Users and roles tables mentioned above are created.

1. Deleting a database

1. db.drop_all()

1. Insert Row

1. Role_admin = Role (name=' admin ')
2. User_tom = User (username=' Tom ', Role=role_admin)
3. User_jim = User (username=' Jim ', Role=role_admin)
4. User_tim = User (username=' Tim ', Role=role_admin)
5. User_sam = User (username=' Sam ', Role=role_admin)
6. Db.session.add (Role_admin)
7. Db.session.add (User_tom)
8. Db.session.add (User_jim)
9. Db.session.commit ()

Through database session management changes made to the database, in Flask-sqlalchemy, the session is db.session represented, before the formation is ready to write to the database, you need to add it to the session. Writing to the database requires calling the db.session.commit() method.

2. Modify the row

We will change the Role_admin variable name to admin to administrator.

1. Role_admin.name=' Administrator '
2. Db.session.add (Role_admin)
3. Db.session.commit ()

3. Delete rows

Delete Jim User

1. db.session.delete(user_jim)
2. db.session.commit()

4. Query lines

Query All Users

#返回结果：[<User ‘tom‘>, <User ‘tim‘>, <User ‘sam‘>]

Flask--relationship