Install Sqlite3 from Sqlite.org

[KimOllivier]

The default sqlite3.dll installed  with Python does not have enable_extensions compiled into it not just disabled as the python docs suggest. Therefore you cannot add spatial extensions to access geopackage spatial featureclasses from Python in ArcTools. If the sqlite3.dll from Sqlite.org was installed in .../Python/Dlls (that is enabled) we could then load the mod_spatialite.dll extension and supplement ArcTools just as is done with the python numpy module for Grid Tools. This could be done for ArcMap (32 and 64 bit) and ArcPro (64 bit) installations.

By the way, for those of you who cannot wait, you can do this yourself now for your own installations if you have admin rights and are prepared to hack.

[ShaunWalbridge]

Can you confirm this remains an issue for you? The sqlite provided in the Python environment has gone through multiple changes since you posted, and would appreciate your input if this is still impeding your work.

[ShaunWalbridge]

I believe that you can work with libspatialite by installing it from conda-forge:

conda install conda-forge::libspatialite
sqlite3
sqlite> SELECT load_extension('mod_spatialite.dll');

I don't think we have any immediate plans to reconfigure how we deploy sqlite, so will mark this as implemented since it does work via conda, but we can think more about this if it continues to impact your work. 

[Marco_Foi]

Dear @ShaunWalbridge 

on ArcGis Pro 3.3 I stumbled in a variation of this same issue.

Default sqlite3.dll module lacks RTree support as it was probably BUILT WITHOUT the sensible flag.

The result is that IT IS NOT POSSIBLE to create ArcGIS ToolBoxes that issue commands like:

import sqlite3
con = sqlite3.connect("output.gpkg")
con.execute('CREATE VIRTUAL TABLE "rtree_roads_geom" USING rtree("id", "minx", "maxx", "miny", "maxy");')

as this leads to:

sqlite3.OperationalError: no such module: rtree

Such an error prevents to "manually" create Geopackages since this OGC standard requires creating and maintaining spatial indexes.

I believe compiling sqlite3.dll with R*Tree supported (as stated in https://www.sqlite.org/rtree.html) should not hinder any current ArcGIS Pro functionality!

[ShaunWalbridge]

@Marco_Foi Thanks for your report, I can confirm the issue. For now, you can work around this limitation by using the `sqlite3.exe` included within Pro's Python environment, the command will work there and `PRAGMA compile_options` will correctly show the rtree extension as available (and it is present within the sqlite3.dll). If you needed this from Python, you should be able to do a subprocess call to sqlite3, though of course that is inelegant.

For the Python build, it looks like the build environment is picking up a slightly different version of sqlite3 that does not include these extensions. I'll add an issue for us to look at and address in future builds of Python we redistribute. In a separate standalone environment, you could also get the Python client their working with `conda create -n sqlite -c defaults python=3.11` which will have rtree drivers loaded, but wouldn't recommend mixing that with ArcPy and our custom Python environment.

[CordulaGöke]

@ShaunWalbridge I tried to install as you describe but got an error after the first line. the last part of the message:

failed with initial frozen solve. Retrying with flexible solve.
Solving environment: failed

SpecsConfigurationConflictError: Requested specs conflict with configured specs.
requested specs:
- conda-forge::libspatialite
pinned specs:
- arcgispro=3.3
- arcpy-base=3.3
- gdal=3.8.1
- numpy=1.24
- protobuf=4.21.12
- python=3.11
- truststore
Use 'conda config --show-sources' to look for 'pinned_specs' and 'track_features'
configuration parameters. Pinned specs may also be defined in the file
C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\conda-meta\pinned.

This attempt was supposed to be my workaround for working with gpkg. I have posted a separate idea to fully support gpkg since it has become a standard for data exchange and is a format that supports many data types and bid datasets.

https://community.esri.com/t5/arcgis-pro-ideas/full-functionality-for-gpkg-in-arcgis-pro/idi-p/14915...

[ShaunWalbridge]

@CordulaGöke Thanks for your report. We upgraded to a modern supported version of libxml2, and it looks like the builds of libspatialite on both defaults and conda-forge do not support such a new libxml2. If you're OK working in a standalone environment, you can create one with `conda create -n libspatialite libspatialite` and it should work, but would be decoupled from the rest of Pro. Hopefully the other channels catch up on dependencies soon and have a release compatible with Pro, we potentially could package it but it is non-trivial given libspatialite's large number of dependencies.

[CordulaGöke]

@ShaunWalbridgefor me it would have been the workaround to work with gpkg in ArcGIS Pro. So now I just have to remember that I do that  in R and live with the extra work and limitations of other data formats. Since it is not working at the moment, you might consider to change the status of the idea. As it is now, we cannot vote it up. And your instructions from above also lead to that the python environment got destroyed, since I did not remember to make a clone. So I had to reinstall. If you can edit, it would be good to add the cloning as a first step.

[Shaun-Walbridge]

@KimOllivier @Marco_Foi You are correct this was an issue, it wasn't entirely clear to me from the original posts here when I had marked this as already offered, but more recently a support request came in which clarified the issue: the `sqlite.exe` included within our distribution (and the associated sqlite.dll) do have the extensions enabled as expected. However, the CPython build itself is built against SQLite during its compilation, and it turns out that that particular copy was built in such a way that the extensions were not included, which affects `import sqlite3` from a Python session and equivalent uses through the Python API. This has been addressed in Pro 3.5, and will be possible to upgrade to for Pro 3.3 and 3.4 users by upgrading to a newer build of Python itself.

[Shaun-Walbridge]

@CordulaGökeI'm sorry to hear about the data loss. For the future, it is possible to forward and backward migrate a conda environment, if you run `conda list --revisions` you'll see the various states, and an earlier one can be restored to. That said, yes cloning and custom environments are helpful to isolate issues from unwanted changes.