User:MZMcBride/Sandbox 5: Difference between revisions

< User:MZMcBride Browse history interactively ← Previous editContent deleted Content addedVisual WikitextInline

Revision as of 03:32, 13 January 2014 editMZMcBride (talk \| contribs)Extended confirmed users190,597 edits updated page← Previous edit		Latest revision as of 02:51, 3 December 2015 edit undoMZMcBride (talk \| contribs)Extended confirmed users190,597 edits updated page
Line 1:		Line 1:
			<syntaxhighlight lang="sql">
	mzmcbride@willow:~$ cat scripts/misc/articlecount.py
			select distinct page_title from page join categorylinks on cl_from = page_id where cl_to = 'Wikipedia_bureaucrats' and page_namespace in (2,3) and substring_index(replace(page_title, '_', ' '), '/', 1) not in (select user_name from user join user_groups on ug_user = user_id where ug_group = 'bureaucrat');
	<syntaxhighlight lang="python" enclose="div">
			</syntaxhighlight>
	#! /usr/bin/env python


			<syntaxhighlight lang="sql">
	# Copyright 2010 bjweeks, MZMcBride
			select distinct page_title from page join categorylinks on cl_from = page_id where cl_to = 'Wikipedia_administrators' and page_namespace in (2,3) and substring_index(replace(page_title, '_', ' '), '/', 1) not in (select user_name from user join user_groups on ug_user = user_id where ug_group = 'sysop');
			</syntaxhighlight>

			# ]
	# This program is free software: you can redistribute it and/or modify
			# ]
	# it under the terms of the GNU General Public License as published by
			# ]
	# the Free Software Foundation, either version 3 of the License, or
			# ]
	# (at your option) any later version.
			# ]

			# ]
	# This program is distributed in the hope that it will be useful,
			# ]
	# but WITHOUT ANY WARRANTY; without even the implied warranty of
			# ]
	# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
			# ]
	# GNU General Public License for more details.
			# ]

			# ]
	# You should have received a copy of the GNU General Public License
			# ]
	# along with this program. If not, see <http://www.gnu.org/licenses/>.
			# ]

			# ]
	import datetime
			# ]
	import MySQLdb
			# ]
	import re
			# ]
	import wikitools
			# ]
	import settings
			# ]

			# ]
	report_title = 'Misplaced Pages:List of Wikipedians by article count/Data'
			# ]

			# ]
	report_template = u'''\
			# ]
	{\| class="wikitable sortable"
			# ]
	\|- style="white-space:nowrap;"
			# ]
	! No.
			# ]
	! User
			# ]
	! Article count
			# ]
	\|-
			# ]
	%s
			# ]
	\|}
			# ]
	'''
			# ]

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

			# ]
	conn = MySQLdb.connect(host=settings.host, db=settings.dbname, read_default_file='~/.my.cnf')
			# ]
	cursor = conn.cursor()
			# ]
	cursor.execute('''
			# ]
	/* articlecount.py SLOW_OK */
			# ]
	SELECT
			# ]
	p2.page_creator,
			# ]
	COUNT(*)
			# ]
	FROM enwiki_p.page AS p1
			# ]
	JOIN u_mzmcbride_enwiki_page_creators_p.page AS p2
			# ]
	ON p1.page_id = p2.page_id
			# ]
	AND p1.page_namespace = 0
			# ]
	AND p1.page_is_redirect = 0
			# ]
	GROUP BY p2.page_creator
			# ]
	ORDER BY COUNT(*) DESC
			# ]
	LIMIT 3000;
			# ]
	''')
			# ]

			# ]
	def thous(x): # From http://code.activestate.com/recipes/498181/
			# ]
	return re.sub(r'(\d{3})(?=\d)', r'\1,', str(x))
			# ]

			# ]
	i = 1
			# ]
	output =
			# ]
	for row in cursor.fetchall():
			# ]
	page_creator = u'%s' % unicode(row, 'utf-8')
			# ]
	article_count = thous(str(row))
			# ]
	table_row = u'''\| %d
			# ]
	\| %s
			# ]
	\| %s
			# ]
	\|-''' % (i, page_creator, article_count)
			# ]
	output.append(table_row)
			# ]
	i += 1
			# ]

			# ]
	report = wikitools.Page(wiki, report_title)
			# ]
	report_text = report_template % ('\n'.join(output))
			# ]
	report_text = report_text.encode('utf-8')
			# ]
	report.edit(report_text, summary=settings.editsumm, bot=1)
			# ]

			# ]
	cursor.close()
			# ]
	conn.close()
			# ]
	</syntaxhighlight>
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]
			# ]

Latest revision as of 02:51, 3 December 2015

select distinct page_title from page join categorylinks on cl_from = page_id where cl_to = 'Wikipedia_bureaucrats' and page_namespace in (2,3) and substring_index(replace(page_title, '_', ' '), '/', 1) not in (select user_name from user join user_groups on ug_user = user_id where ug_group = 'bureaucrat');

select distinct page_title from page join categorylinks on cl_from = page_id where cl_to = 'Wikipedia_administrators' and page_namespace in (2,3) and substring_index(replace(page_title, '_', ' '), '/', 1) not in (select user_name from user join user_groups on ug_user = user_id where ug_group = 'sysop');