Problem using with spatialite

[bernardotorres]

It has problem reading properties of SpatialIndex - VirtualSpatialIndex virtual table even though I can run the same SQL in the CLI.

In ipython:

In [1]: from db import DB
In [2]: db = DB(username=None, password=None, hostname='localhost', filename='/home/bernardo/Dropbox/workspace/orache/db.sqlite3', dbtype='sqlite')
Indexing schema. This will take a second...---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-2-a38e0de61324> in <module>()
----> 1 db = DB(username=None, password=None, hostname='localhost', filename='/home/bernardo/Dropbox/workspace/orache/db.sqlite3', dbtype='sqlite')

/home/bernardo/envs/devpy/local/lib/python2.7/site-packages/db.py-0.3.1-py2.7.egg/db/db.py in __init__(self, username, password, hostname, port, filename, dbname, dbtype, schemas, profile, exclude_system_tables, limit)
    757             self.con = sqlite.connect(self.filename)
    758             self.cur = self.con.cursor()
--> 759             self._create_sqlite_metatable()
    760         elif self.dbtype=="mysql":
    761             if not HAS_MYSQL:

/home/bernardo/envs/devpy/local/lib/python2.7/site-packages/db.py-0.3.1-py2.7.egg/db/db.py in _create_sqlite_metatable(self)
   1170         tables = [row[0] for row in self.cur.execute("select name from sqlite_master where type='table';")]
   1171         for table in tables:
-> 1172             for row in self.cur.execute("pragma table_info(%s)" % table):
   1173                 rows_to_insert.append((table, row[1], row[2]))
   1174         # find for table and column names

OperationalError: no such module: VirtualSpatialIndex

[bernardotorres]

So, some findings: It doesn't matter on spatialite version. I've tested with spatialite 4.2.0 with the same result. What I have done to run was execute this before querying for SpatialIndex table:

self.con.enable_load_extension(True)
self.cur.execute("SELECT load_extension('/usr/local/lib/mod_spatialite.so.7');")

The problem is defining the lib location for each installation. An option would be another initialization parameter for DB().

Other solution would be to skip loading this table. A simple "if" would suffice. The class already has a exclude_system_tables option, but is disregarded because this loading is made in:

self._create_sqlite_metatable()

Depending on your idea on how to tackle the problem I can produce a pull request accordingly.

[WindfallLabs]

Same problem here 3 years later: my short-term solution was to add an initialization parameter extensions=[]. If this is not empty, it triggers self.con.enable_load_extension(True) and then iterates over extensions for ext in extensions: self.con.load_extension(ext). Pretty useful when subclassing DB:

class SpatialDB(db.DB):
    def __init__(self, filename):
        db.DB.__init__(self, filename=filename, dbtype="sqlite", extensions=["mod_spatialite"])