Wikipedia:List of Wikipedians by number of edits/script
From Wikipedia, the free encyclopedia
This is the python script used to generate the parent page.
#!/usr/sfw/bin/python
#Mediawiki top editors counter
import MySQLdb,locale
execfile("/u01/u//gmaxwell/.pydb.cf", globals());
class query:
"A simple class to contain a query and its response"
def __init__(self,query):
self.query = query
self.results = ()
def run(self,connection):
cursor = connection.cursor()
cursor.execute("set session transaction isolation level read uncommitted")
cursor.execute(self.query)
self.results = cursor.fetchall()
cursor.close()
locale.setlocale(locale.LC_ALL,'en_US')
connection = MySQLdb.connect(host="localhost", user="gmaxwell", passwd=dbpassword, db="enwiki" )
wcnt = query("""
SELECT user_name, all_ecount, month_all_ecount,
ns_0_ecount, month_0_ecount,
ns_2_ecount, month_2_ecount,
ns_4_ecount, month_4_ecount,
ns_6_ecount, month_6_ecount,
days,
IF(rev_user!=0 AND EXISTS (
SELECT 1 FROM user_groups
WHERE user_groups.ug_user=rev_user
AND ug_group='bot'
),1,0) AS bot,
IF(rev_user!=0 AND EXISTS (
SELECT 1 FROM user_groups
WHERE user_groups.ug_user=rev_user
AND ug_group='sysop'
),1,0) AS sysop,
IF(rev_user!=0,0,1) AS anon,
lmonth_all_ecount
FROM (SELECT rev_user_text AS user_name, rev_user,
COUNT(*) AS all_ecount,
COUNT(IF(rev_timestamp>DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 30 DAY)+0),'%Y%m%d%k%i%s'),'1',NULL)) AS month_all_ecount,
COUNT(IF(page_namespace=0 OR page_namespace=1,'1',NULL)) AS ns_0_ecount,
COUNT(IF((page_namespace=0 OR page_namespace=1) AND
rev_timestamp>DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 30 DAY)+0),'%Y%m%d%k%i%s'),'1',NULL)) AS month_0_ecount, COUNT(IF(page_namespace=2 OR page_namespace=3,'1',NULL)) AS ns_2_ecount,
COUNT(IF((page_namespace=2 OR page_namespace=3) AND
rev_timestamp>DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 30 DAY)+0),'%Y%m%d%k%i%s'),'1',NULL)) AS month_2_ecount, COUNT(IF(page_namespace=4 OR page_namespace=5,'1',NULL)) AS ns_4_ecount,
COUNT(IF((page_namespace=4 OR page_namespace=5) AND
rev_timestamp>DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 30 DAY)+0),'%Y%m%d%k%i%s'),'1',NULL)) AS month_4_ecount, COUNT(IF(page_namespace=6 OR page_namespace=7,'1',NULL)) AS ns_6_ecount,
COUNT(IF((page_namespace=6 OR page_namespace=7) AND
rev_timestamp>DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 30 DAY)+0),'%Y%m%d%k%i%s'),'1',NULL)) AS month_6_ecount, DATEDIFF(NOW(),STR_TO_DATE(MIN(rev_timestamp),'%Y%m%d%k%i%s')) AS days,
COUNT(IF(( rev_timestamp>DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 60 DAY)+0),'%Y%m%d%k%i%s')
AND rev_timestamp<DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 30 DAY)+0),'%Y%m%d%k%i%s')
),'1',NULL)) AS lmonth_all_ecount
FROM revision JOIN page ON rev_page=page_id where (rev_user_text='Mindspillage' or rev_user_text='Gmaxwell' or rev_user_text='MBot' or rev_user_text='24.165.233.150' or rev_user_text='Gdrbot')
GROUP BY rev_user_text
) AS ucounts;
""")
wcnt.run(connection)
connection.close()
def outtable(wcnt_sort):
header="""<TABLE BORDER=0>
<TR>
<TH rowspan="2" align=right style="border-bottom:3px solid black;">Rank</TH>
<TH rowspan="2" style="border-bottom:3px solid black;"> User name</TH>
<TH colspan = "2" align=center style="border-left:3px solid black;">Global</TH>
<TH colspan = "2" align=center style="border-left:3px solid black;">Article</TH>
<TH colspan = "2" align=center style="border-left:3px solid black;">User</TH>
<TH colspan = "2" align=center style="border-left:3px solid black;">Project</TH>
<TH colspan = "2" align=center style="border-left:3px solid black;">Image</TH>
</TR>
<TR>"""
for x in range(5):
header=header+"""<TH align=left style="border-left:3px solid black;border-bottom:3px solid black;">Edits<br>This month</TH>
<TH align=left style="border-bottom:3px solid black;"> Rate <br> Rate </TH>"""
print(header+"</tr>")
n=0;i=1;row_alternate=0;
keys=wcnt_sort.keys()
keys.sort()
keys.reverse()
while (i<=2000 and n<len(keys)):
iinc=0
for urow in wcnt_sort[keys[n]]:
rank=""
if row_alternate:
outrow=["<tr bgcolor=\"#EEEEEE\"><td align=right>"]; row_alternate=0
else:
outrow=["<tr><td align=right>"]; row_alternate=1
days=max(1.0,int(urow[11])+0.0)
mdays=min(30.0,days)
usertext = "[[User:"+urow[0]+"|"+urow[0]+"]]"
if urow[12]==1 or urow[13]==1:
usertext=usertext+"<small>"
if urow[12]==1:
usertext=usertext+" bot"
else:
if urow[14]==0: rank="<b>"+locale.format("%d",i,1)+"</b>";iinc=1;
if urow[13]==1: usertext=usertext+" sysop"
usertext=usertext+"</small>"
else:
if urow[14]==0: rank="<b>"+locale.format("%d",i,1)+"</b>";iinc=1;
if urow[15]>0:
diff=((int(urow[2])/(int(urow[15])+0.0))*100.0)-100.0
if diff<0.0000000001:
difft="<font color=\"red\">("+locale.format("%.0f",diff,1)+"%)</font> "
else:
difft="<font color=\"green\">("+locale.format("%.0f",diff,1)+"%)</font> "
else:
difft=""
outrow.append(rank)
outrow.append("</td><td>")
outrow.append(usertext)
outrow.append("</td>")
def counthunk(outrow,total,month):
outrow.append("<td align=right style=\"border-left:3px solid black;\">")
dt=""
if total==1: dt=difft
outrow.append(locale.format("%d",urow[total],1)+"<br>"+dt+locale.format("%d",urow[month],1)+"</td><td align=right>"+"%.2f<br>"%(int(urow[total])/days)+"%.2f"%(int(urow[month])/mdays))
outrow.append("</td>")
for ns in [1,3,5,7,9]:
counthunk(outrow,ns,ns+1)
outrow.append("</tr>")
print ("".join(outrow))
n+=1;i+=iinc
print ("</table>")
print ("==Main namespace==")
wcnt_sort={}
for i in wcnt.results:
if (i[3]+i[4]) in wcnt_sort:
wcnt_sort[i[3]+i[4]]+=[i]
else:
wcnt_sort[i[3]+i[4]]=[i]
outtable(wcnt_sort)
print ("==All namespaces==")
wcnt_sort={}
for i in wcnt.results:
if (i[1]+i[2]) in wcnt_sort:
wcnt_sort[i[1]+i[2]]+=[i]
else:
wcnt_sort[i[1]+i[2]]=[i]
outtable(wcnt_sort)
