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
Repositorio dedicado a contener los archivos fuentes del bot de discord "Lector de Ejercicios".

Lector de Ejercicios Este bot de discord está pensado para usarse únicamente en el discord de la materia Algoritmos y Programación I, de la Facultad d

Franco Lighterman Reismann 3 Sep 17, 2022
A Telegram bot to extracting text from images. All languages supported.

OCR Bot A Telegram bot to extracting text from images. All languages supported. Deploy to Heroku Local Deploying Clone the repo git clone https://gith

6 Oct 21, 2022
A powerful, cool and well-made userbot for your Telegram profile with promising extension capabilities.

Telecharm userbot A powerful, fast and simple Telegram userbot written in Python 3 and based on Pyrogram 1.X. Currently in active WIP state, so feel f

Daniil Kovalenko 16 Dec 01, 2022
YARSAW is an Async Python API Wrapper for the Random Stuff API.

Yet Another Random Stuff API Wrapper - YARSAW YARSAW is an Async Python API Wrapper for the Random Stuff API. This module makes it simpler for you to

Bruce 6 Mar 27, 2022
短信发送 Python 程序(包含1000+有效接口)

短信轰炸 Python 程序(包含1000+有效接口) 前言 这是一个爬取网络上在线轰炸的接口,后通过 Python 异步 请求接口以达到 手机短信轰炸 的目的。 此为开源项目,仅供娱乐学习使用,使用者所带来的一切后果与作者无关,使用请遵守相关的法律法规,合理使用,请勿滥用。 食用方法 1. 爬取接

蓝鲸落 10.2k Jan 02, 2023
Monitor your Binance portfolio

Binance Report Bot The intent of this bot is to take a snapshot of your binance wallet, e.g. the current balances and store it for further plotting. I

37 Oct 29, 2022
Python version of PlaceNL's headless bot with automatic access token refresh

Reddit /r/place 2022 headless bot This headless Python bot will automatically login to reddit, obtain access tokens (and refreshes them when they expi

19 May 21, 2022
My telegram bot to download Instagram Profiles

Instagram Profile Get for Telegram My telegram bot to download Instagram Profiles First you have to get a telegrm bot api key from @BotFather Then you

Ali Yoonesi 2 Sep 22, 2022
Discord feeder for AIL

ail-feeder-discord Discord feeder for AIL Warning! Automating user accounts is technically against TOS, so use at your own risk! Discord API https://d

ail project 6 Mar 09, 2022
Seth Userbot with python

SETH-USERBOT DEPLOY TO HEROKU Group Support: String Session : Stay Support 🚀 ❁ LonamiWebs and Telethon © Credits ⚡ THANK YOU VERY MUCH FOR zeinzo Zei

seth 4 Jan 10, 2022
Bender: A Markov Babbler Slack Bot

See the Digital Ocean tutorial for instructions on how to get the basic bot structure in place. Once you have that, set the gunicorn command to run as

Andrew Howard 1 Dec 04, 2021
Smilecreator4 - This site is for people who want to hack or want to learn it!

smilecreator4 This site is for people who want to hack or want to learn it! Furthermore, this program does not work without turning off Antivirus or W

1 Jan 04, 2022
One version package to rule them all, One version package to find them, One version package to bring them all, and in the darkness bind them.

AwesomeVersion One version package to rule them all, One version package to find them, One version package to bring them all, and in the darkness bind

Joakim Sørensen 39 Dec 31, 2022
Multi-purpose bot made with discord.py

PizzaHat Discord Bot A multi-purpose bot for your server! ℹ️ • Info PizzaHat is a multi-purpose bot, made to satisfy your needs, as well as your serve

DTS 28 Dec 16, 2022
discord.xp Bot, counts XP for members

discord.xp Bot, counts XP for members. How to setup and run? You must have an mysql database Download libs from the requirements.txt file Configurize

irwing 4 Feb 05, 2022
Unarchive Bot for Telegram

Telegram UnArchiver Bot UnArchiveBot: 🇬🇧 Bot that allows you to extract supported archive formats in telegram. 🇹🇷 Desteklenen arşiv biçimleri tele

Hüzünlü Artemis [HuzunluArtemis] 25 May 07, 2022
An advanced automatic top.gg dank memer voter that votes automatically for you.

Auto Dank Memer Voter An automatic dank memer voter that sends votes onto top.gg every 12 hours, unless their is captcha. I am working on a captcha de

6 Aug 27, 2022
Example of Telegram local API and aiogram 3.x

Telegram Local Full example of Telegram local application. Contains Telegram Bot API Local Telegram Bot API server based on aiogram Bot API Server ima

Oleg A. 9 Sep 16, 2022
Rust UserBot, Telegram istifadəsini asanlaşdıran bir proyektdir.

RUST USERBOT 🇦🇿 Rust UserBot, Telegram istifadəsini asanlaşdıran bir proyektdir. Qurulum Heroku Serverə qurulum git clone https://github.com/rustres

1 Oct 25, 2021
TeslaPy - A Python implementation based on unofficial documentation of the client side interface to the Tesla Motors Owner API

TeslaPy - A Python implementation based on unofficial documentation of the client side interface to the Tesla Motors Owner API, which provides functiona

Tim Dorssers 233 Dec 30, 2022