Fix script to load emails in the database

- also turn on foreign keys support in sqlite3

1 files changed, 23 insertions, 5 deletions

diff --git a/email_helpers.py b/email_helpers.py
index 15d69a2..8a71b09 100644
--- a/email_helpers.py
+++ b/email_helpers.py
@@ -121,21 +121,39 @@ def extract_response(content):
 
 if __name__ == "__main__":
     import re
+    import sqlite3
+
     regex = re.compile("[^+]*\+([^@]*)")
+    db = sqlite3.connect("famille.db",
+                         detect_types=sqlite3.PARSE_DECLTYPES)
+    db.row_factory = sqlite3.Row
+    db.execute("PRAGMA foreign_keys=ON")
+    sql_str = "INSERT INTO comments" \
+              "(date, news_id, user_id, content, content_cache) " \
+              "VALUES(?, ?, ?, ?, ?)"
+
     for msg_id in ListMessagesWithLabels(GmailMessage._service, 'me', 'INBOX'):
         mail = GmailMessage.from_id(msg_id['id'])
-        _, email_addr = parseaddr(mail['From'])
         m = regex.match(mail['To'])
         if m:
+            _, email_addr = parseaddr(mail['From'])
+            c = db.execute("SELECT id FROM users WHERE email = ?", (email_addr,))
+            user_id, = c.fetchone()
+            c.close()
             body = mail.get_body()
             if body.get_content_type() == 'text/html':
                 comment_cache = extract_response(body.get_content())
                 comment = comment_cache.get_text()
+                comment_cache = str(comment_cache)
             elif body.get_content_type() == 'text/plain':
                 comment = comment_cache = body.get_content()
-            news_id = m.groups()[0]
+            news_id = int(m.groups()[0])
             date = parsedate_to_datetime(mail['Date'])
-            sql_str = "INSERT INTO comments(date, user_id, content, news_id, content_cache) "
-            "VALUES(?, ?, ?, ?)"
-            db.execute(sql_str, (date, user_id, comment.get_text(), news_id, comment)
+            try:
+                db.execute(sql_str, (date, news_id, user_id, comment, comment_cache))
+            except sqlite3.Error as e:
+                print(e)
+                db.rollback()
+                continue
+            db.commit()
             print(msg_id['id'], news_id, email_addr, date, comment)