Python + Flask 做个接口-员工带薪年假查询-技术文章-北屋教程网

学Python有一段时间了，但接口还没去做过。也就是前端html，css，javascripts，后端就用Python。

先看效果图：

安装Python 和Flask这些我跳过，自行安装。

总体目录结构：

app.py代码如下：

一：导入必要的python库，记的要有app = Flask(__name__)

from flask import Flask, render_template, jsonify, request
import pyodbc
from datetime import datetime as dt
import pandas as pd

app = Flask(__name__)

二：先配置连接到后端数据库的连接信息

# 定义数据库连接信息
server = '0.0.0.0\sql2008' #IP地址
database = 'XXXX' # 数据库名
username = 'sa'
password = ''
# 连接Microsoft SQL Server数据库
try:
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
print('数据库连接成功!')
except Exception as e:
print('数据库连接失败'+e)
conn.close()

三：从数据库中获取数据的接口

# 按钮“查询”的触发函数
@app.route('/api/data', methods=['GET'])
def get_data():
as_year = request.args.get('as_year')
as_empid = request.args.get('as_empid')
# print(as_year + as_empid)
if as_year and as_empid:
data = onclick(as_year, as_empid)
return jsonify(data)
else:
return jsonify({"error": "Missing as_year or as_empid parameters"}), 400

