数据库sqlite3
以创建数据库（2023-12-28.db），当前时间13点，精确到小时（table_13），创建。
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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
import sqlite3
import os
from datetime import datetime
# 指定数据库文件夹路径
db_dir = "/home/pi/Desktop/sleep/file/sqlite_file/"
def create_table(db_path, table_name):
# 连接数据库，如果不存在则会创建
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
sql = '''
CREATE TABLE IF NOT EXISTS '{}' (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
temperature FLOAT,
humidity FLOAT,
light_intensity FLOAT,
co2_level FLOAT,
sound_level FLOAT,
wind_speed FLOAT
)
'''.format(table_name)
# 创建表
cursor.execute(sql)
# 提交更改并关闭连接
conn.commit()
conn.close()
def insert_data(db_path, table_name, temperature, humidity, light_intensity, co2_level, sound_level, wind_speed):
create_table(db_path, table_name) # 创建表格
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# 获取当前时间
current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
# 插入数据
cursor.execute(f'''
INSERT INTO {table_name} (timestamp, temperature, humidity, light_intensity, co2_level, sound_level, wind_speed)
VALUES (?, ?, ?, ?, ?, ?, ?)
''', (current_time,temperature, humidity, light_intensity, co2_level, sound_level, wind_speed))
# 提交更改并关闭连接
conn.commit()
conn.close()
def query_data(db_path, table_name):
create_table(db_path, table_name) # 创建表格
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# 查询数据
cursor.execute(f'SELECT * FROM {table_name}')
data = cursor.fetchall()
# 关闭连接
conn.close()
return data
def insert_batch_data(db_path, table_name, batch_data):
if not batch_data:
return
create_table(db_path, table_name) # 创建表格
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# 计算平均值
avg_temperature = sum(entry[0] for entry in batch_data) / len(batch_data)
avg_humidity = sum(entry[1] for entry in batch_data) / len(batch_data)
avg_light_intensity = sum(entry[2] for entry in batch_data) / len(batch_data)
avg_co2_level = sum(entry[3] for entry in batch_data) / len(batch_data)
avg_sound_level = sum(entry[4] for entry in batch_data) / len(batch_data)
avg_wind_speed = sum(entry[5] for entry in batch_data) / len(batch_data)
# 获取当前时间
current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
# 插入平均值数据
cursor.execute(f'''
INSERT INTO {table_name} (timestamp, temperature, humidity, light_intensity, co2_level, sound_level, wind_speed)
VALUES (?, ?, ?, ?, ?, ?, ?)
''', (current_time, avg_temperature, avg_humidity, avg_light_intensity, avg_co2_level, avg_sound_level, avg_wind_speed))
# 提交更改并关闭连接
conn.commit()
conn.close()
def get_db_path():
# 获取当前日期
current_date = datetime.now().strftime('%Y-%m-%d')
# 构建数据库文件名
db_name = f"{current_date}.db"
# 构建数据库文件路径
db_path = os.path.join(db_dir, db_name)
return db_path
def get_table_name():
# 获取当前小时
current_hour = datetime.now().strftime('%H')
# 构建表名
table_name = f"table_{current_hour}"
return table_name
# 示例用法
#db_path = get_db_path()
#table_name = get_table_name()
# 插入单条数据
#insert_data(db_path, table_name, 25.5, 60, 500, 400, 70, 3.5)
# 插入批量数据
#data_batch = [
# (25.5, 60, 500, 400, 70, 3.5),
# (26.5, 62, 510, 410, 75, 3.8),
# 添加更多数据
#]
#insert_batch_data(db_path, table_name, data_batch)
# 查询数据
#query_result = query_data(db_path, table_name)
#print(query_result)