ESP32 → HTTP → Python API → TimescaleDB

安裝PostgreSQL 15(不選擇太新的版本是因為怕不支援timescaledb)

先確認是否有timescaledb [SELECT * FROM pg_extension;]

沒有的話 先安裝 TimescaleDB:

一開始遇到問題(TimescaleDB installer 找不到 PostgreSQL 的 pg_config )

所以我把載檔移到了 這個路徑下

C:\Users\Hon\Downloads\timescaledb-postgresql-15-windows-amd64\timescaledb

再去執行setup.exe

有問題都按y

在PostgreSQL執行:

CREATE EXTENSION timescaledb; SELECT * FROM pg_extension;

開 VSCode Terminal 

python api_server.py

from flask import Flask, request, jsonify, render_template import psycopg2 app = Flask(__name__) conn = psycopg2.connect(     host="localhost",     database="postgres",     user="postgres",     password="1234" ) @app.route("/") def index():     return render_template("index.html") @app.route("/data", methods=["GET"]) def get_data():     cur = conn.cursor()     cur.execute("""         SELECT time, temperature         FROM sensor_data         ORDER BY time DESC         LIMIT 50     """)     rows = cur.fetchall()     data = []     for row in rows:         data.append({             "time": row[0].strftime("%H:%M:%S"),             "temp": row[1]         })     cur.close()     response = jsonify(data[::-1])     response.headers["Cache-Control"] = "no-cache"     return response @app.route("/sensor", methods=["POST"]) def receive_data():     data = request.json     device = data.get("device", "unknown")     temp = data.get("temp", 0)     cur = conn.cursor()     cur.execute(         """         INSERT INTO sensor_data(time, device_id, temperature)         VALUES (NOW(), %s, %s)         """,         (device, temp)     )     conn.commit()     cur.close()     print("Inserted:", device, temp)     return jsonify({"status": "ok"}) if __name__ == "__main__":     app.run(host="0.0.0.0", port=5000, debug=True)

arduino程式:

#include <SPI.h> #include <Ethernet2.h> #include <ModbusMaster.h> // ===== W5500 ===== #define W5500_CS 6 #define W5500_RST 14 byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; IPAddress server(192,168,0,220); // 你的電腦IP EthernetClient client; // ===== RS485 ===== #define RS_TX_PIN 5 #define RS_RX_PIN 4 #define RS_ENAMBLE_232_PIN 21 #define RS_ENAMBLE_422_PIN 16 #define RS_ENAMBLE_485_PIN 15 #define RS_BAUDRATE 9600 ModbusMaster node; // ===== Modbus callback ===== void preTransmission() { while (Serial2.available()) Serial2.read(); } void postTransmission() { Serial2.flush(); delayMicroseconds(100); while (Serial2.available()) Serial2.read(); } // ===== setup ===== void setup() { Serial.begin(115200); // ===== W5500 reset ===== pinMode(W5500_RST, OUTPUT); digitalWrite(W5500_RST, LOW); delay(20); digitalWrite(W5500_RST, HIGH); delay(100); // ⚠️ 用你成功的 SPI 腳位 SPI.begin(12,13,11,W5500_CS); Ethernet.init(W5500_CS); IPAddress ip(192,168,0,50); IPAddress gateway(192,168,0,1); IPAddress subnet(255,255,255,0); Ethernet.begin(mac, ip, gateway, gateway, subnet); Serial.print("IP: "); Serial.println(Ethernet.localIP()); // ===== RS485 ===== pinMode(RS_ENAMBLE_232_PIN, OUTPUT); pinMode(RS_ENAMBLE_422_PIN, OUTPUT); pinMode(RS_ENAMBLE_485_PIN, OUTPUT); digitalWrite(RS_ENAMBLE_232_PIN, LOW); digitalWrite(RS_ENAMBLE_422_PIN, LOW); digitalWrite(RS_ENAMBLE_485_PIN, HIGH); Serial2.begin(RS_BAUDRATE, SERIAL_8N1, RS_RX_PIN, RS_TX_PIN); node.begin(1, Serial2); node.preTransmission(preTransmission); node.postTransmission(postTransmission); Serial.println("System Ready"); } // ===== loop ===== void loop() { uint8_t result; uint16_t raw; // ✅ 用你成功的地址 result = node.readHoldingRegisters(43, 1); if (result == node.ku8MBSuccess) { raw = node.getResponseBuffer(0); float temperature = raw / 10.0; Serial.print("Temp: "); Serial.println(temperature); // ===== HTTP 傳送 ===== if (client.connect(server, 5000)) { String json = "{\"device\":\"CH4_SENSOR\",\"temp\":" + String(temperature) + "}"; client.println("POST /sensor HTTP/1.1"); client.println("Host: 192.168.0.220"); client.println("Content-Type: application/json"); client.print("Content-Length: "); client.println(json.length()); client.println(); client.println(json); Serial.println("Data sent!"); client.stop(); } else { Serial.println("HTTP failed"); } } else { Serial.print("Modbus error: "); Serial.println(result); } delay(2000); }

建立檔案： templates/index.html

你的專案/

├── api_server.py

└── templates/

        └── index.html

打開： http://localhost:5000 (同個網域的都可以連線網站)

index.html:

<!DOCTYPE html> <html> <head>   <title>Factory Dashboard</title>   <script src="https://cdn.jsdelivr.net/npm/chart.js"></script> </head> <body style="background:#111;color:white;font-family:sans-serif"> <h1>? 工廠監控畫面</h1> <h2>溫度: <span id="temp">--</span> °C</h2> <canvas id="chart" width="600" height="300"></canvas> <script> const ctx = document.getElementById('chart').getContext('2d'); let chart = new Chart(ctx, {     type: 'line',     data: {         labels: [],         datasets: [{             label: 'Temperature',             data: [],             borderColor: 'lime',             fill: false         }]     },     options: {         scales: {             y: {                 ticks: { color: "white" }             },             x: {                 ticks: { color: "white" }             }         },         plugins: {             legend: {                 labels: { color: "white" }             }         }     } }); function updateData() {     fetch('/data')     .then(res => res.json())     .then(data => {         let labels = [];         let temps = [];         data.forEach(d => {             labels.push(d.time);             temps.push(d.temp);         });         chart.data.labels = labels;         chart.data.datasets[0].data = temps;         chart.update();         // 顯示最新溫度         let latest = temps[temps.length - 1];         document.getElementById("temp").innerText = latest;         // 警報（> 50°C）         if (latest > 50) {             document.body.style.background = "red";         } else {             document.body.style.background = "#111";         }     }); } // 每2秒更新 setInterval(updateData, 2000); </script> </body> </html>

網頁畫面: