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
A Pancakeswap v2 trading client (and bot) with limit orders, stop-loss, custom gas strategies, a GUI and much more.

Pancakeswap v2 trading client A Pancakeswap trading client (and bot) with limit orders, stop-loss, custom gas strategies, a GUI and much more. If you

571 Mar 15, 2022
Binance leverage futures Hook

Simple binance futures Attention Just use leverage. The fee difference between futures and spot is not considered. For example, funding rate, etc. Onl

Adriance 26 Aug 27, 2022
The official Magenta Voice Skill SDK used to develop skills for the Magenta Voice Assistant using Voice Platform!

Magenta Voice Skill SDK Development • Support • Contribute • Contributors • Licensing Magenta Voice Skill SDK for Python is a package that assists in

Telekom Open Source Software 18 Nov 19, 2022
A Python Client to View F1TV Content the right way

F1Hub is a terminal application running directly on your computer -- no connection to the website needed* *In theory. As of now, the F1TV website is needed for some content

kodos 3 Jun 14, 2022
A small module to communicate with Triller's API

A small, UNOFFICIAL module to communicate with Triller's API. I plan to add more features/methods in the future.

A3R0 1 Nov 01, 2022
Slack Developer Kit for Python

Python Slack SDK The Slack platform offers several APIs to build apps. Each Slack API delivers part of the capabilities from the platform, so that you

SlackAPI 3.5k Jan 02, 2023
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
Easy & powerful bot to check if your all Telegram bots are working or not. This bot status bot updates every 45 minutes & runs for 24x7 hours.

PowerfulBotStatus-IDN-C-X Easy & powerful bot to check if your all Telegram bots are working or not. This bot status bot updates every 45 minutes & ru

IDNCoderX 5 Oct 06, 2022
Eclipse-grabber - Generate Discord Token Grabbers for both Windows and MacOS

Eclipse Grabber Eclipse Discord Token Grabber What is Eclipse? Eclipse is an ope

Dimitris Kalopisis 117 Dec 23, 2022
A stock information collector and parser for Taiwan and US market. Automatically send LINE message if the pre-defined rules are triggered.

agastock 開發動機 就在海運飆漲的2021年7月,差點跪在地上喜迎財富自由的當下,EPS超高好消息不斷的長榮竟然套在202元一去不回,有圖有真相(哭) 忽然體會到追高殺低不是辦法,魯蛇我得靠邏輯分析也能出頭天,經過三個月無數個不出門的周末,產出簡單的爬蟲和分析工具。 上過金融研訓院的量化交易

Gavin Lee 12 Nov 16, 2022
Simplebot-tg - Telegram/DeltaChat Bridge with python

simplebot_tg Telegram/DeltaChat Bridge, is a plugin for https://github.com/simpl

Arián Díaz Cruz 1 Dec 30, 2021
Discord bot template.py

discord_bot_template.py A minimal and open-source discord.py boilerplate for kick-starting bot projects. I spend a lot of time developing bots for dif

Tarran Prior 1 Feb 24, 2022
The Social-Engineer Toolkit (SET) is specifically designed to perform advanced attacks against the human element.

The Social-Engineer Toolkit (SET) The Social-Engineer Toolkit (SET) is specifically designed to perform advanced attacks against the human element. SE

Professor 6 Nov 28, 2022
DeleteAllBot - Telegram bot to delete all messages in a group

Delete All Bot A star ⭐ from you means a lot to me ! Telegram bot to delete all

Stark Bots 15 Dec 26, 2022
Space Bot, a Discord bot built for HackerSpace Club of PES University

Space Bot Space Bot, a Discord bot built for HackerSpace Club of PES University What can Space Bot do? Space Bot allows you to lookup any mentor or to

HackerSpace @PESU 7 Oct 23, 2022
SpotPlay2YouPlay - Converts new additions to a Spotify playlist to a matching Youtube playlist

SpotPlay2YouPlay - Converts new additions to a Spotify playlist to a matching Youtube playlist, can also be configured to converting whole playlists with the refresh fun

9 Mar 06, 2022
Fast discord token checker with high cpm

Discord-Token-checker Fast discord token checker with high cpm preivew Download git clone https://github.com/TusTusDev/Discord-Token-checker pip insta

Tustus 1 Oct 15, 2021
This repo provides the source code for "Cross-Domain Adaptive Teacher for Object Detection".

Cross-Domain Adaptive Teacher for Object Detection This is the PyTorch implementation of our paper: Cross-Domain Adaptive Teacher for Object Detection

Meta Research 91 Dec 12, 2022
Fast and multi-threaded script to automatically claim targeted username including 14 day bypass

Instagram Username Auto Claimer Fast and multi-threaded script to automatically claim targeted username. Click here to report bugs. Usage Download ZIP

265 Dec 28, 2022
Luna Rush Auto Clicker Bot

Luna Rush Auto Clicker Bot Se o aplicativo lhe ajudar de alguma forma, uma doação para ajudar a pagar a conta de luz sempre é bem vinda ;) Wallet Smar

Walter Discher Cechinel 29 Dec 20, 2022