如何将树莓派3火焰传感器的火情检测数据写入MySQL数据库

Hey there! You’ve got a solid foundation with your existing code—let’s tweak it to get those flame detection events safely stored in your MySQL database. Here’s a step-by-step breakdown to make this work reliably:

1. First, Create Your MySQL Table #

Before writing data, you need a place to store it. Log into your MySQL server and run this SQL to create a flame_detections table (feel free to adjust the name or columns if needed):

CREATE TABLE flame_detections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    detection_time DATETIME NOT NULL,
    status VARCHAR(20) NOT NULL
);

This table tracks a unique ID for each event, the exact timestamp of the detection, and a status label (like "FLAME_DETECTED" or "NO_FLAME" if you want to log state changes).

2. Update Your Python Code to Insert Data #

We’ll expand your callback function to handle database inserts, add error handling, and ensure proper resource cleanup. Here’s the full updated code:

import RPi.GPIO as GPIO
import time
import MySQLdb
from datetime import datetime

# Database connection setup
db = MySQLdb.connect(host="localhost", user="root", passwd="123456", db="raspbd")
cur = db.cursor()

# GPIO settings
channel = 3
GPIO.setmode(GPIO.BCM)
GPIO.setup(channel, GPIO.IN)

def callback(channel):
    # Capture the exact time of the event
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    # Check sensor state (adjust logic if your sensor outputs LOW on flame detection)
    if GPIO.input(channel):
        status = "FLAME_DETECTED"
        print("Flame detected! Logging to database...")
    else:
        status = "NO_FLAME"
        print("No flame detected.")
    
    try:
        # Use parameterized query to avoid SQL injection (best practice)
        sql = "INSERT INTO flame_detections (detection_time, status) VALUES (%s, %s)"
        cur.execute(sql, (current_time, status))
        db.commit()
        print("Data logged successfully!")
    except MySQLdb.Error as e:
        # Rollback on database errors to avoid partial entries
        db.rollback()
        print(f"Database error: {e}")

# Set up GPIO event detection
GPIO.add_event_detect(channel, GPIO.BOTH, bouncetime=300)
GPIO.add_event_callback(channel, callback)

# Keep the program running, handle graceful exit
try:
    while True:
        time.sleep(1)
except KeyboardInterrupt:
    GPIO.cleanup()
    cur.close()
    db.close()
    print("Program exited—resources cleaned up.")

Key Changes Explained #

• Parameterized Queries: Using %s placeholders instead of string concatenation prevents SQL injection and makes your code more robust.
• Error Handling: The try-except block catches database issues (like connection drops) and rolls back failed transactions to keep your data consistent.
• Timestamp Tracking: We use datetime.now() to record exactly when each event occurs—critical for analyzing detection patterns later.
• Graceful Cleanup: The KeyboardInterrupt handler ensures we clean up GPIO pins and close the database connection properly when you stop the program with Ctrl+C.

Quick Tips #

• Verify your sensor’s logic: Some flame sensors output HIGH when a flame is detected, others output LOW. Test GPIO.input(channel) to confirm what your sensor sends, and adjust the if statement if needed.
• If you only want to log flame detection events (not every state change), modify the callback to only run the insert when status == "FLAME_DETECTED".

内容的提问来源于stack exchange，提问作者Malik Al rushaidi