Gmail'i SQLite'e kaydetmek

 (github.com/marcboeker)
2 puan yazan GN⁺ 2025-05-11 | 1 yorum | WhatsApp'ta paylaş
  • Gmail to SQLite, Gmail iletilerini yerel bir SQLite veritabanına senkronize ederek analiz ve arşivleme için kullanmayı sağlayan bir Python uygulamasıdır
  • Varsayılan davranış yalnızca yeni iletileri indiren artımlı senkronizasyondur; tam senkronizasyon seçeneğiyle tüm iletiler indirilebilir ve silinme durumu da tespit edilebilir
  • İleti alma işlemi çok iş parçacıklı paralel işleme kullanır; üstel geri çekilme tabanlı otomatik yeniden deneme ile CTRL+C işleme gibi hata ve sonlandırma önlemlerini içerir
  • Çalıştırmak için Python 3.8 veya üzeri, Gmail API etkinleştirilmiş bir Google Cloud Project ve OAuth 2.0 credentials.json dosyası gerekir
  • Kaydedilen veriler; gönderen, alıcılar, etiketler, gövde, boyut, okunma durumu, gönderilmiş olup olmadığı ve silinmiş olup olmadığı gibi alanları içerir; böylece Gmail kullanım kalıpları SQL ile doğrudan analiz edilebilir

Gmail iletilerini yerel olarak senkronize etme aracı

  • Gmail to SQLite, Gmail iletilerini yerel bir SQLite veritabanına kaydeden bir Python uygulamasıdır
  • Amacı, Gmail verilerini analiz ve arşivleme için kullanılabilir hâle getirmektir
  • Kod tabanının genelinde type hint uygulanarak tip güvenliği sağlanmıştır

Senkronizasyon yöntemi ve kararlılık

  • Varsayılan senkronizasyon, artımlı senkronizasyon olarak çalışır ve yalnızca yeni iletileri indirir
  • --full-sync seçeneği kullanıldığında tüm iletiler senkronize edilir ve Gmail'de silinen iletiler tespit edilir
  • İleti alma işlemi performansı artırmak için çok iş parçacıklı paralel işleme ile gerçekleştirilir
  • Hata işleme otomatik yeniden deneme ve üstel geri çekilme içerir
  • CTRL+C'ye basıldığında düzgün bir kapanış süreci çalıştırılır
    • Yeni iş kabul etmeyi durdurur
    • Çalışmakta olan işlerin bitmesini bekler
    • Tamamlanan işlerin ilerleme durumunu kaydeder
    • Normal şekilde kapanır
  • CTRL+C'ye bir kez daha basılırsa hemen sonlandırılır

Kurulum ve ön koşullar

  • Çalışma ortamında Python 3.8 veya üzeri gerekir
  • Gmail API etkinleştirilmiş bir Google Cloud Project gerekir
  • OAuth 2.0 kimlik doğrulama dosyası credentials.json, proje kök dizininde bulunmalıdır
  • Kurulum akışı, depoyu klonladıktan sonra uv sync ile bağımlılıkları yükleme şeklindedir
  • Gmail API kimlik doğrulama ayarı için Google Cloud Console'da bir proje oluşturulur veya seçilir, Gmail API etkinleştirilir, ardından Desktop application için OAuth 2.0 kimlik bilgileri oluşturulup credentials.json olarak kaydedilir

Komut kullanımı

  • Temel artımlı senkronizasyon şu şekilde çalıştırılır
python main.py sync --data-dir ./data

# or: uv run main.py sync --data-dir ./data
  • Tam senkronizasyon ve silme tespiti için --full-sync kullanılır
python main.py sync --data-dir ./data --full-sync
  • Yalnızca belirli bir iletiyi senkronize etmek için sync-message ve --message-id kullanılır
python main.py sync-message --data-dir ./data --message-id MESSAGE_ID
  • Yalnızca silinen iletileri tespit edip göstermek için sync-deleted-messages kullanılır
python main.py sync-deleted-messages --data-dir ./data
  • Worker iş parçacığı sayısı --workers ile belirtilebilir; varsayılan değer CPU çekirdeği sayısıdır
