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.nijs...@terglobo.nl
<mailto:r.nijs...@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@lists.osgeo.org
<mailto:qgis-developer@lists.osgeo.org>
<mailto:qgis-developer@lists.osgeo.org
<mailto:qgis-developer@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@lists.osgeo.org
<mailto:QGIS-Developer@lists.osgeo.org>
<mailto:QGIS-Developer@lists.osgeo.org
<mailto:QGIS-Developer@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>
_______________________________________________
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
