2.1. Simple watcher database

Note

If not done yet, please install groundwork-database:

pip install groundwork-database.

During the following steps we will:

  • Create a new plugin CsvWatcherDbPlugin
  • Define some config parameters, which are needed for our database
  • Register a SQLite database
  • Define and register a database model
  • Write functions to add, delete and list watchers

2.1.1. Plugin creation

At first we need to set up a new plugin. So create a folder csv_watcher_db_plugin inside the folder csv_manager/plugins. Then create two files in it: __init__.py and csv_watcher_db_plugin.py.

Finally let’s add some code to our new csv_watcher_db_plugin.py:

from click import Argument, Option

from groundwork.patterns import GwCommandsPattern
from groundwork_database.patterns import GwSqlPattern
from csv_manager.patterns import CsvWatcherPattern


class CsvWatcherDbPlugin(GwCommandsPattern, CsvWatcherPattern, GwSqlPattern):
    """
    A plugin for monitoring csv files.
    """
    def __init__(self, app, **kwargs):

        self.name = "CsvWatcherDbPlugin"
        super().__init__(app, **kwargs)
        self.db = None

    def activate(self):

        # Argument for our command, which stores the csv file path.
        path_argument = Argument(("csv_file",),
                                 required=True,
                                 type=str)

        interval_option = Option(("-i", "--interval"),
                                 type=int,
                                 default=10,
                                 help="Sets the time between two checks in seconds")

        self.commands.register("csv_watcher_list",
                               "Shows all csv watchers",
                               self.csv_watcher_list)

        self.commands.register("csv_watcher_add",
                               "Adds a permanent watcher",
                               self.csv_watcher_add,
                               params=[path_argument, interval_option])

        self.commands.register("csv_watcher_delete",
                               "Removes a permanent watcher",
                               self.csv_watcher_delete,
                               params=[path_argument])

    def deactivate(self):
        pass

As you can see, our plugin will provide three new commands to the user: csv_watcher_list, csv_watcher_add and csv_watcher_delete.

csv_watcher_list will provide a list of all stored watchers in our database. csv_watcher_add will add a new watcher to our database, so that it will still be available after an application restart. And csv_watcher_delete will delete an existing watcher from our database.

But before we can use these functions, we have to setup our database.

2.1.2. Configuration preparation

Currently groundwork-database only supports sql-based database like MySQL, Postgresql, SQLite and more.

We will use SQLite, because it does not need any installation and is part of Python’s standard library.

Let’s add the following configurations to our configuration.py file inside the csv_manager/applications folder:

WATCHER_DATABASE_NAME = "WATCHER_DB"
WATCHER_DATABASE_DESCRIPTION = "DB for CSV file watchers"
WATCHER_DATABASE_LOCATION = "%s/watcher_db.db" % APP_PATH
WATCHER_DATABASE_CONNECTION = "sqlite:///%s" % WATCHER_DATABASE_LOCATION

We use the prefix WATCHER_DATABASE here to identify easily, which configuration parameters belong to each other.

NAME and DESCRIPTION is for documentation only. LOCATION stores the file path only and becomes part of the important CONNECTION parameter, which contains the database type and the uri, which is used as address to connect to our database.

Note

Take a look into the SQLAlchemy documentation about Engine Configuration for specific information about all supported databases and their needed connection parameter.

2.1.3. Database registration

For database registration and configuration we will create a function called setup_db(), which we call in our activation() routine:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
 def activate(self):
     #  ...
     self.setup_db()


 def setup_db(self):
     self.db = self.databases.register(self.app.config.get("WATCHER_DATABASE_NAME", "csv_watcher_db"),
                                       self.app.config.get("WATCHER_DATABASE_CONNECTION", "sqlite://"),
                                       self.app.config.get("WATCHER_DATABASE_DESCRIPTION", "Stores csv watchers"))
     Base = self.db.Base

     class CsvWatchers(Base):
         __tablename__ = 'csv_watchers'

         id = Column(Integer, primary_key=True)
         csv_file = Column(String(2048), nullable=False)
         interval = Column(Integer)

     self.Watcher = self.db.classes.register(CsvWatchers)
     self.db.create_all()

