6

I am trying to connect with python code, QGIS and Spatialite, to do SQL query. I have read some tips and I tried with this code, but no add anything.

Any Idea?

uri=QgsDataSourceURI()
uri.setDatabase('C:\Users\pr\Desktop\GIS.sqlite')
schema=' '
table ='ambi'
geom_column= 'geom'
uri.setDataSource(schema, table, geom_column)
display_name=('prueba')
vlayer=QgsVectorLayer(uri.uri(), display_name, 'spatialite')

Finally I have put this:

uri = QgsDataSourceURI()
uri.setDatabase('C:/Desktop/GIS.sqlite')
schema = ''
table = 'PV'
geom_column = 'geom'
uri.setDataSource(schema, table, geom_column)
display_name = 'PV'
vlayer = QgsVectorLayer(uri.uri(), display_name, 'spatialite')
db = QSqlDatabase.addDatabase("QSQLITE");
db.setDatabaseName(uri.database())
db.open()
query=db.exec_("""select * from PV where pk=1 """)
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

But It adds the complete shape and I want only add, the PV with pk=1. Could you tell me why it is?

  • 1
    Try forward slashes in your database path. – underdark Oct 26 '15 at 0:00
  • Or try putting an 'r' in front of your path like this: r'C:\Users\pr\Desktop\GIS.sqlite' – Fezter Oct 26 '15 at 5:55
7

I have a doubt about your expectations

If it's doing SQL query on a SQlite/Spatialite DB within QGIS

Select the layer that use SQlite as a source, then do

# Import QtSql function(s)
from PyQt4.QtSql import QSqlDatabase

# Get selected layer
layer = iface.activeLayer()
# Get file path
uri = QgsDataSourceURI(layer.dataProvider().dataSourceUri())
# Create DB connexion to do SQL 
db = QSqlDatabase.addDatabase("QSQLITE");
# Reuse the path to DB to set database name
db.setDatabaseName(uri.database())
# Open the connection
db.open()
# query the table
query = db.exec_("""select * from your_table""")
# Play with results (not efficient, just for demo)
while query.next():
    values = []
    record = query.record()
    for index in range(record.count()):
        # We exclude the geometry to join attributes data
        if not isinstance(record.value(index), QByteArray):
            values.append(str(record.value(index)))
    print ';'.join(values)

It was mainly inspired by this topic but applied to SQLite/Spatialite

To really use Spatialite (in fact an extension you load in SQLite) and not only SQLite, you can replace QSQLITE with QSPATIALITE. Then you can see if extension has been loaded with:

query = db.exec_("""SELECT sqlite_version(), spatialite_version()""")
query.next()
print query.value(0), query.value(1)

If it's just displaying the layer from Python

If you want to add the layer, don't forget after

vlayer=QgsVectorLayer(uri.uri(), display_name, 'spatialite')

to add it with:

QgsMapLayerRegistry.instance().addMapLayer(vlayer)

A shortcut to do the same operation in one is:

iface.addVectorLayer(uri.uri(), display_name, 'spatialite')
2
uri.setDataSource(schema, table, geom_column,"pk=1") 

The 4th parameter (optioonal) in setDataSource is a sql WHERE

  • It might help to provide some more context, or a reference. – BradHards Aug 23 '16 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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