1

I have a SpatiaLite database that has relational tables to link geo entities that can be reflected in QGIS as layers. To link the relations I need some custom forms where I can select these entities which should not be the problem using a custom plugin.

What I struggle with is the fact on how to access tte SpatiaLite database. How can I access it via a custom plugin? I already tried importing all tables as layers and the access them but struggle with this as well; then I tried to use SpatiaLite as a python plugin but also struggle as QGIS does not find the extension.

Does anyone have a hint on directly access the registered SpatiaLite DB (via database manager) to use SQL on it?

I'm using QGIS-Version 3.24.2-Tisler with SQLite-Version 3.38.1 and SpatiaLite-Version 5.0.1.

1 Answer 1

0

In my QGIS 3.16.2 i can use

import sqlite3 as lite

con = lite.connect('d:/temp/test.sqlite')
# or use a DB from the DB manager like this:
# lite.connect(QSettings().value("SpatiaLite/connections/test.sqlite/sqlitepath"))

cur = con.cursor()
cur.execute("create table a (id int, txt varchar(100))")

cur.execute("insert into a VALUES (1,'Test one'),(2,'Test two')")

cur.execute("select * from a")
cur.fetchall()
# this yields [(1, 'Test one'), (2, 'Test two')]

from the QGIS Python Console (Ctrl+Alt+P).

Working with the QSettings() to get a list of possible sqlite connections is (to me) not intuitive, it works like this:

s = QSettings()
s.beginGroup("SpatiaLite/connections")
s.allKeys()
# this gives a list of strings like 'test.sqlite/sqlitepath'
# which need to be prefaced by 'SpatiaLite/connections' to get the full path for the QSettings().value(...) function.
s.endGroup()

Note that the QSettings stores internal state, it is relevant what group you are "in" at the moment. value(...) and allKeys() is relative to the group. See the documentation at https://doc.qt.io/archives/qt-4.8/qsettings.html#beginGroup .

2
  • Thanks a lot, I was able to manage the first part on my own before I saw your answer but the seconds part is very helpful as well. Thank you! Jun 27, 2022 at 9:17
  • 1
    As an additional hint to @til_b answer also this can be used: from qgis.utils import spatialite_connect together with connection = spatialite_connect('<DB file path>') to directly conenct to a real SpatiaLite database and SpatiaLite system tables can be filtered out with sys_tables = QgsSqliteUtils.systemTables() if you compare a list of existing tables with the result list of the command mentioned. Jun 27, 2022 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.