PyQGIS 3 - Add New Layer to Spatialite Database

Question

For the most part, working with QGIS layers has been a pleasure, but I've run into some difficulties recently when trying to create a new vector layer in the SQLite database I've been using.

I've created a new database with the QgsVectorFileWriter like this:

writer = QgsVectorFileWriter(
    "/path/to/db/testdb.sqlite",
    "utf-8",
    myFields,
    QgsWkbTypes.PointZ,
    QgsCoordinateReferenceSystem(
        4326, QgsCoordinateReferenceSystem.EpsgCrsId),
    "SQLite",
    ["SPATIALITE=YES"])

And noticed that QGIS creates a default new table called "testdb" (named after the database) that works fine to load and save as to a layer.

I want to create a new layer with a new table now in the same database, so I went over to the docs at https://qgis.org/api/classQgsVectorFileWriter.html.

---

Using QgsVectorFileWriter()

I saw there was an ActionOnExistingFile parameter in the constructor to create a new layer & table, only to be disappointed when I noticed this parameter had been mysteriously dropped in the python implementation of the QgsVectorFileWriter, according to the PyQGIS docs.
Feeling hopeful, I threw in an action=QgsVectorFileWriter.CreateOrOverwriteLayer keyword, and even tried typing out all 11 arguments to avoid the keyword argument, but no luck.

# Doesn't work
writer = QgsVectorFileWriter(...
    action=QgsVectorFileWriter.CreateOrOverwriteLayer)

Using QgsVectorFileWriter.writeAsVectorFormat()

I did see that the python writeAsVectorFormat method had an option to specify the new table action, so I went ahead and wrote up something like this, using the SaveVectorOptions class:

options = QgsVectorFileWriter.SaveVectorOptions()
options.actionOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
options.layerName = "testlayer"
writer = QgsVectorFileWriter.writeAsVectorFormat(
    layer,
    "/path/to/db/testdb.sqlite",
    options)

But... The layer argument assumes I already have a valid layer from a valid data source. I need to create the source first before I can create the layer though, right?

Using a memory layer...?

If one can't create the data source first, I thought maybe loading a layer with a temporary "memory" provider and then writing it to the database would be an option. I'm not sure how I would specify the other parameters that I need for the vector layer (such as the fields and the CRS) using this:

layer = QgsVectorLayer("PointZ", "testlayer", "memory")

and then saving it to the database. I'm not even sure whether this method would work, but it seems a little hacky to me in any case. But I could be wrong...

Answer 1

Try the following code - tested from python window on QGIS 3.4. It adds the layer selected in the layer panel to an existing sqlite database. As you mention in your question this method obviously requires a valid layer in the first place.

layer = iface.activeLayer()
options = QgsVectorFileWriter.SaveVectorOptions()
options.actionOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
options.EditionCapability = QgsVectorFileWriter.CanAddNewLayer
options.driverName="SQLite"
options.layerName = layer.name()
writer = QgsVectorFileWriter.writeAsVectorFormat(layer, 'C:\\Users\\Ian\\Downloads\\GIS\\new', options)