jenkins-bot has submitted this change and it was merged.

Change subject: Run a mysql query and yield results by row ......................................................................

Run a mysql query and yield results by row

Allow execution of a mysql query and yield result row by row. Change pagegenerators.MySQLPageGenerator to use this function.

Change-Id: I723ab533cabc0f59e5a0d7752c513b9ddb1f343c --- A pywikibot/data/mysql.py M pywikibot/pagegenerators.py 2 files changed, 91 insertions(+), 31 deletions(-)

Approvals: John Vandenberg: Looks good to me, approved jenkins-bot: Verified

diff --git a/pywikibot/data/mysql.py b/pywikibot/data/mysql.py new file mode 100644 index 0000000..afe5993 --- /dev/null +++ b/pywikibot/data/mysql.py @@ -0,0 +1,78 @@ +# -*- coding: utf-8 -*- +"""Miscellaneous helper functions for mysql queries.""" +# +# (C) Pywikibot team, 2016 +# +# Distributed under the terms of the MIT license. +# +from __future__ import absolute_import, print_function, unicode_literals +__version__ = '$Id$' + +# Requires oursql https://pythonhosted.org/oursql/ or +# MySQLdb https://sourceforge.net/projects/mysql-python/ +try: + import oursql as mysqldb +except ImportError: + import MySQLdb as mysqldb + +import pywikibot + +from pywikibot import config2 as config + + +def mysql_query(query, params=(), dbname=None, encoding='utf-8', verbose=None): + """ + Yield rows from a MySQL query. + + An example query that yields all ns0 pages might look like:: + + SELECT + page_namespace, + page_title, + FROM page + WHERE page_namespace = 0; + + @param query: MySQL query to execute + @type query: str + @param params: input parametes for the query, if needed + @type params: tuple + @param dbname: db name + @type dbname: str + @param encoding: encoding used by the database + @type encoding: str + @param verbose: if True, print query to be executed; + if None, config.verbose_output will be used. + @type verbose: None or bool + @return: generator which yield tuples + """ + if verbose is None: + verbose = config.verbose_output + + if config.db_connect_file is None: + conn = mysqldb.connect(config.db_hostname, + db=config.db_name_format.format(dbname), + user=config.db_username, + passwd=config.db_password, + port=config.db_port) + else: + conn = mysqldb.connect(config.db_hostname, + db=config.db_name_format.format(dbname), + read_default_file=config.db_connect_file, + port=config.db_port) + + cursor = conn.cursor() + if verbose: + pywikibot.output('Executing query:\n%s' % query) + query = query.encode(encoding) + params = tuple(p.encode(encoding) for p in params) + + if params: + cursor.execute(query, params) + else: + cursor.execute(query) + + for row in cursor: + yield row + + cursor.close() + conn.close() diff --git a/pywikibot/pagegenerators.py b/pywikibot/pagegenerators.py index 8981b9d..0e0b1f3 100644 --- a/pywikibot/pagegenerators.py +++ b/pywikibot/pagegenerators.py @@ -2424,7 +2424,7 @@ yield page

-def MySQLPageGenerator(query, site=None): +def MySQLPageGenerator(query, site=None, verbose=None): """ Yield a list of pages based on a MySQL query.

@@ -2437,43 +2437,25 @@ FROM page WHERE page_namespace = 0;

- Requires oursql https://pythonhosted.org/oursql/ or - MySQLdb https://sourceforge.net/projects/mysql-python/ - @param query: MySQL query to execute @param site: Site object @type site: L{pywikibot.site.BaseSite} - @return: iterator of pywikibot.Page + @param verbose: if True, print query to be executed; + if None, config.verbose_output will be used. + @type verbose: None or bool + @return: generator which yield pywikibot.Page """ - try: - import oursql as mysqldb - except ImportError: - import MySQLdb as mysqldb + from pywikibot.data import mysql + if site is None: site = pywikibot.Site()

- if config.db_connect_file is None: - conn = mysqldb.connect(config.db_hostname, - db=config.db_name_format.format(site.dbName()), - user=config.db_username, - passwd=config.db_password, - port=config.db_port) - else: - conn = mysqldb.connect(config.db_hostname, - db=config.db_name_format.format(site.dbName()), - read_default_file=config.db_connect_file, - port=config.db_port) - - cursor = conn.cursor() - pywikibot.output(u'Executing query:\n%s' % query) - query = query.encode(site.encoding()) - cursor.execute(query) - while True: - try: - namespaceNumber, pageName = cursor.fetchone() - except TypeError: - # Limit reached or no more results - break + row_gen = mysql.mysql_query(query, + dbname=site.dbName(), + encoding=site.encoding(), + verbose=verbose) + for row in row_gen: + namespaceNumber, pageName = row if pageName: # Namespace Dict only supports int namespace = site.namespace(int(namespaceNumber))