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
This is a script to export logs from AWS CloudTrail to a local file.

cloudtrail-export-logs This is a script to export logs from AWS CloudTrail to a local file. Getting Started Prerequisites python 3 boto3 pip Installin

Claick Assunรงรฃo de Oliveira 2 Jan 02, 2022
The bot I used to win a 3d printing filament giveaway.

Instagram-CommentBot-For-Giveaways This is the bot I used to win a 3d printer filament giveaway on Instagram. Usually giveaways require you to tag oth

Esad Yusuf Atik 1 Aug 01, 2022
A Pluggable And Powerful Telegram Manager Bot

FRIDAY-USERBOT ๐Ÿ‡ฎ๐Ÿ‡ณ A Powerful, Smart And Simple Userbot In Pyrogram. Support ๐Ÿš‘ Inspiration & Credits Userge-X Userge Pokurt Pyrogram Code Owners Chs

DevsExpo 371 Mar 26, 2022
Wrapper for Between - ๋น„ํŠธ์œˆ์„ ์œ„ํ•œ ํŒŒ์ด์ฌ ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ

PyBetween Wrapper for Between - ๋น„ํŠธ์œˆ์„ ์œ„ํ•œ ํŒŒ์ด์ฌ ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ Legal Disclaimer ์˜ค์ง ๊ต์œก์  ๋ชฉ์ ์œผ๋กœ๋งŒ ์‚ฌ์šฉํ• ์ˆ˜ ์žˆ์œผ๋ฉฐ, ๋น„ํŠธ์œˆ์€ VCNC์˜ ์ž์‚ฐ์ž…๋‹ˆ๋‹ค. ์•…์˜์  ๊ณต๊ฒฉ์— ์ด์šฉํ• ์‹œ ์ฒ˜๋ฒŒ ๋ฐ›์„์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์‚ฌ์šฉ์— ๋”ฐ๋ฅธ ์ฑ…์ž„์€ ์‚ฌ์šฉ์ž๊ฐ€

1 Mar 15, 2022
Azure Neural Speech Service TTS

Written in Python using the Azure Speech SDK. App.py provides an easy way to create an Text-To-Speech request to Azure Speech and download the wav file. Azure Neural Voices Text-To-Speech enables flu

Rodney 4 Dec 14, 2022
Please Do Not Throw Sausage Pizza Away - Side Scrolling Up The OSI Stack

Please Do Not Throw Sausage Pizza Away - Side Scrolling Up The OSI Stack

John Capobianco 2 Jan 25, 2022
Orca is an extensive and extendable Python 3.x library for the Discord API.

Orca is an extensive and extendable Python 3.x library for the Discord API.

RPS 4 Apr 03, 2022
StringSessionGenerator - A Telegram bot to generate pyrogram and telethon string session

โญ๏ธ String Session Generator โญ๏ธ Genrate String Session Using this bot. Made by TeamUltronX ๐Ÿ”ฅ String Session Demo Bot: Environment Variables Mandatory

TheUltronX 1 Dec 31, 2021
A simple google translator telegram bot

Translator-Bot A simple google translator telegram bot Please fork this repository don't import code Made with Python3 (C) @FayasNoushad Copyright per

Fayas Noushad 14 Nov 12, 2022
A Telegram Message Manager Bot by @AbirHasan2005

Messages-Manager-Bot A Telegram Message Manager Bot by @AbirHasan2005. This Bot can delete specific type of messages from Group. I specially use for @

Abir Hasan 32 Nov 12, 2022
Covid19 API. (Currently Scrapes: worldometers)

Covid19-API An opensource Covid19 API (currently uses worldometer only) Output Examples Covid19 Every Country Data Request URL your-ip/api/all Resp

Amresh Prasad Sinha 14 Oct 03, 2022
Repository to access information of stocks in Bombay Stock Exchange.

BSE Repository to access information of stocks in Bombay Stock Exchange. The code in this repository uses BSE API and conclusions made using the code

1 Nov 13, 2021
Using GNU Radio and HackRF One to Receive, Analyze and Send ASK/OOK signals

play_with_ask NIS-8016 Lab A code: Recv.grc/py: Receive signals and match with ASK button using HackRF and GNU radio. I use AM demod block(can also in

Chen Anxue 1 Jul 04, 2022
Group Management Bot

โค๏ธ ๐—ฆ๐—›๐—”๐——๐—œ๐—ฌ๐—ข โค๏ธ A Powerful, Smart And Advance Group Manager ... Written with AioGram , Pyrogram and Telethon... โญ๏ธ Thanks to everyone who starred

Abdisamad Omar Mohamed 4 Dec 01, 2021
An API wrapper for the file.io web service.

๐Ÿ—ƒ๏ธ File.io An API wrapper for the file.io web service. Install $ pip3 install fileio or

nkot56297 1 Dec 18, 2021
GitNews: Github webhooks for Telegram

GitNews - Github webhooks for Telegram Setup: server: clone repo git clone https

Druv Jagdish 1 Feb 14, 2022
Find the best repos to contribute to, right from Discord!

repo-finder-bot Find the best repos to contribute to, right from Discord! Add to your server FAQs Hmm. What's this? This is the Repo Finder Bot, a bot

Skyascii 61 Dec 25, 2022
Discord Webhook Proxy for Roblox payloads.

RoProxy A Discord webhook proxy passthrough for roblox. Setup Your port and endpoint are in the config.json, make sure both app.py and config.json are

PythonSerious 2 Nov 05, 2021
An analysis of the efficiency of the COVID-19 vaccine

VaccineEfficiency ๐Ÿ’‰ An analysis of the efficiency of the COVID-19 vaccine 3 Methods 1๏ธโƒฃ Compare country's vaccination data to number of day- to-day c

Stephanie Younes 1 Dec 10, 2021
๐Ÿ” ๐Ÿ“Š Look up information about anime, manga and much more directly in Discord!

AniSearch The source code of the AniSearch Discord Bot. Contribute You have an idea or found a bug? Open a new issue with detailed explanation. You wa

็งใฏใƒฌใ‚ชใƒณใงใ™ 19 Dec 07, 2022