Gmail in SQLite speichern

 (github.com/marcboeker)
2 Punkte von GN⁺ 2025-05-11 | 1 Kommentare | Auf WhatsApp teilen
  • Gmail to SQLite ist eine Python-Anwendung, die Gmail-Nachrichten mit einer lokalen SQLite-Datenbank synchronisiert, um sie für Analysen und Archivierung zu nutzen
  • Das Standardverhalten ist eine inkrementelle Synchronisierung, die nur neue Nachrichten herunterlädt; mit der Vollsynchronisierungsoption können alle Nachrichten heruntergeladen und auch Löschungen erkannt werden
  • Der Nachrichtenabruf verwendet multithreaded Parallelverarbeitung und umfasst Fehler- und Beendigungsbehandlung wie automatische Wiederholungsversuche mit exponentiellem Backoff und Verarbeitung von CTRL+C
  • Für die Ausführung werden Python 3.8 oder höher, ein Google Cloud Project mit aktivierter Gmail API und die OAuth-2.0-Datei credentials.json benötigt
  • Die gespeicherten Daten enthalten Absender, Empfänger, Labels, Textinhalt, Größe, Gelesen-Status, Gesendet-Status, Löschstatus usw., sodass sich Gmail-Nutzungsmuster direkt per SQL analysieren lassen

Tool zur lokalen Synchronisierung von Gmail-Nachrichten

  • Gmail to SQLite ist eine Python-Anwendung, die Gmail-Nachrichten in einer lokalen SQLite-Datenbank speichert
  • Ziel ist es, Gmail-Daten zu analysieren und zu archivieren
  • Im gesamten Codebestand werden Type Hints verwendet, um Typsicherheit zu gewährleisten

Synchronisationsweise und Stabilität

  • Die Standardsynchronisierung arbeitet als inkrementelle Synchronisierung und lädt nur neue Nachrichten herunter
  • Mit der Option --full-sync werden alle Nachrichten synchronisiert und in Gmail gelöschte Nachrichten erkannt
  • Der Nachrichtenabruf erfolgt per multithreaded Parallelverarbeitung, um die Leistung zu erhöhen
  • Die Fehlerbehandlung umfasst automatische Wiederholungsversuche und exponentiellen Backoff
  • Beim Drücken von CTRL+C wird ein geordneter Beendigungsablauf ausgeführt
    • Das Annehmen neuer Aufgaben wird gestoppt
    • Es wird gewartet, bis laufende Aufgaben abgeschlossen sind
    • Der Fortschritt abgeschlossener Aufgaben wird gespeichert
    • Das Programm wird regulär beendet
  • Wenn CTRL+C ein weiteres Mal gedrückt wird, wird sofort beendet

Installation und Voraussetzungen

  • Als Laufzeitumgebung wird Python 3.8 oder höher benötigt
  • Erforderlich ist ein Google Cloud Project mit aktivierter Gmail API
  • Die OAuth-2.0-Datei credentials.json muss sich im Projektstamm befinden
  • Der Installationsablauf besteht darin, das Repository zu klonen und anschließend die Abhängigkeiten mit uv sync zu installieren
  • Für die Einrichtung der Gmail-API-Authentifizierung wird in der Google Cloud Console ein Projekt erstellt oder ausgewählt, die Gmail API aktiviert und anschließend OAuth-2.0-Anmeldedaten für eine Desktop application erzeugt und als credentials.json gespeichert

Verwendung der Befehle

  • Die standardmäßige inkrementelle Synchronisierung wird wie folgt ausgeführt
python main.py sync --data-dir ./data

# or: uv run main.py sync --data-dir ./data
  • Für Vollsynchronisierung und Löschungserkennung wird --full-sync verwendet
python main.py sync --data-dir ./data --full-sync
  • Um nur eine bestimmte Nachricht zu synchronisieren, werden sync-message und --message-id verwendet
python main.py sync-message --data-dir ./data --message-id MESSAGE_ID
  • Um nur gelöschte Nachrichten zu erkennen und zu markieren, wird sync-deleted-messages verwendet
python main.py sync-deleted-messages --data-dir ./data
  • Die Anzahl der Worker-Threads kann mit --workers festgelegt werden; der Standardwert ist die Anzahl der CPU-Kerne
python main.py sync --data-dir ./data --workers 8
  • Die Kommandozeilenargumente sind wie folgt
    • command: erforderlich; eines von sync, sync-message oder sync-deleted-messages
    • --data-dir: erforderlich; Verzeichnis, in dem die SQLite-Datenbank gespeichert wird
    • --full-sync: optional; erzwingt eine Vollsynchronisierung
    • --message-id: bei sync-message erforderlich; die konkrete Nachrichten-ID, die synchronisiert werden soll
    • --workers: optional; Anzahl der Worker-Threads
    • --help: zeigt Hilfe zu Befehlen und Optionen an

