Lưu Gmail vào SQLite

 (github.com/marcboeker)
2 điểm bởi GN⁺ 2025-05-11 | 1 bình luận | Chia sẻ qua WhatsApp
  • Gmail to SQLite là một ứng dụng Python đồng bộ tin nhắn Gmail vào cơ sở dữ liệu SQLite cục bộ để phục vụ phân tích và lưu trữ
  • Cách hoạt động mặc định là đồng bộ gia tăng, chỉ tải các tin nhắn mới; với tùy chọn đồng bộ toàn bộ, có thể tải tất cả tin nhắn và phát hiện cả việc đã bị xóa hay chưa
  • Việc lấy tin nhắn sử dụng xử lý song song đa luồng, đồng thời bao gồm cơ chế xử lý lỗi và kết thúc như tự động thử lại với exponential backoff và xử lý CTRL+C
  • Để chạy cần có Python 3.8 trở lên, một Google Cloud Project đã bật Gmail API, và tệp OAuth 2.0 credentials.json
  • Dữ liệu đã lưu bao gồm người gửi, người nhận, nhãn, nội dung, kích thước, trạng thái đã đọc, trạng thái gửi đi, trạng thái đã xóa, v.v., cho phép phân tích trực tiếp thói quen sử dụng Gmail bằng SQL

Công cụ đồng bộ cục bộ tin nhắn Gmail

  • Gmail to SQLite là một ứng dụng Python lưu tin nhắn Gmail vào cơ sở dữ liệu SQLite cục bộ
  • Mục tiêu là giúp phân tích và lưu trữ dữ liệu Gmail
  • Toàn bộ codebase được áp dụng type hint để đảm bảo an toàn kiểu dữ liệu

Cách đồng bộ và độ ổn định

  • Đồng bộ mặc định hoạt động theo kiểu đồng bộ gia tăng, chỉ tải xuống các tin nhắn mới
  • Dùng tùy chọn --full-sync sẽ đồng bộ toàn bộ tin nhắn và phát hiện các tin nhắn đã bị xóa khỏi Gmail
  • Việc lấy tin nhắn được thực hiện bằng xử lý song song đa luồng để tăng hiệu năng
  • Xử lý lỗi bao gồm tự động thử lại và exponential backoff
  • Khi nhấn CTRL+C, quy trình thoát an toàn sẽ được thực hiện
    • Ngừng nhận tác vụ mới
    • Chờ các tác vụ đang chạy hoàn thành
    • Lưu tiến độ của các tác vụ đã hoàn tất
    • Thoát bình thường
  • Nhấn CTRL+C thêm một lần nữa sẽ thoát ngay lập tức

Cài đặt và điều kiện chuẩn bị

  • Môi trường chạy cần Python 3.8 trở lên
  • Cần có Google Cloud Project đã bật Gmail API
  • Tệp xác thực OAuth 2.0 credentials.json phải nằm ở thư mục gốc của dự án
  • Quy trình cài đặt là clone repository rồi cài dependency bằng uv sync
  • Thiết lập xác thực Gmail API được thực hiện bằng cách tạo hoặc chọn một dự án trong Google Cloud Console, bật Gmail API, sau đó tạo thông tin xác thực OAuth 2.0 cho Desktop application và lưu thành credentials.json

Cách dùng lệnh

  • Đồng bộ gia tăng mặc định được chạy như sau
python main.py sync --data-dir ./data

# or: uv run main.py sync --data-dir ./data
  • Đồng bộ toàn bộ và phát hiện xóa dùng --full-sync
python main.py sync --data-dir ./data --full-sync
  • Nếu chỉ muốn đồng bộ một tin nhắn cụ thể, dùng sync-message và --message-id
python main.py sync-message --data-dir ./data --message-id MESSAGE_ID
  • Nếu chỉ muốn phát hiện và đánh dấu các tin nhắn đã xóa, dùng sync-deleted-messages
python main.py sync-deleted-messages --data-dir ./data
  • Có thể chỉ định số worker thread bằng --workers, giá trị mặc định là số lõi CPU
python main.py sync --data-dir ./data --workers 8
  • Các tham số dòng lệnh như sau
    • command: bắt buộc, là một trong sync, sync-message, sync-deleted-messages
    • --data-dir: bắt buộc, là thư mục sẽ lưu cơ sở dữ liệu SQLite
    • --full-sync: tùy chọn, dùng để ép đồng bộ toàn bộ
    • --message-id: bắt buộc với sync-message, là ID của tin nhắn cụ thể cần đồng bộ
    • --workers: tùy chọn, là số lượng worker thread
    • --help: hiển thị trợ giúp về lệnh và tùy chọn

