Flask使用Flask-SQLAlchemy操作MySQL数据库(四)——联表多对多查询

一、联表多对多查询示例

一个简单的博客文章系统。特点是:一篇文章可以有多个分类和多个标签,一个分类或标签下可以有多篇文章。可以获取指定分类或标签下的所有文章。

 

1、数据表

(1)文章表(post)

DROP TABLE IF EXISTS `post`;
CREATE TABLE `post` (
  `post_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` int(4) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`post_id`),
  KEY `type_status_date` (`post_status`,`post_date`,`post_id`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

(2)分类标签表(term)

DROP TABLE IF EXISTS `term`;
CREATE TABLE `term` (
  `term_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `term_name` varchar(50) NOT NULL,
  `term_title` varchar(50) NOT NULL,
  `term_type` varchar(10) NOT NULL,
  PRIMARY KEY (`term_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

(3)文章与分类标签对应关系表(posterm)

DROP TABLE IF EXISTS `posterm`;
CREATE TABLE `posterm` (
  `posterm_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL,
  `term_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`posterm_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

(4)用户表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_password` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_nickname` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`),
  KEY `user_email` (`user_email`),
  KEY `user_name` (`user_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

 

2、API接口

获取所有文章数据[GET]:

http://127.0.0.1:5000/posts

 

获取某一个分类或标签下的文章数据[GET]:

http://127.0.0.1:5000/posts/1

 

添加一条文章数据[POST]:

http://127.0.0.1:5000/post

 

删除一条文章数据[DELETE]:

http://127.0.0.1:5000/post/1

 

修改一条文章数据[PATCH]:

http://127.0.0.1:5000/post/1

 

查询一条文章数据[GET]:

http://127.0.0.1:5000/post/1

 

二、接口实现

1、定义数据表模型

(1)用户表数据模型

class User(mydb.Model):
    user_id = mydb.Column(mydb.Integer, primary_key=True)
    user_name = mydb.Column(mydb.String(60), nullable=False)
    user_password = mydb.Column(mydb.String(30), nullable=False)
    user_nickname = mydb.Column(mydb.String(50), nullable=False)
    user_email = mydb.Column(mydb.String(100), nullable=False)
    postes = mydb.relationship('Post', backref='user', lazy=True)
    def __repr__(self):
        return '<User %r>' % self.user_nickname

(2)文章表数据模型

class Post(mydb.Model):
    post_id = mydb.Column(mydb.Integer, primary_key=True)
    post_date = mydb.Column(mydb.DateTime, nullable=False)
    post_content = mydb.Column(mydb.Text, nullable=False)
    post_title = mydb.Column(mydb.String(100), nullable=False)
    post_excerpt = mydb.Column(mydb.Text, nullable=False)
    post_status = mydb.Column(mydb.String(20), nullable=False)
    post_modified = mydb.Column(mydb.DateTime, nullable=False)
    post_author = mydb.Column(mydb.Integer, mydb.ForeignKey('user.user_id'), nullable=False)
    def __repr__(self):
        return '<Post %r>' % self.post_title

(3)文章分类标签表数据模型

class Term(mydb.Model):
    term_id = mydb.Column(mydb.Integer, primary_key=True)
    term_name = mydb.Column(mydb.String(50), nullable=False)
    term_title = mydb.Column(mydb.String(50), nullable=False)
    term_type = mydb.Column(mydb.String(10), nullable=False)
    def __repr__(self):
        return '<Term %r>' % self.term_title

(4)文章与分类标签的关系表数据模型

class Posterm(mydb.Model):
    __tablename__ = 'posterm'
    posterm_id = mydb.Column(mydb.Integer, primary_key=True)
    post_id = mydb.Column(mydb.Integer, nullable=False)
    term_id = mydb.Column(mydb.Integer, nullable=False)

 

2、添加文章与分类的对应记录与删除文章与分类的对应记录(函数)

# 添加文章与分类的对应记录
def addPosterm(postId, termId):
    posterm = Posterm(post_id=postId, term_id=termId)
    try:
        mydb.session.add(posterm)
        mydb.session.commit()
    except:
        mydb.session.rollback()
        mydb.session.flush()

    postermId = posterm.posterm_id
    if (postermId is None):
        result = {'msg': '添加失败'}
        return jsonify(data=False)

    return postermId


# 删除文章与分类的对应记录
def delPosterm(postermId):
    Posterm.query.filter_by(posterm_id=postermId).delete()
    mydb.session.commit()

 

3、获取所有文章数据

@app.route('/posts', methods=['GET'])
def getPosts():
    postData = mydb.session \
        .query(Post.post_id, Post.post_author, Post.post_date, Post.post_title, Post.post_content, Post.post_excerpt,
               Post.post_status, Post.post_modified, User.user_name, User.user_nickname) \
        .join(User, Post.post_author == User.user_id)

    termData = Term.query.all()
    postermData = Posterm.query.all()

    datas = []
    for post in postData:
        post_terms = []
        post_tags = []
        post_cates = []

        for posterm in postermData:
            if (posterm.post_id == post.post_id):
                post_terms.append(posterm.term_id)

        if (len(post_terms) > 0):
            for post_term in post_terms:
                for term in termData:
                    if (term.term_id == post_term):
                        if (term.term_type == 'category'):
                            post_cates.append(
                                {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                                 'term_type': term.term_type})
                        elif (term.term_type == 'tag'):
                            post_tags.append(
                                {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                                 'term_type': term.term_type})

        datas.append({'post_id': post.post_id, 'post_author': post.post_author, 'post_author_name': post.user_name,
                      'post_author_nickname': post.user_nickname, 'post_date': post.post_date,
                      'post_title': post.post_title,
                      'post_content': post.post_content, 'post_excerpt': post.post_excerpt,
                      'post_status': post.post_status,
                      'post_modified': post.post_modified, 'post_cates': post_cates, 'post_tags': post_tags})
    return jsonify(data=datas)

 

4、获取某一个分类或标签下的文章数据

@app.route('/posts/<int:termId>', methods=['GET'])
def filterPosts(termId):
    posts = []
    postermData = Posterm.query.filter_by(term_id=termId).all()
    termData = Term.query.all()
    for posterm in postermData:
        post = mydb.session.query(Post.post_id, Post.post_author, Post.post_date, Post.post_title, Post.post_content, Post.post_excerpt,
               Post.post_status, Post.post_modified, User.user_name, User.user_nickname).filter_by(post_id=posterm.post_id).join(User, Post.post_author == User.user_id).first()

        post_cates = []
        post_tags = []
        post_terms = Posterm.query.filter_by(post_id=post.post_id).all()

        if (len(post_terms) > 0):
            for post_term in post_terms:
                for term in termData:
                    if (term.term_id == post_term.term_id):
                        if (term.term_type == 'category'):
                            post_cates.append(
                                {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                                 'term_type': term.term_type})
                        elif (term.term_type == 'tag'):
                            post_tags.append(
                                {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                                 'term_type': term.term_type})

        posts.append({'post_id': post.post_id, 'post_author': post.post_author, 'post_author_name': post.user_name,
                      'post_author_nickname': post.user_nickname, 'post_date': post.post_date,
                      'post_title': post.post_title,
                      'post_content': post.post_content, 'post_excerpt': post.post_excerpt,
                      'post_status': post.post_status,
                      'post_modified': post.post_modified, 'post_cates': post_cates, 'post_tags': post_tags})

    return jsonify(data=posts)

 

5、查询一条文章数据

@app.route('/post/<int:postId>', methods=['GET'])
def getPost(postId):
    post = mydb.session.query(Post.post_id, Post.post_author, Post.post_date, Post.post_title, Post.post_content,
                              Post.post_excerpt,
                              Post.post_status, Post.post_modified, User.user_name, User.user_nickname)\
        .filter_by(post_id=postId)\
        .join(User, Post.post_author == User.user_id)\
        .first()

    termData = Term.query.all()

    post_cates = []
    post_tags = []
    post_terms = Posterm.query.filter_by(post_id=postId).all()

    if (len(post_terms) > 0):
        for post_term in post_terms:
            for term in termData:
                if (term.term_id == post_term.term_id):
                    if (term.term_type == 'category'):
                        post_cates.append(
                            {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                             'term_type': term.term_type})
                    elif (term.term_type == 'tag'):
                        post_tags.append(
                            {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                             'term_type': term.term_type})

    postInfo = {
        'post_id': post.post_id, 'post_author': post.post_author, 'post_author_name': post.user_name,
        'post_author_nickname': post.user_nickname, 'post_date': post.post_date,
        'post_title': post.post_title,
        'post_content': post.post_content, 'post_excerpt': post.post_excerpt,
        'post_status': post.post_status,
        'post_modified': post.post_modified, 'post_cates': post_cates, 'post_tags': post_tags
    }

    return jsonify(data=postInfo)

 

6、添加一条文章数据

@app.route('/post', methods=['POST'])
def addPost():
    post_author = request.form.get('post_author')
    post_content = request.form.get('post_content')
    post_title = request.form.get('post_title')
    post_excerpt = request.form.get('post_excerpt')
    post_status = request.form.get('post_status')
    post_cates = request.form.get('post_cates')
    post_tags = request.form.get('post_tags')

    if (post_status is None):
        post_status = 'publish'

    now = datetime.datetime.now()
    now.strftime('%Y-%m-%d %H:%M:%S')
    post_date = now
    post_modified = now

    post = Post(post_author=post_author, post_date=post_date, post_content=post_content, post_title=post_title, post_excerpt=post_excerpt, post_status=post_status, post_modified=post_modified)
    try:
        mydb.session.add(post)
        mydb.session.commit()
    except:
        mydb.session.rollback()
        mydb.session.flush()

    postId = post.post_id

    if (post_cates is None or post_cates == ''):
        print('cate is none')
        # 没有选类别,默认设置第一个分类——“未分类”
        addPosterm(postId, 1)
    else:
        tmp_cates = post_cates.split(',')
        for tmp_cate in tmp_cates:
            addPosterm(postId, int(tmp_cate))

    if (post_tags is None or post_cates == ''):
        print('no tag')
        # 没有选标签,不处理
    else:
        tmp_tags = post_tags.split(',')
        for tmp_tag in tmp_tags:
            addPosterm(postId, int(tmp_tag))

    return getPost(postId)

 

7、修改一条文章数据

@app.route('/post/<int:postId>', methods=['PATCH'])
def updatePost(postId):
    post_author = request.form.get('post_author')
    post_content = request.form.get('post_content')
    post_title = request.form.get('post_title')
    post_excerpt = request.form.get('post_excerpt')
    post_status = request.form.get('post_status')
    post_cates = request.form.get('post_cates')
    post_tags = request.form.get('post_tags')

    if (post_status is None):
        post_status = 'publish'

    now = datetime.datetime.now()
    now.strftime('%Y-%m-%d %H:%M:%S')
    post_modified = now

    try:
        post = Post.query.filter_by(post_id=postId).first()
        if (post is None):
            result = {'msg': '找不到要修改的文章记录'}
            return jsonify(data=result)
        else:
            post.post_author = post_author
            post.post_content = post_content
            post.post_title = post_title
            post.post_excerpt = post_excerpt
            post.post_status = post_status
            post.post_modified = post_modified
            mydb.session.commit()
    except:
        mydb.session.rollback()
        mydb.session.flush()

    postId = post.post_id


    # 如果没传类别也没传标签,则不修改类别和标签
    if (post_cates is None or post_cates == '' or post_tags is None or post_tags == ''):
        pass
    else:
        post_terms_cates = post_cates.split(',')
        post_terms_tags = post_tags.split(',')
        post_terms_list = post_terms_cates + post_terms_tags

        post_terms = mydb.session.query(Posterm.posterm_id, Posterm.post_id, Posterm.term_id, Term.term_name, Term.term_title, Term.term_type).filter_by(
            post_id=postId).join(Term, Posterm.term_id == Term.term_id).all()

        # 删除原来的对应数据
        for post_term in post_terms:
            delPosterm(post_term.posterm_id)

        # 将传过来的分类和标签新增到关系表中
        for id in post_terms_list:
            addPosterm(postId, id)

    return getPost(postId)

 

8、删除一条文章数据

@app.route('/post/<int:postId>', methods=['DELETE'])
def delPost(postId):
    # 删除文章
    Post.query.filter_by(post_id=postId).delete()
    mydb.session.commit()

    # 删除文章和分类标签的对应关系
    post_terms = mydb.session.query(Posterm.posterm_id, Posterm.post_id, Posterm.term_id)\
        .filter_by(post_id=postId).all()
    for post_term in post_terms:
        delPosterm(post_term.posterm_id)

    return getPosts()

 

至此,关于所有接口都已实现,程序完整代码如下:

import configparser, datetime
from flask_sqlalchemy import SQLAlchemy
from flask import Flask, jsonify, request

app = Flask(__name__)

my_config = configparser.ConfigParser()
my_config.read('db.conf')

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://' + my_config.get('DB', 'DB_USER') + ':' + my_config.get('DB',
                                                                                                          'DB_PASSWORD') + '@' + my_config.get(
    'DB', 'DB_HOST') + '/' + my_config.get('DB', 'DB_DB')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

mydb = SQLAlchemy()
mydb.init_app(app)


class Posterm(mydb.Model):
    __tablename__ = 'posterm'
    posterm_id = mydb.Column(mydb.Integer, primary_key=True)
    post_id = mydb.Column(mydb.Integer, nullable=False)
    term_id = mydb.Column(mydb.Integer, nullable=False)


class Post(mydb.Model):
    post_id = mydb.Column(mydb.Integer, primary_key=True)
    post_date = mydb.Column(mydb.DateTime, nullable=False)
    post_content = mydb.Column(mydb.Text, nullable=False)
    post_title = mydb.Column(mydb.String(100), nullable=False)
    post_excerpt = mydb.Column(mydb.Text, nullable=False)
    post_status = mydb.Column(mydb.String(20), nullable=False)
    post_modified = mydb.Column(mydb.DateTime, nullable=False)
    post_author = mydb.Column(mydb.Integer, mydb.ForeignKey('user.user_id'), nullable=False)
    def __repr__(self):
        return '<Post %r>' % self.post_title


class Term(mydb.Model):
    term_id = mydb.Column(mydb.Integer, primary_key=True)
    term_name = mydb.Column(mydb.String(50), nullable=False)
    term_title = mydb.Column(mydb.String(50), nullable=False)
    term_type = mydb.Column(mydb.String(10), nullable=False)
    def __repr__(self):
        return '<Term %r>' % self.term_title


class User(mydb.Model):
    user_id = mydb.Column(mydb.Integer, primary_key=True)
    user_name = mydb.Column(mydb.String(60), nullable=False)
    user_password = mydb.Column(mydb.String(30), nullable=False)
    user_nickname = mydb.Column(mydb.String(50), nullable=False)
    user_email = mydb.Column(mydb.String(100), nullable=False)
    postes = mydb.relationship('Post', backref='user', lazy=True)
    def __repr__(self):
        return '<User %r>' % self.user_nickname


@app.route('/')
def index():
    html = '<h1>Flask RESTful API</h1>'
    html += '<p>获取所有文章数据[GET]:<br />http://127.0.0.1:5000/posts</p>'
    html += '<p>获取某一个分类或标签下的文章数据[GET]:<br />http://127.0.0.1:5000/posts/1</p>'
    html += '<p>添加一条文章数据[POST]:<br />http://127.0.0.1:5000/post</p>'
    html += '<p>删除一条文章数据[DELETE]:<br />http://127.0.0.1:5000/post/1</p>'
    html += '<p>修改一条文章数据[PATCH]:<br />http://127.0.0.1:5000/post/1</p>'
    html += '<p>查询一条文章数据[GET]:<br />http://127.0.0.1:5000/post/1</p>'
    return html


# 查询所有文章
@app.route('/posts', methods=['GET'])
def getPosts():
    postData = mydb.session \
        .query(Post.post_id, Post.post_author, Post.post_date, Post.post_title, Post.post_content, Post.post_excerpt,
               Post.post_status, Post.post_modified, User.user_name, User.user_nickname) \
        .join(User, Post.post_author == User.user_id)

    termData = Term.query.all()
    postermData = Posterm.query.all()

    datas = []
    for post in postData:
        post_terms = []
        post_tags = []
        post_cates = []

        for posterm in postermData:
            if (posterm.post_id == post.post_id):
                post_terms.append(posterm.term_id)

        if (len(post_terms) > 0):
            for post_term in post_terms:
                for term in termData:
                    if (term.term_id == post_term):
                        if (term.term_type == 'category'):
                            post_cates.append(
                                {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                                 'term_type': term.term_type})
                        elif (term.term_type == 'tag'):
                            post_tags.append(
                                {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                                 'term_type': term.term_type})

        datas.append({'post_id': post.post_id, 'post_author': post.post_author, 'post_author_name': post.user_name,
                      'post_author_nickname': post.user_nickname, 'post_date': post.post_date,
                      'post_title': post.post_title,
                      'post_content': post.post_content, 'post_excerpt': post.post_excerpt,
                      'post_status': post.post_status,
                      'post_modified': post.post_modified, 'post_cates': post_cates, 'post_tags': post_tags})
    return jsonify(data=datas)


# 查询某个分类/标签的文章
@app.route('/posts/<int:termId>', methods=['GET'])
def filterPosts(termId):
    posts = []
    postermData = Posterm.query.filter_by(term_id=termId).all()
    termData = Term.query.all()
    for posterm in postermData:
        post = mydb.session.query(Post.post_id, Post.post_author, Post.post_date, Post.post_title, Post.post_content, Post.post_excerpt,
               Post.post_status, Post.post_modified, User.user_name, User.user_nickname).filter_by(post_id=posterm.post_id).join(User, Post.post_author == User.user_id).first()

        post_cates = []
        post_tags = []
        post_terms = Posterm.query.filter_by(post_id=post.post_id).all()

        if (len(post_terms) > 0):
            for post_term in post_terms:
                for term in termData:
                    if (term.term_id == post_term.term_id):
                        if (term.term_type == 'category'):
                            post_cates.append(
                                {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                                 'term_type': term.term_type})
                        elif (term.term_type == 'tag'):
                            post_tags.append(
                                {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                                 'term_type': term.term_type})

        posts.append({'post_id': post.post_id, 'post_author': post.post_author, 'post_author_name': post.user_name,
                      'post_author_nickname': post.user_nickname, 'post_date': post.post_date,
                      'post_title': post.post_title,
                      'post_content': post.post_content, 'post_excerpt': post.post_excerpt,
                      'post_status': post.post_status,
                      'post_modified': post.post_modified, 'post_cates': post_cates, 'post_tags': post_tags})

    return jsonify(data=posts)


# 查询单篇文章
@app.route('/post/<int:postId>', methods=['GET'])
def getPost(postId):
    post = mydb.session.query(Post.post_id, Post.post_author, Post.post_date, Post.post_title, Post.post_content,
                              Post.post_excerpt,
                              Post.post_status, Post.post_modified, User.user_name, User.user_nickname)\
        .filter_by(post_id=postId)\
        .join(User, Post.post_author == User.user_id)\
        .first()

    termData = Term.query.all()

    post_cates = []
    post_tags = []
    post_terms = Posterm.query.filter_by(post_id=postId).all()

    if (len(post_terms) > 0):
        for post_term in post_terms:
            for term in termData:
                if (term.term_id == post_term.term_id):
                    if (term.term_type == 'category'):
                        post_cates.append(
                            {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                             'term_type': term.term_type})
                    elif (term.term_type == 'tag'):
                        post_tags.append(
                            {'term_id': term.term_id, 'term_name': term.term_name, 'term_title': term.term_title,
                             'term_type': term.term_type})

    postInfo = {
        'post_id': post.post_id, 'post_author': post.post_author, 'post_author_name': post.user_name,
        'post_author_nickname': post.user_nickname, 'post_date': post.post_date,
        'post_title': post.post_title,
        'post_content': post.post_content, 'post_excerpt': post.post_excerpt,
        'post_status': post.post_status,
        'post_modified': post.post_modified, 'post_cates': post_cates, 'post_tags': post_tags
    }

    return jsonify(data=postInfo)


# 添加文章
@app.route('/post', methods=['POST'])
def addPost():
    post_author = request.form.get('post_author')
    post_content = request.form.get('post_content')
    post_title = request.form.get('post_title')
    post_excerpt = request.form.get('post_excerpt')
    post_status = request.form.get('post_status')
    post_cates = request.form.get('post_cates')
    post_tags = request.form.get('post_tags')

    if (post_status is None):
        post_status = 'publish'

    now = datetime.datetime.now()
    now.strftime('%Y-%m-%d %H:%M:%S')
    post_date = now
    post_modified = now

    post = Post(post_author=post_author, post_date=post_date, post_content=post_content, post_title=post_title, post_excerpt=post_excerpt, post_status=post_status, post_modified=post_modified)
    try:
        mydb.session.add(post)
        mydb.session.commit()
    except:
        mydb.session.rollback()
        mydb.session.flush()

    postId = post.post_id

    if (post_cates is None or post_cates == ''):
        print('cate is none')
        # 没有选类别,默认设置第一个分类——“未分类”
        addPosterm(postId, 1)
    else:
        tmp_cates = post_cates.split(',')
        for tmp_cate in tmp_cates:
            addPosterm(postId, int(tmp_cate))

    if (post_tags is None or post_cates == ''):
        print('no tag')
        # 没有选标签,不处理
    else:
        tmp_tags = post_tags.split(',')
        for tmp_tag in tmp_tags:
            addPosterm(postId, int(tmp_tag))

    return getPost(postId)


# 修改文章
@app.route('/post/<int:postId>', methods=['PATCH'])
def updatePost(postId):
    post_author = request.form.get('post_author')
    post_content = request.form.get('post_content')
    post_title = request.form.get('post_title')
    post_excerpt = request.form.get('post_excerpt')
    post_status = request.form.get('post_status')
    post_cates = request.form.get('post_cates')
    post_tags = request.form.get('post_tags')

    if (post_status is None):
        post_status = 'publish'

    now = datetime.datetime.now()
    now.strftime('%Y-%m-%d %H:%M:%S')
    post_modified = now

    try:
        post = Post.query.filter_by(post_id=postId).first()
        if (post is None):
            result = {'msg': '找不到要修改的文章记录'}
            return jsonify(data=result)
        else:
            post.post_author = post_author
            post.post_content = post_content
            post.post_title = post_title
            post.post_excerpt = post_excerpt
            post.post_status = post_status
            post.post_modified = post_modified
            mydb.session.commit()
    except:
        mydb.session.rollback()
        mydb.session.flush()

    postId = post.post_id


    # 如果没传类别也没传标签,则不修改类别和标签
    if (post_cates is None or post_cates == '' or post_tags is None or post_tags == ''):
        pass
    else:
        post_terms_cates = post_cates.split(',')
        post_terms_tags = post_tags.split(',')
        post_terms_list = post_terms_cates + post_terms_tags

        post_terms = mydb.session.query(Posterm.posterm_id, Posterm.post_id, Posterm.term_id, Term.term_name, Term.term_title, Term.term_type).filter_by(
            post_id=postId).join(Term, Posterm.term_id == Term.term_id).all()

        # 删除原来的对应数据
        for post_term in post_terms:
            delPosterm(post_term.posterm_id)

        # 将传过来的分类和标签新增到关系表中
        for id in post_terms_list:
            addPosterm(postId, id)

    return getPost(postId)


# 删除文章
@app.route('/post/<int:postId>', methods=['DELETE'])
def delPost(postId):
    # 删除文章
    Post.query.filter_by(post_id=postId).delete()
    mydb.session.commit()

    # 删除文章和分类标签的对应关系
    post_terms = mydb.session.query(Posterm.posterm_id, Posterm.post_id, Posterm.term_id)\
        .filter_by(post_id=postId).all()
    for post_term in post_terms:
        delPosterm(post_term.posterm_id)

    return getPosts()


# 添加文章与分类的对应记录
def addPosterm(postId, termId):
    posterm = Posterm(post_id=postId, term_id=termId)
    try:
        mydb.session.add(posterm)
        mydb.session.commit()
    except:
        mydb.session.rollback()
        mydb.session.flush()

    postermId = posterm.posterm_id
    if (postermId is None):
        result = {'msg': '添加失败'}
        return jsonify(data=False)

    return postermId


# 删除文章与分类的对应记录
def delPosterm(postermId):
    Posterm.query.filter_by(posterm_id=postermId).delete()
    mydb.session.commit()


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

 

那时那我

随遇,随缘,随安,随喜!

发表评论

电子邮件地址不会被公开。 必填项已用*标注