SQLite-Schema und Analysebeispiele

  • Die messages-Tabelle der erzeugten SQLite-Datenbank enthält die für die Analyse von Gmail-Nachrichten nötigen Felder
    • message_id: eindeutige Gmail-Nachrichten-ID
    • thread_id: Gmail-Thread-ID
    • sender: JSON-Absenderinformationen mit Name und E-Mail
    • recipients: Empfänger-JSON nach den Typen to, cc, bcc
    • labels: Array von Gmail-Labels
    • subject: Betreff der Nachricht
    • body: Nur-Text-Nachrichteninhalt
    • size: Nachrichtengröße in Byte
    • timestamp: Zeitpunkt der Nachricht
    • is_read: Gelesen-Status
    • is_outgoing: ob die Nachricht vom Nutzer gesendet wurde
    • is_deleted: ob die Nachricht in Gmail gelöscht wurde
    • last_indexed: Zeitpunkt der letzten Synchronisierung
  • Die Anzahl der E-Mails pro Absender lässt sich aggregieren
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Ungelesene E-Mails lassen sich pro Absender aggregieren, um Absender zu identifizieren, die viele uninteressante E-Mails senden
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE is_read = 0
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Mit strftime lässt sich die Anzahl der E-Mails nach Jahr, Monat, Tag, Wochentag oder Stunde aggregieren
SELECT strftime('%Y', timestamp) AS period, COUNT(*) AS count
FROM messages
GROUP BY period
ORDER BY count DESC
  • E-Mails, deren Inhalt newsletter oder unsubscribe enthält, können gefunden und Newsletter nach Absender gruppiert werden
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE body LIKE '%newsletter%' OR body LIKE '%unsubscribe%'
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Die gesamte E-Mail-Größe pro Absender und große E-Mail-Absender lassen sich in MB anzeigen
SELECT sender->>'$.email', sum(size)/1024/1024 AS size
FROM messages
GROUP BY sender->>'$.email'
ORDER BY size DESC
  • Die Anzahl der an sich selbst gesendeten E-Mails kann über das recipients-JSON und die sender-E-Mail-Bedingung berechnet werden
SELECT count(*)
FROM messages
WHERE EXISTS (
  SELECT 1
  FROM json_each(messages.recipients->'$.to')
  WHERE json_extract(value, '$.email') = 'foo@example.com'
)
AND sender->>'$.email' = 'foo@example.com'
  • Bei empfangenen E-Mails lässt sich die Gesamtkapazität pro Absender in absteigender Reihenfolge prüfen
SELECT sender->>'$.email', sum(size)/1024/1024 as total_size
FROM messages
WHERE is_outgoing=false
GROUP BY sender->>'$.email'
ORDER BY total_size DESC
  • Gelöschte Nachrichten werden mit der Bedingung is_deleted=1 abgefragt
SELECT message_id, subject, timestamp
FROM messages
WHERE is_deleted=1
ORDER BY timestamp DESC

Verwandte Beiträge

