Flask数据库_表关系之多对多

Flask数据库_表关系之多对多

一.多对多关系介绍

• 多对多的关系需要通过一张中间表来绑定他们之间的关系。

• 先把两个需要做多对多的模型定义出来

• 使用Table定义一个中间表，中间表一般就是包含两个模型的外 键字段就可以了，并且让他们两个来作为一个“复合主键”

• 在两个需要做多对多的模型中随便选择一个模型，定义一个 relationship属性，来绑定三者之间的关系，在使用relationship 的时候，需要传入一个secondary=中间表对象名

二.实例

db_utils.py:

#coding=utf-8


from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

USER = 'root'
PWD = 'root'
HOST = '127.0.0.1'
PORT = 3306
DATA_BASE = 'flask_db'

DB_URL = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'

# 创建一个引擎
engine = create_engine(DB_URL)
Base = declarative_base(engine)
Session = sessionmaker(engine)

main.py:

#coding=utf-8

from turtle import title
from unicodedata import name
from db_utils import Base,Session
from sqlalchemy import Column,Integer,String,Table,ForeignKey
from sqlalchemy.orm import relationship,backref

news_tag = Table(
    't_news_tag',
    Base.metadata,
    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)
    tags = relationship('Tag',backref='newss',secondary=news_tag)
    
    def __repr__(self):  
        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)
    # news =relationship('News',backref='tags',secondary= news_tag)
    
    def __repr__(self):  
        return f'<Tag: id={self.id} name={self.name}>'

def create_data():
    news1 = News(title='Python')
    news2 = News(title='Java')
    tag1 = Tag(name='python升级了')
    tag2 = Tag(name='qf')

    news1.tags.append(tag1)
    news1.tags.append(tag2)
    news2.tags.append(tag1)
    news2.tags.append(tag2)

    with Session() as s:
        s.add_all([news1,news2])
        s.commit()

def query_data():
    with Session() as s:
        n = s.query(News).first()
        print(n.tags)

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