背景
在每天上班的时候,很多同事都有喝热水的习惯,但是饮水机内的热水量总是比较少的,如何避免等待,高效的接到热水是我接下来要做的事情的动机。
理论基础
- 在大量真实数据的情况下,可以分析出用水紧张的时间段和用水不紧张的时间段。在用水不紧张的时间去接热水,有热水的可能性比较大。
实现思路与理论基础
- 每天倒水的时候记录下,当前时间对应的剩余热水量
- 把历史数据提交给大模型,通过提示词让大模型来总结并预测结果
依赖服务
- 大模型服务
实现效果
实现代码
后台代码
from flask import Flask, render_template, request, redirect, url_for, jsonify
import sqlite3
from datetime import datetime
import requests
app = Flask(__name__)
# 连接到 SQLite 数据库
def connect_db():
conn = sqlite3.connect('test_data.db')
return conn
# 创建数据表
def create_table():
conn = connect_db()
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS test_data (
id INTEGER PRIMARY KEY,
test_date TEXT,
test_datetime TEXT,
water_level INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)''')
conn.commit()
conn.close()
# 插入测试数据
def insert_data(test_date, test_datetime, water_level):
conn = connect_db()
cursor = conn.cursor()
cursor.execute('''INSERT INTO test_data (test_date, test_datetime, water_level)
VALUES (?, ?, ?)''', (test_date, test_datetime, water_level))
conn.commit()
conn.close()
# 提交测试数据
@app.route('/submit', methods=['POST'])
def submit():
test_date = request.form['test_date']
test_datetime = f"{test_date} {request.form['test_time']}"
water_level = request.form['water_level']
insert_data(test_date, test_datetime, water_level)
return redirect(url_for('index'))
# 删除测试数据
@app.route('/delete/<int:id>', methods=['POST'])
def delete(id):
delete_data(id)
return redirect(url_for('index'))
# 删除测试数据
def delete_data(id):
conn = connect_db()
cursor = conn.cursor()
cursor.execute('''DELETE FROM test_data WHERE id = ?''', (id,))
conn.commit()
conn.close()
# 获取测试数据并按创建时间降序排序
def get_data_by_date_range(start_date, end_date, page, per_page):
conn = connect_db()
cursor = conn.cursor()
offset = (page - 1) * per_page
cursor.execute('''SELECT * FROM test_data WHERE test_date BETWEEN ? AND ?
ORDER BY created_at DESC LIMIT ? OFFSET ?''',
(start_date, end_date, per_page, offset))
data = cursor.fetchall()
cursor.execute('''SELECT COUNT(*) FROM test_data WHERE test_date BETWEEN ? AND ?''', (start_date, end_date))
total = cursor.fetchone()[0]
conn.close()
return data, total
# 获取最新的100条test_datetime和water_level数据
def get_latest_100_data():
conn = connect_db()
cursor = conn.cursor()
cursor.execute('''SELECT test_datetime, water_level FROM test_data
ORDER BY created_at DESC LIMIT 100''')
data = cursor.fetchall()
conn.close()
return data
# 首页,显示所有测试数据
@app.route('/')
def index():
today = datetime.today().strftime('%Y-%m-%d')
current_time = datetime.now().strftime('%H:%M')
start_date = request.args.get('start_date', today)
end_date = request.args.get('end_date', today)
page = int(request.args.get('page', 1))
per_page = 5 # 每页显示5条记录
data, total = get_data_by_date_range(start_date, end_date, page, per_page)
total_pages = (total + per_page - 1) // per_page
# 增加序号字段
data_with_index = [(i + 1 + (page - 1) * per_page, *row) for i, row in enumerate(data)]
return render_template('index.html', title="饮水机数据记录", data=data_with_index, today=today, current_time=current_time, start_date=start_date, end_date=end_date, page=page, total_pages=total_pages)
# 数据预测页面
@app.route('/predict')
def predict():
return render_template('predict.html', title="猜一猜")
# 数据预测结果处理
@app.route('/predict_result', methods=['POST'])
def predict_result():
# 获取最新的100条数据作为提示词
latest_data = get_latest_100_data()
prompt = "以下是最近期办公室饮水机时间和剩余热水水量的数据:---\n "
for entry in latest_data:
prompt += f"时间: {entry[0]}, 热水水量: {entry[1]}%\n"
prompt += " 请总结规律并帮我预测时间是:"+datetime.now().strftime("%Y-%m-%d %H:%M") \
+ "的热水水量是多少?你需要严格按照 '预测结果是:当前水量是XXX' 的格式回答,千万不要做任何解释"
# 定义要发送的 JSON 数据
data = {
"model": "llama3-chinese:8b",
"messages": [
{
"role": "system",
"content": "你是一个高冷的规律总结大师,可以根据提供的数据进行预测结果,不需要做成任何解释"
},
{
"role": "user",
"content": prompt
}
],
"options": {
"temperature": 0.5
},
"stream": False
}
# 定义目标 URL
url = "http://192.168.10.70:11434/api/chat"
print("prompt:"+prompt)
# 发送 POST 请求,并将 JSON 数据作为请求体发送
response = requests.post(url, json=data)
return render_template('predict.html', title="猜一猜", prediction=response.json()['message']['content'])
if __name__ == '__main__':
create_table()
app.run(debug=True)
前台代码
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>{{ title }}</title>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" rel="stylesheet">
<style>
.form-section, .list-section {
padding: 20px;
border-radius: 8px;
}
.form-section {
background-color: #f8f9fa;
border: 1px solid #e9ecef;
}
.list-section {
background-color: #ffffff;
border: 1px solid #dee2e6;
}
.navbar {
margin-bottom: 20px;
}
</style>
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-light bg-light">
<a class="navbar-brand" href="/">饮水机数据记录</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav">
<li class="nav-item">
<a class="nav-link" href="/">首页</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/predict">猜一猜</a>
</li>
</ul>
</div>
</nav>
<div class="container mt-5">
<div class="row">
<div class="col-md-4">
<div class="form-section">
<h2 class="mb-4">记录数据</h2>
<form action="/submit" method="post">
<div class="form-group">
<label for="test_date">日期:</label>
<input type="date" id="test_date" name="test_date" class="form-control" value="{{ today }}" required>
</div>
<div class="form-group">
<label for="test_time">时间:</label>
<input type="time" id="test_time" name="test_time" class="form-control" value="{{ current_time }}" required>
</div>
<div class="form-group">
<label for="water_level">储水量百分比:</label>
<select id="water_level" name="water_level" class="form-control" required>
<option value="0">0%</option>
<option value="20">20%</option>
<option value="40">40%</option>
<option value="60">60%</option>
<option value="80">80%</option>
<option value="100">100%</option>
</select>
</div>
<button type="submit" class="btn btn-primary btn-block">提交</button>
</form>
</div>
</div>
<div class="col-md-8">
<div class="list-section">
<h2 class="mb-4">已记录数据</h2>
<form method="get" action="/" class="mb-4">
<div class="form-row">
<div class="col">
<label for="start_date">开始日期:</label>
<input type="date" id="start_date" name="start_date" class="form-control" value="{{ start_date }}" required>
</div>
<div class="col">
<label for="end_date">结束日期:</label>
<input type="date" id="end_date" name="end_date" class="form-control" value="{{ end_date }}" required>
</div>
<div class="col align-self-end">
<button type="submit" class="btn btn-secondary btn-block">筛选</button>
</div>
</div>
</form>
<table class="table table-bordered">
<thead class="thead-light">
<tr>
<th>序号</th>
<th>日期</th>
<th>时间</th>
<th>储水量百分比</th>
<th>操作</th>
</tr>
</thead>
<tbody>
{% for row in data %}
<tr>
<td>{{ row[0] }}</td> <!-- 这里使用序号 -->
<td>{{ row[2] }}</td>
<td>{{ row[3] }}</td>
<td>{{ row[4] }}%</td>
<td>
<form action="/delete/{{ row[1] }}" method="post">
<button type="submit" class="btn btn-danger btn-sm">删除</button>
</form>
</td>
</tr>
{% endfor %}
</tbody>
</table>
<nav aria-label="Page navigation">
<ul class="pagination justify-content-center">
{% if page > 1 %}
<li class="page-item">
<a class="page-link" href="/?start_date={{ start_date }}&end_date={{ end_date }}&page={{ page - 1 }}">上一页</a>
</li>
{% endif %}
{% if page < total_pages %}
<li class="page-item">
<a class="page-link" href="/?start_date={{ start_date }}&end_date={{ end_date }}&page={{ page + 1 }}">下一页</a>
</li>
{% endif %}
</ul>
</nav>
</div>
</div>
</div>
</div>
<script src="{{ url_for('static', filename='js/jquery-3.7.1.min.js') }}"></script>
<script src="{{ url_for('static', filename='js/bootstrap.js') }}"></script>
</body>
</html>
总结
以上代码并不是完整的代码,可以到 gitee仓库下载。也可以到资源中下载。如果你觉得我的文章对你有帮助,请点赞关注。有任何问题都可以留言给我。
常用查询sql
- 查询每天九点到几点三十之间时间段内的数据
SELECT *
FROM test_data
WHERE strftime('%H', test_datetime) = '09'
AND strftime('%M', test_datetime) >= '00'
AND strftime('%M', test_datetime) < '30';