python orm多对多表结构

现有books表和authors表，可通过书名查对应的作者，可通过作者查其对应的书。那么除books表和authors表以外还需另一张表存放书和作者的对应关系。
创建表结构的orm_multiTomulti_connetion.py文件：

import sqlite3
import sqlalchemy
from sqlalchemy import create_engine,Column,Integer,DATE,String,Table,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship

Base = declarative_base()

#不需要手动管理该表，所以不用class的方式创建
book_m2m_author = Table(
    'book_m2m_author',Base.metadata,
    Column('book_id',Integer,ForeignKey('books.id')),
    Column('author_id',Integer,ForeignKey('authors.id'))
)
class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    #books表关联authors表，同通过第二张表book_m2m_author查询，bachref反向查询即通过作者查每个作者对应基本书
    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(64))
    def __repr__(self):
        return self.name

#mysql '?charset=utf8'必须写在路径后面才支持中文
engine = create_engine('sqlite:///F:\软件下载\自动化测试软件\pycharmProfessional\workspace\c14\sudentsInfo.db3')
Base.metadata.create_all(engine)

添加表数据及查询的orm_multiTomulti_connetion_api.py文件：

from datetime import datetime
from day11 import orm_multiTomulti_connetion
from sqlalchemy.orm import sessionmaker
from sqlalchemy import DATE

session_class = sessionmaker(bind=orm_multiTomulti_connetion.engine)
session = session_class()

# book1 = orm_multiTomulti_connetion.Book(name = 'book_name1',pub_date = datetime(2014,10,2))
# book2 = orm_multiTomulti_connetion.Book(name = 'book_name2',pub_date = datetime(2015,10,2))
# book3 = orm_multiTomulti_connetion.Book(name = 'book_name3',pub_date = datetime(2016,10,2))
# session.add_all([book1,book2,book3]) #先提交书的数据，防止后面用到该书名时却还未创建造成的错误
#
# author1 = orm_multiTomulti_connetion.Author(name = 'Tim')
# author2 = orm_multiTomulti_connetion.Author(name = 'Jack')
# author3 = orm_multiTomulti_connetion.Author(name = 'Kimmy')
#
# book1.authors = [author1,author3]
# book3.authors = [author1,author2,author3]
# session.add_all([author1,author2,author3])
# session.commit()

author_obj = session.query(orm_multiTomulti_connetion.Author).filter(orm_multiTomulti_connetion.Author.name == 'Tim').first()
print(author_obj,author_obj.books)

book_obj = session.query(orm_multiTomulti_connetion.Book).filter(orm_multiTomulti_connetion.Book.id == 3).first()
print(book_obj.authors)
book_obj.authors.remove(author_obj)
print(book_obj.authors)

结果：

Tim [book_name1, book_name3]
[Kimmy, Jack, Tim]
[Kimmy, Jack]