I'm looking for advice on the best way to build a simple database out of information generated every day from about half a dozen iOS and MacOS apps, mainly about time use. I want to generate a dashboard and trigger some actions based on the data.
I know AppleScript and Keyboard Maestro, and I can use them to do almost everything I want. I can collect the data as I need as CSV or JSON files and load it into lists of records (with dictionaries). As far as I can tell neither Applescript nor KM make it easy to query those records without a lot of repeat loops.
Essentially all I want is a statement like this:
Set DashboardValue to [sum, average, min, max, or count]
of Field A for records where Field B = X and Date is between Y and Z
Is there an easy way to do that in AppleScript or KM? Or is there a tool I can learn just enough of to solve my problem:
SQL
A scripting language (JavaScript, Python, etc.)
A webapp (Airtable, FireBase, etc.)
A spreadsheet (Numbers or Excel) or other Mac app.
Other than spreadsheets, I don't have any experience with these other languages or platforms. All of these could solve my problem. Does anyone have a view as to what the most painless path to take?
Since you already know how to use a spreadsheet, it would be simplest just to use one. There is a reason why spreadsheets are so frequently used as "databases" when the data is basically a "flat file". They are simple and many people already know how to use them.
Problems occur when the number of "records" or "rows" gets to be too big. How much are you generating in one day? The second problem is that queries can become hard to do unless they are simple queries.
I think these two problems are the most common reason why people are forced out of the spreadsheet solution. I would probably next move to SQLite. But if a spreadsheet "works" for your needs, I would just use one.
Robert- Thanks for your reply. Spreadsheets can do the calculations I want. Essentially I am asking about the AppleScript/KM equivilent of a SumIF/SumIFS function.
What I need is to find an easy way to set up a script that multiple times a day can append rows to a spreadsheet then get the results from cells that tally the results I want to use in AppleScripts and KM Macros. I'm indifferent between Numbers, Excel and Google Sheets.
Do you have a sense of which would do what I needed best?
If I wanted to use SQLite, how do I get that set up on the Mac? What program do i run? How do I automatically add data and then query the results?
Martin- Thanks for your help. I'm up to learn a bit of SQL and Python, but I'm a little flummoxed about how actually to get set up
on a mac.
Before I ran the macro you pointed me to, what do I need to download? And what other things do I need to do on the mac to make it run?
Martin- I'm sorry to have to ask basic questions. Your instructions seem to leave out some of the baby steps to get going.
Are Python3 and MySQL already on the default install of the Mac (without XCode)? If not, what do I need to download from where?
I did run "pip3 install PyMySQL" in terminal. I got an error "ERROR: Could not install packages due to an EnvironmentError: [Errno 13] Permission denied: '/Library/Python/3.8' "
Where did I set up the " Name, User, Password, and the command" I am supposed to put into the KM Macro?
When I ran your Macro to see what would happen, it asked me if I wanted to download Install Command Line Developer Tools. I did that. Do I need to download the full XCode?
Thanks
Saul
, and the PyMySQL module to interact with.
I have not needed to do development of this sort before. Are Python3 and
I'm no expert at all. I don't think Xcode is needed.
You may have Python 3.8.
Check where your python is installed. Run which python or which python3 (for python3) in Terminal.
Also, check where pip is installed. Run 'which piporwhich pip3` (for python3) in Terminal.
I have set alias python and pip as python3 and pip3 respectively. If which python and which pip are different from which python3 and which pip3, then you need to use python3 and pip3 for version 3. If you don't have them installed, you need to install them first.
I'm indifferent between Numbers, Excel and Google Sheets.
Do you have a sense of which would do what I needed best?
I think they would all be fine.
If I wanted to use SQLite, how do I get that set up on the Mac? What program do i run? How do I automatically add data and then query the results?
SetApp comes with SQLPro for SQLite which I have used and seems fine. DB Browser for SQLite and TablePlus are other free (at least at the start) applications. I do not use SQLite enough to be an expert at all. I only use it under duress because I have not invested the time to be comfortable with it. I have extensively used 4D which is an expensive database product but from this contact, I became familiar with database concepts and language. The problem is while the resources for SQLite exist at low or no cost, becoming fluent takes a lot of time.
Similarly Python. Macs used to come with version 2.7 but you do not want to use that. If you want to jump into Python, version 3.9 from python.org would make sense. Again, the problem is that while Python is free, there needs to be a big investment of your time to learn it. Even just managing its software on your Mac takes time and can be confusing.
There are plenty of reasons to learn Python and SQLite. Both are hugely powerful. But since it will take months to really grok these things, I would not want to have to learn them to solve a small problem. Their great potential is that they can solve a whole slew of problems. Python in particular is fun to learn but it is a big project.
@rlivingston Can we use AppleScript to get the data from, say, Numbers or Excels, without opening the apps?
I thought the OP was asking for that.
Python has a module to get data from Excel without open it.
Thanks for recommending SQLite. I have not tried it before. After your recommendation, I tried it out. It's great. @shansell If your data is not sensitive, SQLite might be a very good option, since you seem to have already know the commands for database.
Comparing with MySQL,
SQLite deals with a single .db file. It's much easier to manage.
SQLite does not need username and password. Everyone with the file can read it. This could be an advantage if your data is not sensitive.
The grammar is very similar to MySQL. So there is not much new to learn if you already know MySQL.
SQLite does not need a server to run. MySQL needs the server to run.
Python comes with sqlite module. So it's ready to use.
Here is an example:
#!/usr/bin/python3
import sqlite3
from os import path
home = path.expanduser('~')
file = path.join(home, 'Documents/sqlite/test.db')
conn = sqlite3.connect(file)
c = conn.cursor()
cursor = c.execute("SELECT * from COMPANY")
for row in cursor:
print(' '.join(map(str, row)))
conn.close()
I have a test.db file saved to the ~/Documents/sqlite/ folder.
It has a table named COMPANY.
It contains data:
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
The above python code will print the data.
I've attached the test.db file here. You can put it in the same folder (or change the file directory if you put it elsewhere) and test out the code.
Thanks for your reply. I've learned a lot from your many contributions to this forum.
Why do you suggest Filemaker Pro instead of the other response to the post at Latre Night that suggested Shane's SQL libraries. I used to use Filemaker then Apple started to raise the price beyond what it was worth to me.
Since Shane's libraries are free and SQLite is already on the Mac, I am thinking that this might be the best way to go. Thoughts anyone?
My suggestion was mainly based on ease-of-use and AppleScript support.
If you don't want to pay the price of FMP, then Shane's SQL lib provides an excellent option.
I'm not familiar with this. But I think it depends on how it is installed.
Usually, it should be in the /site-packages/ folder in the python directory.