1 Kommentare

 
GN⁺ 2025-05-11
Meinungen auf Hacker News

  • Ich frage mich, warum bestimmte Header im Schema separat herausgezogen wurden. Man könnte recipients, subject und sender als JSON-Felder halten, aber auch alles in einem einzigen headers-Feld ablegen und sogar die übrigen Header der Nachricht darin speichern.
    Wenn es um Performance geht, kann man headers als einzelnen JSON-Blob belassen und die benötigten Felder als generierte Spalten anlegen. subject ließe sich zum Beispiel mit json_extract("headers", '$.Subject') erzeugen und indizieren.
    Dieses Modell war mächtig, weil Nutzer per ALTER TABLE indizierte generierte Spalten hinzufügen konnten, die sie für ihre eigenen Abfragen brauchen. Auch der DKIM-Status lässt sich ermitteln, indem man "Dkim-Signature" extrahiert, daraus eine Spalte samt Index macht und dann GROUP BY verwendet.

    • Eigentlich braucht man nicht einmal generierte Spalten, denn SQLite unterstützt Expression Indexes. Man könnte zum Beispiel CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject')) anlegen; dann wird der Index an Stellen genutzt, die auf diesen Ausdruck verweisen.
      Nachdem man einen solchen Index erstellt hat, war es hilfreicher, eine VIEW zu erstellen, die diesen Ausdruck verwendet, statt die Haupttabelle per ALTER um eine generierte Spalte zu erweitern.
    • Für einmalige Abfragen einen Index hinzuzufügen wirkt wie eine schlechte Angewohnheit.
      Normalerweise bevorzuge ich es, Spalten, die dauerhaft genutzt werden, separat herauszuziehen. Bei etwas so stabilem wie E-Mail-Headern gilt das umso mehr; eine headers-Spalte kann Schemaänderungen zwar etwas vereinfachen, verlagert aber den Schmerz vom Schreibzeitpunkt auf den Lesezeitpunkt und lässt Raum für stillschweigende Fehler.
    • Beim Hochskalieren von Systemen mit PostgreSQL nutze ich oft ein ähnliches Muster. Am Anfang erstellt man Tabellen anhand der Felder, von denen man weiß, dass man sie braucht, und legt übrige Metadaten in einer JSON-Spalte ab.
      Nach etwa zwei Monaten, wenn man sieht, welche Felder tatsächlich benötigt werden, füllt man sie aus dem JSON heraus, sorgt dafür, dass die API sie aktuell hält, oder erstellt Views. Das hat ziemlich geholfen, die Wachstumsschmerzen von „packen wir einfach alles in MongoDB“ oder „legen wir es einfach im Dateisystem ab“ zu vermeiden, und die Kosten waren nicht hoch.
    • Die Spalte dkim ist als NOT NULL definiert; ich frage mich, was passiert, wenn eine E-Mail-Nachricht keinen Dkim-Signature-Header hat.

  • Vor ein paar Jahren habe ich ein Tool zur Visualisierung großer E-Mail-Mengen wie in Gmail gebaut: https://github.com/terhechte/postsack

    • Ziemlich cool. Es ähnelt Tools zur Visualisierung der Festplattennutzung, scheint aber eher auf die Gesamtmenge der Mails als auf deren Speicherbedarf fokussiert zu sein.
      Ich frage mich, ob es auch eine Größenoption gibt. Ich würde gern sehen, welche Absender den meisten Speicherplatz belegen. Außerdem ist das SSL-Zertifikat der Website abgelaufen.
    • Sieht interessant aus. Der gmvault-Link im README ist inzwischen tot; ich frage mich, ob das hier der richtige ist: https://github.com/gaubert/gmvault
    • Sieht interessant aus. Früher habe ich mit qdirstat selbst etwas Ähnliches versucht, aber dafür musste man E-Mails auf eine bestimmte Weise anordnen, etwa in Datumsordnern, und es war schwierig, sie nach anderen Kriterien erneut aufzuschneiden.
      Dagegen sind qdirstat-Cache-Dateien leicht zu erstellen und lassen sich nutzen, um verschiedene dateiähnliche Objekte zu visualisieren.

  • Wirklich schade ist, dass man sich inzwischen nicht einmal mehr mit app-spezifischen Passwörtern anmelden kann, sondern einen OAuth-Client erstellen und den OAuth-Flow durchlaufen muss. Es sind meine E-Mails, und trotzdem hat Google mir einen offenen Standard für den Zugriff darauf weggenommen.

    • Wenn ich die Menge an Spam sehe, die an kostenlose Gmail-Adressen geht, und die Menge an Spam, die über Gmail-Server an Nicht-Gmail-Konten kommt, tendiere ich immer stärker zur EntGoogleung.
      Besonders erhalte ich immer häufiger Hinweise, dass meine Freelancer-E-Mails bei den Empfängersystemen im Spam landen. Allerdings weiß ich nicht, wie ich die Gewohnheiten loswerden soll, die sich durch das Google-Ökosystem eingeschliffen haben.
    • Ich frage mich, warum du app-spezifische Passwörter als offenen Standard ansiehst, OAuth aber nicht.
    • Ich bin nicht sicher, worauf du hinauswillst, denn mit App-Passwörtern bekommt man vollen IMAP-Zugriff.

  • Kürzlich habe ich versucht, Gmail in meine App https://github.com/rumca-js/Django-link-archive zu integrieren, habe viel zu viel Zeit darauf verwendet und bin zu dem Schluss gekommen, dass Gmail-Support den Aufwand nicht wert ist.
    Gmail to SQLite beschreibt die Einrichtung der Zugangsdaten in sechs Schritten, aber bei mir war es nicht so. Nach den sechs Schritten sagte Google immer noch, die App sei nicht veröffentlicht und müsse veröffentlicht werden; außerdem könne ich sie nicht als interne App belassen, weil ich kein Workspace-Nutzer sei, und nach der Umstellung auf eine externe App hieß es, sie sei vor der Verifizierung nicht nutzbar.
    Im Verifizierungsprozess wurden eine Domain, eine Adresse, weitere Details, eine Begründung für die Scopes und sogar ein Video verlangt, das die Nutzung der App erklärt; außerdem hieß es, die Prüfung der eingereichten Daten werde Zeit brauchen. Das Ganze ist ein Konfigurationslabyrinth, und es ist einfach zu viel verlangt, Nutzer durch die von Google geforderten Hürden springen zu lassen.

    • Die Prozedur, die Google den Leuten schon für einen einzigen API-Key zumutet, ist völlig absurd. Weiß jemand, warum das so schlecht ist?
    • Nimm einfach das altmodische IMAP und App-Passwörter. Du musst nicht durch Googles Hürden springen.

  • Ich frage mich, was derzeit die beste Open-Source-Gmail-Backup-Software ist. Mich interessiert, ob jemand so etwas schon eingerichtet hat, einschließlich der Archivierung von Anhängen.

    • Es gibt https://github.com/GAM-team/got-your-back. Es ist Open Source und hat eine Resume-Funktion, sodass Backup und Wiederherstellung am Ende abgeschlossen werden.
      Zur Info: Es gibt auch https://www.mailstore.com/en/products/mailstore-home/. Das ist zwar nicht Open Source, aber als GUI mit Index gut für die lokale Mail-Suche; Resume funktioniert nur beim Backup, daher scheitern große Wiederherstellungen meist.
    • Es ist vielleicht nicht genau die gewünschte Antwort, aber Google hat einen Dienst namens Takeout, mit dem man ein Backup der Daten aller Google-Dienste einschließlich Gmail anfordern und herunterladen kann.
      Ich lasse mich alle paar Monate daran erinnern, das auszuführen, und aktualisiere mein lokales Backup. Soweit ich mich erinnere, kommt es als gzip-komprimierte mbox-Datei herunter.
    • Wenn man einen IMAP-Client verwendet und ihn auf Offline-/Download-Modus stellt, kann man ebenfalls alles herunterladen und lokal speichern. In Evolution heißt das, glaube ich, „Offline-Modus“, in Thunderbird oder anderen Clients kann es anders heißen.

  • Das sollte meiner Meinung nach nicht „Gmail to SQLite“, sondern eher „IMAP to SQLite“ heißen. Ich verstehe nicht, warum man es an einen bestimmten E-Mail-Anbieter bindet.

    • Weil es tatsächlich Gmail-spezifisch ist. Es nutzt OAuth und vermutlich API-Zugriff.
      IMAP ist deutlich schwieriger und viel langsamer und hängt außerdem an Googles Bandbreitenlimits.
    • Ich habe jahrelang versucht, Gmail-Konten per IMAP zu sichern, aber es hat nie funktioniert, auch nicht mit Gmail-spezifischen Tools. Selbst das beste Synchronisierungstool lief einen Monat lang und blieb dann an einer Stelle hängen, an der es eine bestimmte Mail nicht abrufen konnte.
      Keine Ahnung, ob sie in einem zu kalten Storage lag und deshalb ein Timeout auftrat. Daher kann ich nachvollziehen, dass die Nutzung von Googles proprietärer API besser funktionieren kann.
      Heutzutage enthält Google Takeout mbox-Dateien, funktioniert korrekt und ist ziemlich schnell, aber es bietet keine kontinuierlichen Updates. Am Ende bin ich zu einem anderen Mail-Anbieter, Infomaniak, umgezogen und war meinem früheren Ich dankbar, dass ich meine Mail-Domain verwendet hatte.

  • Es wäre schön, wenn man auch Volltextsuche aktivieren könnte.

    • Dafür, dass Gmail von einem Suchunternehmen betrieben wird, finde ich die Volltextsuche überraschend schlecht.

  • Ich habe gestern dasselbe gebaut, weil ich die Empfänger-E-Mails nach Domain auflisten wollte. Der Code ist ein Chaos, aber hier ist er: https://github.com/hugoferreira/gmail-sqlite-db

  • Das erinnert mich ein wenig an Archiveopteryx, einen PostgreSQL-basierten IMAP-Server: https://github.com/aox/aox
    Das Schema von AOX sah für mich immer gut aus, aber ich habe es nie wirklich produktiv genutzt. Der Hauptanwendungsfall war Mail-Analyse und Suche, nicht ein IMAP-Server als Daily Driver.

    • Manitou-Mail fällt mir auch ein. Ein leistungsfähiger dedizierter PostgreSQL-basierter Mail-Client, den man als Daily Driver nutzen kann und der ziemlich robust ist: https://www.manitou-mail.org/

  • Ich frage mich, wie hier die Bandbreitenkosten aussehen. Als jemand mit einem Gmail-Konto von über 40 GB würde ich gern wissen, ob mir für die Übertragung mit diesem Tool Gebühren berechnet werden.
    Das lässt sich leicht beheben. Google Takeout scheint kostenlos zu sein, also kann man es zuerst herunterladen und dann die Datei parsen. Aus Sicht eines sofortigen Starts dürfte dieses Tool aber trotzdem schneller sein.