Getting db manager error from QGIS 3 on Mac?

Question

I've got a new QGIS 3 install from Kingchaos on osx, but have few issues. One of them is with db manager and sqlite database.

This is the error I got:

Une erreur est survenue lors de l'éxécution du code Python: 

AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension' 
Traceback (most recent call last):
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_model.py", line 441, in rowCount
    self._refreshIndex(parent, True)
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_model.py", line 490, in _refreshIndex
    if item.populate():
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_model.py", line 167, in populate
    if not connection.connect():
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_plugins/spatialite/plugin.py", line 80, in connect
    return self.connectToUri(uri)
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_plugins/plugin.py", line 109, in connectToUri
    self.db = self.databasesFactory(self, uri)
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_plugins/spatialite/plugin.py", line 66, in databasesFactory
    return SLDatabase(connection, uri)
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_plugins/spatialite/plugin.py", line 109, in __init__
    Database.__init__(self, connection, uri)
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_plugins/plugin.py", line 218, in __init__
    self.connector = self.connectorsFactory(uri)
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_plugins/spatialite/plugin.py", line 112, in connectorsFactory
    return SpatiaLiteDBConnector(uri)
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_plugins/spatialite/connector.py", line 51, in __init__
    self.connection = spatialite_connect(self._connectionInfo())
  File "/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/qgis/utils.py", line 596, in spatialite_connect
    con.enable_load_extension(True)
AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension'


Version de Python : 3.6.4 (v3.6.4:d48ecebad5, Dec 18 2017, 21:07:28) [GCC 4.2.1 (Apple Inc. build 5666) (dot 3)] 
Version de QGIS : 3.0.0-Girona Girona, exported 

Chemin Python :
/Applications/QGIS 3.app/Contents/MacOS/../Resources/python
/Users/arthur/Library/Application Support/QGIS/QGIS3/profiles/default/python
/Users/arthur/Library/Application Support/QGIS/QGIS3/profiles/default/python/plugins
/Applications/QGIS 3.app/Contents/MacOS/../Resources/python/plugins
/Library/Frameworks/GEOS.framework/Versions/3B/Python/3.6/site-packages
/Library/Frameworks/GDAL.framework/Versions/2.2/Python/3.6/site-packages
/Library/Frameworks/Python.framework/Versions/3.6/lib/python36.zip
/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6
/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/lib-dynload
/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages
/Users/arthur/Library/Application Support/QGIS/QGIS3/profiles/default/python
/Users/arthur/Library/Application Support/QGIS/QGIS3/profiles/default/python/plugins/mmqgis/forms

Answer 1

I see the same thing. I'm using a slightly newer version than you (KyngChaos build 3.0.0-3).

I can successfully drag and drop sqlite / spatialite / GPKG from the browser panel, but not add them from Db Manager. This might be a workaround in some cases.

This appears to be a build issue with the standard Python framework on Mac OS X, rather than QGIS specifically.

• see https://riccardo.forina.me/spatialite-for-python-does-not-work-on-osx/
• related https://github.com/docker-library/python/issues/106

To quote the first source (emphasis mine)

You must install Python from scratch, enabling the loadable extension support at compile time.

It seems that this functionality needs to be compiled into the python framework on the mac as it's not enabled by default.

I found the same error attempting to do this in standalone python; I could get it to work with the sqlite3 from the homebrew python setup, but not the official python framework.

This is not an easy end-user change as it involves compiling a custom version of Python. The KyngChaos install pages specifically state that you should be using the standard Python framework, rather than (say) one from MacPorts or Homebrew.

Answer 2

I am getting the same issue and way too scared to start fiddling with the system Python.

My workaround at the moment is to use virtual layers and do the query from there. I found the virtual layer can really slow down and occasionally freeze QGIS so not ideal either...