現(xiàn)有books表和authors表，可通過書名查對應(yīng)的作者此洲，可通過作者查其對應(yīng)的書厂汗。那么除books表和authors表以外還需另一張表存放書和作者的對應(yīng)關(guān)系。
創(chuàng)建表結(jié)構(gòu)的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()

#不需要手動(dòng)管理該表呜师，所以不用class的方式創(chuàng)建
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表關(guān)聯(lián)authors表娶桦，同通過第二張表book_m2m_author查詢，bachref反向查詢即通過作者查每個(gè)作者對應(yīng)基本書
    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:\軟件下載\自動(dòng)化測試軟件\pycharmProfessional\workspace\c14\sudentsInfo.db3')
Base.metadata.create_all(engine)

添加表數(shù)據(jù)及查詢的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]) #先提交書的數(shù)據(jù)汁汗，防止后面用到該書名時(shí)卻還未創(chuàng)建造成的錯(cuò)誤
#
# 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)

結(jié)果：

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