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
An API which returns random AOT quote everytime it's invoked

An API which returns random AOT quote everytime it's invoked

Nishant Sapkota 1 Feb 07, 2022
This Wrapper is a Discum Copy With Addons, original one is made by Merubokkusu

Remaded Discum Its not Official Discum Wrapper ! This Wrapper is a Discum Copy With Addons, original one is made by Merubokkusu Authors @merubokkusu (

discum-remaded 8 Aug 09, 2022
Get some python in google cloud functions

[NOTE]: This is a highly experimental (and proof of concept) library so do not expect all python packages to work flawlessly. Also, cloud functions ar

Martin Abelson Sahlen 200 Nov 24, 2022
Deepak Clouds Torrent is a multipurpose Telegram Bot writen in Python for mirroring files on the Internet to our beloved Google Drive.

Deepak Clouds Torrent is a multipurpose Telegram Bot writen in Python for mirroring files on the Internet to our beloved Google Drive.

Deepak Clouds 37 Oct 28, 2022
A free sniper bot built to work with PancakeSwap: Router V2

Pancakeswap Sniper Bot PancakeSwap sniper bot. Automated sniping bot to snipe crypto coin launches. How it works The sniping bot can be used in three

89 Aug 06, 2022
Analyzed the data of VISA applicants to build a predictive model to facilitate the process of VISA approvals.

Analyzed the data of Visa applicants, built a predictive model to facilitate the process of visa approvals, and based on important factors that significantly influence the Visa status recommended a s

Jesus 1 Jan 08, 2022
Properly-formatted dynamic timestamps for Discord messages

discord-timestamps discord-timestamps generates properly-formatted dynamic timestamps for Discord messages, with support for Arrow objects. format

Ben Soyka 2 Mar 10, 2022
Telegram bot for downloading covid-19 vaccine certificate

cowin-certificate-bot This is the source code of @cowincertbot, A telegram bot inspired by the whatsapp bot implementation of indian government for co

ArUn Pt 30 Oct 07, 2022
Some 3Commas helper bots, AltRank, GalaxyScore, Watchlist, Auto-Compound

3Commas Cyber Bot Helpers A collection of 3Commas bot helpers I wrote. (collection will grow over time) Disclaimer THE SOFTWARE IS PROVIDED "AS IS", W

Ron Klinkien 176 Jan 02, 2023
Trading strategy for the Freqtrade crypto bot

NostalgiaForInfinity Trading strategy for the Freqtrade crypto bot Change strategy Add strategies to the user_data/strategies folder and also in the d

iterativ 1.5k Jan 01, 2023
A listener for RF >= 4.0 that prints a Stack Trace to console to faster find the code section where the failure appears.

robotframework-stacktrace A listener for RF = 4.0 that prints a Stack Trace to console to faster find the code section where the failure appears. Ins

marketsquare 16 Nov 24, 2022
Async ShareX uploader written in python

Async ShareX uploader written in python

Jacob 2 Jan 07, 2022
Python API for working with RESQML models

resqpy: Python API for working with RESQML models Introduction resqpy is a pure python package which provides a programming interface (API) for readin

BP 44 Dec 14, 2022
The Simple Google Colab Notebook to Download Files from Direct Link to Google Drive with custom name and bulk link support.

Direct Link to Google Drive (Advanced! 🔥 ) The Most Advanced yet Simple Google Colab Notebook to Download Files from Direct Link to Google Drive. 🆕

Dr.Caduceus 14 Jul 26, 2022
A discord bot that autobans blacklisted users by ID and Names

AutoBan A discord bot that autobans blacklisted users by ID and Names Getting Started Dependencies disnake @ git+https://github.com/DisnakeDev/disnake

Jason Martin 0 Oct 02, 2022
A Sublime Text plugin that displays inline images for single-line comments formatted like `// ![](example.png)`.

Inline Images Sometimes ASCII art is not enough. Sometimes an image says more than a thousand words. This Sublime Text plugin can display images inlin

Andreas Haferburg 8 Jul 01, 2022
Bot simply search for the files from provided channel according to given query and gives link to those files as buttons!

Auto Filter Bot ㅤㅤㅤㅤㅤㅤㅤ ㅤㅤㅤㅤㅤㅤㅤ You can call this as an Auto Filter Bot if you like :D Bot simply search for the files from provided channel according

TroJanzHEX 89 Nov 23, 2022
GitPython is a python library used to interact with Git repositories.

Gitoxide: A peek into the future… I started working on GitPython in 2009, back in the days when Python was 'my thing' and I had great plans with it. O

3.8k Jan 03, 2023
A python library built on the API of the coderHub.sa, which helps you to fetch the challenges and more

coderHub A python library built on the API of the coderHub.sa, which helps you to fetch the challenges and more Installation • Features • Usage • Lice

TheAwiteb 5 Nov 04, 2022
A module to complement discord.py that has Music, Paginator and Levelling.

discord-super-utils A modern python module including many useful features that make discord bot programming extremely easy. Features Modern leveling m

Yash 106 Dec 19, 2022