def onclick(as_year,as_empid):
"显示查询年假表格数据"
# 表查询语句中文显示会乱码的要convert 成navrchar(12)类型
ls_year = as_year
today = str(dt.today().date())
# print(today)
Enddate = dt.strptime(ls_year + '-12-31', "%Y-%m-%d")
Empid = as_empid
sql_cmd = (f"SELECT emp_id as 工号 from 表名" where 条件）
data = pd.read_sql(sql_cmd, conn)
# print(data)
return data.to_dict(orient='records')

四：最后给flask配置一个主页的路由。

# API路由
@app.route('/')
def index():
return render_template('index.html')
if __name__ == '__main__':
app.run(debug=True, host='0.0.0.0', port=5000)

五：index.html 代码

<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>年度带薪年假查询</title>
<script src="
https://cdn.tailwindcss.com"></script>
<link href="
https://cdn.jsdelivr.net/npm/font-awesome@
4.7.0/css/font-awesome.min.css" rel="stylesheet">
<script>
tailwind.config = {
theme: {
extend: {
colors: {
primary: '#3874c8',
},
fontFamily: {
sans: ['Inter', 'system-ui', 'sans-serif'],
},
}
}
}
</script>
<style type="text/tailwindcss">
@layer utilities {
.content-auto {
content-visibility: auto;
}
.fixed-bottom-right {
position: fixed;
right: 1rem;
bottom: 1rem;
}
.header-height {
height: 50px;
}
.table-shadow {
box-shadow: 0 1px 3px rgba(0,0,0,0.12), 0 1px 2px rgba(0,0,0,0.24);
}
}
</style>
</head>
<body class="bg-gray-50 min-h-screen flex flex-col">

<header class="bg-primary header-height text-white flex items-center justify-between px-4 shadow-lg z-10">
<h1 class="text-xl font-bold">年度带薪年假查询</h1>
<button id="menu-toggle" class="p-2 rounded-full hover:bg-primary-700 transition-colors">
<i class="fa fa-bars text-xl"></i>
</button>
</header>

<main class="flex-1 p-4 md:p-6">

<div class="bg-white rounded-lg shadow-md p-4 mb-4">
<h2 class="text-lg font-semibold mb-4 text-gray-700">查询条件</h2>
<div class="flex flex-col md:flex-row gap-4">
<div class="flex-1">
<label for="as_year" class="block text-sm font-medium text-gray-700 mb-1">输入年度</label>
<input type="text" id="as_year" class="w-full px-3 py-2 border border-gray-300 rounded-md focus:outline-none focus:ring-2 focus:ring-primary/50 focus:border-primary transition">
</div>
<div class="flex-1">
<label for="as_empid" class="block text-sm font-medium text-gray-700 mb-1">请输入工号</label>
<input type="text" id="as_empid" class="w-full px-3 py-2 border border-gray-300 rounded-md focus:outline-none focus:ring-2 focus:ring-primary/50 focus:border-primary transition">
</div>
<div class="flex items-end">
<button id="query-btn" class="w-full md:w-auto bg-primary hover:bg-primary/90 text-white font-medium py-2 px-4 rounded-md transition duration-300 transform hover:scale-[1.02] focus:outline-none focus:ring-2 focus:ring-offset-2 focus:ring-primary">
<i class="fa fa-search mr-2"></i>查询
</button>
</div>
</div>
</div>

<div class="bg-white rounded-lg shadow-md p-4">
<h2 class="text-lg font-semibold mb-4 text-gray-700">查询结果</h2>

<div id="loading" class="hidden flex justify-center items-center py-16">
<div class="animate-spin rounded-full h-12 w-12 border-t-2 border-b-2 border-primary"></div>
</div>

<div id="table-container" class="hidden overflow-x-auto">
<table class="min-w-full divide-y divide-gray-200 table-shadow">
<thead class="bg-gray-50">
<tr>
<th scope="col" class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">工号</th>
<th scope="col" class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">姓名</th>
<th scope="col" class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">入职日期</th>
<th scope="col" class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">雇佣状态</th>
<th scope="col" class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">可休时数</th>
<th scope="col" class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">工龄</th>
<th scope="col" class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">己休时数</th>
</tr>
</thead>
<tbody id="result-body" class="bg-white divide-y divide-gray-200">

</tbody>
</table>

<div id="pagination" class="flex items-center justify-between px-4 py-3 sm:px-6 mt-4">
<div class="hidden sm:flex-1 sm:flex sm:items-center sm:justify-between">
<div>
<p class="text-sm text-gray-700">
显示第 <span id="page-range" class="font-medium">1-2</span> 条，共 <span id="total-count" class="font-medium">2</span> 条
</p>
</div>
<div>
<nav class="relative z-0 inline-flex rounded-md shadow-sm -space-x-px" aria-label="Pagination">
<a href="#" class="relative inline-flex items-center px-2 py-2 rounded-l-md border border-gray-300 bg-white text-sm font-medium text-gray-500 hover:bg-gray-50">
<span class="sr-only">上一页</span>
<i class="fa fa-chevron-left"></i>
</a>
<a href="#" class="relative inline-flex items-center px-4 py-2 border border-gray-300 bg-primary text-sm font-medium text-white hover:bg-primary/90">
1
</a>
<a href="#" class="relative inline-flex items-center px-4 py-2 border border-gray-300 bg-white text-sm font-medium text-gray-700 hover:bg-gray-50">
2
</a>
<span class="relative inline-flex items-center px-4 py-2 border border-gray-300 bg-white text-sm font-medium text-gray-700">
...
</span>
<a href="#" class="relative inline-flex items-center px-4 py-2 border border-gray-300 bg-white text-sm font-medium text-gray-700 hover:bg-gray-50">
4
</a>
<a href="#" class="relative inline-flex items-center px-2 py-2 rounded-r-md border border-gray-300 bg-white text-sm font-medium text-gray-500 hover:bg-gray-50">
<span class="sr-only">下一页</span>
<i class="fa fa-chevron-right"></i>
</a>
</nav>
</div>
</div>
</div>
</div>

<div id="empty-state" class="hidden flex flex-col items-center justify-center py-16">
<i class="fa fa-search text-6xl text-gray-300 mb-4"></i>
<p class="text-gray-500">请输入查询条件并点击查询按钮</p>
</div>

<div id="error-state" class="hidden bg-red-50 border-l-4 border-red-400 p-4 my-4">
<div class="flex">
<div class="flex-shrink-0">
<i class="fa fa-exclamation-triangle text-red-500"></i>
</div>
<div class="ml-3">
<p id="error-message" class="text-sm text-red-700"></p>
</div>
</div>
</div>

<div id="no-result" class="hidden flex flex-col items-center justify-center py-16">
<i class="fa fa-folder-open-o text-6xl text-gray-300 mb-4"></i>
<p class="text-gray-500">没有找到匹配的记录</p>
</div>
</div>
</main>

<footer class="bg-white border-t border-gray-200 py-4 px-6">
<div class="text-center text-gray-500 text-sm">
<span id="footer-text"></span>
</div>
</footer>

<div id="right-drawer" class="fixed inset-y-0 right-0 w-64 bg-white shadow-lg transform translate-x-full transition-transform duration-300 ease-in-out z-20 lg:hidden">
<div class="p-4">
<div class="flex justify-between items-center mb-4">
<h3 class="text-lg font-semibold text-gray-700">菜单</h3>
<button id="close-drawer" class="p-1 rounded-full hover:bg-gray-100 transition-colors">
<i class="fa fa-times"></i>
</button>
</div>
<nav class="space-y-2">
<a href="#" class="block px-4 py-2 rounded-md text-base font-medium text-gray-700 hover:bg-gray-50">首页</a>
<a href="#" class="block px-4 py-2 rounded-md text-base font-medium text-gray-700 hover:bg-gray-50">报表管理</a>
<a href="#" class="block px-4 py-2 rounded-md text-base font-medium text-gray-700 hover:bg-gray-50">员工管理</a>
<a href="#" class="block px-4 py-2 rounded-md text-base font-medium text-gray-700 hover:bg-gray-50">系统设置</a>
</nav>
</div>
</div>

<div id="overlay" class="fixed inset-0 bg-black bg-opacity-50 z-10 hidden transition-opacity duration-300"></div>
<script>
// 侧边栏控制
const menuToggle = document.getElementById('menu-toggle');
const rightDrawer = document.getElementById('right-drawer');
const closeDrawer = document.getElementById('close-drawer');
const overlay = document.getElementById('overlay');

menuToggle.addEventListener('click', () => {

rightDrawer.classList.toggle('translate-x-full');
overlay.classList.toggle('hidden');

document.body.classList.toggle('overflow-hidden');
});

closeDrawer.addEventListener('click', () => {
rightDrawer.classList.add('translate-x-full');
overlay.classList.add('hidden');

document.body.classList.remove('overflow-hidden');
});
overlay.addEventListener('click', () => {
rightDrawer.classList.add('translate-x-full');
overlay.classList.add('hidden');

document.body.classList.remove('overflow-hidden');
});
// 设置页脚日期
document.getElementById('footer-text').textContent = new Date().getFullYear();
// 显示空状态
document.getElementById('empty-state').classList.remove('hidden');
document.getElementById('table-container').classList.add('hidden');
document.getElementById('error-state').classList.add('hidden');
document.getElementById('no-result').classList.add('hidden');
// 查询按钮点击事件
document.getElementById('query-btn').addEventListener('click', async function() {
// 获取输入值
const as_year = document.getElementById('as_year').value.trim();
const as_empid = document.getElementById('as_empid').value.trim();
// 简单验证
if (!as_year) {
showError('请输入年度');
return;
}
if (!as_empid) {
showError('请输入工号');
return;
}
// 显示加载状态
document.getElementById('empty-state').classList.add('hidden');
document.getElementById('table-container').classList.add('hidden');
document.getElementById('error-state').classList.add('hidden');
document.getElementById('no-result').classList.add('hidden');
document.getElementById('loading').classList.remove('hidden');
try {
// 调用真实的 API
await fetchApiData(as_year, as_empid);
} catch (error) {
showError(error.message);
} finally {
// 隐藏加载状态
document.getElementById('loading').classList.add('hidden');
}
});
// 调用真实的 API
async function fetchApiData(year, empId) {
const apiUrl = `
http://192.168.0.41:5000/api/data?as_year=${year}&as_empid=${empId}`;
try {
const response = await fetch(apiUrl);
if (!response.ok) {
throw new Error(`HTTP error! status: ${response.status}`);
}
const data = await response.json();
// 清空表格内容
const resultBody = document.getElementById('result-body');
resultBody.innerHTML = '';
// 如果有数据，填充表格
if (data.length > 0) {
data.forEach(item => {
const row = document.createElement('tr');
row.className = 'hover:bg-gray-50 transition';
row.innerHTML = `
<td class="px-6 py-4 whitespace-nowrap text-sm font-medium text-gray-900">${item['工号']}</td>
<td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">${item['姓名']}</td>
<td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">${item['入职日期']}</td>
<td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">${item['雇佣状态']}</td>
<td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">${item['可休时数']}</td>
<td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">${item['工龄']}</td>
<td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">${item['己休时数']}</td>
`;
resultBody.appendChild(row);
});
// 显示表格
document.getElementById('table-container').classList.remove('hidden');
// 更新分页信息
document.getElementById('page-range').textContent = `1-${data.length}`;
document.getElementById('total-count').textContent = data.length;
} else {
// 显示无结果状态
document.getElementById('no-result').classList.remove('hidden');
}
} catch (error) {
throw error;
}
}
// 显示错误信息
function showError(message) {
document.getElementById('error-message').textContent = message;
document.getElementById('error-state').classList.remove('hidden');
}
</script>
</body>
</html>

六：启动后端服务器

出现这样表示成功了，

七：浏览器上输入http://192.168.0.41:5000 或 http://127.0.0.1:5000 ，就可以了。

#python自学# #pythonflask##flask框架有什么用#

北屋教程网

专注编程知识分享，从入门到精通的编程学习平台

Python + Flask 做个接口-员工带薪年假查询