Things I Learned... Again: Spatialite with Datasette on Windows

 I'm mostly writing this so that the next time I run into this, I'll have a handy set of notes.

Many months ago, I found out that Datasette on Windows doesn't like this:

            for extension in self.sqlite_extensions:

                # "extension" is either a string path to the extension

                 or a 2-item tuple that specifies which entrypoint to load.

                if isinstance(extension, tuple):

                    path, entrypoint = extension

                    conn.execute("SELECT load_extension(?, ?)", [path, entrypoint])

                else:
                    conn.execute("SELECT load_extension(?)", [extension])

instead, it wants to see 

            for extension in self.sqlite_extensions:

                # "extension" is either a string path to the extension

                # or a 2-item tuple that specifies which entrypoint to load.

                #if isinstance(extension, tuple):

                #    path, entrypoint = extension

                #    conn.execute("SELECT load_extension(?, ?)", [path, entrypoint])

                #else:

                conn.execute("SELECT load_extension('C:\Windows\System32\mod_spatialite.dll')")

The convenient thing about all this is that datasette will tell you where to find the code to change:

  File "AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\datasette\app.py", line 713, in _prepare_connection

    conn.execute("SELECT load_extension(?)", [extension])

sqlite3.OperationalError: The specified module could not be found.

NOTE: pip show datasette will also show you the path.

One day, one day, I will work on debugging the root cause and maybe making a pull request!