บันทึก Gmail ลงใน SQLite

 (github.com/marcboeker)
2 คะแนน โดย GN⁺ 2025-05-11 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • Gmail to SQLite เป็นแอปพลิเคชัน Python ที่ซิงก์ข้อความ Gmail ไปยัง ฐานข้อมูล SQLite บนเครื่อง เพื่อนำไปใช้ในการวิเคราะห์และจัดเก็บถาวร
  • การทำงานปกติคือ การซิงก์แบบเพิ่มเฉพาะส่วน ที่จะดาวน์โหลดเฉพาะข้อความใหม่ และมีตัวเลือกซิงก์ทั้งหมดเพื่อดาวน์โหลดทุกข้อความและตรวจจับได้ว่ามีการลบหรือไม่
  • การดึงข้อความใช้ การประมวลผลแบบขนานหลายเธรด และมีการจัดการข้อผิดพลาดและการปิดโปรแกรม เช่น การลองใหม่อัตโนมัติด้วย exponential backoff และการจัดการ CTRL+C
  • การใช้งานต้องมี Python 3.8 ขึ้นไป, Google Cloud Project ที่เปิดใช้ Gmail API และไฟล์ OAuth 2.0 credentials.json
  • ข้อมูลที่บันทึกไว้ประกอบด้วยผู้ส่ง ผู้รับ ป้ายกำกับ เนื้อหา ขนาด สถานะการอ่าน สถานะส่งออก สถานะการลบ ฯลฯ ทำให้สามารถวิเคราะห์รูปแบบการใช้งาน Gmail ได้โดยตรงด้วย SQL

เครื่องมือซิงก์ข้อความ Gmail แบบโลคัล

  • Gmail to SQLite เป็นแอปพลิเคชัน Python ที่เก็บข้อความ Gmail ลงในฐานข้อมูล SQLite บนเครื่อง
  • จุดประสงค์คือทำให้สามารถ วิเคราะห์ และ จัดเก็บถาวร ข้อมูล Gmail ได้
  • มีการใส่ type hints ทั่วทั้งโค้ดเบสเพื่อให้มี ความปลอดภัยด้านชนิดข้อมูล

วิธีซิงก์และความเสถียร

  • การซิงก์เริ่มต้นทำงานแบบ ซิงก์เพิ่มเฉพาะส่วน และจะดาวน์โหลดเฉพาะข้อความใหม่
  • หากใช้ตัวเลือก --full-sync จะซิงก์ข้อความทั้งหมดและตรวจจับข้อความที่ถูกลบจาก Gmail
  • การดึงข้อความทำด้วย การประมวลผลแบบขนานหลายเธรด เพื่อเพิ่มประสิทธิภาพ
  • การจัดการข้อผิดพลาดมีการลองใหม่อัตโนมัติและ exponential backoff
  • เมื่อกด CTRL+C จะเข้าสู่ขั้นตอนปิดอย่างปลอดภัย
    • หยุดรับงานใหม่
    • รอให้งานที่กำลังทำอยู่เสร็จ
    • บันทึกสถานะความคืบหน้าของงานที่เสร็จแล้ว
    • ปิดโปรแกรมตามปกติ
  • หากกด CTRL+C อีกครั้งจะออกทันที

การติดตั้งและเงื่อนไขที่ต้องเตรียม

  • สภาพแวดล้อมสำหรับใช้งานต้องมี Python 3.8 ขึ้นไป
  • ต้องมี Google Cloud Project ที่เปิดใช้ Gmail API
  • ต้องมีไฟล์ยืนยันตัวตน OAuth 2.0 credentials.json อยู่ที่รูทของโปรเจ็กต์
  • ขั้นตอนติดตั้งคือโคลนรีโพซิทอรีแล้วติดตั้ง dependency ด้วย uv sync
  • การตั้งค่าการยืนยันตัวตนของ Gmail API ทำได้โดยสร้างหรือเลือกโปรเจ็กต์ใน Google Cloud Console, เปิดใช้ Gmail API, จากนั้นสร้าง OAuth 2.0 credentials สำหรับ Desktop application แล้วบันทึกเป็น credentials.json

