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 part of HyRiver software stack for accessing hydrology data through web services

Package Description Status PyNHD Navigate and subset NHDPlus (MR and HR) using web services Py3DEP Access topographic data through National Map's 3DEP

Taher Chegini 51 Dec 10, 2022
A fast, distributed, high performance gradient boosting (GBT, GBDT, GBRT, GBM or MART) framework based on decision tree algorithms, used for ranking, classification and many other machine learning tasks.

Light Gradient Boosting Machine LightGBM is a gradient boosting framework that uses tree based learning algorithms. It is designed to be distributed a

Microsoft 14.5k Jan 08, 2023
Spore API wrapper written in Python

A wrapper for the Spore API that simplifies and complements its functionality

1 Nov 25, 2021
Bitstamp API wrapper for Python

NOTICE: THIS REPOSITORY IS NO LONGER ACTIVELY MAINTAINED It is highly unlikely that I will respond to PRs and questions about usage. This library was

Jack Preston 53 Mar 09, 2022
A multi-tenant multi-client scalable product categorising demo stack

Better Categories 4All: A multi-tenant multi-client product categorising stack The steps to reproduce training and inference are in the end of this fi

7 Feb 15, 2022
A Python library for loading data from a SpaceX Starlink satellite.

Starlink Python A Python library for loading data from a SpaceX Starlink satellite. The goal is to be a simple interface for Starlink. It builds upon

Austin 2 Jan 16, 2022
A bot to playing music in telegram vcg

Idzeroid Music|| Idzeroid Music adalah sebuah repository music bot telegram untuk memainkan suara di voice chat group anda. Fyi This repo im using for

idzeroid 1 Oct 26, 2021
A fork of discord.py meant to replace it

Texus A modern, easy to use, feature-rich, and async ready API wrapper for Discord written in Python. Key Features Modern Pythonic API using async and

Texus 1 Nov 18, 2021
A free, minimal, lightweight, cross-platform, easily expandable Twitch IRC/API bot.

parky's twitch bot A free, minimal, lightweight, cross-platform, easily expandable Twitch IRC/API bot. Features 🔌 Connect to Twitch IRC chat! 🔌 Conn

Andreas Schneider 10 Dec 30, 2022
Free python/telegram bot for easy execution and surveillance of crypto trading plans on multiple exchanges.

EazeBot Introduction Have you ever traded cryptocurrencies and lost overview of your planned buys/sells? Have you encountered the experience that your

Marcel Beining 100 Dec 06, 2022
Some python code to make twitter bots ;)

How to set up a twitter bot using python's tweepy library Create a twitter developer account and project Make sure you are logged into your twitter ac

Wael 2 Jan 10, 2022
trackbranch is a tool for developers that can be used to store collections of branches in the form of profiles.

trackbranch trackbranch is a tool for developers that can be used to store collections of branches in the form of profiles. This can be useful for sit

Kevin Morris 1 Oct 21, 2021
Automatically kick deleted accounts

AntiDeletedAccountsBot (ADAB) Automatically kick deleted accounts Based on uniborg, a pluggable asyncio Telegram userbot based on Telethon. Installati

Qwerty-Space 34 Jan 02, 2023
Telegram Link Shortener Bot (With 20 Shorteners)

Telegram ShortenerBot ShortenerBot: 🇬🇧 Telegram Link Shortener Bot (11 + 9 Shorteners) 🇹🇷 Telegram Link Kısaltıcı Bot (11 + 9 Kısaltıcı) All suppo

Hüzünlü Artemis [HuzunluArtemis] 10 May 24, 2022
Discord bot developed by Delhi University Student Community!

DUSC-Bot Discord bot developed by Delhi University Student Community! Libraries Used Pycord - Documentation Features Can purge messages in bulk Drop-D

professor 1 Jan 29, 2022
Messing around with GitHub API to look at omicron build times

gh-workflow-runs This is a very simple tool to dump out basic information about workflow runs for a GitHub repo. The structure is based on gh-subscrip

David Pacheco 1 Nov 30, 2021
Twitter feed of newly published articles in Limnology

limnopapers Code to monitor limnology RSS feeds and tweet new articles. Scope The keywords and journal choices herein aim to focus on limnology (the s

7 Dec 20, 2022
An API wrapper library for opensea api.

Opensea API An API wrapper library for opensea api. Installation pip3 install opensea Usage Retrieving assets: from opensea import get_assets # This

Ankush Singh 38 Jul 17, 2022
A tool written in Python used to instalock agents in VALORANT using the local API.

Valorant Instalock Tool v2.1.0 by Mr. SOSA A tool written in Python used to instalock agents in VALORANT using the local API. This is NOT a hotkey pro

Mr. SOSA 3 Nov 18, 2021