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))
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 "