Python PermissionError: [WinError 32] The process cannot access the file..... but my file is closed

Question

I have a Python program that creates a SQLite .db file, then executes a .sql script to initialize a table TRANSACTIONS within that db. As part of my design, if anything fails in creating the TRANSACTIONS table, I want to delete the .db itself so that I'm not left with an incomplete database.

My problem is that when I try to delete the .db file with os.remove(db_path), I get a Win32 error that the .db file is in use by another process. I can't figure out what this process is.

My module, initialize_db.py:

import sqlite3 as sql
import os

class initialize_db:

    # Parameters are Path objects of where to create the DB, 
    # and where to find the DB initialization script
    def __init__(self, db_path, init_script_path):
        self.db_path = db_path
        self.init_script_path = init_script_path

        try:
            self.cursor = self.create_db()
            self.initialize_transactions()
        # If an exception is raised, delete the entire database
        except ValueError:
            print('Error encountered, DB not created')
            self.cursor.close()
            os.remove(db_path)  # This is causing the issue
            raise

    def create_db(self):
        '''Creates .db database at self.db_path'''
        conn = sql.connect(self.db_path)
        print('creating database')
        return conn.cursor()

    def initialize_transactions(self):
        '''Initializes the TRANSACTIONS table within the database'''
        with open(self.init_script_path, 'r') as init_script:
            sql_script = init_script.read()

        raise ValueError  # Raise this before executing sql script as a test

        self.cursor.execute(sql_script)

        print('initialization script executed')

I intentionally raise a ValueError within initialize_transactions() to test whether my .db is deleted properly from within __init__(). If I execute this, I get the following:

from initialize_db import initialize_db
initialize_db(my_db_path, my_db_script_path)

Traceback (most recent call last):

...

File "C:\some_database_path\initialize_db.py", line 26, in init

os.remove(db_path)

PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: 'C:\some_database_path\database.db'

So basically, my program is creating my database, but not properly deleting it when an exception is raised. Any thoughts on what process might be using db_path by the time my program gets there? I tried investigating whether I should close a Path object, as well as my cursor, and neither seem to work.

Answer 1

I think your problem is that your own process won't let you delete the database 'cause it has a handle to it. Save your conn like this:

self.conn = sql.connect(self.db_path)

And then, in your except clause, after closing the cursor, close your connection and try to delete the file:

self.cursor.close()
self.conn.close()
os.remove(db_path)

Hope it helps.