# cf. http://paws-public.wmflabs.org/paws-public/User:Jtmorgan/ds4ux/paws-cheatsheet.ipynb
import os 
"""
Your db login credentials are stored in os.environ. 
DO NOT print or run os.environ, or it will expose your credentials in the Notebook
"""
'\nYour db login credentials are stored in os.environ. \nDO NOT print or run os.environ, or it will expose your credentials in the Notebook\n'
# from https://pythonhosted.org/mwreverts/db.html#mwreverts.db.check :
import mwdb
import mwreverts.api
# added (obviously missing from https://pythonhosted.org/mwreverts/db.html#mwreverts.db.check )
import mwreverts.db

# fails with "OperationalError: (pymysql.err.OperationalError) (1045, "Access denied for user 'tools.paws'@'10.68.23.117' (using password: NO)")":
schema = mwdb.Schema("mysql+pymysql://enwiki.labsdb/enwiki_p" +
                         "?read_default_file=~/replica.my.cnf")

def print_revert(revert):
    if revert is None:
        print(None)
    else:
        print(revert.reverting['rev_id'],
              [r['rev_id'] for r in revert.reverteds],
              revert.reverted_to['rev_id'])

reverting, reverted, reverted_to = mwreverts.db.check(schema, 679778587)
---------------------------------------------------------------------------
Empty                                     Traceback (most recent call last)
/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in _do_get(self)
   1121             wait = use_overflow and self._overflow >= self._max_overflow
-> 1122             return self._pool.get(wait, self._timeout)
   1123         except sqla_queue.Empty:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/util/queue.py in get(self, block, timeout)
    144                 if self._empty():
--> 145                     raise Empty
    146             elif timeout is None:

Empty: 

During handling of the above exception, another exception occurred:

OperationalError                          Traceback (most recent call last)
/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2137         try:
-> 2138             return fn()
   2139         except dialect.dbapi.Error as e:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in unique_connection(self)
    327         """
--> 328         return _ConnectionFairy._checkout(self)
    329 

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in _checkout(cls, pool, threadconns, fairy)
    765         if not fairy:
--> 766             fairy = _ConnectionRecord.checkout(pool)
    767 

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in checkout(cls, pool)
    515     def checkout(cls, pool):
--> 516         rec = pool._do_get()
    517         try:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in _do_get(self)
   1137                     with util.safe_reraise():
-> 1138                         self._dec_overflow()
   1139             else:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     59             self._exc_info = None   # remove potential circular references
---> 60             compat.reraise(exc_type, exc_value, exc_tb)
     61         else:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    186             raise value.with_traceback(tb)
--> 187         raise value
    188 

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in _do_get(self)
   1134                 try:
-> 1135                     return self._create_connection()
   1136                 except:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in _create_connection(self)
    332 
--> 333         return _ConnectionRecord(self)
    334 

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in __init__(self, pool, connect)
    460         if connect:
--> 461             self.__connect(first_connect_check=True)
    462         self.finalize_callback = deque()

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in __connect(self, first_connect_check)
    650             self.starttime = time.time()
--> 651             connection = pool._invoke_creator(self)
    652             pool.logger.debug("Created new connection %r", connection)

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/strategies.py in connect(connection_record)
    104                             return connection
--> 105                 return dialect.connect(*cargs, **cparams)
    106 

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/default.py in connect(self, *cargs, **cparams)
    392     def connect(self, *cargs, **cparams):
--> 393         return self.dbapi.connect(*cargs, **cparams)
    394 

/srv/paws/lib/python3.4/site-packages/pymysql/__init__.py in Connect(*args, **kwargs)
     89     from .connections import Connection
---> 90     return Connection(*args, **kwargs)
     91 

/srv/paws/lib/python3.4/site-packages/pymysql/connections.py in __init__(self, host, user, password, database, port, unix_socket, charset, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, ssl, read_default_group, compress, named_pipe, no_delay, autocommit, db, passwd, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address)
    698         else:
--> 699             self.connect()
    700 

/srv/paws/lib/python3.4/site-packages/pymysql/connections.py in connect(self, sock)
    927             self._get_server_information()
--> 928             self._request_authentication()
    929 

/srv/paws/lib/python3.4/site-packages/pymysql/connections.py in _request_authentication(self)
   1147         self.write_packet(data)
-> 1148         auth_packet = self._read_packet()
   1149 

/srv/paws/lib/python3.4/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
   1009         packet = packet_type(buff, self.encoding)
-> 1010         packet.check_error()
   1011         return packet

/srv/paws/lib/python3.4/site-packages/pymysql/connections.py in check_error(self)
    392             if DEBUG: print("errno =", errno)
--> 393             err.raise_mysql_exception(self._data)
    394 

/srv/paws/lib/python3.4/site-packages/pymysql/err.py in raise_mysql_exception(data)
    106     errorclass = error_map.get(errno, InternalError)
--> 107     raise errorclass(errno, errval)

OperationalError: (1045, "Access denied for user 'tools.paws'@'10.68.23.117' (using password: NO)")

The above exception was the direct cause of the following exception:

OperationalError                          Traceback (most recent call last)
<ipython-input-15-15bb9d1df36d> in <module>()
      6 # fails with "OperationalError: (pymysql.err.OperationalError) (1045, "Access denied for user 'tools.paws'@'10.68.23.117' (using password: NO)")":
      7 schema = mwdb.Schema("mysql+pymysql://enwiki.labsdb/enwiki_p" +
----> 8                          "?read_default_file=~/replica.my.cnf")
      9 
     10 def print_revert(revert):

/srv/paws/lib/python3.4/site-packages/mwdb/schema.py in __init__(self, engine_or_url, only_tables, *args, **kwargs)
    112 
    113         self.meta = MetaData(bind=self.engine)
--> 114         self.meta.reflect(views=True, only=only_tables or self.ONLY_TABLES)
    115         self.public_replica = 'revision_userindex' in self.meta
    116         """

