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
CDBEC: Catware DataBase Encryption Client

CDBEC: Catware DataBase Encryption Client Описание CDBEC - клиент для создания, просмотра и редактирования .db-catencrypted списков, шифруемых при пом

Catware-Foundation 2 Nov 03, 2022
This repo provides the source code for "Cross-Domain Adaptive Teacher for Object Detection".

Cross-Domain Adaptive Teacher for Object Detection This is the PyTorch implementation of our paper: Cross-Domain Adaptive Teacher for Object Detection

Meta Research 91 Dec 12, 2022
Cryptocurrency Trading Bot - A trading bot to automate cryptocurrency trading strategies using Python, equipped with a basic GUI

Cryptocurrency Trading Bot - A trading bot to automate cryptocurrency trading strategies using Python, equipped with a basic GUI. Used REST and WebSocket API to connect to two of the most popular cry

Francis 8 Sep 15, 2022
Petit webhook manager by moi (wassim)

Webhook Manager By wassim oubliez pas de ⭐ le projet Installations il te faut python sinon quand tu va lancer le start.bat sa va tout installer tout s

wassim 9 Jul 08, 2021
Streaming Finance Data with AWS Lambda

A data pipeline consisting of an AWS lambda function reading data from yfinance API, an AWS Kinesis stream to receive & store data in S3 buckets and AWS Glue crawler & Athena to run SQL queries.

Aarif Munwar Jahan 4 Aug 30, 2022
A self hosted slack bot to conduct standups & generate reports.

StandupMonkey A self hosted slack bot to conduct standups & generate reports. Report Bug · Request Feature Installation Install already hosted bot (Us

Muhammad Haseeb 69 Jan 01, 2023
Herramienta para transferir eventos de Sucuri WAF hacia Azure Monitor Log Analytics.

Transfiere eventos de Sucuri hacia Azure LogAnalytics Script para transferir eventos del Sucuri Web Application Firewall (WAF) hacia Azure LogAnalytic

CSIRT-RD 1 Dec 22, 2021
Mixcloud API wrapper for Python and Async IO

aiomixcloud Mixcloud API wrapper for Python and Async IO aiomixcloud is a wrapper library for the HTTP API of Mixcloud. It supports asynchronous opera

Aristotelis Mikropoulos 26 Dec 31, 2022
Powerful Url uploader bot With Mongodb support 🔥

Uploader X Bot Telegram RoBot to Upload Links. Features: 👉 Upload YouTube-dl Supported Links to Telegram. 👉 Upload HTTP/HTTPS as File/Video to Teleg

C͡linton Abraꫝam 250 Jan 06, 2023
A simple library for interacting with Amazon SQS.

qoo is a very simple Amazon SQS client, written in Python. It aims to be much more straight-forward to use than boto3, and specializes only in Amazon

Jacobi Petrucciani 2 Oct 30, 2020
Python library for generating sequences with uniform stimulus history

Sampling Euler tours for uniform stimulus history Table of Contents About Examples Experiment 1 Experiment 2 Experiment 3 Experiment 4 Experiment 5 Co

5 Nov 11, 2021
Block Telegram's new

Telegram Channel Blocker Bot Channel go away! This bot is used to delete and ban message sent by channel How this appears? The reason this appears ple

16 Feb 15, 2022
Python wrapper for JeyyAPI

Async python wrapper for JeyyAPI

7 Dec 10, 2022
Reddit cli to slack at work

Reddit CLI (v1.0) Introduction Why Reddit CLI? Coworker who sees me looking at something in a browser: "Glad you're not busy; I need you to do this, t

3 Jun 22, 2021
This is a very simple botnet with a CnC server, made by me. Feel free to change anything

This is a very simple botnet with a CnC server, made by me. Feel free to change anything

8 Nov 12, 2022
Its The Basic Commands Of Termux

Its The Basic Commands Of Termux

ANKIT KUMAR 1 Dec 27, 2021
Flaga ze Szturmu na AWS.

Witaj Jesteś na GitHub'ie i czytasz właśnie plik README.md który znajduje się wewnątrz repozytorium Flaga z 7 i 8 etapu Szturmu na AWS. W tym etapie w

9 May 16, 2022
A simple Python API wrapper for Cloudflare Stream's API.

python-cloudflare-stream A basic Python API wrapper for working with Cloudflare Stream. Arbington.com started off using Cloudflare Stream. We used the

Arbington 3 Sep 08, 2022
A high level library for building Discord bots.

Qord A high level library for building Discord bots. 🚧 This library is currently in development. Questions that you are having What is this? This is

Izhar Ahmad 16 May 14, 2022
Raid ToolBox (RTB) is a big toolkit of Spamming/Raiding/Token management tools for discord.

This code is very out of date and not very good, feel free to make it into something better. (we check the github page every 5 years to pulls your PRs

2 Oct 03, 2021