Save data from Instagram takeout to a SQLite database

Overview

instagram-to-sqlite

Save data from a Instagram takeout to a SQLite database.

Mise En Place

git clone https://github.com/gavindsouza/instagram-to-sqlite
pip install -e ./instagram-to-sqlite

This tool only supports JSON data takeouts.

Ricing the potatoes

$ instagram-to-sqlite chats insta-chats.db ~/Downloads/takeout-20190530.zip

This will create a database file called insta-chats.db if one does not already exist.

Serving with the steak

Once you have imported Instagram data into a SQLite database file you can browse your data using Datasette. Install Datasette like so:

$ pip install datasette

Next run

datasette insta-chats.db -o

If you're new to SQL but still want to see what you could do with this, then

  1. Find out what was the first message ever sent on any of your instagram chat rooms*
SELECT
    chat_room "Room", sender_name "Sender", coalesce(content, share, photos, videos, users, audio_files) "Message"
FROM
    chats_messages
GROUP BY
    chat_room
HAVING
    min(timestamp_ms)
ORDER BY
    timestamp_ms

Chat rooms refer to any regular, cross-platform or group chat.

  1. Awhhgee, how about the second messages? A bit unrealistic but still...maybe you really have to KNOW
WITH ordered_messages
     AS (SELECT *,
                Row_number()
                  OVER (
                    partition BY chat_room
                    ORDER BY timestamp_ms) AS 'rank'
         FROM   chats_messages
         )
SELECT
    chat_room "Room", sender_name "Sender", coalesce(content, share, photos, videos, users, audio_files) "Message"
FROM
    ordered_messages
WHERE
    rank = 2
ORDER BY
    timestamp_ms ASC
  1. Okay cool, what if I just want to start reading my chats from their inception like a...normal person...?
SELECT
    type, sender_name, DATETIME(ROUND(timestamp_ms / 1000), 'unixepoch') "Date", coalesce(content, share, photos, videos, users, audio_files) "Message"
FROM
    chats_messages
WHERE
    chat_room = '{chat_room}'
ORDER BY
    timestamp_ms

You will have to figure out the chat_room ID you want to query, but it won't be hard to figure that out.

References

  • Read more about datasette in the docs.

  • Checkout the dogsheep project if you're interested in building your personal data warehouse ;)

Pending stuff

This is the rest of the data available in the Instagram takeout that I haven't built import tools for, yet. Currently, only chat data is covered.

other_data = {
    "login_and_account_creation": [
        "login_activity.json", "logout_activity.json",
        "signup_information.json", "password_change_activity.json",
        "account_privacy_changes.json"
    ],
    "shopping": ["recently_viewed_items.json"],
    "comments": ["post_comments.json", "comments_reported.json"],
    "device_information": ["camera_information.json", "devices.json"],
    "ads_and_content": [
        "suggested_accounts_viewed.json", "ads_viewed.json",
        "posts_viewed.json", "videos_watched.json", "ads_clicked.json"
    ],
    "information_about_you": ["account_based_in.json", "ads_interests.json"],
    "likes": ["liked_posts.json", "liked_comments.json"],
    "content": [
        "posts_1.json", "profile_photos.json", "stories.json",
        "archived_posts.json", "other_content.json",
        "recently_deleted_content.json"
    ],
    "your_topics": [
        "your_reels_topics.json", "your_topics.json",
        "your_reels_sentiments.json"
    ],
    "story_sticker_interactions": [
        "emoji_reactions.json", "quizzes.json", "questions.json",
        "emoji_sliders.json", "polls.json"
    ],
    "comments_settings": ["use_cross-app_messaging.json", "comments_allowed_from.json"],
    "recent_searches": ["tag_searches.json", "account_searches.json"],
    "saved": ["saved_collections.json", "saved_posts.json"],
    "followers_and_following": [
        "removed_suggestions.json", "following_hashtags.json",
        "following.json", "followers.json", "recent_follow_requests.json",
        "pending_follow_requests.json", "close_friends.json"
    ],
    "account_information": [
        "account_information.json", "profile_changes.json",
        "personal_information.json"
    ]
}
Owner
gavin
gavin
The Python SDK for the BattleshAPI game

BattleshAPy The Python SDK for the BattleshAPI game Installation This library will be eventually going on PyPI, but until then, simply clone or downlo

Christopher 0 Apr 18, 2022
Telegram 隨機色圖,支援每日自動爬取

Telegram 隨機色圖機器人 使用此原始碼的Bot 開放的隨機色圖機器人: @katonei_bot 已實現的功能 爬取每日R18排行榜 不夠色!再來一張 Tag 索引,指定Tag色圖 將爬取到的色圖轉為 WebP 格式儲存,節省空間 需要注意的事件 好久之前的怪東西,代碼質量不保證 請在使用A

cluckbird 15 Oct 18, 2021
Модуль для создания скриптов для ВКонтакте | vk.com API wrapper

vk_api vk_api – Python модуль для создания скриптов для ВКонтакте (vk.com API wrapper) Документация Примеры Чат в Telegram Документация по методам API

Kirill 1.2k Jan 04, 2023
A wrapper for The Movie Database API v3 and v4 that only uses the read access token (not api key).