/srv/paws/lib/python3.4/site-packages/sqlalchemy/sql/schema.py in reflect(self, bind, schema, views, only, extend_existing, autoload_replace, **dialect_kwargs)
   3764             bind = _bind_or_error(self)
   3765 
-> 3766         with bind.connect() as conn:
   3767 
   3768             reflect_opts = {

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/base.py in connect(self, **kwargs)
   2080         """
   2081 
-> 2082         return self._connection_cls(self, **kwargs)
   2083 
   2084     def contextual_connect(self, close_with_result=False, **kwargs):

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/base.py in __init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events)
     88         else:
     89             self.__connection = connection \
---> 90                 if connection is not None else engine.raw_connection()
     91             self.__transaction = None
     92             self.__savepoint_seq = 0

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/base.py in raw_connection(self, _connection)
   2166         """
   2167         return self._wrap_pool_connect(
-> 2168             self.pool.unique_connection, _connection)
   2169 
   2170 

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2140             if connection is None:
   2141                 Connection._handle_dbapi_exception_noconnection(
-> 2142                     e, dialect, self)
   2143             else:
   2144                 util.reraise(*sys.exc_info())

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception_noconnection(cls, e, dialect, engine)
   1454             util.raise_from_cause(
   1455                 sqlalchemy_exception,
-> 1456                 exc_info
   1457             )
   1458         else:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info)
    201     exc_type, exc_value, exc_tb = exc_info
    202     cause = exc_value if exc_value is not exception else None
--> 203     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    204 
    205 if py3k:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    184             value.__cause__ = cause
    185         if value.__traceback__ is not tb:
--> 186             raise value.with_traceback(tb)
    187         raise value
    188 

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2136         dialect = self.dialect
   2137         try:
