实现指定查找功能
与list共用一个视图函数，修改一下sql
@app.route("/list/")
def list():
""" 展示所有问题 """
sql = "select ROWID,* from feedback order by ROWID DESC"
# feedbacks = query_sql(sql)
# print(feedbacks)
key = request.args.get('key', '')
sql = 'select f.ROWID,f.*,c.CategoryName from feedback f INNER JOIN category c on c.ROWID = f.CategoryID where f.Subjeck like ? order by f.ROWID'
feedbacks = query_sql(sql, (f'%{key}%',))
return render_template('feedback-list.html', items=feedbacks)
feedback-list.html，之前的渲染逻辑不变，新增一个panel
搜索看一下
无条件搜索的时候，默认展示所有内容
视图
# coding:utf-8
import sqlite3
from datetime import datetime
from flask import Flask, request, render_template, redirect, url_for, g
app = Flask(__name__)
DATABASE = r'.\db\feedbach.db'
'=======================封装sql助手函数============================='
def make_dicts(cursor, row):
""" 将游标获取的Tuple根据数据库列表转换为dict """
return dict((cursor.description[idx][0], value) for idx, value in enumerate(row))
def get_db():
""" 获取(简历数据库链接)
g: flask内置的变量：g = LocalProxy(partial(_lookup_app_object, "g"))
"""
db = getattr(g, '_database', None)
if not db:
db = g._database = sqlite3.connect(DATABASE)
db.row_factory = make_dicts
return db
def execute_sql(sql, params=()):
""" 执行sql语句不返回数据结果 insert、update、delete """
c = get_db().cursor()
c.execute(sql, params)
c.connection.commit()
def query_sql(sql, params=(), one=False):
""" 查询数据 one=False的时候返回多条"""
c = get_db().cursor()
result = c.execute(sql, params).fetchall()
c.close()
return (result[0] if result else None) if one else result
@app.teardown_appcontext # 在当前app上下文销毁时执行
def close_connection(exeption):
""" 关闭数据库 """
db = getattr(g, '_database', None)
if db is not None:
db.close()
'========================================================================'
@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():
""" 展示所有问题 """
sql = "select ROWID,* from feedback order by ROWID DESC"
# feedbacks = query_sql(sql)
# print(feedbacks)
key = request.args.get('key', '')
sql = 'select f.ROWID,f.*,c.CategoryName from feedback f INNER JOIN category c on c.ROWID = f.CategoryID where f.Subjeck like ? order by f.ROWID'
feedbacks = query_sql(sql, (f'%{key}%',))
return render_template('feedback-list.html', items=feedbacks)
@app.route('/del/<id>/')
def delete_feedback(id=0):
""" 删除问题 ，前端传id"""
conn = sqlite3.connect(DATABASE)
c = conn.cursor()
sql = "delete from feedback where ROWID = ?"
c.execute(sql, (id,))
conn.commit()
conn.close()
return redirect(url_for('list'))
# 编辑功能
@app.route("/edit/<id>/")
def edit(id=None):
""" 根据前端传过来的id返回编辑的html """
# 获取绑定的下拉列表
sql = "select ROWID,CategoryName from category"
categories = query_sql(sql)
# 获取当前id的信息，并绑定至form表单，以备修改
sql = "select rowid,* from feedback where rowid = ?"
curren_feedback = query_sql(sql, (id,), True)
# return str(curren_feedback) # 查看查出来的数据顺序，方便html渲染排序
return render_template('edit.html', categories=categories, item=curren_feedback)
@app.route("/save_edit/", methods=['POST'])
def save_edit():
""" 保存编辑 """
if request.method == 'POST':
id = request.form.get('rowid', None)
reply = request.form.get('reply')
state = 1 if request.form.get('state', 0) == 'on' else 0
sql = 'update feedback set Reply=?, State=? where rowid=?'
conn = sqlite3.connect(DATABASE)
c = conn.cursor()
c.execute(sql, (reply, state, id))
conn.commit()
conn.close()
return redirect(url_for('list'))
if __name__ == '__main__':
app.run(
debug=True
)
html
{% extends 'base.html'%}
{% block main_content %}
<div class="row">
<!--增加搜索的入口-->
<div class="panel panel-default">
<div class="panel-heading">
<form action="#" class="form-inline">
<input type="text" class="form-control" name="key" id="key">
<input type="submit" class="btn btn-default" value="点击搜索">
</form>
</div>
<!--把之前的表格移到panel-body里面-->
<div class="panel-body">
<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.Subjeck }}</td><!--jinja模板提供的字典取值功能-->
<td>{{ item.get('CategoryID', 0) }}</td><!--python自带的字典取值功能-->
<td>{{ item['UserName'] }}</td><!--python自带的字典取值功能-->
<td>{{ item['Email'] }}</td>
<td><span class="label label-{{ 'danger' if item['State'] ==0 else 'success' }}">{{ "未处理" if item['State'] ==0 else "已处理" }}</span></td>
<td>{{ item['ReleaseTime'] }}</td>
<td>
<a href="#" class="btn btn-success">查看</a>
<a href="{{ url_for('edit', id=item['rowid']) }}" class="btn btn-default">编辑</a>
<a href="{{ url_for('delete_feedback', id=item['rowid']) }}" class="btn btn-danger">删除</a>
</td>
</tr>
{% endfor %}
</table>
</div>
</div>
</div>
{% endblock %}