1

In: How to write a (memory) layer from QGIS to PostGIS?, one of the the suggestions for writing a qGIS layer to postgreSQL table involves using the below code in the qGIS python console. The code seems like a reasonable approach. And it sometimes does work and creates a table called "test." Mostly, it does not create a table but instead throws an error. The code is:

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

This part seems as if it might indicate a path for a table in postgreSQL? table=\"public\".\"test\". But I have not heard of using paths for postgreSQL table? And I use windows 7. Nor I am sure about this part: (geom) sql="

The error is:

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "C:/Program Files/QGIS 2.18/bin/layer save selected to postgres.py", line 6, in <module>
    error = QgsVectorLayerImport.importLayer(layer, uri, "postgres", crs, False, False)
TypeError: QgsVectorLayerImport.importLayer(QgsVectorLayer, QString, QString, QgsCoordinateReferenceSystem,
bool onlySelected=False, bool skipAttributeCreation=False,
dict-of-QString-QVariant options=None, QProgressDialog progress=None)
-> (QgsVectorLayerImport.ImportError, QString): argument 1 has unexpected type 'str'

I have tried placing the port in quotes but it did not help: port='5432'

Another much more involved approach, might be based on issuing commands in postgreSQL, for the qGIS python console; but this would also mean adding in the the commands to insert the values for each attribute and row for a qGIS layer, something I have not figured out. In addition, the geometry attribute would need to be added in, yet it does not appear in the qGIS attribute table?

    from PyQt4.QtSql import *
    layer = iface.activeLayer()
    uri = QgsDataSourceURI()
    uri.setConnection("localhost", "5432", "your database", "user name", "password")
    uri.setDataSource("public", "table name", "geom")
    db = QSqlDatabase.addDatabase("QPSQL");
    db.setDatabaseName(uri.database())
    db.setPort(int(uri.port()))
    db.setUserName(uri.username())
    db.setPassword(uri.password())
    db.open()
    ok = db.open()
    ## below just shows that a table can be created and values can be inserted.
    query = db.exec_("""create table aaa (d INT4) """)
    query = db.exec_("""INSERT INTO aaa(d) VALUES(2) """)

Could I get the value for the geometry attribute like this?

    layer = iface.activeLayer()
    for f in layer.getFeatures():
        dGeom = f.geometry()

And then add in dGeom using AddGeometryColumn?

Note: This works, even if it uses a round about method:

    from PyQt4.QtSql import *
    from PyQt4 import QtGui
    from PyQt4.QtCore import *
    import random
    import subprocess

    polyIsHere = 0
    postgresTable = ""
    # make sure layer is there
    layer=None
    for lyr in QgsMapLayerRegistry.instance().mapLayers().values():
        lookFor = 'your_layer_name'  ## it looks for the poly one
        lookIn = lyr.name()
        mId = lyr.id()
        mYes = "" 
        if lookFor in lookIn:
            print "it's in here: " + lookIn
            polyIsHere = 1
            vl = QgsMapLayerRegistry.instance().mapLayersByName(lookIn)[0]
            iface.setActiveLayer(vl)
    if polyIsHere == 1:
        print 'forming path'
        # get info from this layer to form file name
        lyr = iface.activeLayer()
        features = lyr.getFeatures()
        for ft in features:
            attrs = ft.attributes()
        d1 = attrs[1]  
        d2 = attrs[2]  
        n = d2.partition(' ')[0]
        n3 = d2.split()[1]
        n2 = n.replace(",", "")
        dName = d1  +'_' + n2 
        layer = iface.activeLayer().name()
        layer2 = iface.activeLayer()
        short_layer = layer.split("_")[0]
        dPath = "your_path" 
        dExt = ".shp"
        file_and_path = dPath + dName +'_' +short_layer + dExt
        postgresTable = dName +'_' +short_layer
        dCRS = QgsCoordinateReferenceSystem(2229)
        file_and_path = dPath + dName +'_' +short_layer + dExt
        print 'writing shapefile'
        writer = QgsVectorFileWriter.writeAsVectorFormat( layer2,      file_and_path, "utf-8", dCRS, "ESRI Shapefile")
        # -d will delete the table if it is there
        doThis = 'C:\\PostgreSQL\\pg96\\bin\\shp2pgsql -d -s 2229 ' + file_and_path + ' > aaaa.sql  | psql -h localhost -d your_database -U postgres  -f aaaa.sql'
        os.system(doThis)    
        uri = QgsDataSourceURI()
        myPrimaryKey = "apn"                   
    uri.setConnection("localhost","5432","your_database","postgres","postgres")
        uri.setDataSource("public",postgresTable,"geom","",myPrimaryKey)
        db = QSqlDatabase.addDatabase("QPSQL");
        db.setDatabaseName(uri.database())
        db.setPort(int(uri.port()))
        db.setUserName(uri.username())
        db.setPassword(uri.password())
        db.open()
        #query = db.exec_("""select * from """ +postgresTable)
  • Are you sure layer is always a QgsVectorLayer (and not for example a layer id)? – Matthias Kuhn Jul 31 '17 at 8:20
  • I think you need to convert your strings to Qstrings see here – firefly-orange Jul 31 '17 at 8:43
  • table=\"public\".\"test\" just escapes the " within a ". You would probably achieve the same by just doing table='public.test' or even table=public.test though I don't use pyQGIS so maybe not. – LR1234567 Jul 31 '17 at 15:11
  • I can't import QSring I get the error: – user3659172 Jul 31 '17 at 15:25
  • from PyQt4.QtCore import QString Traceback (most recent call last): File "<input>", line 1, in <module> ImportError: cannot import name QString – user3659172 Jul 31 '17 at 15:28
1

As a work around for the qString problem, I am using layer = qgis.utils.iface.activeLayer(). This works and will save the table in postgreSQL. For some reason, the geometry is lost and instead becomes an incrementing integer? Also, I don't know if there is a way to test whether or not the postgreSQL table exists? Writing to an existing table will throw and error.

    dTable = "table name"
    uri = "dbname='database name' host=localhost port=5432 user='user name' password='your password' key=gid type=POINT table=" + dTable
    crs_id = 2229
    crs = QgsCoordinateReferenceSystem(crs_id, QgsCoordinateReferenceSystem.EpsgCrsId)
    layer = qgis.utils.iface.activeLayer()
    error = QgsVectorLayerImport.importLayer(layer, uri, "user name", crs, False, False)
    if error[0] != 0:
            iface.messageBar().pushMessage(u'Error', error[1], QgsMessageBar.CRITICAL, 5)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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