-> 2138             return fn()
   2139         except dialect.dbapi.Error as e:
   2140             if connection is None:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in unique_connection(self)
    326 
    327         """
--> 328         return _ConnectionFairy._checkout(self)
    329 
    330     def _create_connection(self):

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in _checkout(cls, pool, threadconns, fairy)
    764     def _checkout(cls, pool, threadconns=None, fairy=None):
    765         if not fairy:
--> 766             fairy = _ConnectionRecord.checkout(pool)
    767 
    768             fairy._pool = pool

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in checkout(cls, pool)
    514     @classmethod
    515     def checkout(cls, pool):
--> 516         rec = pool._do_get()
    517         try:
    518             dbapi_connection = rec.get_connection()

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in _do_get(self)
   1136                 except:
   1137                     with util.safe_reraise():
-> 1138                         self._dec_overflow()
   1139             else:
   1140                 return self._do_get()

/srv/paws/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     58             exc_type, exc_value, exc_tb = self._exc_info
     59             self._exc_info = None   # remove potential circular references
---> 60             compat.reraise(exc_type, exc_value, exc_tb)
     61         else:
     62             if not compat.py3k and self._exc_info and self._exc_info[1]:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    185         if value.__traceback__ is not tb:
    186             raise value.with_traceback(tb)
--> 187         raise value
    188 
    189 else:

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in _do_get(self)
   1133             if self._inc_overflow():
   1134                 try:
-> 1135                     return self._create_connection()
   1136                 except:
   1137                     with util.safe_reraise():

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in _create_connection(self)
    331         """Called by subclasses to create a new ConnectionRecord."""
    332 
--> 333         return _ConnectionRecord(self)
    334 
    335     def _invalidate(self, connection, exception=None):

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in __init__(self, pool, connect)
    459         self.__pool = pool
    460         if connect:
--> 461             self.__connect(first_connect_check=True)
    462         self.finalize_callback = deque()
    463 

/srv/paws/lib/python3.4/site-packages/sqlalchemy/pool.py in __connect(self, first_connect_check)
    649         try:
    650             self.starttime = time.time()
--> 651             connection = pool._invoke_creator(self)
    652             pool.logger.debug("Created new connection %r", connection)
    653             self.connection = connection

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/strategies.py in connect(connection_record)
    103                         if connection is not None:
    104                             return connection
--> 105                 return dialect.connect(*cargs, **cparams)
    106 
    107             creator = pop_kwarg('creator', connect)

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/default.py in connect(self, *cargs, **cparams)
    391 
    392     def connect(self, *cargs, **cparams):
--> 393         return self.dbapi.connect(*cargs, **cparams)
    394 
    395     def create_connect_args(self, url):

/srv/paws/lib/python3.4/site-packages/pymysql/__init__.py in Connect(*args, **kwargs)
     88     """
     89     from .connections import Connection
---> 90     return Connection(*args, **kwargs)
     91 
     92 from . import connections as _orig_conn

/srv/paws/lib/python3.4/site-packages/pymysql/connections.py in __init__(self, host, user, password, database, port, unix_socket, charset, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, ssl, read_default_group, compress, named_pipe, no_delay, autocommit, db, passwd, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address)
    697             self._sock = None
    698         else:
--> 699             self.connect()
    700 
    701     def _create_ssl_ctx(self, sslp):

/srv/paws/lib/python3.4/site-packages/pymysql/connections.py in connect(self, sock)
    926 
    927             self._get_server_information()
--> 928             self._request_authentication()
    929 
    930             if self.sql_mode is not None:

/srv/paws/lib/python3.4/site-packages/pymysql/connections.py in _request_authentication(self)
   1146 
   1147         self.write_packet(data)
-> 1148         auth_packet = self._read_packet()
   1149 
   1150         # if authentication method isn't accepted the first byte

/srv/paws/lib/python3.4/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
   1008 
   1009         packet = packet_type(buff, self.encoding)
-> 1010         packet.check_error()
   1011         return packet
   1012 

/srv/paws/lib/python3.4/site-packages/pymysql/connections.py in check_error(self)
    391             errno = self.read_uint16()
    392             if DEBUG: print("errno =", errno)
--> 393             err.raise_mysql_exception(self._data)
    394 
    395     def dump(self):