python main.py sync --data-dir ./data --workers 8
  • Komut satırı argümanları şöyledir
    • command: Zorunludur; sync, sync-message, sync-deleted-messages değerlerinden biri
    • --data-dir: Zorunludur; SQLite veritabanının kaydedileceği dizin
    • --full-sync: İsteğe bağlıdır; tam senkronizasyonu zorlar
    • --message-id: sync-message için zorunludur; senkronize edilecek belirli ileti ID'si
    • --workers: İsteğe bağlıdır; worker iş parçacığı sayısı
    • --help: Komut ve seçenek yardımını gösterir

SQLite şeması ve analiz örnekleri

  • Oluşturulan SQLite veritabanındaki messages tablosu, Gmail iletilerini analiz etmek için gereken alanları içerir
    • message_id: Benzersiz Gmail ileti ID'si
    • thread_id: Gmail ileti dizisi ID'si
    • sender: Ad ve e-posta içeren JSON gönderen bilgisi
    • recipients: to, cc, bcc türlerine göre alıcı JSON'u
    • labels: Gmail etiketleri dizisi
    • subject: İleti başlığı
    • body: Düz metin ileti gövdesi
    • size: Bayt cinsinden ileti boyutu
    • timestamp: İleti zamanı
    • is_read: Okunma durumu
    • is_outgoing: İletinin kullanıcı tarafından gönderilip gönderilmediği
    • is_deleted: İletinin Gmail'den silinip silinmediği
    • last_indexed: Son senkronizasyon zamanı
  • Gönderene göre e-posta sayısı toplanabilir
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Okunmamış e-postalar gönderene göre toplanarak ilgi çekmeyen çok sayıda e-posta gönderenler görülebilir
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE is_read = 0
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • strftime kullanarak e-posta sayısı yıl, ay, gün, haftanın günü ve saat bazında toplanabilir
SELECT strftime('%Y', timestamp) AS period, COUNT(*) AS count
FROM messages
GROUP BY period
ORDER BY count DESC
  • Gövdesinde newsletter veya unsubscribe bulunan e-postalar bulunarak bültenler gönderen bazında gruplanabilir
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE body LIKE '%newsletter%' OR body LIKE '%unsubscribe%'
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Gönderene göre toplam e-posta boyutu ve büyük e-posta gönderenleri MB cinsinden görülebilir
SELECT sender->>'$.email', sum(size)/1024/1024 AS size
FROM messages
GROUP BY sender->>'$.email'
ORDER BY size DESC
  • Kendinize gönderdiğiniz e-posta sayısı, recipients JSON'u ve sender e-posta koşuluyla hesaplanabilir
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'
  • Alınan e-postalar arasında gönderene göre toplam boyutu en yüksek olanlar görülebilir
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
  • Silinen iletiler is_deleted=1 koşuluyla sorgulanır
SELECT message_id, subject, timestamp
FROM messages
WHERE is_deleted=1
ORDER BY timestamp DESC

İlgili okumalar

