Connecting IFTTT to a MySQL database

Connecting IFTTT to a MySQL database

I’ve just finish developing a nifty little tool called yaml2db that allows you to dump the contents of a yaml file, or an entire directory of yaml files, into a database. What’s so nifty about that I here you ask? Well … IFTTT! With the IFTTT service you have quick and super easy access to the APIs of dozens of your favourite web-services. You can use any of the IFTTT channels as an input for an applet that then writes a yaml file with the data you wish to capture to a directory in your Dropbox account. Pointing yaml2db at this Dropbox directory in your filesystem then sucks all this lovely data into a MySQL database. It’s up to you then what you want to do with the data, the point is you now have a simple, homogenised way of pouring content from all those APIs into a local database. nIFTTTy right? (sorry couldn’t resist).

You could grab the URLs for videos you ‘like’ on youtube or add to your watch-later list on vimeo. Or how about your sleep log from your fitbit, or a new contact added to your iOS device.

How about this applet that logs URLs moved into my instapaper read-work-projects folder:

To install yaml2db simply install the fundamentals python package. The easiest way to install this is via pip:

pip install fundamentals

Or you can clone the github repo and install from a local version of the code:

git clone git@github.com:thespacedoctor/fundamentals.git
cd fundamentals
python setup.py install

So how does it work? yaml2db will take either a path to a single yaml file or to an entire directory of yaml files, parse the contents of those files and add the key-values to a mysql database. Here’s the usage statement (printed by typing yaml2db -h from a terminal window):

Usage:
    yaml2db [-d] -s <pathToSettingsFile> <pathToYaml>
    yaml2db [-d] --host=<host> --user=<user> --passwd=<passwd> --dbName=<dbName> <pathToYaml>

Options:

    pathToYaml            path to a single yaml file or directory of yaml files
    pathToSettingsFile    path to a settings file with logging and database information (yaml file)
    --host=<host>         the database host
    --user=<user>         database user
    --passwd=<passwd>     database user password
    --dbName=<dbName>     name of the database to add the table content to

    -d, --delete          delete yaml file(s) once added to datbase
    -h, --help            show this help message
    -s, --settings        path to a settings file with logging and database parameters

The yaml files require a table value to indicate the name of the database table (or tables, comma-separated) to add the yaml key-values to. If the database tables do not exist, yaml2db will create them for you. Here’s an example of the content of a yaml file:

title: Why you should do most of your text editing in : Sublime Text | Sublime Text Tips
url: http://sublimetexttips.com/why-you-should-do-most-of-your-text-editing-in-sublime-text/?utm_source=drip&utm_medium=email&utm_campaign=editor-proliferation
kind: webpage
subtype: article
table: web_articles,podcasts 

yaml2db will take the content of this file and add it to both a web_articles and podcasts table.

To execute yaml2db on a single yaml file run:

yaml2db -d --host=localhost --user=username --passwd=mypassword --dbName=myGreatDatabase /Users/Me/yaml/20161219105124.yaml 

Obviously replace the database parameters with your own. The -d flag indicates that you want to delete the yaml file once it’s been parsed.

To run yaml2db on an entire directory of yaml files run:

yaml2db -s /Users/Me/mydefault_settings.yaml /Users/Me/yaml/

where /Users/Me/mydefault_settings.yaml contains your database and logging parameters (see the fundamentals tutorial if you require more details).