Guardar Gmail en SQLite

 (github.com/marcboeker)
2 puntos por GN⁺ 2025-05-11 | 1 comentarios | Compartir por WhatsApp
  • Gmail to SQLite es una aplicación en Python que sincroniza mensajes de Gmail con una base de datos SQLite local para usarlos en análisis y archivado
  • Su comportamiento predeterminado es una sincronización incremental, que descarga solo los mensajes nuevos; con la opción de sincronización completa puede descargar todos los mensajes y también detectar si fueron eliminados
  • La importación de mensajes usa procesamiento paralelo multihilo e incluye manejo de errores y cierre, como reintentos automáticos con backoff exponencial y manejo de CTRL+C
  • Para ejecutarla se requiere Python 3.8 o superior, un Google Cloud Project con la Gmail API habilitada y un archivo OAuth 2.0 credentials.json
  • Los datos guardados incluyen remitente, destinatarios, etiquetas, cuerpo, tamaño, estado de lectura, si fue enviado por el usuario y si fue eliminado, lo que permite analizar directamente con SQL los patrones de uso de Gmail

Herramienta de sincronización local de mensajes de Gmail

  • Gmail to SQLite es una aplicación en Python que guarda mensajes de Gmail en una base de datos SQLite local
  • Su objetivo es permitir analizar y archivar datos de Gmail
  • Aplica type hints en toda la base de código para contar con seguridad de tipos

Método de sincronización y confiabilidad

  • La sincronización predeterminada funciona como sincronización incremental, descargando solo mensajes nuevos
  • Al usar la opción --full-sync, sincroniza todos los mensajes y detecta los mensajes eliminados en Gmail
  • La importación de mensajes se realiza con procesamiento paralelo multihilo para mejorar el rendimiento
  • El manejo de errores incluye reintentos automáticos y backoff exponencial
  • Al presionar CTRL+C, se ejecuta un procedimiento de cierre ordenado
    • Deja de aceptar trabajos nuevos
    • Espera a que terminen los trabajos en ejecución
    • Guarda el progreso de los trabajos completados
    • Finaliza normalmente
  • Si se presiona CTRL+C una vez más, finaliza de inmediato

Instalación y requisitos previos

  • El entorno de ejecución requiere Python 3.8 o superior
  • Se necesita un Google Cloud Project con la Gmail API habilitada
  • El archivo de autenticación OAuth 2.0 credentials.json debe estar en la raíz del proyecto
  • El flujo de instalación consiste en clonar el repositorio y luego instalar las dependencias con uv sync
  • La configuración de autenticación de la Gmail API se realiza creando o seleccionando un proyecto en Google Cloud Console, habilitando la Gmail API, generando credenciales OAuth 2.0 para Desktop application y guardándolas como credentials.json

Uso de comandos

  • La sincronización incremental básica se ejecuta así
python main.py sync --data-dir ./data

# or: uv run main.py sync --data-dir ./data
  • Para sincronización completa y detección de eliminados, usa --full-sync
python main.py sync --data-dir ./data --full-sync
  • Para sincronizar solo un mensaje específico, usa sync-message y --message-id
python main.py sync-message --data-dir ./data --message-id MESSAGE_ID
  • Para detectar y mostrar solo mensajes eliminados, usa sync-deleted-messages
python main.py sync-deleted-messages --data-dir ./data
  • La cantidad de hilos worker puede especificarse con --workers; el valor predeterminado es la cantidad de núcleos de CPU
python main.py sync --data-dir ./data --workers 8
  • Los argumentos de línea de comandos son los siguientes
    • command: obligatorio; uno de sync, sync-message, sync-deleted-messages
    • --data-dir: obligatorio; directorio donde se guardará la base de datos SQLite
    • --full-sync: opcional; fuerza una sincronización completa
    • --message-id: obligatorio en sync-message; ID del mensaje específico a sincronizar
    • --workers: opcional; cantidad de hilos worker
    • --help: muestra la ayuda de comandos y opciones

Esquema SQLite y ejemplos de análisis

  • La tabla messages de la base de datos SQLite generada incluye los campos necesarios para analizar mensajes de Gmail
    • message_id: ID único del mensaje de Gmail
    • thread_id: ID del hilo de Gmail
    • sender: información del remitente en JSON con nombre y correo electrónico
    • recipients: JSON de destinatarios por tipo to, cc, bcc
    • labels: arreglo de etiquetas de Gmail
    • subject: asunto del mensaje
    • body: cuerpo del mensaje en texto plano
    • size: tamaño del mensaje en bytes
    • timestamp: fecha y hora del mensaje
    • is_read: estado de lectura
    • is_outgoing: si es un mensaje enviado por el usuario
    • is_deleted: si es un mensaje eliminado de Gmail
    • last_indexed: hora de la última sincronización
  • Se puede agrupar la cantidad de correos por remitente
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Se pueden agrupar los correos no leídos por remitente para identificar remitentes que envían muchos correos poco interesantes
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE is_read = 0
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Se puede usar strftime para agrupar la cantidad de correos por año, mes, día, día de la semana u hora
SELECT strftime('%Y', timestamp) AS period, COUNT(*) AS count
FROM messages
GROUP BY period
ORDER BY count DESC
  • Se pueden buscar correos cuyo cuerpo incluya newsletter o unsubscribe y agrupar newsletters por remitente
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE body LIKE '%newsletter%' OR body LIKE '%unsubscribe%'
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Se puede revisar el tamaño total de correos por remitente y los remitentes de correos grandes en MB
SELECT sender->>'$.email', sum(size)/1024/1024 AS size
FROM messages
GROUP BY sender->>'$.email'
ORDER BY size DESC
  • Se puede calcular la cantidad de correos enviados a uno mismo usando el JSON recipients y una condición sobre el correo del 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'
  • Se puede ver, entre los correos recibidos, el orden de remitentes con mayor volumen 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
  • Los mensajes eliminados se consultan con la condición is_deleted=1
