Python: QSqlTableModel works on Windows but not on macOS

[DrTiaZ]

Hello,

I am not able to solve this boring problem with this simple Python script. I only would like to show the content of a simple table into a QTableView. It works perfectly in Windows, as you can see in the picture below, but nothing to do on macOS. I also tried using QSqlQuery and QSqlQueryModel(), again it is fine in Windows but on macOS I'm only able to retrieve columns name.
Has anyone ever had the same problem?

Windows:

macOS (using QSqlTableModel):

macOS (using QSqlQuery and QSqlQueryModel()):

My code is:

import sys, os
from PyQt5 import QtCore, QtGui, QtWidgets, uic, QtSql

class MainWindow(QtWidgets.QMainWindow):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        uic.loadUi("UI/mainwindow.ui", self)
        print()
        db = QtSql.QSqlDatabase.addDatabase("QODBC3")
        hostname="192.168.1.4:3306"
        databasename = "test"
        username="user"
        password="pwd"
        db.setDatabaseName('DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;'% (hostname, databasename, username, password))
        response = db.open()
        if response is False:
            print("Connection error:\t",db.lastError().text())
        else:
            self.model = QtSql.QSqlTableModel(db=db)
            print("Connection status:  "+ str(db.isOpen()))
            self.tableView.setModel(self.model)
            self.model.setTable('canzoni')
            self.model.select()
            print(self.model.lastError().text())
            '''
            self.model = QtSql.QSqlQueryModel()
            self.tableView.setModel(self.model)
            query = QtSql.QSqlQuery("SELECT * FROM canzoni", db=db)
            self.model.setQuery(query)
            print('Numero di righe:\t', query.size())
            #print(self.model.lastError().text())
            '''
app = QtWidgets.QApplication(sys.argv)
window = MainWindow()
window.show()
app.exec_()

Thank you!

[Christian Ehrlicher]

@DrTiaZ said in Python: QSqlTableModel works on Windows but not on macOS:

QODBC3

What Qt version? 'QODBC3' is deprecated - use 'QODBC'

[DrTiaZ]

@Christian-Ehrlicher said in Python: QSqlTableModel works on Windows but not on macOS:

@DrTiaZ said in Python: QSqlTableModel works on Windows but not on macOS:

QODBC3

What Qt version? 'QODBC3' is deprecated - use 'QODBC'

I tried both QODBC3 and QODBC, using PyQt5 and PyQt6. Nothing changed.

[DrTiaZ]

Using

print(self.model.lastError().text())

on macOS return "Unable to find table canzoni"

[Christian Ehrlicher]

@DrTiaZ said in Python: QSqlTableModel works on Windows but not on macOS:

on macOS return "Unable to find table canzoni"

Then you should create it...

[DrTiaZ]

@Christian-Ehrlicher the table exists. As i said before, the same code (pointing the same server, the same database and the same table, using the same account) works perfectly on Windows.

[JonB]

@DrTiaZ
Qt just returns what the (ODBC) driver tells it. It is not clear what you expect us to tell you.

FWIW, you might try seeing what SELECT COUNT(*) FROM canzoni returns.

on macOS return "Unable to find table canzoni"

There is something a bit odd/curious here, insofar as if it cannot find the table it should not be able to return the column names.....

[DrTiaZ]

@JonB looking only for a solution :P . As you said it is very weird. I am sure that my code (and the ODBC driver) communicates with my MySQL server, otherwhise it should not be able to retrieve columns name.

As you suggest, I tried these lines of code:

            query = QtSql.QSqlQuery("SELECT * FROM canzoni", db=db)
            print("Executed?:\t" + str(query.isActive()))
            print("Number of rows:\t" + str(query.size()))
            print("Number of columns:\t" + str(query.record().count()))

            '''Iterate over rows'''
            while query.next():
                print(query.value(0))

            '''Iterate over columns'''
            for index in range(0, query.record().count()):
                print(f"Name of columns {index}:\t" + query.record().fieldName(index))

And the result is only columns name. No rows (data) were retrieved.

Surfing the net, I found an old and similar topic on this forum (Click Me!) marked as solved (Spoiler: the solution was to update the Qt5... I did it before open the topic).

