Armazenando o Gmail em SQLite

 (github.com/marcboeker)
2 pontos por GN⁺ 2025-05-11 | 1 comentários | Compartilhar no WhatsApp
  • Gmail to SQLite é um aplicativo em Python que sincroniza mensagens do Gmail com um banco de dados SQLite local para uso em análise e arquivamento
  • O comportamento padrão é a sincronização incremental, que baixa apenas mensagens novas; com a opção de sincronização completa, é possível baixar todas as mensagens e também detectar exclusões
  • A importação de mensagens usa processamento paralelo com múltiplas threads e inclui tratamento de erros e encerramento, como tentativas automáticas com recuo exponencial e suporte a CTRL+C
  • Para executar, são necessários Python 3.8 ou superior, um Google Cloud Project com a Gmail API ativada e o arquivo OAuth 2.0 credentials.json
  • Os dados armazenados incluem remetente, destinatários, rótulos, corpo, tamanho, status de leitura, status de envio, status de exclusão e outros, permitindo analisar diretamente os padrões de uso do Gmail com SQL

Ferramenta de sincronização local de mensagens do Gmail

  • Gmail to SQLite é um aplicativo em Python que armazena mensagens do Gmail em um banco de dados SQLite local
  • O objetivo é permitir analisar e arquivar os dados do Gmail
  • Todo o código usa type hints para oferecer segurança de tipos

Modo de sincronização e confiabilidade

  • A sincronização padrão funciona como sincronização incremental, baixando apenas mensagens novas
  • Ao usar a opção --full-sync, todas as mensagens são sincronizadas e mensagens excluídas no Gmail são detectadas
  • A importação de mensagens é feita com processamento paralelo multithread para melhorar o desempenho
  • O tratamento de erros inclui novas tentativas automáticas e recuo exponencial
  • Ao pressionar CTRL+C, um procedimento de encerramento gracioso é executado
    • Para de aceitar novos trabalhos
    • Espera os trabalhos em execução terminarem
    • Salva o progresso dos trabalhos concluídos
    • Encerra normalmente
  • Se CTRL+C for pressionado mais uma vez, o programa é encerrado imediatamente

Instalação e requisitos de preparação

  • O ambiente de execução requer Python 3.8 ou superior
  • É necessário um Google Cloud Project com a Gmail API ativada
  • O arquivo de autenticação OAuth 2.0 credentials.json deve estar na raiz do projeto
  • O fluxo de instalação consiste em clonar o repositório e instalar as dependências com uv sync
  • A configuração de autenticação da Gmail API é feita criando ou selecionando um projeto no Google Cloud Console, ativando a Gmail API e depois gerando credenciais OAuth 2.0 para Desktop application, salvas como credentials.json

Uso dos comandos

  • A sincronização incremental padrão é executada assim
python main.py sync --data-dir ./data

# or: uv run main.py sync --data-dir ./data
  • Para sincronização completa com detecção de exclusão, use --full-sync
python main.py sync --data-dir ./data --full-sync
  • Para sincronizar apenas uma mensagem específica, use sync-message e --message-id
python main.py sync-message --data-dir ./data --message-id MESSAGE_ID
  • Para detectar e marcar apenas mensagens excluídas, use sync-deleted-messages
python main.py sync-deleted-messages --data-dir ./data
  • O número de threads worker pode ser definido com --workers; o padrão é o número de núcleos da CPU
python main.py sync --data-dir ./data --workers 8
  • Os argumentos de linha de comando são os seguintes
    • command: obrigatório; um entre sync, sync-message ou sync-deleted-messages
    • --data-dir: obrigatório; diretório onde o banco de dados SQLite será armazenado
    • --full-sync: opcional; força uma sincronização completa
    • --message-id: obrigatório em sync-message; ID específico da mensagem a sincronizar
    • --workers: opcional; número de threads worker
    • --help: mostra a ajuda dos comandos e opções

Esquema SQLite e exemplos de análise

  • A tabela messages do banco de dados SQLite gerado inclui os campos necessários para analisar mensagens do Gmail
    • message_id: ID único da mensagem no Gmail
    • thread_id: ID da thread no Gmail
    • sender: informações do remetente em JSON, com nome e e-mail
    • recipients: JSON de destinatários por tipo to, cc, bcc
    • labels: array de rótulos do Gmail
    • subject: assunto da mensagem
    • body: corpo da mensagem em texto simples
    • size: tamanho da mensagem em bytes
    • timestamp: horário da mensagem
    • is_read: status de leitura
    • is_outgoing: indica se a mensagem foi enviada pelo usuário
    • is_deleted: indica se a mensagem foi excluída no Gmail
    • last_indexed: horário da última sincronização
  • É possível agregar a quantidade de e-mails por remetente
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Também é possível contar e-mails não lidos por remetente para identificar quem envia muitos e-mails sem interesse
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE is_read = 0
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Com strftime, dá para agregar a quantidade de e-mails por ano, mês, dia, dia da semana ou hora
SELECT strftime('%Y', timestamp) AS period, COUNT(*) AS count
FROM messages
GROUP BY period
ORDER BY count DESC
  • Dá para encontrar e-mails com newsletter ou unsubscribe no corpo e agrupar newsletters por remetente
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE body LIKE '%newsletter%' OR body LIKE '%unsubscribe%'
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Também é possível verificar o tamanho total de e-mails por remetente e identificar grandes remetentes em MB
SELECT sender->>'$.email', sum(size)/1024/1024 AS size
FROM messages
GROUP BY sender->>'$.email'
ORDER BY size DESC
  • O número de e-mails enviados para si mesmo pode ser calculado com o JSON recipients e a condição do e-mail em 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'
  • Também é possível ver quais remetentes ocupam mais espaço entre os e-mails recebidos
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
  • Mensagens excluídas podem ser consultadas com a condição is_deleted=1
