How to close connection to a DuckDB backend?

[dimitri-b]

Hi,
I was wondering if there is a way to close the open Ibis connection to a DuckDB backend.

My use case: I'm developing some data processing code, for which I generate a test database in DuckDB with fake data, and then use a Jupyter notebook to inspect the generated data and, if any changes are needed - re-generate it.
But it appears that the notebook locks the connection so I need to restart the Jupyter kernel to drop the lock, so that I can re-generate the data.
I believe some other backends have an explicit close() method, but not DuckDB. Is there a way?

[cpcloud]

@dimitri-b Hi, thanks for opening the discussion!

Any chance you could show the code and a traceback showing the behavior you're describing?

[gforsyth]

The first run of the script is a red herring -- it's just creating a test database. The issue is that you have an open connection to test.duckdb in your active jupyter kernel and then you try to connect to the same test.duckdb file while the kernel is still running.

There is no helper method to close the connection while the Jupyter kernel is still running, but as a workaround, you can do

del conn

import gc
gc.collect()

[cpcloud]

Ah, right.

@dimitri-b The only way this can work is if all open connections were opened in read-only mode. You can't, for example, open 1 in read-write mode and the rest in read-only mode, they all must be opened in read-only mode.

[cpcloud]

You can open connections in read-only mode with ibis using:

con = ibis.duckdb.connect(..., read_only=True)

[dimitri-b]

Thank you, I experimented with the suggestions:

1. Using read-only mode in all connections doesn't work in this use case, as I need to use the generate -> check -> re-generate -> check -> repeat pattern, which requires a 'write' connection.
2. Using the gc trick did not work, unfortunately.
3. I found that conn.con.dispose() did the job, at least in this case, with both 'read-only' and default mode. I understand this reaches into the internals (SQLAlchemy backend) and uses a method that has the side effect of closing the connection.

...has the effect of fully closing all currently checked in database connections. Connections that are still checked out will not be closed, however they will no longer be associated with this _engine