Connect to multiple databases in PyQt5

Question

I need to connect to two different databases and servers. Right now I can only connect to one database using this code:

def createConnection()
    global db
    db = QSqlDatabase.addDatabase('QODBC')
    db.setDatabaseName('DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWS=%s;' % (SERVER1, DATABASE1, USERNAME1, PASSWORD1))
    if db.open():
        print("connected")
        return True
    else:
        print("failed")
        return False

Then somewhere in my code I call something like this:

if createConnection():
    qry = QSqlQuery(db)
    qry.prepare("SELECT * FROM tbl_name")
    qry.exec()
    while qry.next()
        col1 = qry.value("column1")
        col2 = qry.value("column2")

Now, I have to connect also to another database that is different: SERVER2, DATABASE2, USERNAME2, PASSWORD2. How can I do that?

Did my answer solve your problem? If not, please explain why so that I can try to improve it. — ekhumoro, Nov 20 '19 at 18:51
I don't but the error keeps happening about addDatabase, I've done some research and I'm ended up using pyodbc now, thanks — kokoro, Nov 20 '19 at 22:25

score 0 · Answer 1 · answered Nov 18 '19 at 07:45

0

Why not create function who takes variables.

def createConnection(SERVER, DATABASE, USERNAME, PASSWORD)
    global db
    db = QSqlDatabase.addDatabase('QODBC')
    db.setDatabaseName('DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWS=%s;' % (SERVER, DATABASE, USERNAME, PASSWORD))
    if db.open():
        print("connected")
        return True
    else:
        print("failed")
        return False

while calling provide those variables.

if createConnection(SERVER1, DATABASE1, USERNAME1, PASSWORD1):
    qry = QSqlQuery(db)
    qry.prepare("SELECT * FROM tbl_name")
    qry.exec()
    while qry.next()
        col1 = qry.value("column1")
        col2 = qry.value("column2")

answered Nov 18 '19 at 07:45

LOrD_ARaGOrN

3,884
3
27
49

I tried this, but it returns an error saying QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work and QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed – kokoro Nov 18 '19 at 10:55
@kokoro i guess you need to close the db connection once your work is done. – LOrD_ARaGOrN Nov 19 '19 at 01:10

score 0 · Answer 2 · answered Nov 18 '19 at 14:25

If you want multiple connections, you must give each one a unique name. If no name is supplied, the default connection will be used, and any previous connection will be removed.

So you need to do something like this:

def createConnection(conn, server, dbase, user, pwd)
    if QSqlDatabase.contains(conn):
        QSqlDatabase.removeDatabase(conn)
    db = QSqlDatabase.addDatabase('QODBC', conn)
    db.setDatabaseName('DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWS=%s;' % (server, dbase, user, pwd))
    if db.open():
        print("connected")
        return True
    else:
        print("failed")
        return False

and then do:

CONN1 = 'conn_01'
CONN2 = 'conn_02'
...

if createConnection(CONN1, SERVER1, DATABASE1, USERNAME1, PASSWORD1):
    qry = QSqlQuery(QSqlDatabase.database(CONN1))
    qry.prepare("SELECT * FROM tbl_name")
    qry.exec()
    while qry.next()
        col1 = qry.value("column1")
        col2 = qry.value("column2")

Connect to multiple databases in PyQt5

2 Answers2