1

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.

  • 1
    did you try to work directly on the layer? Now you are working with the dataprovider. If you use the local edit buffer (layer.startEditing() and layer.commitChanges()) you should use layer.changeAttributeValue() etc and not layer.dataProvider().changeAttributeValues() etc. – Matthias Kuhn Jul 2 '16 at 21:40
  • @MatthiasKuhn changing it to self.layer.changeAttributeValue(fid, self.from2, bMin) etc seems to work, but it still raises the error 5 seconds or so after execution end. – marcp Jul 2 '16 at 21:51
  • Sorry, I can't help. You probably need to check if there's something else accessing the database at the same time (in your code or not). But the above advice is good to keep in mind anyway. – Matthias Kuhn Jul 2 '16 at 21:59
3

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.

|improve this answer|||||
  • I tried but failed to also find a solution using the dataprovider. There are surprisingly few complete examples out there. My understanding is that it should be faster for many edits. – marcp Jul 3 '16 at 15:34
  • "My understanding is that it should be faster for many edits." <- This really depends a lot on the particular case, I/O, database, queries. It can also be faster to use the edit buffer because changes are batch committed. See also the bottom paragraph of this post: opengis.ch/2015/08/12/with-edit-layer – Matthias Kuhn Jul 3 '16 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.