Stocker Gmail dans SQLite

 (github.com/marcboeker)
2 points par GN⁺ 2025-05-11 | 1 commentaires | Partager sur WhatsApp
  • Gmail to SQLite est une application Python qui synchronise les messages Gmail dans une base de données SQLite locale afin de les analyser et de les archiver
  • Le comportement par défaut est une synchronisation incrémentale qui ne télécharge que les nouveaux messages ; avec l’option de synchronisation complète, il est possible de récupérer tous les messages et de détecter aussi les suppressions
  • L’importation des messages utilise un traitement parallèle multithread et inclut une gestion des erreurs et de l’arrêt, comme les nouvelles tentatives automatiques avec backoff exponentiel et la prise en charge de CTRL+C
  • L’exécution nécessite Python 3.8 ou une version supérieure, un Google Cloud Project avec l’API Gmail activée, ainsi qu’un fichier OAuth 2.0 credentials.json
  • Les données stockées incluent l’expéditeur, les destinataires, les labels, le corps, la taille, l’état de lecture, le statut d’envoi, l’état de suppression, etc., ce qui permet d’analyser directement les usages de Gmail en SQL

Outil de synchronisation locale des messages Gmail

  • Gmail to SQLite est une application Python qui enregistre les messages Gmail dans une base de données SQLite locale
  • Son objectif est de permettre l’analyse et l’archivage des données Gmail
  • L’ensemble du code applique des indications de type afin d’assurer une bonne sûreté de typage

Mode de synchronisation et fiabilité

  • La synchronisation par défaut fonctionne en mode incrémental et ne télécharge que les nouveaux messages
  • L’option --full-sync permet de synchroniser l’ensemble des messages et de détecter ceux supprimés dans Gmail
  • La récupération des messages s’effectue en parallèle multithread pour améliorer les performances
  • La gestion des erreurs inclut des nouvelles tentatives automatiques et un backoff exponentiel
  • En appuyant sur CTRL+C, une procédure d’arrêt propre est lancée
    • arrêt de l’acceptation de nouvelles tâches
    • attente de la fin des tâches en cours
    • enregistrement de l’état d’avancement des tâches terminées
    • arrêt normal du programme
  • Si l’on appuie une seconde fois sur CTRL+C, le programme se termine immédiatement

Installation et prérequis

  • L’environnement d’exécution requiert Python 3.8 ou une version supérieure
  • Un Google Cloud Project avec l’API Gmail activée est nécessaire
  • Le fichier d’authentification OAuth 2.0 credentials.json doit se trouver à la racine du projet
  • Le processus d’installation consiste à cloner le dépôt, puis à installer les dépendances avec uv sync
  • Pour configurer l’authentification à l’API Gmail, il faut créer ou sélectionner un projet dans Google Cloud Console, activer l’API Gmail, puis générer des identifiants OAuth 2.0 pour une Desktop application et les enregistrer sous credentials.json

Utilisation en ligne de commande

  • La synchronisation incrémentale par défaut s’exécute ainsi
python main.py sync --data-dir ./data

# or: uv run main.py sync --data-dir ./data
  • La synchronisation complète avec détection des suppressions utilise --full-sync
python main.py sync --data-dir ./data --full-sync
  • Pour ne synchroniser qu’un message précis, utiliser sync-message avec --message-id
python main.py sync-message --data-dir ./data --message-id MESSAGE_ID
  • Pour détecter et marquer uniquement les messages supprimés, utiliser sync-deleted-messages
python main.py sync-deleted-messages --data-dir ./data
  • Le nombre de threads workers peut être défini avec --workers ; la valeur par défaut correspond au nombre de cœurs CPU
python main.py sync --data-dir ./data --workers 8
  • Les arguments en ligne de commande sont les suivants
    • command : obligatoire ; l’une des valeurs sync, sync-message, sync-deleted-messages
    • --data-dir : obligatoire ; répertoire dans lequel sera stockée la base de données SQLite
    • --full-sync : optionnel ; force une synchronisation complète
    • --message-id : obligatoire avec sync-message ; identifiant du message à synchroniser
    • --workers : optionnel ; nombre de threads workers
    • --help : affiche l’aide sur les commandes et les options

