My idea is to be able to manage my mail through thunderbird but also have the ability to hook in with python (and SQL is the way)
Do you have a write up or some examples anywhere (I'm trying to learn Godot at the moment so any shortcuts would be so useful!) ?
In Thunderbird, go to Help -> Troubleshooting Information -> Profile Directory.
That opens your profile directory. In there is a file global-messages-db.sqlite that holds all your messages. Treat that as read-only. If it does get messed up for some reason, you can delete it. It's an index that will get rebuilt.
If you close Thunderbird, you can open the db in sqlitebrowser to view the tables. Some tables I've used for queries:
folderLocations - tells you the id of each email folder
messages - metadata for each message
messagesText_content - subject, recipients, body of each email message
conversations - info about your email threads that can be matched with data in messages to retrieve full email threads
For personal email though I wanted this to work regardless of the client - got Thunderbird on my personal laptop, a webmail on my work computer and k9 on my phone ; so just client side wasn't enough
Are you talking about "global-messages-db.sqlite" ? From cursory googling, that's only an index.[1] The default storage for Thunderbird is mbox format not SQLite.
When I experiment with some SQL queries on "global-messages-db.sqlite", I notice that many of my gmail emails are not in that index cache.
[1] http://forums.mozillazine.org/viewtopic.php?f=39&t=2861483*
Purely because I'm curious, as a user who might end up doing this: are those messages perhaps not synced locally? And/or if you delete the cache and rebuild, do they appear? Or are there just some kinds of messages that intentionally don't appear in that index?
A few things I've done: use it to ignore my email except for a small number of senders while I'm working, viewing only starred messages the majority of the day, and copying email threads as html documents inside a project folder. There's something nice about being able to look at an "email inbox" that's a plain html page with six messages.