I am developing a Toolbox in ArcGIS Pro 3.3 using the usual Conda Python environment.
I need to manipulate a Geopackage and since ArcPy support for .gpkg is almost non-existing I have to connect to the data source as if it was a plain SQLite DB.
Connection works perfectly.
sqlite_connection = sqlite3.connect("c:\\geopackage.gpkg")
Also executing SQL works. To create a feature-class table I run:
sql = f'''CREATE TABLE "faults" ( "fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "geom" LINESTRING, "fault_id" MEDIUMINT);'''
sqlite_connection .execute(sql)
Then I need to create an RTree index as this is one of the requirements for a geompetry layer in a geodatabase. This is done using a virtual table as stated here:
https://www.geopackage.org/guidance/extensions/rtree_spatial_indexes.html
https://sqlite.org/rtree.html
..but running the code:
sql = f'''CREATE VIRTUAL TABLE "rtree_{table_name}_geom" USING rtree("id", "minx", "maxx", "miny", "maxy");'''
sqlite_connection .execute(sql)
will fail into a:
sqlite3.OperationalError: no such module: rtree
Any ideas how to solve this.. ..or an alternative approach for running an arcpy-enabled script?