In Python, we can use the SQLAlchemy framework for database operations. in The Flask framework, we can use SQLAlchemy, next, let's take a look at the tutorial on using Flask-SQLAlchemy to manage databases in the Python Flask framework.
Use Flask-SQLAlchemy to manage databases
Flask-SQLAlchemy is a Flask extension that simplifies the use of SQLAlchemy in the Flask application. SQLAlchemy is a powerful relational database framework that supports backend databases. Provides advanced ORM and local SQL functions for accessing the underlying database.
Like other extensions, install Flask-SQLAlchemy through pip:
(venv) $ pip install flask-sqlalchemy
In Flask-SQLAlchemy, the database is specified as a URL. The table lists the formats of the three most popular database engine URLs:
In these URLs, hostname is the server hosting the MySQL service. it may be a local server or a remote server. The database server can host multiple databases, so the database indicates the name of the database to be used. The database requires authentication. username and password are Database User creden.
Note:> The SQLite database has no service, so hostname, username, and password can be defaulted and the database is a disk file name.
The application database URL must be configured in the SQLALCHEMY_DATABASE_URI key of the Flask configuration object. Another useful option is SQLALCHEMY_COMMIT_ON_TEARDOWN, which can be set to True to enable automatic submission of database changes in each request. See Flask-SQLAlchemy for more 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 functions to access Flask-SQLAlchemy.
Model Definition
A model is a persistent entity used by an application. In the background of ORM, a model is usually a Python class with attributes, and its attributes match the columns of the database table. The Flask-SQLAlchemy database 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(64), 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(64), unique=True, index=True)def __repr__(self): return '
' % self.username
_ Tablename _ class variable defines the name of the table in the database. If _ tablename _ is the default Flask-SQLAlchemy, the default table names are specified. However, these default names do not comply with the naming conventions using the plural. Therefore, it is best to explicitly name the table names. Other variables are model attributes and are defined as db. Column instances.
The first parameter passed to the db. Column constructor is the type of the database Column, that is, the data type of the model attribute. Table 5-2 lists the types of available columns, which are also used in the Python model.
The most common SQLAlchemy column type
The remaining db. Column parameters specify configuration options for each attribute.
The most common SQLAlchemy column options
Note: Flask-SQLAlchemy must define a primary key column for all models, usually named as id.
Both models contain the repr () method to show them a readable string. although not completely necessary, it is good for debugging and testing.
Link
Relational databases establish connections in different tables by using links. The relationship diagram shows a simple relationship between a user and a user role. This role has a one-to-multiple relationship with the user, because one role can belong to multiple users, and one user can have only one role.
The following model class shows the 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 link uses a foreign key to connect two rows. The role_id column added to the User model is defined as a foreign key and the relationship is established. The column specified by the parameter roles. id of db. ForeignKey () should be interpreted as the column with the id value in the row of the roles table.
The users attribute added to the Role model represents the object-oriented view of the relationship. Given an instance of the Role class, the users attribute returns a group of users connected to this Role. The first parameter specified to db. relationship () indicates the other side of the link in the model. If the class is not defined, this model can be provided as a string.
Note: I encountered a problem in segmentdefault. later I roughly read the source code of SQLAlchemy. The column of the ForeignKey class receives three types of parameters, one of which is "model name. attribute name; table name. column name. The last one is not clear. try again next time.
The backref parameter of db. relationship () defines the inverse relationship by adding the role attribute to the User model. This attribute can replace role_id to access the Role model, which is used as an object rather than a foreign key.
In most cases, db. relationship () can locate its own foreign key relationship, but sometimes it cannot be determined which column is used as the foreign key. For example, if the User model has two or more columns defined as the Role's foreign key, SQLAlchemy does not know which of the two columns to use. When the foreign key configuration is ambiguous, the additional parameter db. relationship () must be used (). The subscript lists some common configuration options for defining relationships:
Commonly used SQLAlchemy link options
Suggestion: If you have an application cloned on GitHub, you can run git checkout 5a to switch to this version of the application.
In addition to one-to-many relationships, there are other types of relationships. A one-to-one relationship can be expressed as the one-to-many relationship described above. you only need to set the uselist option in db. relationship () to False and change "multiple" to "one. Multiple-to-one relationships can also be expressed as one-to-multiple relationships after the table is reversed, or foreign keys and db. relationship () are defined on the "multiple" side. The most complex relational type, many to many, requires an additional table called an associated table. In chapter 2, you will learn many-to-many relationships.
Database operations
The best way to learn how to use a model is to use Python shell. The following sections describe the most common database operations.
Create a table
The first thing to do is to instruct Flask-SQLAlchemy to create a database based on the model class. The db. create_all () function completes these steps:
(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, which is provided in the configuration. If the database already exists, the db. create_all () function does not re-create or update the database table. This is inconvenient when the model is modified and the changes need to be applied to the existing database. The brute force solution for updating existing database tables is to delete the old tables first:
>>> db.drop_all()>>> db.create_all()
Unfortunately, an undesirable side effect of this method is to destroy all the data in the old database. The solution to updating database problems 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='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 attribute as the keyword parameter. Note that you can even use the role attribute, even if it is not a real database column, but an advanced representation of a one-to-multiple relationship. The id attribute of these new objects is not explicitly set: the primary key is managed by Flask-SQLAlchemy. So far, objects have only been stored in Python, and they have not been written into the database. Because their id value has not been assigned:
>>> print(admin_role.id) None>>> print(mod_role.id) None>>> print(user_role.id) None
The database modification operation is managed by the db. session database session provided by Flask-SQLAlchemy. Objects 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])
To write an object to a database, you need to submit the session through its commit () method:
>>> db.session.commit()
Check the id attributes again. at this time, they are all set:
>>> print(admin_role.id) 1>>> print(mod_role.id)2>>> print(user_role.id) 3
Note: There is no connection between db. session database sessions and the Flask session discussed in Chapter 4. A database session is also called a transaction.
Database sessions are very useful in database consistency. The commit operation writes all objects added to the session to the database atomically. If an error occurs during the write process, the entire session is discarded. If you always submit relevant changes in a session, you must ensure that the database inconsistency caused by some updates is avoided.
Note: database sessions can also be rolled back. If you call db. session. rollback (), any objects added to the database session will be restored to the state they used to be in the database.
Modify rows
The add () method in the database session can also be used to update the model. In the same shell session, rename the "Admin" role as "Administrator" in the following example ":
>>> admin_role.name = 'Administrator'>>> db.session.add(admin_role)>>> db.session.commit()
Note: However, it seems that we do not use db. session. add () during the update operation, but directly use db. session. commit () to submit transactions.
Delete row
Database sessions also have the delete () method. The following example deletes the "Moderator" role from the database:
>>> db.session.delete(mod_role)>>> db.session.commit()
Note that deletion is performed after the database session is submitted, just like inserting an update.
Return row
Flask-SQLAlchemy creates a query object for each model class. The most basic query model is to return all the content of the corresponding table:
>>> Role.query.all()[
,
]>>> User.query.all()[
,
,
]
You can use filters to configure query objects for more specific database searches. The following example finds all users assigned the "User" role:
>>> User.query.filter_by(role=user_role).all()[
,
]
For a given query, you can also check 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 :param_1 = users.role_id'
If you exit the shell session, the object created in the previous example cannot exist as a Python object, but you can continue to exist as a row record in your own database table. If you start a completely new shell session, you must recreate Python objects from their database lines. The following example executes a query to load the User role named "User.
>>> user_role = Role.query.filter_by(name='User').first()
A filter, such as filter_by (), is called through a query object and the extracted query is returned. Multiple filters can be called in sequence until the query configuration is complete.
The following lists some filters that are commonly used in queries.
After all the required filters are applied to the query, calling all () triggers the query execution and returns a set of results. However, besides all (), there are other methods to trigger the execution. Commonly used SQLAlchemy query executor:
The relational principle is similar to query. The following example describes the one-to-many relationship between roles and users:
>>> users = user_role.users>>> users[
,
]>>> users[0].role
The user_role.users query here has a small problem. When the user_role.users expression calls all () internally, it returns the user list through implicit query execution. Because the query object is hidden, it is impossible to further extract it by appending a query filter. In this particular example, it may be used to return the user list in alphabetical order. In the following example, the query of the link configuration modified by the lazy = 'dynamic 'parameter is not automatically executed.
App/models. py: dynamic relationship
class Role(db.Model): # ... users = db.relationship('User', backref='role', lazy='dynamic') # ...
Configure the link 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