Turning the tables
April 13, 2019 at 11:13 AM by Dr. Drang
The current episode of Rosemary and David’s Automators podcast has me as a guest. You should go listen, especially if you want to hear me bitch about Shortcuts as a programming environment.1
One topic that came up briefly was Airtable, the cloud-hosted database with an exceptionally well-documented API. As I wrote about back in the fall, I’ve been using Airtable as, among other things, a handy means of having basic information on all of my work projects available to me on all of my computing devices. The Airtable database was converted from an SQLite table that I’d been using. As I said in the post, I was looking for something a little friendlier than SQLite:
As I found myself wanting to search the database on my phone and iPad, I moved the [SQLite] database file to iCloud Drive and wrote some scripts in Pythonista to do the searching. This worked, but it was a little clumsy and the results, while correct, were ugly. I didn’t really want to put in the effort to make the output prettier, but I was resigning myself to get down to it when Airtable appeared in my Twitter feed.
Aesthetics aside, Airtable allowed easier ad hoc searching than SQLite, mainly because SQLite is typically accessed either programmatically or though SQL queries, neither of which are easy to construct on the fly.
But after a few months of using Airtable, I came to realize that the great majority of my searches could be handled in one of two ways:
- Show me the ten most recent projects. It’s when I first start working on a new project that I tend to forget the project’s name and number and need to consult the database. A short list of the most recent projects—not even a real search—is the quickest way to get the info I need.
Find the projects with a particular search term used in the project name, project number, client name, or client company. Usually I can remember at least one of these things but have forgotten one of the others. An SQL query of one particular pattern,
select name, number, client, email from projects where client like <term> or name like <term> or email like <term> or number like <term>will do the trick.
In other words, most of time Airtable’s flexibility is of no use to me. And I was finding that the time it took to launch Airtable, select the projects database, and (sometimes) re-sort the fields was not a fair price to pay for flexibility I seldom used.
Fortunately, I had never given up the SQLite database. All of the new projects opened since I started using Airtable had been entered in both Airtable and SQLite.2 So all I had to do was pull out an old Python script for querying the SQLite database and tweak it to handle the two common situations.
As important as the scripting, though, was how to run the scripts on iOS. My first thought was to do it through Shortcuts, because that’s become the hot new thing, but David Barnard’s appearance on Automators reminded me of how fast Launch Center Pro is at running Pythonista scripts, especially since I could access them directly from the home screen by enabling QuickActions.3
The Pythonista script that returns the ten most recently added projects is this:
python:
1: import sqlite3
2: import console
3:
4: # Initialize projects database
5: pl = 'projects.db'
6: db = sqlite3.connect(pl)
7: query = 'select name, number, client, email from projects'
8:
9: # How many recent projects to return
10: count = 10
11:
12: # Perform the query and print the results
13: results = db.execute(query).fetchall()
14: if len(results) == 0:
15: answer = "Not found"
16: else:
17: lines = []
18: for r in reversed(results[-count:]):
19: # Older entries have empty client and/or email fields.
20: if r[3]:
21: lines.append('{} ({})\n{}\n{}'.format(*r))
22: elif r[2]:
23: lines.append('{} ({})\n{}'.format(*r[:3]))
24: else:
25: lines.append('{} ({})'.format(*r[:2]))
26: answer = '\n\n'.join(lines)
27:
28: console.clear()
29: console.set_font('Helvetica', 20)
30: print(answer)
31: console.set_font()
The projects.db SQLite file is saved in the same directory as the script, so the connection to the database in Line 6 is simple. So is the query in Line 7, as it is not doing any actual searching, it’s just returning certain fields from all the records in the database.
Line 13 executes the query, and if nothing went wrong, Lines 18–26 assemble the answer in a reasonably readable format. The key is Line 18, which reverses the results list (putting the most recently added projects at the top) and truncates it to ten items. Lines 19-25 adjust the output according to the information available. If all four fields—project name, project number, client name, and client email—are available, each item will be arranged like this in Pythonista’s console,
Kernighan Project (9999)
Dennis Ritchie
dmr@unix.org
and there will be blank lines between projects.
Lines 28–31 handle the formatting of the console and the printing. I set the font to 20-point Helvetica in Line 29 to make the output a bit easier to read. Sending an empty argument to the set_font function resets the console to the default font in Line 31.
The name of the script is recentProjects.py and it’s saved in iCloud Drive. In Launch Center Pro, the Recent action runs this script:
The URL is
pythonista3://recentProjects.py?root=icloud&action=run
The script that does actual searching of the database is called searchProjects.py and looks like this:
python:
1: import sqlite3
2: import console
3:
4: # Initialize projects database
5: pl = 'projects.db'
6: db = sqlite3.connect(pl)
7: query = 'select name, number, client, email from projects where client like ? or name like ? or email like ? or number like ?'
8:
9: # Get the search term
10: input = console.input_alert('Project Search Term').strip()
11: arg = '%' + input + '%'
12:
13: # Perform the query and print the results
14: results = db.execute(query, (arg, arg, arg, arg)).fetchall()
15: if len(results) == 0:
16: answer = "Not found"
17: else:
18: lines = []
19: for r in reversed(results):
20: # Older entries have empty client and/or email fields.
21: if r[3]:
22: lines.append('{} ({})\n{}\n{}'.format(*r))
23: elif r[2]:
24: lines.append('{} ({})\n{}'.format(*r[:3]))
25: else:
26: lines.append('{} ({})'.format(*r[:2]))
27: answer = '\n\n'.join(lines)
28:
29: console.clear()
30: console.set_font('Helvetica', 20)
31: print(answer)
32: console.set_font()
The difference between it and recentProjects.py is the query in Line 7 and that it gets the search term from the user via Lines 10. The dialog box that pops up to collect the search term is this:
Line 11 takes the search term and surrounds it with percentage signs. This, along with the question marks in Line 7, is the query syntax required by the sqlite3 module.
The rest of the script is the same as before, and the LCP Search action looks just like the Recent action except the URL is changed to
pythonista3://searchProjects.py?root=icloud&action=run
So now I have the best of both worlds: a very fast system via SQLite, Pythonista, and Launch Center Pro for doing what I do most often; and a more user-friendly system via Airtable for doing more unusual searches.
-
Although we had talked about HyperCard earlier in the program, I didn’t think to mention how much better programming in HyperCard was in the late 80s than programming in Shortcuts is in the late teens. A missed opportunity for a stereotypical cranky old man rant. ↩
-
I didn’t want my database lost if Airtable was bought out and “sunsetted.” ↩
-
The Scanner Pro quick action is what I use to grab expense receipts when I’m traveling. ↩