วิธีใช้คำสั่ง

  • การซิงก์แบบเพิ่มเฉพาะส่วนตามปกติรันได้ดังนี้
python main.py sync --data-dir ./data

# or: uv run main.py sync --data-dir ./data
  • การซิงก์ทั้งหมดและตรวจจับการลบใช้ --full-sync
python main.py sync --data-dir ./data --full-sync
  • หากต้องการซิงก์เฉพาะข้อความใดข้อความหนึ่ง ให้ใช้ sync-message และ --message-id
python main.py sync-message --data-dir ./data --message-id MESSAGE_ID
  • หากต้องการตรวจจับและแสดงเฉพาะข้อความที่ถูกลบ ให้ใช้ sync-deleted-messages
python main.py sync-deleted-messages --data-dir ./data
  • สามารถกำหนดจำนวน worker threads ด้วย --workers โดยค่าปริยายคือจำนวนคอร์ของ CPU
python main.py sync --data-dir ./data --workers 8
  • อาร์กิวเมนต์บรรทัดคำสั่งมีดังนี้
    • command: จำเป็น และต้องเป็นหนึ่งใน sync, sync-message, sync-deleted-messages
    • --data-dir: จำเป็น คือไดเรกทอรีที่จะเก็บฐานข้อมูล SQLite
    • --full-sync: ตัวเลือกเพิ่มเติม เพื่อบังคับซิงก์ทั้งหมด
    • --message-id: จำเป็นสำหรับ sync-message คือ ID ของข้อความที่จะซิงก์
    • --workers: ตัวเลือกเพิ่มเติม คือจำนวน worker threads
    • --help: แสดงคำอธิบายคำสั่งและตัวเลือก

สคีมาของ SQLite และตัวอย่างการวิเคราะห์

  • ตาราง messages ในฐานข้อมูล SQLite ที่สร้างขึ้นมีฟิลด์ที่จำเป็นสำหรับการวิเคราะห์ข้อความ Gmail
    • message_id: ID ข้อความ Gmail แบบไม่ซ้ำกัน
    • thread_id: ID เธรดของ Gmail
    • sender: ข้อมูลผู้ส่งแบบ JSON ที่มีชื่อและอีเมล
    • recipients: JSON ของผู้รับแยกตามประเภท to, cc, bcc
    • labels: อาร์เรย์ของป้ายกำกับ Gmail
    • subject: หัวข้อข้อความ
    • body: เนื้อหาข้อความแบบ plain text
    • size: ขนาดข้อความเป็นไบต์
    • timestamp: เวลาของข้อความ
    • is_read: สถานะการอ่าน
    • is_outgoing: เป็นข้อความที่ผู้ใช้ส่งหรือไม่
    • is_deleted: เป็นข้อความที่ถูกลบจาก Gmail หรือไม่
    • last_indexed: เวลาที่ซิงก์ล่าสุด
  • สามารถสรุปจำนวนอีเมลตามผู้ส่งได้
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • สามารถนับอีเมลที่ยังไม่ได้อ่านตามผู้ส่ง เพื่อดูว่าผู้ส่งรายใดส่งอีเมลที่ไม่น่าสนใจจำนวนมาก
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE is_read = 0
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • สามารถใช้ strftime เพื่อสรุปจำนวนอีเมลตามปี เดือน วัน วันในสัปดาห์ หรือชั่วโมง
SELECT strftime('%Y', timestamp) AS period, COUNT(*) AS count
FROM messages
GROUP BY period
ORDER BY count DESC
  • สามารถค้นหาเมลที่มี newsletter หรือ unsubscribe อยู่ในเนื้อหา แล้วจัดกลุ่มจดหมายข่าวตามผู้ส่ง
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE body LIKE '%newsletter%' OR body LIKE '%unsubscribe%'
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • สามารถดูขนาดรวมของอีเมลตามผู้ส่ง และดูว่าผู้ส่งรายใดส่งอีเมลขนาดใหญ่ในหน่วย MB
SELECT sender->>'$.email', sum(size)/1024/1024 AS size
FROM messages
GROUP BY sender->>'$.email'
ORDER BY size DESC
  • สามารถคำนวณจำนวนเมลที่ส่งถึงตัวเองได้จาก recipients JSON และเงื่อนไขอีเมลใน 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'
  • สามารถดูเรียงลำดับตามขนาดรวมของเมลที่ได้รับจากผู้ส่งแต่ละราย
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
  • ข้อความที่ถูกลบสามารถค้นหาได้ด้วยเงื่อนไข is_deleted=1
