Description

I have a database that I already built in python3 utilizing sqlite. Up till this point, I have not had any issues with commit saving changes (with insert commands and delete commands). However, I am trying to utilize an update command and I have not been able to save the changes (it only changes the DB in the working memory despite calling commit().

The goal of this code snippet is to replace the null values in the database with an empty string as I have another function that cannot handle null data. I found a solution to do that here: Find null values from table and replace it with space - sqlite.

Details

Here is the current code that I am trying to execute:

self.cursor.execute(f'UPDATE {tbl_name} SET {col_name} = IFNULL({col_name}, "")')
self.conn.commit()

This code basically goes through the entire database one column at a time and replaces the null values.

Note that self is defined as follows:

Database.conn = sqlite3.connect(self.location + self.name)
Database.cursor = sqlite3.connect(self.location + self.name).cursor()

As earlier stated this correctly operates; however, it will not commit the changes to the actual database. This is verified by both DB browser for sqlite and pulling the data again on a close and re-execute.

I will also note that if I close out of this program and reinitialize it to run it again it will error out for the DB still being locked despite the last line of my code being:

Database.conn.commit() # Save (commit) the changes
Database.conn.close() # Close database

Conclusion

Thanks in advance as I have been beating my head against the wall with this one and have yet to find a problem like this elsewhere!