[Python] How to force DuckDB to reconnect to a file and prevent it from remembering contents...

Motivation


I'm working on a larger dashboard application which uses duckdb. I found out a bug which in a nutshell is like this:

# create new connection
con = duckdb.connect('/somedir/duckdb.db')

# do something

# remove or replace the file
Path('/somedir/duckdb.db').unlink()
shutil.copy('/otherdir/duckdb.db', '/somedir/duckdb.db')

# create new connection to the *new* file with same location
con = duckdb.connect('/somedir/duckdb.db')

# PROBLEM: the connection remembers the contents from the first file!

Question


How do you guarantee that when you create a new duckdb.DuckDBPyConnection object or an Exception is raised with:

import duckdb

con = duckdb.connect('/somedir/duckdb.db')


that duckdb would not try to use some internal cache? In essence, I want a new, clean connection to the specified location or an Exception to be raised no matter what has happened before in the application.

MWE for reproducing the problem


from pathlib import Path
import duckdb

filepath = Path("/tmp/test_duckdb/data.db")
filepath.parent.mkdir(exist_ok=True, parents=True)

con = duckdb.connect(str(filepath))
con.sql(f"CREATE TABLE first AS SELECT 42 AS i, 84 AS j")
print("before removing:\n" + str(con.sql("show tables")))

filepath.unlink()

con2 = duckdb.connect(str(filepath))
print("after removing:\n" + str(con2.sql("show tables")))
con2.sql(f"CREATE TABLE first AS SELECT 42 AS i, 84 AS j")

Actual outcome:


before removing:
┌─────────┐
│ name │
│ varchar │
├─────────┤
│ first │
└─────────┘

after removing:
┌─────────┐
│ name │
│ varchar │
├─────────┤
│ first │
└─────────┘

---------------------------------------------------------------------------
CatalogException Traceback (most recent call last)
Cell In[1], line 15
13 con2 = duckdb.connect(str(filepath))
14 print("after removing:\n" + str(con2.sql("show tables")))
---> 15 con2.sql(f"CREATE TABLE first AS SELECT 42 AS i, 84 AS j")

CatalogException: Catalog Error: Table with name "first" already exists!


Desired outcome:


Raise an Exception on the second connect attempt as there are open connections to the file.

Note:


The problem is part of a larger application and fixing the bugs there is not the point of this question. The point is that even if you have a bug in your application logic, how do you prevent duckdb giving connection to already deleted/replaced database file? Is there some duckdb.clear_all_caches() method or duckdb.connect(filepath, raise_if_open_connections=True)?

Edit: Should probably explicitly add that I'm aware that connections should be closed. The question is: Can you do something about it if the connections are not closed?

Continue reading...