Write a layer in a specific table of a spatialite file

Question

I'm manipulating several layers using python and qgis and I would like to save all those layers as separate tables in the same spatialite file.

At the moment I'm using :

QgsVectorFileWriter.writeAsVectorFormat(layer, output_file_path_str, 'utf-8',
  crs, 'SQLite', False, None, ['SPATIALITE=YES', ])

However, this only enables me to store one layer into one file. I can't figure out how to store several layers into the same file.

In a qgis unit test file i found this :

options = QgsVectorFileWriter.SaveVectorOptions()
options.actionOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer

But it seems to be outdated, i get errors such as AttributeError: type object 'QgsVectorFileWriter' has no attribute 'SaveVectorOptions'.

Answer 1

This is one example of missing functionality in QGIS API v2 that has been incorporated (by Even Rouault) into v3.

---

Possible workarounds for QGIS v2:

1.

I use this solution in QGIS v2.14.8 for a company: run the Processing algorithm Convert Format from GDAL/OGR (you need to comment or remove these two lines in /usr/share/qgis/python/plugins/processing/algs/gdal/ogr2ogr.py for this solution to work):

res = processing.runalg( "gdalogr:convertformat",
        vLayer,
        3, #SQLite
        "-lco GEOMETRY_NAME=geom -update", # Creation options
        existingDBPath # Path to existing .sqlite file
      )

Because of some flaws in Processing, this solution works for PostgreSQL/PostGIS layers (enough for the company I work for), but it does not work for Shapefiles nor Memory layers. I wanted to fix this in #3644 but unfortunately it was never merged.

2.

Use the ogr2ogr port to Python.

From PostGIS layer to SpatiaLite:

from ogr2ogr import *
main( [ "",
        "-f",
        "SQLite",
        "-lco",
        "GEOMETRY_NAME=geom",
        "-update",
        existingDBPath, # Path to existing .sqlite file
        "PG:{}".format( dataSourceURI.connectionInfo() ), # Connection string
        "{}.{}".format( dataSourceURI.schema(), dataSourceURI.table() ) # Tablename
      ]
   )

From a Shapefile QGIS layer to SpatiaLite:

from ogr2ogr import *
main( [ "",
        "-f",
        "SQLite",
        "-lco",
        "GEOMETRY_NAME=geom",
        "-update",
        existingDBPath, # Path to existing .sqlite file
        layer.source()
      ]
   )

3.

Save your layers in temporary files (e.g., Shapefiles) and directly run the ogr2ogr command:

ogr2ogr -f SQLite -update myExistingDB.sqlite /docs/geodata/rivers.shp rivers 

Answer 2

If your data is in shapefile format you may use ogr2ogr python module to add tables/layers to a sqlite db:

ogr2ogr.main(["","-update", "-overwrite", "-f", "SQLite", "{}".format(sqLiteDBPath), "{}".format(shpFilePath)])