How to update attributes on spatialite table programmatically: SQLite Error

Question

I am writing a plugin that updates 2 fields in an existing spatialite layer. The spatialite table contains several layers which are displayed on the current map. It executes to completion (very slowly), but the updates aren't appearing and I am getting an error that states:

SQLite error: database is locked SQL:

I've tried putting the layer into edit mode before running the plugin and also calling startEditing() on the layer within the code.

def run(self):
   #bunch of code that works self.bRanges created here
   layer_name = self.dlg.cbStreets.currentText()
   self.layer = layer_list[layer_name]
   self.from2 = self.layer.fields().fieldNameIndex('from2')
   self.to2 = self.layer.fields().fieldNameIndex('to2')
   self.dp = self.layer.dataProvider()
   self.layer.startEditing()
   self.processEachVectorCount(self.updateRanges, 10)
   self.layer.commitChanges()

def processEachVectorCount(self, func, count):
    iter = self.layer.getFeatures()
    counter = 1
    for feature in iter:
        func(feature)
        counter = counter + 1
        if counter == count:
            break

def updateRanges(self, feature):
    # self.bRanges is a dict with a list as values
    bRange = self.bRanges.get(feature.attribute('road_id'))
    fid = feature.id()
    if bRange and len(bRange):
        bMin = min(bRange)
        bMax = max(bRange)
        print feature.attribute('full_name'), bMin, bMax
        #I'm seeing this print, so far so good
        attrs = {self.from2: bMin, self.to2:bMax}
        self.dp.changeAttributeValues({fid:attrs})

---

EDIT: The following code works.

def run(self):
   ... same setup as above, but no need to set the dataprovider
   self.layer.startEditing()
   self.processEachVectorCount(self.updateRanges, 10)
   self.layer.commitChanges()


def updateRanges(self, feature):
    ... again, same as above
    self.layer.changeAttributeValue(fid, self.from2, bMin)
    self.layer.changeAttributeValue(fid, self.to2, bMax)

The commitChanges call isn't strictly necessary. Without it the layer is left in edit mode.

Answer 1

The database is locked error is raised when two connections try to access the database in a conflicting way, e.g. when one connection deletes a table which the second one tries to update. So the very first thing to check is, if the database is accessed by something else (in your code or not).

What you should also check is that you either work with the layer or work with the provider and not mix these two concepts. If you use layer.startEditing() and layer.commitChanges() you need to perform write operations through layer.changeAttributeValue() etc or the two beforementioned commands are useless because the database is accessed directly anyway. This may be the cause (startEditing() opening a connection and then trying to directly write to the database via provider.changeAttributeValues(), but I am not enough sqlite expert to be sure about this)

A very elegant but less known functionality of PyQGIS is the following code:

with edit(vectorLayer):
    vectorLayer.changeAttributeValue(fid, attr_index, new_value)

This will automatically start editing in the beginning and commit changes in the end. If anything goes wrong in between (an excpetion is raised) this will rollBack() all the changes, so you end up in a consistent state either way.