[QGIS-Developer] Unexpected results from GPKG table query using executeSql()

[Alessandro Pasotti]

Hi Raymond,

I tested your samples and I cannot reproduce the issue on QGIS master and
GDAL 3.4.

Here is the result of my query on your test file:

In [13]: md = QgsProviderRegistry.instance().providerMetadata('ogr')
    ...: conn =
md.createConnection('/home/xxxxx/Scaricati/soilext_db.gpkg', {})
    ...:
    ...: # Would expect this to return 4 columns: edit_id, timestamp, name,
can_undo
    ...: q = 'SELECT * FROM soilexc_edits ORDER BY edit_id'
    ...: qr = conn.executeSql(q)
    ...:
    ...:
    ...: # This does not return the edit_id field (Which is the PK)
    ...: for rec in qr:
    ...:     print(rec)
    ...: '''
    ...: [PyQt5.QtCore.QDateTime(2022, 3, 1, 0, 0), '', True]
    ...: [PyQt5.QtCore.QDateTime(2022, 3, 2, 0, 0), '', True]
    ...: [PyQt5.QtCore.QDateTime(2022, 3, 3, 0, 0), '', True]
    ...: '''
[1, PyQt5.QtCore.QDateTime(2022, 3, 1, 0, 0), '', True]
[2, PyQt5.QtCore.QDateTime(2022, 3, 2, 0, 0), '', True]
[3, PyQt5.QtCore.QDateTime(2022, 3, 3, 0, 0), '', True]
[4, PyQt5.QtCore.QDateTime(2022, 3, 30, 0, 0), 'purge', False]
[5, PyQt5.QtCore.QDateTime(2022, 3, 31, 0, 0), 'test_new', True]
[6, PyQt5.QtCore.QDateTime(2022, 3, 31, 0, 0), 'test_new', True]
[7, PyQt5.QtCore.QDateTime(2022, 3, 31, 0, 0), 'test_new', True]
[8, PyQt5.QtCore.QDateTime(2022, 3, 31, 0, 0), 'test_new', True]

the edit_id is actually in the results.

Cheers


On Fri, Apr 1, 2022 at 8:28 AM Raymond Nijssen <r.nijssen at terglobo.nl>
wrote:

> For a workaround I found that selecting this way:
>
> SELECT
>    edit_id + 0 AS edit_id,
>    timestamp,
>    name
> FROM
>    ...
>
> The +0 seem to makes QGIS (or GDAL?) unaware of edit_id being the PK
> field and it does return it. Works both in 3.10 and 3.25.
>
> Raymond
>
>
>
> On 31-03-2022 18:15, Alessandro Pasotti wrote:
> > Hi Raymond,
> >
> > your code looks good.
> >
> > Can you provide a test file?
> >
> > There are some test in core here:
> >
> https://github.com/qgis/QGIS/blob/master/tests/src/python/test_qgsproviderconnection_ogr_gpkg.py
> > <
> https://github.com/qgis/QGIS/blob/master/tests/src/python/test_qgsproviderconnection_ogr_gpkg.py
> >
> >
> >
> > On Thu, Mar 31, 2022 at 6:09 PM Raymond Nijssen via QGIS-Developer
> > <qgis-developer at lists.osgeo.org <mailto:qgis-developer at lists.osgeo.org>>
>
> > wrote:
> >
> >     Hi devs,
> >
> >     I'm working on a plugin that connects to a custom table (without
> >     geometry) in a GPKG. When I'm sending queries to it, I keep on
> getting
> >     unexpected results. Somehow the id field is missing, sometimes it
> >     appears twice (!). And this seems to be different between QGIS 3.10
> and
> >     3.25. (I think it changed since 3.22).
> >
> >     Here is a piece of my code:
> >
> >
> >     fn = '/path/to/test.gpkg'
> >     md = QgsProviderRegistry.instance().providerMetadata('ogr')
> >     conn = md.createConnection(fn, {})
> >
> >     q = 'select * from test_table;'
> >     qr = conn.executeSql(q)
> >     print(qr) # No id field in result
> >
> >
> >
> >     Before diving into the QGIS code I'd like to check if this is the
> >     way to
> >     go. Or should i use another way to query that GPKG (SQLite) db?
> >
> >
> >     Kind regards,
> >     Raymond
> >     _______________________________________________
> >     QGIS-Developer mailing list
> >     QGIS-Developer at lists.osgeo.org <mailto:
> QGIS-Developer at lists.osgeo.org>
> >     List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> >     <https://lists.osgeo.org/mailman/listinfo/qgis-developer>
> >     Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> >     <https://lists.osgeo.org/mailman/listinfo/qgis-developer>
> >
> >
> >
> > --
> > Alessandro Pasotti
> > QCooperative: www.qcooperative.net <https://www.qcooperative.net>
> > ItOpen: www.itopen.it <http://www.itopen.it>
>


-- 
Alessandro Pasotti
QCooperative:  www.qcooperative.net
ItOpen:   www.itopen.it
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20220406/822c2d1b/attachment.html>