Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Query all statuses from Mastodon SQL database, extract the hashtags from the text, and update the tags and statuses_tags tables
#!/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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment