sqlalchemy connection issue in drivers.py
jmarkloew opened this issue · 16 comments
Hi John,
i've recently set up a new testing environment for our SLC-processor on Ubuntu 22.04 with pyroSAR 0.19, SNAP 9 and python 3.10.
I installed pyroSAR via pip.
However, when I start the process and it needs to call init of pyroSAR/drivers.py I get the following error:
File "/home/s1porcessor/.local/lib/python3.10/site-packages/pyroSAR/drivers.py", line 2295, in __init__
conn = self.engine.connect()
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3315, in connect
return self._connection_cls(self, close_with_result=close_with_result)
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
else engine.raw_connection()
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3394, in raw_connection
return self._wrap_pool_connect(self.pool.connect, _connection)
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3364, in _wrap_pool_connect
Connection._handle_dbapi_exception_noconnection(
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2198, in _handle_dbapi_exception_noconnection
util.raise_(
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
return fn()
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 320, in connect
return _ConnectionFairy._checkout(self)
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 884, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 486, in checkout
rec = pool._do_get()
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 256, in _do_get
return self._create_connection()
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 266, in _create_connection
return _ConnectionRecord(self)
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 381, in __init__
self.__connect()
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 677, in __connect
with util.safe_reraise():
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 673, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 578, in connect
return dialect.connect(*cargs, **cparams)
File "/home/s1porcessor/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 598, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file
(Background on this error at: https://sqlalche.me/e/14/e3q8)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/s1porcessor/Documents/s1_processor/s1_processor/slc_processor/slc_processor.py", line 22, in <module>
S1_SLC_proc(**proc)
File "/home/s1porcessor/Documents/s1_processor/s1_processor/slc_processor/functions.py", line 2088, in S1_SLC_proc
with Archive(dbfile= database_path) as archive:
File "/home/s1porcessor/.local/lib/python3.10/site-packages/pyroSAR/drivers.py", line 2299, in __init__
raise RuntimeError('could not load spatialite extension')
RuntimeError: could not load spatialite extension
For sqlite3 and spatialite I have found the following packages:
sqlite3-doc/jammy,jammy 3.37.2-2 all
sqlite3-pcre/jammy 0~git20070120091816+4229ecc-2 amd64
sqlite3-tools/jammy 3.37.2-2 amd64
sqlite3-tools/jammy 3.37.2-2 i386
sqlite3/jammy,now 3.37.2-2 amd64 [installed]
sqlite3/jammy 3.37.2-2 i386
sqlite/jammy 2.8.17-15fakesync1build1 amd64
sqlitebrowser/jammy 3.12.1-2 amd64
spatialite-bin/jammy,now 5.0.1-1build1 amd64 [installed]
spatialite-gui/jammy 2.1.0~beta1-1build1 amd64
Am I missing some packages or extension or is this a error specific to a python or sqlite3 version?
Best regards
Johannes
Hi @jmarkloew. Likely you have to install libsqlite3-mod-spatialite via apt. See here for a Ubuntu build recipe:
https://github.com/johntruckenbrodt/pyroSAR/blob/master/.travis.yml
Unfortunately I have installed libsqlite3-mod-spatialite, I even switched to a new conda environment. So do I have to install them in a certain order?
This is surprising. I haven't had this issue in a long time. The order should be irrelevant. You can try this minimal example to see if sqlite+spatialite are working together as intended:
https://pyrosar.readthedocs.io/en/latest/general/installation.html#sqlite-spatialite
So I've run that minimal example in and it can be executed without any error messages. I even tested it in an additional conda environment.
Puh, I have no clue. @MarkusZehner do you have an idea? I will try to get to it in the next days but it might take a bit.
Thats a long time since I've seen that error..
I'd suggest searching the mod_spatialite* file in your environment, check which extension it has, read permissions etc.. For Ubuntu 'mod_spatialite', 'mod_spatialite.so' are searched as extensions.
Are you creating a new database or do you point to an existing one?
@MarkusZehner I've found mod_spatialite.so in a lib folder of my conda environment. I try to connect to an exisiting one via pyroSARs Archive.
@jmarkloew I was able to reproduce your problem with the following example on Ubuntu 20.04. On Windows 10 this works.
from pyroSAR.drivers import Archive
db_name = 'test.db'
fname = 'S1A_IW_GRDH_1SDV_20180829T170721_20180829T170746_023464_028DE0_5310.zip'
with Archive(db_name) as db:
db.insert(fname)
files = db.select(sensor='S1A')
print('\n'.join(files))edit: so far I have tested Python 3.9 and 3.10.
@johntruckenbrodt so it doesn't work on either python versions for ubuntu 20.04 correct?
I've been looking through the code which produces the error. Unfortunately, the part in question has not been created by myself. This is the part that creates the issue:
scenes = finder(data, [r'^S1[AB].*(SAFE|zip)$'],
regex=True, recursive=True, foldermode=1)
database_path = f'{tmpdir}/scene.db'
with Archive(dbfile= database_path) as archive:
archive.insert(scenes)
@SteveMHill: If I understand it correctly, it creates a data base called scene.db at a temp. directory and filles it with S1-zip files
Yes, neither version works for me. This code you show is basically the same as my example above and you are correct with your interpretation. I will try to to find some time to get into it this week.
A small update.. it works on osx-arm64 for Python 3.9 - 3.11.
@jmarkloew I looked into this and realized that I was actually only able to reproduce your issue on Ubuntu because the directory I was trying to write to did not exist 😅. Could you check that the directory you have defined is writable?
This will make it easier to see whether the directory is writable: fb49b2d
@johntruckenbrodt thanks for the info. Unfortunately I ran conda update pyrosar to see if your newly added error message will trigger. But now _gdal cannot be found anymore: ModuleNotFoundError: No module named '_gdal'. So it may take a bit to check if this is about the directory being writable or not.
@jmarkloew I have not yet published a new version. So far the change I made is only part of the current main branch.
Looks like something went wrong with your conda GDAL installation. It might be easiest to create a new environment. Also, it could be that some external GDAL installation is messing with the one in your conda environment. This could also be the problem with spatialite in case the non-writable directory is not the problem.
Running something like this is a good indicator whether a GDAL installation is well isolated in a conda env:
ldd $(which gdalinfo)Here you can list all the linked C/C++ libraries. Most should be inside the conda env directory.
@johntruckenbrodt It seems my issue was indeed related to the folder being not writeable. Thanks for all the very useful input and advice. I also included the the check for writeable status in my code.
Great @jmarkloew! This error took us quite some time to figure out, with the new check it is much easier. Thanks. It will be part of the next version of pyroSAR.