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.所有显示在主界面的查询结果,都可在前端界面进行修改

 

python+flask数据库增删改查

上一篇:php+mysql实践项目


下一篇:Nodejs怎样在服务端写定时脚本,自动备份MongoDB数据库,并记录日志