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
Python library for the eWarehousing Solutions API.

eWarehousing Solutions Python Library This library provides convenient access to the eWarehousing Solutions API from applications written in the Pytho

eWarehousing Solutions 2 Nov 09, 2022
Automatically pulls specified repository whenever a specified file is pushed. Great for working collaboratively when you need to run something locally.

autopull Simple python tool that allows you to automatically pull from a github repository whenever a file with a specified name is uploaded installat

carreb 0 Sep 27, 2022
Discord Token Generator of a project - Some stupids ppl are trying to leak it so i'm leaking faster :)

Original creator: Rolf (dort) HCaptcha Bypasser: h0nde Shark.Solar Discord Token Generator of a project - Some stupids ppl are trying to leak it so i'

Stanley 14 Sep 29, 2021
A modular telegram Python bot running on python3 with an sqlalchemy database.

TG_Bot A modular telegram Python bot running on python3 with an sqlalchemy database. Originally a simple group management bot with multiple admin feat

Movindu Bandara 1 Nov 02, 2021
Bulk convert image types with Python

Bulk Image Converter 🔥 Helper script to convert a folder's worth of images from one filetype to another, and optionally delete originals Use Setup /

1 Nov 13, 2021
数字货币动态趋势网格,随着行情变动。目前实盘月化10%。目前支持币安,未来上线火币、OKEX。

数字货币动态趋势网格,随着行情变动。目前实盘月化10%。目前支持币安,未来上线火币、OKEX。

幸福村的码农 98 Dec 27, 2022
May or may not be work🚶

AnyDLBot There are multiple things I can do: 👉 All Supported Video Formats of https://rg3.github.io/youtube-dl/supportedsites.html 👉 Upload as file

Arun 2 Nov 16, 2021
A modern Python client for controlling Wyze devices.

Python Wyze SDK A modern Python client for controlling Wyze devices. Whether you're building a custom app, or integrating into a third-party service l

Shaun Tarves 205 Jan 02, 2023
Python lib to control HottoH based stove devices

Project desciption This library can be used to discuss with HootoH based stove devices Actually tested and validated with a CMG Drum stove. To use thi

3 May 16, 2022
Opensea-upload-with-recaptcha-solution - Updated opensea uploading solution with recaptcha pass

opensea-upload-with-recaptcha-solution updated opensea uploading solution with r

byeonggeon sim 25 Nov 15, 2022
A Python library wrapping the iFixit (Dozuki) API.

A Python library wrapping the iFixit API. Status Working, but incomplete. Fully tested and documented. Hacking I highly recommend using virtualenv: [$

James Pearson Hughes 13 May 24, 2021
Unencrypted Story View Botter is a helpful tool that allows thousands of people to watch your posts.

Unencrypted Story View Botter is a helpful tool that allows thousands of people to watch your posts.

8 Aug 05, 2022
A Telegram Most Powerful Media Info Bot.

Media Info Bot Support 🚑 Demo For The Bot -Test Our Bot By Clicking The Button Below Deploy To Heroku 🗳 Press the Deploy Button to Get Your Own Bot.

Anonymous 5 May 16, 2022
A Discord bot for osu!

This is the mostly-complete repo for the owo Discord osu! bot which you can invite here. As you look through this repo, please keep in mind that all o

Stevy 43 Dec 28, 2022
API kumpulan doa-doa sesuai al-qur'an dan as-sunnah

API kumpulan doa-doa sesuai al-qur'an dan as-sunnah

Miftah Afina 4 Nov 26, 2022
Discord Account Generator that will create Account with hCaptcha bypass. Using socks4 proxies

Account-Generator [!] This was made for education. Please use socks4 proxies for nice experiences. [!] Please install these modules - "pip3 install ht

RyanzSantos 10 Feb 23, 2022
Google scholar share - Simple python script to pull Google Scholar data from an author's profile

google_scholar_share Simple python script to pull Google Scholar data from an au

Paul Goldsmith-Pinkham 9 Sep 15, 2022
Telegram forwarder

Telegram Forwarder Quick Start This application using docker, docker-compose to run. So I suppose that you can install these two things. Prepare essen

10 Dec 20, 2022
Stinky ID - A stable pluggable Telegram userbot + Voice & Video Call music bot, based on Telethon

Ultroid - UserBot A stable pluggable Telegram userbot + Voice & Video Call music

Riyan.rz 1 Jan 03, 2022
Hcl.py is an Amino client for Python

Hcl.py Hcl.py Hcl.py is an Amino client for Python. It provides to access aminoapps Web, app and socket servers. Developed BY Kapidev And Upgraded BY

Oustex 3 Dec 02, 2021