I really have no idea.

[JonB]

@DrTiaZ said in Python: QSqlTableModel works on Windows but not on macOS:

I really have no idea.

Nor do I, so these are just things I personally would try in an attempt to see where the problem might lie:

• SELECT COUNT(*) FROM canzoni
• SELECT 1, 2, 3

Do you get results back from either of these (exactly as written, as QSqlQuerys, not QSqlQueryModel)?

[SGaist]

Hi,

Maybe a silly question and also to rule out the obvious: are hitting the same server (and database) from both Windows and macOS ?

[JonB]

@SGaist said in Python: QSqlTableModel works on Windows but not on macOS:

are hitting the same server from both Windows and macOS ?

OP has said:

(pointing the same server, the same database and the same table, using the same account)

I do hope we can take him at his word, else this is a wild goose chase...!
@DrTiaZ Please stake your life on this, we have had countless times people say they are using the same and then end up saying "Oh they got it wrong it's a different server" and that gets very irritating!!

[SGaist]

@JonB good point, my question was incomplete (and updated). You might be hitting the same machine but directed to a different database.

[DrTiaZ]

Certainly, you are right.

This is the MySQL server that I am pointing to:

It is reacheable using Terminal from macOS. There are no other databases call "test". As you can see, it contains 3 tables. Specifically, "canzoni" has 3 fields (canzoni_id, titolo, album_id).

I'm going to show you the best proof I can provide to ensure that I am operating on the correct server.

As you can see, the table "canzoni" contains the following 7 rows:

Now, if I try to execute an INSERT INTO statement via my script, using these lines

            query_to_execute = "INSERT INTO canzoni (titolo, album_id) VALUES ('Test1', '1')"
            query = QtSql.QSqlQuery(query_to_execute, db=db)

the application correctly insert the new data into "canzoni".

So I can suppose that ODBC driver works correctly, my application is able to point the correct server and also to query it.

The problem using macOS is only to retrieve data from it.

If I try

SELECT COUNT(*) FROM canzoni;

the result is

[JonB]

@DrTiaZ
Any reason you chose not to try the SELECT 1, 2, 3 I suggested? That's why I typed it in....

[DrTiaZ]

@JonB
Sorry, I forgot to upload the last pic. This is the result

[JonB]

@DrTiaZ
That implies to me that it is succeeding in retrieving the result set (rows) from the query from the database --- as opposed to never retrieving data. Don't know where that leaves you for your case though. Nor why Number of rows keeps being reported as -1.

[DrTiaZ]

But also SELECT 1, 2, 3 fails to retrieve the queryset, in facts it retrieve only columns name and 0 row(s), like the queries before.

Documentation relative to QtSql.QSqlQuery.size() says "Returns the size of the result (number of rows returned), or -1 if the size cannot be determined or if the database does not support reporting information about query sizes. Note that for non-SELECT statements ( isSelect() returns false ), will return -1. If the query is not active ( isActive() returns false ), -1 is returned."

I really don't understand why this happens.

[Christian Ehrlicher]

@DrTiaZ said in Python: QSqlTableModel works on Windows but not on macOS:

I really don't understand why this happens.

I would blame the ODBC driver then.

[JonB]

@DrTiaZ said in Python: QSqlTableModel works on Windows but not on macOS:

But also SELECT 1, 2, 3 fails to retrieve the queryset, in facts it retrieve only columns name and 0 row(s), like the queries before.

Ah, yes. I was expecting "No column name" as the titles for each column and thought the row shown was the single data row, but I think you're right.

I don't know where you go next, or what help you will get here. Does MacOS supply an ODBC tool to allow you to query/view the data?

[DrTiaZ]

@Christian-Ehrlicher said in Python: QSqlTableModel works on Windows but not on macOS:

@DrTiaZ said in Python: QSqlTableModel works on Windows but not on macOS:

I really don't understand why this happens.

I would blame the ODBC driver then.

I thought the same, but as @JonB suggested before, using iODBC tool and connecting to MySQL server via ODBC 8.0 Unicode Driver (same driver used in my Py script) I can view the data.