[QGIS-Developer] core API to fetch query output fields from SQL query?

[Alessandro Pasotti]

On Thu, Jan 9, 2020 at 12:36 PM Sandro Santilli <strk at kbt.io> wrote:

> Hi all,
> some of you might know already I've been working on improving the
> DBManager SQL Window so so not rely on "psycopg2" when interacting
> with PostgreSQL backend. This work is in progress in this PR:
>
>   https://github.com/qgis/QGIS/pull/33225
>
> The advice, from Alessandro, was to use the new core API for this:
>
>   https://qgis.org/api/classQgsAbstractDatabaseProviderConnection.html
>
> Using that API does give a more stable interaction (automatic
> reconnect on connection loss) but has a lack of features currently
> used by SQLWindow, which are:
>
>   1. Extracting names of fields returned by a query
>   2. Chunk retrival of results (useful for large result sets)
>
> Alessandro proposed to use QgsVectorLayer to solve the first issue:
>
>   https://qgis.org/api/classQgsVectorLayer.html
>
> but I did not find a way to create a QgsDataSourceURI for a generic query
> (when a Geometry field might not even exist).
>
> Can anyone help with doing this ?
> Or, have another suggestion as to how to solve both issues ?
>
> Thanks in advance.
>
> --strk;
>
>   ()   Free GIS & Flash consultant/developer
>   /\   https://strk.kbt.io/services.html
> _______________________________________________
> QGIS-Developer mailing list
> QGIS-Developer at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer



Hi Sandro,

This works for me:


vl = QgsVectorLayer( "dbname=qgis_tests host=localhost port=5432
key='__rid__' user=ale table=\"(SELECT row_number() OVER () AS __rid__, *
FROM (SELECT * FROM raster_columns) AS foo)\" schema=public", 'layer',
'postgres')

for fld in vl.fields():
    print(fld)


this is basically how DB manager creates "query" layers, note that the
above example does not have any geometry column.

Also, the vector API uses an iterator, so chunk retrieval is definitely
possible.


Btw, I would be in favor of adding missing methods to the abstract API, as
long as they are not provider-specifics and they can be abstracted from the
DB backend, a capability flag can be added to know if a provider supports
that feature or if it doesn't.

In particular, me an Nyall have been chatting about adding a method to
factory a QgsVectorLayer from a generic SQL query, the issues I see is that
we need a PK (at least for PG) and how to decide the geometry column if
there are more than one.

Having this method would allow us to replace all the Python code that
generates query layers from the provider-specific part of DB manager (the
connectors).

In any event, I was planning to apply for another grant in order to
continue the development of the abstract API, this is what I believe must
be done:

There is still a large amount of work to be done in order to complete all
the desired refactoring and to remove all the Python and C++ code that will
be ultimately be made redundant.

In particular, future work should be undertaken to:
- port all remaining data providers to the new API
refactor and eliminate the remaining DB-manager connectors to make use of
the abstract API
- eliminate duplicate and untested code inside the Processing framework for
working with Postgres databases and port the code to the new, stable,
well-tested API
- refactor and eliminate the remaining QGIS browser data items to make use
of the abstract API

Hope this helps.


-- 
Alessandro Pasotti
w3:   www.itopen.it
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20200109/26dafc97/attachment-0001.html>