第十一章：SQLAlchemy多对多讲解

多对多关系讲解

• config.py

  SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://root:root@127.0.0.1:3306/demo1?charset=utf8'
  SQLALCHEMY_TRACK_MODIFICATIONS = False

• app.py

'''
多对多的关系
'''
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column,String,Integer,DateTime,SmallInteger,ForeignKey
from datetime import datetime
app = Flask(__name__)
app.config.from_object('config')
db = SQLAlchemy(app)

'''
建立多对多的关系
'''
tag_channel = db.Table(
    'tag_channel',# 数据库的表名
    Column('id',Integer,autoincrement=True,primary_key=True),
    Column('tid',Integer,ForeignKey('tag.id')),
    Column('cid',Integer,ForeignKey('channel.id'))
)


'''
一级分类   比如说 央视频道 卫视频道  港澳台频道
'''
class Tag(db.Model):
    __tablename__ = 'tag'
    id = Column(Integer,autoincrement=True,primary_key=True)
    name = Column(String(32),nullable=False)
    status = Column(SmallInteger,default=1)
    addtime = Column(DateTime,default=datetime.now())
    update_time = Column(DateTime,default=datetime.now())
    channels = db.relationship('Channel',backref=db.backref('tag'),secondary=tag_channel)


'''
二级分类 比如说 CCTV-1  CCTV-2  湖南卫视 江苏卫视 浙江卫视  翡翠电视台 TVB
'''
class Channel(db.Model):
    __tablename__ = 'channel'
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(32), nullable=False)
    status = Column(SmallInteger, default=1)
    addtime = Column(DateTime, default=datetime.now())
    update_time = Column(DateTime, default=datetime.now())

db.create_all()

@app.route('/insert')
def insert():
    tag1 = Tag(name='央视频道')
    tag2 = Tag(name='卫视频道')
    cha1 = Channel(name='北京卫视')
    cha2 = Channel(name='湖南卫视')
    cha3 = Channel(name='CCTV-10')
    cha4 = Channel(name='CCTV-15')
    tag1.channels.append(cha3)
    tag1.channels.append(cha4)
    tag2.channels.append(cha1)
    tag2.channels.append(cha2)
    db.session.add(tag1)
    db.session.add(tag2)
    db.session.add(cha1)
    db.session.add(cha2)
    db.session.add(cha3)
    db.session.add(cha4)
    db.session.commit()

    return ''

@app.route('/get_channelinfo_by_tagname')
def get_channelinfo_by_tagname():
    tag =  Tag.query.filter_by(name='卫视频道',status=1).first()
    print(tag.channels)  # 得到一个 list [<Channel 3>, <Channel 4>]
    return ''

@app.route('/get_taginfo_by_channelname')
def get_taginfo_by_channelname():
    channel =  Channel.query.filter_by(name='CCTV-10',status=1).first()
    if channel.tag:
        print(channel.tag) #得到一个 list [<Tag 1>]
    return ''


if __name__ == '__main__':
    app.run(debug=True)