Some issues during first time testing vdsql

[p3k]

Small description

1. I needed to install sqlalchemy which is undocumented (source)
2. vdsql mysql://root:@localhost/mysql causes an error:
   OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)")
   However, vd -f ibis mysql://root:@localhost/mysql works as expected (but is this vdsql at all?)
3. Not sure how the -f option works, anyway – I can set it to an arbitrary string and still get the database view:
   vd -f foobar mysql://root:@localhost/mysql

Expected result

1. Documentation should include sqlalchemy as a required dependency
2. vdsql should work with the database string
3. The -f option should cause an error for unsupported loaders

Actual result with screenshot

Note: I can see the stack trace but I do not know how to copy it without the surrounding TUI getting in the way 🤷 (How can I hide the status box‽)

Update: The stack trace is now complete and the TUI out of the way.

Traceback (most recent call last):
File "/root/.venv/visidata/lib/python3.9/site-packages/pymysql/connections.py", line 649, in connect
sock = socket.create_connection(
File "/usr/lib/python3.9/socket.py", line 843, in create_connection
raise err
File "/usr/lib/python3.9/socket.py", line 831, in create_connection
sock.connect(sa)
ConnectionRefusedError: [Errno 111] Connection refused

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/root/.venv/visidata/lib/python3.9/site-packages/visidata/threads.py", line 220, in _toplevelTryFu
nc
t.status = func(*args, **kwargs)
File "/root/.venv/visidata/lib/python3.9/site-packages/visidata/sheets.py", line 260, in reload
self.loader()
File "/root/.venv/visidata/lib/python3.9/site-packages/visidata/sheets.py", line 285, in loader
for r in self.iterload():
File "/root/.venv/visidata/lib/python3.9/site-packages/visidata/apps/vdsql/_ibis.py", line 123, in iterload
with self.con as con:
File "/usr/lib/python3.9/contextlib.py", line 117, in __enter__
return next(self.gen)
File "/root/.venv/visidata/lib/python3.9/site-packages/visidata/apps/vdsql/_ibis.py", line 93, in get_conn
r = ibis.connect(str(self.source))
File "/root/.venv/visidata/lib/python3.9/site-packages/ibis/backends/__init__.py", line 1409, in connect
return backend._from_url(url, **orig_kwargs)
File "/root/.venv/visidata/lib/python3.9/site-packages/ibis/backends/mysql/__init__.py", line 91, in _fr
om_url
return self.connect(**kwargs)
File "/root/.venv/visidata/lib/python3.9/site-packages/ibis/backends/__init__.py", line 848, in connect
new_backend.reconnect()
File "/root/.venv/visidata/lib/python3.9/site-packages/ibis/backends/__init__.py", line 862, in reconnec
t
self.do_connect(*self._con_args, **self._con_kwargs)
File "/root/.venv/visidata/lib/python3.9/site-packages/ibis/backends/mysql/__init__.py", line 158, in do
_connect
con = pymysql.connect(
File "/root/.venv/visidata/lib/python3.9/site-packages/pymysql/connections.py", line 361, in __init__
self.connect()
File "/root/.venv/visidata/lib/python3.9/site-packages/pymysql/connections.py", line 716, in connect
raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connecti
on refused)")

Steps to reproduce

Any MySQL/MariaDB database should do.

Configuration

• Does this issue reproduce without any plugins or configuration (using the -N CLI flag)?

  Yes, same error when invoking vdsql -N mysql://…

• Does this issue reproduce with either the latest release, or with the develop branch

  Did not try this.

Additional context

• What platform and version are you using (Linux, MacOS, Windows)?

  ❯ cat /etc/os-release | grep PRETTY_NAME
  PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
  
  ❯ vdsql --version
  saul.pw/VisiData v3.0.2
  
  ❯ pip list
  Package            Version
  ------------------ ---------
  atpublic           4.1.0
  bidict             0.23.1
  greenlet           3.0.3
  ibis-framework     9.0.0
  ibis-substrait     3.2.0
  importlib-metadata 6.6.0
  markdown-it-py     3.0.0
  mdurl              0.1.2
  mysqlclient        2.2.4
  numpy              1.26.4
  packaging          24.0
  pandas             1.5.3
  parsy              2.1
  pip                24.0
  pkg_resources      0.0.0
  protobuf           5.26.1
  pyarrow            16.1.0
  pyarrow-hotfix     0.6
  Pygments           2.18.0
  PyMySQL            1.1.1
  python-dateutil    2.8.2
  pytz               2024.1
  PyYAML             6.0.1
  regex              2024.5.15
  rich               13.7.1
  setuptools         70.0.0
  six                1.16.0
  SQLAlchemy         2.0.30
  sqlglot            23.12.2
  sqlparse           0.5.0
  substrait          0.18.0
  toolz              0.12.1
  typing_extensions  4.11.0
  vdsql              0.3.dev0
  visidata           3.0.2
  wheel              0.43.0
  zipp               3.15.0
  
  ❯ pip install 'ibis-framework[mysql]'
  Requirement already satisfied: ibis-framework[mysql] in ./lib/python3.9/site-packages (9.0.0)
  …

• Which version of Python?

  ❯ python3 --version
  Python 3.9.2

• Which terminal are you using (for display and input issues)?

  Hyper 3.4.1 (stable) on macOS 10.15.7

[p3k]

Just gave the (stable?) version 0.2 of vdsql a spin, with even less success:

❯ vdsql mysql://root:@localhost/mysql
Traceback (most recent call last):
  File "/root/.venv/visidata/bin/vdsql", line 5, in <module>
    from vdsql.__main__ import main
  File "/root/.venv/visidata/lib/python3.9/site-packages/vdsql/__init__.py", line 2, in <module>
    from ._ibis import *
  File "/root/.venv/visidata/lib/python3.9/site-packages/vdsql/_ibis.py", line 8, in <module>
    from visidata.pyobj import ExpandedColumn
ImportError: cannot import name 'ExpandedColumn' from 'visidata.pyobj' (/root/.venv/visidata/lib/python3.9/site-packages/visidata/pyobj.py)

[saulpw]

Thanks for the report, @p3k. Ibis development happens at a very fast pace (there have been 6 major releases in the <2 years since vdsql was first implemented), and so things change faster than we can adapt to them. For example, sqlalchemy is no longer required by ibis, and we've removed the dependency from vdsql, we just haven't released a new version yet.

Note: I can see the stacktrace but I do not know how to copy it without the surrounding TUI getting in the way 🤷 (How can I hide the status box‽)

You can see the stacktrace with Ctrl+E, and either save it from there with Ctrl+S, or copy it to your system clipboard with gsgzY if you have that set up. We're aware of the status box being in the way; b should toggle it on/off, but it's often more frustrating than that. It's top of my list for a 3.1 release.

The other issues you mentioned could use a little attention too. Thanks again and sorry for the frustration.

[p3k]

Hi @saulpw – thanks for your swift reply!

I hope I did/do not sound too frustrated, that was not my intention. I am always delighted when I invoke vd, how well it works and how easy I can work even with huge data. It is a great piece of software! 🤩

Sorry to hear the Ibis integration is so stressful – I do not wish to add to the pressure. Just was curious to try out vdsql (and version 3 of visidata)…

True, the status box can be a bit irritating – although I generally like its idea, and the quirky quotes (I feel quirky sometimes).

Maybe immediately advertise the ctrl+b key binding as toggle for the box on first display? Also, I was looking for something with “status” in the view menu – would not have thought of “sidebar”!

Thanks for the help to save the stack trace – I updated the issue description with the complete output. (Unfortunately, I cannot use a clipboard tool because visidata and the database are running on a remote machine… oh, and what is gsgzY? A search for it did not yield any meaningful results 🤷 )

[saulpw]

Thanks, @p3k, I appreciate the kind words!

Maybe immediately advertise the ctrl+b key binding as toggle for the box on first display?

There is a note at the bottom of the sidebar saying exactly that. It's not there for the status messages because those can't be disabled--but if you invoke any command, even just a move/click, it will dismiss the status sidebar and bring up the sheet-specific sidebar.

oh, and what is gsgzY?

• gs to select all rows
• gzY to copy values in the current column for selected rows into the system clipboard

[p3k]

Ah, gsgzy is a keyboard combo! 💡 My mind went into a very different direction 😅

Re the note at the bottom of the status box: I now saw it ☺️

[mvtango]

Same here:

Traceback (most recent call last):
  File "/home/martin/.local/pipx/venvs/visidata/bin/vdsql", line 5, in <module>
    from vdsql.__main__ import main
  File "/home/martin/.local/pipx/venvs/visidata/lib/python3.10/site-packages/vdsql/__init__.py", line 2, in <module>
    from ._ibis import *
  File "/home/martin/.local/pipx/venvs/visidata/lib/python3.10/site-packages/vdsql/_ibis.py", line 8, in <module>
    from visidata.pyobj import ExpandedColumn
ImportError: cannot import name 'ExpandedColumn' from 'visidata.pyobj' (/home/martin/.local/pipx/venvs/visidata/lib/python3.10/site-packages/visidata/pyobj.py)

Visidata installed from github (3.1dev commit 8f823f5), vdsql installed from github as well (0.3.dev0 commit 21b43513700e305f4ee92bcfba88cf94d4a35243).

And thanks for visidata, a great tool of daily use.

[frankier]

This works for me:

uv tool install 'git+https://github.com/saulpw/visidata.git@develop#egg=vdsql&subdirectory=visidata/apps/vdsql' --with 'git+https://github.com/saulpw/visidata.git' --with 'ibis-framework[duckdb]'

Looks like the 0.2 release should have bounded its visidata dependency. Perhaps another release of vdsql to pypi, with pinned visidata could help?

[mvtango]

@frankier Thanks for this input, I tried to adapt it to my setup, it worked ☀️

My slightly different route to success goes like this:

pipx install  'git+https://github.com/saulpw/visidata.git@develop#egg=vdsql&subdirectory=visidata/apps/vdsql'
pipx inject vdsql visidata
pipx inject vdsql 'ibis-framework[duckdb]'
pipx inject vdsql 'sqlalchemy'

... giving me vdsql with Visidata 3.0.2 on Python 3.11.6, able to work with duckdb files