Processing your offline gmail in Python
3 February 2009
In a discussion in my local Linux group, I was encouraged to try out a new feature of Google's webmail ('Gmail') that allows you to read your email offline.
Inside Gmail, you can click on Settings then Labs then Enable Offline.
This will then prompt you to install a Firefox extension called Google Gears. The one provided this way is only for 32 bit platforms, for 64 bit Linux, I found a third-party 64-bit compiled version in the blog of Niels Peen that works on my 64 Linux laptop.
I had a nosy in my .mozilla directory and found that Google stores your email in a SQLite database. So I thought I would have a play with it in Python.
To follow along at home, set up the Offline Gmail, then type the following commands into the Python shell, which you can open with python (or if you are lucky) ipython.
To start with, you need to set the filepath of the database, have a little ls around and then swap the two instances of something below with whatever it is on your system.
PATH = ".mozilla/firefox/something.default/Google Gears for Firefox/" + \
"mail.google.com/http_80/something@gmail.com-GoogleMail#database"
Now we can connect to the database:
import sqlite3
conn = sqlite3.connect(PATH)
Lets start by pulling out all the available tables:
tables = conn.execute('SELECT name FROM sqlite_master WHERE type = "table"')
for table in tables:
print table
Now lets read the first message in the first thread:
messages = conn.execute('SELECT * FROM messagesFT_content')
mymessage = messages.next()
for line in mymessage:
print line
Now lets make a dictionary of all your contacts
contacts = {}
for contact in conn.execute('SELECT * FROM Contacts'):
contacts[contact[2]] = contact[3]
print contacts
Now lets make a dictionary of your attachments, sorted by filetype:
attachments = {}
for attachment in conn.execute('SELECT * FROM Attachments'):
if attachments.has_key(attachment[5]):
attachments[attachment[5]].append(attachment[3])
else:
attachments[attachment[5]] = [attachment[3],]
print attachments
There are lots of useful possibilities. Of course there are other easier ways to access your Gmail in Python, but this approach is useful when you might need to do something offline, e.g. on a train or plane.
If you come up with any interesting uses or code with this, please do leave a comment letting everyone know about it.
1 MG says...
i wonder... if you insert something to the database with the google gears backend update this to the main account when it gets a connection?
if so, this would be quite useful for making changes to your google account without having to make sure that you are online... hijacking the offline ability! the only downside is that the changes would only get refreshed the next time firefox is online (with the gears plugin enabled)
Posted at 12:18 a.m. on February 4, 2009
2 Jerry says...
Wow...I forsee searching gmail messages with Quicksilver/Spotlite !
Posted at 4:54 a.m. on February 5, 2009
3 Aaron says...
Hey, I just wanted to say thank you for point all this out. Now I can Python my way out of this scary Web 2.0 thing and converting to a more private e-mail server. I also have to say, in all sarcasm, thanks a lot for giving me one more diversion this semester! Seriously, though, this is going to be fun...
Posted at 12:19 a.m. on February 6, 2009
4 james says...
note that the "sqlite3" command can open database files, providing a CLI interface supporting stdin
Posted at 8:07 p.m. on April 25, 2009