A Simple and Efficient SQLite-based Data Monitoring and Alert Email System (2023 Email Actually Works!))

This project is a simple solution for monitoring sensor data and receiving email alerts when user defined thresholds are exceeded. It is built on top of SQLite and includes a Python script that queries the database at specified intervals. The script checks for readings that fall above and below the set thresholds and then sends an email alert.

PC

IDE - I use pycharm

1. Open your IDE and ensure that the SQLite library is installed.
2. Create a new python script
3. Copy the "Create Data" code from below into your script and run it.
4. The first time you run the code, it will create a database called "sensor_database.db" and a table named "sensor_data".
5. Every time you run this script, it will create more data.

If you change the name of the database or the table, you will need to make the same change to all the relevant code, such as the code for testing the database and activating email alerts.

Create Data-

import sqlite3
from random import randint
import datetime

# Connect to the database and create the sensor_data table if it doesn't exist
conn = sqlite3.connect('sensor_database.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS sensor_data (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    sensor_id INTEGER,
                    value INTEGER,
                    alert_sent BOOLEAN,
                    alert_sent_datetime TEXT,
                    record_created_datetime TEXT
                )''')

# Generate a random sensor value and sensor ID
sensor_value = randint(0, 100)
sensor_id = randint(1, 3)

# Get the current date and time
min_datetime = datetime.datetime.min
current_datetime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

# Insert the record into the database
query = f"INSERT INTO sensor_data (sensor_id, value, alert_sent,  alert_sent_datetime, record_created_datetime) VALUES ({sensor_id}, {sensor_value}, FALSE, '{min_datetime}', '{current_datetime}')"
cursor.execute(query)
conn.commit()
print("Date written to database")

# Close the database connection
conn.close()

Now that you have an SQLite database and a table with data we are going to test it.

1. Create a new python script
2. Copy the "Read Test Data" code from below into your new script and run it.
3. This will read all of the data that is in your database and print it to the IDE console

Read Test Data -

import sqlite3

# Connect to the database
conn = sqlite3.connect('sensor_database.db')
cursor = conn.cursor()

# Query the database to retrieve all data and column names from the sensor_data table
query = "PRAGMA table_info(sensor_data)"
cursor.execute(query)
column_names = [row[1] for row in cursor.fetchall()]

query = "SELECT * FROM sensor_data"
result_set = cursor.execute(query)

# Print the column names
print(column_names)

# Print all the data
for row in result_set:
    print(row)

# Close the database connection
conn.close()

• Create a Gmail account if you don't already have one
• Set up your Gmail account to allow "App passwords" (https://support.google.com/accounts/answer/185833?hl=en)
• This guy has a great tutorial: https://www.youtube.com/watch?v=g_j6ILT-X0k&list=PLEucz4PLicEJAWpBNEeTKPk41RaN_s5E3&index=18

Once you have setup your gmail account for app passwords

1. Create a new python script
2. Copy the "Install Certificates" code from below into your new script and run it.
3. This will install the the necessary SSL certificates

Install Certificates -

# install_certifi.py
#
# sample script to install or update a set of default Root Certificates
# for the ssl module.  Uses the certificates provided by the certifi package:
#       https://pypi.python.org/pypi/certifi

import os
import os.path
import ssl
import stat
import subprocess
import sys

STAT_0o775 = ( stat.S_IRUSR | stat.S_IWUSR | stat.S_IXUSR
             | stat.S_IRGRP | stat.S_IWGRP | stat.S_IXGRP
             | stat.S_IROTH |                stat.S_IXOTH )


def main():
    openssl_dir, openssl_cafile = os.path.split(
        ssl.get_default_verify_paths().openssl_cafile)

    print(" -- pip install --upgrade certifi")
    subprocess.check_call([sys.executable,
        "-E", "-s", "-m", "pip", "install", "--upgrade", "certifi"])

    import certifi

    # change working directory to the default SSL directory
    os.chdir(openssl_dir)
    relpath_to_certifi_cafile = os.path.relpath(certifi.where())
    print(" -- removing any existing file or link")
    try:
        os.remove(openssl_cafile)
    except FileNotFoundError:
        pass
    print(" -- creating symlink to certifi certificate bundle")
    os.symlink(relpath_to_certifi_cafile, openssl_cafile)
    print(" -- setting permissions")
    os.chmod(openssl_cafile, STAT_0o775)
    print(" -- update complete")

if __name__ == '__main__':
    main()

Not that we have database with data it and an email address that will send emails we will run the database monitoring and alert script.

1. Make sure that you have the smtplib library is installed.
2. Create a new python script
3. Copy the "Database Monitoring And Alert" code from below into your script.
4. Make sure that you change these details in the code
5. email_sender = 'youremail@gmail.com'
6. email_password = 'xxxxxxxxxxxxxx'
7. email_receiver = 'receiver@gmail.com'
8. Run the script.
9. You can the the following values
10. bounds (This is the lower and upper trigger for each sensor)
11. alert_interval (This changes how often you will get an alert)

Database Monitoring And Alert -

import smtplib
import ssl
from email.message import EmailMessage
import sqlite3
import datetime
import time

# Connect to the database
conn = sqlite3.connect('sensor_database.db')
cursor = conn.cursor()

# Define the bounds for the sensor value
bounds = {
    1: (32, 68), # sensor_id/lower bound/upper bound
    2: (25, 72),
    3: (30, 75)
}

# Define the alert interval in minutes
alert_interval = 10 # Set this to 0 if you want to be alerted everytime the bounds are exceeded

# Define the email settings
email_sender = 'youremail@gmail.com'
email_password = 'xxxxxxxxxxxxxx' # less secure apps password
email_receiver = 'receiver@gmail.com'


while True:
    # Query the database for all sensor ids
    query = "SELECT DISTINCT sensor_id FROM sensor_data"
    cursor.execute(query)
    sensor_ids = cursor.fetchall()

    # Loop through each sensor id
    for sensor_id in sensor_ids:
        sensor_id = sensor_id[0]
        print("Sensor ID: ", sensor_id)

        # Query the database for the latest record for this sensor id
        query = "SELECT * FROM sensor_data WHERE sensor_id = ? ORDER BY id DESC LIMIT 1"
        cursor.execute(query, (sensor_id,))
        current_record = cursor.fetchone()
        current_value = current_record[2]
        current_alert_sent = current_record[3]
        current_alert_sent_datetime = current_record[4]

        # Query the database for the previous record for this sensor id
        query = "SELECT * FROM sensor_data WHERE sensor_id = ? ORDER BY id DESC LIMIT 1 OFFSET 1"
        cursor.execute(query, (sensor_id,))
        previous_record = cursor.fetchone()
        if previous_record:
            previous_value = previous_record[2]
        else:
            previous_value = None

        # Query the database for the previous alert sent record for this sensor id
        query = "SELECT * FROM sensor_data WHERE sensor_id = ? and alert_sent = 1 ORDER BY id DESC LIMIT 1"
        cursor.execute(query, (sensor_id,))
        alert_record = cursor.fetchone()
        if alert_record:
            previous_id = alert_record[0]
            previous_alert_sent_datetime = alert_record[4]
        else:
            previous_id = None
            previous_alert_sent_datetime = None
        print("Previous alert time: ", previous_id, previous_alert_sent_datetime)

        # Check if a previous value exists. We need more than two records for each sensor value or it will pass untill more than two records exist
        if previous_value != None:
            # Check if the current value is out of bounds
            if current_value < bounds[sensor_id][0] or current_value > bounds[sensor_id][1]:
                # Check if the previous value was within bounds
                if previous_value >= bounds[sensor_id][0] and previous_value <= bounds[sensor_id][1]:
                    print("Sensor reading is out of bounds")
                    if not current_alert_sent:
                        # Check if enough time has elapsed since the last alert was sent
                        if not previous_alert_sent_datetime or (datetime.datetime.now() - datetime.datetime.strptime(previous_alert_sent_datetime, '%Y-%m-%d %H:%M:%S')).total_seconds() > alert_interval* 60:                    # Send the alert
                            print("Sending email alert sent")

                            # Set the subject and body of the email
                            subject = f'Sensor {sensor_id} Alert!'
                            body = f'The value of sensor {sensor_id} is: ({current_value}).'

                            em = EmailMessage()
                            em['From'] = email_sender
                            em['To'] = email_receiver
                            em['Subject'] = subject
                            em.set_content(body)

                            # Add SSL (layer of security)
                            context = ssl.create_default_context()

                            # Log in and send the email
                            with smtplib.SMTP_SSL('smtp.gmail.com', 465, context=context) as smtp:
                                smtp.login(email_sender, email_password)
                                smtp.sendmail(email_sender, email_receiver, em.as_string())

                            print("Email alert sent")

                            # Update the alert_sent flag and alert_sent_datetime in the database
                            current_datetime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                            query = "UPDATE sensor_data SET alert_sent = 1, alert_sent_datetime = ? WHERE id = ?"
                            cursor.execute(query, (current_datetime, current_record[0]))
                            conn.commit()

                        else:
                            print("Alert NOT in time interval")
                    else:
                        print("An alert has already been sent")
                else:
                    print("Sensor reading is still out of bounds")
            elif previous_value < bounds[sensor_id][0] or previous_value > bounds[sensor_id][1]:
                print("Sensor reading is back in bounds")
            else:
                print("Sensor reading is still in bounds")
        else:
            print("No previous value to compare")

    print("Starting sleep for 5 seconds")
    time.sleep(5)
    print("Sleep complete")

# Close the database connection
conn.close()

Give yourself a pat on the back, you made it!

Check out my other related Instructables! They build on each other and cover a range of topics, from reading sensor data to sending alerts via email and text message. Some of my projects include:

Reading sensor data with an Arduino

Sending Arduino data to an SQLite database

Sending alert emails based on sensor readings

Displaying sensor data on a desktop application

Take a look and see what inspires you!

That's it! Happy tinkering!