flask-19 Flask-SQLAlchemy多对多(many-to-many)关系

如果您想要用多对多关系，您需要定义一个用于关系的辅助表。对于这个辅助表， 强烈建议 不 使用模型，而是采用一个实际的表:

tags = db.Table('tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('page_id', db.Integer, db.ForeignKey('page.id'))
)

class Page(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tags = db.relationship('Tag', secondary=tags,
        backref=db.backref('pages', lazy='dynamic'))

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)

这里我们配置 Page.tags 加载后作为标签的列表，因为我们并不期望每页出现太多的标签。而每个 tag 的页面列表（ Tag.pages）是一个动态的反向引用。 正如上面提到的，这意味着您会得到一个可以发起 select 的查询对象。

 在教程

基础下实现

一、模型创建

1、apps下创建goods

2、apps/goods下创建models.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/12/17 17:23
# @Author  : niubobo
# @File    : models.py
# @Software: PyCharm
from datetime import datetime
from ext import db


class Goods(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    gname = db.Column(db.String(100), nullable=False)
    price = db.Column(db.Float, nullable=False)

    def __str__(self):
        return self.gname


# 关系表
class User_goods(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    good_id = db.Column(db.Integer, db.ForeignKey('goods.id'), nullable=False)
    number = db.Column(db.Integer, default=1)

 3、修改app.py

from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from apps import create_app
from ext import db
from apps.user.models import User
from apps.article.models import Article
from apps.goods.models import *


app = create_app()
print(app)
manager = Manager(app=app)

# 命令工具
migrate = Migrate(app=app, db=db)
# 添加命令到manager
manager.add_command('db', MigrateCommand)


@manager.command
def init():
    print('初始化')


if __name__ == '__main__':
    manager.run()

 4、生成模型库表

终端输入命令执行：

 
python app.py db migrate
 
python app.py db upgrade

 5、做一点数据在数据库当中执行脚本

insert into `goods` (`gname`, `price`) values('手机','5899');
insert into `goods` (`gname`, `price`) values('笔记本','9999');
insert into `goods` (`gname`, `price`) values('耳机','199');
insert into `goods` (`gname`, `price`) values('手环','399');
insert into `goods` (`gname`, `price`) values('键盘','499');
insert into `goods` (`gname`, `price`) values('鼠标','99');

二、应用（商城例子）

1、在apps/goods下创建view.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/12/17 17:51
# @Author  : niubobo
# @File    : view.py
# @Software: PyCharm
from flask import Blueprint, request, render_template, redirect, url_for
from sqlalchemy import or_, and_

from apps.goods.models import Goods, User_goods
from apps.user.models import User
from ext import db

goods_bp = Blueprint('goods', __name__)

# 用户找商品 （xxx用户买了哪些商品）
@goods_bp.route('/findgoods')
def find_goods():
    pass


# 根据商品找用户 （xxx商品哪些人买了）
@goods_bp.route('/finduser')
def find_user():
    pass


# 商品展示列表
@goods_bp.route('/show')
def show():
    users = User.query.filter(User.isdelete == False).all()
    goods_list = Goods.query.all()
    return  render_template('goods/show.html', users=users,goods_list=goods_list)


# 用户购买商品
@goods_bp.route('/buy')
def buy():
    uid = request.args.get('uid')
    gid = request.args.get('gid')
    ug = User_goods()
    ug.user_id = uid
    ug.good_id = gid
    db.session.add(ug)
    db.session.commit()
    return  redirect(url_for('goods.show'))

2、修改apps下__init__.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/12/17 13:05
# @Author  : niubobo
# @File    : __init__.py.py
# @Software: PyCharm

from flask import Flask
import settings
from apps.article.view import article_bp
from apps.goods.view import goods_bp
from apps.user.view import user_bp
from ext import db


def create_app():
    app = Flask(__name__, template_folder='../templates', static_folder='../static')
    # 加载配置
    app.config.from_object(settings.DevelopmentConfig)
    # 将db对象与app进行关联
    db.init_app(app=app)
    # 蓝图 ,将蓝图对象绑定到app上
    app.register_blueprint(user_bp)
    app.register_blueprint(article_bp)
    app.register_blueprint(goods_bp)
    return app


if __name__ == '__main__':
    app = create_app()
    app.run()

3、templates下新增goods文件夹

4、templates/goods下新增show.html

{% extends 'base.html' %}
{% block title %}
    购物界面
{% endblock %}
{% block jquery %}
    <script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
{% endblock %}
{% block middle %}

    <form action="{{ url_for('user.register') }}" method="post">
        <p>
            <select name="uid">
                <option value="0">请选择购物用户--必须选一个</option>
                {% for user in users %}
                    <option value="{{ user.id }}">{{ user.username }}</option>
                {% endfor %}

            </select>
        </p>
        <p>
        <table border="1" cellspacing="0" width="50%">
            <tr>
                <th>序号</th>
                <th>商品名</th>
                <th>价格</th>
                <th>操作</th>
            </tr>
            {% for goods in goods_list %}
                <tr>
                    <td>{{ loop.index }}</td>
                    <td>{{ goods.gname }}</td>
                    <td>{{ goods.price }}</td>
                    <td>
                        <input type="button" class="btnbuy" value="购买" tag="{{ goods.id }}"/>

                    </td>
                </tr>

            {% endfor %}

        </table>
        </p>

    </form>
{% endblock %}

{% block myjs %}
    <script>
        //购买
    $('.btnbuy').click(function () {
        goods_id = $(this).attr('tag');
        user_id = $("select[name='uid']").val();
        console.log(goods_id,user_id) // 可以注释掉
        location.href = '{{ url_for('goods.buy') }}?uid='+user_id+'&gid='+goods_id;
         // http://ip:端口/search?search=输入的值
    })

    </script>
{% endblock %}

5、base.html添加商城验证购买逻辑

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>
        {% block title %} 父模板的title{% endblock %}
    </title>
    <style>
        #head{
            height: 50px;
            background-color: bisque;
        }

        #head ul{
            list-style: none;
            height: 50px;
        }
        #head ul li{
            float: left;
            width: 100px;
            text-align: center;
            font-size: 15px;
            line-height: 50px;
        }

        #middle{
            height: 100%;
            background-color: azure;
        }
        #foot{
            height: 50px;
            line-height: 50px;
            background-color: darkolivegreen;
        }
    </style>
    {%  block modle_style %}{%  endblock %}
    {% block jquery %}{% endblock %}

