Flask database Many-to-many relationships

Database usage relationships establish a link between records. Where a one-to-many relationship is the most common type of relationship, it links a record with a set of related records. When you implement this relationship, you add a foreign key to the "many" side and a record that points to the "one" side of the join. Most of the other relationship types can be derived from a one-to-many type. Many-to-one relationship from the "more" side of the view, is a one-to-many relationship. A one-to-many relationship type is a single-to-multiple relationship with a simplified version, limiting the "many" side to a maximum record. The only type that cannot be easily evolved from a one-to-many relationship is a many-to-many relationship with multiple records on both sides of the relationship.

Many-to-many relationships
A one-to-many relationship, a multi-pair relationship, and a single relationship have at least one side of the individual entity, so the connection between records is implemented by a foreign key, with the foreign key pointing to the entity. But how do you achieve "many" relationships on both sides?

Here is an example of a typical many-to-many relationship, a database that records students and their chosen course. Obviously, you can't add a foreign key to a course in the student table, because a student can choose multiple courses and a foreign key is not enough. Similarly, you cannot include a foreign key to a student in the curriculum because there is more than one student's choice in a course. A set of foreign keys is required on both sides. The solution to this problem is to add a third table, which is called an association table. Many-to-many relationships can now be decomposed into two one-to-many relationships between the original table and the associated table.

Describes the many-to-many relationships between students and the curriculum.

The association table in this example is registrations, and each row in the table represents a student enrolled in a course. Querying a many-to-many relationship takes two steps. To find out which courses a student has chosen, start with a one-to-many relationship between the student and the registration, get all the records of the student in the registrations table, and then traverse the one-to-many relationship between the course and registration in the direction of many to the other, finding the student in Registrations the corresponding courses for each record in the table. Similarly, if you want to find all the students who have selected a course, you need to start with the curriculum, get their records in the registrations table, and then get the students who join the records. The practice of getting query results by traversing two relationships sounds difficult, but as with the simple relationship of the previous one, SQLAlchemy can do most of the work.

The code used for many-to-many relationships in is represented as follows:

[Python]View PlainCopy

2. Registrations = db. Table (' registrations ',
4. Db. Column (' student_id ', db. Integer, Db. ForeignKey (' students.id ')),
6. Db. Column (' class_id ', db. Integer, Db. ForeignKey (' classes.id '))
8. )
14. Class Student (db. Model):
16. ID = db. Column (db. Integer, primary_key=True)
18. Name = db. Column (db. String)
20. Classes = db.relationship (' Class ', secondary=registrations,
22. Backref=db.backref (' students ', lazy=' dynamic '),
24. lazy=' dynamic ')
30. Class class (db. Model):
32. ID = db. Column (db. Integer, Primary_key = True)
34. Name = db. Column (db. String)

Many-to-many relationships are still defined using the Db.relationship () method that defines a one-to-many relationship, but in a many-to-many relationship, the secondary parameter must be set to the associated table. Many-to-many relationships can be defined in any one class, and the Backref parameter handles the other side of the relationship. The association table is a simple table, not a model, and SQLAlchemy will automatically take over the table.
This is especially easy to handle many-to-many relationships. Assuming the student is S, the course is C, and the Student Registration course code is:
>>> S.classes.append (c)
>>> Db.session.add (s)
It is also easy to list student s enrolled courses and students enrolled in Course C:
>>> S.classes.all ()
>>> C.students.all ()
The students relationship in the CLASS model is defined by the parameter Db.backref (). Note that the lazy= ' dynamic ' parameter is also specified in this relationship, so queries returned on both sides of the relationship can accept additional filters.
If later student S decides not to choose Course C, then use the following code to update the database:
>>> S.classes.remove (c)

Here is a practical example: because students are transferred to the college in the design, the student and the college are many-to-many relationships

1. Defining the Model

[Python]View PlainCopy

2. Class User (Usermixin, DB. Model):
4. __tablename__ = ' users '
6. ID = db. Column (db. Integer, primary_key=True)
8. email = db. Column (db. String (+), unique=true, index=true)
12. ............. Omit other fields
14. Departments=db.relationship ('Department ', Secondary=user_department, Backref=db.backref (' users ', lazy= ' dynamic '), lazy=' dynamic ')

[Python]View PlainCopy

1. Class Department (db. Model):
2. __tablename__ = ' departments '
3. ID = db. Column (db. Integer, primary_key=True)
4. Department = DB. Column (db. String (+))

[Python]View PlainCopy

1. User_department = db. Table (' user_department ',
2. Db. Column (' user_id ', db. Integer, Db. ForeignKey (' users.id '), primary_key=True),
3. Db. Column (' department_id ', db. Integer, Db. ForeignKey (' departments.id '), primary_key=True)
4. )

2. Define the form:

[Python]View PlainCopy

2. Class Smform (Form):
4. Name = Stringfield (' real name ', Validators=[length (0, + )])
6. .................... Omit other fields
8. Is_departmentchange = Booleanfield (' whether to go to college ')
10. Pre_department = Selectfield (' original Academy: ', Coerce=int)
12. Cut_department = Selectfield (' Current college: ', Coerce=int)
16. Submit = Submitfield (' submit ')
20. #下拉菜单初始化
22. def __init__ (self, user, *args, **kwargs):
24. Super (Smform, Self ). __init__ (*args, **kwargs)
26. <strong>self.pre_department.choices = [(Pre_department.id, Pre_department.department)
28. For pre_department in Department.query.order_by (department.department). All ()]
30. self.cut_department.choices = [(Cut_department.id, Cut_department.department)
32. For cut_department in Department.query.order_by (department.department). All ()]</strong>
36. self.user = user

3. Define the route:

[Python]View PlainCopy

2. @main. Route ('/sm ', methods=[' GET ', ' POST ')
4. @login_required
6. @main. ErrorHandler (404)
8. def SM ():
10. user = User.query.filter_by (email=current_user.email). First ()
12. form = smform (user)
14. if user.is_realname = =False:
16. if Form.validate_on_submit ():
20. # User's Academy update delete old data,<strong> federated Delete
22. usr = current_user._get_current_object ()
24. Deparment = User.departments.all ()
26. For de in deparment:
28. De.users.remove (usr) </strong>
32. ........................ Omit other
38. User.is_departmentchange = Form.is_departmentChange.data
40. <strong>#向关系表中添加
42. User.departments.append (Department.query.get (Form.pre_department.data))
44. User.departments.append (Department.query.get (Form.cut_department.data))
48. Db.session.add (user)
50. Db.session.commit () </strong>
52. return Redirect (Url_for ('. sm_success '))
54. return render_template (' sm.html ', form=form)

4. Render template (omitted)

Flask database Many-to-many relationships