QtSql connect to multiple databases

Question

I am having a problem with Python 3.7 PyQt5 QtSql. I am trying to have multiple MSSQL databases connected, but cannot figure how to do it.

I have the following function to connect:

from PyQt5 import QtSql
def connectDb(database_name):
    SERVER_NAME = 'COMPUTER\\SQLEXPRESS'
    DATABASE_NAME = database_name
    connString = f'DRIVER={{SQL Server}};'\
                 f'SERVER={SERVER_NAME};'\
                 f'DATABASE={DATABASE_NAME}'
    db = QtSql.QSqlDatabase.addDatabase("QODBC")
    db.setDatabaseName(connString)
    if not db.open():
        print(db.lastError().text())
    return db

To connect to a database:

db1 = connectDb("Database1")
db2 = connectDb("Database2")

However, when trying to connect to the second database, I get the message:

QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.
QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

The reason I want to keep multiple databases open is that it is far too slow to keep opening and closing database connections each time I need to query a different database in my code. I'd rather keep them all open and close them when the program exits.

eyllanesc · Accepted Answer · 2020-02-26T15:32:44.707

When a QSqlDataBase is created using the addDatabase() method then the names passed through the connectionName parameter are stored in a dictionary where the key takes that value, if that parameter is not passed then "qt_sql_default_connection" is used causing the creation of the Second database you get a duplicate in the dictionary so Qt issues that warning. A possible solution is to pass it a different name (not tested):

from PyQt5 import QtSql


def connectDb(database_name, connection_name):
    SERVER_NAME = "COMPUTER\\SQLEXPRESS"
    DATABASE_NAME = database_name
    connString = (
        f"DRIVER={{SQL Server}};" f"SERVER={SERVER_NAME};" f"DATABASE={DATABASE_NAME}"
    )
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE", connection_name)
    db.setDatabaseName(connString)
    if not db.open():
        print(db.lastError().text())
    return db

db1 = connectDb("Database1", "connection_1")
db2 = connectDb("Database2", "connection_2")

I wasn't sure how to mark as correct, but I've done it now. My application is running so much quicker now that I'm not connecting and disconnecting each time I'm having to query a different database. — , Feb 27 '20 at 07:39
Thank you so much - just wasted hours trying to see what was going wrong with this, before finding this answer! — Andy Brown, Jun 23 '22 at 10:08

QtSql connect to multiple databases

1 Answers1