Schéma SQLite et exemples d’analyse

  • La table messages créée dans la base de données SQLite contient les champs nécessaires à l’analyse des messages Gmail
    • message_id : identifiant unique du message Gmail
    • thread_id : identifiant du thread Gmail
    • sender : informations JSON de l’expéditeur, avec nom et e-mail
    • recipients : JSON des destinataires par type to, cc, bcc
    • labels : tableau des labels Gmail
    • subject : objet du message
    • body : corps du message en texte brut
    • size : taille du message en octets
    • timestamp : horodatage du message
    • is_read : état de lecture
    • is_outgoing : indique si le message a été envoyé par l’utilisateur
    • is_deleted : indique si le message a été supprimé dans Gmail
    • last_indexed : date de la dernière synchronisation
  • Il est possible d’agréger le nombre d’e-mails par expéditeur
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • On peut agréger les e-mails non lus par expéditeur afin d’identifier ceux qui envoient beaucoup de messages peu intéressants
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE is_read = 0
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Avec strftime, il est possible de compter les e-mails par année, mois, jour, jour de la semaine ou heure
SELECT strftime('%Y', timestamp) AS period, COUNT(*) AS count
FROM messages
GROUP BY period
ORDER BY count DESC
  • On peut trouver les messages dont le corps contient newsletter ou unsubscribe afin de regrouper les newsletters par expéditeur
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE body LIKE '%newsletter%' OR body LIKE '%unsubscribe%'
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • La taille totale des e-mails par expéditeur peut être consultée pour repérer les plus gros volumes, en Mo
SELECT sender->>'$.email', sum(size)/1024/1024 AS size
FROM messages
GROUP BY sender->>'$.email'
ORDER BY size DESC
  • Il est possible de calculer le nombre d’e-mails que l’on s’est envoyés à soi-même via le JSON recipients et la condition sur l’e-mail de sender
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'
  • On peut voir, parmi les e-mails reçus, quels expéditeurs représentent le plus gros volume total
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
  • Les messages supprimés se consultent avec la condition is_deleted=1
SELECT message_id, subject, timestamp
FROM messages
WHERE is_deleted=1
ORDER BY timestamp DESC

À lire aussi

