测开之路一百三十：实现前端到数据库交互（增和查）
模拟实现一个问题提交系统，并把数据保存到数据库
目录结构
base模板页
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>-问题反馈系统</title>
<!--bootstrap、jquery、font-awesome-->
<link href="https://cdn.bootcss.com/twitter-bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.bootcss.com/jquery/2.2.4/jquery.min.js"></script>
<link href="https://cdn.bootcss.com/font-awesome/4.6.3/css/font-awesome.min.css" rel="stylesheet">
<script src="https://cdn.bootcss.com/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<!--导航栏-->
<div class="row">
<nav class="navbar navbar-default" role="navigation">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-ex1--collapse">
<span class="sr-only"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="/">问题反馈系统</a>
</div>
<div class="collapse navbar-collapse navbar-ex1--collapse">
<ul class="nav navbar-nav">
<li class="active"><a href="/">首页</a></li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown">反馈管理<b class="caret"></b></a>
<ul class="dropdown-menu">
<li><a href="#">问题列表</a></li>
</ul>
</li>
</ul>
<form class="navbar-form navbar-left" role="search">
<div class="for-group">
<input type="text" class="form-control" placeholder="Search">
<button type="submit" class="btn btn-default">submit</button>
</div>
</form>
<ul class="nav navbar-nav navbar-right">
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown">管理员<b class="caret"></b></a>
<ul class="dropdown-menu">
<li><a href="#">退出</a></li>
</ul>
</li>
</ul>
</div>
</nav>
</div>
<!--面包屑导航-->
<div class="row">
<ul class="breadcrumb">
<li><a href="/">首页</a></li>
<li class="active">问题反馈</li>
</ul>
</div>
<!--正文部分预留空间，其他页面只需要继承过后往这里面添加内容即可-->
{% block main_content %}
{% endblock %}
<!--页脚-->
<div class="row">
<div class="well text-center">
©版权所有 <a href="https://www.baidu.com/">点击跳转</a>
</div>
</div>
</div>
</body>
</html>
正文部分：post.html
<!--继承base.html-->
{% extends 'base.html' %}
<!--引用base.html预留的正文部分-->
{% block main_content %}
<div class="row">
<div class="panel panel-default">
<div class="panel-heading">
<h4>问题反馈</h4>
</div>
<div class="panel-body">
<form action="#" class="form-horizontal">
<div class="form-group">
<label for="subject" class="control-label col-md-2">主题</label>
<div class="col-md-6">
<input type="text" class="form-control" id="subject" name="subject">
</div>
</div>
<div class="form-group">
<label for="category" class="control-label col-md-2">问题分类</label>
<div class="col-md-2">
<select name="category" id="category" class="form-control">
<option value="1">产品质量</option>
<option value="2">客户服务</option>
<option value="3">购买支付</option>
</select>
</div>
</div>
<div class="form-group">
<label for="username" class="control-label col-md-2">姓名</label>
<div class="col-md-2">
<input type="text" class="form-control" id="username" name="username">
</div>
</div>
<div class="form-group">
<label for="email" class="control-label col-md-2">邮箱</label>
<div class="col-md-6">
<input type="text" class="form-control" id="email" name="email">
</div>
</div>
<div class="form-group">
<label for="image" class="control-label col-md-2">图片</label>
<div class="col-md-6">
<input type="file" id="image" name="image">
</div>
</div>
<div class="form-group">
<label for="body" class="control-label col-md-2">内容</label>
<div class="col-md-6">
<textarea name="body" id="body" cols="30" rows="10" class="form-control"></textarea>
</div>
</div>
<div class="col-md-offset-2">
<input type="submit" class="btn btn-primary" value="提交">
<input type="reset" class="btn btn-default" value="重置">
</div>
</form>
</div>
<div class="panel-footer">
</div>
</div>
</div>
{% endblock %}
路由：
from flask import Flask, render_template
app = Flask(__name__)
@app.route("/")
def index():
return render_template('base.html')
# 模板继承
@app.route("/feedback/")
def feedback():
return render_template('post.html')
if __name__ == '__main__':
app.run(
debug=True
)
访问看一下效果
准备数据库、表和字段
create table category(
CategoryName TEXT
);
insert into category values ('产品质量');
insert into category values ('客户服务');
insert into category values ('购买支付');
select ROWID,* from category;
create table feedback(
Subjeck TEXT,
CategoryID integer,
UserName TEXT,
Email TEXT,
Image TEXT,
Body TEXT,
State Boolean,
Reply TEXT,
ReleaseTime DATETIME,
FOREIGN KEY (CategoryID) REFERENCES category(ROWID)
);
select * from feedback;
insert into feedback values ('网银不能支付？', 3, 'xx','qq.com', null,'网银不能支付',null,null,null);
select * from feedback;
增：
处理提交数据的视图
# coding:utf-8
import sqlite3
from datetime import datetime
from flask import Flask, request, render_template, redirect, url_for
app = Flask(__name__)
DATABASE = r'.\db\feedbach.db'
@app.route("/")
def index():
return render_template('base.html')
# 模板继承
@app.route("/feedback/")
def feedback():
return render_template('post.html')
@app.route("/post_feedback/", methods=["POST"])
def post_feedback():
""" 提交视图 """
if request.method == 'POST': # 如果是post请求就获取表单值
subject = request.form.get('subject', None)
categoryid = request.form.get('category', 1)
username = request.form.get('username')
email = request.form.get('email')
body = request.form.get('body')
release_time = str(datetime.now())
state = 0
print(subject, categoryid, username, email, body, state, release_time)
conn = sqlite3.connect(DATABASE)
c = conn.cursor()
# 防止sql注入，用？代替值
sql = "insert into feedback (Subjeck, CategoryID, UserName, Email, Body, State, ReleaseTime) values (?,?,?,?,?,?,?)"
c.execute(sql, (subject, categoryid, username, email, body, state, release_time))
conn.commit()
conn.close()
# 为防止因卡顿引起重复提交，提交过后跳转到填写页面
return redirect(url_for('feedback'))
if __name__ == '__main__':
app.run(
debug=True
)
修改一下form的属性
数据库查询
查：展示功能，列表页
模板
效果
查询渲染逻辑
@app.route("/list/")
def list():
conn = sqlite3.connect(DATABASE)
c = conn.cursor()
sql = "select ROWID,* from feedback order by ROWID DESC"
feedbacks = c.execute(sql).fetchall()
conn.close()
return render_template('feedback-list.html', items=feedbacks)
feedback-list.html
{% extends 'base.html'%}
{% block main_content %}
<div class="row">
<table class="table table-hover">
<tr>
<th>ID</th>
<th>主题</th>
<th>分类</th>
<th>用户</th>
<th>邮箱</th>
<th>处理状态</th>
<th>提交时间</th>
<th>操作</th>
</tr>
{% for item in items %}
<tr>
<td>{{ loop.index }}</td><!--jinja模板提供的遍历序号功能-->
<td>{{ item[1] }}</td>
<td>{{ item[2] }}</td>
<td>{{ item[3] }}</td>
<td>{{ item[4] }}</td>
<td><span class="label label-{{ 'danger' if item[7] ==0 else 'success' }}">{{ "未处理" if item[7] ==0 else "已处理" }}</span></td>
<td>{{ item[9] }}</td>
<td>
<a href="#" class="btn btn-success">查看</a>
<a href="#" class="btn btn-default">编辑</a>
<a href="#" class="btn btn-danger">删除</a>
</td>
</tr>
{% endfor %}
</table>
</div>
{% endblock %}
请求
# coding:utf-8
import sqlite3
from datetime import datetime
from flask import Flask, request, render_template, redirect, url_for
app = Flask(__name__)
DATABASE = r'.\db\feedbach.db'
@app.route("/")
def index():
return render_template('base.html')
# 模板继承
@app.route("/feedback/")
def feedback():
return render_template('post.html')
@app.route("/post_feedback/", methods=["POST"])
def post_feedback():
""" 提交视图 """
if request.method == 'POST': # 如果是post请求就获取表单值
subject = request.form.get('subject', None)
categoryid = request.form.get('category', 1)
username = request.form.get('username')
email = request.form.get('email')
body = request.form.get('body')
release_time = str(datetime.now())
state = 0
print(subject, categoryid, username, email, body, state, release_time)
conn = sqlite3.connect(DATABASE)
c = conn.cursor()
# 防止sql注入，用？代替值
sql = "insert into feedback (Subjeck, CategoryID, UserName, Email, Body, State, ReleaseTime) values (?,?,?,?,?,?,?)"
c.execute(sql, (subject, categoryid, username, email, body, state, release_time))
conn.commit()
conn.close()
# 为防止因卡顿引起重复提交，提交过后跳转到填写页面
return redirect(url_for('feedback'))
@app.route("/list/")
def list():
conn = sqlite3.connect(DATABASE)
c = conn.cursor()
sql = "select ROWID,* from feedback order by ROWID DESC"
feedbacks = c.execute(sql).fetchall()
conn.close()
return render_template('feedback-list.html', items=feedbacks)
if __name__ == '__main__':
app.run(
debug=True
)
讨论群：249728408
