[GIS] How to write a new layer to PostgreSQL from QGIS plugin

postgresqlpyqgisqgis

The PyQGIS cookbook states that QgsVectorFileWriter can write vector layers to any format supported by OGR:

https://www.qgis.org/en/docs/pyqgis_developer_cookbook/vector.html#writing-vector-layers

However when I try this I get an error (based on the .source() of an existing layer):

error = QgsVectorFileWriter.writeAsVectorFormat(layer, u'dbname=\'snorf\' host=localhost port=5432 user=\'snorf\' sslmode=disable key=\'id\' srid=4326 type=POINT table="public"."mynewlayer" (the_geom) sql=', 'UTF-8', layer.crs(), 'PostgreSQL')

This returns QgsVectorFileWriter.ErrCreateDataSource.

I don't have any problems modifying existing PostgreSQL layers, and I can write a new layer to the database using ogr2ogr without issue.

  • How do I create a new empty postgres layer?
  • How do I save an existing layer into a postgres database?

Best Answer

I had to go through the source code of DB Manager plugin to get to the bottom of this! I don't know why they didn't mention it in PyQGIS cookbook, but in order to write to the database another (but similar) function have to be used: QgsVectorLayerImport.importLayer(). Also notice that provider name is postgres instead of PostgreSQL O_o and no word in documentation about it...

Here is the example:

uri = "dbname='snorf' host=localhost port=5432 user='snorf' password='password' key=gid type=POINT table=\"public\".\"mynewlayer\" (geom) sql="
crs_id = 4326
crs = QgsCoordinateReferenceSystem(crs_id, QgsCoordinateReferenceSystem.EpsgCrsId)
error = QgsVectorLayerImport.importLayer(layer, uri, "postgres", crs, False, False)
if error[0] != 0:
    iface.messageBar().pushMessage(u'Error', error[1], QgsMessageBar.CRITICAL, 5)
Related Question