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
Huggingface inference with GPU Docker on AWS

This repository contains code to containerize and deploy a GPU docker on AWS for summarization task. Find a detailed blogpost here Youtube Video Versi

Ramsri Goutham Golla 21 Dec 30, 2022
💖 Telegram - Telethon - UserBot 💖

『᭙ꪖ᥅ƺẞø†』 🇮🇳 ⚡ ᭙ꪖ᥅ƺBot Is One Of The Fastest & Smoothest Bot On Telegram Based on Telethon ⚡ Status Of Bot Telegram 🏪 YouTube 📺 Dєρℓογ το нєяοκυ D

Team WarZ 1 Mar 28, 2022
UP It is a script to notify of a new update of your project, done in python and using GitHub, to modify the versions to notify users.

UP-Updater UP It is a script to notify of a new update of your project, done in python and using GitHub, to modify the versions to notify users. Requi

Made in 4 Oct 28, 2021
Fully undetected auto skillcheck hack for dead by daylight that works decently well

Auto-skillcheck was made by Love ❌ code ✅ ❔ ・How to use Start off by installing python ofc Open cmd in the same directory and type pip install -r requ

Rdimo 10 Aug 13, 2022
A fork of discord.py

discord.py A modern, easy to use, feature-rich, and async ready API wrapper for Discord written in Python. The Future of discord.py Please read the gi

1 Dec 19, 2021
A secure and customizable bot for controlling cross-server announcements and interactions within Discord

DiscordBot A secure and customizable bot for controlling cross-server announcements and interactions within Discord. Within the code of the bot, you c

Jacob Dorfmeister 1 Jan 22, 2022
LavaAPI - A simple library for accepting payments and using the LAVA Wallet

This library was created to simplify the LAVA api provided on the official websi

Vlad Baccara 8 Dec 18, 2022
Get-Phone-Number-Details-using-Python - To get the details of any number, we can use an amazing Python module known as phonenumbers.

Get-Phone-Number-Details-using-Python To get the details of any number, we can use an amazing Python module known as phonenumbers. We can use the amaz

Coding Taggers 1 Jan 01, 2022
Fast IP address lookup

ipscoop Fast IP Scoop Table of Contents Installation CLI Getting Started Ref Installation To install ipscoop, simply: $ python3 -m pip install -U git+

6 Mar 16, 2022
Code for paper "Adversarial score matching and improved sampling for image generation"

Adversarial score matching and improved sampling for image generation This repo contains the official implementation for the ICLR 2021 paper Adversari

Alexia Jolicoeur-Martineau 114 Dec 11, 2022
A website application running in Google app engine, deliver rss news to your kindle. generate mobi using python, multilanguages supported.

Readme of english version refers to Readme_EN.md 简介 这是一个运行在Google App Engine(GAE)上的Kindle个人推送服务应用,生成排版精美的杂志模式mobi/epub格式自动每天推送至您的Kindle或其他邮箱。 此应用目前的主要

2.6k Jan 06, 2023
Python 3 tools for interacting with Notion API

NotionDB Python 3 tools for interacting with Notion API: API client Relational database wrapper Installation pip install notiondb API client from noti

Viet Hoang 14 Nov 24, 2022
Twitter feed of newly published articles in Limnology

limnopapers Code to monitor limnology RSS feeds and tweet new articles. Scope The keywords and journal choices herein aim to focus on limnology (the s

7 Dec 20, 2022
MONAI Deploy App SDK offers a framework and associated tools to design, develop and verify AI-driven applications in the healthcare imaging domain.

MONAI Deploy App SDK offers a framework and associated tools to design, develop and verify AI-driven applications in the healthcare imaging domain.

Project MONAI 49 Dec 23, 2022
Um simples bot público para todos usarem no discord!

Discord Bot - Código Público Características: Linguagem de Programação: Python Quantidade de comandos: 17 Comandos: Prefixo do bot: O prefixo desse bo

Kevin 3 Dec 31, 2021
SmsSender v3.0.0 - the script is designed to send free SMS to any number and with any text.

SmsSender v3.0.0 - скрипт предназначен для бесплатной отправки SMS на любой номер и с любым текстом. Возможны небольшие баги, в скором времени исправл

Андрей Сергеев 20 Dec 03, 2021
Polar devices Python API and CLI.

loophole - Polar devices API About Python API for Polar devices. Command line interface included. Tested with: A360 Loop M400 Installation pip install

[roscoe] 145 Sep 14, 2022
An advanced Filter Bot with nearly unlimitted filters!

Unlimited Filter Bot ㅤㅤㅤㅤㅤㅤㅤ ㅤㅤㅤㅤㅤㅤㅤ An advanced Filter Bot with nearly unlimitted filters! Features Nearly unlimited filters Supports all type of fil

1 Nov 20, 2021
A discord bot to check if messages have the correct code formatting.

discord-code-formatter A discord bot to check if messages have the correct code formatting. This was a basic project to help me learn Python and learn

Nash Boisvert 1 Nov 23, 2021
ZenML 🙏: MLOps framework to create reproducible ML pipelines for production machine learning.

ZenML is an extensible, open-source MLOps framework to create production-ready machine learning pipelines. It has a simple, flexible syntax, is cloud and tool agnostic, and has interfaces/abstraction

ZenML 2.6k Dec 27, 2022