groundwork supports the usage and registration of multiple databases. But we only need one, so we register it in line 7. As return value we get a database object, which we will use for all interactions with our database.

In line 10 we request the SQLAlchemy Base class. SQLAlchemy allows us to define python classes to handle our database data. But we need to make sure that these classes always inherit from this base class.

Our database table - or better database model - is defined from line 12 to 17. We use __tablename__ to define our own name for table. If we do not set this value, SQLAlchemy generates a name based on the class name.

We need and therefore create 3 columns: id, csv_file and interval.

There should be always an id column, so that each row can be clearly identified by this value. If nullable is True, the column is allowed to be empty.

Final we register this class on our database [19].

The command self.db.create_all() tells SQLAlchemy that the configuration is done and that it shall create our tables, if they do not exist yet.

2.1.4. Working with database models

It’s time to use our newly created database model and add some data.

2.1.4.1. Loading existing watchers

But before we can add watchers, we should implement a function, which loads existing watchers and starts their monitoring thread. So we add a function called load_watchers() und load it during our activate() routine:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 def activate(self):
     #  ...
     self.setup_db()
     load_watchers()

 def load_watchers(self):
     current_watchers = self.Watcher.query.all()
     for watcher in current_watchers:
         try:
             self.activate_watcher(watcher.csv_file, watcher.interval)
         except Exception:
             self.log.error("Couldn't activate watcher for %s" % watcher.csv_file)

 def activate_watcher(self, csv_file, interval):
     try:
         # Register thread
         watcher_thread = self.csv_watcher.register(csv_file, interval, "Watcher for %s" % csv_file)

         # Start thread
         if watcher_thread is not None:
             watcher_thread.run()
     except Exception as e:
         raise e
     else:
         self.log.info(("Watcher started for %s" % csv_file))

In line 7 we query for all our watchers from database. All we need for this is our database model class.

As you can see, we can use the return value as a normal python list and iterate over it [8].

And each object in this list is an instance of our database model class, so we are able to access its data like we would do with other python class [10, 12].

The activate_watcher() function activates a single watcher. It is not part of the load_watchers() routine, because we will reuse it later inside our watcher creation function.

2.1.4.2. Show watchers

Let’s add the command function to print a list of currently existing watchers in our database:

def csv_watcher_list(self):
    watchers = self.Watcher.query.all()
    for watcher in watchers:
        self.log.info("file: %s - interval: %s" % (watcher.csv_file, watcher.interval))

2.1.4.3. Adding watchers

And now the interesting part, we create a new watcher:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 def csv_watcher_add(self, csv_file, interval):
     watcher = self.Watcher.query.filter_by(csv_file=csv_file).first()
     if watcher is not None:
         self.log.error("csv file %s already exists in database." % watcher.csv_file)
     else:
         try:
             watcher = self.Watcher(csv_file=csv_file, interval=interval)
             self.db.add(watcher)
         except Exception:
             self.log.error("Couldn't create csv_file %s in database" % csv_file)
         else:
             try:
                 self.activate_watcher(csv_file, interval)
             except Exception:
                 self.db.rollback()
             else:
                 self.db.commit()

Before we can create a new watcher, we must be sure that it has not been already added to our database [2-4].

To create a new row in our database table, we need to:

  1. Create an instance of our database model and set its values [7]
  2. Add this instance to our database [8]
  3. And final commit the change to our database [17]

In line 13 we also try to activate our watcher and start the monitoring.

If something goes wrong during creation or activation of our watcher, we are able to rollback all changes [15].

2.1.4.4. Deleting watchers

If we can add data, we should also be able to delete data. So lets add the delete function:

def csv_watcher_delete(self, csv_file):
    self.Watcher.query.filter_by(csv_file=csv_file).delete()
    self.db.commit()
    self.log.info("Watcher for %s removed" % csv_file)

That’s it, watchers can now be created and deleted via command line command. And our application does not loose them, if it gets restarted.

In the next chapter Complex history database we will use several database models, which have relations to each other.