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
⚡ Yuriko Robot ⚡ - A Powerful, Smart And Simple Group Manager Written with AioGram , Pyrogram and Telethon

⚡ Yuriko Robot ⚡ - A Powerful, Smart And Simple Group Manager Written with AioGram , Pyrogram and Telethon

Øғғɪᴄɪᴀʟ Ⱡᴏɢ [₳ғᴋ] 1 Apr 01, 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 03, 2022
The open source version of Tentro - A multipurpose Discord bot.

Welcome to Tentro 👋 A multipurpose Discord bot. 🏠 Homepage Install pip install -r requirements.txt Usage py Tentro.py Contributors 👤 Tentro Dev Tea

6 Jul 14, 2022
this is a telegram torrent bot

owner of this repo :- AYUSH contact me :- AYUSH Slam Mirror Bot This is a telegram bot writen in python for mirroring files on the internet to our bel

AYUSH 237 Dec 16, 2021
A powerful bot to copy your google drive data to your team drive

⚛️ Clonebot - Heroku version ⚡ CloneBot is a telegram bot that allows you to copy folder/team drive to team drives. One of the main advantage of this

MsGsuite 269 Dec 23, 2022
A Very Simple Telegram Files Rename Bot by @AbirHasan2005

Rename-Bot This is a very simple Telegram Files Rename Bot by @AbirHasan2005. Features Rename Videos, Files or Audios. Permanent Custom Thumbnail Supp

Abir Hasan 124 Dec 25, 2022
Quadrirrotor UFABC - ROS/Gazebo

QuadROS_UFABC - Repositório utilizado durante minha dissertação de mestrado para simular sistemas de controle e estimação para navegação de um quadrirrotor utilizando visão computacional.

Mateus Ribeiro 1 Dec 13, 2022
An integrated information collection tool

infoscaner 环境配置 目前infoscaner仅支持在linux上运行,建议运行在最新版本的kali中 infoscaner是基于python3版本实现的,运行之前首先安装python库 如果同时存在python2和python3,请输入以下命令 pip3 install -r requi

CMACCKK 74 Sep 13, 2021
ShoukoKomiRobot - An anime themed telegram bot that can convert telegram media

ShoukoKomiRobot • 𝕎𝕣𝕚𝕥𝕥𝕖𝕟 𝕀𝕟 Python3 • 𝕃𝕚𝕓𝕣𝕒𝕣𝕪 𝕌𝕤𝕖𝕕 Pyrogram

25 Aug 14, 2022
A new coin listing alert bot using Python, Flask, MongoDB, Telegram API and Binance API

Bzzmans New Coin Listing Detection Bot Architecture About Project Work in progress. This bot basically gets new coin listings from Binance using Binan

Eyüp Barlas 21 May 31, 2022
Wordnik Python public library

Python 2.7 client for Wordnik.com API Overview This is a Python 2.7 client for the Wordnik.com v4 API. For more information, see http://developer.word

Wordnik 224 Dec 29, 2022
Este programa tem como objetivo o cadastro dos usuários. Assim, caso a autenticação seja feita, permitir que o usuário entre em determinado sistema ou programa.

LoginPy Este programa tem como objetivo o cadastro dos usuários. Assim, caso a autenticação seja feita, permitir que o usuário entre em determinado si

Jonas Carvalho 4 Dec 23, 2021
Popcorn-time-api - Python API for interacting with the Popcorn Time Servers

Popcorn Time API 📝 CONTRIBUTIONS Before doing any contribution read CONTRIBUTIN

Antonio 3 Oct 31, 2022
Набор утилит для Discord с использованием языка программирования Python.

Discord Tools v0.1 Functions: WebHook spamer Spotify account generator (What?) QR Code Token stealer Token generator Discord nitro gen/check Discor to

Максим Скризов 3 Aug 23, 2022
A Anything goes Discord bot written in python and uses the wrapper Discord.py

GerardTheWizard A Anything goes Discord bot written in python and uses the wrapper Discord.py What can he do? Allow users to level up through typing,

1 May 05, 2022
A discord bot that autobans blacklisted users by ID and Names

AutoBan A discord bot that autobans blacklisted users by ID and Names Getting Started Dependencies disnake @ git+https://github.com/DisnakeDev/disnake

Jason Martin 0 Oct 02, 2022
Beyonic API Python official client library simplified examples using Flask, Django and Fast API.

Beyonic API Python official client library simplified examples using Flask, Django and Fast API.

Yes, it's true :revolving_hearts: This repository has 301 stars.

Yes, it's true! Inspired by a similar repository from @RealPeha, but implemented using a webhook on AWS Lambda and API Gateway, so it's serv

510 Dec 28, 2022
Telegram bot to scrape images from the reddit universe

Telegram bot to scrape images from the reddit universe

XD22 3 Sep 30, 2022