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
Python Wrapper for handling payment requests through the Daraja MPESA API

Python Daraja Description Python Wrapper for handling payment requests through the Daraja MPESA API Contribution Refer to the CONTRIBUTING GUIDE. Usag

William Otieno 18 Dec 14, 2022
An Inline Telegram YouTube Downloader bot with custom, permanent thumbnail support and cancel upload facility. Make your fork now.

Inline-Tube-Mate (YouTube Downloader) An Inline Telegram bot that can download YouTube videos with permanent thumbnail support Bot need to be in Inlin

Renjith Mangal 41 Dec 14, 2022
pymobiledevice fork with more recent coding standards and many more features

Description Features Installation Usage Sending your own messages Lockdown messages Instruments messages Example Lockdown services com.apple.instrumen

255 Dec 28, 2022
Troposphere and shellscript based AWS infrastructure automation creates an awsapigateway lambda with a go backend

Automated-cloudformation-infra Troposphere and shellscript based AWS infrastructure automation. Feel free to clone and edit for personal usage. The en

1 Jan 03, 2022
This bot automaticaly access to giveaway ! You can won free NFT !

This bot automaticaly access to giveaway ! You can won free NFT !

2s.py 28 Oct 20, 2022
This library is for simplified work with the sms-man.com API

SMSMAN Public API Python This is a lightweight library that works as a connector to Sms-Man public API Installation pip install smsman Documentation h

13 Nov 19, 2022
A discord bot that manages your server's hedge fund

Can't Hide Money Bot A discord bot that manages your server's hedge fund Installing Install wkhtmltopdf sudo apt-get install wkhtmltopdf OR brew insta

Kelvin Abrokwa-Johnson 0 Oct 16, 2021
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
Chatbot construido com o framework Rasa para responder dúvidas referentes ao COVID-19.

Racom Chatbot Chatbot construido com o framework Rasa. Como executar Necessário instalar Docker e Docker Compose. Para inicializar a aplicação, basta

Vinícius Souza 4 Jul 28, 2022
Python3 script to dump employee information from XING API

XingDumper Python 3 script to dump company employees from XING API. Perfect OSINT tool ;-) The results contain firstname, lastname, position, gender,

LRVT 11 Dec 26, 2022
A pre-attack hacker tool which aims to find out sensitives comments in HTML comment tag and to help on reconnaissance process

Find Out in Comment Find sensetive comment out in HTML ⚈ About This is a pre-attack hacker tool that searches for sensitives words in HTML comments ta

Pablo Emídio S.S 8 Dec 31, 2022
A Discord bot written in Python that can be used to control event management on a server.

Event Management Discord Bot A Discord bot written in Python that can be used to control event management on a Discord server. Made originally for GDS

Suvaditya Mukherjee 2 Dec 07, 2021
KaydyPurge - Python Purge Script for Discord made by Kaydy Cain#0001

How to Install Open terminal Execute "git clone https://github.com/apolo1337/Kay

apolo 5 Jan 27, 2022
A small repository with convenience functions for working with the Notion API.

Welcome! Within this respository are a few convenience functions to assist with the pulling and pushing of data from the Notion API.

10 Jul 09, 2022
Huggingface transformers for discord

disformers Huggingface transformers for discord base source butyr/huggingface-transformer-chatbots install pip install -U disformers example see examp

SpaceDEVofficial 1 Nov 09, 2021
Telegram bot for searching videos in your PDisk account by @AbirHasan2005

PDisk-Videos-Search A Telegram bot for searching videos in your PDisk account by @AbirHasan2005. Configs API_ID - Get from @TeleORG_Bot API_HASH - Get

Abir Hasan 39 Oct 21, 2022
🚀 An asynchronous python API wrapper meant to replace discord.py - Snappy discord api wrapper written with aiohttp & websockets

Pincer An asynchronous python API wrapper meant to replace discord.py ❗ The package is currently within the planning phase 📌 Links |Join the discord

Pincer 125 Dec 26, 2022
Experiment to find the best time to look for an appointment at the Berlin Bürgeramt

Bürgeramt appointment experiment Checks Berlin.de for free Anmeldung appointments every X minutes, then analyses the results. How to use Run get-page.

Nicolas Bouliane 42 Jan 02, 2023
An anime themed telegram bot that can convert telegram media.

ShoukoKomiRobot • 𝕎𝕣𝕚𝕥𝕥𝕖𝕟 𝕀𝕟 Python3 • 𝕃𝕚𝕓𝕣𝕒𝕣𝕪 𝕌𝕤𝕖𝕕 Pyrogram • 𝕊𝕠𝕗𝕥𝕨𝕒𝕣𝕖 𝕌𝕤𝕖𝕕 Ebook-convert Deploy 𝔽𝕠𝕣𝕜 𝕥𝕙𝕚𝕤 𝕣

25 Aug 14, 2022
This bot will automatically like and follow users that post under a specified hashtag

Instagram-bot This bot will automatically like and follow users that post under a specified hashtag Dependencies Java JDK Selenium Updated version of

Makana Edwards 1 Nov 04, 2021