Exporting a layer from QGIS to PostgreSQL

Question

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)

• Matthias Kuhn: Are you sure layer is always a QgsVectorLayer (and not for example a layer id)?
• firefly-orange: I think you need to convert your strings to Qstrings see here
• Regina Obe: 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.
• user3659172: I can't import QSring I get the error:
• user3659172: from PyQt4.QtCore import QString Traceback (most recent call last): File "<input>", line 1, in <module> ImportError: cannot import name QString

Answer 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)