def import_from_db(dbname, old=0, cur=get_cur_ts()):
with db.connect(dbname) as cur:
cur.execute(import_query, (ts,cur))
res = cur.fetchall()
urls = []
for x in res:
ser = x[0]
data = phpserialize.loads(ser)
urls.append((data['4::url'], x[1]))
dump(dbname, urls)
def on_msg(**kw):
text = kw['text']
if not text.startswith(('!a', '!l')):
return True
sp = text.split(' ')
if len(sp) < 3:
kw['bot'].queue_msg(kw['channel'],
'!(a|l) log_action username[@dbname]')
type_ = sp[0][1]
action = sp[1]
username = '******'.join(sp[2:])
if '@' in username:
dbname = username.split('@', 1)[1]
username = username.split('@', 1)[0]
else:
dbname = 'enwiki'
query = """
SELECT COUNT(*)
FROM logging_userindex
JOIN user
ON user_id=log_user
WHERE """
if type_ == 'a':
human = 'log actions'
query += 'log_action = ?'
else:
human = 'log entries'
query += 'log_type = ?'
query += '\nAND user_name = ?;'
with db.connect(dbname) as cur:
cur.execute(query, (action, username))
count = cur.fetchone()[0]
kw['bot'].queue_msg(
kw['channel'],
'{0}@{1} has {2} {3} of type {4}'.format(username, dbname, count,
human, action))
return True
def on_msg(**kw):
text = kw['text']
if not text.startswith(('!a', '!l')):
return True
sp = text.split(' ')
if len(sp) < 3:
kw['bot'].queue_msg(kw['channel'], '!(a|l) log_action username[@dbname]')
type_ = sp[0][1]
action = sp[1]
username = '******'.join(sp[2:])
if '@' in username:
dbname = username.split('@', 1)[1]
username = username.split('@', 1)[0]
else:
dbname = 'enwiki'
query = """
SELECT COUNT(*)
FROM logging_userindex
JOIN user
ON user_id=log_user
WHERE """
if type_ == 'a':
human = 'log actions'
query += 'log_action = ?'
else:
human = 'log entries'
query += 'log_type = ?'
query += '\nAND user_name = ?;'
with db.connect(dbname) as cur:
cur.execute(query, (action, username))
count = cur.fetchone()[0]
kw['bot'].queue_msg(kw['channel'], '{0}@{1} has {2} {3} of type {4}'.format(
username, dbname, count, human, action
))
return True
#!/usr/bin/env python
#-*- coding: utf-8 -*-
#Import libs and init them
import urllib
import json
from wmflabs import db
connHeritage = db.connect('s51138__heritage_p')
connWiki = db.connect('cswiki')
warnings = []
#Get all monuments lists in cswiki
cur = connWiki.cursor()
with cur:
sql = 'select page_title from page where page_namespace=0 and page_is_redirect=0 and page_id in (select tl_from from templatelinks where tl_title="Památky_v_Česku");'
cur.execute(sql)
lists = cur.fetchall()
res = {}
for row in lists:
totalZero = False
#For every list fetch required data
rowData = {}
monumentListEncoded = urllib.quote_plus(row[0])
monumentList = row[0]
#Get num of monuments in the list
cur = connHeritage.cursor()
with cur:
sql = 'select count(*) from `monuments_cz_(cs)` where source like "//cs.wikipedia.org/w/index.php?title=' + monumentListEncoded + '&oldid=%";'
if 'labs' in qs:
labs = True
import pymysql
# Load config
__dir__ = os.path.dirname(__file__)
config = yaml.safe_load(open(os.path.join(__dir__, 'config.yaml')))
conn = pymysql.connect(
db=qs['labs'][0],
host=config['DB_HOST'],
user=config['DB_USER'],
password=config['DB_PASS'],
charset="utf8",
)
else:
try:
conn = db.connect(qs['db'][0])
except:
conn = db.connect('commonswiki')
else:
print('nouser')
sys.exit(0)
##### PROGRAM ####
cur = conn.cursor()
with cur:
sql = 'select count(*) from logging_userindex where log_type="upload" and log_user=(select user_id from user where user_name="' + username + '");'
if labs:
sql = sql.replace('_userindex', '')
cur.execute(sql)
data = cur.fetchall()
import sys
from wmflabs import db
#Print header
print 'Content-type: text/html\n'
# Fetch params
if 'QUERY_STRING' in os.environ:
QS = os.environ['QUERY_STRING']
qs = cgi.parse_qs(QS)
try:
username = qs['user'][0]
except:
print 'nouser'
sys.exit(0)
else:
print 'nouser'
sys.exut(0)
##### PROGRAM ####
conn = db.connect('commonswiki')
cur = conn.cursor()
with cur:
sql = 'select count(*) from logging_userindex where log_type="upload" and log_user=(select user_id from user where user_name="' + username + '");'
cur.execute(sql)
data = cur.fetchall()
result = data[0][0]
print result
#!/usr/bin/env python
#-*- coding: utf-8 -*-
##############################################
from xml.sax.saxutils import escape as html_escape
import cgi
import sys
import HTML
import os
from wmflabs import db
conn = db.connect("s53348__mostLinkedMissing")
#Print header
print 'Content-type: text/html\n'
#Print header of html document
print """
<!DOCTYPE html>
<html lang="cs-cz">
<head>
<meta charset="utf-8" />
<title>Chybějící stránky</title>
</head>
<body>
<p><a href="index.php">Zpět</a>
"""
###############FUNCTIONS######################
#Print end header
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
IN THE SOFTWARE.
"""
from __future__ import unicode_literals
import pywikibot
from wmflabs import db as mysql
site = pywikibot.Site('wikidata', 'wikidata')
site.login()
db = mysql.connect('wikidatawiki')
def linkify(page):
title = page.title().split('/', 1)[1]
return '[[{0}|{1}]]'.format(page.title(), title)
def contributors(page, db):
with db.cursor() as cur:
cur.execute("select count(distinct(rev_user_text)) "
"from revision "
"join page "
"on rev_page=page_id "
"where page_namespace=4 "
"and page_title=?", (page.title(withNamespace=False).replace(' ', '_').encode('utf-8'),))
import os
query = """
select
term_entity_id,
term_text
from wb_terms
where term_entity_type="item"
and term_language="en"
and term_type="label"
and term_text like "%)"
limit 1000;
"""
def make_link(_id):
return '<a href="https://www.wikidata.org/wiki/Q{0}">Q{0}</a>'.format(_id)
with db.connect('wikidatawiki') as cur:
cur.execute(query)
data = cur.fetchall()
output = ''
for _id, desc in data:
output += make_link(_id)
output += desc
output += '<br />'
with open(os.path.expanduser('~/public_html/para.html'), 'w') as f:
f.write(output)
# Fetch params
if 'QUERY_STRING' in os.environ:
QS = os.environ['QUERY_STRING']
qs = cgi.parse_qs(QS)
try:
username = qs['user'][0].replace('_', ' ')
except:
print('{"error": "nouser"}')
sys.exit(0)
else:
print('{"error": "nouser"}')
sys.exit(0)
##### PROGRAM ####
conn = db.connect('wikidatawiki')
cur = conn.cursor()
with cur:
sql = 'select count(*) as edit_count from change_tag join revision on rev_id=ct_rev_id where ct_tag_id=155 and rev_actor=(select actor_id from actor where actor_name="' + username + '");'
cur.execute(sql)
data = cur.fetchall()
result = data[0][0]
response = {
'edits': result
}
print(jsonify(response))
#Importování modulu pro práci s objekty spravovanými interpretem
import sys
#Importování knihovny pro práci s databází
from wmflabs import db
#Importování modulu pro práci s datem a časem
import datetime
#Init modulů
#Vytvoření proměnné s kódem wikiprojektu
#wikisite = sys.argv[1] + "wiki"
wikisite = "cswiki"
#Vytvoření proměnné s číslem filtru
#fnum = sys.argv[2]
fnum = 15
#Navázání připojení s databází
conn = db.connect(wikisite)
######################### ZÍSKÁVÁNÍ DAT ###############################
#Vytvoření databázové transakce
cur = conn.cursor()
#Otevření transakce
with cur:
#Nalezení ĺogu pro daný filtr
cur.execute('select * from abuse_filter_log where afl_filter="' + str(fnum) + '"order by afl_timestamp asc')
data = cur.fetchall()
###################### TŘÍDĚNÍ DAT ###############################
#Vytovření proměnné pro roztříděná data
#!/usr/bin/env python
#-*- coding: utf-8 -*-
import datetime
from wmflabs import db
# Get the data from database
conn = db.connect('cswiki')
with conn.cursor() as cur:
sql = '''select pl_title as title from pagelinks left join page on (pl_from=page_id)
where pl_namespace=0 and (page_namespace in (0, 2, 14, 100)
or (page_namespace=4 and (page_title like 'Požadované%' or page_title like 'Cizojazyčné%' or page_title like 'WikiProjekt%')))
and not exists (select * from page where page_namespace=pl_namespace and page_title=pl_title) group by title;'''
cur.execute(sql)
data = cur.fetchall()
# Store the data into tools-db
conn = db.connect('s52964__missingpages_p')
with conn.cursor() as cur:
sql = 'drop table if exists missingPagesNew;'
cur.execute(sql)
with conn.cursor() as cur:
sql = 'create table missingPagesNew(title varchar(256));'
cur.execute(sql)
with conn.cursor() as cur:
sql = 'set charset utf8;'
cur.execute(sql)
for row in data:
try:
user_name = qs['user_name'][0]
except:
print('{"error": "user_name"}')
sys.exit(0)
try:
new_format = qs['format'][0] == 'new'
except:
new_format = False
try:
wiki = qs['wiki'][0]
except:
wiki = 'cswiki'
else:
print('{"error": "user_name"}')
sys.exit(0)
##### PROGRAM ####
conn = db.connect(wiki)
cur = conn.cursor()
with cur:
sql = 'select rc_this_oldid, rc_id from recentchanges_userindex where rc_actor=(select actor_id from actor where actor_name="%s") and rc_patrolled=0;' % user_name
cur.execute(sql)
if new_format:
result = dict(cur.fetchall())
else:
result = []
result.extend(row[1] for row in cur.fetchall())
print(json.dumps(result))
#Importování modulu pro práci s objekty spravovanými interpretem
import sys
#Importování knihovny pro práci s databází
from wmflabs import db
#Importování modulu pro práci s datem a časem
import datetime
#Init modulů
#Vytvoření proměnné s kódem wikiprojektu
#wikisite = sys.argv[1] + "wiki"
wikisite = "cswiki"
#Vytvoření proměnné s číslem filtru
#fnum = sys.argv[2]
fnum = 15
#Navázání připojení s databází
conn = db.connect(wikisite)
######################### ZÍSKÁVÁNÍ DAT ###############################
#Vytvoření databázové transakce
cur = conn.cursor()
#Otevření transakce
with cur:
#Nalezení ĺogu pro daný filtr
cur.execute('select * from abuse_filter_log where afl_filter="' +
str(fnum) + '"order by afl_timestamp asc')
data = cur.fetchall()
###################### TŘÍDĚNÍ DAT ###############################
#Vytovření proměnné pro roztříděná data
query = """
select
term_entity_id,
term_text
from wb_terms
where term_entity_type="item"
and term_language="en"
and term_type="label"
and term_text like "%)"
limit 1000;
"""
def make_link(_id):
return '<a href="https://www.wikidata.org/wiki/Q{0}">Q{0}</a>'.format(_id)
with db.connect('wikidatawiki') as cur:
cur.execute(query)
data = cur.fetchall()
output = ''
for _id, desc in data:
output += make_link(_id)
output += desc
output += '<br />'
with open(os.path.expanduser('~/public_html/para.html'), 'w') as f:
f.write(output)
#!/usr/bin/env python
#-*- coding: utf-8 -*-
##############################################
import cgi
import sys
import os
from wmflabs import db
conn = db.connect("s52964__missingpages_p")
#Print header
print 'Content-type: text/html\n'
#Print header of html document
print """
<!DOCTYPE html>
<html lang="cs-cz">
<head>
<meta charset="utf-8" />
<title>Chybějící stránky</title>
</head>
<body>
<p><a href="index.php">Zpět</a>
"""
###############FUNCTIONS######################
#Print end header
def tail():
print """
</body>
</html>
from wmflabs import db
def ResultIter(cursor, arraysize=1000):
'An iterator that uses fetchmany to keep memory usage down'
while True:
results = cursor.fetchmany(arraysize)
if not results:
break
for result in results:
yield result
# Prepare database for storing
conn = db.connect('s53348__mostLinkedMissing')
with conn.cursor() as cur:
sql = 'drop table if exists mostLinkedMissingNew;'
cur.execute(sql)
with conn.cursor() as cur:
sql = 'create table mostLinkedMissingNew ( namespace int , title varchar(256) , value int );'
cur.execute(sql)
# Get the data from database
conn = db.connect('cswiki')
with conn.cursor() as cur:
sql = "SELECT pl_namespace AS namespace, pl_title AS title, COUNT(*) AS value FROM pagelinks LEFT JOIN page AS pg1 ON pl_namespace = pg1.page_namespace AND pl_title = pg1.page_title LEFT JOIN page AS pg2 ON pl_from = pg2.page_id WHERE pg1.page_namespace IS NULL AND pl_namespace NOT IN ( 2, 3 ) AND pg2.page_namespace NOT IN ( 8, 10 ) GROUP BY pl_namespace, pl_title ORDER BY value;"
cur.execute(sql)
tconn = db.connect('s53348__mostLinkedMissing')
with tconn.cursor() as cur2:
def get_all_db_names():
with db.connect('meta_p') as cur:
cur.execute('SELECT dbname from wiki')
r = cur.fetchall()
for w in r:
yield w[0]
campaigns = [
'studenti',
'seniori',
'knihovny',
]
# Clean data
if os.path.isdir('/data/project/urbanecmbot/mark-students/public/data'):
shutil.rmtree('/data/project/urbanecmbot/mark-students/public/data')
os.mkdir('/data/project/urbanecmbot/mark-students/public/data')
else:
os.mkdir('/data/project/urbanecmbot/mark-students/public/data')
# Regenerate files campaign-users.txt in public iface
rules = []
conn = db.connect('cswiki')
autopatrolled = []
with conn.cursor() as cur:
sql = "select user_name from user where user_id in (select ug_user from user_groups where ug_group='autopatrolled');"
cur.execute(sql)
data = cur.fetchall()
for row in data:
autopatrolled.append(row[0].decode('utf-8'))
for campaign in campaigns:
url = base + campaign + '/users'
r = requests.get(url)
tree = html.fromstring(r.content)
users_raw = tree.xpath('//*[@id="users"]/table/tbody/tr/td/a/text()')
users = []
for user in users_raw:
user = str(user)
#!/usr/bin/env python
#-*- coding: utf-8 -*-
import datetime
from wmflabs import db
# Get the data from database
conn = db.connect('cswiki')
with conn.cursor() as cur:
sql = '''select pl_title as title from pagelinks left join page on (pl_from=page_id)
where pl_namespace=0 and (page_namespace in (0, 2, 14, 100)
or (page_namespace=4 and (page_title like 'Požadované%' or page_title like 'Cizojazyčné%' or page_title like 'WikiProjekt%')))
and not exists (select * from page where page_namespace=pl_namespace and page_title=pl_title) group by title;'''
cur.execute(sql)
data = cur.fetchall()
# Store the data into tools-db
conn = db.connect('s52964__missingpages_p')
with conn.cursor() as cur:
sql = 'drop table if exists missingPagesNew;'
cur.execute(sql)
with conn.cursor() as cur:
sql = 'create table missingPagesNew(title varchar(256));'
cur.execute(sql)
with conn.cursor() as cur:
sql = 'set charset utf8;'
cur.execute(sql)
#!/usr/bin/env python
#-*- coding: utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
from wmflabs import db
import pywikibot
site = pywikibot.Site()
sql = 'select count(*), pl_title from pagelinks where pl_namespace=0 and pl_title in (select page_title from page where page_id in (select cl_from from categorylinks where cl_to="Wikipedie:Rozcestníky") and page_namespace=0) group by pl_title order by count(*) desc limit 500;'
conn = db.connect('cswiki')
cur = conn.cursor()
with cur:
cur.execute(sql)
data = cur.fetchall()
res = u""
for row in data:
rowres = u"# [[" + row[1].encode('utf-8') + u"]] ([[Speciální:Whatlinkshere/" + row[1].encode('utf-8') + u"|" + unicode(row[0]) + u" odkazů]])"
res += rowres + '\n'
page = pywikibot.Page(site, u"Wikipedie:Seznam nejvíce odkazovaných rozcestníků/Odkazy")
page.text = res
page.save(u'Robot: Aktualizace seznamu nejodkazovanějších rozcestníků')
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
IN THE SOFTWARE.
"""
from __future__ import unicode_literals
import pywikibot
from wmflabs import db as mysql
site = pywikibot.Site('wikidata', 'wikidata')
site.login()
db = mysql.connect('wikidatawiki')
def linkify(page):
title = page.title().split('/', 1)[1]
return '[[{0}|{1}]]'.format(page.title(), title)
def contributors(page, db):
with db.cursor() as cur:
cur.execute(
"select count(distinct(rev_user_text)) "
"from revision "
"join page "
"on rev_page=page_id "
"where page_namespace=4 "
