Many to many relationship of database in Flask

• Many to many relationship

One to many, many to one, and one to one relationships all have at least one side that is a single entity. The relationship between tables is realized by foreign keys, which point to that entity. To solve the many to many relationship, we need to introduce a third table, called association table, which can be decomposed into two one to many relationships between the original table and the association table. For example, when students choose courses, one student can choose multiple courses, and one course can be chosen by multiple students. This is a typical many to many relationship.

The process of many to many relationship query: for example, to find out which courses a student has chosen, first obtain the student student from the one to many relationship between the student and the registration_ All classes corresponding to ID_ ID, and then traverse the one to many relationship between the course and registration in the direction of many to one to find all courses selected by the student.

1. Many to many relationship between two entities

Flask Sqlalchemy implements many to many relationships

1 registrations = db.Table('registrations', 2 db.Column('student_id', db.Integer, db.ForeignKey('students.id')), 3 db.Column('class_id', db.Integer, db.ForeignKey('classes.id'))) 4 5 class Student(db.Model): 6 __tablename__ = 'students' 7 id = db.Column(db.Integer, primary_key=True) 8 name = db.Column(db.String) 9 classes = db.relationship('Class', secondary=registrations, 10 backref = db.backref('students', lazy='dynamic'), 11 lazy='dynamic') 12 13 class Class(db.Model): 14 __tablename__ = 'classes' 15 id = db.Column(db.Integer, primary_key=True) 16 name = db.Column(db.String)

Still used in many to many relationships db.relationship() method definition, but in many to many relationships, the secondary parameter must be set as the associated table. Many to many relationships can be defined in any class, and the backref parameter handles the other side of the relationship. The associated table is a simple table, not a model. Flask Sqlalchemy will take over the table automatically.

Student registration course:

1 stu.classes.append(c) 2 db.session.add(stu)

List all courses that students are enrolled in:

1 stu.classes.all()

All students registered for Course c:

1 c.students.all()

• self-referential relation

In the User focus function, there are no two entities in the many to many relationship, only the User entity model. If both sides of a relationship are in the same table, this relationship is called a self referencing relationship

Associated table follows, where each row represents one user following another. On the left is the follower, which can be understood as a fan, on the right is the followed, which can be understood as paying attention to others.

When using many to many relationships, additional information between two entities needs to be stored, such as the time information that one user pays attention to another user. In order to process the customized data, the association table can be designed as an accessible model.

1 class Follow(db.Model): 2 __tablename__ = 'follows' 3 follower_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True) 4 followed_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True) 5 timestamp = db.Column(db.DateTime, default=datetime.utcnow) 6 7 class User(UserMixin, db.Model): 8 __tablename__ = 'users' 9 ... 10 followers = db.relationship('Follow', 11 foreign_keys=[Follow.followed_id], 12 backref=db.backref('followed', lazy='joined'), 13 lazy='dynamic', 14 cascade='all, delete-orphan') 15 followed = db.relationship('Follow', 16 foreign_keys=[Follow.follower_id], 17 backref=db.backref('follower', lazy='joined'), 18 lazy='dynamic', 19 cascade='all, delete-orphan')

Parameter introduction:

1. foreign_ The keys parameter represents the associated foreign key

2. The backref parameter indicates that the followed/follower is referred back to the follow model through the Follow.follower Visit fans through Follow.followed Visit the following people

3. When the lazy parameter in the callback is specified as joined, related objects can be loaded from the join query immediately

4. The influence of the operation of cascade parameter configuration on the parent object on related objects

• Auxiliary methods of attention relationship

1 class User(UserMixin, db.Model): 2 __tablename__ = 'users' 3 ... 4 5 # Follow a user 6 def follow(self, user): 7 # Judge if you have paid attention 8 if not self.is_following(user): 9 # Create a new association table object instance to record the relationship between fans and followers 10 f = Follow(follower=self, followed=user) 11 db.session.add(f) 12 13 # Unfollow a user 14 def unfollow(self, user): 15 # Check if the user of the cancelled attention has been paid attention to 16 f = self.followed.filter_by(followed_id=user.id).first() 17 if f: 18 db.session.delete(f) 19 20 # Judge whether you have paid attention 21 def is_following(self, user): 22 # Confirm whether the user has id，In case a user is created but not committed to the database 23 if user.id is None: 24 return False 25 return self.followed.filter_by(followed_id=user.id).first() is not None 26 27 # Judge whether it is concerned 28 def is_followed_by(self, user): 29 if user.id is None: 30 return False 31 return self.followers.filter_by(follower_id=user.id).first() is not None

• END

27 May 2020, 02:00 | Views: 2772