fulltmdb A wrapper for The Movie Database API v3 and v4 that only uses the read access token (not api key). Installation Use the package manager pip t

Jacob Hale 2 Sep 26, 2021
ANKIT-OS/STYLISH-TEXT is a special repository. Its Is A Telegram Bot Which Can Translate Your Text Into 100+ Language

🔥 ᴳᴼᴼᴳᴸᴱ⁻ᵀᴿᴬᴺᔆᴸᴬᵀᴱᴿ 🔥 The owner would not be responsible for any kind of bans due to the bot. • ⚡ INSTALLING ⚡ • • 🛠️ Lᴀɴɢᴜᴀɢᴇs Aɴᴅ Tᴏᴏʟs 🔰 • If

ANKIT KUMAR 1 Dec 23, 2021
trading strategy for freqtrade crypto bot it base on CDC-ActionZone

ft-action-zone trading strategy for freqtrade crypto bot it base on CDC-ActionZone Indicator by piriya33 Clone The Repository if you just clone this r

Miwtoo 17 Aug 13, 2022
Most Simple & Powefull web3 Trade Bot (WINDOWS LINUX) Suport BSC ETH

Most Simple & Powefull Trade Bot (WINDOWS LINUX) What Are Some Pros And Cons Of Owning A Sniper Bot? While having a sniper bot is typically an advanta

GUI BOT 4 Jan 25, 2022
Simple integrate of API musixmatch.com with python

Python Musixmatch Simple integrate of API musixmatch.com with python Quick start $ pip install pymusixmatch or $ python setup.py install Authenticatio

Hudson Brendon 79 Dec 20, 2022
This an API wrapper library for the OpenSea API written in Python 3.

OpenSea NFT API Python 3 wrapper This an API wrapper library for the OpenSea API written in Python 3. The library provides a simplified interface to f

Attila Tóth 159 Dec 26, 2022
An advanced api client for python botters.

[ALPHA] pybotters An advanced api client for python botters. 📌 Description pybottersは仮想通貨botter向けのPythonライブラリです。複数取引所に対応した非同期APIクライアントであり、bot開発により素晴ら

261 Dec 31, 2022
Yandex OSINT tool

YaSeeker Description YaSeeker - an OSINT tool to get info about any Yandex account using email or login. It can find: Fullname Photo Gender Yandex UID

HowToFind 110 Jan 03, 2023
Terraform wrapper to manage state across multiple cloud providers(AWS, GCP, and Azure)

Terraform Remote State Manager(tfremote) tf is a python package for managing terraform remote state for: Google(Gcloud), AWS, and Azure. It sets a def

tomarv2 1 Dec 08, 2021
Python client for using Prefect Cloud with Saturn Cloud

prefect-saturn prefect-saturn is a Python package that makes it easy to run Prefect Cloud flows on a Dask cluster with Saturn Cloud. For a detailed tu

Saturn Cloud 15 Dec 07, 2022
Nasdaq Cloud Data Service (NCDS) provides a modern and efficient method of delivery for realtime exchange data and other financial information. This repository provides an SDK for developing applications to access the NCDS.

Nasdaq Cloud Data Service (NCDS) Nasdaq Cloud Data Service (NCDS) provides a modern and efficient method of delivery for realtime exchange data and ot

Nasdaq 8 Dec 01, 2022
We have made you a wrapper you can't refuse

We have made you a wrapper you can't refuse We have a vibrant community of developers helping each other in our Telegram group. Join us! Stay tuned fo

20.6k Jan 04, 2023
The first open-source PyTgCalls-based project.

SU Music Player — The first open-source PyTgCalls based Pyrogram bot to play music in voice chats Requirements FFmpeg NodeJS 15+ Python 3.7+ Deploymen

Calls Music 74 Nov 19, 2022
A repo-watcher to watch for commits on a repo an trigger GitHub action by sending a `repository_dispatch` event to destinantion repo

repo-watcher-dispatch-sender This app is used to send a repository_dispatch event to the destination repo set in config.py or Environmental Variables

Divide Projects™ 2 Feb 06, 2022
Frwdit-V1 - A Simple Bot can copy any media to a private channel provided

📠 Auto Forward V2 A Simple Bot can copy any media to a private channel provided

FUCK OFF ANE 3 Dec 03, 2022
A stable telegram bot to get restricted messages with custom thumbnail support

Save restricted content Bot A stable telegram bot to get restricted messages with custom thumbnail support

DEVANSH 3 Feb 09, 2022
An anime themed telegram bot that can convert telegram media.

ShoukoKomiRobot • 𝕎𝕣𝕚𝕥𝕥𝕖𝕟 𝕀𝕟 Python3 • 𝕃𝕚𝕓𝕣𝕒𝕣𝕪 𝕌𝕤𝕖𝕕 Pyrogram • 𝕊𝕠𝕗𝕥𝕨𝕒𝕣𝕖 𝕌𝕤𝕖𝕕 Ebook-convert Deploy 𝔽𝕠𝕣𝕜 𝕥𝕙𝕚𝕤 𝕣

25 Aug 14, 2022