mailfu.py

# coding: utf-8

"""
tbl_name messages
sql CREATE TABLE messages (date text, sender text, subject text, headers text,
        tags text)

tbl_name parts
sql CREATE TABLE parts
(
        msgid integer, 
        type text,
        payload blob,
        foreign key(msgid) references messages(rowid)
        )

"""

import sqlite3
import mailbox 

try:
    db, mb
except:
    db = sqlite3.Connection('gmail.sqlite')
    mb = mailbox.mbox('tbird/dqlx6jt6.default/Mail/Local Folders/gmail')

def make_tables(db):
    db.execute("""create table messages (date text, sender text, subject text,
    headers text, tags text);""")
    db.execute("""create table parts (msgid integer, type text, payload blob,
    foreign key (msgid) references messages (rowid));""")


def msg2sql(msg):
    headers = str(dict(msg.items()))
    return (msg.get('Date', ''), 
            msg.get('From', '').decode('utf8', 'ignore'), 
            msg.get('Subject', '').decode('utf8', 'ignore'), 
            headers, 
            '')

def msgparts(msg, id=0):
    for part in msg.walk():
        if part.is_multipart():
            continue
        print part.get_content_type()
        yield (id,
               part.get_content_type(),
               part.get_payload().decode('utf8', 'ignore'))

def msg_into(c, msg):
    q = 'insert into messages values (?, ?, ?, ?, ?)'
    c.execute(q, msg2sql(msg))
    q = 'insert into parts values (?, ?, ?)'
    for part in msgparts(msg, id=c.lastrowid):
        c.execute(q, part)

def rmall(table):
    q = 'delete from {table} where rowid>-1'
    db.execute(q.format(table=table))