Testing MySQL connection

[tim23x]

Hi, thanks a lot for posting this build - I've been trying to connect to a MySQL database using Qt for ages and this seems like a massive step forward compared to trying to build from source code in Windows 10. I've installed the dll's as per instructions and I'm not having any luck with getting data from my database. Here is the code I'm using to try to connect:

from mysql.connector import connect, Error
from PyQt5.QtSql import QSqlDatabase, QSqlQuery

db = QSqlDatabase('QMYSQL')
db.setDatabaseName('monty')
db.setPort(3306)
db.setHostName('127.0.0.1')
db.setUserName('graham')
db.setPassword('gilliam')
if not db.open():
    print('Could not open db')
print(QSqlDatabase.drivers())
print(db.tables())
query = QSqlQuery("SELECT * FROM parrot", db=db)
print('Records found: %d' % query.record().count())

try:
    with connect(host="localhost", user="graham", password='gilliam', database='monty') as connection:
        cursor = connection.cursor()
        query = 'SELECT * FROM parrot'
        cursor.execute(query)
        for(idOperator, first_name, last_name, email_address) in cursor:
            print("{} {} {}".format(
                first_name, last_name, email_address))

except Error as e:
    print(e)

and here is what is returned: Could not open db ['QSQLITE', 'QMARIADB', 'QMYSQL', 'QMYSQL3', 'QODBC', 'QODBC3', 'QPSQL', 'QPSQL7'] [] Records found: 0 John Palin john@circus.org Michael Idle michael@circus.org Eric Cleese eric@circus.org

Process finished with exit code 0

As far as I can tell, the QSqlDatabase connection doesn't work, although the 'QMYSQL' driver is available. It doesn't open the db, and can't return tables or records. After trying the QSqlDatabase connection I've connected to the db using mysql-connector-python, and this seems fine (returns records). Is there some way of telling if there's something missing which is messing with the QSqlDatabase connection?

Thanks in advance, Tim

[thecodemonkey86]

hi, you could check the output of print(db.lastError().text()) after the failed "db.open" method call

[tim23x]

Hi, thanks for the quick response! I added the line you suggested, here's a code snippet and the response I get from it :

if not db.open():
    print(db.lastError().text())
    print('Could not open db')
print(QSqlDatabase.drivers())

and the response was:

Driver not loaded Driver not loaded Could not open db ['QSQLITE', 'QMARIADB', 'QMYSQL', 'QMYSQL3', 'QODBC', 'QODBC3', 'QPSQL', 'QPSQL7']

I'm using PyCharm and I've put the drivers into the virtual environment for the project:

db_interface\venv\pythonproject\Lib\site-packages\PyQt5\Qt5\bin

contains amongst other things

libmysql.dll libcrypto-1_1 libssl-1_1

and

db_interface\venv\pythonproject\Lib\site-packages\PyQt5\Qt5\plugins\sqldrivers

contains

qsqlite.dll qsqlmysql.dll qsqlodbc.dll qsqlpsql.dll

Does this look right to you?

Thanks (again) in advance!

Tim

[thecodemonkey86]

Library locations seem alright to me, although I never used PyCharm, and I'm no expert in Python. I just have a standard Python 3.9 installation running via Qt Creator. Did you already try setting the environment variable QT_DEBUG_PLUGINS = 1, which might print some hints why the driver cannot be loaded or at which location it is looking for the driver DLL?

My library locations using the standard Python installation are as follows:

• qsqlmysql.dll (the MSVC 2019 build): C:\Users\<Username>\AppData\Local\Programs\Python\Python39\Lib\site-packages\PyQt5\Qt\plugins\sqldrivers

• libmysql and libcrypto-1_1.dll/libssl-1_1.dll: C:\Users\<Username>\AppData\Local\Programs\Python\Python39, or any other path in PATH variable works for me as well

[tim23x]

I've uninstalled all of my fancy environments and gone back to a standard Python install, also now using the PyQt6 MSVC version. Working now, thanks a lot for your help and thanks again for the build.