</head>
<body>
<div id="head">
    <ul>
        <li><a href="{{ url_for('user.user_center') }}">首页</a></li>
        <li><a href="{{ url_for('user.register') }}">注册</a></li>
        <li><a href="{{ url_for('user.login') }}">登录</a></li>
        <li><a href="{{ url_for('article.all_article') }}">博客</a></li>
        <li><a href="{{ url_for('goods.show') }}">商城</a></li>

    </ul>
</div>

<div id="middle">
    {% block middle %} 中间{% endblock %}
</div>
<div id="foot">
    来自春天的菠菜问候
</div>
{% block myjs %}{% endblock %}
</body>
</html>

为上图商品添加链接查看哪些人购买了此商品

6、修改templates/goods下show.html

{% extends 'base.html' %}
{% block title %}
    购物界面
{% endblock %}
{% block jquery %}
    <script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
{% endblock %}
{% block middle %}

    <form action="{{ url_for('user.register') }}" method="post">
        <p>
            <select name="uid">
                <option value="0">请选择购物用户--必须选一个</option>
                {% for user in users %}
                    <option value="{{ user.id }}">{{ user.username }}</option>
                {% endfor %}

            </select>
        </p>
        <p>
        <table border="1" cellspacing="0" width="50%">
            <tr>
                <th>序号</th>
                <th>商品名</th>
                <th>价格</th>
                <th>操作</th>
            </tr>
            {% for goods in goods_list %}
                <tr>
                    <td>{{ loop.index }}</td>
                    <td><a href="{{ url_for('goods.find_user') }}?gid={{ goods.id }}">{{ goods.gname }}</a></td>
                    <td>{{ goods.price }}</td>
                    <td>
                        <input type="button" class="btnbuy" value="购买" tag="{{ goods.id }}"/>

                    </td>
                </tr>

            {% endfor %}

        </table>
        </p>

    </form>
{% endblock %}

{% block myjs %}
    <script>
        //购买
    $('.btnbuy').click(function () {
        goods_id = $(this).attr('tag');
        user_id = $("select[name='uid']").val();

        console.log(goods_id,user_id) // 可以注释掉
        location.href = '{{ url_for('goods.buy') }}?uid='+user_id+'&gid='+goods_id;
         // http://ip:端口/search?search=输入的值
    })

    </script>
{% endblock %}

7、新增templates/goods下finduser.html

{% extends 'base.html' %}
{% block title %}
    根据商品找用户
{% endblock %}
{% block middle %}

商品名：{{ goods.gname }}
 <br>
 购买此商品的用户有：
    {% for user in goods.users %}
    <p>{{ user.username }}</p>
    {% endfor %}

{% endblock %}

8、修改apps/goods下view.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/12/17 17:51
# @Author  : niubobo
# @File    : view.py
# @Software: PyCharm
from flask import Blueprint, request, render_template, redirect, url_for
from sqlalchemy import or_, and_

from apps.goods.models import Goods, User_goods
from apps.user.models import User
from ext import db

goods_bp = Blueprint('goods', __name__)

# 用户找商品 （xxx用户买了哪些商品）
@goods_bp.route('/findgoods')
def find_goods():
    pass


# 根据商品找用户 （xxx商品哪些人买了）
@goods_bp.route('/finduser')
def find_user():
    goods_id = request.args.get('gid')
    goods = Goods.query.get(goods_id)
    return render_template('goods/finduser.html', goods=goods)


# 商品展示列表
@goods_bp.route('/show')
def show():
    users = User.query.filter(User.isdelete == False).all()
    goods_list = Goods.query.all()
    return  render_template('goods/show.html', users=users,goods_list=goods_list)


