Mysql-Sqlalchemy-ORM-many_to_many

秒速五厘米 2021-12-21 01:49 215阅读 0赞

![1331390-20180718152443529-1743338530.png][]

orm_m2m.py

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
    
    
    Base =declarative_base()#生成orm基类
    
    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
    '''?charset=utf8 就可以支持中文'''
    engine = create_engine("mysql+pymysql://root:521521@192.168.71.140/oldboy?charset=utf8",
                           )  # 可以加echo=True显示数据
    
    Base.metadata.create_all(engine)#创建表结构

orm_m2m_api.py

from day12 import orm_m2m
    
    from sqlalchemy.orm import sessionmaker
    
    Session_class = sessionmaker(bind=orm_m2m.engine)
    
    Session =Session_class()#游标
    
    '''插入数据'''
    Python = orm_m2m.Book(name="Python", pub_date="2018-07-17")
    Java = orm_m2m.Book(name="JAVA", pub_date="2018-07-16")
    Php = orm_m2m.Book(name="PHP", pub_date="2018-07-15")
    
    a1 = orm_m2m.Author(name="xiaopang")
    a2 = orm_m2m.Author(name="dapangpang")
    a3 = orm_m2m.Author(name="Xiaopang_to_Dapangpang")
    
    # Python.authors=[a1,a2]
    # Java.authors=[a1,a2,a3]
    # Php.authors = [a1,a3]
    # Session.add_all([Python,Java,Php,a1,a2])
    '''查询数据'''
    #查找dapangpang这个作者创作了几本书
    author_obj =Session.query(orm_m2m.Author).filter(orm_m2m.Author.name=="dapangpang").first()
    print(author_obj)
    #查找orm_m2m.Book.id==1的这本书有几个作者
    book_obj =Session.query(orm_m2m.Book).filter(orm_m2m.Book.id==2).first()
    print(book_obj.authors)
    
    '''删除数据'''
    book_obj =Session.query(orm_m2m.Book).filter(orm_m2m.Book.id==1).first()
    book_obj.authors.remove(author_obj)#从一本书里删除一个作者
    
    #删除作者
    author_obj =Session.query(orm_m2m.Author).filter(orm_m2m.Author.name=="dapangpang").first()
    Session.delete(author_obj)
    Session.commit()#提交

转载于:https://www.cnblogs.com/fuyuteng/p/9328396.html

[1331390-20180718152443529-1743338530.png]: /images/20211221/2fcff52bc9624ced80ca8b164bd3ba8b.png