How do I ensure closing of database connection using PyQt5.QtSql?

Question

I wrote an app that kept having the message

QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.

and I found out how to stop that by providing a 'connection name' when doing

self.db_connection = QSqlDatabase.addDatabase("QSQLITE", self.db_connection_name)

I wanted to ensure that all my database connections would be properly closed by using a DatabaseConnectionMaker class along with with blocks.

In the code below I have done that, but now I keep getting the following output from a small feasibility app to confirm successful use of the with blocks:

Table already exists and does not need to be created by dbConnection1.
QSqlDatabasePrivate::removeDatabase: connection 'dbConnection1' is still in use, all queries will cease to work.
Data inserted successfully by dbConnection1
Database connection dbConnection1 closed!!!

Process finished with exit code 0

I just can't seem to glean what I'm doing wrong here. No matter what I try the QSqlDatabasePrivate::removeDatabase: connection 'dbConnection1' is still in use, all queries will cease to work.' will inevitably appear. Can someone enlighten me?

Here is the app:

import sys

from PyQt5.QtSql import QSqlDatabase, QSqlQuery
from PyQt5.QtWidgets import QPushButton, QVBoxLayout, QMainWindow, QApplication, 
QWidget


class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
    
        self.db_name_main = "example.db"
    
        self.setGeometry(100, 100, 570, 600)
        self.setWindowTitle("Database Manipulation")
    
        # Create buttons
        create_database_button = QPushButton("Create Database ", self)
        create_database_button.clicked.connect(self.test_database_creation)
    
        central_widget = QWidget()
        layout = QVBoxLayout(central_widget)
        layout.addWidget(create_database_button)
    
        self.setCentralWidget(central_widget)
    
    def test_database_creation(self):
        with DatabaseConnectionMaker(self.db_name_main, "dbConnection1") as db_connection1:
            db_connection1.create_table("""CREATE TABLE IF NOT EXISTS example_table (
                   id INTEGER PRIMARY KEY,
                   name TEXT,
                   age INTEGER)""")
    
            db_connection1.insert_data("INSERT INTO example_table (name, age) "
                                       "VALUES (?, ?), (?, ?), (?, ?), (?, ?)",
                                       ["John", 30, "Alice", 25, "Bob", 35, "Eve", 28])
    
    
class DatabaseConnectionMaker:
    def __init__(self, db_name, db_connection_name):
        self.db_name = db_name
        self.db_connection = None
        self.db_connection_name = db_connection_name

    def connect(self):
        self.db_connection = QSqlDatabase.addDatabase("QSQLITE", self.db_connection_name)
        self.db_connection.setDatabaseName(self.db_name)
        if not self.db_connection.open():
            print("Failed to connect to database:", self.db_connection.lastError().text())
            return False
        return True

    def create_table(self, create_table_sql):
        query = QSqlQuery(self.db_connection)
        # Check if the table already exists
        if not self.table_exists():
            # If the table does not exist, execute the create_table_sql
            # query = QSqlQuery(self.db_connection)
            if not query.exec_(create_table_sql):
                print("Error creating table:", query.lastError().text())
            else:
                print(f"Table created successfully by {self.db_connection_name}")
                query.clear()
                del query
        else:
            print(f"Table already exists and does not need to be created by {self.db_connection_name}.")
            # Finished with the query, so clear it
            query.clear()
            del query

    def table_exists(self):
        tables = self.db_connection.tables()
        return "example_table" in tables

    def insert_data(self, insert_data_sql, values):
        query = QSqlQuery(self.db_connection)
        query.prepare(insert_data_sql)
        for i, value in enumerate(values):
            query.bindValue(i, value)
        if not query.exec_():
            print("Error inserting data:", query.lastError().text())
        else:
            print(f"Data inserted successfully by {self.db_connection_name}")
            query.clear()
            del query

    def close(self):
        self.db_connection.close()
        # QSqlDatabase.removeDatabase(self.db_connection.connectionName())
        QSqlDatabase.removeDatabase("dbConnection1")
        del self.db_connection

    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()
        print(f"Database connection {self.db_connection_name} closed!!!")


if __name__ == "__main__":
    app = QApplication(sys.argv)
    mainWindow = MainWindow()
    mainWindow.show()
    sys.exit(app.exec_())

Answer 1

The docs on removeDatabase refer to C++ and point out problems with scoping. It turns out that in Python using PyQt5.QtSql one must place a del self.db_connection after self.db_connection.close() and before QSqlDatabase.removeDatabase("dbConnection1") and all is fine!!

I was doing the del self.db_connection after both self.db_connection.close() and QSqlDatabase.removeDatabase("dbConnection1"), which is wrong.