1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rico
  • 11
  • 2
  • 1
    Seems to be a bug in the QODBC plugin: see [QTBUG-36160](https://bugreports.qt.io/browse/QTBUG-36160). Can you test using PyQt6? – ekhumoro Nov 23 '22 at 14:26
  • Now i test other SQL-plugins and PyQt6 and pyodbc is working. I get the right result. I will refactor the code to work with PyQt6. Thank you @ekhumoro. – rico Nov 24 '22 at 12:15
  • The cause of the error seems to be related to a Windows update KB5019959. It change the c:\windows\SysWOW64\sqlsrv32.dll (Win x64) or c:\windows\sqlsrv32.dll (Win x86) – rico Nov 26 '22 at 22:30
  • A workaround is to use the "SQL Server Native Client 11.0" db.setDatabaseName(f'Driver={{**SQL Server Native Client 11.0**}};Server={server}; Database={database};UID={username};PWD={password}') – rico Nov 27 '22 at 13:27

1 Answers1

0

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);
user20488960
  • 49
  • 4