Wikipedia:Database reports/User preferences/Configuration

userprefs.py

#! /usr/bin/env python
# Public domain; MZMcBride; 2011

import oursql
import wikitools

import settings

report_title = settings.rootpage + 'User preferences'

report_template = u'''\
User preferences statistics; data as of <onlyinclude>~~~~~</onlyinclude>.

== Gender ==
{| class="wikitable sortable plainlinks" style="width:80%%;"
|- style="white-space:nowrap;"
! Gender
! Users
|-
%s
|}

== Language ==
{| class="wikitable sortable plainlinks" style="width:80%%;"
|- style="white-space:nowrap;"
! Language code
! Language name
! Users
|-
%s
|}

== Skin ==
{| class="wikitable sortable plainlinks" style="width:80%%;"
|- style="white-space:nowrap;"
! Skin
! Users
|-
%s
|}

== Gadgets ==
{| class="wikitable sortable plainlinks" style="width:80%%;"
|- style="white-space:nowrap;"
! Gadget
! Users
|-
%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()

gender_output = []
gendered_users = 0
cursor.execute('''
/* userprefs.py SLOW_OK */
SELECT
  up_value,
  COUNT(*)
FROM user_properties
WHERE up_property = 'gender'
GROUP BY up_value;
''')
for row in cursor.fetchall():
    up_value = '{{MediaWiki:gender-%s}}' % row[0]
    count = row[1]
    gendered_users += int(row[1])
    table_row = u'''\
| %s
| %s
|-''' % (up_value, count)
    gender_output.append(table_row)

cursor.execute('''
/* userprefs.py SLOW_OK */
SELECT
  COUNT(*)
FROM user;
''')
all_users = int(cursor.fetchone()[0])
gender_output.append(u'''\
| Undeclared edits wiki pages
| %d
|-''' % (all_users - gendered_users))

language_output = []
cursor.execute('''
/* userprefs.py SLOW_OK */
SELECT
  up_value,
  COUNT(*)
FROM user_properties_anon
WHERE up_property = 'language'
GROUP BY up_value;
''')
for row in cursor.fetchall():
    lang_code = row[0]
    lang_name = '{{#language:%s}}' % row[0]
    count = row[1]
    table_row = u'''\
| %s
| %s
| %s
|-''' % (lang_code, lang_name, count)
    language_output.append(table_row)

skin_output = []
cursor.execute('''
/* userprefs.py SLOW_OK */
SELECT
  up_value,
  COUNT(*)
FROM user_properties_anon
WHERE up_property = 'skin'
GROUP BY up_value;
''')
for row in cursor.fetchall():
    up_value = '{{MediaWiki:skinname-%s}}' % row[0]
    count = row[1]
    table_row = u'''\
| %s
| %s
|-''' % (up_value, count)
    skin_output.append(table_row)

gadgets_output = []
cursor.execute('''
/* userprefs.py SLOW_OK */
SELECT
  up_property,
  COUNT(*)
FROM user_properties_anon
WHERE up_property LIKE 'gadget-%%'
AND up_value = '1'
GROUP BY up_property;
''')
for row in cursor.fetchall():
    up_property = '[[MediaWiki:%s|%s]]' % (row[0], row[0].split('gadget-', 1)[1])
    count = row[1]
    table_row = u'''\
| %s
| %s
|-''' % (up_property, count)
    gadgets_output.append(table_row)

report = wikitools.Page(wiki, report_title)
report_text = report_template % ('\n'.join(gender_output),
                                 '\n'.join(language_output),
                                 '\n'.join(skin_output),
                                 '\n'.join(gadgets_output))
report_text = report_text.encode('utf-8')
report.edit(report_text, summary=settings.editsumm, bot=1)

cursor.close()
conn.close()

crontab

0 22 * * 4 PYTHONPATH=$HOME/scripts python $HOME/scripts/database-reports/userprefs.py > /dev/null