Query all statuses from Mastodon SQL database, extract the hashtags from the text, and update the tags and statuses_tags tables

update-mastodon-hashtags.py

#!/usr/bin/python

import psycopg2
import re

PG_HOST = "localhost"
PG_DB = "mastodon_production"
PG_USER = "postgres"
PG_PASS = "xxx"
ACCOUNT_ID = "xxx"

conn = psycopg2.connect(
    host=PG_HOST,
    database=PG_DB,
    user=PG_USER,
    password=PG_PASS)

def get_hashtag(hashtag):
    cur = conn.cursor()
    cur.execute(f"SELECT id FROM tags WHERE name ILIKE '{hashtag}'")
    tag = cur.fetchone()
    cur.close()

    if tag is None:
        cur = conn.cursor()
        cur.execute(f"INSERT INTO tags(name, created_at, updated_at) VALUES('{hashtag}', current_timestamp, current_timestamp) RETURNING id")
        tag = cur.fetchone()
        cur.close()
        print(f"Created tag: {hashtag}")

    return tag[0]

cur = conn.cursor()
cur.execute(f"SELECT id,text,created_at FROM statuses WHERE account_id = '{ACCOUNT_ID}' ORDER BY created_at ASC")
status = cur.fetchone()
while status != None:
    tags = re.findall(r"#(\w+)", status[1])
    if len(tags) > 0:
        print(f"Status {status[0]}: {status[1]}")
        print(f"Tags: {tags}")
        for tag in tags:
            tag_id = get_hashtag(tag)
            c = conn.cursor()
            c.execute(f"INSERT INTO statuses_tags(status_id, tag_id) VALUES({status[0]}, {tag_id}) ON CONFLICT DO NOTHING")
            c.execute(f"UPDATE tags SET last_status_at = '{status[2]}' WHERE id = {tag_id}")
            c.close()
    status = cur.fetchone()
cur.close()
conn.commit()
conn.close()