Wikipedia:Database reports/Thanks usage/Configuration

thanksstats.py

#! /usr/bin/env python
# Public domain; MZMcBride, Edgars2007; 2017

import oursql
import wikitools

import settings

report_title = settings.rootpage + 'Thanks usage'

report_template = u'''\
[[mw:Extension:Thanks|Thanks]] usage statistics; \
data as of <onlyinclude>~~~~~</onlyinclude>.

== Given ==
{| class="wikitable sortable plainlinks"
|- style="white-space:nowrap;"
! No.
! User
! Count
|-
%s
|}

== Received ==
{| class="wikitable sortable plainlinks"
|- style="white-space:nowrap;"
! No.
! User
! Count
|-
%s
|}

== Uses per day ==
{| class="wikitable sortable plainlinks"
|- style="white-space:nowrap;"
! Day
! Uses
|-
%s
|- class="sortbottom"
! Total
! style="text-align:left;" | %s
|}

== Pairs ==
{| class="wikitable sortable plainlinks"
|- style="white-space:nowrap;"
! User 1
! User 2
! Count
|-
%s
|}
'''

wiki = wikitools.Wiki(settings.apiurl)
wiki.login(settings.username, settings.password)

conn = oursql.connect(host=settings.host,
                      db=settings.dbname,
                      read_default_file='~/.my.cnf')
cursor = conn.cursor()

givers = []
i = 1
cursor.execute('''
/* thanksstats.py SLOW_OK */
SELECT
  user_name,
  COUNT(*)
FROM logging_userindex
JOIN user
ON log_user = user_id
WHERE log_type = 'thanks'
AND log_action = 'thank'
GROUP BY user_name
HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC
LIMIT 2000;
''')
for row in cursor.fetchall():
    user_name = unicode(row[0], 'utf-8')
    count = '{:,.0f}'.format(row[1])
    table_row = u'''\
| %d
| %s
| %s
|-''' % (i, user_name, count)
    givers.append(table_row)
    i += 1

receivers = []
i = 1
cursor.execute('''
/* thanksstats.py SLOW_OK */
SELECT
  log_title,
  COUNT(*)
FROM logging_userindex
WHERE log_type = 'thanks'
AND log_action = 'thank'
GROUP BY log_title
HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC
LIMIT 2000;
''')
for row in cursor.fetchall():
    user_name = unicode(row[0], 'utf-8').replace('_', ' ')
    count = '{:,.0f}'.format(row[1])
    table_row = u'''\
| %d
| %s
| %s
|-''' % (i, user_name, count)
    receivers.append(table_row)
    i += 1

days = []
total = 0
cursor.execute('''
/* thanksstats.py SLOW_OK */
SELECT
  DATE(CONCAT(YEAR(log_timestamp),"-",MONTH(log_timestamp),"-",DAY(log_timestamp))) AS day,
  COUNT(log_timestamp) AS uses
FROM logging_userindex
WHERE log_type = 'thanks'
AND log_action = 'thank'
GROUP BY day
ORDER BY day ASC;
''')
for row in cursor.fetchall():
    day = row[0]
    uses = '{:,.0f}'.format(row[1])
    total += int(row[1])
    table_row = u'''\
| %s
| %s
|-''' % (day, uses)
    days.append(table_row)

pairs = []
cursor.execute('''
/* thanksstats.py SLOW_OK */
SELECT
  LEAST(REPLACE(log_title, '_', ' '), log_user_text),
  GREATEST(REPLACE(log_title, '_', ' '), log_user_text),
  COUNT(*)
FROM logging_userindex
WHERE log_type = 'thanks'
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 2000;
''')
for row in cursor.fetchall():
    user_1 = unicode(row[0], 'utf-8')
    user_2 = unicode(row[1], 'utf-8')
    count = '{:,.0f}'.format(row[2])
    table_row = u'''\
| %s
| %s
| %s
|-''' % (user_1, user_2, count)
    pairs.append(table_row)

report = wikitools.Page(wiki, report_title)
report_text = report_template % ('\n'.join(givers),
                                 '\n'.join(receivers),
                                 '\n'.join(days),
                                 total,
                                 '\n'.join(pairs))
report_text = report_text.encode('utf-8')
report.edit(report_text, summary=settings.editsumm, bot=1)

cursor.close()
conn.close()