Python QtSql.QSqlQuery result wrong in connection with SQL Server database and float values

Question

I create a GUI with PyQt5 and display a SQL Server database table in a tableView widget.

The id, date and text columns are OK, but I have also four float columns. The result from the float columns are None if there is a value in it and if the Value is NULL in the database then I get a 0 in the result.

Developer system is Win11 + VSCode + Python 3.9.6 32Bit with PyQt5 v5.15.4

Database runs on: Win10 x86 + SQL Server 2012 Express, access over TCP/IP port 1433

Here is my code to get the values from the DB

from PyQt5.QtSql import *

SERVER = '127.0.0.1'
DATABASE = 'DbName'
USERNAME = 'user'
PASSWORD = 'password'

db = QSqlDatabase.addDatabase('QODBC')
db.setDatabaseName(f'Driver={{SQL SERVER}}; Server={SERVER}; Database={DATABASE}; UID={USERNAME}; PWD={PASSWORD}')
db.open()

GET_RESULTS =  '''SELECT Id, ModifiedAt, TreadDepthFL, TreadDepthFR FROM Measurement
                  WHERE Id < 4;
               '''
data = QSqlQuery(db)
data.prepare(GET_RESULTS)
data.exec()

while (data.next()):
    print(" | " + str(data.value(0)) + " | " + str(data.value(1)) + " | " + str(data.value(2))+ " | " + str(data.value(3))+ " | ")
    
db.close()

The result of this is:

id	ModifiedAt	TreadDepthFL	TreadDepthFR
1	PyQt5.QtCore.QDateTime(2021, 9, 16, 19, 9, 13, 990)	0.0	0.0
2	PyQt5.QtCore.QDateTime(2021, 9, 16, 19, 16, 2, 137)	None	None
3	PyQt5.QtCore.QDateTime(2021, 9, 17, 8, 36, 41, 607)	None	None

If I check the database with database-tool like HeidiSQL, the values are:

Id	ModifiedAt	TreadDepthFL	TreadDepthFR
1	2021-09-16 19:09:13,990	NULL	NULL
2	2021-09-16 19:16:02,137	6.5414	7.1887
3	2021-09-17 08:36:41,607	6.31942	6.41098

If I move the ModifiedAt to the end, I get the following strange result:

GET_RESULTS =  '''SELECT Id, TreadDepthFL, TreadDepthFR, ModifiedAt FROM Measurement
                  WHERE Id < 4;
               '''

Id	TreadDepthFL	TreadDepthFR	ModifiedAt
1	0.0	0.0	PyQt5.QtCore.QDateTime(2021, 9, 16, 19, 9, 13, 990)
2	None	None	PyQt5.QtCore.QDateTime()
3	None	None	PyQt5.QtCore.QDateTime()

Is there something missing in the code to handle float-values with PyQt5.QtSql?

Answer 1

I experience exactly the same behavior: the float fields are not read correctly when they are non-null and the fields following the float field are read incorrectly too.

I am using C++ QT 4.8.7 under Win10 x64. The problem has appeared with a recent Windows Security Update KB5019959. Uninstalling the update helps. I am still searching for better solutions.

It seems that only the ordering of the query matters (not the order of the fields in the database). So, reordering the fields in the query and accessing them by name will help at least to read the rest of the fields.

UPDATE: There seems to be an easy solution. Just change the type of the column into decimal (adjust the precision to your needs), i.e.

alter table TableName alter column ColumnName decimal(18,6);