SELECT message_id, subject, timestamp
FROM messages
WHERE is_deleted=1
ORDER BY timestamp DESC

Leituras relacionadas

1 comentários

 
GN⁺ 2025-05-11
Comentários no Hacker News

  • Fiquei curioso sobre o motivo de destacar alguns cabeçalhos específicos no esquema. recipients, subject e sender poderiam ficar como campos JSON, mas também daria para colocar tudo em um único headers e incluir ali o restante dos cabeçalhos da mensagem
    Se a questão for desempenho, dá para manter headers como um único blob JSON e transformar os campos necessários em colunas geradas. Por exemplo, subject pode ser criado com json_extract("headers", '$.Subject') e indexado
    Esse modelo era poderoso porque o usuário podia adicionar, com ALTER TABLE, colunas geradas indexadas conforme a necessidade das próprias consultas. Também daria para extrair o estado do DKIM com "Dkim-Signature", criar uma coluna e um índice, e depois usar GROUP BY

    • Na prática, nem precisa de colunas geradas: o SQLite suporta índices por expressão. Por exemplo, CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject')); assim o índice é usado em qualquer lugar que referencie essa expressão
      Depois de criar índices desse jeito, achei mais útil criar uma VIEW usando essa expressão do que dar ALTER na tabela principal para adicionar uma coluna gerada
    • Adicionar índices para consultas pontuais parece um mau hábito
      Normalmente prefiro separar em colunas os campos que serão usados de forma recorrente. Isso vale ainda mais para algo estável como cabeçalhos de e-mail; embora a coluna headers possa facilitar um pouco mudanças de esquema, ela acaba trocando dor na escrita por dor na leitura e ainda abre espaço para falhas silenciosas
    • Uso um padrão parecido com frequência quando vou escalando sistemas em PostgreSQL. No começo, monto a tabela pensando nos campos que sei que vou precisar, e deixo o restante dos metadados em uma coluna JSON
      Depois de uns dois meses, quando fica claro quais campos realmente importam, eu os preencho a partir do JSON, mantenho a API atualizada ou crio uma view. Isso ajudou bastante a evitar as dores de crescimento do “vamos jogar tudo no MongoDB” ou “vamos deixar tudo no sistema de arquivos”, sem um custo grande
    • A coluna dkim foi definida como NOT NULL, então fiquei curioso sobre o que acontece quando uma mensagem de e-mail não tem o cabeçalho Dkim-Signature

  • Alguns anos atrás fiz uma ferramenta de visualização de grandes volumes de e-mail no estilo Gmail: https://github.com/terhechte/postsack

    • Bem legal. Parece uma ferramenta de visualização de uso de disco, mas com foco no volume total de e-mails, mais do que no uso de disco
      Fiquei curioso se há opção de tamanho. Eu gostaria de ver quais remetentes estão consumindo mais do meu armazenamento. E o certificado SSL do site expirou
    • Parece interessante. O link para gmvault no README está quebrado agora; seria este aqui o correto? https://github.com/gaubert/gmvault
    • Parece interessante. Já tentei fazer algo parecido no passado com qdirstat, mas isso exigia organizar os e-mails de uma forma específica, como em pastas por data, e depois era difícil recortar novamente por outro critério
      Já o arquivo de cache do qdirstat é fácil de criar, então ele pode ser usado para visualizar vários tipos de alvos que se parecem com arquivos

  • É realmente uma pena que agora não dê mais para entrar nem com senha de app, e que seja preciso criar um cliente OAuth e passar por todo o fluxo OAuth. O e-mail é meu, mas o Google tirou de mim um padrão aberto para eu poder acessá-lo

    • Olhando a quantidade de spam que chega em endereços Gmail gratuitos e a quantidade de spam que sai dos servidores do Gmail para contas fora do Gmail, vou ficando cada vez mais inclinado ao anti-Google
      Principalmente porque estou recebendo cada vez mais avisos de que meu e-mail de freelancer está caindo em spam nos sistemas dos destinatários. Só não faço ideia de como abandonar os hábitos que criei dentro do ecossistema do Google
    • Fiquei curioso por que você considera senha de app um padrão aberto, mas OAuth não
    • Não entendi muito bem o que você quis dizer, já que usar senha de app permite acesso IMAP completo

  • Recentemente tentei integrar o Gmail ao meu app https://github.com/rumca-js/Django-link-archive e gastei tempo demais nisso; concluí que não vale a pena dar suporte ao Gmail
    O Gmail to SQLite explica a configuração de credenciais em 6 etapas, mas no meu caso não foi assim. Mesmo depois da sexta etapa, o Google dizia que o app não estava publicado e que eu precisava publicá-lo; dizia também que, como eu não era usuário do Workspace, não podia deixá-lo como app interno; e, quando mudei para app externo, dizia que eu não poderia usá-lo antes da verificação
    No processo de verificação, pediram domínio, endereço, outros detalhes, justificativa para os escopos e até um vídeo explicando como o app seria usado. Também disseram que levaria tempo para verificar os dados enviados. Tudo isso parece um labirinto de configuração, e fazer o usuário passar por todos esses obstáculos impostos pelo Google é demais

    • O processo que o Google impõe às pessoas só para conseguir uma chave de API é completamente absurdo. Queria saber se alguém entende por que isso é tão ruim assim
    • Basta usar IMAP do jeito antigo com senha de app. Não precisa ficar pulando os obstáculos do Google

  • Estou curioso para saber qual é o melhor software open source de backup do Gmail que existe hoje. Também queria saber se alguém já montou algo assim incluindo arquivamento de anexos

    • Existe o https://github.com/GAM-team/got-your-back. É open source e tem recurso de retomada, então o backup e a restauração acabam sendo concluídos
      Como referência, também existe o https://www.mailstore.com/en/products/mailstore-home/. Não é open source, mas tem uma GUI com índice, então é bom para pesquisar e-mails localmente, e a retomada só funciona para backup, então restaurações grandes geralmente falham
    • Talvez não seja exatamente a resposta desejada, mas o Google tem um serviço chamado Takeout que permite solicitar e baixar um backup dos dados de todos os serviços do Google, incluindo o Gmail
      Eu deixo um lembrete para rodar isso a cada poucos meses e atualizar meu backup local. Pelo que lembro, ele baixa como arquivos mbox compactados com gzip
    • Também dá para usar um cliente IMAP e configurar em modo offline/download para baixar tudo e salvar localmente. No Evolution isso parece se chamar “modo offline”, mas no Thunderbird ou em outros clientes o nome pode ser diferente

  • Parece que isso deveria se chamar algo como “IMAP to SQLite”, e não “Gmail to SQLite”. Não entendo por que amarrar isso a um único provedor de e-mail específico

    • Na prática, é específico para Gmail. Usa OAuth e provavelmente acesso via API
      IMAP é bem mais difícil e bem mais lento, além de ficar preso aos limites de largura de banda do Google
    • Tentei por anos fazer backup da minha conta do Gmail via IMAP, mas nunca consegui, nem mesmo com ferramentas específicas para Gmail. Até a melhor ferramenta de sincronização travava depois de rodar por um mês, ao chegar num ponto em que não conseguia buscar um determinado e-mail
      Não sei se ele estava em algum armazenamento frio demais e por isso dava timeout. Então entendo que usar a API proprietária do Google pode funcionar melhor
      Hoje em dia o Google Takeout inclui mbox, funciona direito e é bem rápido, mas não serve para atualizações contínuas. Acabei migrando para outro provedor de e-mail, o Infomaniak, e agradeci a mim mesmo por já ter usado meu domínio de e-mail lá atrás

  • Seria bom se também fosse possível ativar busca em texto completo

    • Para uma empresa de busca, acho surpreendente como a busca em texto completo do Gmail é ruim

  • Ontem eu também fiz a mesma coisa. Eu queria listar os e-mails dos destinatários por domínio. O código está uma bagunça, mas está aqui: https://github.com/hugoferreira/gmail-sqlite-db

  • Isso me lembrou um pouco o Archiveopteryx, um servidor IMAP baseado em PostgreSQL: https://github.com/aox/aox
    O esquema do AOX sempre pareceu bom, mas nunca cheguei a usá-lo de verdade. O uso principal era análise e busca de e-mails, não um servidor IMAP principal para uso diário

    • Também me lembrou o Manitou-Mail. É um cliente de e-mail dedicado baseado em PostgreSQL forte o bastante para uso diário e bem sólido: https://www.manitou-mail.org/

  • Fico curioso sobre como funcionam os custos de largura de banda aqui. Como alguém com uma conta do Gmail com mais de 40 GB, queria saber se transferir com essa ferramenta geraria cobrança
    É fácil de resolver. Como o Google Takeout aparentemente é gratuito, basta baixar por lá primeiro e fazer o parse dos arquivos. Ainda assim, do ponto de vista de começar imediatamente, esta ferramenta parece mais rápida