Error: “Driver not loaded” in PyQt5

Question

i'm about to connect PyQt5 to MySQL. Unfortunately I get the error' Driver not loaded'. Python says the driver's inside:

from PyQt5.QtSql import QSqlDatabase
print(list(map(str, QSqlDatabase.drivers())))

Answer: ['QSQLITE', 'QMYSQL', 'QMYSQL3', 'QODBC', 'QODBC3', 'QPSQL', 'QPSQL7'] I use Windows 7 and Qt Designer is installed

here's my code:

from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel
from PyQt5.QtWidgets import QTableView, QApplication
import sys
app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase('QMYSQL')
db.setHostName('****')
db.setDatabaseName('****')
db.setUserName('****')
db.setPassword('****')
ok = db.open()
if not ok: print(db.lastError().text())
else: print("connected")
query = QSqlQuery(db);
query.exec_('SELECT * FROM tbl_Customers');

Who has experience with it. Thank you very much

Answer 1

The problem was in the application start process. In case of PyQt, you need to start a GUI application before you can actually use the classes under it. Another thing to remember, always try to see the actual error. In your case, you are forcing a lastError() method and missing some valuable points before that. You are only watching the Driver not loaded error. But before that terminal also showed QSqlDatabase: an instance of QCoreApplication is required for loading driver plugins this error, which clearly shows the actual reason of why the driver is not found.

To make a QCoreApplication, you need to add this in your code -

if __name__ == '__main__':
   app = QApplication(sys.argv)

So your code can be -

from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel
from PyQt5.QtWidgets import QTableView, QApplication
import sys


def dbcon():
    db = QSqlDatabase.addDatabase('QMYSQL')
    db.setHostName('****')
    db.setDatabaseName('****')
    db.setUserName('****')
    db.setPassword('****')
    ok = db.open()
    if not ok: print(db.lastError().text())
    # else: print("connected")
    query = QSqlQuery(db)
    query.exec_('SELECT * FROM tbl_Customers')


if __name__ == '__main__':
    app = QApplication(sys.argv)
    dbcon()

I have done a sample with SQlite database, as I have no MySql configured. The demo -

from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel
from PyQt5.QtWidgets import QTableView, QApplication, QMessageBox


def createDB():
    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName('sports.db')
    if not db.open():
        QMessageBox.critical(None, ("Cannot open database"),
                                   ("Unable to establish a database connection.\n"
                                                 "This example needs SQLite support. Please read "
                                                 "the Qt SQL driver documentation for information "
                                                 "how to build it.\n\n"
                                                 "Click Cancel to exit."),
                                   QMessageBox.Cancel)
    return False

    query = QtSql.QSqlQuery()
    query.exec_("create table sportsmen(id int primary key, "
                "firstname varchar(20), lastname varchar(20))")
    query.exec_("insert into sportsmen values(101, 'Roger', 'Federer')")
    query.exec_("insert into sportsmen values(102, 'Christiano', 'Ronaldo')")
    query.exec_("insert into sportsmen values(103, 'Ussain', 'Bolt')")
    query.exec_("insert into sportsmen values(104, 'Sachin', 'Tendulkar')")
    query.exec_("insert into sportsmen values(105, 'Saina', 'Nehwal')")
    return True


if __name__ == '__main__':
    import sys

    app = QApplication(sys.argv)
    createDB()

Answer 2

I'm connecting PyQt5 to MySQL like this:

from PyQt5       import QtCore, QtGui, QtWidgets
#from PyQt5.QtSql import QSqlDatabase, QSqlQuery
import MySQLdb   as mysql                  # pip install mysqlclient        


def convert(in_data):
    def cvt(data):
        try:
            return ast.literal_eval(data)
        except Exception:
            return str(data)
    return tuple(map(cvt, in_data))


class Ui_MainWindow(object):

    def loadconection(self):
        #                        HostName     UserName           Password      DatabaseName
        db = mysql.connect(host="localhost",user="user", passwd="password",db="testdb")
        with db:
            cur = db.cursor()
            #                             table vvvvvvvvv         
            rows = cur.execute("""select * from pyqt5data""")
            data = cur.fetchall()

            for row in data:
                self.add_table(convert(row))

            cur.close()

    def add_table(self, columns):
        rowPosition = self.tableWidget.rowCount()
        self.tableWidget.insertRow(rowPosition)         
        for i, column in enumerate(columns):
            self.tableWidget.setItem(rowPosition, i, QtWidgets.QTableWidgetItem(str(column)))

    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 520)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.tableWidget = QtWidgets.QTableWidget(self.centralwidget)
        self.tableWidget.setGeometry(QtCore.QRect(35, 21, 731, 401))
        self.tableWidget.setColumnCount(5)
        self.tableWidget.setHorizontalHeaderLabels(['Id', 'Name', 'Email', 'Phone', 'Note'])
        self.tableWidget.setObjectName("tableWidget")
        self.tableWidget.setAlternatingRowColors(True)
        self.tableWidget.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.Stretch)

        self.btn_abrirbd2 = QtWidgets.QPushButton(self.centralwidget)
        self.btn_abrirbd2.setGeometry(QtCore.QRect(340, 460, 75, 23))
        self.btn_abrirbd2.setObjectName("btn_abrirbd2")
        self.btn_abrirbd2.clicked.connect(self.loadconection)

        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 21))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.btn_abrirbd2.setText(_translate("MainWindow", "SELECT mysql"))

if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

Answer 3

From an elevated command prompt run:

o Either: pip install --only-binary :all: mysqlclient
o Or: first install the build tools https://visualstudio.microsoft.com/downloads/#build-tools-for-visual-studio-2017 then run: pip install mysqlclient) (this option requires you to download a 4gb+ file)

Once that is done, you can connect like so:

from PyQt5 import QtSql
import mysql.connector

class DbConnection(object):
    def connect(self):
        try:
            self.__db = QtSql.QSqlDatabase.addDatabase("QMYSQL")
            self.__db.setHostName("localhost")
            self.__db.setDatabaseName("yourDB")
            self.__db.setUserName("root")
            self.__db.setPassword("yourPassword")
            self.__ok = self.__db.open()
        except:
            print(self.__db.lastError().text())

        return self.__ok

    def closeConnection(self):
        try:
            self.__db.close()
        except:
            pass

if __name__ == "__main__":
    from PyQt5 import QtWidgets
    import sys
    app = QtWidgets.QApplication(sys.argv)

    conn = DbConnection()
    print("Connected: " + str(conn.connect()))
    conn.closeConnection()

    sys.exit(app.exec_())

Answer 4

I solved this problem by installing version 12.0 of PyQt5 instead of 14. It might also help if you put a copy of libmysql.dll in your project folder