1 yorum

 
GN⁺ 2025-05-11
Hacker News yorumları

  • Şemada belirli header’ların neden ayrı çıkarıldığını merak ediyorum. recipients, subject, sender JSON alanı olarak da tutulabilir; ama hepsi tek bir headers içine konup mesajın kalan header’ları da orada saklanabilir
    Performans yüzündense headers tek bir JSON blob’u olarak tutulup gerekli alanlar generated column yapılabilir. Örneğin subject, json_extract("headers", '$.Subject') ile oluşturulup indekslenebilir
    Bu model güçlüydü; kullanıcı kendi sorguları için ihtiyaç duyduğu indeksli generated column’ları ALTER TABLE ile ekleyebiliyordu. DKIM durumu da "Dkim-Signature" çıkarılarak bir kolon ve indeks oluşturulduktan sonra GROUP BY ile yapılabilir

    • Aslında generated column’a da gerek yok; SQLite expression index destekliyor. Örneğin CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject')) gibi oluşturursanız, ilgili ifadeye başvurulan yerlerde indeks kullanılır
      Bu şekilde indeks oluşturduktan sonra ana tabloya ALTER ile generated column eklemek yerine, ilgili ifadeyi kullanan bir VIEW oluşturmak daha kullanışlıydı
    • Tek seferlik sorgular için indeks eklemek kötü bir alışkanlık gibi görünüyor
      Genelde sürekli kullanılacak kolonları ayrı çıkarmayı tercih ederim. E-posta header’ları gibi stabil bir hedef söz konusuysa bu daha da geçerli; headers kolonu şema değişikliğini biraz kolaylaştırsa da yazma anındaki sıkıntıyı okuma anındaki sıkıntıya dönüştürür ve sessizce başarısız olma ihtimalini de bırakır
    • PostgreSQL ile sistemi büyütürken benzer bir deseni sık kullanıyorum. Başta ihtiyaç duyulacağını bildiğim alanları düşünüp tabloyu oluşturuyor, kalan metadata’yı JSON kolonuna koyuyorum
      Yaklaşık iki ay sonra gerçekten gereken alanlar ortaya çıkınca JSON’dan dolduruyor, API’nin bunu güncel tutmasını sağlıyor ya da view oluşturuyorum. “Her şeyi MongoDB’ye atalım” veya “Dosya sisteminde bırakalım” yaklaşımından gelen büyüme sancılarını önlemede epey yardımcı oldu ve maliyeti de büyük değildi
    • dkim kolonunu NOT NULL olarak tanımlamışsınız; bir e-posta mesajında Dkim-Signature header’ı yoksa ne olacağını merak ediyorum

  • Birkaç yıl önce Gmail benzeri büyük ölçekli e-posta görselleştirme aracı yapmıştım: https://github.com/terhechte/postsack

    • Oldukça havalı. Disk kullanımını görselleştiren araçlara benziyor, ama disk kullanımından çok e-postaların toplam miktarına odaklanıyor gibi
      Boyut seçeneği de var mı merak ediyorum. Hangi gönderenin depolama alanımı en çok kullandığını görmek isterim. Ayrıca web sitesinin SSL sertifikası süresi dolmuş
    • İlginç görünüyor. README’deki gmvault bağlantısı artık ölü; doğru olan bu mu acaba: https://github.com/gaubert/gmvault
    • İlginç görünüyor. Daha önce qdirstat ile benzer bir şeyi kendim denemiştim, ama e-postaları tarih klasörleri gibi belirli bir düzende yerleştirmek gerekiyor ve başka ölçütlerle yeniden bölmek zor oluyordu
      Buna karşılık qdirstat cache file oluşturmak kolay, bu yüzden dosya gibi görünen çeşitli hedefleri görselleştirmek için kullanılabilir

  • Artık uygulamaya özel parolayla bile giriş yapılamaması, bir OAuth client oluşturup OAuth akışından geçmek zorunda olmak gerçekten üzücü. Kendi e-postam olmasına rağmen Google, erişebilmem için kullanılan açık standardı elimden aldı

    • Ücretsiz Gmail adresine gelen spam miktarına ve Gmail sunucularından Gmail dışı hesaplara gelen spam miktarına bakınca giderek Google’dan uzaklaşma tarafına kayıyorum
      Özellikle freelance e-postalarımın alıcı sistemlerde spam’e düştüğüne dair giderek daha fazla bilgi alıyorum. Yine de Google ekosistemine alışmış alışkanlıkları nasıl bırakacağım konusunda kararsızım
    • Uygulamaya özel parolanın neden açık standart olup OAuth’un olmadığını düşündüğünüzü merak ediyorum
    • Uygulama parolası kullanınca tam IMAP erişimi mümkün oluyorken ne demek istediğinizi pek anlayamıyorum

  • Yakın zamanda uygulamama https://github.com/rumca-js/Django-link-archive Gmail entegre etmeye çalıştım ama çok fazla zaman harcadım ve Gmail desteğinin buna değmeyeceğine karar verdim
    Gmail to SQLite kimlik bilgisi kurulumunu 6 adımda anlatıyor, ama benim durumumda öyle olmadı. 6 adımdan sonra da Google uygulamanın yayımlanmadığını söyleyip yayımlamamı istedi; Workspace kullanıcısı olmadığım için uygulamayı dahili bırakamayacağımı söyledi; harici uygulamaya çevirince de doğrulamadan önce kullanılamayacağını söyledi
    Doğrulama sürecinde domain, adres, diğer ayrıntılar, kapsamlar için gerekçe ve uygulamanın nasıl kullanıldığını açıklayan bir video bile istedi; gönderilen verileri doğrulamanın zaman alacağını söyledi. Tüm süreç bir ayar labirenti gibi ve kullanıcıların Google’ın istediği engelleri aşmasını beklemek fazlasıyla abartı

    • Google’ın insanlara tek bir API anahtarı almak için bile yaptırdığı süreç tamamen akıl almaz. Bunun neden bu kadar kötü olduğunu bilen var mı merak ediyorum
    • Sadece eski usul IMAP ve uygulama parolası kullanın. Google’ın engellerine göre zıplamanıza gerek yok

  • Şu anda var olan en iyi açık kaynak Gmail yedekleme yazılımının hangisi olduğunu merak ediyorum. Eklerin saklanması da dahil, böyle bir kurulum yapmış olan var mı merak ediyorum

    • https://github.com/GAM-team/got-your-back var. Açık kaynak ve devam ettirme özelliği olduğu için yedekleme ve geri yükleme eninde sonunda tamamlanıyor
      Bu arada https://www.mailstore.com/en/products/mailstore-home/ da var. Açık kaynak değil ama indeksli bir GUI olduğu için yerel e-posta aramada iyi; devam ettirme yalnızca yedeklemede çalıştığından büyük ölçekli geri yüklemeler genelde başarısız oluyor
    • Tam olarak istediğin yanıt olmayabilir ama Google’da Takeout diye bir hizmet var; Gmail dahil tüm Google hizmetlerindeki verilerin yedeğini isteyip indirebiliyorsun
      Birkaç ayda bir bunu çalıştırmamı hatırlatacak bir bildirim kurup yerel yedeği güncelliyorum. Hatırladığım kadarıyla gzip ile sıkıştırılmış mbox dosyası olarak geliyor
    • Bir IMAP istemcisi kullanıp çevrimdışı/indirme moduna ayarlarsan tamamını indirip yerelde saklayabilirsin. Evolution’da buna “çevrimdışı mod” deniyor sanırım; Thunderbird veya diğer istemcilerde adı farklı olabilir

  • Bence bunun adı “Gmail to SQLite” değil, “IMAP to SQLite” gibi bir şey olmalı. Neden tek bir belirli e-posta sağlayıcısına bağlandığını anlamıyorum

    • Çünkü gerçekten Gmail’e özel. OAuth ve muhtemelen API erişimi kullanıyor
      IMAP çok daha zor ve çok daha yavaş; ayrıca Google’ın bant genişliği sınırlamalarına da takılıyor
    • Yıllar boyunca IMAP ile Gmail hesabımı yedeklemeye çalıştım ama Gmail’e özel araçlar dahil hiçbirinde başarılı olamadım. En iyi eşitleme aracı bile bir ay çalıştıktan sonra belirli bir e-postayı getiremediği bir noktada takıldı
      Çok soğuk depolamada olduğu için zaman aşımına mı uğradı bilmiyorum. Bu yüzden Google’ın sahipli API’sini kullanmanın daha iyi çalışabileceğini anlıyorum
      Bugünlerde Google Takeout mbox içeriyor, düzgün çalışıyor ve oldukça hızlı; ama sürekli güncelleme sağlamıyor. Sonunda başka bir e-posta sağlayıcısı olan Infomaniak’a taşındım ve geçmişte kendi posta alan adımı kullanmış olduğum için kendime teşekkür ettim

  • Tam metin arama da etkinleştirilebilse iyi olurdu

    • Bir arama şirketi tarafından işletilen bir hizmete göre Gmail’in tam metin aramasının şaşırtıcı derecede kötü olduğunu düşünüyorum

  • Dün ben de aynısından yaptım. Çünkü alıcı e-postalarını alan adına göre listelemek istiyordum. Kod berbat ama burada: https://github.com/hugoferreira/gmail-sqlite-db

  • PostgreSQL tabanlı IMAP sunucusu Archiveopteryx’i biraz hatırlatıyor: https://github.com/aox/aox
    AOX’un şeması bana hep iyi görünmüştü ama gerçek anlamda düzgün kullanamadım. Ana kullanım amacı, günlük kullanılan bir IMAP sunucusu olmaktan çok posta analizi ve aramaydı

    • Manitou-Mail de aklıma geliyor. Günlük kullanım için tercih edilebilecek güçlü, PostgreSQL tabanlı özel bir e-posta istemcisi ve oldukça sağlam: https://www.manitou-mail.org/

  • Burada bant genişliği maliyetinin nasıl olduğunu merak ediyorum. 40 GB’tan büyük Gmail hesabı olan biri olarak, bu araçla aktarım yaparsam ücretlendirilip ücretlendirilmeyeceğimi bilmek isterim
    Düzeltmesi kolay. Google Takeout ücretsiz gibi göründüğüne göre önce indirip dosyayı ayrıştırmak yeterli. Yine de hemen başlamak açısından bu araç daha hızlı olur gibi