1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
import streamlit as st
import pandas as pd
import altair as alt
from peewee import MySQLDatabase, Model, DecimalField, DateTimeField
from playhouse.shortcuts import model_to_dict
# 连接到 MySQL 数据库
db = MySQLDatabase('dianfei', host="192.168.1.190", user='root', passwd='123456', port=3306)
# 定义模型
class ChaZuo(Model):
charge = DecimalField()
time = DateTimeField()
class Meta:
database = db
class KongTiao(Model):
charge = DecimalField()
time = DateTimeField()
class Meta:
database = db
# 查询数据
def get_data(model):
query = model.select()
data = pd.DataFrame(list(map(model_to_dict, query)))
data['time'] = pd.to_datetime(data['time']) # 确保时间字段是 datetime 类型
return data
def web(title='插座', model=ChaZuo):
# 获取数据并转换为 DataFrame
data = get_data(model)
# 确保时间列是索引
data.set_index('time', inplace=True)
st.markdown(f"### {title}耗电")
# 选择时间间隔,并设置默认值为 "10分钟"
time_interval = st.selectbox(
"选择时间间隔",
["10分钟", "半小时", "小时", "天"],
index=0, # index=0 表示默认选中第一个选项,即 "10分钟"
key=title
)
# 根据选择的时间间隔调整数据
if time_interval == "10分钟":
resampled_data = data.resample("10T").last()
elif time_interval == "半小时":
resampled_data = data.resample("30T").last()
elif time_interval == "天":
resampled_data = data.resample("D").last()
else: # 默认为小时
resampled_data = data.resample("H").last()
# 计算电量的变化值,并取绝对值
resampled_data['charge_diff'] = resampled_data['charge'].diff().fillna(resampled_data['charge']).abs()
resampled_data['charge_diff'] = resampled_data['charge_diff'].astype(float)
# 重置索引
resampled_data = resampled_data.reset_index()
x_format = "%m-%d %H:%M"
# 绘制折线图
line_chart = alt.Chart(resampled_data[1:]).mark_line(point=True).encode(
x=alt.X("time:T",timeUnit='monthdatehoursminutes', axis=alt.Axis(format=x_format), title="时间"), # 添加 title 属性
y=alt.Y("charge_diff:Q", axis=alt.Axis(format=".3f"), title="使用电费"), # 添加 axis 属性
tooltip=[alt.Tooltip("time:T",timeUnit='monthdatehoursminutes', title="时间", format=x_format),
alt.Tooltip("charge_diff:Q", format=".3f", title="使用电费")]
).interactive()
st.altair_chart(line_chart, use_container_width=True)
web('插座', ChaZuo)
web('空调', KongTiao)
|