QSqlQuery not committing to SQLite database

TheBigChay

While attempting to debug my program, I discovered a very interesting scenario -- my QSqlQuery SELECT query is showing different results than the same SELECT query run using a SQLite connection.

Result:

FILTER SOURCE DATASET ATTRIBUTES QUERY: SELECT distinct attribute FROM source_dataset_attributes_excluded
got '['Authors']' for items returned from PRAGMA (PySide)
got '[]' for items returned from PRAGMA (SQLite)

Expectation:

FILTER SOURCE DATASET ATTRIBUTES QUERY: SELECT distinct attribute FROM source_dataset_attributes_excluded
got '['Authors']' for items returned from PRAGMA (PySide)
got '['Authors']' for items returned from PRAGMA (SQLite)

Code printing different results from same query in PySide2 and SQLite:

# select distinct attribute from excluded attributes
query = "SELECT distinct attribute FROM source_dataset_attributes_excluded"
excluded_attribute_rows = [dict(row) for row in collection_conn.execute(query).fetchall()]

filter_source_dataset_query = self.execute_query(db=collection_db, query=query, verbose=True, desc="Filter Source Dataset")
print(f"FILTER SOURCE DATASET ATTRIBUTES QUERY: {query}")
items = []
while filter_source_dataset_query.next():
    item = filter_source_dataset_query.value(0)
    items.append(item or '')
print(f"got '{items}' for items returned from PRAGMA (PySide)")

rows = [dict(row) for row in collection_conn.execute(query).fetchall()]
items = []
for row in rows:
    items.append(row['attribute'] or '')
print(f"got '{items}' for items returned from PRAGMA (SQLite)"

As you can see in the above console output, the previous update to the database -- which was a DELETE followed by an INSERT on the PySide connection (in code snippet below) -- doesn't seem to have committed itself to the database, despite my using explicit QSqlDatabase.commit() statements after all INSERT/UPDATE/DELETE queries.

collection_db = common.get_db(self.collection_db)
deselect_source_attributes_query = QtSql.QSqlQuery(collection_db)
deselect_source_attributes_query.clear()
deselected_source_attribute_ids = self.get_selected_rows(self.attribute_selection_include_table_view)
for deselected_source_attribute_id in deselected_source_attribute_ids:
    print(f"deselected source attribute id: '{deselected_source_attribute_id}'")
    id = deselected_source_attribute_id
    query = f"INSERT INTO source_dataset_attributes_excluded SELECT * FROM source_dataset_attributes_included WHERE id='{id}'"
    print(f"about to execute query '{query}'")
    query = f"INSERT INTO source_dataset_attributes_excluded SELECT * FROM source_dataset_attributes_included WHERE id=?"
    deselect_source_attributes_query.prepare(query)
    deselect_source_attributes_query.addBindValue(id)
    if deselect_source_attributes_query.exec_():
        collection_db.commit()
        deselect_source_attributes_query.clear()
        query = f"DELETE FROM source_dataset_attributes_included WHERE id='{id}'"
        print(f"about to execute query '{query}'")
        query = f"DELETE FROM source_dataset_attributes_included WHERE id=?"
        deselect_source_attributes_query.prepare(query)
        deselect_source_attributes_query.addBindValue(id)
        if deselect_source_attributes_query.exec_():
            print(f"successfully executed query '{query}'")
        else:
            print(f"ERROR: FAILED to execute query '{query}'")
            print("deselect_source_attribute query:")
            print("executedQuery: " + deselect_source_attributes_query.executedQuery())
            print("lastQuery: " + deselect_source_attributes_query.lastQuery())
            print("lastError: " + deselect_source_attributes_query.lastError().text())
            print("\n")
        collection_db.commit()
        deselect_source_attributes_query.clear()
    else:
        print("deselect_source_attribute query:")
        print("executedQuery: " + deselect_source_attributes_query.executedQuery())
        print("lastQuery: " + deselect_source_attributes_query.lastQuery())
        print("lastError: " + deselect_source_attributes_query.lastError().text())
        print("\n")
collection_db.commit()

DB Browser for SQLite screenshot also showing result not committed to database:

Any ideas on what might be causing this, and what I can do to prevent this? I need my INSERT/UPDATE/DELETE queries to persist after closing the program.

For some helpful context, here are some technical details:

SQLite: WAL database
Python: 3.9
PySide2: 5.15.2

TheBigChay

When I modify the INSERT and DELETE statements to use the SQLite connection instead of QSqlQuery, the results persist in the database but are not visible to the QSqlQuery SELECT statements used to populate the UI QSqlTableViews:

Result:

got '[]' for items returned from PRAGMA (PySide)
got '['Authors']' for items returned from PRAGMA (SQLite)

Expected:

got '['Authors']' for items returned from PRAGMA (PySide)
got '['Authors']' for items returned from PRAGMA (SQLite)

DB Browser for SQLite (expected) result:

DB Browser for SQLite (expected) result

Code snippet to execute INSERT and DELETE (modified to use SQLite here):

collection_conn = sqlite3.connect(self.collection_db)

deselected_source_attribute_ids = self.get_selected_rows(self.attribute_selection_include_table_view)

for deselected_source_attribute_id in deselected_source_attribute_ids:
    print(f"deselected source attribute id: '{deselected_source_attribute_id}'")
    d = deselected_source_attribute_id
    query = f"INSERT INTO source_dataset_attributes_excluded SELECT * FROM source_dataset_attributes_included WHERE id='{id}'"
    print(f"about to execute query '{query}'")
    collection_conn.execute(query)
    query = f"DELETE FROM source_dataset_attributes_included WHERE id='{id}'"
    print(f"about to execute query '{query}'")
    collection_conn.execute(query)

collection_conn.commit()
collection_conn.close()

Any ideas on why the QSqlDatabase connection will not commit to the database and be visible to other connections? Additionally, any ideas on why the QSqlDatabase connection appears to be stale and will not see new values that have been added/updated in the database that are verifiable by other independent connections (such as in DB Browser for SQLite)?

SGaist

Hi and welcome to devnet,

Can you provide a minimal runnable script that shows this behaviour ?

As for the QTableView update, since you are likely using a QSqlTableModel, you should also use the model to interact with the database (adding, modifying, deleting rows).