Schema SQLite và ví dụ phân tích

  • Bảng messages trong cơ sở dữ liệu SQLite được tạo ra bao gồm các trường cần thiết để phân tích tin nhắn Gmail
    • message_id: ID tin nhắn Gmail duy nhất
    • thread_id: ID luồng Gmail
    • sender: thông tin người gửi JSON chứa tên và email
    • recipients: JSON người nhận theo các loại to, cc, bcc
    • labels: mảng nhãn Gmail
    • subject: tiêu đề tin nhắn
    • body: nội dung tin nhắn dạng văn bản thuần
    • size: kích thước tin nhắn tính theo byte
    • timestamp: thời điểm của tin nhắn
    • is_read: trạng thái đã đọc
    • is_outgoing: có phải tin nhắn do người dùng gửi hay không
    • is_deleted: có phải tin nhắn đã bị xóa khỏi Gmail hay không
    • last_indexed: thời điểm đồng bộ gần nhất
  • Có thể thống kê số email theo từng người gửi
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Có thể thống kê email chưa đọc theo từng người gửi để xác định những người gửi gửi nhiều email không gây hứng thú
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE is_read = 0
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Có thể dùng strftime để thống kê số email theo năm, tháng, ngày, thứ trong tuần hoặc giờ
SELECT strftime('%Y', timestamp) AS period, COUNT(*) AS count
FROM messages
GROUP BY period
ORDER BY count DESC
  • Có thể tìm các thư có chứa newsletter hoặc unsubscribe trong nội dung để nhóm newsletter theo từng người gửi
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE body LIKE '%newsletter%' OR body LIKE '%unsubscribe%'
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Có thể xem tổng kích thước thư theo từng người gửi và xác định những người gửi email lớn theo đơn vị MB
SELECT sender->>'$.email', sum(size)/1024/1024 AS size
FROM messages
GROUP BY sender->>'$.email'
ORDER BY size DESC
  • Có thể tính số thư tự gửi cho chính mình bằng JSON recipients và điều kiện email trong 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'
  • Có thể xem thứ tự người gửi có tổng dung lượng thư nhận được lớn nhất
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
  • Có thể truy vấn các tin nhắn đã xóa với điều kiện is_deleted=1
SELECT message_id, subject, timestamp
FROM messages
WHERE is_deleted=1
ORDER BY timestamp DESC

Bài viết liên quan

