current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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?
share|improve this question
    
Any progress on using QgsVectorFileWriter.writeAsVectorFormat()? –  SS_Rebelious Jun 9 at 19:51

2 Answers 2

up vote 0 down vote

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)
share|improve this answer
up vote 1 down vote accepted

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()))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.