[Solved] Anaconda Error: pyqt can’t use QSqlDatabase to connect to MySQL

1. Problem description

Generally speaking, pyqt can connect to MySQL database in the following format:

self.DB = QSqlDatabase.addDatabase('QMYSQL')
self.DB.setDatabaseName("school_club") # Enter the data table you want to access
self.DB.setHostName('localhost')
self.DB.setPort(3306)
self.DB.setUserName('root')
self.DB.setPassword('') # Enter the password for your own database

However, sometimes the connection fails, and debug can’t point out the error clearly. Errors can be found in the following ways.

Method 1: check the driver attached to pyqt

print(QSqlDatabase.drivers())

If ‘qmmysql’ and ‘qmysql3’ are found missing in the driver, it indicates that the error is driver missing. You can use this method to solve the problem. Because under normal conditions, the output should be as follows:

['QSQLITE', 'QMYSQL', 'QMYSQL3', 'QODBC', 'QODBC3', 'QPSQL', 'QPSQL7']

Method 2: print error information

print(self.DB.lastError().text())

This method can also get the specific part of the connection error.

2. How to solve the lack of qmmysql driver?

The author’s pyqt is installed in Anaconda environment, so the folder path to be found by the driver is special.

The general idea is to add two DLL files: qsqlmysql.dll and libmysql.dll in pyqt related folder. At the same time, these two files must be fully matched with the relevant pyqt version.

Step 1: load qsqlmysql.dll

First, open Anaconda prompt, enter the following command line, and adjust the python version to 5.12.1 (only this version comes with qsqlmysql.dll, other versions of this DLL are too difficult to find a matching one)

pip install PyQt5==5.12.1

After this step, qsqlmysql.dll has been successfully installed in the relevant folder.

Step 2: load libmysql.dll

This is relatively easy. First, find the libmysql.dll (this is the default path for MySQL installation, and the user-defined path will be found separately) in the path of C:// program files/MySQL/MySQL server 8.0/bin, and copy it.

Take my computer as an example, paste the file to the following path:

D:\Anaconda\Anaconda\Lib\site-packages\PyQt5\Qt\bin

You can choose the appropriate path according to the relative path of the above path according to the installation location of anaconda.

Restart the programming tool and print again (QSqlDatabase. Drivers()). It is found that ‘qmmysql’ and ‘qmysql3’ have successfully appeared.


Read More: