Database Listeners

I'm starting to work on a system of checks to ensure the database is up to date. The way I see it, the checks will run on a fairly regular cron and generate 'issues' where Data doesn't line up.

Users will also have a way of generating 'issues' so there's a human element to checking the data integrity as well.

As it stands though, that would mean that I have to check a database table regularly. Surely it would be better to get an email or something every time there was a new entry in the database.

And as it turns out there's a pretty easy way to do that using SQLAlchemy and Flask.

Here's a truncated version of my models.py file:

from yvih import db  
from sqlalchemy import event  
from sqlalchemy.orm import mapper  
import smtplib

class Data(db.Model):  
    __tablename__ = 'data'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    email = db.Column(db.String)
    issue = db.Column(db.Text)
    member_id = db.Column(db.Integer, db.ForeignKey('members.id'))
    status = db.Column(db.Integer)

    def __init__(self, name, email, issue, member_id, status = 0):
        self.name = name
        self.email = email
        self.issue = issue
        self.member_id = member_id
        self.status = status

def data_listener(mapper, connection, target):  
    ''' send email to alert that new issue has been raised '''
    message = """From: From Person
    To: To Person
    Subject: A new issue has been generated

    %s
    """
    message = message % (target.issue)
    # @todo: send email from here

event.listen(Data, 'after_insert', data_listener)  

The first thing to note is the includes. 'Events' need to be includes from 'sqlalchemy' and 'mapper' from 'sqlalchemy.orm'.

Essentially events listen to mapped classes and execute a function when an event occurs on that class.

So in this case, I've set up a listener on the 'Data' class and am listening for the after_insert event which then executes the data_listener function.

The event listener will pass three arguments to the listening function so make sure there are three variables passed to the function. The third argument is the data that is being saved so in the case, I can use it to email the issue.