[QGIS-Developer] Unexpected results from GPKG table query using executeSql()
Raymond Nijssen
r.nijssen at terglobo.nl
Wed Apr 6 02:06:43 PDT 2022
Hi Alessandro,
Thanks for looking into this!
My QGIS-master + GDAL 3.0.4 returns the same result for that query.
But in QGIS-master, when I try:
q = 'SELECT *, edit_id FROM soilexc_edits ORDER BY edit_id'
I get:
[0, 1, PyQt5.QtCore.QDateTime(2022, 3, 1, 0, 0), 'test1', True]
[1, 2, PyQt5.QtCore.QDateTime(2022, 3, 2, 0, 0), 'test2', True]
[2, 3, PyQt5.QtCore.QDateTime(2022, 3, 3, 0, 0), 'test3', True]
I would expect:
[0, PyQt5.QtCore.QDateTime(2022, 3, 1, 0, 0), 'test1', True, 0]
The point is I'm building a plugin that should work in multiple QGIS
versions (from 3.10), so I'm trying to write queries that will work for
any version and that seems very hard.
For now the edit_id+0 workaround does the trick.
BTW, Another thing I bumped into is the QDateTime in the result always
having the time 00:00. Is that some known limitation?
Kind regards,
Raymond
On 06-04-2022 10:14, Alessandro Pasotti wrote:
> 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
> <mailto: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>
>
> >
> <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>
> <mailto: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>
> <mailto: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>
> > <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>
> > <https://lists.osgeo.org/mailman/listinfo/qgis-developer
> <https://lists.osgeo.org/mailman/listinfo/qgis-developer>>
> >
> >
> >
> > --
> > Alessandro Pasotti
> > QCooperative: www.qcooperative.net <http://www.qcooperative.net>
> <https://www.qcooperative.net <https://www.qcooperative.net>>
> > ItOpen: www.itopen.it <http://www.itopen.it>
> <http://www.itopen.it <http://www.itopen.it>>
>
>
>
> --
> Alessandro Pasotti
> QCooperative: www.qcooperative.net <https://www.qcooperative.net>
> ItOpen: www.itopen.it <http://www.itopen.it>
More information about the QGIS-Developer
mailing list