How to write a new layer to PostgreSQL from QGIS plugin

Question

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?

Answer 1

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)

Answer 2

The workaround I've found it to create a new table in postgres using the QtSql module. I'm sure there must still be a way to do this from QgsVectorLayerWriter, but I haven't found it yet.

The code below creates a new table called 'testing' with an autoincrementing 'id' attribute and adds it to the legend.

from qgis.core import *

from PyQt4 import QtSql

database = 'yourdatabase'
username = 'youruser'
table = 'testing'
srid = 4326
dimension = 2
typmod = 'POINT'

db = QtSql.QSqlDatabase.addDatabase('QPSQL')

uri = QgsDataSourceURI()
uri.setConnection('localhost', '5432', database, username, '')
uri.setDataSource('public', table, 'the_geom', '')

db.setHostName(uri.host())
db.setPort(int(uri.port()))
db.setDatabaseName(uri.database())
db.setUserName(uri.username())
db.setPassword(uri.password())

ok = db.open()
if ok:
    query = QtSql.QSqlQuery(db)
    if not query.exec_('CREATE TABLE {table} (id BIGSERIAL PRIMARY KEY)'.format(table=table)):
        raise RuntimeError('Failed to create table')
    if not query.exec_('''SELECT AddGeometryColumn('public', '{table}', 'the_geom', {srid}, '{typmod}', {dimension})'''.format(table=table, srid=srid, typmod=typmod, dimension=dimension)):
        raise RuntimeError('Failed to add geometry column to table')
    layer = QgsVectorLayer(uri.uri(), table, 'postgres')
    if layer.isValid():
        QgsMapLayerRegistry().instance().addMapLayer(layer)
else:
    raise RuntimeError('Failed to open database connection: {}'.format(db.lastError().driverText()))

Answer 3

QGIS 3.x

In QGIS 3.x, you can write a layer to Postgis using QgsVectorLayerExporter.exportLayer(), as documented.

Postgresql provider uri

The Postgresql provider uri indicates the destination database server, table, geometry column and primary key field.

It depends on how you connect to the database.

If you provide hostname, database, username and password, the uri should be like:

uri = 'dbname=\'miebiom\' host=localhost port=5432 user=\'geobox\' password=\'geobox\' sslmode=disable table="public"."import" (geom) key=\'id\''

If you use libpq services to connect, just use:

uri = 'service=covid table="public"."import" (geom) key=\'id\''

Usage example

In the following example, an existent layer called distrito is written to a table public.import.

project = QgsProject.instance()
lyr = project.mapLayersByName('distrito')[0]

uri = 'dbname=\'miebiom\' host=localhost port=5432 user=\'geobox\' password=\'geobox\' sslmode=disable table="public"."import" (geom) key=\'id\' '

err = QgsVectorLayerExporter.exportLayer(lyr, uri, "postgres", lyr.crs() )

if err[0] != QgsVectorLayerExporter.NoError:
    print('Import layer {} failed with error {}'.format( lyr.name(), err) )
else:
    print('Layer {} import ok'.format( lyr.name() ) )

Options

You can add additional options to QgsVectorLayerExporter.exportLayer().

Common options to all providers:

• overwrite (False, if not provided)
• forceSinglePartGeometryType (False, if not provided)

Postgresql provider specific option:

• lowercaseFieldNames (False, if not provided)

Usage example with all options:

err = QgsVectorLayerExporter.exportLayer(lyr, uri, "postgres", lyr.crs(), 
        options = { "overwrite": True, "forceSinglePartGeometryType": True, "lowercaseFieldNames": True } )

Non geographic layers

For non geographic layers, just remove the geometry column from the uri. You can check if the layer is geographic or not with lyr.geometryType().

Other examples on QGIS unit tests

A good source of usage examples are QGIS unit tests. Follow this link, for usage examples written by QGIS developers.