1 commentaires

 
GN⁺ 2025-05-11
Avis sur Hacker News

  • Je me demande pourquoi certains en-têtes ont été extraits séparément dans le schéma. recipients, subject, sender pourraient être des champs JSON, mais on pourrait aussi tout mettre dans un seul champ headers, avec le reste des en-têtes du message
    Si c’est pour des raisons de performance, il suffit de garder headers comme blob JSON unique et de créer les champs nécessaires sous forme de colonnes générées. Par exemple, subject peut être défini avec json_extract("headers", '$.Subject') et indexé
    Ce modèle était puissant, car l’utilisateur pouvait ajouter avec ALTER TABLE les colonnes générées indexées nécessaires à ses propres requêtes. Pour l’état DKIM aussi, il suffit d’extraire "Dkim-Signature", d’en faire une colonne et un index, puis de faire un GROUP BY

    • En réalité, les colonnes générées ne sont même pas nécessaires : SQLite prend en charge les index sur expressions. Par exemple, avec CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject')), l’index sera utilisé là où cette expression est référencée
      Après avoir créé ce type d’index, il m’a semblé plus utile de créer une VIEW utilisant l’expression en question, plutôt que de faire un ALTER sur la table principale pour ajouter une colonne générée
    • Ajouter un index pour une requête ponctuelle me semble être une mauvaise pratique
      En général, je préfère extraire séparément les colonnes qui seront utilisées régulièrement. C’est d’autant plus vrai pour quelque chose d’aussi stable que les en-têtes d’e-mail ; même si une colonne headers peut rendre les changements de schéma un peu plus faciles, cela revient à déplacer la douleur de l’écriture vers la lecture, tout en laissant la possibilité d’échecs silencieux
    • J’utilise souvent un schéma similaire quand je fais évoluer un système avec PostgreSQL. Au début, je crée une table avec les champs dont je sais avoir besoin, et je mets le reste des métadonnées dans une colonne JSON
      Au bout de deux mois environ, quand les champs réellement nécessaires apparaissent, je les renseigne à partir du JSON, puis je fais en sorte que l’API les garde à jour, ou je crée une vue. Cela m’a pas mal aidé à éviter les douleurs de croissance du type « mettons tout simplement dans MongoDB » ou « mettons tout simplement ça sur le système de fichiers », sans coût important
    • La colonne dkim est définie comme NOT NULL ; je me demande ce qui se passe si un message e-mail n’a pas d’en-tête Dkim-Signature

  • Il y a quelques années, j’ai créé un outil de visualisation d’e-mails en masse façon Gmail : https://github.com/terhechte/postsack

    • Plutôt sympa. Cela ressemble à un outil de visualisation de l’utilisation disque, mais avec un focus sur le volume total des mails plutôt que sur l’espace disque utilisé
      Je me demande s’il existe aussi une option de taille. J’aimerais voir quels expéditeurs utilisent le plus mon espace de stockage. Et le certificat SSL du site web est expiré
    • Ça a l’air intéressant. Le lien gmvault dans le README est désormais mort ; je me demande si c’est bien celui-ci : https://github.com/gaubert/gmvault
    • Ça a l’air intéressant. J’avais déjà essayé de faire quelque chose de similaire moi-même avec qdirstat, mais il fallait organiser les e-mails d’une certaine façon, par exemple dans des dossiers par date, et il était difficile de les redécouper selon d’autres critères
      En revanche, les fichiers de cache qdirstat sont faciles à générer, ce qui permet de les utiliser pour visualiser différents objets qui ressemblent à des fichiers

  • Ce qui est vraiment dommage aujourd’hui, c’est qu’on ne peut même plus se connecter avec un mot de passe propre à l’application : il faut créer un client OAuth et passer par le flux OAuth. C’est mon e-mail, mais Google m’a retiré un standard ouvert pour y accéder

    • Quand je vois la quantité de spam reçue sur une adresse Gmail gratuite, et celle qui arrive depuis les serveurs Gmail vers des comptes non Gmail, je penche de plus en plus vers le dégooglisation
      En particulier, on me signale de plus en plus souvent que mon e-mail de freelance arrive dans le dossier spam des systèmes des destinataires. Cela dit, je ne vois pas bien comment me débarrasser des habitudes prises dans l’écosystème Google
    • Je me demande pourquoi les mots de passe d’application seraient considérés comme un standard ouvert, et pas OAuth
    • Je ne suis pas sûr de comprendre l’argument, sachant qu’un mot de passe d’application donne un accès IMAP complet

  • J’ai récemment essayé d’intégrer Gmail à mon application https://github.com/rumca-js/Django-link-archive, mais j’y ai passé beaucoup trop de temps et j’ai conclu que la prise en charge de Gmail n’en valait pas la peine
    Gmail to SQLite explique la configuration des identifiants en 6 étapes, mais dans mon cas, ce n’était pas comme ça. Après ces 6 étapes, Google m’a dit que l’application n’était pas publiée et qu’il fallait la publier ; que je ne pouvais pas la laisser en application interne parce que je n’étais pas utilisateur Workspace ; puis, en la passant en application externe, qu’elle ne pouvait pas être utilisée avant vérification
    Dans le processus de vérification, on m’a demandé un domaine, une adresse, d’autres détails, une justification des scopes, et même une vidéo expliquant comment l’application est utilisée ; puis on m’a indiqué que la vérification des données soumises prendrait du temps. Tout cela ressemble à un labyrinthe de configuration, et imposer aux utilisateurs de franchir les obstacles exigés par Google est vraiment excessif

    • Les démarches que Google fait faire aux gens ne serait-ce que pour obtenir une clé d’API sont complètement absurdes. Je me demande si quelqu’un sait pourquoi c’est aussi mauvais
    • Il suffit d’utiliser l’ancien IMAP avec un mot de passe d’application. Pas besoin de sauter à travers les cerceaux de Google

  • Je me demande quel est actuellement le meilleur logiciel open source de sauvegarde Gmail. J’aimerais savoir si quelqu’un a déjà mis en place quelque chose de ce genre, y compris l’archivage des pièces jointes.

    • Il y a https://github.com/GAM-team/got-your-back. C’est open source, et comme il prend en charge la reprise, les sauvegardes et restaurations finissent par aboutir.
      À noter qu’il existe aussi https://www.mailstore.com/en/products/mailstore-home/. Ce n’est pas open source, mais c’est une GUI indexée, donc pratique pour rechercher dans le courrier local ; la reprise ne fonctionne que pour la sauvegarde, si bien que les restaurations volumineuses échouent généralement.
    • Ce n’est peut-être pas exactement la réponse attendue, mais Google propose un service appelé Takeout, qui permet de demander et télécharger une sauvegarde des données de tous les services Google, y compris Gmail.
      Je me mets un rappel tous les quelques mois pour le lancer et mettre à jour ma sauvegarde locale. De mémoire, on récupère des fichiers mbox compressés avec gzip.
    • On peut aussi utiliser un client IMAP et le configurer en mode hors ligne/téléchargement pour tout récupérer et le stocker en local. Dans Evolution, il me semble que cela s’appelle « mode hors ligne », mais le nom peut varier dans Thunderbird ou d’autres clients.

  • Je me dis que cela devrait plutôt s’appeler quelque chose comme « IMAP to SQLite » plutôt que « Gmail to SQLite ». Je ne comprends pas pourquoi l’attacher à un fournisseur d’e-mail précis.

    • Parce qu’en réalité, c’est spécifique à Gmail. Il utilise OAuth et probablement l’accès à l’API.
      IMAP est beaucoup plus difficile, beaucoup plus lent, et reste soumis aux limites de bande passante de Google.
    • J’ai essayé pendant des années de sauvegarder des comptes Gmail via IMAP, mais je n’ai jamais réussi, même avec des outils dédiés à Gmail. Même le meilleur outil de synchronisation a tourné pendant un mois avant de rester bloqué à un point où il n’arrivait pas à récupérer un message précis.
      Je ne sais pas si ce message se trouvait dans un stockage trop froid et provoquait un timeout. Je comprends donc qu’utiliser l’API propriétaire de Google puisse mieux fonctionner.
      Aujourd’hui, Google Takeout inclut les mbox, fonctionne correctement et assez vite, mais il ne permet pas de mises à jour continues. Au final, je suis passé chez un autre fournisseur de messagerie, Infomaniak, et je me suis remercié d’avoir utilisé mon propre domaine mail à l’époque.

  • Ce serait bien de pouvoir activer aussi la recherche plein texte.

    • Pour une messagerie exploitée par une société de recherche, je trouve la recherche plein texte de Gmail étonnamment médiocre.

  • Hier, j’ai créé la même chose, parce que je voulais lister les e-mails des destinataires par domaine. Le code est en vrac, mais il est ici : https://github.com/hugoferreira/gmail-sqlite-db

  • Cela me rappelle un peu Archiveopteryx, un serveur IMAP basé sur PostgreSQL : https://github.com/aox/aox
    Le schéma d’AOX m’a toujours semblé intéressant, mais je ne l’ai jamais vraiment utilisé correctement. Son usage principal était l’analyse et la recherche dans les e-mails, pas un serveur IMAP de tous les jours.

    • Cela me fait aussi penser à Manitou-Mail. C’est un client mail spécialisé basé sur PostgreSQL puissant, utilisable au quotidien, et plutôt robuste : https://www.manitou-mail.org/

  • Je me demande ce qu’il en est des coûts de bande passante ici. Avec un compte Gmail de plus de 40 Go, j’aimerais savoir si le transfert via cet outil est facturé.
    C’est facile à corriger. Comme Google Takeout semble gratuit, il suffit de télécharger d’abord les données puis de parser les fichiers. Cela dit, pour démarrer directement, cet outil paraît plus rapide.