myles / mastodon-to-sqlite

Save data from Mastodon to a SQLite database

mastodon-to-sqlite

Save data from Mastodon to a SQLite database.

Install

foo@bar:~$ pip install mastodon-to-sqlite

Authentication

First you will need to create an application on your Mastodon server. You can find that on your Mastodon serer.

foo@bar:~$ mastodon-to-sqlite auth
Mastodon domain: mastodon.social

Create a new application here: https://mastodon.social/settings/applications/new
Then navigate to newly created application and paste in the following:

Your access token: xxx

This will write an auth.json file to your current directory containing your access token and Mastodon domain. Use -a other-file.json to save those credentials to a different location.

That -a option is supported by all other commands.

You can verify your authentication by running mastodon-to-sqlite verify-auth.

Retrieving Mastodon followers

The followers command will retrieve all the details about your Mastodon followers.

foo@bar:~$ mastodon-to-sqlite followers mastodon.db

Retrieving Mastodon followings

The followings command will retrieve all the details about your Mastodon followings.

foo@bar:~$ mastodon-to-sqlite followings mastodon.db

Retrieving Mastodon statuses

The statuses command will retrieve all the details about your Mastodon statuses.

foo@bar:~$ mastodon-to-sqlite statuses mastodon.db

Retrieving Mastodon bookmarks

The bookmarks command will retrieve all the details about your Mastodon bookmarks.

foo@bar:~$ mastodon-to-sqlite bookmarks mastodon.db

Retrieving Mastodon favourites

The favourites command will retrieve all the details about your Mastodon favourites.

foo@bar:~$ mastodon-to-sqlite favourites mastodon.db

mastodon-to-sqlite's Issues

AttributeError: 'str' object has no attribute 'keys'

I got this error running:

mastodon-to-sqlite followers mastodon.db

Full trace below:

Importing followers  [#-----------------------------------]  11760
Traceback (most recent call last):
  File "/Users/simon/.local/bin/mastodon-to-sqlite", line 8, in <module>
    sys.exit(cli())
             ^^^^^
  File "/Users/simon/.local/pipx/venvs/mastodon-to-sqlite/lib/python3.11/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/simon/.local/pipx/venvs/mastodon-to-sqlite/lib/python3.11/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/Users/simon/.local/pipx/venvs/mastodon-to-sqlite/lib/python3.11/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/simon/.local/pipx/venvs/mastodon-to-sqlite/lib/python3.11/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/simon/.local/pipx/venvs/mastodon-to-sqlite/lib/python3.11/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/simon/.local/pipx/venvs/mastodon-to-sqlite/lib/python3.11/site-packages/mastodon_to_sqlite/cli.py", line 112, in followers
    service.save_accounts(db, followers, follower_id=account_id)
  File "/Users/simon/.local/pipx/venvs/mastodon-to-sqlite/lib/python3.11/site-packages/mastodon_to_sqlite/service.py", line 196, in save_accounts
    transformer_account(account)
  File "/Users/simon/.local/pipx/venvs/mastodon-to-sqlite/lib/python3.11/site-packages/mastodon_to_sqlite/service.py", line 173, in transformer_account
    for k in account.keys()
             ^^^^^^^^^^^^
AttributeError: 'str' object has no attribute 'keys'

Datasette ergonomic improvements?

Not sure if this is a non-goal or not, but I noticed datasette special-cases the name column when displaying foreign keys, and it really improves the experience of navigating databases created by mastodon-to-sqlite:

Here's a diff that implements this, though it does bring up the question of schema migrations again:

diff --git a/mastodon_to_sqlite/service.py b/mastodon_to_sqlite/service.py
index 5c43833..b2bee0d 100644
--- a/mastodon_to_sqlite/service.py
+++ b/mastodon_to_sqlite/service.py
@@ -36,13 +36,13 @@ def build_database(db: Database):
                 "id": int,
                 "username": str,
                 "url": str,
-                "display_name": str,
+                "name": str,
                 "note": str,
             },
             pk="id",
         )
         accounts_table.enable_fts(
-            ["username", "display_name", "note"], create_triggers=True
+            ["username", "name", "note"], create_triggers=True
         )
 
     if following_table.exists() is False:
@@ -170,10 +170,13 @@ def transformer_account(account: Dict[str, Any]):
     Transformer a Mastodon account, so it can be safely saved to the SQLite
     database.
     """
+    if account.get("display_name"):
+        account["name"] = account["display_name"]
+
     to_remove = [
         k
         for k in account.keys()
-        if k not in ("id", "username", "url", "display_name", "note")
+        if k not in ("id", "username", "url", "name", "note")
     ]
     for key in to_remove:
         del account[key]
diff --git a/tests/test_services.py b/tests/test_services.py
index 7809dca..ec99478 100644
--- a/tests/test_services.py
+++ b/tests/test_services.py
@@ -20,7 +20,7 @@ def test_transformer_account():
         "id": fixtures.ACCOUNT_ONE["id"],
         "username": fixtures.ACCOUNT_ONE["username"],
         "url": fixtures.ACCOUNT_ONE["url"],
-        "display_name": fixtures.ACCOUNT_ONE["display_name"],
+        "name": fixtures.ACCOUNT_ONE["display_name"],
         "note": fixtures.ACCOUNT_ONE["note"],
     }
 

We shouldn't be downloading all the statuses, favourites, and bookmarks on each call.

We should only bring down the latest status, favourites, and bookmarks when we call the CLI.

Auth token should strip whitespace

When I copied in my access token from Mastodon I accidentally included a leading whitespace, which resulted in a confusing error. I think the input box should strip whitespace.