Reflecting sqlite database with spellfix1 tables
<p>I'm trying to reflect a database (more specifically the <a href="https://www.plex.tv/" rel="nofollow">plex media server database</a>) which is sqlite based.</p>

<p>I can connect to the DB:</p>

<pre><code>import sqlalchemy as sa
from sqlalchemy.orm import create_session

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.engine.reflection import Inspector

conn = 'sqlite:////..../com.plexapp.plugins.library.db-2016-07-17'
eng = sa.create_engine(conn)
plex = eng.connect()
</code></pre>

<p>I can query the DB:</p>

<pre><code>s = create_session(plex)
r = s.execute('SELECT COUNT(*) FROM METADATA_ITEMS')

r.fetchall()

&gt;&gt;&gt; [(55368,)]
</code></pre>

<p>And I can even inspect all the tables with SQLAlchemy's Inspector</p>

<pre><code>i = Inspector.from_engine(plex)
i.get_sorted_table_and_fkc_names()
&gt;&gt;&gt;&gt; [('metadata_item_views', set()),
 ('cloudsync_files', set()),
 ('library_timeline_entries', set()),
 ('synced_ancestor_items', set()),
 ('schema_migrations', set()),
 ('fts4_tag_titles_stat', set()),
 ('spellfix_tag_titles', set()),
 ('metadata_items', set()),
 ('media_streams', set()),
 ('metadata_relations', set()),
 ('play_queue_generators', set()),
 ...
</code></pre>

<p>However, I can't seem to reflect the entire DB. I'm getting this big ugly exception:</p>

<pre><code>m = sa.MetaData(bind=plex)
m.reflect()
&gt;&gt;&gt;&gt;
   ---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1138                         parameters,
-&gt; 1139                         context)
   1140         except Exception as e:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    449     def do_execute(self, cursor, statement, parameters, context=None):
--&gt; 450         cursor.execute(statement, parameters)
    451 

OperationalError: no such module: spellfix1

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

OperationalError                          Traceback (most recent call last)
&lt;ipython-input-58-8cb90bbdb0ad&gt; in &lt;module&gt;()
      1 m = sa.MetaData(bind=plex)
      2 
----&gt; 3 m.reflect()

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in reflect(self, bind, schema, views, only, extend_existing, autoload_replace, **dialect_kwargs)
   3653 
   3654             for name in load:
-&gt; 3655                 Table(name, self, **reflect_opts)
   3656 
   3657     def append_ddl_listener(self, event_name, listener):

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in __new__(cls, *args, **kw)
    414             except:
    415                 with util.safe_reraise():
--&gt; 416                     metadata._remove_table(name, schema)
    417 
    418     @property

/opt/anaconda3/lib/python3.5/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
---&gt; 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]:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    184         if value.__traceback__ is not tb:
    185             raise value.with_traceback(tb)
--&gt; 186         raise value
    187 
    188 else:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in __new__(cls, *args, **kw)
    409             metadata._add_table(name, schema, table)
    410             try:
--&gt; 411                 table._init(name, metadata, *args, **kw)
    412                 table.dispatch.after_parent_attach(table, metadata)
    413                 return table

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in _init(self, name, metadata, *args, **kwargs)
    482         # circular foreign keys
    483         if autoload:
