sqlalchemy一对多的关系表案例

最新推荐文章于 2023-08-08 16:44:09 发布

水痕01

最新推荐文章于 2023-08-08 16:44:09 发布

阅读量972

点赞数

分类专栏： python 文章标签： python

本文链接：https://blog.csdn.net/kuangshp128/article/details/61617043

版权

python 专栏收录该内容

75 篇文章 3 订阅

订阅专栏

本案例中直接使用sqlalchemy，没有使用flask-sqlalchemy来做的

1.引入包文件

# coding:utf-8
from flask import Flask, render_template,url_for,redirect
import flask
from sqlalchemy import create_engine, Column, String, Integer, Text, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
import datetime

app = Flask(__name__)
app.debug = True

DB_URI = "mysql+mysqldb://root:root@127.0.0.1:3306/python?charset=utf8"
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()

2.创建一个用户表的映射

# 创建一个用户表的映射
class Author(Base):
    __tablename__ = "author"
    authorid = Column(Integer, primary_key=True, autoincrement=True)
    authorname = Column(String(100), nullable=False)

    def __repr__(self):
        return "<Author authorid='%s' authorname='%s'>"%(self.authorid,self.authorname)

3.创建文章表的映射

# 创建一个文章表的映射
class Article(Base):
    __tablename__ = "article"
    articleid = Column(Integer, primary_key=True, autoincrement=True)
    articletitle = Column(String(100), nullable=False)
    articlecontent = Column(Text)
    createTime = Column(DateTime, default=datetime.datetime.now())

    # 创建作者一对多的映射
    author_id = Column(Integer, ForeignKey("author.authorid"))
    #创建反向查找的(用户表可以根据articles来查找文章)
    author = relationship("Author", backref="articles")

    def __repr__(self):
        return "<Article author_id='%s' author='%s'>"%(self.author_id,self.author)

4.创建数据库

# 创建数据表
Base.metadata.create_all()

5.路由页面展示

# 主页面路由
@app.route('/')
def index():
    data = session.query(Article).all()
    dataset = {
        "data":data
    }
    return render_template("index.html",**dataset)


@app.route("/addarticle/", methods=["GET", "POST"])
def addarticle():
    if flask.request.method == "GET":
        return render_template("addarticle.html")
    else:
        # 获取用户输入信息
        authorname = flask.request.form.get('authorname')
        title = flask.request.form.get('title')
        content = flask.request.form.get('content')
        # 先查询author数据表中是否有这个用户,如果没有就添加，如果就有不添加用户信息
        author = session.query(Author).filter(Author.authorname == authorname).first()
        if not author:
            author = Author(authorname=authorname)

        # 添加文章数据
        article = Article(articletitle=title, articlecontent=content)
        article.author = author
        session.add(article)
        session.commit()
        # 添加成功返回主页面
        return redirect(url_for("index"))


@app.route("/authorlist/<int:authorid>")
def authorlist(authorid):
    #获取作者id是传递进来的,根据用户id去查找文章
    user = session.query(Author).get(authorid)
    articles = user.articles
    dataSet = {
        "data":articles
    }
    return render_template("author.html",**dataSet)

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

6.完整的demo

# coding:utf-8
from flask import Flask, render_template,url_for,redirect
import flask
from sqlalchemy import create_engine, Column, String, Integer, Text, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
import datetime

app = Flask(__name__)
app.debug = True

DB_URI = "mysql+mysqldb://root:root@127.0.0.1:3306/python?charset=utf8"
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()


# 创建一个用户表的映射
class Author(Base):
    __tablename__ = "author"
    authorid = Column(Integer, primary_key=True, autoincrement=True)
    authorname = Column(String(100), nullable=False)

    def __repr__(self):
        return "<Author authorid='%s' authorname='%s'>"%(self.authorid,self.authorname)

# 创建一个文章表的映射
class Article(Base):
    __tablename__ = "article"
    articleid = Column(Integer, primary_key=True, autoincrement=True)
    articletitle = Column(String(100), nullable=False)
    articlecontent = Column(Text)
    createTime = Column(DateTime, default=datetime.datetime.now())

    # 创建作者一对多的映射
    author_id = Column(Integer, ForeignKey("author.authorid"))
    author = relationship("Author", backref="articles")

    def __repr__(self):
        return "<Article author_id='%s' author='%s'>"%(self.author_id,self.author)


# 创建数据表
Base.metadata.create_all()

# 主页面路由
@app.route('/')
def index():
    data = session.query(Article).all()
    dataset = {
        "data":data
    }
    return render_template("index.html",**dataset)


