Getting pyqgis geometry() into PostGIS database via psycopg2 - Python 3.7 QGIS 3

Question

Using pyqgis in a QGIS 3 Plugin, I can get features from a layer and geometry from features. Is there a way to pass the geometry into psycopg2 so that it will work in PostGIS?

I did find the PPyGIS http://www.fabianowski.eu/projects/ppygis/ but could not get it to work with Python 3.7

# Create table
    cursor.execute("CREATE TABLE structures (name VARCHAR(32) PRIMARY KEY, start VARCHAR(16), geom GEOMETRY);")


    self.get_layers()
    pipe_lyr = QgsProject.instance().mapLayersByName(self.pipe_layer)[0]
    if not pipe_lyr.isValid():
        self.popMsg('Layer is not valid')        
        return
    pipes = pipe_lyr.getFeatures()
    for pipe in pipes:
        geom = pipe.geometry()     
        # Insert some data into table
        cursor.execute("INSERT INTO structures (name, start, geom) VALUES (%s, %s, %s);", (str(pipe['name']), str(pipe['start']), geom))
        print( pipe['name'])

It errors out with

TypeError: not all arguments converted during string formatting Traceback (most recent call last): File "C:/Users/Cary/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins\invert_builder\invert_builder.py", line 288, in azure_db cursor.execute("INSERT INTO structures (name, start, geom) VALUES (%s, %s);", (str(pipe['name']), str(pipe['start']), geom)) TypeError: not all arguments converted during string formatting<

This is a plugin for QGIS 3.

Answer 1

You could use WKT and the PostGIS "ST_GeomFromText" function as a go-between:

cursor.execute("INSERT INTO structures (name, start, geom) VALUES (%s, %s, ST_GeomFromText(%s));", (str(pipe['name']), str(pipe['start']), geom.asWkt()))

QgsGeometry.asWkt() converts the geometry to a string representing that geometry, and PostGIS' ST_GeomFromText converts the string back to an actual geometry object.

Alternatively, you may want to conside avoiding the need to use psycopg2 here and reading in the destination layer as a normal QgsVectorLayer, and then you can directly add new features using the PyQGIS API.