有二张表，一张作者表，一张书表，一个作者写多本书，一本书可以由多个作者写，与是通过新加一张关系表把他们联系起来

#!/usr/bin/env python# -*- coding: utf-8 -*-# author aliex-hrgfrom sqlalchemy import Table, Column, Integer,String,DATE, ForeignKeyfrom sqlalchemy.orm import relationshipfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+pymysql://hrg:123@192.168.80.100:3306/test?charset=utf8")Base = declarative_base() #生成orm基类Base = declarative_base()book_to_author = Table(‘book_to_author‘, Base.metadata, Column(‘book_id‘,Integer,ForeignKey(‘books.id‘)), Column(‘author_id‘,Integer,ForeignKey(‘authors.id‘)), )class Author(Base): __tablename__ = ‘authors‘ #表名 id = Column(Integer, primary_key=True) name = Column(String(32)) def __repr__(self): return self.nameclass Book(Base): __tablename__ = ‘books‘ #表名 id = Column(Integer, primary_key=True) name = Column(String(32)) authors = relationship(‘Author‘, secondary=book_to_author, backref=‘books‘) def __repr__(self): return self.nameBase.metadata.create_all(engine) #创建表a1 = Author(name=‘alex‘)a2 = Author(name=‘keke‘)a3 = Author(name=‘tom‘)b1 = Book(name=‘linux‘)b2 = Book(name=‘python‘)b3 = Book(name=‘C++‘)b1.authors = [a1,a2,a3]b2.authors = [a1,a3]b3.authors = [a3]Session_class = sessionmaker(bind=engine)Session = Session_class()Session.add_all([a1,a2,a3,b1,b2,b3])Session.commit()print(‘--------通过书表查关联的作者---------‘)book_obj = Session.query(Book).filter(Book.name==‘linux‘).first()print(book_obj.name,book_obj.authors)print(‘--------通过作者表查关联的书---------‘)author_obj = Session.query(Author).filter(Author.name==‘alex‘).first()print(author_obj.name,author_obj.books)Session.commit()#通过书删除作者 只会从book_to_author表中删除记录sobj = Session.query(Author).filter(Author.name==‘alex‘).first() #找出要删除作者alex的对象名bobj = Session.query(Book).filter(Book.name==‘linux‘).first() #找出从linux这本书中删除bobj.authors.remove(sobj)Session.commit()#直接删除作者　 会从authors,book_to_author中删除#删除作者时，会把这个作者跟所有书的关联关系数据也自动删除author_obj =Session.query(Author).filter_by(name="tom").first()Session.delete(author_obj)Session.commit()

　　。。。。