Add view generated with psycopg2 through sshtunnel as layer

Question

I'm writing an internal QGIS plugin to query a remote database. I'm not administrating the server, but administrator has set up a virtual machine on a windows file server, with PostGIS set up.

I wrote a script to download CSV files from the web, upload them on SFTP and load them in the database.

I achieved to create a view in this table from within QGIS using sshtunnel. I can fetch it (still not have the headers but I guess it's on another topic).

Thing is I cannot succeed to get a proper QgsVectorLayer : i don't know which parameters I should pass to QgsDataSourceUri - even if psycopg2 connector is working.

So I guess there are different possibilities :

• get a layer from cur.fetchall() method, last column being a geometry (generated from the CREATE VIEW statement), maybe using other external modules such as geopandas or fiona,
• find which parameters I should pass to QGSDataSourceUri.

Which one should I consider?

What I've accomplished so far:

from qgis.utils import iface
import processing
import tempfile
import os

with sshtunnel.open_tunnel(('xxx.xxx.xxx.x', x),ssh_username="user",ssh_password="password",remote_bind_address=('localhost', 5432)) as server:
   server.start()
   params = {'database': 'db','user': 'postgres','host': 'localhost','password':'postgres','port': server.local_bind_port}
   conn = psycopg2.connect(**params)
   with conn.cursor() as cur:
       cur.execute('''CREATE OR REPLACE VIEW view_name AS SELECT *, Tt_MakePoint(longitude,latitude) as geom FROM table1 JOIN table2 on "column"."table1"="column"."table2" WHERE SPATIAL CONDITION''')
       cur.execute('''SELECT * FROM extrait;''')
       cur.fetchall()

I first tried to use this answer to add the view as layer. I keep stuck at which parameters I could pass to setConnection : remote or local host/port (if only this class is able to connect to postgresql through ssh)? For now, I tried with localhost because it worked for psycopg2 connection (inspired from this answer).

What I understand is that the setConnection "doesn't know" that I'm using a tunnel, even if I'm calling in from within the with block, but I may be wrong.

         uri = QgsDataSourceUri()
         uri.setConnection('localhost', str(server.local_bind_port),"dbname","postgres","postgres")
         uri.setDataSource ("public", "view_name", 'geom')
         vlayer=QgsVectorLayer (uri.uri(False), "etablissements_complets", 'postgres')
         QgsProject.instance().addMapLayer(vlayer) 

I should precise that I set up my postgresql.conf with listen_adresses='*' and my pg_hba.conf as mentionned in various topics.

Answer 1

I eventually ended using first option, parsing the cur.fetchall() method.

Here's the way I did it. I'm sure it's not the most elegant way, but it satisfied my needs.

def addAtts(layer, attName, varType):
    layer.dataProvider().addAttributes([QgsField(attName, varType)])
    layer.updateFields()

def conversion(pg_type):
    if pg_type== 25:
       qvariant_type=QVariant.String
    elif pg_type==23:
       qvariant_type=QVariant.Int
    elif pg_type==700:
       qvariant_type=QVariant.Double
    else:
        qvariant_type=QVariant.Invalid
    return(qvariant_type)

with conn.cursor() as cur:
    cur.execute('''DROP VIEW extract;''')
    cur.execute('''CREATE OR REPLACE VIEW extract AS SELECT * FROM TABLE, St_AsText(St_MakePoint(longitude, latitude)) as geom from table 1 join table 2 on "column".table1="column".table2 WHERE SPATIAL CONDITION''')
    cur.execute('''COMMIT;''')
    cur.execute('''SELECT * FROM extrait;''')
    conn.commit()
    headers=cur.description
    table=cur.fetchall()
    cur.close()
    vlayer=QgsVectorLayer('Point','Name','memory')
    vlayer.startEditing()
    for i in headers[:-1]:
        addAtts(vlayer,i[0],conversion(i[1]))           
    vlayer.commitChanges()
    total_features=[]
    for f in table:
        feature=QgsFeature(vlayer.fields())
        feature.setAttributes(list(f)[:-1])
        feature.setGeometry(QgsGeometry.fromWkt(f[-1]))
        ensemble_feature.append(feature)
    vlayer.dataProvider().addFeatures(ensemble_feature)