Wikidata:Database reports/Deletions/Configuration

The most recent version of this script can be found at Github: deletions. The script is run on Toolforge in the deltabot tool account; Toolforge users usually have read-access to all scripts in that tool account.

A potentially outdated version is stored onwiki at User:DeltaBot/source/deletions in order to be permanently available and conveniently accessible; it is being displayed on this page with code formatting. Mind that the onwiki source code might have been slightly altered in order to prevent onlyinclude directives from being effective, and that the wikitext parser im some situations messes up the formatted output of the transcluding page content including the source code.

#!/usr/bin/python
# -*- coding: UTF-8 -*-
#licensed under CC-Zero: https://creativecommons.org/publicdomain/zero/1.0

from os.path import expanduser
from time import strftime

import mariadb
import pywikibot


HEADER_1 = '[[:Wikidata:Database reports/Deletions/Configuration/Header/en]]\n'
HEADER_2 = 'Update: <only' + 'include>{update_timestamp}</onlyinclude>.\n\n{{| class="wikitable sortable plainlinks" style="width:100%; margin:auto;"\n|-\n! {{{{int:userlogin-yourname}}}} !! {{{{int:deletionlog}}}}\n'
TABLE_ROW = '|-\n| [[User:{username}|{username}]] || [{user={username_underscore}} {cnt}]\n'
FOOTER = '|}\n\n[[Category:Wikidata statistics]]\n[[Category:Wikidata administrators]]'


def make_report() -> str:
    db = mariadb.connect(
        host='wikidatawiki.analytics.db.svc.wikimedia.cloud',
        database='wikidatawiki_p',
        default_file=f'{expanduser("~")}/replica.my.cnf'
    )
    cursor = db.cursor(dictionary=True)

    query = """SELECT
    CONVERT(actor_name USING utf8) AS actor_name,
    COUNT(*) AS cnt
FROM
    logging
        JOIN actor ON log_actor=actor_id
WHERE
    log_action="delete"
    AND actor_user IN (
        SELECT
            ug_user
        FROM
            user_groups
        WHERE
            ug_group="sysop"
    )
GROUP BY
    log_actor
ORDER BY
    actor_name"""

    cursor.execute(query)
    text = ''
    for row in cursor:
        username = row.get('actor_name', '')
        text += TABLE_ROW.format(username=username, username_underscore=username.replace(' ', '_'), cnt=row.get('cnt', 0))

    return text


def main():
    text = HEADER_1 + HEADER_2.format(update_timestamp=strftime('%Y-%m-%d %H:%M (%Z)')) + make_report() + FOOTER

    page = pywikibot.Page(pywikibot.Site('wikidata', 'wikidata'), 'Wikidata:Database reports/Deletions')
    page.text = text
    page.save(summary='Bot:Updating database report', minor=False)


if __name__=='__main__':
    main()