本页面写了一个登陆页面和一个查询页面:查询页面有增删改查和导出数据到excel文件的功能
登陆页面:loging_myself.html
查询页面:query_myself.html
后台py文件:query_myself.py
使用flask框架首先要创建一个项目,然后再项目中创建虚拟环境,如下,one为我创建的项目,fir是我创建的虚拟环境,写的html文件要放在templates中,这是硬性规定
此外,还需要引入
<script src="../static/lib/layui-v2.6.3/layui.js" charset="utf-8"></script>
<link rel="stylesheet" href="../static/lib/layui-v2.6.3/css/layui.css" media="all">
<link rel="stylesheet" href="../static/css/public.css" media="all">
<script src="../static/lib/layui-v2.6.3/layui.js" charset="utf-8"></script>
目录
loging_myself.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Demo</title>
<!-- 请勿在项目正式环境中引用该 layui.css 地址 -->
<link rel="stylesheet" href="../static/lib/layui-v2.6.3/css/layui.css" media="all">
<link rel="stylesheet" href="../static/css/public.css" media="all">
</head>
<body>
<style>
.demo-login-container{width: 320px; margin: 21px auto 0;}
.demo-login-other .layui-icon{position: relative; display: inline-block; margin: 0 2px; top: 2px; font-size: 26px;}
</style>
<form class="layui-form">
<div class="demo-login-container">
<div class="layui-form-item">
<div class="layui-input-wrap">
<div class="layui-input-prefix">
<i class="layui-icon layui-icon-username"></i>
</div>
<input type="text" name="username" value="" lay-verify="required" placeholder="工号" lay-reqtext="请输入工号" autocomplete="off" class="layui-input" lay-affix="clear">
</div>
</div>
<div class="layui-form-item">
<div class="layui-input-wrap">
<div class="layui-input-prefix">
<i class="layui-icon layui-icon-password"></i>
</div>
<input type="password" name="password" value="" lay-verify="required" placeholder="初始密码为123456" lay-reqtext="请填写密码" autocomplete="off" class="layui-input" lay-affix="eye">
</div>
</div>
<div class="layui-form-item">
<input type="checkbox" name="remember" lay-skin="primary" title="记住密码">
<a href="#forget" style="float: right; margin-top: 7px;">忘记密码?</a>
</div>
<div class="layui-form-item">
<button class="layui-btn layui-btn-fluid" lay-submit lay-filter="demo-login">登录</button>
</div>
</div>
</form>
<!-- 请勿在项目正式环境中引用该 layui.js 地址 -->
<script src="../static/lib/layui-v2.6.3/layui.js" charset="utf-8"></script>
<script>
layui.use(function(){
var $ = layui.jquery;
var form = layui.form;
var layer = layui.layer;
// 提交事件
form.on('submit(demo-login)', function(data){
var field = data.field; // 获取表单字段值
// 显示填写结果,仅作演示用
var param=JSON.stringify(field)
// layer.alert(param, {
// title: '当前填写的字段值'
// });
// 此处可执行 Ajax 等操作
$.ajax({
url:'/login_myself',
type:'post',
data:{"datas":param},
dataType:'JSON',
success:function(response){
if(response.msg.search("登陆成功") != -1){// 如果響應消息中包含“登录成功”,則顯示消息並重新加載頁面。
layer.msg("你好,我是将臣",{icon: 1, time: 1000}); //提交成功后刷新页面
window.location.href="/query_myself"
// window.location.href="../templates/query_myself.html"
}else{
layer.alert(response.msg);
}
console.log(response)
console.log("success")
// window.location.href='../templates/query_myself.html'
},
error:function (data) {
}
});
return false; // 阻止默认 form 跳转
});
});
</script>
</body>
</html>
query_myself.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>layui</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="../static/lib/layui-v2.6.3/css/layui.css" media="all">
<link rel="stylesheet" href="../static/css/public.css" media="all">
</head>
<body>
<div class="layuimini-container">
<div class="layuimini-main">
<fieldset class="table-search-fieldset">
<legend>搜索页面</legend>
<div style="margin: 10px 10px 10px 10px">
<form class="layui-form layui-form-pane" action="">
<div class="layui-form-item">
<div class="layui-inline">
<label class="layui-form-label">ID</label>
<div class="layui-input-inline">
<input type="text" name="ID" autocomplete="off" class="layui-input">
</div>
</div>
<div class="layui-inline">
<button type="submit" class="layui-btn layui-btn-primary" lay-submit lay-filter="data-search-btn"><i class="layui-icon"></i> 搜 索</button>
</div>
</div>
</form>
</div>
</fieldset>
<script type="text/html" id="toolbarDemo">
<div class="layui-btn-container">
<button class="layui-btn layui-btn-normal layui-btn-sm data-add-btn" lay-event="add" data-method="notice">
<i class="layui-icon layui-icon-add-circle" style="font-size: 10px; color: white;"></i> 新增</button>
<button class="layui-btn layui-btn-sm layui-btn-normal data-add-btn" lay-event="edit"> 修改 </button>
<button class="layui-btn layui-btn-sm layui-btn-danger data-delete-btn" lay-event="delete"> 删除 </button>
<button class="layui-btn layui-btn-sm layui-btn-normal data-add-btn" lay-event="ExportAll"> 批量導出 </button>
<button class="layui-btn layui-btn-sm layui-btn-normal data-add-btn" lay-event="Export"> 導出 </button>
</div>
</script>
<table class="layui-hide" id="table" lay-filter="currentTableFilter"></table>
<!-- <script type="text/html" id="currentTableBar">
<a class="layui-btn layui-btn-normal layui-btn-xs data-count-edit" lay-event="edit">编辑</a>
</script> -->
</div>
</div>
<script src="../static/lib/layui-v2.6.3/layui.js" charset="utf-8"></script>
<script>
//定义这个table容器
layui.use(['form', 'table', 'layer'], function () {
var $ = layui.jquery,
form = layui.form,
table = layui.table;
$(".layui-table-body,layui-table-box,.layui-table-cell").css('overflow','visible');
d = table.render({
elem: '#table',
url: "/searchall",
id: "table",
toolbar: '#toolbarDemo',
method: "POST",
defaultToolbar: ['filter', 'exports', 'print', {
title: '提示',
layEvent: 'LAYTABLE_TIPS',
icon: 'layui-icon-tips'
}],
cellMinWidth:80,
cols: [[
{type: "checkbox"},
{field: 'ID', width:80, title: 'ID', sort: true},
{field: 'QUERY', title: 'QUERY',sort: true,edit:true,width:500,length:5000},
{field: 'TEXT', title: 'TEXT', sort: true,edit:true},
]],
limits: [10, 15, 20, 25, 50, 100],
limit: 10,
page: true,
parseData: function (res) {
var result;
exportData = res.data;
if (this.page.curr) {
result = res.data.slice(this.limit * (this.page.curr - 1), this.limit * this.page.curr);
} else {
result = res.data.slice(0, this.limit);
}
return {"code": res.code, "msg": res.msg, "count": res.count, "data": result};},
done: function (res) {
console.log($('table'));
$("table").css("width","100%")
},
skin: 'line'
});
// 监听搜索操作
form.on('submit(data-search-btn)', function (data) {
var result = JSON.stringify(data.field);//将js对象转换为字符串
console.log(result)
//执行搜索重载
table.reload('table', {
url:"/search",
done:function(res){
console.log("success")
// if(res.msg.search('111') != -1 || res.msg.search("請輸入查詢條件") != -1){
// layer.alert(res.msg);
// };
},
page: {
curr: 1
},where:{
searchParams:result
}
}, 'data');
return false;
});
/**
* toolbar监听事件
*/
table.on('toolbar(currentTableFilter)', function (obj) {
if (obj.event === 'add'){ // 监听添加操作
var index =layer.open({
type: 1,
title:"新增",
area:['50%','70%'],
btn: ['确定', '取消'],
shade: 0.6,
maxmin: true,
content:'<form id="book" class="submitform" action=""><div class="layui-inline ">ID<input type="text" name="ID" autocomplete="off" class="layui-input" id="getid">QUERY<input type="text" name="QUERY" autocomplete="off" class="layui-input" id="getquery">TEXT<input type="text" name="TEXT" autocomplete="off" class="layui-input" id="gettext"></div></form>',
yes:function(index,layero){
a=JSON.stringify({//取出content中的内容
ID: $('#getid').val(),
QUERY: $('#getquery').val(),
TEXT: $('#gettext').val()
})
console.log(a)//{"ID":"12","QUERY":"1","TEXT":"1"}
console.log(typeof(a))//string
console.log('User clicked "Yes"');
layer.close(index);//這個表示按確定後就關閉圖層
$.ajax({
url:'/add',
type:'post',
data:{"datas":a},
dataType:'JSON',
success:function(res){
if(res.msg.search("添加成功") != -1){ // 如果響應消息中包含“刪除成功”(刪除成功),則顯示成功消息並重新加載頁面。
layer.msg("添加成功",{icon: 1, time: 1000}); //提交成功后刷新页面
}else{
layer.alert(res.msg); // 如果響應消息不包含“刪除成功”,它會顯示響應消息的警報。
}},
error:function (data) {
}
});
}
});
} else if (obj.event === 'delete') { // 监听删除操作
var checkStatus = table.checkStatus('table');
var data1=checkStatus.data
if (checkStatus&&checkStatus.data!=''){
var data=checkStatus.data
console.log("請求參數是:",data);
console.log("data的字符類型是:",typeof(data));
var datas = JSON.stringify(data)
console.log("轉換后的參數值是:",datas)
console.log("轉換后的參數值類型是:",typeof(datas));
$.ajax({
url:'/delete',
//method:'post',
type:'post',
data:{"datas":datas},
dataType:'JSON',
success:function(res){
if(res.msg.search("修改成功") != -1){ // 如果響應消息中包含“刪除成功”(刪除成功),則顯示成功消息並重新加載頁面。
layer.msg("修改成功",{icon: 1, time: 1000}); //提交成功后刷新页面
}else{
layer.alert(res.msg); // 如果響應消息不包含“刪除成功”,它會顯示響應消息的警報。
}},
error:function (data) {
}
});
}else{
layer.alert("請勾選您要修改的數據");
}
return false;
} else if (obj.event === 'edit') {
var checkStatus = table.checkStatus('table');
var data1=checkStatus.data
if (checkStatus&&checkStatus.data!=''){
var data=checkStatus.data
console.log("請求參數是:",data);
console.log("data的字符類型是:",typeof(data));
var datas = JSON.stringify(data)
console.log("轉換后的參數值是:",datas)
console.log("轉換后的參數值類型是:",typeof(datas));
$.ajax({
url:'/edit',
//method:'post',
type:'post',
data:{"datas":datas},
dataType:'JSON',
success:function(res){
if(res.msg.search("修改成功") != -1){
layer.msg("修改成功",{icon: 1, time: 1000});
}else{
layer.alert(res.msg);
}},
error:function (data) {
}
});
}else{
layer.alert("請勾選您要修改的數據");
}
return false;
}else if(obj.event === 'ExportAll'){
table.exportFile(d.config.id, exportData, 'xls');
}else if(obj.event === 'Export'){
var checkStatus = table.checkStatus('table')
if (checkStatus&&checkStatus.data!=''){
var data=checkStatus.data
console.log("請求參數是:",data);
console.log("data的字符類型是:",typeof(data));
table.exportFile(d.config.id, data,'xls');
}
else{
layer.alert("請勾選您要導出的數據");
}
return false;//避免表單重複提交
}
});
//监听表格复选框选择
table.on('checkbox(currentTableFilter)', function (obj) {
console.log(obj)
});
});
</script>
</body>
</html>
query_myself.py
# 引入Flask
from flask import Flask, render_template, request,jsonify,json
import cx_Oracle
import logging
# 创建Flask实例
one = Flask(__name__)
#填入你要连接的数据库资料
user = ''
password = ''
host = ''
service_name = ''
conn_str = f"{user}/{password}@{host}/{service_name}"
#定义一个函数,用于在前端没有任何输入值的情况下默认查询出前数100条数据
def data(id):
connect = cx_Oracle.connect(conn_str)
cursor = connect.cursor()
datalist = []
if id:
sql=f"SELECT ID,QUERY,TEXT FROM MYQUERY where ID='{id}'"
cursor.execute(sql)
res=cursor.fetchall()
else:
sql="SELECT ID,QUERY,TEXT FROM MYQUERY where rownum<=100"
cursor.execute(sql)
res=cursor.fetchall()
cursor.close()
connect.close()
for i in res:
item_date = {
"ID": i[0],
"QUERY": i[1],
"TEXT": i[2]
}
datalist.append(item_date)
return datalist
#主頁面路由
@one.route('/')
def login_test():
one.logger.info('info log')
one.logger.warning('warning log')
return render_template('login_myself.html')
#登录路由
@one.route('/login_myself', methods=['GET','POST'])
def login():
data = request.form.get("datas")
print("data1:",data)#data1: {"username":"Q22000109","password":"123456"}
print(type(data))#<class 'str'>
post_data = json.loads(data)#将post_data从JSON字符串转换为Python对象,json對象是一個鍵值形式的字符串,要轉換成
#python對象python才能識別
print("transferdata:",post_data)#transferdata: {'username': 'Q22000109', 'password': '123456'}
print("transfer",type(post_data))#transfer <class 'dict'>
connect = cx_Oracle.connect(conn_str)
cursor = connect.cursor()
sql="select count(*) from OP WHERE OP='%s' and pass='%s'"%(post_data['username'],post_data['password'])
print("sql is :",sql)#sql is : select count(*) from OP WHERE OP='Q22000109' and pass='123456'
cursor.execute(sql)
res=cursor.fetchall()
print(res)#[(1,)]
print(type(res))#<class 'list'>
cursor.close()
connect.close()
a=0
if res[0][0] == a:
return_res = {
"code": 0,
"msg": "您没有权限",
"count": 1,
"data": data
}
else:
return_res = {
"code": 0,
"msg": "登陆成功",
"count": 1,
"data": data
}
return jsonify(return_res)# 然后使用“jsonify”将“return_res”字典作为 JSON 响应返回
@one.route('/query_myself', methods=['GET','POST'])
def query():
return render_template('query_myself.html')
#定义一个路由,在前端没有传进任何数据的情况下调用data函数
@one.route('/searchall', methods=['GET','POST'])
def searchall():
id=0
datalist = data(id)
count = len(datalist)
return_res = {
"code": 0,
"msg": "success",
"count": count,
"data": datalist
}
return jsonify(return_res)
@one.route('/search', methods=['GET','POST'])
def search():
post_data = request.form.get("searchParams")#是从前端发送到后端的POST请求中的一个参数名,代表搜索参数
# 通过这个参数名,后端可以获取前端用户输入的搜索关键词等信息,从而进行相应的搜索操作。
post_data = json.loads(post_data)#将post_data从JSON字符串转换为Python对象,json對象是一個鍵值形式的字符串,要轉換成
#python對象python才能識別
datalist = data(post_data['ID'])# 'ID' 键是从 'post_data' 对象中提取的,用于从 'data' 函数中检索数据
print("DATALIST",datalist)#这里返回的是一个列表,列表里面是很多个字典
count = len(datalist)
return_res = {
"code": 0,
"msg": "請求成功",
"count": count,
"data": datalist
}
return jsonify(return_res)
@one.route('/add', methods=['GET','POST'])
def adddata():
datas=request.form.get("datas")
print("getdatas:",datas)#getdatas: {"ID":"11","QUERY":"1","TEXT":"1"}
print("datas:",type(datas))#datas: <class 'str'>
post_data = json.loads(datas)
print("post_data:",type(post_data))
connect = cx_Oracle.connect(conn_str)
cursor = connect.cursor()
sql="INSERT INTO MYQUERY VALUES('%s','%s','%s')"%(post_data['ID'],post_data['QUERY'],post_data['TEXT'])
print("sql is :",sql)
cursor.execute(sql)
connect.commit()
cursor.close()
connect.close()
return_res = {
"code": 0,
"msg": "添加成功",
"count": 1,
"data": post_data
}
return jsonify(return_res)
@one.route('/delete', methods=['GET','POST'])
def delete():
data = request.form.get("datas")
print("data1:",data)#data1: [{"ID":11,"QUERY":"1","TEXT":"1"}]
print(type(data))
post_data = json.loads(data)
print("transferdata:",post_data)
print("transfer",type(post_data))
connect = cx_Oracle.connect(conn_str)
cursor = connect.cursor()
nrows = len(post_data)
for i in range(nrows):
sql="delete from myquery where id = '%d'"%(post_data[0]['ID'])
print("sql is :",sql)
cursor.execute(sql)
connect.commit()
cursor.close()
connect.close()
return_res = {
"code": 0,
"msg": "删除成功",
"count": i,
"data": data
}
return jsonify(return_res)# 然后使用“jsonify”将“return_res”字典作为 JSON 响应返回
@one.route('/edit', methods=['GET','POST'])
def edit():
data = request.form.get("datas")
print("data1:",data)#data1: [{"ID":11,"QUERY":"1","TEXT":"1"}]
print(type(data))
# post_data = json.loads(data)
post_data = eval(data)
print("transferdata:",post_data)
print("transfer",type(post_data))
connect = cx_Oracle.connect(conn_str)
cursor = connect.cursor()
nrows = len(post_data)
for i in range(nrows):
sql="UPDATE MYQUERY SET QUERY='%s' ,TEXT='%s' WHERE ID='%s'"%(post_data[i]['QUERY'],post_data[i]['TEXT'],post_data[i]['ID'])
print("sql is :",sql)
cursor.execute(sql)
connect.commit()
cursor.close()
connect.close()
return_res = {
"code": 0,
"msg": "更新成功",
"count": i,
"data": data
}
return jsonify(return_res)# 然后使用“jsonify”将“return_res”字典作为 JSON 响应返回
if __name__ == '__main__':
handler = logging.FileHandler('flask.log')
one.logger.addHandler(handler)
one.run(debug=True,port=15002)