[GIS] Can Spatialite SQL statements be issued with a python script in QGIS

pythonqgisspatialite

In my previous scripting environments (ms-access and Manifold GIS), it was possible to create and execute SQL queries from withing VBA or VB Script procedures. This is really useful for things such as reloading a combo box on a form based on an entry in a previous widget/control. It is also good for creating update queries on the fly.

Given that spatialite can be run from a command line, I wonder if it is possible to use Spatialite SQL statements from within Python scripts in QGIS. With ms-access and Manifold, they are both SQL aware and procedures will wait until a recordset is returned. I wonder if this is the case with Python scripts in QGIS?

Andrew M

Best Answer

Sure you can use pyspatialite or even the Qt QSqlDatabase

Using pyspatiailite (from http://www.gaia-gis.it/spatialite-2.4.0-4/splite-python.html)

from pyspatialite import dbapi2 as db

# creating/connecting the test_db
conn = db.connect('test_db.sqlite')

# creating a Cursor
cur = conn.cursor()

# testing library versions
rs = cur.execute('SELECT sqlite_version(), spatialite_version()')
for row in rs:
    msg = "> SQLite v%s Spatialite v%s" % (row[0], row[1])
    print msg

Using Qt QSqlDatabase:

db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('test_db.sqlite')
db.open()

query = QSqlQuery('SELECT sqlite_version(), spatialite_version()')
query.exec_()
while query.next():
   print query.value(0)

Both have the same kind of API and work fine. pyspatialite is designed with spatialite in mind but because spatialite is just a sqlite database you can normally get away with just using Sqlite drivers.

Related Question