How do I update joined data from MySQL programmatically?

Question

I have a point vector layer (Sensores) in my QGIS project, I also have a MySQL database layer (SensoresDB) containing only data. I have joined these two layers in order to geographically reference my MySQL data and classify it.

My problem is the data in my vector layer (Sensores) doesn't update when the data in the MySQL layer (SensoresDB) does. That's ok, I don't need it to update in the exact moment it changes, but it would be nice if I could force it with code. I've tried using layer.setCacheImage(None) and layer.triggerRepaint() to no avail. Until now, the only way it does update is when I close the project and open it again.

Answer 1

Might I suggest creating a macro which contains a function to rejoin both your layers. Using this method then allows you to run the function from the Python console whenever you want to update the vector layer.

You can do this by going to the toolbar:

Project > Project Properties > Macros

And then insert the following code:

shp = QgsMapLayerRegistry.instance().mapLayersByName( "Sensores" )[0]
mysql = QgsMapLayerRegistry.instance().mapLayersByName( "SensoresDB" )[0]

# Set JOIN properties by changing 'ID' to relevant field names
shpField='ID'
mysqlField='ID'
joinObject = QgsVectorJoinInfo()
joinObject.joinLayerId = mysql.id()
joinObject.joinFieldName = mysqlField
joinObject.targetFieldName = shpField

def update():
    shp.removeJoin(mysql.id())
    shp.addJoin(joinObject)
    shp.triggerRepaint()

Make sure that you save the project and enable macros by going to the toolbar:

Settings > General > Enable macros

Now the next time you reload the project and you edit your MySQL layer, just type update() in the Python console and it should rejoin and update your vector layer accordingly.