Helps clean up large iMessage attachments by letting you search for them

imessage_cleanup.py

#!/usr/bin/env python3

import pathlib
import sqlite3

if __name__ == "__main__":
	connection = sqlite3.connect(f"{pathlib.Path.home()}/Library/Messages/chat.db")
	for (name, size, chat, date) in connection.execute("""
		SELECT transfer_name, total_bytes, chat_message_join.chat_id, date
		FROM message_attachment_join JOIN message ON message_attachment_join.message_id = message.ROWID
		JOIN attachment ON attachment.ROWID = message_attachment_join.attachment_id
		JOIN chat_message_join ON chat_message_join.message_id = message.ROWID
		ORDER BY total_bytes DESC;
		"""):
		print(f"{name} ({size:,} bytes):")
		query = """
		SELECT text, is_from_me
		FROM chat_message_join JOIN message ON chat_message_join.message_id = message.ROWID
		WHERE chat_id = :chat AND length(text) > 1
		"""
		for (text, sender) in connection.execute(query + " AND date < :date ORDER BY date DESC LIMIT 5;", {"chat": chat, "date": date}):
			print("\t" + ("<- " if sender else "-> ") + text)
		print("\t[Attachment]")
		for (text, sender) in connection.execute(query + " AND date > :date ORDER BY date ASC LIMIT 5;", {"chat": chat, "date": date}):
			print("\t" + ("<- " if sender else "-> ") + text)
		print()