- The project introduces flask Sqlalchemy
- ORM introduction and model definition
- Table relation type and coding implementation
- One to many relationship (many to one relationship)
- One to one relationship
- Many to many relationship
- Basic database operation
- to update
The project introduces flask Sqlalchemy
First, install the flash Sqlalchemy extension:
$pip install flask-sqlalchemy
Then, import the Sqlalchemy class in the project and instantiate the database used by the application (take MySQL as an example):
from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://account:[email protected]/database' db = SQLAlchemy(app)
The DB object is an instance of the Sqlalchemy class, which represents the database used by the program, and also obtains all the functions provided by flask Sqlalchemy.
ORM introduction and model definition
In Python, you can use the corresponding packages of the database to directly operate the database, such as pymysql to operate the MySQL database. There are also some database abstraction layer code packages to choose from, such as Sqlalchemy to be discussed here. The abstract package directly handles high-level Python objects instead of database entities such as tables.
The abstraction layer is the so-called object relational mapping (ORM). Its biggest advantage is that it can convert high-level object-oriented operations into low-level database instructions without users’ perception, which greatly simplifies code writing. Sqlalchemy is a higher-level abstraction example that has been well integrated with flask. It also supports a variety of relational database engines.
Sqlalchemy based model definition:
class Roles(db.Model): __tablename__ = 'roles' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(64), unique=True) users = db.relationship('Users', backref='role') class Users(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(64), unique=True, index=True) role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))
__ tablename__ Define the table name used in the database;
The first parameter of the db.column class constructor is the type of the database table column (also a model attribute). The other parameters specify the configuration options of the attribute (database table column).
2.1 common Sqlalchemy column types
|Type name||Python type||explain|
|Integer||int||Ordinary integer, 32-bit|
|SmallInteger||int||An integer with a small value range, usually 16 bits|
|BigInteger||Int or long||Integer with unlimited precision|
|Float||float||Floating point number|
|String||str||Variable length string|
|DateTime||datetime.datetime||Date and time|
|Text||str||Variable length string, optimized for long or unlimited length strings|
|Numeric||decimal.Decimal||Fixed point decimal|
2.2 common Sqlalchemy column options
|primary_key||If set to true, this column is the primary key of the table|
|unique||If set to true, duplicate values are not allowed in this column|
|index||If set to true, create an index for the column to improve query efficiency|
|nullable||If set to true, null values are allowed in this column. If set to false, null values are not allowed|
|default||Define default values for this column|
Table relation type and coding implementation
Relational databases use relationships to connect rows in different tables. Taking the above model definition code as an example, assume that roles have a one to many relationship with users (that is, one role can belong to multiple users, and each user can only have one role).
One to many relationship (many to one relationship)
On the “many” side, use foreign keys to define relationships
For example, in the users model, define a role_ ID is listed as the foreign key, pass the DB. Foreignkey() parameter to the DB. Column constructor, and take roles.id as the parameter of DB. Foreignkey(), which indicates role_ The value of the ID column is the ID value of the row in the roles table.
On the “one” side, create attributes representing instances (records or rows) from an object-oriented perspective, such as users = dB. Relationship (‘users’, backref = ‘role’) above
The first parameter of DB. Relationship () indicates which model is at the other end of the relationship. The backref parameter adds a role attribute to the users model to define the reverse relationship. This attribute (role) can replace role_ ID to access the roles model. At this time, you will get the model object, not the value of the foreign key.
How to understand the above paragraph can be deepened from the following two sentences:
users = inst_role.users user_role = user.role
Through the code in the first sentence, you can directly obtain all users objects corresponding to a specific role instance (inst_role), which are returned in the form of a list.
Through the code in the second sentence, you can directly obtain the role object corresponding to the user through the user instance (1 line of record, rather than the role_id field value defined by the users mode).
One to one relationship
To define a one-to-one relationship, just pass an additional keyword to DB. Relationship() function based on the one to many model definition to represent the relationship option:
users = db.relationship(‘Users’, backref=‘role’, userlist=False)
One to many and one to one should pay special attention when encoding: when obtaining the object of one of many through the instance of “one” (DB. Relationship defining party):
- One to many: users = Inst_ Role.users returns a list of objects
- One to one: users = Inst_ Role. Users returns objects, not lists
Many to many relationship
Basic database operation
In flask Sqlalchemy, changes to the database are managed through the database “session”, which is represented by dB. Session. If you need dB. Session. Commit() to submit changes to records, always submit database related changes in the session to avoid data inconsistency in the database due to partial update exceptions.
admin_role = Roles(name='Admin') mod_role = Roles(name='Moderator') john = Users(username='liyu', role=admin_role) david = Users(username='liji', role=admin_role) db.session.add(admin_role) db.session.add(mod_role) db.session.add(john) db.session.add(david) db.session.commit()
The first four lines of code instantiate two roles and two user objects (mapping to the database means assigning values to the recorded fields)
5 ~ 7 lines of code is to put the new roles and user operations in one session, and finally commit uniformly, which can prevent the data inconsistency caused by the update of part due to the exception of one of the statements. This operation is to form multiple atomic operations into a transaction. If an update fails, the whole session will be invalidated.
Lines 5-7 can also be abbreviated as db.session.add ([admin_role, mod_role, John, David])
In flask Sqlalchemy, a record is represented as an object; The field of the record is represented as the attribute of the object. Therefore, to update the field value is actually to assign a value to the attribute of the object:
admin_role.name = 'Administrator' db.session.add(admin_role) db.session.commit()
Query all records in the table: schema. Query. All()
Use filters for more accurate queries
|filter||Add filter to original query|
|filter_by||Add the equivalence filter to the original query|
|limit||Use the specified value to limit the number of results returned by the original query|
|offset||Offset the results returned by the original query|
|order_by||Sort the original query results according to the specified criteria|
|group_by||Group the original query results according to the specified criteria|
2.1 filter and filter_ By difference
2.1.1 grammatical differences
Filter needs to use “class name. Attribute name” and = = comparison, while filter_ By directly uses the attribute name, and comparison uses=
users = Users.query.filter(Users.id == 1).all() users = Users.query.filter_by(id = 1).all()
2.1.2 combined query
Filter does not support combined query, and can only be implemented continuously with filter_ By supports combined queries (the effect of the following two statements is the same)
users = Users.query.filter(Users.id == 1).filter(Users.username == 'xxx').all() users = Users.query.filter_by(id = 1, username='xxx').all() # filter_ By also supports continuous use
Note: if you want to view the native SQL query statements generated by Sqlalchemy for the query, you only need to convert the query object into a string: str (users. Query. Filter_by (role = admin_role))
After the specified filter is applied to the query, the query execution is triggered by calling all(). The common trigger execution methods are:
|all()||Query all results|
|first()||The first result of the query is returned, and none is not returned|
|get()||The row corresponding to the specified primary key is returned, and none is not returned|
|count()||Returns the number of query results|
|first_or_404()||Return the first result of the query. If there is no result, terminate the request and return 404 error response|
|get_or_404()||Returns the row corresponding to the specified primary key. If the specified primary key is not found, the request is terminated and a 404 error response is returned|
|paginate()||Returns a paginate object|
4.1 one to many
4.1.1 obtain all corresponding multi terminal objects from “one”
users = inst_ Role. Users // directly obtain all user objects belonging to the role through the users attribute of the role object role = inst_ User. Role // obtain the role object of the user directly through the role attribute of the user instance. Note that the role object obtained here is not only the role ID
Note: Inst_ When role.users gets the object, it implicitly calls the all () method to trigger execution. However, if you add some filters (such as sorting), you need to add the lazy =’dynamic ‘keyword parameter in db.relationship. Then you can introduce the filter: Inst_ role.users.order_ by(Users.username)
4.2 many to many
In flask Sqlalchemy, deleting a database record can be mapped to deleting the object representing the record:
This is the end of this article about using Sqlalchemy to implement persistent data in flask. For more information about persistent data in flask Sqlalchemy, please search previous articles of developeppaer or continue to browse the following articles. I hope you will support developeppaer in the future!