What could be the reason why QSQLITE ATTACH DATABASE is hanging

Question

I'm struggling on a very uncommon failure. I had a program running which attaches a configuration database to a production database. I used the ATTACH DATABASE command from sqlite. It was working fine and I was glad with that solution. From one run to the other it stopped attaching. If I use in the environment of the Sqlviewer I can open the first DB and run an SQL Statement like ATTACH DATABASE 'c:\DATEN\temp\clcconfig.db' as 'configdb' and I see all tables. The enclosed snipped includes the command I used as it was running. I'm using Pyqt 5.15.1 with Python 3.7 under Windows 10 1909

I have no idea how this could happen. Can anybody test the enclosed code using any databases.

from PyQt5.QtSql import QSqlDatabase, QSqlQuery
import os


maindb = 'C:/DATEN/temp/Test20.db'
confdb = 'c:/DATEN/temp/clcconfig.db'

if os.path.exists(maindb):
    print("maindb exists")
if os.path.exists(confdb):
    print("confdb exists")

db = QSqlDatabase.addDatabase ("QSQLITE")
db.setDatabaseName (maindb)
if db.open ():
    print ("Main DB open")

print ("DB driver     :", db.drivers ())
print ("DB Names      :", db.databaseName ())
print ("db Connections:", db.connectionNames ())
print ("db Tables     :", db.tables ())

sql = "ATTACH DATABASE '%s' AS %s"%(confdb,'configdb')
qry = QSqlQuery (db)
qry.exec_ (sql)
print ("sqlexec fault:", qry.lastError ().text ())

print ("DB Names      :", db.databaseName ())
print ("db Connections:", db.connectionNames ())
print ("db Tables     :", db.tables ())

print ("DB Error      :", db.lastError ().databaseText ())
print ("driver Error  :", db.lastError ().driverText ())

Answer 1

The tables() method only returns the tables of the sqlite_master of the main database, so the name of the attached tables is not obtained. If you want to get the tables from the attached databases then you must use a SQL query:

import os
import sys

from PyQt5.QtSql import QSqlDatabase, QSqlQuery


maindb = "C:/DATEN/temp/Test20.db"
confdb = "C:/DATEN/temp/clcconfig.db"

if os.path.exists(maindb):
    print("maindb exists")
if os.path.exists(confdb):
    print("confdb exists")

db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(maindb)
if not db.open():
    print(db.lastError().text())
    sys.exit(-1)

print("Main DB open")

qry = QSqlQuery(db)
qry.prepare("ATTACH ? AS ?")
for path, name in ((confdb, "configdb"),):
    qry.addBindValue(path)
    qry.addBindValue(name)
    if not qry.exec_():
        print(qry.lastError().text())
        sys.exit(-1)

databases = []
qry = QSqlQuery("PRAGMA database_list", db)
if not qry.exec_():
    print(qry.lastError().text())
    sys.exit(-1)

rec = qry.record()
i = rec.indexOf("name")
while qry.next():
    databases.append(qry.value(i))

tables = []

for database in databases:
    qry = QSqlQuery(
        "SELECT name FROM %s.sqlite_master WHERE type='table'" % (database,), db
    )
    if not qry.exec_():
        print(qry.lastError().text())
    while qry.next():
        tables.append(qry.value(0))
print(tables)