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
π—–π π¦π©π πžπ₯ 𝝦π—₯πžπ—–π½Β°β„’οΈ πŸ‡±πŸ‡° Is An All In One Media Inline Bot Made For Inline Your Media Effectively With Some Advance Security Toolsβ™₯️

π—–π π¦π©π πžπ₯ 𝝦π—₯πžπ—–π½Β° ℒ️ πŸ‡±πŸ‡° π—™π—˜π π©π—¨π—₯π—˜π—¦ Auto Filter IMDB Admin Commands Broadcast Index IMDB Search Inline Search Random Pics Ids & User I

KΙͺκœ±α΄€Κ€α΄€ Pα΄‡κœ±α΄€Ι΄α΄ŠΙͺα΄›Κœ Pᴇʀᴇʀᴀ 〄 13 Jun 21, 2022
Google translator bot using pyTelegramBotAPI

iTranslator-bot Super google translator bot using pyTelegramBotAPI A bot is a professional bot that automatically detects a language in texts or capti

Abdulatif 6 Nov 22, 2022
A stack-based systems language that supports structures, functions, expressions, and user-defined operator behaviour

A stack-based systems language that supports structures, functions, expressions, and user-defined operator behaviour. Currently compiles to URCL with plans to add additional formats in the future.

Lucida Dragon 3 Nov 03, 2022
Utilizing the freqtrade high-frequency cryptocurrency trading framework to build and optimize trading strategies. The bot runs nonstop on a Rasberry Pi.

Freqtrade Strategy Repository Please test all scripts and dry run them before using them in live mode Contact me on discord if you have any questions!

Michael Fourie 90 Jan 01, 2023
Asynchronous and also synchronous non-official QvaPay client for asyncio and Python language.

Asynchronous and also synchronous non-official QvaPay client for asyncio and Python language. This library is still under development, the interface could be changed.

Leynier GutiΓ©rrez GonzΓ‘lez 8 Sep 18, 2021
Telegram Userbot built with Pyrogram

Pyrogram Userbot A Telegram Userbot based on Pyrogram This repository contains the source code of a Telegram Userbot and the instructions for running

Athfan Khaleel 113 Jan 03, 2023
A bot which is a ghost and you can make friends with it

This is a bot which is a ghost and you can make friends with it. It will haunt your friends. Explore and test the bot in replit !

Siwan SR 0 Oct 06, 2022
Der Dischkort Bot fΓΌr Andiismus

AndreOS Der Dischkort Bot fΓΌr Andiismus Wichtigger Bot fΓΌr den hauseigenen Discord-Server Indoktrinationsmechanismusleitungsprogramm der andiistischen

Leon Bartle 3 Jan 13, 2022
A Flask inspired, decorator based API wrapper for Python-Slack.

A Flask inspired, decorator based API wrapper for Python-Slack. About Tangerine is a lightweight Slackbot framework that abstracts away all the boiler

Nick Ficano 149 Jun 30, 2022
Change between dark/light mode depending on the ambient light intensity

svart Change between dark/light mode depending on the ambient light intensity Installation Install using pip $ python3 -m pip install --user svart Ins

Siddharth Dushantha 169 Nov 26, 2022
A Simple Voice Music Player

πŸ“€ π•π‚π”π¬πžπ«ππ¨π­ βˆšπ™π™šπ™–π™’βœ˜π™Šπ™˜π™©π™–π™«π™š NOTE JUST AN ENGLISH VERSION OF OUR PRIVATE SOURCE WAIT FOR LATEST UPDATES JOIN @π’π”πππŽπ‘π“ JOIN @𝐂?

TeamOctave 8 May 08, 2022
AWS DeepRacer Free Student Workshop: Run faster by using your custom waypoints

AWS DeepRacer Free Student Workshop: Run faster by using your custom waypoints Reward Function Template for waypoints def reward_function(params):

Yuen Cheuk Lam 88 Nov 27, 2022
Security Monkey monitors AWS, GCP, OpenStack, and GitHub orgs for assets and their changes over time.

NOTE: Security Monkey is in maintenance mode and will be end-of-life in 2020. For AWS users, please make use of AWS Config. For GCP users, please make

Netflix, Inc. 4.3k Jan 09, 2023
A multi-purpose Discord bot with simple moderation commands, reaction roles, reminders, and much more!

Nokari This is the rewrite of Nokari. There are still a lot of things to be done. I'm still working on the internal logic, so the bot basically has no

Norizon 13 Nov 17, 2022
Wrapper for the Swiss Parliament API for Python

swissparlpy This module provides easy access to the data of the OData webservice of the Swiss parliament. Table of Contents Installation Usage Get tab

Stefan Oderbolz 8 Jun 13, 2022
Replace sequence_IDs in gff3 based on given genome.fasta

gff-rename Replace the sequence IDs in a gff3 file with a set of provided sequence IDs from a genom.fasta. This is useful when a gff3 file is retrieve

tolkit 1 Nov 12, 2021
A very simple Salesforce.com REST API client for Python

Simple Salesforce Simple Salesforce is a basic Salesforce.com REST API client built for Python 3.5, 3.6, 3.7 and 3.8. The goal is to provide a very lo

simple salesforce 1.4k Dec 29, 2022
A Python script to update Spotify Playlist data every 5 minutes.

Spotify Playlist Updater A Python script to update Spotify Playlist data every 5 minutes. Description An automatic playlist updater using Spotify API

6 Nov 24, 2022
See trending stock tickers on Reddit and check Stock perfomance

See trending stock tickers on Reddit and check Stock perfomance

Abbas 1.5k Jan 06, 2023
:spaghetti: Pastas is an open-source Python framework for the analysis of hydrological time series.

Pastas: Analysis of Groundwater Time Series Pastas: what is it? Pastas is an open source python package for processing, simulating and analyzing groun

Pastas 277 Dec 29, 2022