@app.route("/addarticle/", methods=["GET", "POST"])
def addarticle():
    if flask.request.method == "GET":
        return render_template("addarticle.html")
    else:
        # 获取用户输入信息
        authorname = flask.request.form.get('authorname')
        title = flask.request.form.get('title')
        content = flask.request.form.get('content')
        # 先查询author数据表中是否有这个用户,如果没有就添加，如果就有不添加用户信息
        author = session.query(Author).filter(Author.authorname == authorname).first()
        if not author:
            author = Author(authorname=authorname)

        # 添加文章数据
        article = Article(articletitle=title, articlecontent=content)
        article.author = author
        session.add(article)
        session.commit()
        # 添加成功返回主页面
        return redirect(url_for("index"))


@app.route("/authorlist/<int:authorid>")
def authorlist(authorid):
    #获取作者id是传递进来的,根据用户id去查找文章
    user = session.query(Author).get(authorid)
    articles = user.articles
    dataSet = {
        "data":articles
    }
    return render_template("author.html",**dataSet)

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

7.补充html页面内容

templates/index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>主页</title>
    <link href="http://cdn.bootcss.com/bootstrap/4.0.0-alpha.6/css/bootstrap.css" rel="stylesheet">
</head>
<body>
<div class="container">
    <div style="margin: 30px 0;overflow: hidden">
        <a class="btn btn-primary" href="{{ url_for('addarticle') }}" style="float: right">新增文章</a>
    </div>
    <table class="table table-hover">
        <thead>
            <tr>
                <th class="text-center" style="border-bottom: 1px solid #eceeef">NO.</th>
                <th class="text-center" style="border-bottom: 1px solid #eceeef">标题</th>
                <th class="text-center" style="border-bottom: 1px solid #eceeef">内容</th>
                <th class="text-center" style="border-bottom: 1px solid #eceeef">作者</th>
                <th class="text-center" style="border-bottom: 1px solid #eceeef">时间</th>
            </tr>
        </thead>
        <tbody>
            {% for item in data %}
                <tr>
                    <td class="text-center">{{ item.articleid }}</td>
                    <td>{{ item.articletitle }}</td>
                    <td>{{ item.articlecontent }}</td>
                    <td><a href="{{ url_for('authorlist',authorid=item.author_id) }}">{{ item.author.authorname }}</a></td>
                    <td class="text-center">{{ item.createTime }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
</div>
</body>
</html>

templates/addarticle.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link href="http://cdn.bootcss.com/bootstrap/4.0.0-alpha.6/css/bootstrap.css" rel="stylesheet">
</head>
<body>
<div class="container">
    <div class="col-md-6">
        <form action="" method="post" role="form" class="form-horizontal">
            <div class="form-group">
                <label>作者:</label>
                <input type="text" placeholder="请输入作者名" name="authorname" class="form-control"/>
            </div>
            <div class="form-group">
                <label>标题:</label>
                <input type="text" placeholder="请输入标题" name="title" class="form-control"/>
            </div>
            <div class="form-group">
                <label>内容:</label>
                <textarea class="form-control" cols="5" rows="5" style="resize: none" placeholder="请输入文章内容" name="content"></textarea>
            </div>
            <div class="form-group">
                <input type="submit" value="新增" class="btn btn-success">
            </div>
        </form>
    </div>
</div>
</body>
</html>

templates/author.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>作者全部书籍</title>
    <link href="http://cdn.bootcss.com/bootstrap/4.0.0-alpha.6/css/bootstrap.css" rel="stylesheet">
</head>
<body>
<div class="container">
    <h3 class="text-danger text-center">作者:({{ data[0].author.authorname }})所有文章列表</h3>
    <div style="margin:0 0 30px 0;overflow: hidden">
        <a class="btn btn-primary" href="{{ url_for('index') }}" style="float: right">返回主页</a>
    </div>
    <table class="table table-hover">
        <thead>
            <tr>
                <th class="text-center" style="border-bottom: 1px solid #eceeef">NO.</th>
                <th class="text-center" style="border-bottom: 1px solid #eceeef">标题</th>
                <th class="text-center" style="border-bottom: 1px solid #eceeef">内容</th>
                <th class="text-center" style="border-bottom: 1px solid #eceeef">作者</th>
                <th class="text-center" style="border-bottom: 1px solid #eceeef">时间</th>
            </tr>
        </thead>
        <tbody>
            {% for item in data %}
                <tr>
                    <td class="text-center">{{ item.articleid }}</td>
                    <td>{{ item.articletitle }}</td>
                    <td>{{ item.articlecontent }}</td>
                    <td>{{ item.author.authorname }}</td>
                    <td class="text-center">{{ item.createTime }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
</div>
</body>
</html>