Help Needed: Using Keyboard Maestro with SQLite

Questions & Suggestions
,
1

Howdy folks, I recently dived into the world of SQLite and am trying to figure out if it's possible to run command lines via Keyboard Maestro. I have searched multiple times on the forum but have not found much about SQLite unfortunately.

Basically what I would like to do is keep a customer database and update it via Keyboard Maestro. As of right now I use dictionaries to accomplish this, and while it works fine, due to the fact that I have multiple pieces of data stored in each key's value, I have to use RegEx to extract each piece of data when I want to update it as well as to concatenate it when I want to save it back to the dictionary.

I suspect this might be "easier" to do using SQLite, and even if it wasn't, I just want to learn it and see if it's integrable with Keyboard Maestro. Any help is appreciated!

-Chris

1 Like
2

If you want to run a single line of SQL, you can pass it as the second argument to the sqlite3 command:

sqlite3 customer.db 'select * from tbl1 where id=25'

See Section 21 of the SQLite command line documentation.

If you have more complex SQL commands to run, you can write an SQL script and pass it in using shell redirection:

sqlite3 customer.db < script.sql

There are other ways to do this.

You can do either of these in Keyboard Maestro within an Execute a Shell Script step.

3 Likes
3

Just to give you something to chew on, I use this code to run Web apps I've written in PHP that manage MySQL databases. PHP is free (although no longer part of the standard macOS install). MySQL is free. And they make a really nice package for managing (data entry, editing, reporting, maintenance) databases.

So you can go a long way with nothing more than the Custom HTML Prompt wrapped around some CSS, HTML, PHP and MySQL.

4 Likes
4

Not sure if you saw this:

Simple querying of CSV with sqlite3 - Macro Library - Keyboard Maestro Discourse

2 Likes
5

This has given me a good start, thank you.

I still need to look at this because ultimately my case use would be extracting all values from a table's entry, using a KM prompt to update one or more of those values, and then updating them in the database, which obviously would be a little more involved than just a couple of lines.

@mrpasini for the moment I am trying to keep this as simple as possible, so I'd rather avoid having to deal with HTML and CSS (even though I have built custom prompts in the past), since that would likely defeat the purpose of trying to do this with sql.

@ComplexPoint Thanks for the link, after looking at it several times I'm not sure if it's applicable to my case or not... but I likely just don't understand everything yet. So i'll keep reading it and tinkering in the meantime.

1 Like
6

If you’re going to do Keyboard Maestro stuff in the middle, I think you’ll have to use two sets of SQL commands: one that does the extraction and then, after your KM prompt stuff, another one to do the update. But each of these sets could be just a few lines. And after you get past one line, it doesn’t really matter how long it is.

To help get the extracted data into KM, look into SQLite’s JSON output setting.

1 Like
7

Yes that's pretty much how I'm setting it up (with some additional things like verifying that a table exists, verifying that an entry exists etc). I built a temporary macro to transfer all my data from about 20 KM dictionaries into my database and another to extract, display and update entries in said database and it's working VERY well. Your example of putting the argument in single quotation marks after the initial command was what I was missing. Il'l be tweaking it over the course of the next few weeks no doubt (due to just how many things I want to do with it) but that got me going in the right direction. Thank you!

1 Like