# 用户购买商品
@goods_bp.route('/buy')
def buy():
    uid = request.args.get('uid')
    gid = request.args.get('gid')
    ug = User_goods()
    ug.user_id = uid
    ug.good_id = gid
    db.session.add(ug)
    db.session.commit()
    return  redirect(url_for('goods.show'))

9、修改apps/goods下models.py

多对多的关系体现在上图 secondary表示两张表的关系表 user_goods是数据库表名，要注意

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/12/17 17:23
# @Author  : niubobo
# @File    : models.py
# @Software: PyCharm
from datetime import datetime
from ext import db


class Goods(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    gname = db.Column(db.String(100), nullable=False)
    price = db.Column(db.Float, nullable=False)
    users = db.relationship('User', backref='goodslist', secondary='user_goods')
    def __str__(self):
        return self.gname


# 关系表
class User_goods(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    good_id = db.Column(db.Integer, db.ForeignKey('goods.id'), nullable=False)
    number = db.Column(db.Integer, default=1)

10、验证商品找用户

 根据用户找商品

11、修改templates/goods下finduser.html

{% extends 'base.html' %}
{% block title %}
    根据商品找用户
{% endblock %}
{% block middle %}

商品名：{{ goods.gname }}
 <br>
 购买此商品的用户有：
    {% for user in goods.users %}
    <p><a href="{{ url_for('goods.find_goods') }}?uid={{ user.id }}">{{ user.username }}</a></p>
    {% endfor %}

{% endblock %}

12、新增templates/goods下findgoods.html

{% extends 'base.html' %}
{% block title %}
    根据用户找商品
{% endblock %}
{% block middle %}

用户名：{{ user.username }}
 <br>
 此用户购买的商品如下：
    {% for goods in user.goodslist %}
    <p>{{ goods.gname }}--{{ goods.price }}</p>
    {% endfor %}

{% endblock %}

13、修改apps/goods下view.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/12/17 17:51
# @Author  : niubobo
# @File    : view.py
# @Software: PyCharm
from flask import Blueprint, request, render_template, redirect, url_for
from sqlalchemy import or_, and_

from apps.goods.models import Goods, User_goods
from apps.user.models import User
from ext import db

goods_bp = Blueprint('goods', __name__)

# 用户找商品 （xxx用户买了哪些商品）
@goods_bp.route('/findgoods')
def find_goods():
    user_id = request.args.get('uid')
    user = User.query.get(user_id)
    return render_template('goods/findgoods.html', user=user)


# 根据商品找用户 （xxx商品哪些人买了）
@goods_bp.route('/finduser')
def find_user():
    goods_id = request.args.get('gid')
    goods = Goods.query.get(goods_id)
    return render_template('goods/finduser.html', goods=goods)


# 商品展示列表
@goods_bp.route('/show')
def show():
    users = User.query.filter(User.isdelete == False).all()
    goods_list = Goods.query.all()
    return  render_template('goods/show.html', users=users,goods_list=goods_list)


# 用户购买商品
@goods_bp.route('/buy')
def buy():
    uid = request.args.get('uid')
    gid = request.args.get('gid')
    ug = User_goods()
    ug.user_id = uid
    ug.good_id = gid
    db.session.add(ug)
    db.session.commit()
    return  redirect(url_for('goods.show'))

14、验证用户买商品

三、应用（文章评论表）

 1、修改apps/article下 models.py

 2、修改app.py

from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from apps import create_app
from ext import db
from apps.user.models import User
from apps.article.models import *
from apps.goods.models import *


app = create_app()
print(app)
manager = Manager(app=app)

# 命令工具
migrate = Migrate(app=app, db=db)
# 添加命令到manager
manager.add_command('db', MigrateCommand)


@manager.command
def init():
    print('初始化')


if __name__ == '__main__':
    manager.run()

3、生成模型库表

终端执行命令

python app.py db migrate
 
python app.py db upgrade

4、由于之前article有内容，所有外键没有设置非空，这里处理一下，不然上面执行会有问题

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/12/17 13:14
# @Author  : niubobo
# @File    : models.py
# @Software: PyCharm
from datetime import datetime
from ext import db


class Article_type(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    type_name = db.Column(db.String(20), nullable=False)
    articles = db.relationship('Article',backref='article_type')


class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(50), nullable=False)
    content = db.Column(db.Text, nullable=False)
    pdatetime = db.Column(db.DateTime, default=datetime.now)
    click_num = db.Column(db.Integer, default=0)
    save_num = db.Column(db.Integer, default=0)
    love_num = db.Column(db.Integer, default=0)
    #  外键
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    type_id = db.Column(db.Integer, db.ForeignKey('article_type.id'), nullable=False)
    comments = db.relationship('Comment', backref='article')


class Comment(db.Model):
    # 自定义表的名字
    __tablename__ = 'comment'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    comment = db.Column(db.String(255),nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    article_id = db.Column(db.Integer, db.ForeignKey('article.id'))
    cdatetime = db.Column(db.DateTime, default=datetime.now)

    def __str__(self):
        return self.comment

 5、再次生成模型库表

终端执行命令

python app.py db migrate
 
python app.py db upgrade

后续见flask-20 flask-bootstrap 搭建了