Python - Can't delete a SQLite3 database if connection is closed

[Maicol Batti]

I'm writing a little script that deletes all files that ends with the extension .db, but the file already opened with a connection can't be deleted if the connection is closed...
Error:

os.remove(os.path.join(path,f))
PermissionError: [WinError 32] Can't access the file. The file is used by another process: 'settings.db'

See it in the code below:

import sqlite3 as sql
conn=sql.connect("settings.db", isolation_level=None)
c=conn.cursor()
c.close()
conn.close()
filelist = [ f for f in os.listdir(path) if f.endswith(".db") ]
for f in filelist:
    os.remove(f)

Do you know how to solve this?
Thanks

[Roger Binns]

On 26/01/18 09:16, Maicol Batti wrote:
> I'm writing a little script that deletes all files that ends with the
> extension .db, but the file already opened with a connection can't be
> deleted if the connection is closed...

You presumably mean "still open". This is a restriction from the
Windows operating system. Files open by any process(*) cannot be
deleted. There are ways of registering files to be deleted on the next
reboot which is how installers and similar handle this issue.

The good news however is that you can rename files in use, and can often
rename them into another directory. In this case renaming them into the
temp directory (on the same drive) would do the trick.

> See it in the code below:

CPython is a garbage collected implementation, so you (usually) have to
wait until later for it to happen. You may find a call to gc.collect(2)
after the closes does the trick. Also in addition to doing a close, do
a del. Like:

del c
del conn

> ends with the extension .db

My guess is you are trying to do cleanup, perhaps as part of testing.
Be aware that there are more files making up the database including
journals etc.

https://sqlite.org/tempfiles.html

One very effective way of corrupting databases is deleting or not
deleting the accompanying files. See 1.3:

https://www.sqlite.org/howtocorrupt.html

(*) the other fun Windows problem is "tag alongs". Things like backup
agents, virus checkers etc. They often open the files just as you close
them, so it doesn't matter how well your code cleans up. The usual
solution (and implemented in SQLite itself too) is to do several retries
with short waits before declaring a permanent error.

If you want to see what is happening under the hood, then Process
Explorer is good for this sort of thing:

https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer

The APSW test suite goes to some lengths to delete the databases between
individual tests:

https://github.com/rogerbinns/apsw/blob/master/tests.py#L336

And needs a -shm in that list too ...

Roger

[Vlad]

Roger, thanks so much for all the info, gc.collect call did it for me, after hours of headbanging!