将 Gmail 数据导入 SQLite 数据库

Gmail to SQLite

Source | HN Comments

该项目提供一个脚本，将 Gmail 邮件下载并存储到 SQLite 数据库，方便用户进行数据分析。用户需安装依赖、配置 Google Cloud 项目和 API 凭据。脚本支持同步所有邮件或单个邮件，通过命令行参数控制。数据库包含邮件 ID、发件人、收件人、主题、正文等信息，并提供示例查询，如统计发件人邮件数量、未读邮件数量等。未来计划包括检测已删除邮件。

marcboeker/gmail-to-sqlite

将你的 Gmail 账户索引到 SQLite 数据库中，并进行数据分析。

License

MIT license 108 stars 7 forks

Gmail to SQLite

这是一个从 Gmail 下载邮件并将其存储在 SQLite 数据库中以进行进一步分析的脚本。我发现将所有电子邮件放在数据库中运行查询非常有用。例如，我可以找出每个发件人收到了多少封电子邮件、哪些电子邮件占用的空间最大，以及我从未阅读过哪些发件人的电子邮件。

安装

克隆此存储库: git clone https://github.com/marcboeker/gmail-to-sqlite.git.
安装所需的依赖: pip install -r requirements.txt
在这里创建一个 Google Cloud 项目。
打开 Gmail API & Services 并激活 Gmail API。
打开 OAuth consent screen 并创建一个新的 consent screen。您只需要提供名称和联系方式。
接下来打开 Create OAuth client ID 并为 Desktop app 创建凭据。下载凭据文件并将其保存在此存储库的根目录下的 credentials.json 中。

这是一个关于如何创建凭据的详细指南：https://developers.google.com/gmail/api/quickstart/python#set_up_your_environment。

用法

同步所有邮件

运行脚本: python main.py sync --data-dir path/to/your/data 其中 --<data-dir> 是所有数据存储的路径。这将在 <data-dir>/messages.db 中创建一个 SQLite 数据库，并将用户凭据存储在 <data-dir>/credentials.json 下。
脚本完成后，您可以使用例如 sqlite3 命令行工具查询数据库：sqlite3 <data-dir>/messages.db。
您可以再次运行脚本以同步所有新邮件。提供 --full-sync 强制执行完全同步。但是，这只会更新现有消息的读取状态、标签和上次索引时间戳。

同步单个邮件

python main.py sync-message --data-dir path/to/your/data --message-id <message-id>

命令行参数

usage: main.py [-h] [--data-dir DATA_DIR] [--update] {sync, sync-message}
Main commands:
sync          Sync emails from Gmail to the database.
sync-message      Sync a single message from Gmail to the database.
--data-dir DATA_DIR   Path to the directory where all data is stored.
--full-sync       Force a full sync.
--message-id MESSAGE_ID Sync only the message with the given message id.

Schema

CREATE TABLE IF NOT EXISTS "messages" (
  "id" INTEGER NOT NULL PRIMARY KEY, -- internal id
  "message_id" TEXT NOT NULL, -- Gmail message id
  "thread_id" TEXT NOT NULL, -- Gmail thread id
  "sender" JSON NOT NULL, -- Sender as JSON in the form {"name": "Foo Bar", "email": "foo@example.com"}
  "recipients" JSON NOT NULL, -- JSON object: {
   -- "to": [{"email": "foo@example.com", "name": "Foo Bar"}, ...],
   -- "cc": [{"email": "foo@example.com", "name": "Foo Bar"}, ...],
   -- "bcc": [{"email": "foo@example.com", "name": "Foo Bar"}, ...]
  --}
  "labels" JSON NOT NULL, -- JSON array: ["INBOX", "UNREAD", ...]
  "subject" TEXT NOT NULL, -- Subject of the email
  "body" TEXT NOT NULL, -- Extracted body either als HTML or plain text
  "size" INTEGER NOT NULL, -- Size reported by Gmail
  "timestamp" DATETIME NOT NULL, -- When the email was sent/received
  "is_read" INTEGER NOT NULL, -- 0=Unread, 1=Read
  "is_outgoing" INTEGER NOT NULL, -- 0=Incoming, 1=Outgoing
  "last_indexed" DATETIME NOT NULL -- Timestamp when the email was last seen on the server
);

示例查询

获取每个发件人的电子邮件数量

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

获取特定时间段内的电子邮件数量

For years: strftime('%Y', timestamp)
For months in a year: strftime('%m', timestamp)
For days in a month: strftime('%d', timestamp)
For weekdays: strftime('%w', timestamp)
For hours in a day: strftime('%H', timestamp)

SELECT strftime('%Y', timestamp) AS period, COUNT(*) AS count
FROM messages
GROUP BY period
ORDER BY count DESC

查找所有新闻通讯并按发件人分组

这是一种业余方式来查找所有新闻通讯并按发件人分组。这并不完美，但这是一个开始。你也可以使用

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

计算我发送给自己的电子邮件的数量

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

路线图

检测已删除的电子邮件并在数据库中将其标记为已删除。

关于

将你的 Gmail 账户索引到 SQLite 数据库中，并进行数据分析。