1 bình luận

 
GN⁺ 2025-05-11
Ý kiến trên Hacker News

  • Tôi thắc mắc vì sao lại tách riêng một số header cụ thể trong schema. Có thể đặt recipients, subject, sender thành các trường JSON, nhưng cũng có thể gom tất cả vào một headers và chứa cả những header còn lại của message
    Nếu vì hiệu năng, có thể để headers là một JSON blob duy nhất và tạo các trường cần thiết dưới dạng cột sinh ra. Ví dụ subject có thể được tạo bằng json_extract("headers", '$.Subject') rồi đánh index
    Mô hình này khá mạnh vì người dùng có thể dùng ALTER TABLE để thêm các cột sinh ra đã được đánh index cần cho truy vấn của họ. Trạng thái DKIM cũng có thể lấy từ "Dkim-Signature", tạo cột và index rồi GROUP BY

    • Thực ra thậm chí không cần cột sinh ra; SQLite hỗ trợ index biểu thức. Ví dụ tạo như CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject')) thì index sẽ được dùng ở những chỗ tham chiếu đến biểu thức đó
      Sau khi tạo index kiểu này, việc tạo một VIEW dùng biểu thức đó thường hữu ích hơn là ALTER bảng chính để thêm cột sinh ra
    • Thêm index cho một truy vấn dùng một lần trông giống một thói quen xấu
      Thông thường tôi thích tách riêng các cột sẽ được dùng đều đặn. Với một đối tượng ổn định như header email thì càng như vậy; dù cột headers có thể làm việc thay đổi schema dễ hơn một chút, về bản chất nó chỉ chuyển nỗi đau lúc ghi sang nỗi đau lúc đọc, và còn để ngỏ khả năng lỗi âm thầm
    • Khi mở rộng hệ thống với PostgreSQL, tôi thường dùng một pattern tương tự. Ban đầu tạo bảng dựa trên các trường mình nghĩ là cần, còn metadata dư thì để trong một cột JSON
      Sau khoảng hai tháng, khi thấy các trường thực sự cần, tôi sẽ điền chúng từ JSON và để API tiếp tục cập nhật cho mới, hoặc tạo view. Cách này khá hữu ích để tránh những cơn đau tăng trưởng kiểu “cứ nhét hết vào MongoDB đi” hay “cứ để trong filesystem đi”, mà chi phí cũng không lớn
    • Cột dkim được định nghĩa là NOT NULL, nên tôi thắc mắc chuyện gì xảy ra nếu email message không có header Dkim-Signature

  • Vài năm trước tôi đã làm một công cụ trực quan hóa email số lượng lớn kiểu Gmail: https://github.com/terhechte/postsack

    • Khá hay. Nó giống công cụ trực quan hóa mức dùng đĩa, nhưng có vẻ tập trung vào tổng lượng mail hơn là dung lượng đĩa
      Tôi thắc mắc có tùy chọn theo kích thước không. Tôi muốn xem người gửi nào chiếm nhiều dung lượng lưu trữ của mình nhất. Và chứng chỉ SSL của website đã hết hạn
    • Trông thú vị. Link gmvault trong README giờ đã chết, không biết đây có phải link đúng không: https://github.com/gaubert/gmvault
    • Trông thú vị. Trước đây tôi từng tự thử làm thứ tương tự bằng qdirstat, nhưng phải sắp xếp email theo một cách cụ thể như thư mục ngày tháng, và khó cắt lại theo tiêu chí khác
      Ngược lại, file cache của qdirstat lại dễ tạo, nên có thể dùng để trực quan hóa nhiều đối tượng trông như file

  • Thật đáng tiếc là giờ ngay cả mật khẩu dành riêng cho ứng dụng cũng không đăng nhập được nữa; phải tạo OAuth client và đi qua luồng OAuth. Dù là email của tôi, Google đã lấy đi chuẩn mở để tôi có thể truy cập

    • Nhìn vào lượng spam nhận được ở địa chỉ Gmail miễn phí, và lượng spam đến các tài khoản không phải Gmail từ máy chủ Gmail, tôi ngày càng nghiêng về hướng thoát khỏi Google
      Đặc biệt, tôi ngày càng nhận được nhiều thông tin rằng email freelance của mình bị hệ thống người nhận đưa vào spam. Tuy vậy, tôi vẫn chưa biết phải bỏ những thói quen đã quen trong hệ sinh thái Google thế nào
    • Tôi thắc mắc vì sao bạn xem mật khẩu dành riêng cho ứng dụng là chuẩn mở còn OAuth thì không
    • Tôi không rõ bạn muốn nói gì, vì dùng mật khẩu ứng dụng thì có thể truy cập toàn bộ IMAP

  • Gần đây tôi đã cố tích hợp Gmail vào ứng dụng của mình https://github.com/rumca-js/Django-link-archive và tốn quá nhiều thời gian, nên kết luận rằng hỗ trợ Gmail không đáng
    Gmail to SQLite mô tả việc thiết lập thông tin xác thực trong 6 bước, nhưng trường hợp của tôi thì không như vậy. Sau 6 bước, Google vẫn nói ứng dụng chưa được xuất bản nên phải xuất bản; rồi nói vì tôi không phải người dùng Workspace nên không thể để ứng dụng nội bộ; khi chuyển sang ứng dụng bên ngoài thì lại nói trước khi được xác minh thì không thể dùng
    Trong quá trình xác minh, họ yêu cầu domain, địa chỉ, các chi tiết khác, giải thích lý do dùng phạm vi quyền, thậm chí cả video mô tả cách sử dụng ứng dụng, rồi nói sẽ mất thời gian để xác minh dữ liệu đã gửi. Toàn bộ giống như một mê cung thiết lập, và bắt người dùng vượt qua các chướng ngại mà Google yêu cầu như vậy là quá mức

    • Những thủ tục mà Google bắt mọi người làm chỉ để lấy một API key đúng là hoàn toàn vô lý. Không biết có ai biết vì sao nó lại tệ đến mức này không
    • Cứ dùng IMAP kiểu cũ và mật khẩu ứng dụng là được. Không cần nhảy qua các vòng cản trở của Google

  • Tôi tò mò không biết hiện nay phần mềm sao lưu Gmail mã nguồn mở tốt nhất là gì. Cũng muốn biết có ai từng thiết lập thứ như thế này, bao gồm cả lưu trữ tệp đính kèm chưa

    • Có https://github.com/GAM-team/got-your-back. Đây là mã nguồn mở và có chức năng tiếp tục, nên việc sao lưu và khôi phục rốt cuộc sẽ hoàn tất
      Nhân tiện cũng có https://www.mailstore.com/en/products/mailstore-home/. Không phải mã nguồn mở, nhưng vì là GUI có chỉ mục nên rất tốt để tìm kiếm mail cục bộ; chức năng tiếp tục chỉ áp dụng cho sao lưu, nên khôi phục dữ liệu lớn thường thất bại
    • Có thể không đúng chính xác câu trả lời bạn muốn, nhưng Google có một dịch vụ tên là Takeout, cho phép yêu cầu và tải xuống bản sao lưu dữ liệu của mọi dịch vụ Google, bao gồm Gmail
      Tôi đặt nhắc nhở để chạy cái này vài tháng một lần và cập nhật bản sao lưu cục bộ. Theo tôi nhớ thì tải về dưới dạng tệp mbox được nén gzip
    • Nếu dùng một IMAP client và đặt ở chế độ ngoại tuyến/tải xuống, bạn cũng có thể tải toàn bộ về và lưu cục bộ. Trong Evolution hình như gọi là “chế độ ngoại tuyến”, còn trong Thunderbird hoặc các client khác có thể có tên khác

  • Tôi nghĩ cái này nên được gọi là “Gmail to SQLite” chứ không phải kiểu “IMAP to SQLite”. Không hiểu sao lại gắn với một nhà cung cấp email cụ thể

    • Vì thực tế nó dành riêng cho Gmail. Nó dùng OAuth và có lẽ cả quyền truy cập API
      IMAP khó hơn nhiều, chậm hơn nhiều, và còn bị ràng buộc bởi giới hạn băng thông của Google
    • Tôi đã cố sao lưu tài khoản Gmail bằng IMAP trong nhiều năm, nhưng chưa từng thành công, kể cả với các công cụ dành riêng cho Gmail. Ngay cả công cụ đồng bộ tốt nhất cũng chạy suốt một tháng rồi bị kẹt ở điểm không lấy được một email cụ thể
      Không biết có phải vì nó nằm trong kho lưu trữ quá “lạnh” nên bị timeout hay không. Vì vậy tôi hiểu rằng dùng API độc quyền của Google có thể hoạt động tốt hơn
      Dạo này Google Takeout có bao gồm mbox, hoạt động đúng và khá nhanh, nhưng không cập nhật liên tục được. Cuối cùng tôi đã chuyển sang một nhà cung cấp mail khác là Infomaniak, và biết ơn bản thân trước đây đã dùng miền mail riêng

  • Sẽ hay hơn nếu cũng bật được tìm kiếm toàn văn

    • Với một thứ do công ty tìm kiếm vận hành, tôi thấy tìm kiếm toàn văn của Gmail tệ đến đáng ngạc nhiên

  • Hôm qua tôi cũng đã làm một thứ tương tự. Vì tôi muốn liệt kê email người nhận theo từng domain. Code thì lộn xộn, nhưng ở đây: https://github.com/hugoferreira/gmail-sqlite-db

  • Hơi gợi nhớ đến Archiveopteryx, máy chủ IMAP dựa trên PostgreSQL: https://github.com/aox/aox
    Schema của AOX lúc nào cũng trông có vẻ hay, nhưng tôi chưa từng thật sự dùng nó cho ra trò. Mục đích chính là phân tích và tìm kiếm mail, chứ không phải làm máy chủ IMAP dùng hằng ngày

    • Cũng nhớ đến Manitou-Mail. Đây là mail client chuyên dụng dựa trên PostgreSQL mạnh mẽ, có thể dùng hằng ngày và khá vững chắc: https://www.manitou-mail.org/

  • Tôi tò mò chi phí băng thông ở đây sẽ thế nào. Với tư cách người có tài khoản Gmail hơn 40GB, tôi muốn biết liệu chuyển qua công cụ này có bị tính phí không
    Dễ sửa thôi. Google Takeout có vẻ miễn phí, nên cứ tải xuống trước rồi parse tệp là được. Dù vậy, xét về việc bắt đầu ngay thì công cụ này có vẻ nhanh hơn