1, Foreword
A many to many relationship is a table that can be associated with multiple tables.
Now let's design a table structure that can describe the relationship between "book" and "author". The requirements are
- A book can be published by several authors
- A writer can write several books
2, Table structure and data
The book > M2M > author table is generated by the author table and the book table
3, Examples
Create 3 tables
# -*- coding: UTF-8 -*- from sqlalchemy import Table, Column, Integer, String, DATE, ForeignKey from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine # If the inserted data has Chinese, you need to specify charset=utf8 engine = create_engine("mysql+pymysql://bigberg:111111@172.16.200.49:3306/study?charset=utf8", encoding='utf-8') Base = declarative_base() # Establish orm Base class Base.metadata.create_all(engine) # After this table is created, it does not need to be maintained book_m2m_author = Table("book_m2m_author", Base.metadata, Column("id", Integer, primary_key=True), Column('books_id', Integer, ForeignKey("books.id")), Column('authors_id', Integer, ForeignKey("authors.id"))) class Book(Base): __tablename__ = "books" id = Column(Integer, primary_key=True) name = Column(String(64)) pub_date = Column(DATE) authors = relationship("Author", secondary=book_m2m_author, backref="books") def __repr__(self): return self.name class Author(Base): __tablename__ = "authors" id = Column(Integer, primary_key=True) name = Column(String(32)) def __repr__(self): return self.name # Create table Base.metadata.create_all(engine)