sqlalchemy 多对多关系

#encoding: utf-8

from sqlalchemy import create_engine,Column,Integer,String,Float,func,and_,or_,Text,\
    ForeignKey,Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship,backref
from random import randint

HOSTNAME = '127.0.0.1'

PORT = 3306

DATABASE = 'first_sqlalchemy'

USERNAME = 'root'

PASSWORD = '123456'

#dialect+driver://username:password@host:port/database
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/" \
         "{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

engine = create_engine(DB_URI)

Base = declarative_base(engine)

# Session = sessionmaker(engine)
# session = Session()

session = sessionmaker(engine)() #Session(**local_kw)


#使用Table这个类来实现多对多关系的第三张表
article_tag = Table(
    'article_tag',#第三张表名
    Base.metadata, #元类的数据
    Column('article_id',Integer,ForeignKey('article.id'),primary_key=True),#关联文章的字段
    Column('tag_id',Integer,ForeignKey('tag.id'),primary_key=True),#标签的外键关联字段
    #两字段primary_key都等于True，组合主键唯一，防止内容一样
)


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    #反向关联，Tag关联的表，Article同tags关联Tag，Tag通过articles反向关联，
    #secondary就是第三张表
    tags = relationship('Tag',backref='articles',secondary=article_tag)


class Tag(Base):
    __tablename__ = 'tag'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50),nullable=False)


# Base.metadata.drop_all()
#
# Base.metadata.create_all()


#多对多关系，所获取得对象都是，外层列表包裹着对象，记住只要是多得关系都是
#列表包裹着对象

# article1 = Article(title='武动乾坤')
# article2 = Article(title='斗破苍穹')
#
# tag1 = Tag(name='玄幻')
# tag2 = Tag(name='YY小说')
#
# article1.tags.append(tag1)
# article1.tags.append(tag2)
#
# session.add(article1)
# session.add(article2)
#
# session.commit()

#多对多的查

tag1 = session.query(Tag).filter(Tag.name == 'YY小说').all()
print(tag1) #用all（)方法都是列表，first（）方法都是单个的对象

print(tag1[0].articles)