102.多对多关系的使用

from turtle import title
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy import Table
from sqlalchemy.orm import relationship, backref

from util_db import Base, Session

# 创建第3张表, 建立多对多的关系
# 放到前两个表之上
# 参数详解
# mytable = Table(
#             "mytable", metadata,
#             Column('mytable_id', Integer, primary_key=True),
#             Column('value', String(50))
#         )
news_tag = Table(
    't_news_tag',# 表名
    Base.metadata,
    #      新表中的属性名       作为t_news表的外键        并且作为主键 就避免了重复数据   
    Column('news_id', Integer, ForeignKey('t_news.id'),primary_key = True),
    Column('tag_id',  Integer, ForeignKey('t_tag.id'),primary_key = True)
)


class News(Base):
    __tablename__ = 't_news'
    id = Column(Integer, primary_key = True, autoincrement = True)
    title = Column(String(32), nullable = False)
    #                  关联表tag    可以反向查找     链接news_tag       
    tags = relationship('Tag', backref = 'newss', secondary = news_tag)

    def __repr__(self) -> str:
        return f"<News: id={self.id} title={self.title}>"
    

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

    def __repr__(self) -> str:
        return f'<Tag: id={self.id} name={self.name}>'

# 创建表的数据
def create_data():
    news1 = News(title = "Python更新了")
    news2 = News(title = "SQLAlchemy功能又强大了")
    tag1 = Tag(name = 'IT新闻')
    tag2 = Tag(name = '科学技术')

    # 关联到第三个表中
    news1.tags.append(tag1)
    news1.tags.append(tag2)
    # 在第三个表中就会有（1，1）（1，2）（2，1）（2，2）
    news2.tags.append(tag1)
    news2.tags.append(tag2)
    with Session() as ses:
        ses.add(news1)
        ses.add(news2)
        ses.commit()

# 查找数据
def query_data():
    with Session() as ses:
        news = ses.query(News).first()
        print(news.tags)

if __name__ == "__main__":
    # Base.metadata.create_all()
    # create_data()
    query_data()