【物联网】基于树莓派的物联网开发【22】——树莓派获取传感器数据实时存储实战

场景介绍

今天程序猫带领大家如何实时获取树莓派传感器温湿度数据，并自动存储到数据库中。确保数据的持续性。

实现过程

硬件连接

树莓派4b连接GPIO引脚与DHT11传感器;
硬件只涉及树莓派、DHT11传感器。
DHT11的信号引脚连接树莓派的GPIO17， DHT11的Vdd（+）和GND引脚（-）分别连接树莓派的电源正极和GND引脚。

启动MQTT服务

终端启动Mosquitto服务

sudo systemctl start mosquitto

读取传感器数据存储数据库

在数据采集模块中添加数据库写入的逻辑，编写脚本代码sensor_save.py

import timeimport boardimport adafruit_dhtfrom datetime import datetimeimport paho.mqtt.client as mqttimport jsonimport sqlite3from pathlib import Path# MQTT配置broker = \"192.168.137.31\"port = 1883topic = \"DHT11_message\"# 传感器引脚配置SENSOR_PIN = board.D17 # 使用CircuitPython标准命名（物理引脚11）# 数据库配置DB_NAME = \"sensor_data.db\"DB_TABLE = \"environment_data\"# 初始化数据库def init_db(): # 检查数据库文件是否存在，不存在则创建 db_file = Path(DB_NAME) if not db_file.exists(): conn = sqlite3.connect(DB_NAME) cursor = conn.cursor() cursor.execute(f\"\"\" CREATE TABLE {DB_TABLE} ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL, temperature REAL NOT NULL, humidity REAL NOT NULL ) \"\"\") conn.commit() conn.close() print(f\"数据库 {DB_NAME} 和表 {DB_TABLE} 创建成功\") else: print(f\"使用现有数据库 {DB_NAME}\")# 写入数据到数据库def write_to_db(timestamp, temperature, humidity): try: conn = sqlite3.connect(DB_NAME) cursor = conn.cursor() cursor.execute(f\"\"\" INSERT INTO {DB_TABLE} (timestamp, temperature, humidity) VALUES (?, ?, ?) \"\"\", (timestamp, temperature, humidity)) conn.commit() conn.close() return True except sqlite3.Error as e: print(f\"数据库写入错误: {e}\") return False# 初始化MQTT客户端client = mqtt.Client()client.connect(broker, port)# 初始化数据库init_db()try: # 添加 use_pulseio=False 参数 dhtDevice = adafruit_dht.DHT11(SENSOR_PIN, use_pulseio=False) print(\"传感器初始化成功，开始数据采集......\") while True: try: # 读取温湿度数据 temperature = dhtDevice.temperature humidity = dhtDevice.humidity # 检查有效读数 if temperature is None or humidity is None: raise RuntimeError(\"读取到无效值\") # 获取当前时间戳 recDate = datetime.now().strftime(\'%Y-%m-%d %H:%M:%S\') # 1. 写入数据库 if write_to_db(recDate, temperature, humidity): print(f\"✓ [{recDate}] 数据库写入成功: 温度={temperature}°C, 湿度={humidity}%\") else: print(f\"✗ [{recDate}] 数据库写入失败\") # 2. 准备MQTT消息并发送 msg = json.dumps({ \'Tem\': temperature, \'Hum\': humidity, \'Date\': recDate }) result = client.publish(topic, msg) if result[0] == mqtt.MQTT_ERR_SUCCESS: print(f\"✓ [{recDate}] MQTT发送成功: {msg}\") else: print(f\"✗ MQTT发送失败，错误码: {result[0]}\") except RuntimeError as e: print(f\"传感器读取错误: {e}\") except Exception as e: print(f\"意外错误: {e}\") time.sleep(10.0) # 采集间隔10sexcept KeyboardInterrupt: print(\"程序被用户终止\")except RuntimeError as e: print(f\"初始化失败: {e}\") exit(1)finally: # 清理资源 if \'dhtDevice\' in locals(): dhtDevice.exit() client.disconnect() print(\"资源已释放\")

进入虚拟环境

树莓派终端输入命令：

source adafruit_env/bin/activate

运行脚本

python3 sensor_save.py

可以看到传感器数据采集写入数据库

安装DB Browser for SQLite‌工具

适用于Raspbian/Debian系统：
控制台输入命令：

sudo apt install sqlitebrowser

打开可视化工具，并打开sensor_data.db,查看表数据environment_data写入成功。