1

I've tried to add my temporary table into QGIS canvas using pyqgis but it's failed. Here the code(view_tatanga is a temporary table that I've created using sql on pgadmin) :

uri = QgsDataSourceURI()
sql = "SELECT * FROM gis.view_tatanga"
uri.setConnection("localhost", "5433", "db_pbb", "postgres", "septin")
uri.setDataSource('', u'(%s\n)' % sql, 'geom', '', 'nop')
#uri.setDataSource("",sql,"geom","","nop")
tmadminkec = QgsVectorLayer(uri.uri(), 'view_tatanga', "postgres")
QgsMapLayerRegistry.instance().addMapLayer(tmadminkec)

I didn't get any error when I try to load a usual table (non temporary table). Any ideas ?

  • Try uri.setDataSource('gis', 'view_tatanga', 'geom', '', 'nop') Note that nop must be a field with unique values. – Germán Carrillo Oct 6 '16 at 12:22
2

You don't need the sql parameter! In particular, it only takes the where clause, so the name sql is misleading. See Adding PostGIS layer from QGIS Python plugin for a simple example.

supplement:

if you want the result of an sql query shown as a layer, you'll have to reproduce the behaviour of the qgis database manager. Here the connection replaces the table name with sql string. I did this with a spatialite db.

# the specific connect-string for spatialite
sConnect = "dbname='" + myDBName + "'"
# table contains sql 
table = "select mytable.field1, ..." + "where myTable.field1 >= 100"
# putting together as a uri-String
uri = sConnect + " key='myNumerfield'" + "table=\"(%s)\" (geom) sql=" % table
display_name = 'SQL-Result'
# create vector layer
vlayer = QgsVectorLayer(uri, display_name, 'spatialite')

You want to find out, how the connection string for postgres looks like. Load a layer through the database manager and look into the layer properties. May this helps you!

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.