sqlalchemy一对多的关系表案例

本案例中直接使用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>