IBM Cloud DB2 数据sql 增删改查
首先简单介绍一下程序实现的功能：
python + flask
1.使用IBM Cloud DB2 数据库，sql语句数据库条件查询
2.用户输入查询条件，筛选数据
3.数据增删改查
先决条件： 程序已经成功连接 IBM Cloud DB2 数据库 连接方式参考：https://www.cnblogs.com/bocaimao/p/13277134.html
数据来源：https://earthquake.usgs.gov/earthquakes/feed/v1.0/csv.php 近30天内earthquake data
代码实现：
1. app.py
功能：用户给出经纬度范围，筛选出在此范围的所有数据
1 @app.route('/search/', methods=['GET', 'POST']) 2 def search():
#前端传参 3 lat1 =request.args.get('lat1') 4 lat2 =request.args.get('lat2') 5 long1 = request.args.get('long1') 6 long2 = request.args.get('long2') 7 if lat1<=lat2: 8 print('good!') 9 else: 10 lat1_bak = lat1 11 lat1 = lat2 12 lat2 =lat1_bak 13 if long1<=long2: 14 print('good!') 15 else: 16 long1_bak = long1 17 long1 = long2 18 long2 =long1_bak
19 sql = """ 20 SELECT * 21 FROM QUAKES 22 where LATITUDE is not null and LONGITUDE is not null 23 and LATITUDE BETWEEN '"""+lat1+"""' and '"""+lat2+"""' 24 and "LONGITUDE" BETWEEN '"""+long1+"""' and '"""+long2+"""'; 25 """
#数据读取
26 result = ibm_db.exec_immediate(connect_db, sql) 27 dictionary = ibm_db.fetch_both(result) 28 userlist = [] 29 while dictionary != False: 30 userlist.append(dictionary) 31 dictionary = ibm_db.fetch_both(result) 32 return render_template('result.html', data=userlist)
前端代码展现：
主界面：
1 <h4> please input</h4> 2 <form method="GET" action="/search/"enctype="multipart/form-data"> 3 <input type="text" placeholder="lat1" name="lat1"> 4 <input type="text" placeholder="lat2" name="lat2"> 5 <input type="text" placeholder="long1" name="long1"> 6 <input type="text" placeholder="long2" name="long2"> 7 <button type="submit">search</button> 8 </form>
result.html 结果跳转页面
1 <div> 2 <table> 3 <thead> 4 <tr> 5 <tr> 6 <th>id</th> 7 8 <th>latitude</th> 9 <th>longitude</th> 10 <th>time</th> 11 <th>place</th> 12 </tr> 13 </thead> 14 15 <tbody> 16 {% for v in data %} 17 <tr> 18 <td>{{ v.ID }}</td> 19 <td>{{ v.LATITUDE }}</td> 20 <td>{{ v.LONGITUDE }}</td> 21 <td>{{ v.TIME }}</td> 22 <td>{{ v.PLACE }}</td> 23 24 25 </tr> 26 {% endfor %} 27 </tbody> 28 29 </table> 30 31 </table> 32 </div>
2. app.py
功能：sql模糊查询，对筛选到的数据进行更新或删除
1 #sql 模糊查询 2 @app.route('/sqlsearch1/',methods=['GET','POST']) 3 def sqlsearch1(): 4 name = request.args.get('location') 5 sql = "select * from QUAKES where place like '%" + name + "%' " 6 result = ibm_db.exec_immediate(connect_db, sql) 7 dictionary = ibm_db.fetch_both(result) 8 userlist = [] 9 while dictionary != False: 10 userlist.append(dictionary) 11 dictionary = ibm_db.fetch_both(result) 12 quake = sorted(userlist, key=lambda x: x[4], reverse=True) 13 14 large_eq = [] 15 large_eq.append(quake[0]) 16 large_eq.append(quake[1]) 17 large_eq.append(quake[2]) 18 large_eq.append(quake[3]) 19 return render_template('sqlsearchresult.html', data8=large_eq) 20 21 @app.route('/dele/',methods=['GET','POST']) 22 def dele(): 23 id = request.args.get('ID') 24 sql = "delete from QUAKES where ID = '" + id + "'" 25 result = ibm_db.exec_immediate(connect_db, sql) 26 if result: 27 return '<script>alert("Delete Successful");location.href="/";</script>' 28 # 29 else: 30 return '<script>alert("Delete Failure");location.href="/";</script>' 31 32 33 @app.route('/eq_edit/',methods=['GET','POST']) 34 def eq_edit(): 35 id = request.args.get('ID') 36 sql = "select * from QUAKES where ID = '" + id + "'" 37 result = ibm_db.exec_immediate(connect_db, sql) 38 dictionary = ibm_db.fetch_both(result) 39 userlist = [] 40 while dictionary != False: 41 userlist.append(dictionary) 42 dictionary = ibm_db.fetch_both(result) 43 return render_template('csv_edit.html',userlist=userlist) 44 45 @app.route('/edited/',methods=['GET','POST']) 46 47 def edited(): 48 id = request.form.get('id') 49 place =request.form.get('place') 50 sql = "UPDATE QUAKES SET PLACE = '"+place+"' where ID = '" + id + "'" 51 result = ibm_db.exec_immediate(connect_db, sql) 52 if result: 53 return '<script>alert("Edit Successful");location.href="/";</script>' 54 # 55 else: 56 return '<script>alert("Edit Failure");location.href="/";</script>'
前端代码展现
主界面：
1 <div> 2 <h4>please input</h4> 3 <form action="/question8" method="get" enctype="multipart/form-data"> 4 <input type="text" name="location" > 5 <button type="submit" value="submit">search</button> 6 </form> 7 </div>
sqlsearchresult.html
<div> <h3>result </h3> <table> <thead> <tr> <tr> <th>time</th> <th>latitude</th> <th>longitude</th> <th>depth</th> <th>mag</th> <th>gap</th> <th>id</th> <th>place</th> <th>locationSource</th> <th></th> </tr> </thead> <tbody> {% for row in data8 %} <tr> <td >{{ row.TIME }}</td> <td >{{ row.LATITUDE }}</td> <td >{{ row.LONGITUDE }}</td> <td >{{ row.DEPTH }}</td> <td >{{ row.MAG }}</td> <td >{{ row.GAP }}</td> <td >{{ row.ID }}</td> <td >{{ row.PLACE }}</td> <td >{{ row.LOCATIONSOURCE }}</td> <td><a href="/dele/?ID={{ row.ID }}">Delete</a > | <a href="/eq_edit/?ID={{ row.ID }}">Edit</a ></td> </tr> {% endfor %} </tbody> </table> </div> </table> </div>
数据修改界面：
<!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 action="/edited" method="post"> <table> {% for row in userlist %} <tr> <input type="text" name="id" value="{{ row.ID }}" style="display:none" > <td>place:<input type="text" name="place" value="{{ row.PLACE }}"><br></td> </tr> {% endfor %} <tr> <td ><input type="submit" value="submit"></td> </tr> </table> </form> </div> </div> </body> </html>