--&gt; 484             self._autoload(metadata, autoload_with, include_columns)
    485 
    486         # initialize all the column, etc. objects.  done after reflection to

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in _autoload(self, metadata, autoload_with, include_columns, exclude_columns)
    494             autoload_with.run_callable(
    495                 autoload_with.dialect.reflecttable,
--&gt; 496                 self, include_columns, exclude_columns
    497             )
    498         else:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in run_callable(self, callable_, *args, **kwargs)
   1475 
   1476         """
-&gt; 1477         return callable_(self, *args, **kwargs)
   1478 
   1479     def _run_visitor(self, visitorcallable, element, **kwargs):

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py in reflecttable(self, connection, table, include_columns, exclude_columns)
    362             self, connection, table, include_columns, exclude_columns):
    363         insp = reflection.Inspector.from_engine(connection)
--&gt; 364         return insp.reflecttable(table, include_columns, exclude_columns)
    365 
    366     def get_pk_constraint(self, conn, table_name, schema=None, **kw):

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py in reflecttable(self, table, include_columns, exclude_columns)
    561 
    562         for col_d in self.get_columns(
--&gt; 563                 table_name, schema, **table.dialect_kwargs):
    564             found_table = True
    565 

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py in get_columns(self, table_name, schema, **kw)
    367         col_defs = self.dialect.get_columns(self.bind, table_name, schema,
    368                                             info_cache=self.info_cache,
--&gt; 369                                             **kw)
    370         for col_def in col_defs:
    371             # make this easy and only return instances for coltype

&lt;string&gt; in get_columns(self, connection, table_name, schema, **kw)

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py in cache(fn, self, con, *args, **kw)
     52     ret = info_cache.get(key)
     53     if ret is None:
---&gt; 54         ret = fn(self, con, *args, **kw)
     55         info_cache[key] = ret
     56     return ret

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/dialects/sqlite/base.py in get_columns(self, connection, table_name, schema, **kw)
   1181     def get_columns(self, connection, table_name, schema=None, **kw):
   1182         info = self._get_table_pragma(
-&gt; 1183             connection, "table_info", table_name, schema=schema)
   1184 
   1185         columns = []

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/dialects/sqlite/base.py in _get_table_pragma(self, connection, pragma, table_name, schema)
   1478         qtable = quote(table_name)
   1479         statement = "%s%s(%s)" % (statement, pragma, qtable)
-&gt; 1480         cursor = connection.execute(statement)
   1481         if not cursor._soft_closed:
   1482             # work around SQLite issue whereby cursor.description

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in execute(self, object, *multiparams, **params)
    904         """
    905         if isinstance(object, util.string_types[0]):
--&gt; 906             return self._execute_text(object, multiparams, params)
    907         try:
    908             meth = object._execute_on_connection

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _execute_text(self, statement, multiparams, params)
   1052             statement,
   1053             parameters,
-&gt; 1054             statement, parameters
   1055         )
   1056         if self._has_events or self.engine._has_events:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1144                 parameters,
   1145                 cursor,
-&gt; 1146                 context)
   1147 
   1148         if self._has_events or self.engine._has_events:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1339                 util.raise_from_cause(
   1340                     sqlalchemy_exception,
-&gt; 1341                     exc_info
   1342                 )
   1343             else:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info)
    200     exc_type, exc_value, exc_tb = exc_info
    201     cause = exc_value if exc_value is not exception else None
--&gt; 202     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    203 
    204 if py3k:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    183             value.__cause__ = cause
    184         if value.__traceback__ is not tb:
--&gt; 185             raise value.with_traceback(tb)
    186         raise value
    187 

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1137                         statement,
   1138                         parameters,
-&gt; 1139                         context)
   1140         except Exception as e:
   1141             self._handle_dbapi_exception(

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    448 
    449     def do_execute(self, cursor, statement, parameters, context=None):
--&gt; 450         cursor.execute(statement, parameters)
    451 
    452     def do_execute_no_params(self, cursor, statement, context=None):

OperationalError: (sqlite3.OperationalError) no such module: spellfix1 [SQL: 'PRAGMA table_info("spellfix_metadata_titles")']
</code></pre>

<p>I understand that <a href="https://www.sqlite.org/spellfix1.html" rel="nofollow">spellfix1</a> is a feature of sqlite. But I couldn't find any python support/plugin to properly reflect it, or I don't fully understand the exception raised.</p>

<ol>
<li>I'd love an explanation on <strong>why</strong> this is happening (why are these
tables special and cannot be reflected normally?)</li>
<li>Is there any library/extension so that sqlalchemy could support this?</li>
</ol>

<p>Thanks!</p>