12ORM多对多表

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

engine = create_engine('mysql+pymysql://root:123@localhost/test?charset=utf8')
Base = declarative_base()

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)

    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


Base.metadata.create_all(engine)

session_class = sessionmaker(bind=engine)
session=session_class()
'''
b1=Book(name='天龙八部')
b2=Book(name='射雕英雄传')
b3=Book(name='鹿鼎记')
b4=Book(name='流星蝴蝶剑')

a1=Author(name='金庸')
a2=Author(name='古龙')
a3=Author(name='梁羽生')

b1.authors=[a1,a3]
b2.authors=[a1,a2,a3]

session.add_all([b1,b2,b3,b4,a1,a2,a3])
'''
'''
#查询书
book_obj=session.query(Book).filter(Book.name=='天龙八部').first()
#通过数查询作者
print(book_obj.authors)

#查询作者写了那几本书
author_obj=session.query(Author).filter(Author.name=='金庸').first()
print(author_obj.books)
'''

#删除从一本书里删除一个作者
author_obj=session.query(Author).filter(Author.name=='金庸').first()
book_obj=session.query(Book).filter(Book.name=='天龙八部').first()
book_obj.authors.remove(author_obj)

author_obj =session.query(Author).filter_by(name="金庸").first()
# print(author_obj.name , author_obj.books)
session.delete(author_obj)

session.commit()