Add postgis temporary table using pyqgis into canvas failed

Question

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 ?

• Germán Carrillo: Try uri.setDataSource('gis', 'view_tatanga', 'geom', '', 'nop') Note that nop must be a field with unique values.

Answer 1

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!