python+flask数据库增删改查
首先简单介绍一下程序实现的功能：
条件：python+flask+db
1.实现用户提交表单，数据入库
2.用户输入一定查询条件，web端显示查询结果
3.用户可在web端对库内数据进行简单的增删改查操作
代码实现：
1.app.py文件
1 from flask import Flask, render_template, request, session, redirect 2 import os 3 import csv 4 from flask_sqlalchemy import SQLAlchemy 5 6 app = Flask(__name__) 7 8 # 禁止缓存 9 app.config['TEMPLATES_AUTO_RELOAD'] = True 10 app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False 11 #上传文件路径 12 db_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "db.sqlite3") 13 DB_URI = 'sqlite:///{}'.format(db_path) 14 app.config["SQLALCHEMY_DATABASE_URI"] = DB_URI 15 16 db = SQLAlchemy(app) 17 18 class CSVInfo(db.Model): 19 __tablename__ = 'csv_info' 20 21 cid = db.Column(db.Integer, primary_key=True) 22 csv_name = db.Column(db.String(32)) 23 Name = db.Column(db.String(32)) 24 ID= db.Column(db.String(16), default='') 25 Room = db.Column(db.String(32), default='') 26 State = db.Column(db.String(16), default='') 27 Picture = db.Column(db.String(16), default='') 28 Caption = db.Column(db.String(32), default='')
2.db.sql文件 ，此文件和app.py同步更改，更改完成后，我此处使用navicat 进行数据库可视化，在navicat中选中此sql文件，点击执行，即修改成功
1 DROP TABLE IF EXISTS `csv_info`; 2 CREATE TABLE `csv_info` ( 3 -- `id` int(11) NOT NULL AUTO_INCREMENT, 4 `cid` INTEGER PRIMARY KEY, 5 `csv_name` varchar(50) DEFAULT "", 6 `Name` varchar(50) DEFAULT "", 7 `ID` varchar(50) DEFAULT "", 8 `Room` varchar(50) DEFAULT "", 9 `State` varchar(50) DEFAULT "", 10 `Picture` varchar(50) DEFAULT "", 11 `Caption` varchar(50) DEFAULT "" 12 );
3.a 数据查询
1 #判断查询条件 2 @app.route('/csvdata', methods=['GET', 'POST']) 3 def csvdata(): 4 if request.method == "GET": 5 csv_name = request.args.get('csv_name') 6 if csv_name: 7 csv_infos = CSVInfo.query.filter(CSVInfo.csv_name == csv_name).all() 8 else: 9 csv_infos = CSVInfo.query.filter().all() 10 11 return render_template('csvdata.html', csv_infos=csv_infos) 12 elif request.method == 'POST': 13 # csv_name = request.form.get('csv_name') 14 # if csv_name: 15 # return redirect('/csvdata?csv_name={}'.format(csv_name)) 16 17 cid = request.form.get('cid') 18 if cid: 19 csv_infos = CSVInfo.query.get(cid) 20 return render_template('csvdata.html', csv_infos=[csv_infos,]) 21 22 img_name = request.form.get('img_name') 23 if img_name: 24 # user.u_name.like('%三%') 25 csv_infos = CSVInfo.query.filter(CSVInfo.Picture == img_name).all() 26 if csv_infos: 27 return render_template('user_info.html', csv_infos_name=csv_infos) 28 else: 29 return '<script>alert("no picture available");location.href="/";</script>' 30 31 32 ID = request.form.get('ID') 33 if ID: 34 # user.u_name.like('%三%') 35 csv_infos = CSVInfo.query.filter(CSVInfo.ID == ID).all() 36 return render_template('user_info.html', csv_infos_id=csv_infos) 37 38 Name = request.form.get('Name') 39 if Name: 40 # user.u_name.like('%三%') 41 csv_infos = CSVInfo.query.filter(CSVInfo.Name == Name).all() 42 return render_template('csvdata.html', csv_infos=csv_infos)
3.b 数据更新
1 #修改 2 @app.route('/csvedit/<cid>', methods=['GET', 'POST']) 3 def csv_edit(cid): 4 # ID = request.form.get('ID') 5 obj = CSVInfo.query.get(cid) 6 if request.method == "GET": 7 return render_template('csv_edit.html', obj=obj) 8 elif request.method == 'POST': 9 u = request.form.get('Name') 10 s = request.form.get('ID') 11 r = request.form.get('Room') 12 t = request.form.get('State') 13 p = request.form.get('Picture') 14 d = request.form.get('Caption') 15 16 obj.Name = u 17 obj.ID = s 18 obj.Room = r 19 obj.State = t 20 obj.Picture = p 21 obj.Caption = d 22 db.session.commit() 23 # return redirect('/csvedited?cid={}') 24 return redirect('/csvedited?ID={}'.format(obj.ID)) 25 #修改后执行的操作 26 @app.route('/csvedited', methods=['GET', 'POST']) 27 def csv_edited(): 28 # id = CSVInfo.query.get('ID') 29 # if request.method == "GET": 30 id = request.args.get('ID') 31 if id: 32 csv_infos = CSVInfo.query.filter(CSVInfo.ID == id).all() 33 else: 34 csv_infos = CSVInfo.query.filter().all() 35 36 return render_template('user_info.html', csv_infost_edit=csv_infos) 37 # elif request.method == 'POST': 38 # csv_name = request.form.get('csv_name') 39 # if csv_name: 40 # return redirect('/csvdata?csv_name={}'.format(csv_name))
3.c数据删除
1 #数据删除 2 @app.route('/csvdel/<cid>/', methods=['GET']) 3 def csv_del(cid): 4 obj = CSVInfo.query.get(cid) 5 db.session.delete(obj) 6 db.session.commit() 7 return redirect('/csvdata?csv_name={}'.format(obj.csv_name))
4.前端代码展现：
简单的介绍：
1.主界面命名为user_info
在这个界面可以执行各项查询操作，例如：输入用户ID，查询用户的姓名，对应的图片等，并将结果显示到主界面上。
2.数据库中数据的增删改查
搜索到需要修改的数据，跳转到编辑界面，编辑完毕后提交，界面显示想要的数据结果。
4.a 主界面user_info
1 <div > 2 <h3>Query</h3> 3 <h4>Search All CSV</h4> 4 <a>please click this button to see all csv files</a> 5 <form method="GET" action="/csvdata"> 6 <input type="text" placeholder="All" name="csv_name" disabled> 7 <button type="submit">search</button> 8 </form> 9 10 <h4>Search by CSV Name</h4> 11 <a>please input csv name and click this button to see corresponding csv file </a> 12 <form method="GET" action="/csvdata"> 13 <input type="text" placeholder="csv_name" name="csv_name"> 14 <button type="submit">search</button> 15 </form> 16 <h4> Search by picture name to see picture </h4> 17 <form method="POST" action="/csvdata"> 18 <input type="text" placeholder="img_name" name="img_name"> 19 <button type="submit">search</button> 20 </form> 21 {% for i in csv_infos_name %} 22 <td>{{ i.Picture }}</td> 23 <td><a href="/static/imgs/{{ i.Picture }}"><img src="/static/imgs/{{ i.Picture }}" style="max-height: 100px"></a></td> 24 25 {% endfor %} 26 27 <div> 28 <h4>Search by ID to show picture</h4> 29 <form method="POST" action="/csvdata"> 30 <input type="text" placeholder="ID" name="ID"> 31 <button type="submit">search</button> 32 </form> 33 34 {% for v in csv_infos_id %} 35 <td>{{ v.Picture }}</td> 36 <td><a href="/static/imgs/{{ v.Picture }}"><img src="/static/imgs/{{ v.Picture }}" style="max-height: 100px"></a></td> 37 <td>{{ v.Caption }}</td> 38 39 {% endfor %} 40 </div> 41 <div> 42 <h4>Search by user Name to modify and show something</h4> 43 <form method="POST" action="/csvdata"> 44 <input type="text" placeholder="Name" name="Name"> 45 <button type="submit">search</button> 46 </form> 47 {% for v in csv_infost_edit %} 48 <td>{{ v.Name }}</td> 49 <td>{{ v.Picture }}</td> 50 <td><a href="/static/imgs/{{ v.Picture }}"><img src="/static/imgs/{{ v.Picture }}" style="max-height: 100px"></a></td> 51 <td>{{ v.Caption }}</td> 52 53 {% endfor %} 54 55 </div>
4.b csvdata.html 数据展示界面，若数据库中的参数修改，则此处对应的参数也要进行修改
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> <style> body{ margin: 0; } .menu{ display: block; padding: 5px; } </style> </head> <body> <div> <div style="position:absolute;top:48px;left: 210px;bottom: 0;right: 0;overflow: auto"> <h1>Edit</h1> <form> <input type="text" name="cid" value="cid" disabled> <input type="text" name="Name" value="Name" disabled> <input type="text" name="ID" value="ID" disabled> <input type="text" name="Room" value="Room" disabled> <input type="text" name="State" value="State" disabled> <input type="text" name="Picture" value="Picture" disabled> <input type="text" name="Caption" value="Caption" disabled> <!-- <input type="submit" value="submit" /> --> </form> <form method="POST" action="/csvedit/{{ obj.cid }}"> {# <form method="POST" action="/csvedit/">#} <input type="text" name="id" value="{{ obj.cid }}" disabled> <input type="text" name="Name" value="{{ obj.Name }}" placeholder="Name"/> <input type="text" name="ID" value="{{ obj.ID }}" placeholder="ID"/> <input type="text" name="Room" value="{{ obj.Room }}" placeholder="Room" /> <input type="text" name="State" value="{{ obj.State }}" placeholder="State" /> <input type="text" name="Picture" value="{{ obj.Picture }}" placeholder="Picture" /> <input type="text" name="Caption" value="{{ obj.Caption }}" placeholder="Caption"/> <input type="submit" value="submit" /> </form> <!-- <div > <h3>Upload Files</h3> <form action="/upload_file/" method="POST" enctype ="multipart/form-data"> <p><input type="file" id="fl" name="myfiles"></p> <input type="submit" value="submit"> </form> </div> <div > <h3>Upload Pictures</h3> <form action="/upload_img/" method="POST" enctype ="multipart/form-data"> <p><input type="file" name="mypictures"></p> <input type="submit" value="submit"> </form> </div> --> </div> </div> </body> </html>
4.c csv_edit.html 数据编辑界面
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> <style> table{ border:1px solid #000; border-collapse:collapse; width: 100%; } th,td{ border:1px solid #000; } </style> </head> <body> <div> <table> <thead> <tr> <th>CSV ID</th> <th>CSV Name</th> <th>Name</th> <th>ID</th> <th>Room</th> <th>State</th> <th>Picture</th> <th>Picture</th> <th>Caption</th> <!-- <th>Pic</th> --> <th>Edit</th> <th>DEL</th> </tr> </thead> <tbody> {% for v in csv_infos %} <tr> <td>{{ v.cid }}</td> <td>{{ v.csv_name }}</td> <td>{{ v.Name }}</td> <td>{{ v.ID }}</td> <td>{{ v.Room }}</td> <td>{{ v.State }}</td> <td>{{ v.Picture }}</td> <td><a href="/static/imgs/{{ v.Picture }}"><img src="/static/imgs/{{ v.Picture }}" style="max-height: 100px"></a></td> <td>{{ v.Caption }}</td> <!-- <td>{{ v.Pic }}</td> --> <td><a href="/csvedit/{{ v.cid }}">Modify</a></td> <td><a href="/csvdel/{{ v.cid }}">Del</a></td> </tr> {% endfor %} </tbody> </table> </div> </body> </html>
注意事项：
1. 前端界面的表格设计，需要与数据库的参数对应
2.参数修改后，前后端要对应
3.所有显示在主界面的查询结果，都可在前端界面进行修改
