Loading table from Postgres database using PyQGIS

Question

I'm trying to load a table in QGIS layers tree using PyQGIS (see here for example). Said table has got a primary key with no null/duplicate entries; a geometry column (which is empty for now); and works perfectly fine when I load it using QGIS database manager.

But, when I try to load it using PyQGIS, the layer does not load correctly: there's a /!\ next to its name in QGIS layers tree, and print(myLayer.isValid()) returns False. And I can't figure out what is wrong with my code...

Here is my code so far (host, bdd, password etc.. have been defined as global variables earlier in the code):

uri = QgsDataSourceUri()
uri.setConnection(host, port, bdd, user, password, QgsDataSourceUri.SslDisable)
uri.setDataSource("ref", "grds_types_milieux","empty_geom","")
myLayer = QgsVectorLayer(uri.uri(), "grds_types_milieux", "postgres") 
QgsProject.instance().addMapLayer(myLayer)

• Leo Cardona: I couldn't replicate, maybe this function can help you. github.com/bstroebl/DataDrivenInputMask/blob/…

Answer 1

Made it work by :

• recreating my table with the primary key column in first position
• renaming my geometry column to "geom" (also, it's the last column; but I don't know if this matters)

• giving the name of the primary key column in the code :

  uri.setDataSource("ref", "grds_types_milieux","geom","","id")