英文:
loading spatialite extension to sqlite3 in python on windows
问题
我正在尝试在Windows上使用Python设置一个带有SQLite数据库的应用程序，并且需要Spatialite扩展。
我已经下载了mod_spatialite二进制文件。
import os
import sqlite3
spatialite_path = r"包含二进制文件的文件夹路径"
os.environ['PATH'] = spatialite_path + ';' + os.environ['PATH']
sys.path = [spatialite_path] + sys.path
conn = sqlite3.Connection(':memory:')
conn.enable_load_extension(True)
conn.load_extension('mod_spatialite') # 失败
conn.load_extension('mod_spatialite.dll') # 失败
conn.load_extension(os.path.join(spatialite_path, 'mod_spatialite.dll')) # 失败
os.chdir(spatialite_path)
assert os.path.exists('mod_spatialite.dll')
conn.load_extension('mod_spatialite') # 失败
对于所有的尝试，过程都失败了：
OperationalError: 无法找到指定的模块。
Python版本为3.10，sqlite3版本为2.6，mod_spatialite版本为5.0.1。
编辑：
这个问题似乎是Spatialite 5.0.1和Python 3.10.11之间的兼容性问题。
编辑#2：
上面的代码在Spyder IDE提供的Python 3.8.10安装中完美运行，但在虚拟环境中的干净Python 3.8.10安装中不起作用。
它还可以在ArcGIS附带的Python 3.9.11中运行。
英文:
I'm trying to set up an application with an sqlite database on windows, using python, and I need the spatialite extension.
I have the mod_spatialite binaries downloaded.
import os; import sys
import sqlite3
spatialite_path = r"folder\with\binaries"
os.environ['PATH'] = spatialite_path + ';' + os.environ['PATH']
sys.path = [spatialite_path] + sys.path
conn = sqlite3.Connection(':memory:')
conn.enable_load_extension(True)
conn.load_extension('mod_spatialite') # fails
conn.load_extension('mod_spatialite.dll') # fails
conn.load_extension(os.path.join(spatialite_path,'mod_spatialite.dll')) #fails
os.chdir(spatialite_path); assert os.path.exists('mod_spatialite.dll'); conn.load_extension('mod_spatialite') # fails
For all, the process fails with:
>OperationalError: The specified module could not be found.
Python version 3.10, sqlite3 version 2.6, mod_spatialite version 5.0.1.
EDIT:
This problem appears to be a compatibility issue between Spatialite 5.0.1 and python 3.10.11
EDIT#2:
The code above works perfectly well with the python 3.8.10 installation provided with the Spyder IDE, but does not work with a clean python 3.8.10 installation in a virtual environment.
It also works with the python 3.9.11 that came with ArcGIS.
答案1
得分: 1
尝试手动将 Spatialite 二进制文件的路径添加到你的 PATH 环境变量中，不要通过代码来添加。
你可能甚至不需要 import spatialite 语句。
最后，使用你的第三个选项 (conn.load_extension(os.path.join(spatialite_path,'mod_spatialite.dll'))) 来加载这个扩展。如果这不起作用，尝试提供实际的字符串路径给 load_extension 方法。并确保在添加到 PATH 后重新启动你的 IDE。
希望这有所帮助。
英文:
Try adding the path to the spatialite binaries to your PATH environment variable manually, not through the code.
You probably don't even need the import spatialite statement.
lastly, use your third option (conn.load_extension(os.path.join(spatialite_path,'mod_spatialite.dll'))) to load the extension. If that doesn't work, try to provide the actual string path to the load_extension method. And make sure to restart your IDE after adding to your PATH.
Hope this helps
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论