PyQGIS – Correct Way to Use DROP TABLE on SpatiaLite

pyqgisqgisspatialite

I try to delete a table in an SpatiaLite database using PyQGIS. The table is deleted, but still shown in the browser panel.

enter image description here

I'm using the following code:

query = "DROP TABLE table_name;"
cursor.execute(query)

This deletes the table content and fields, but the table is still shown in the browser.
Deleting the table from the browser then produces an error "Unable to delete table…"

When deleting a table using the browser panel, it then is totally removed from the database.

Deleting the table using PyQGIS, the references to it, in other tables seem to survive, thus the table is still shown.

Does anybody have a solution/best practice for this?

QGIS Version 3.22

Best Answer

DROPTABLE() should work OK if the SpatiaLite version that your QGIS is using is 5.0 or higher https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Drop-RenameTable+and+RenameColumn.

import spatialite as db
spatialite_db_path = 'db_testing.sqlite'
connection = db.connect(spatialite_db_path)
cursor = connection.cursor()
query = "SELECT DropTable(NULL, 'name_of_table_to_delete')"; # NULL takes 'main' as default
cursor.execute(query)
connection.close()

If you have an older version of SpatiaLite the deprecated DropGeoTable() function should be able to remove the triggers and rows from the metadata tables.

Because SQLite is ACID (atomic, consistent, isolated, and durable) you must test that the table and all the references have been disappeared by using the same database connection that you used for DROP TABLE, or from a newer connection.

Related Question