User:DreamRimmer/globalrenamerstats.py

"""
Copyright (c) 2024 DreamRimmer

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
"""

# to update global renamers stats, See [[User:DreamRimmer/Global renamer stats]]
import pywikibot, pymysql
from datetime import datetime
from db_config import db_config

site = pywikibot.Site('meta', 'meta')
stats_page = 'User:DreamRimmer/Global renamer stats'

def get_db_connection():
    try:
        return pymysql.connect(**db_config)
    except Exception as e:
        print("db connection failed:", e)
        exit()

def renamers_data():
    connection = get_db_connection()
    cursor = connection.cursor()
    try:
        query = """SELECT a.actor_name, ug.ug_group AS role, COUNT(log.log_id) AS num_actions, MAX(log.log_timestamp) AS last_action FROM user_groups ug JOIN actor_user a ON a.actor_user = ug.ug_user LEFT JOIN logging_userindex log ON log.log_actor = a.actor_id AND log.log_type = 'renameuser' WHERE ug.ug_group IN ('global-renamer', 'steward', 'bot') GROUP BY a.actor_name, ug.ug_group HAVING COUNT(log.log_id) > 0 OR ug.ug_group NOT IN ('bot', 'steward') UNION ALL SELECT a.actor_name, 'service account' AS role, COUNT(log.log_id) AS num_actions, MAX(log.log_timestamp) AS last_action FROM actor_user a LEFT JOIN logging_userindex log ON log.log_actor = a.actor_id AND log.log_type = 'renameuser' WHERE a.actor_name = 'AccountVanishRequests' ORDER BY last_action DESC;"""
        cursor.execute(query)
        rows = cursor.fetchall()
        decoded_rows = []
        for actor_name, role, num_actions, last_action in rows:
            actor_name = actor_name.decode('utf-8') if actor_name else ''
            last_action = last_action.decode('utf-8') if last_action else ''
            role = role.decode('utf-8') if role else ''
            if last_action:
                dt = datetime.strptime(last_action, '%Y%m%d%H%M%S')
                formatted_timestamp = dt.strftime('%b %d, %Y %I:%M %p')
                decoded_rows.append((actor_name, num_actions, formatted_timestamp, role))
            else:
                decoded_rows.append((actor_name, num_actions, 'No data available', role))
        return decoded_rows
    except Exception as e:
        print("db query failed:", e)
        return []
    finally:
        connection.close()

def create_wikitable(rows):
    current_timestamp = datetime.now().strftime('%H:%M, %d %B %Y (UTC)')
    data_as_of = f'Data as of {current_timestamp}'
    lines = [
        data_as_of,
        '{| class="wikitable sortable"',
        '|-',
        '! class="sortable" | Renamer !! Number of actions !! Last action !! Role'
    ]
    for name, num_actions, action, role in rows:
        lines.append(f'|-\n| [[User:{name}|{name}]]\n| {num_actions}\n| {action}\n| {role}')
    lines.append('|}')
    return '\n'.join(lines)

def post(site, title, content):
    page = pywikibot.Page(site, title)
    print(content)
    page.text = content
    page.save(summary="Global renamers stats (bot)", bot=True, minor=True)

def main():
    rows = renamers_data()
    if rows:
        wikitable = create_wikitable(rows)
        post(site, stats_page, wikitable)
    else:
        print("No data retrieved")

main()

##############################################################################################################

#db_config.py (chmod 700 db_config.py)
db_config = {
    'host': 'metawiki.analytics.db.svc.wikimedia.cloud',
    'user': '', #db_username
    'password': '', #db_password
    'database': 'metawiki_p',
}