7、flask创建一对一关系，一对多，多对多，关系表

本文链接：https://blog.youkuaiyun.com/panjunxiao/article/details/100712053

创建flask一对一关系表

from flask import Flask,render_template
from flask_sqlalchemy import SQLAlchemy
import os

app = Flask(__name__)
db = SQLAlchemy(app)
#配置-项目的根路径
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
# 连接mysql,要按照pymysql ; root:123456@127.0.0.1 用户名：密码@
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@127.0.0.1/onetoone'
# 显示mysql语句
# app.config['SQLALCHEMY_ECHO'] = True

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    name = db.Column(db.String(29))
    child = db.relationship("Child", backref='parent', uselist=False)
#      uselist=False 来约束, 因为在一对多中通过 查询得到的是一个列表,
# 所以, uselist=False禁用列表, 使我们只能查找到一个, 就构成了一对一关系
class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    name = db.Column(db.String(29))
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'),unique=True)

def func():
    # db.drop_all() #清空数据库
    db.create_all() #导入数据到数据库

def add1():
    parent = Parent(name='mama')
    db.session.add(parent)
    db.session.commit()
    child = Child(name='child',parent_id=parent.id)
    db.session.add(child)
    db.session.commit()

def update1():
    a = Child.query.filter_by(name='child').first()
    a.name = 'lala'
    db.session.commit()

if __name__ == '__main__':
    # func()
    # add1()
    update1()

一对多

# 一个人有 多个地址
class Person(db.Model):
    __tablename__='my_person'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    name = db.Column(db.String(80),unique=True)
    addresses = db.relationship('Address',backref='person',lazy='dynamic',cascade='all, delete-orphan')

class Address(db.Model):
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    address = db.Column(db.String(69))
    person_id = db.Column(db.Integer,db.ForeignKey('my_person.id'))

#删除表
def init():
    # db.drop_all()
    db.create_all()
# 添加
def add1():
    a = Person(name='yes')
    db.session.add(a)
    db.session.commit()
    addr1 = Address(address='广州',person_id=a.id)
    addr2 = Address(address='深圳',person_id=a.id)
    db.session.add(addr1)
    db.session.add(addr2)
    db.session.commit()
#修改
def update1():
    b = Person.query.get(2)
    b.name='panpan23'
    db.session.commit()

# 删除
def delete1():
#     级联删除只要主键被删除，外键也会被删除
    p = Person.query.get(1)
    db.session.delete(p)
    db.session.commit()

#查询
def select():
    person = Person.query.filter_by(name='panpan').first()
    print(person.name)
    print(person.id)
    adrs = Address.query.filter_by(person_id=person.id)
    for adr in adrs:
        print(adr.address)

if __name__ == '__main__':
    # init()
    # add1()
    update1()
    # delete1()
    # select()

多对多

tec_stu = db.Table('tec_stu',
    db.Column('student_id', db.Integer, db.ForeignKey('student.id')),
    db.Column('teacher_id', db.Integer, db.ForeignKey('teacher.id')),
)

class Teacher(db.Model):
    id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    name = db.Column(db.String(10))
    students = db.relationship('Student', secondary=tec_stu,backref=db.backref('teachers', lazy='dynamic'),cascade='all')

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    name = db.Column(db.String(29))

def init():
    # db.drop_all()
    db.create_all()

def add1():
    # 先添加学生
    stu = Student(name='aa')
    stu2 = Student(name='bb')
    db.session.add(stu)
    db.session.commit()
    # 筛选老师
    tec = Teacher.query.filter_by(name='sdfr').first()
    # tec = Teacher(name='sdfr')
    db.session.add(tec) # 添加老师
    # 给老师添加学生
    tec.students.append(stu)
    tec.students.append(stu2)
    # 之后再提交
    db.session.commit()

def delete1():
    #     删除指定老师和所有的学生,级联情况下
    q = Teacher.query.get(1)
    db.session.delete(q)
    db.session.commit()
    #    删除某个学生,外键表对应的信息也会删除
    # s = Student.query.filter_by(name='aa').first()
    # db.session.delete(s)
    # db.session.commit()

def update():
    d = Teacher.query.filter_by(name='sdfr').first()
    d.name = 'lala'
    db.session.commit()

if __name__ == '__main__':
    # init()
    # add1()
    # delete1()
    update()

复杂关系表

# **一个用户有多篇文章，文章与文章类型多对多**

class User(db.Model):
    __tablename__ = 'blog_user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False, unique=True)
    password = db.Column(db.String(100), nullable=False)
    articles = db.relationship('Article', backref='user', lazy='dynamic', cascade='all,delete-orphan')


blog_articletype_article = db.Table(
    'blog_articletype_article',
    db.Column('id', db.Integer, primary_key=True),
    db.Column('articletype_id', db.Integer, db.ForeignKey('blog_articletype.id')),
    db.Column('article_id', db.Integer, db.ForeignKey('blog_article.id'))
)


class ArticleType(db.Model):
    __tablename__ = 'blog_articletype'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False, unique=True)
    articles = db.relationship(
        'Article',
        secondary=blog_articletype_article,
        backref=db.backref('articletypes', lazy='dynamic')
    )


class Article(db.Model):
    __tablename__ = 'blog_article'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False, unique=True)
    content = db.Column(db.Text, nullable=False)
    publishdate = db.Column(db.DateTime, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('blog_user.id'))


# db.create_all()

@app.route(rule='/',methods=['get'])
def index():
    return render_template('home.html')

@app.route(rule='/register/',methods=['get'])
def register_get():
    return render_template('register.html')

@app.route(rule='/register/',methods=['POST'])
def register_post():
    name = request.form.get('name')
    password = request.form.get('pwd')
    if not password or not name :
        return '请输入用户名和密码'
    user = User(name=name,password=password)
    db.session.add(user)
    db.session.commit()
    return '注册成功'

@app.route(rule='/login/',methods=['get'])
def login_get():
    return render_template('login.html')

@app.route(rule='/login/',methods=['POST'])
def login_post():
    name = request.form.get('name')
    password = request.form.get('pwd')
    if not password or not name :
        return '请输入用户名和密码'
    user = User.query.filter(User.name==name,User.password==password).first()
    if user:
        return '登录成功'
    else:
        return '用户名不存在或密码错误'


if __name__ == '__main__':
    app.run(host='127.0.0.1',port=8888,debug=True)