I think this question is answered by A.A. Changing local saved files is slow with changeAttributeValues
but on a layer in memory it's faster. I compared different ways of changing attributes of a local layer.
Methods
changeAttributeValues
- change attribute values of Shapefile with dbf package
- change attribute values by creating new layer with parts of old layer and do join by attributes and save as SQLite
- change attribute values by sql
- change attribute values by creating new layer with geometry of old layer, do join by attribute with creating new SQLite Datasource (old and new values) and save as SQLite
Conclusion
The fastest way is change values of an existing local layer is to create a new layer with parts of old layer and do join by attributes and save it. But in sql there might be options to do it faster (I red about 50k changes/sec).
Edit: The fastest way can be improved if the data table is created with sql. If the data creation is not included in timings the table creation, save, join and save takes 10.9s for Python and 2.4s for sql at 10M Values here.
Timings
- create layer with 20.0k features and 20 attributes 0.7 s
changeAttributeValues
on layer with 20.0k features and 20 attributes 12 s
- save changed layer as ESRI Shapefile 1.2 s
- change attribute values of Shapefile with dbf package 11.5 s
- change attribute values by creating new layer with parts of old layer and do join by attribute and save as SQLite 3.3 s
- change attribute values by sql 33.4 s
- change attribute values by creating new layer with geometry of old layer, do join by attribute with creating new SQLite Datasource (old and new values) and save as SQLite 2.7 s
Code
import os
from time import perf_counter as pc
import dbf
import sqlite3
pathtowrite=os.getenv('USERPROFILE') + "\\downloads\\"
xvals=100
# clean temp for new run
lyrs=["lyr","lyr2","lyr3","lyr22","lyr222","lyr33","lyr333","lyr4","lyr444"]
files=["new.sqlite","joined.sqlite","joined_sql.sqlite","changeattributevalues_try_dbf.cpg","changeattributevalues_try_dbf.dbf","changeattributevalues_try_dbf.prj","changeattributevalues_try_dbf.shp","changeattributevalues_try_dbf.shx","temp_data_layer.sqlite"]
for lyr0 in lyrs:
try:
exec("QgsProject.instance().removeMapLayer("+lyr0+")")
except:
pass
for file in files:
try:
os.remove(pathtowrite + file)
except:
pass
if os.path.exists(pathtowrite+"temp_data_layer.sqlite"):
print("temp is not cleaned because of python processing stream - run again")
else:
pc1=pc()
# create a new memory layer
lyr = QgsVectorLayer("Polygon?crs=EPSG:25832", "new_memory_layer", "memory")
lyr_p=lyr.dataProvider()
ifield=[]
for f in range(20):
ifield.append(QgsField("ID" + str(f), QVariant.Int))
lyr_p.addAttributes( ifield )
lyr.updateFields()
testdata=[f for f in range(xvals)]
ifeat=[]
for f in range(len(testdata)):
ff = QgsFeature()
ff.setFields(lyr_p.fields())
for g in range(20):
ff[g] = testdata[f]
ifeat.append(ff)
lyr_p.addFeatures(ifeat)
options = QgsVectorFileWriter.SaveVectorOptions()
options.actioanOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
options.EditionCapability = QgsVectorFileWriter.CanAddNewLayer
options.driverName="SQLite"
options.layerName = lyr.name()
writer = QgsVectorFileWriter.writeAsVectorFormat(lyr, pathtowrite + "new", options)
lyr=QgsVectorLayer(pathtowrite + "new.sqlite","new","ogr")
lyr.dataProvider().setEncoding(u'UTF-8')
QgsProject.instance().addMapLayer(lyr)
lyr_p=lyr.dataProvider()
pc2=pc()
# change attributes of existing local layer
testdata=[f for f in range(xvals)]
ifeat=[]
lyr.startEditing()
k=0
for f in range(len(testdata)):
iifeat=""
for g in range(20):
iifeat+=str(g+1) + ":" + str(testdata[f]) +","
iifeat=iifeat[:-1]
exec("attrs = { "+str(f+1)+" : {"+iifeat+"}}")
lyr_p.changeAttributeValues( attrs )
# k+=1
# if k>500:
# break
lyr.commitChanges()
pc3=pc()
#save to shapefile
options = QgsVectorFileWriter.SaveVectorOptions()
options.actioanOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
options.EditionCapability = QgsVectorFileWriter.CanAddNewLayer
options.driverName="ESRI Shapefile"
options.layerName = "changeattributevalues_try_dbf"
writer = QgsVectorFileWriter.writeAsVectorFormat(lyr, pathtowrite + "changeattributevalues_try_dbf", options)
pc4=pc()
# change attributes of existing local layer with dbf package (not fast enough)
testdata=[f for f in range(xvals)]
fpath=pathtowrite + "changeattributevalues_try_dbf.dbf"
dbf_table = dbf.Table(fpath, codepage='utf8')
dbf_table.open(mode=dbf.READ_WRITE)
k=0
for f in dbf_table:
with f:
f[:]=[testdata[k] for f in range(21)]
# k+=1
# if k>500:
# break
dbf_table.close()
pc5=pc()
## change attributes of existing local layer with creating newlayer with part of existing layer and join values
lyr2 = QgsVectorLayer("Polygon?crs=EPSG:25832", "new_memory_layer_with_part_of_old_memory_layer", "memory")
lyr_p2=lyr2.dataProvider()
ifield=[]
for f in range(2):
ifield.append(QgsField("ID0" + str(f), QVariant.Int))
lyr_p2.addAttributes( ifield )
lyr2.updateFields()
ifeat=[]
for f in lyr.getFeatures():
ff = QgsFeature()
ff.setFields(lyr_p.fields())
for g in range(2):
ff[g] = f[g]
ifeat.append(ff)
lyr_p2.addFeatures(ifeat)
lyr2.commitChanges()
# create new data layer for join
lyr3 = QgsVectorLayer("Polygon?crs=EPSG:25832", "table_for_join", "memory")
lyr_p3=lyr3.dataProvider()
ifield=[]
for f in range(19):
ifield.append(QgsField("ID" + str(f), QVariant.Int))
lyr_p3.addAttributes( ifield )
lyr3.updateFields()
testdata_new=[f for f in range(xvals)]
ifeat=[]
for f in range(len(testdata)):
ff = QgsFeature()
ff.setFields(lyr_p.fields())
for g in range(19):
ff[g] = testdata_new[f]
ifeat.append(ff)
lyr_p3.addFeatures(ifeat)
QgsProject.instance().addMapLayer(lyr3)
# perform join operation
shpField='ID01'
csvField='ID1'
#joinObject = QgsVectorLayerJoinInfo()
#joinObject.joinLayerId = lyr3.id()
#joinObject.joinFieldName = csvField
#joinObject.targetFieldName = shpField
#joinObject.memoryCache = True
#lyr2.addJoin(joinObject)
output=processing.run("native:joinattributestable", \
{'INPUT':lyr2,'FIELD':shpField,\
'INPUT_2':lyr3,'FIELD_2':csvField,\
'FIELDS_TO_COPY':[],'METHOD':1,'DISCARD_NONMATCHING':False,'PREFIX':'','OUTPUT':'TEMPORARY_OUTPUT'})
lyr22=output["OUTPUT"]
options = QgsVectorFileWriter.SaveVectorOptions()
options.actioanOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
options.EditionCapability = QgsVectorFileWriter.CanAddNewLayer
options.driverName="SQLite"
options.layerName = lyr2.name()
writer = QgsVectorFileWriter.writeAsVectorFormat(lyr22, pathtowrite + "joined", options)
lyr4=QgsVectorLayer(pathtowrite + "joined.sqlite","joined","ogr")
lyr4.dataProvider().setEncoding(u'UTF-8')
QgsProject.instance().addMapLayer(lyr4)
pc6=pc()
## change attributes of existing local layer with sql
conn = sqlite3.connect(pathtowrite + "new.sqlite")
c = conn.cursor()
recordList=[]
testdata_new=[f*2 for f in range(xvals)]
for f in range(len(testdata)):
update_vals=[]
for g in range(10):
update_vals.append(testdata_new[f])
update_vals.append(f)
recordList.append(tuple(update_vals))
sqlite_update_query = "update new_memory_layer set "
for f in range(10,20):
sqlite_update_query+="ID"+str(f)+" = ?, "
sqlite_update_query=sqlite_update_query[:-2]
sqlite_update_query+=" where ID1 = ?"
#https://stackoverflow.com/questions/52142645/how-to-improve-sqlite-insert-performance-in-python-3-6
##parameters should improve speed, ensure the power and OS is healthy otherwise data loss, no improvement here
#c.execute("PRAGMA synchronous = OFF")
#c.execute("PRAGMA journal_mode = OFF")
#https://stackoverflow.com/questions/35013453/apsw-or-sqlite3-very-slow-insert-on-executemany
c.execute("BEGIN TRANSACTION")
c.executemany(sqlite_update_query, recordList)
c.execute("COMMIT")
conn.commit()
conn.close()
pc7=pc()
## change attribute values by creating new layer with geometry of old layer, do join by attribute with creating new SQLite Datasource (old and new values) and save as SQLite
lyr222 = QgsVectorLayer("Polygon?crs=EPSG:25832", "new_memory_layer_with_part_of_old_memory_layer (only geoms)", "memory")
lyr_p222=lyr222.dataProvider()
ifield=[]
iter0=[f for f in range(2)]
for f in iter0:
ifield.append(QgsField("ID0" + str(f), QVariant.Int))
lyr_p222.addAttributes( ifield )
lyr222.updateFields()
ifeat=[]
lyr222.startEditing()
feats=lyr.getFeatures()
for f in feats:
ff = QgsFeature()
ff.setFields(lyr_p.fields())
for g in range(2):
ff[g] = f[g]
ifeat.append(ff)
lyr_p222.addFeatures(ifeat)
lyr222.commitChanges()
# create new data layer for join (local SQLite)
ifield=""
values=""
field_count=19
for f in range(field_count):
ifield+="ID" + str(f) + ", "
values+="?,"
ifield=ifield[:-2]
values=values[:-1]
recordList=[]
testdata=[f for f in range(xvals)]
for f in range(len(testdata)):
update_vals=[]
for g in range(field_count):
update_vals.append(testdata[f])
recordList.append(tuple(update_vals))
connection = sqlite3.connect(pathtowrite + "temp_data_layer.sqlite")
connection.execute('CREATE TABLE events(' + ifield + ')')
connection.executemany(
'INSERT INTO events VALUES ('+values+')',
recordList
)
connection.commit()
connection.close()
lyr33 = QgsVectorLayer(pathtowrite + "temp_data_layer.sqlite", "local_table_for_join", "ogr")
QgsProject.instance().addMapLayer(lyr33)
# perform join operation
shpField='ID01'
csvField='ID1'
output=processing.run("native:joinattributestable", \
{'INPUT':lyr222,'FIELD':shpField,\
'INPUT_2':lyr33,'FIELD_2':csvField,\
'FIELDS_TO_COPY':[],'METHOD':1,'DISCARD_NONMATCHING':False,'PREFIX':'','OUTPUT':'TEMPORARY_OUTPUT'})
lyr22=output["OUTPUT"]
options = QgsVectorFileWriter.SaveVectorOptions()
options.actioanOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
options.EditionCapability = QgsVectorFileWriter.CanAddNewLayer
options.driverName="SQLite"
options.layerName = lyr222.name()
writer = QgsVectorFileWriter.writeAsVectorFormat(lyr22, pathtowrite + "joined_sql", options)
lyr444=QgsVectorLayer(pathtowrite + "joined_sql.sqlite","joined_sql","ogr")
lyr444.dataProvider().setEncoding(u'UTF-8')
QgsProject.instance().addMapLayer(lyr444)
pc8=pc()
print("create layer with " +str(round(xvals/10**3,0))+"k features and 20 attributes ", round(pc2-pc1,1), "s")
print("changeattributes on layer with " +str(round(xvals/10**3,0))+"k features and 20 attributes ", round(pc3-pc2,1), "s")
print("save changed layer as ESRI Shapefile ", round(pc4-pc3,1), "s")
print("change attribute values of Shapefile with dbf package ", round(pc5-pc4,1), "s")
print("change attribute values by creating new layer with parts of old layer and do join by attribute and save as SQLite ", round(pc6-pc5,1), "s")
print("change attribute values by sql ", round(pc7-pc6,1), "s")
print("change attribute values by creating new layer with geometry of old layer, do join by attribute with creating new SQLite Datasource (old and new values) and save as SQLite ", round(pc8-pc7,2), "s")