Completion Refresh throwing cannot open file error

[Alex-Monahan]

Thank you again for building this! When using an in-memory DuckDB, I haven't run into any issues at all. I am getting an error when using a persistent DB though. I get this error each time I connect, as well as each time that I create a table.

I think that the background refresh may need to reuse the existing connection from within that other thread. To do that, there is a special flag we need to pass in the DuckDB connect method to allow for multi-threaded access. Updating from multiple threads isn't guaranteed to not step on top of each other, but from a brief look it seems like your background thread isn't updating anything, just pulling schema info.

DuckDB connection allowing multiple threads:

duckdb_con = duckdb.connect('my_filename_or_path.db', check_same_thread=False)

The error I'm seeing is below. Since the only issue seems to be the background refresher, everything else is working fine that I have tested so far!

(duckcli) C:\Users\Alex\Documents\DuckDB\scratch_work>duckcli from_duckcli.db
Exception in thread completion_refresh:
Version: 0.0.1
Traceback (most recent call last):
GitHub: https://github.com/dbcli/duckcli
  File "C:\ProgramData\Anaconda3\envs\duckcli\lib\threading.py", line 973, in _bootstrap_inner
    self.run()
  File "C:\ProgramData\Anaconda3\envs\duckcli\lib\threading.py", line 910, in run
    self._target(*self._args, **self._kwargs)
  File "C:\ProgramData\Anaconda3\envs\duckcli\lib\site-packages\duckcli\completion_refresher.py", line 69, in _bg_refresh
    executor = SQLExecute(e.dbname)
  File "C:\ProgramData\Anaconda3\envs\duckcli\lib\site-packages\duckcli\sqlexecute.py", line 44, in __init__
    self.connect()
  File "C:\ProgramData\Anaconda3\envs\duckcli\lib\site-packages\duckcli\sqlexecute.py", line 55, in connect
    conn = duckdb.connect(database=db_name)
RuntimeError: IO Error: Cannot open file "from_duckcli.db": The process cannot access the file because it is being used by another process.

from_duckcli.db> select 'test' as testing;
| testing |
|---------|
| test    |
1 row in set
Time: 0.010s

[amjith]

@Alex-Monahan Thanks for testing this and filing the report. Unfortunately I'm unable to reproduce the issue you've described here.

I have added the suggested fix to the main branch. Can you install from the main branch and give it a try?

Here's how you can install directly from the main branch:

pip install -U https://github.com/dbcli/duckcli/archive/refs/heads/main.zip

[Alex-Monahan]

Hey @amjith! Unfortunately that link gives me a 404 both in a command prompt and in the browser. Is there another way you would recommend for me to install from the latest main branch? Otherwise maybe it's easiest to just do a 0.0.2 release and get it from pypi directly?

[amjith]

I've released a new version. You can upgrade using:

pip install -U duckcli 

[dbeatty10]

Error with duckcli == 0.0.2

duckcli == 0.0.1 works for me, but 0.0.2 gives the following error:

connect(): incompatible function arguments. The following argument types are supported:
    1. (database: str = ':memory:', read_only: bool = False, config: dict = {}) -> duckdb.DuckDBPyConnection

Invoked with: kwargs: database='jaffle_shop.duckdb', check_same_thread=False

Environment

• zsh
• duckdb 0.4.0
• Python 3.8.13

[Alex-Monahan]

Hello! The cause of that is that the check_same_thread parameter has been removed in 0.4.0. Now connections manage their own locking between multiple Python threads. I am unfortunately out of time to address it for the next few weeks.
DuckDB 0.3.4 should work in the meantime!

[amjith]

@dbeatty10 Thanks for reporting the issue. I've opened a PR #2. Can you give it a try? You can install directly from that PR using the following command:

pip install -U https://github.com/dbcli/duckcli/archive/refs/heads/upgrade-duckdb040.zip

BTW, duckcli is still in beta. Can you tell me how you found the project?

[dbeatty10]

@amjith I'll try installing directly from that PR via your instructions and let you know how it goes.

When searching for a pure-Python command-line interface for DuckDB, I tried the following search:
https://pypi.org/search/?q=duckdb+command

I think duckcli was around the 8th result, and I was intrigued to give it a try. I was amazed seeing all the stars of projects like pgcli and mycli that are within the dbcli org!

Unanticipated bonus of clicking on lots of links:

• the delightful conversations with your kids you've documented (like this one)

[dbeatty10]

@amjith

Installing with your instructions from PR #2, launching the duckcli, and running a query worked for me 👍

[amjith]

Glad that is working for you. The reason this is still in beta is because I can't get the unit tests to pass yet. The tests were ported from the existing litecli repo, so the tests might not even be relevant. 🤷

Thanks for trying it out and reporting back.