Table still exists even though database file has been deleted

Question

I'm trying to create a fresh database each time I test my SQL code as I'm learning SQL, by referring to a file that I delete before making a new database in its place (I'm aware of :memory: and might use that if using files doesn't work), but SQLite says that the table already exists whenever it's a file path I've used before. For example, I used the "recipes3.db" path and it had no error, but then I deleted the file and used it again and it said the table already existed. I've restarted the interpreter and reset connections and nothing frees up the file. The files are being created and deleted in the expected directory. I have no idea where this data is sticking around such that I can't reuse paths.

I've tested some more and I can sqlite3.connect("recipes2.db") then select * from recipes and there's a table with that name in the database, as I created before, even though the "recipes2.db" file doesn't exist, before or after running the command. Going with "recipes5.db", it doesn't see any such table as I haven't used that path. Now that path contains a table. So there's some memory somewhere that I don't know about.

My code:

def myPath(s):
    return "C:\\Users\\Kevin\\Desktop\\Python\\" + s

def loadDataIntoDatabase(database):
    sqliteClear(database, None)
    conn = sqlite3.connect(database)
    sqliteFile(conn, "recipe_tables_init.txt")
    cursor = conn.cursor()
    with open(myPath("recipe_list.txt"), "r") as file:
        for line in file:
            a = tuple(line.split(","))
            cursor.execute("insert into recipes values (?, ?, ?, ?)", a)
    with open(myPath("recipe_item_list.txt"), "r") as file:
        for line in file:
            a = tuple(line.split(","))
            cursor.execute("insert into recipe_items values (?, ?, ?, ?)", a)
    conn.commit()
    conn.close()

def sqliteClear(filename, conn):
    if not conn:
        conn = sqlite3.connect(filename)
    conn.close()
    s = myPath(filename)
    if os.path.isfile(s):
        os.remove(s)


def sqliteFile(conn, filename):
    cursor = conn.cursor()
    with open(myPath(filename), "r") as file:
        cursor.executescript(file.read())
    cursor.close()

Edit 2: The contents of recipe_table_init.txt:

create table recipes (
    recipe_name varchar (30) primary key,
    category varchar (30),
    energy_required integer not null,
    enabled_or_disabled character (1) not null,
    constraint ed_constraint
        check (enabled_or_disabled in ('e', 'd'))
);

create table recipe_items (
    recipe_name varchar (30) not null,
    item_name varchar (30) not null,
    input_or_output character (1) not null,
    item_amount integer not null,
    constraint io_constraint
        check (input_or_output in ('i', 'o')),
    constraint branch primary key
        (recipe_name, item_name, input_or_output),
    constraint name_fk foreign key (recipe_name)
        references recipes (name)
            on delete cascade
);

I'm only calling loadDataIntoDatabase on simple names like *.db and getting this problem. I've called the other functions, but even just calling that one function at the start of a interpreter (and then again to cause the table repeat) causes the problem, so I don't think they are relevant, apart from their use in loadDataIntoDatabase of course.

Edit: I'm aware of this question but it unfortunately seems to have little to do with what I'm doing.

Please show how you are calling these methods and show content of `recipe_tables_init.txt` which appears to create tables? — Parfait, Sep 15 '20 at 21:10
@Parfait I added what you asked about, thanks for the suggestions. — Fricative Melon, Sep 16 '20 at 01:46
Your `sqliteClear` function creates an empty file, rather than deleting it? An empty file is not necessarily a valid SQLite database... — Jiří Baum, Sep 16 '20 at 01:53
Also, you're inconsistent about whether `myPath` is called on a filename or not, so it's possible that the file you're deleting and the file that's actually being used are different. — Jiří Baum, Sep 16 '20 at 01:56
@sabik Oops, I changed it to removing the file but didn't update here. I'm not sure where the inconsistency is, as `myPath` seems to always be called on a filename, given that `loadDataIntoDatabase` also always gets one — Fricative Melon, Sep 16 '20 at 02:13
The `sqlite3.connect(database)` call uses the filename without running it through `myPath`. — Jiří Baum, Sep 16 '20 at 02:20

Parfait · Accepted Answer · 2020-09-17T14:04:52.437

As discussed, use absolute paths for any SQLite database references. With relative paths, new databases is situated wherever script file resides. Even use os.path.join with raw string to recognize single backslash. Even consider executemany:

import os

def myPath(s): 
  db_path = r"C:\Users\Kevin\Desktop\Python"
  return os.path.join(db_path, s)

def loadDataIntoDatabase(database): 
   sqliteClear(myPath(database), None) 
   conn = sqlite3.connect(myPath(database))
   cursor = conn.cursor()

   sqliteFile(conn, "recipe_tables_init.txt") 

   with open(myPath("recipe_list.txt"), "r") as file: 
     rows = [line.split(",") for line in file]
     cursor.executemany("insert into recipes values (?, ?, ?, ?)", rows) 

   with open(myPath("recipe_item_list.txt"), "r") as file:
     rows = [line.split(",") for line in file] 
     cursor.executemany("insert into recipe_items values (?, ?, ?, ?)", rows)
             
   conn.commit()
 
   cursor.close()
   conn.close()

Table still exists even though database file has been deleted

1 Answers1