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 Phyton script working for stream twits from twitter by tweepy to mongoDB

twitter-to-mongo A python script that uses the Tweepy library to pull Tweets with specific keywords from Twitter's Streaming API, and then stores the

3 Feb 03, 2022
An all-in-one discord bot!

Interbot Interbot is a do-it-all bot originally made for the Interbyte Studios discord server. This repo contains the code for this bot, allowing you

Logan 5 Aug 03, 2021
Google Search Results via SERP API pip Python Package

Google Search Results in Python This Python package is meant to scrape and parse search results from Google, Bing, Baidu, Yandex, Yahoo, Home depot, E

SerpApi 254 Jan 05, 2023
Python CMR is an easy to use wrapper to the NASA EOSDIS Common Metadata Repository API.

This repository is a copy of jddeal/python_cmr which is no longer maintained. It has been copied here with the permission of the original author for t

NASA 9 Nov 16, 2022
SimpleTelegramScraper - A python script scrapes accounts from public groups via Telegram API and saves them in a CSV file

SimpleTelegramScraper - the best scraper on GitHub This simple python script scr

Deniz Shabani 12 Oct 06, 2022
OMDB-and-TasteDive-Mashup - Mashing up data from two different APIs to make movie recommendations.

OMDB-and-TasteDive-Mashup This hadns-on project is in the Python 3 Programming Specialization offered by University of Michigan via Coursera. Mashing

Eszter Pai 1 Jan 05, 2022
Project glow is an open source bot worked on by many people to create a good and safe moderation bot for all

Project Glow Greetings, I see you have stumbled upon project glow. Project glow is an open source bot worked on by many people to create a good and sa

Glowstikk 24 Sep 29, 2022
Cleiton Leonel 4 Apr 22, 2022
Python app to notify via slack channel the status_code change from an URL

Python app to notify, via slack channel you choose to be notified, for the status_code change from the URL list you setup to be checked every yy seconds

Pedro Nunes 1 Oct 25, 2021
The official Python library for the Plutto API

Plutto Ruby SDK This library will help you easily integrate Plutto API to your software, making your developer life a little bit more enjoyable. Insta

Plutto 3 Nov 23, 2021
b2blaze

b2blaze Welcome to the b2blaze library for Python. Backblaze B2 provides the cheapest cloud object storage and transfer available on the internet. Com

George Sibble 603 Jan 03, 2023
❤️ Hi There Im EzilaX ❤️ A next gen powerful telegram group manager bot 😱 for manage your groups and have fun with other cool modules Made By Sadew Jayasekara 🔥

❤️ EzilaX v1 ❤️ Unmaintained. The new repo of @EzilaXBot is Public. (It is no longer based on this source code. The completely rewritten bot available

Sadew Jayasekara 18 Nov 24, 2021
A discord bot that will help you browse/download nhentai sources.

Risa Introduction Risa is an nHentai discord bot that will help you browse and download your favorite doujin inside your own discord server. Hosting M

markee7 14 Oct 25, 2021
Linky bot, A open-source discord bot that allows you to add links to ur website, youtube url, etc for the people all around discord to see!

LinkyBot Linky bot, An open-source discord bot that allows you to add links to ur website, youtube url, etc for the people all around discord to see!

AlexyDaCoder 1 Sep 20, 2022
A site devoted to celebrating to matching books with readers and readers with books. Inspired by the Readers' Advisory process in library science, Literati, and Stitch Fix.

Welcome to Readers' Advisory Greetings, fellow book enthusiasts! Visit Readers' Advisory! Menu Technologies Key Features Database Schema Front End Rou

jane martin 6 Dec 12, 2021
Assistant made in python to control your spotify via voice

Spotify-Assistant Assistant made in python to control your spotify via voice Overview 🚀 PLAY, PAUSE, NEXT, PREVIOUS, VOLUME COMMANDS 📝 Toast notific

Mauri 6 Jan 18, 2022
An Auto-Grinding bot made for Pokemeow. Efficient but not many features yet

PokeGrinder 🤖 This is an Auto-Grinding bot made for Pokemeow. Efficient but not many features yet. Supported features This bot can currently handle :

Xombie 9 Feb 01, 2022
Based Telegram Bot and Userbot To Play Music in Your Telegram Groups With Some Cool Extra Features! 🥰

CallMusicPlus69 This Repo base on! 🤗️ A CallsMusic Based Telegram Bot and Userbot To Play Music in Your Telegram Groups With Some Cool Extra Features

brut✘⁶⁹ // ユスフ 6 Jun 26, 2022
A generative art library for NFT avatar and collectible projects.

Generative NFT Art Introduction The generative-art-nft repository is a library for creating generative art. It was developed for the purpose of creati

Rounak Banik 657 Jan 02, 2023
Video Bot: an Advanced Telegram Bot that's allow you to play Video & Music on Telegram Group Video Chat

Video Bot is an Advanced Telegram Bot that's allow you to play Video & Music on

5 Jan 26, 2022