/srv/paws/lib/python3.4/site-packages/pymysql/err.py in raise_mysql_exception(data)
    105         errval = data[3:].decode('utf-8', 'replace')
    106     errorclass = error_map.get(errno, InternalError)
--> 107     raise errorclass(errno, errval)

OperationalError: (pymysql.err.OperationalError) (1045, "Access denied for user 'tools.paws'@'10.68.23.117' (using password: NO)")
# from https://pythonhosted.org/mwreverts/db.html#mwreverts.db.check :
import mwdb
import mwreverts.api
# added (obviously missing from https://pythonhosted.org/mwreverts/db.html#mwreverts.db.check )
import mwreverts.db
import pymysql

# amended per https://paws-public.wmflabs.org/paws-public/User:YuviPanda/replicahelper.ipynb :
host = os.environ['MYSQL_HOST']
user = os.environ['MYSQL_USERNAME']
password = os.environ['MYSQL_PASSWORD']
connection_string = 'mysql+pymysql://{user}:{password}@{host}/?charset=utf8mb4'.format(
    user=user,
    password=password,
    host=host
)
schema =pool_recycle=300ma(connection_string)

def print_revert(revert):
    if revert is None:
        print(None)
    else:
        print(revert.reverting['rev_id'],
              [r['rev_id'] for r in revert.reverteds],
              revert.reverted_to['rev_id'])

reverting, reverted, reverted_to = mwreverts.db.check(schema, 679778587)
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-18-710466ecc480> in <module>()
     15     host=host
     16 )
---> 17 schema = mwdb.Schema(connection_string)
     18 
     19 def print_revert(revert):

/srv/paws/lib/python3.4/site-packages/mwdb/schema.py in __init__(self, engine_or_url, only_tables, *args, **kwargs)
    112 
    113         self.meta = MetaData(bind=self.engine)
--> 114         self.meta.reflect(views=True, only=only_tables or self.ONLY_TABLES)
    115         self.public_replica = 'revision_userindex' in self.meta
    116         """

/srv/paws/lib/python3.4/site-packages/sqlalchemy/sql/schema.py in reflect(self, bind, schema, views, only, extend_existing, autoload_replace, **dialect_kwargs)
   3783 
   3784             available = util.OrderedSet(
-> 3785                 bind.engine.table_names(schema, connection=conn))
   3786             if views:
   3787                 available.update(

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/base.py in table_names(self, schema, connection)
   2117             if not schema:
   2118                 schema = self.dialect.default_schema_name
-> 2119             return self.dialect.get_table_names(conn, schema)
   2120 
   2121     def has_table(self, table_name, schema=None):

<string> in get_table_names(self, connection, schema, **kw)

/srv/paws/lib/python3.4/site-packages/sqlalchemy/engine/reflection.py in cache(fn, self, con, *args, **kw)
     40     info_cache = kw.get('info_cache', None)
     41     if info_cache is None:
---> 42         return fn(self, con, *args, **kw)
     43     key = (
     44         fn.__name__,

/srv/paws/lib/python3.4/site-packages/sqlalchemy/dialects/mysql/base.py in get_table_names(self, connection, schema, **kw)
   1711             rp = connection.execute(
   1712                 "SHOW FULL TABLES FROM %s" %
-> 1713                 self.identifier_preparer.quote_identifier(current_schema))
   1714 
   1715             return [row[0]

/srv/paws/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py in quote_identifier(self, value)
   2873 
   2874         return self.initial_quote + \
-> 2875             self._escape_identifier(value) + \
   2876             self.final_quote
   2877 

/srv/paws/lib/python3.4/site-packages/sqlalchemy/dialects/mysql/mysqldb.py in _escape_identifier(self, value)
     76 
     77     def _escape_identifier(self, value):
---> 78         value = value.replace(self.escape_quote, self.escape_to_quote)
     79         return value.replace("%", "%%")
     80 

AttributeError: 'NoneType' object has no attribute 'replace'
# test