The basic operation of MySQL in flask

• In Flask-sqlalchemy, insert, modify, delete operations are managed by the database session.

  • The session is represented by Db.session. Before preparing to write the data to the database, add the data to the session and then call the commit () method to commit the conversation.

• In Flask-sqlalchemy, the query operation is to manipulate the data through the query object.

  • The most basic query is to return all the data in the table, and you can make a more accurate database query through the filter.

Defining a model class in a view function

From flask import Flaskfrom flask_sqlalchemy Import Sqlalchemyapp = Flask (__name__) #设置连接数据库的URLapp. config[' Sqlalchemy_ Database_uri '] = ' mysql://root:[email protected]:3306/test ' app.config[' sqlalchemy_track_modifications '] = The true# query displays the original SQL statement app.config[' Sqlalchemy_echo ' = Truedb = SQLALCHEMY (APP) class Role (db). Model): # Defines the table name __tablename__ = ' Roles ' # defines the Column object id = db. Column (db. Integer, primary_key=true) name = db. Column (db.        String (unique=true) US = db.relationship (' User ', backref= ' role ') #repr () method displays a readable string def __repr__ (self): Return ' role:%s '% self.nameclass User (db. Model): __tablename__ = ' users ' id = db. Column (db. Integer, primary_key=true) name = db. Column (db. String (+), unique=true, index=true) email = db. Column (db. String (UP), unique=true) password = db. Column (db. String (role_id) = db. Column (db. Integer, Db. ForeignKey (' roles.id ') def __repr__ (self): return ' user:%s '%self.nameif __name__ = = ' __main__ ': App.run (DebuG=true)　 

The association between models is one-to-many

Class Role (db. Model):    ...    #关键代码    US = db.relationship (' user ', backref= ' role ', lazy= ' dynamic ') ...    class User (db). Model):    ...    role_id = db. Column (db. Integer, Db. ForeignKey (' roles.id '))

• Where realtionship describes the relationship between role and user. In this article, the first argument is the class "User" of the corresponding reference
• The second parameter backref the method for declaring the new attribute to the class user
• The third parameter, lazy, determines when sqlalchemy loads data from the database
  • If set to subquery (subquery), the object associated with the role object is loaded immediately after it is loaded, which reduces the total number of queries, but is slower if the number of entries returned is large
    • If set to subquery, Role.users returns all data lists
  • Alternatively, you can set the dynamic mode so that the associated object will be loaded when it is used, and filtered before it is returned, if the number of objects returned, or the future becomes much, it is best to use this method
    • When set to dynamic, Role.users returns the query object, does not make a real query, can use the query object to do other logic, such as: first sort and then return the result

Many-to-many

Registrations = db. Table (' registrations ',      db. Column (' student_id ', db. Integer, Db. ForeignKey (' students.id ')),      db. Column (' course_id ', db. Integer, Db. ForeignKey (' courses.id '))  )  class Course (db). Model):    .. class Student (db. Model):    ...    Courses = db.relationship (' Course ', secondary=registrations,                                      backref= ' students ',                                      lazy= ' dynamic ')

Common SQLAlchemy Query Filters Filters

Filter	Description
Filter ()	Add the filter to the original query and return a new query
Filter_by ()	Add the equivalent filter to the original query and return a new query
Limit	Qualifies the result returned by the original query with the specified value
Offset ()	Offsets the result returned by the original query and returns a new query
Order_by ()	Sorts the original query results according to the specified criteria, returning a new query
Group_by ()	Groups the original query results according to the specified criteria, returning a new query

Common SQLAlchemy Query Actuators

Method	Description
All ()	Return all results of a query as a list
First ()	Returns the first result of a query, if not found, returns none
first_or_404 ()	Returns the first result of a query, if not found, returns 404
Get ()	Returns the row corresponding to the specified primary key, if not present, returns none
get_or_404 ()	Returns the row that corresponds to the specified primary key, if it does not exist, returns 404
Count ()	Returns the number of query results
Paginate ()	Returns a Paginate object that contains the results in the specified range

To create a table:

Db.create_all ()

To delete a table:

Db.drop_all ()

Insert a piece of data

Ro1 = Role (name= ' admin ') db.session.add (Ro1) db.session.commit () #再次插入一条数据ro2 = role (name= ' user ') Db.session.add (RO2) Db.session.commit ()

Insert multiple data at a time

us1 = User (name= ' Wang ', email= ' [email protected] ', password= ' 123456 ', role_id=ro1.id) US2 = User (Name= ' Zhang ', email= ' [email protected] ', password= ' 201512 ', role_id=ro2.id) US3 = User ( Name= ' Chen ', email= ' [email protected] ', password= ' 987654 ', role_id=ro2.id) US4 = User (name= ' Zhou ', email= ' [email  protected] ', password= ' 456789 ', role_id=ro1.id) US5 = User (name= ' Tang ', email= ' [email protected] ', Password= ' 158104 ', role_id=ro2.id) US6 = User (name= ' Wu ', email= ' [email protected] ', password= ' 5623514 ', role_id= ro2.id) US7 = User (name= ' qian ', email= ' [email protected] ', password= ' 1543567 ', role_id=ro1.id) US8 = User (name= ') Liu ', email= ' [email protected] ', password= ' 867322 ', role_id=ro1.id) US9 = User (name= ' li ', email= ' [email  Protected] ', password= ' 4526342 ', role_id=ro2.id) US10 = User (name= ' Sun ', email= ' [email protected] ', password= ' 235523 ', role_id=ro2.id) Db.session.add_all ([Us1,us2,us3,us4,us5,us6,us7,us8,us9,us10]) db.session.commit () 

Query: filter_by Accurate Query

Return everyone whose name equals Wang.

User.query.filter_by (name= ' Wang '). All ()

First () returns the object to which it was queried

User.query.first ()

All () returns all objects queried to

User.query.all ()

Filter fuzzy query that returns all data with the end of the name character G

User.query.filter (User.name.endswith (' G ')). All ()

Get (): parameter is primary key, if primary key does not exist no return content

User.query.get ()

Logical non, return all data whose name is not equal to Wang

User.query.filter (user.name!= ' Wang '). All ()

Not_ is equivalent to taking counter

From SQLAlchemy import Not_User.query.filter (not_ (user.name== ' Chen's)). All ()

Logic and, need to import and, return all data satisfied with and () condition

From SQLAlchemy import And_User.query.filter (And_ (user.name!= ' Wang ', User.email.endswith (' 163.com ')). All ()

Logical OR, import or_ required

From SQLAlchemy import Or_User.query.filter (or_ (user.name!= ' Wang ', User.email.endswith (' 163.com ')). All ()

Delete after querying data

user = User.query.first () db.session.delete (user) Db.session.commit () User.query.all ()

　　

Update data

user = User.query.first () user.name = ' dong ' db.session.commit () User.query.first ()

　　

Example of an association query:

The relationship between roles and users is a one-to-many relationship, a role can have multiple users, and a user can belong to only one role.

• All users of the query role

#查询roles表id为1的角色ro1 = Role.query.get (1) #查询该角色的所有用户ro1. Us.all ()

• Querying the role that the user belongs to

#查询users表id为3的用户us1 = User.query.get (3) #查询用户属于什么角色us1. Role

The basic operation of MySQL in flask