SELECT message_id, subject, timestamp
FROM messages
WHERE is_deleted=1
ORDER BY timestamp DESC

บทความที่เกี่ยวข้อง

1 ความคิดเห็น

 
GN⁺ 2025-05-11
ความคิดเห็นจาก Hacker News

  • สงสัยว่าทำไมถึงแยกบางเฮดเดอร์ออกมาต่างหากในสคีมา recipients, subject, sender อาจใส่ไว้เป็นฟิลด์ JSON ก็ได้ แต่ก็อาจใส่ทั้งหมดไว้ใน headers เดียว รวมถึงเฮดเดอร์ที่เหลือของข้อความด้วย
    ถ้าเป็นเพราะประสิทธิภาพ ก็ให้ headers เป็น JSON blob เดียว แล้วทำฟิลด์ที่ต้องการเป็น คอลัมน์ที่สร้างขึ้น ได้ เช่น subject สร้างด้วย json_extract("headers", '$.Subject') แล้วทำดัชนีได้
    โมเดลนี้ทรงพลังเพราะผู้ใช้สามารถเพิ่มคอลัมน์ที่สร้างขึ้นและมีดัชนีสำหรับคิวรีของตัวเองด้วย ALTER TABLE ได้ สถานะ DKIM ก็ทำได้โดยดึง "Dkim-Signature" ออกมาทำเป็นคอลัมน์และดัชนี จากนั้น GROUP BY

    • จริง ๆ แล้วไม่จำเป็นต้องใช้คอลัมน์ที่สร้างขึ้นด้วยซ้ำ เพราะ SQLite รองรับ expression index เช่น สร้างแบบ CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject')) แล้วตำแหน่งที่อ้างถึงนิพจน์นั้นก็จะใช้ดัชนีได้
      หลังจากสร้างดัชนีแบบนี้แล้ว การสร้าง VIEW ที่ใช้นิพจน์นั้นมีประโยชน์กว่าการ ALTER ตารางหลักเพื่อเพิ่มคอลัมน์ที่สร้างขึ้น
    • การเพิ่มดัชนีเพื่อคิวรีแบบใช้ครั้งเดียวดูเหมือนเป็นนิสัยที่ไม่ดี
      โดยทั่วไปผมชอบแยกคอลัมน์ที่จะถูกใช้อย่างต่อเนื่องออกมาต่างหากมากกว่า ยิ่งถ้าเป็นสิ่งที่เสถียรอย่างเฮดเดอร์อีเมลก็ยิ่งใช่ และแม้คอลัมน์ headers จะทำให้การเปลี่ยนสคีมาง่ายขึ้นเล็กน้อย แต่ก็เหมือนย้ายความเจ็บปวดตอนเขียนไปเป็นความเจ็บปวดตอนอ่าน และยังเหลือช่องให้ล้มเหลวแบบเงียบ ๆ ได้
    • ตอนขยายระบบด้วย PostgreSQL มักใช้แพตเทิร์นคล้าย ๆ กันบ่อย ๆ ตอนแรกก็คิดถึงฟิลด์ที่รู้ว่าจำเป็นแล้วสร้างตาราง ส่วน เมทาดาต้า ที่เหลือใส่ไว้ในคอลัมน์ JSON
      ผ่านไปสักสองเดือน พอเห็นว่าฟิลด์ไหนจำเป็นจริง ๆ ก็เติมจาก JSON แล้วให้ API คอยอัปเดตให้เป็นปัจจุบันต่อไป หรือไม่ก็สร้างวิว วิธีนี้ช่วยหลีกเลี่ยงปัญหาการเติบโตจากแนวคิด “ก็ใส่ทั้งหมดลง MongoDB ไปเลย” หรือ “ก็วางไว้ใน file system ไปเลย” ได้ค่อนข้างดี และต้นทุนก็ไม่มาก
    • เห็นนิยามคอลัมน์ dkim เป็น NOT NULL เลยสงสัยว่าถ้าข้อความอีเมลไม่มีเฮดเดอร์ Dkim-Signature จะเกิดอะไรขึ้น

  • เมื่อหลายปีก่อนเคยทำเครื่องมือ แสดงภาพอีเมลจำนวนมาก แบบ Gmail: https://github.com/terhechte/postsack

    • ค่อนข้างเจ๋ง คล้ายเครื่องมือแสดงภาพการใช้ดิสก์ แต่ดูเหมือนจะเน้นที่ ปริมาณรวม ของเมลมากกว่าการใช้ดิสก์
      สงสัยว่ามีตัวเลือกดูตามขนาดด้วยไหม อยากดูว่าผู้ส่งคนไหนใช้พื้นที่เก็บข้อมูลของผมมากที่สุด และใบรับรอง SSL ของเว็บไซต์หมดอายุแล้ว
    • ดูน่าสนใจ ลิงก์ gmvault ใน README ตอนนี้เสียแล้ว สงสัยว่าอันนี้ใช่ไหม: https://github.com/gaubert/gmvault
    • ดูน่าสนใจ เมื่อก่อนเคยลองทำอะไรคล้าย ๆ กันเองด้วย qdirstat แต่ต้องจัดวางอีเมลในรูปแบบเฉพาะ เช่น โฟลเดอร์ตามวันที่ และยากที่จะตัดแบ่งใหม่ตามเกณฑ์อื่น
      ในทางกลับกัน ไฟล์แคช ของ qdirstat สร้างได้ง่าย จึงใช้แสดงภาพหลายสิ่งที่หน้าตาเหมือนไฟล์ได้

  • ตอนนี้น่าเสียดายมากที่แม้แต่รหัสผ่านเฉพาะแอปก็ใช้ล็อกอินไม่ได้แล้ว ต้องสร้าง OAuth client และผ่านโฟลว์ OAuth ทั้งที่เป็นอีเมลของผมเอง แต่ Google กลับพรากมาตรฐานเปิดสำหรับให้ผมเข้าถึงมันไป

    • ดูจากปริมาณสแปมที่ได้รับจากที่อยู่ Gmail ฟรี และปริมาณสแปมที่เข้ามายังบัญชีที่ไม่ใช่ Gmail ผ่านเซิร์ฟเวอร์ Gmail ก็ยิ่งเอนเอียงไปทาง เลิกพึ่ง Google มากขึ้น
      โดยเฉพาะอย่างยิ่ง ผมได้รับข้อมูลมากขึ้นเรื่อย ๆ ว่าอีเมลฟรีแลนซ์ของผมไปตกอยู่ในสแปมในระบบของผู้รับ แต่ก็ยังมืดแปดด้านว่าจะเลิกนิสัยที่คุ้นเคยกับระบบนิเวศของ Google ได้อย่างไร
    • สงสัยว่าทำไมถึงมองว่ารหัสผ่านเฉพาะแอปเป็นมาตรฐานเปิด แต่ OAuth ไม่ใช่
    • ผมไม่ค่อยเข้าใจว่าต้องการจะสื่ออะไร ในเมื่อใช้รหัสผ่านแอปแล้วจะได้ สิทธิ์เข้าถึง IMAP ทั้งหมด

  • เมื่อเร็ว ๆ นี้ผมพยายามรวม Gmail เข้ากับแอปของผม https://github.com/rumca-js/Django-link-archive แต่เสียเวลาไปมากเกินไป และตัดสินใจว่าการรองรับ Gmail ไม่คุ้มค่า
    Gmail to SQLite อธิบายการตั้งค่าข้อมูลรับรองไว้ 6 ขั้นตอน แต่ในกรณีของผมไม่ใช่แบบนั้น หลังจาก 6 ขั้นตอนแล้ว Google ยังบอกว่าแอปยังไม่ได้เผยแพร่ ต้องเผยแพร่ก่อน และบอกว่าถ้าไม่ใช่ผู้ใช้ Workspace ก็ปล่อยเป็นแอปภายในไม่ได้ พอเปลี่ยนเป็นแอปภายนอกก็บอกว่าใช้ไม่ได้ก่อนผ่านการตรวจสอบ
    ในกระบวนการตรวจสอบยังเรียกร้องโดเมน ที่อยู่ รายละเอียดอื่น ๆ เหตุผลประกอบการขอ scope และแม้แต่วิดีโออธิบายวิธีใช้แอป แล้วยังบอกว่าต้องใช้เวลาเพื่อตรวจสอบข้อมูลที่ส่งไป ทั้งหมดเหมือนเขาวงกตการตั้งค่า และการให้ผู้ใช้ต้องกระโดดข้ามอุปสรรคที่ Google บังคับนั้นมากเกินไป

    • ขั้นตอนที่ Google ให้คนทำแม้แค่เพื่อขอ API key สักอันนั้นไร้เหตุผลโดยสิ้นเชิง สงสัยว่ามีใครรู้ไหมว่าทำไมมันถึงแย่ได้ขนาดนี้
    • ก็ใช้ IMAP แบบเก่ากับรหัสผ่านแอปไปเลย ไม่จำเป็นต้องกระโดดข้ามอุปสรรคของ Google

  • อยากรู้ว่าซอฟต์แวร์ สำรองข้อมูล Gmail แบบโอเพนซอร์สที่ดีที่สุดที่มีอยู่ตอนนี้คืออะไร รวมถึงการเก็บไฟล์แนบด้วย มีใครเคยตั้งค่าอะไรแบบนี้บ้างไหม

    • มี https://github.com/GAM-team/got-your-back เป็นโอเพนซอร์ส และมีฟังก์ชัน resume ทำให้การสำรองและกู้คืนข้อมูลเสร็จได้ในที่สุด
      อนึ่ง ยังมี https://www.mailstore.com/en/products/mailstore-home/ ด้วย ไม่ใช่โอเพนซอร์ส แต่เป็น GUI ที่มีดัชนี จึงเหมาะกับการค้นหาเมลในเครื่อง และ resume ได้เฉพาะตอนสำรองข้อมูล ทำให้การกู้คืนข้อมูลขนาดใหญ่มักล้มเหลว
    • อาจไม่ใช่คำตอบที่ตรงตามที่ต้องการเป๊ะ ๆ แต่ Google มีบริการชื่อ Takeout ที่ให้ขอและดาวน์โหลดข้อมูลสำรองของบริการ Google ทั้งหมด รวมถึง Gmail ได้
      ผมตั้งการแจ้งเตือนให้รันสิ่งนี้ทุก ๆ สองสามเดือน แล้วอัปเดตแบ็กอัปในเครื่อง เท่าที่จำได้จะดาวน์โหลดมาเป็นไฟล์ mbox ที่บีบอัดด้วย gzip
    • ถ้าใช้ไคลเอนต์ IMAP แล้วตั้งเป็นโหมดออฟไลน์/ดาวน์โหลด ก็สามารถดาวน์โหลดทั้งหมดมาเก็บไว้ในเครื่องได้เหมือนกัน ใน Evolution ดูเหมือนจะเรียกว่า “โหมดออฟไลน์” แต่ใน Thunderbird หรือไคลเอนต์อื่น ๆ อาจใช้ชื่อไม่เหมือนกัน

  • ผมคิดว่าสิ่งนี้ควรชื่อประมาณ “IMAP to SQLite” มากกว่า “Gmail to SQLite” ไม่ใช่หรือ ไม่เข้าใจว่าทำไมต้องผูกกับผู้ให้บริการอีเมลเจ้าเดียวโดยเฉพาะ

    • เพราะจริง ๆ แล้วมันเป็นเครื่องมือ เฉพาะ Gmail นั่นเอง ใช้ OAuth และน่าจะใช้การเข้าถึงผ่าน API
      IMAP ยากกว่ามาก ช้ากว่ามาก และยังติดข้อจำกัดแบนด์วิดท์ของ Google ด้วย
    • ผมพยายามสำรองบัญชี Gmail ผ่าน IMAP มาหลายปีแล้ว แต่ไม่เคยสำเร็จเลย รวมถึงเครื่องมือเฉพาะ Gmail ด้วย เครื่องมือซิงก์ที่ดีที่สุดยังรันอยู่หนึ่งเดือนแล้วไปติดตรงจุดที่ดึงเมลบางฉบับไม่ได้
      ไม่รู้ว่าอยู่ในสตอเรจที่เย็นเกินไปจน timeout หรือเปล่า ดังนั้นจึงเข้าใจได้ว่าการใช้ API แบบ proprietary ของ Google อาจทำงานได้ดีกว่า
      ทุกวันนี้ Google Takeout มี mbox รวมมาให้ ทำงานได้ถูกต้อง และค่อนข้างเร็ว แต่ไม่ได้อัปเดตต่อเนื่อง สุดท้ายผมย้ายไปใช้ Infomaniak ซึ่งเป็นผู้ให้บริการเมลรายอื่น และรู้สึกขอบคุณตัวเองในอดีตที่เคยใช้ โดเมนอีเมล ของตัวเองไว้

  • ถ้าเปิดใช้ การค้นหาแบบเต็มข้อความ ได้ด้วยก็คงดี

    • สำหรับสิ่งที่ดำเนินการโดยบริษัทค้นหา ผมรู้สึกว่าการค้นหาแบบเต็มข้อความของ Gmail แย่อย่างน่าประหลาดใจ

  • เมื่อวานผมก็ทำของแบบเดียวกัน เพราะอยาก แสดงรายชื่ออีเมลผู้รับตามโดเมน โค้ดเละเทะ แต่ดูได้ที่นี่: https://github.com/hugoferreira/gmail-sqlite-db

  • ทำให้นึกถึง Archiveopteryx เซิร์ฟเวอร์ IMAP ที่ใช้ PostgreSQL นิดหน่อย: https://github.com/aox/aox
    สคีมาของ AOX ดูดีเสมอ แต่ไม่เคยได้ลองใช้งานจริงอย่างจริงจัง กรณีใช้งานหลักของผมคือการวิเคราะห์และค้นหาเมล ไม่ใช่เซิร์ฟเวอร์ IMAP ที่ใช้เป็น daily driver

    • นึกถึง Manitou-Mail ด้วย เป็น ไคลเอนต์เมลเฉพาะทางที่ใช้ PostgreSQL ที่ทรงพลัง ใช้เป็น daily driver ได้ และค่อนข้างแข็งแรง: https://www.manitou-mail.org/

  • อยากรู้ว่าตรงนี้ค่าแบนด์วิดท์คิดยังไง ในฐานะคนที่มี บัญชี Gmail มากกว่า 40GB อยากรู้ว่าถ้าโอนด้วยเครื่องมือนี้จะโดนคิดเงินไหม
    แก้ไม่ยาก เพราะดูเหมือน Google Takeout จะฟรี ก็แค่ดาวน์โหลดมาก่อนแล้วค่อย parse ไฟล์ ถึงอย่างนั้นในแง่ของการเริ่มใช้งานทันที เครื่องมือนี้น่าจะเร็วกว่า