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
Auto Join: A GitHub action script to automatically invite everyone to the organization who comment at the issue page.

Auto Invite To Org By Issue Comment A GitHub action script to automatically invite everyone to the organization who comment at the issue page. What is

Max Base 6 Jun 08, 2022
Netflix Movies and TV Series Downloader Tool including CDM L1 which you guys can Donwload 4K Movies

NFRipper2.0 I could not shared all the code here Because its has lots of files inisde it https://new.gdtot.me/file/86651844 - Downoad File From Here.

Kiran 15 May 06, 2022
Official Python wrapper for the Quantel Finance API

Quantel is a powerful financial data and insights API. It provides easy access to world-class financial information. Quantel goes beyond just financial statements, giving users valuable information l

Guy 47 Oct 16, 2022
A python script that changes our background based on current weather and time of the day.

Desktop background on Windows 10, based on current weather and time A python script that changes our background based on current weather and time of t

Maj Gaberšček 1 Nov 16, 2021
• Create Your Own YouTube Info Api.

youtube_data_api • Create Your Own YouTube Info Api. Deploy How to Use https://{ Heroku App Name }.herokuapp.com/api?link={YouTube link} In local Host

lokaman chendekar 12 Oct 02, 2022
A EddieHub API python package.

EddieHub A EddieHub API python package. Made with Python3 (C) @FayasNoushad Copyright permission under MIT License License - https://github.com/Fayas

Fayas Noushad 5 Sep 22, 2021
An open source raffle bot made to increase the chance of winning limited sneaker raffles by automating entries.

🚀 SyneziaRaffles An open source raffle bot made to increase the chance of winning limited sneaker raffles by automating entries. 🏄‍♂️ Quick Start Pr

Alexis M. 29 Dec 22, 2022
A file-based quote bot written in Python

Let's Write a Python Quote Bot! This repository will get you started with building a quote bot in Python. It's meant to be used along with the Learnin

1 Feb 23, 2022
A python notification tool used for sending you text messages when certain conditions are met in the game, Neptune's Pride.

A python notification tool used for sending you text messages when certain conditions are met in the game, Neptune's Pride.

Paul Clarke 1 Jan 16, 2022
✨ 🐍 Python SDK for StarkNet.

✨ 🐍 starknet.py StarkNet SDK for Python 📘 Documentation Installation Quickstart Guide API Installation To install this package run pip install stark

Software Mansion 158 Jan 04, 2023
Terraform module to ship CloudTrail logs stored in a S3 bucket into a Kinesis stream for further processing and real-time analysis.

AWS infrastructure to ship CloudTrail logs from S3 to Kinesis This repository contains a Terraform module to ship CloudTrail logs stored in a S3 bucke

Nexthink 8 Sep 20, 2022
This is a straightforward python implementation to specifically grab basic infos about IPO companies in China from Sina Stock website.

SinaStockBasicInfoCollect This is a straightforward python implementation to specifically grab basic infos about IPO companies in China from Sina Stoc

CrosSea 1 Dec 09, 2021
Hassium Server Manager For Python

Hassium Server Manager This is meant to be a tool for mostly internal use. I decided that I would make it open souce in case anyone wanted to use it.

0 Nov 24, 2022
Is the CoWin website updated for registration?

CoWin-Update Is the CoWin website updated for registration? This is a very hacky PYTHON3 script to lookup the CoWin portal if they re-deployed their J

Yash Jakhotiya 5 May 10, 2021
Coinbase Listing Sniper

Coinbase Listing Sniper Script that listens to the @CoinbaseAssets twitter to find information about new Coinbase listings, and automatically buys 100

4 Oct 26, 2022
Customizable and open-sourced bot for a few private servers

MarlBot A private bot for controlling monkeys and turtles. Why does this bot exist? The bot exists as a general-purpose community bot for a select few

KR 1 Jan 18, 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
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
Box SDK for Python

Box Python SDK Installing Getting Started Authorization Server-to-Server Auth with JWT Traditional 3-legged OAuth2 Other Auth Options Usage Documentat

Box 371 Dec 29, 2022