SELECT message_id, subject, timestamp
FROM messages
WHERE is_deleted=1
ORDER BY timestamp DESC

Lecturas relacionadas

1 comentarios

 
GN⁺ 2025-05-11
Opiniones de Hacker News

  • Me da curiosidad por qué sacaron ciertos encabezados por separado en el esquema. recipients, subject y sender podrían estar como campos JSON, pero también podrían poner todo en un solo headers e incluir ahí el resto de los encabezados del mensaje.
    Si es por rendimiento, pueden dejar headers como un único blob JSON y convertir los campos necesarios en columnas generadas. Por ejemplo, subject se puede crear con json_extract("headers", '$.Subject') y ponerle un índice.
    Este modelo era potente porque el usuario podía agregar con ALTER TABLE las columnas generadas indexadas que necesitara para sus consultas. Para el estado de DKIM también se puede extraer "Dkim-Signature", crear una columna y un índice, y luego hacer GROUP BY.

    • En realidad, ni siquiera hacen falta columnas generadas: SQLite admite índices sobre expresiones. Por ejemplo, si se crea algo como CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject')), el índice se usa en los lugares que referencian esa expresión.
      Después de crear índices así, me resultó útil crear un VIEW que use esa expresión, en lugar de hacer ALTER en la tabla principal para agregar una columna generada.
    • Agregar un índice para una consulta puntual parece una mala práctica.
      Normalmente prefiero sacar por separado las columnas que se van a usar de forma constante. Más aún si se trata de algo estable como los encabezados de email; aunque la columna headers pueda facilitar un poco los cambios de esquema, en la práctica cambia el dolor del momento de escritura al momento de lectura y deja margen para fallas silenciosas.
    • Uso mucho un patrón parecido cuando hago crecer sistemas con PostgreSQL. Al principio creo una tabla pensando en los campos que sé que necesito, y dejo la metadata restante en una columna JSON.
      Después de unos dos meses, cuando aparecen los campos que realmente hacen falta, los cargo desde el JSON y hago que la API los mantenga actualizados, o creo una vista. Ayudó bastante a evitar los dolores de crecimiento de “pongamos todo en MongoDB” o “dejémoslo simplemente en el sistema de archivos”, y el costo no fue grande.
    • Definieron la columna dkim como NOT NULL; me pregunto qué pasa si un mensaje de email no tiene el encabezado Dkim-Signature.

  • Hace unos años hice una herramienta de visualización de email masivo tipo Gmail: https://github.com/terhechte/postsack

    • Está bastante buena. Se parece a una herramienta de visualización de uso de disco, pero parece enfocarse más en la cantidad total de correos que en el uso de disco.
      Me pregunto si también tiene una opción por tamaño. Quiero ver qué remitentes ocupan más espacio de almacenamiento. Y el certificado SSL del sitio web está vencido.
    • Se ve interesante. El enlace a gmvault en el README ya no funciona; me pregunto si este es el correcto: https://github.com/gaubert/gmvault
    • Se ve interesante. Antes hice algo parecido por mi cuenta con qdirstat, pero había que acomodar los emails de una forma específica, como carpetas por fecha, y era difícil volver a segmentarlos con otro criterio.
      En cambio, los archivos de caché de qdirstat son fáciles de crear, así que se pueden usar para visualizar varios objetos con aspecto de archivos.

  • Es una verdadera lástima que ahora ya no se pueda iniciar sesión ni siquiera con contraseñas específicas de app, y que haya que crear un cliente OAuth y pasar por el flujo OAuth. Aunque sea mi email, Google me quitó un estándar abierto para acceder a él.

    • Al ver la cantidad de spam que llega a una dirección gratuita de Gmail y la cantidad de spam que llega desde servidores de Gmail a cuentas que no son de Gmail, cada vez me inclino más hacia la desgoogleización.
      En especial, cada vez recibo más avisos de que mi email de freelance está cayendo en spam en los sistemas de los destinatarios. Aun así, no sé bien cómo abandonar los hábitos que adquirí dentro del ecosistema de Google.
    • Me da curiosidad por qué consideras que las contraseñas específicas de app son un estándar abierto y OAuth no.
    • No entiendo bien a qué apuntas, considerando que con una contraseña de app se obtiene acceso completo por IMAP.

  • Hace poco intenté integrar Gmail en mi app https://github.com/rumca-js/Django-link-archive y le dediqué demasiado tiempo; terminé concluyendo que no vale la pena dar soporte a Gmail.
    Gmail to SQLite explica la configuración de credenciales en 6 pasos, pero en mi caso no fue así. Después de esos 6 pasos, Google decía que la app no estaba publicada y que había que publicarla; luego decía que, como no era usuario de Workspace, no podía dejarla como app interna; y al cambiarla a app externa, decía que no podía usarse antes de la verificación.
    En el proceso de verificación pedían dominio, dirección, otros detalles, justificación de los alcances e incluso un video que explicara cómo se usa la app; además decían que tardarían en verificar los datos enviados. Todo es como un laberinto de configuración, y hacer que los usuarios salten los obstáculos que exige Google es demasiado.

    • Lo que Google hace pasar a la gente incluso para obtener una sola clave de API es completamente absurdo. Me pregunto si alguien sabe por qué es tan malo.
    • Simplemente usen el viejo IMAP con una contraseña de app. No hace falta saltar los obstáculos de Google.

  • Me pregunto cuál es el mejor software open source de respaldo para Gmail que existe hoy. También me interesa saber si alguien armó algo así incluyendo el almacenamiento de adjuntos

    • Está https://github.com/GAM-team/got-your-back. Es open source y tiene función de reanudación, así que el respaldo y la restauración terminan completándose
      Como referencia, también está https://www.mailstore.com/en/products/mailstore-home/. No es open source, pero al ser una GUI con índice es bueno para buscar correo localmente, y la reanudación solo funciona para respaldos, así que las restauraciones grandes suelen fallar
    • Quizá no sea exactamente la respuesta que buscas, pero Google tiene un servicio llamado Takeout, con el que puedes solicitar y descargar una copia de seguridad de los datos de todos los servicios de Google, incluido Gmail
      Tengo un recordatorio para ejecutarlo cada pocos meses y actualizar mi respaldo local. Si mal no recuerdo, baja como archivos mbox comprimidos con gzip
    • Si usas un cliente IMAP y lo configuras en modo offline/descarga, también puedes descargar todo y guardarlo localmente. En Evolution creo que lo llaman “modo offline”, pero en Thunderbird u otros clientes puede tener otro nombre

  • Me parece que esto debería llamarse algo como “IMAP to SQLite”, no “Gmail to SQLite”. No entiendo por qué atarlo a un proveedor de email específico

    • Porque en realidad es específico para Gmail. Usa OAuth y probablemente acceso vía API
      IMAP es mucho más difícil y mucho más lento, y además queda sujeto a los límites de ancho de banda de Google
    • Durante años intenté respaldar una cuenta de Gmail con IMAP, pero nunca tuve éxito, ni siquiera con herramientas específicas para Gmail. Incluso la mejor herramienta de sincronización estuvo corriendo un mes y luego se trabó en un punto donde no podía traer un correo específico
      No sé si estaba en un almacenamiento demasiado frío y por eso daba timeout. Así que entiendo que usar la API propietaria de Google pueda funcionar mejor
      Hoy en día Google Takeout incluye mbox, funciona correctamente y es bastante rápido, pero no sirve para actualizaciones continuas. Al final me mudé a otro proveedor de correo, Infomaniak, y agradecí haber usado antes mi propio dominio de correo

  • Estaría bueno poder activar también la búsqueda de texto completo

    • Para estar operado por una empresa de búsquedas, la búsqueda de texto completo de Gmail me parece sorprendentemente mala

  • Ayer hice algo parecido. Quería listar por dominio los emails de los destinatarios. El código es un desastre, pero está aquí: https://github.com/hugoferreira/gmail-sqlite-db

  • Me recuerda un poco a Archiveopteryx, el servidor IMAP basado en PostgreSQL: https://github.com/aox/aox
    El esquema de AOX siempre me pareció bueno, pero nunca llegué a usarlo realmente bien. Su uso principal era análisis y búsqueda de correo, no ser un servidor IMAP de uso diario

    • También me viene a la mente Manitou-Mail. Es un cliente de correo dedicado basado en PostgreSQL muy potente que puede usarse como herramienta diaria, y es bastante sólido: https://www.manitou-mail.org/

  • Me pregunto cómo son los costos de ancho de banda en esto. Como alguien con una cuenta de Gmail de más de 40 GB, quisiera saber si me cobrarían por transferirla con esta herramienta
    Es fácil de arreglar. Como Google Takeout parece ser gratis, podrías descargar primero desde ahí y parsear los archivos. Aun así, desde la perspectiva de empezar de inmediato, esta herramienta parece más rápida