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()#提交