Is it possible to write from a vector memory layer to a Spatialite database?

Question

Using PyQGIS and QGIS version 2.18.11

I have the following code:

def updateDestination(self, importLayer):
    print "updateDestination called"
    masterName = importLayer.masterLayer
    masterLayer = Utils.getLayerByName(masterName)


    (filePath, fileName) = os.path.split(masterLayer.dataProvider().dataSourceUri())
    filePath = Utils.processTempFilePath(filePath)
    print "Merging into path", filePath
    print "fileName is", fileName

    fileName = fileName[:fileName.index("'")]
    print "fileName is", fileName

    temp = Utils.getLayerByName('Merged')
    print temp

    output = filePath + "/" + fileName
    print "Writing to ", output

    crs = self.iface.mapCanvas().mapRenderer().destinationCrs()
    print crs

    QgsVectorFileWriter.writeAsVectorFormat(temp, output, 'utf-8', crs, 'SQLite', False, None, ['SPATIALITE=YES'])
    QgsMapLayerRegistry.instance().removeMapLayers([temp.id()] )

I already have an existing Spatialite file containing a table with the same name as the file. Let's call it file1.sqlite.

If I change the output to save to "file2.sqlite" it writes perfectly, giving me a new spatialite. I can then import that as a new layer.

My objective is to have it write to file1.sqlite, and override the existing table inside that file with one of the same name, containing the new data (It's the same columns, but has had new rows appended onto the bottom).

When I attempt this, I get no error and no complaints, but it doesn't write. Is there some way to force this, or perhaps a viable workaround?

Answer 1

If you have QGIS installed you may use OSGeo4w.bat ogr2ogr command to update/overwrite a specific table within a sqlite db, see example:

import subprocess
app = 'C:/Program Files/QGIS 2.18/OSGeo4W.bat' # you may need to update this to your OSGeo4W.bat file path
# sqliteDBPath and layerPath need to be file paths not vector layer objects
# also make sure the layerPath filename is named the same as the sqlite table to be updated
command = 'ogr2ogr -update -overwrite -f "SQLite" {} {} -dsco SPATIALITE=YES -lco LAUNDER=NO'.format(sqliteDBPath, layerPath)
input_params = [command]
command_run = subprocess.call([app, input_params], shell=True)

In this example you'll have to convert the in-memory layer to a file (eg. shapefile) to process. You could delete the shapefile after it used for processing. This should overwrite only the named table within the sqlite db (leaving other tables untouched).