%sqlcmd columns fails if table identifier contains multiple dots

[adri0]

What happens?

Running %sqlcmd columns --table TABLE fails when TABLE identifier contains more than one dot, raising a ValueError: too many values to unpack (expected 2). In my setup I need to identify BigQuery tables by their full name, that includes the project ID, dataset and table name separated by dots (e.g. project_id.dataset.table_name).

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[19], line 1
----> 1 get_ipython().run_line_magic('sqlcmd', 'columns --table project-test-1011.test_dataset.test_table')

File /opt/envs/env/lib/python3.11/site-packages/IPython/core/interactiveshell.py#line=2455, in InteractiveShell.run_line_magic(self, magic_name, line, _stack_depth)
   2454     kwargs['local_ns'] = self.get_local_scope(stack_depth)
   2455 with self.builtin_trap:
-> 2456     result = fn(*args, **kwargs)
   2458 # The code below prevents the output from being displayed
   2459 # when using magics with decorator @output_can_be_silenced
   2460 # when the last Python token in the expression is a ';'.
   2461 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File /opt/envs/env/lib/python3.11/site-packages/sql/magic_cmd.py#line=104, in SqlCmdMagic._validate_execute_inputs(self, line)
    102     if command in COMMANDS_SQLALCHEMY_ONLY:
    103         support_only_sql_alchemy_connection(f"%sqlcmd {command}")
--> 105     return self.execute(command, others)
    106 else:
    107     raise exceptions.UsageError(
    108         f"%sqlcmd has no command: {command!r}. "
    109         "Valid commands are: {}".format(
    110             ", ".join(AVAILABLE_SQLCMD_COMMANDS)
    111         )
    112     )

File /opt/envs/env/lib/python3.11/site-packages/sql/magic_cmd.py#line=134, in SqlCmdMagic.execute(self, cmd_name, others, cell, local_ns)
    133     return cmd(others)
    134 else:
--> 135     return cmd(others, self.shell.user_ns.copy())

File /opt/envs/env/lib/python3.11/site-packages/sql/cmd/columns.py#line=37, in columns(others, user_ns)
     35 if is_rendering_required(" ".join(others)):
     36     expand_args(args, user_ns)
---> 38 return inspect.get_columns(name=sanitize_identifier(args.table), schema=args.schema)

File /opt/envs/env/lib/python3.11/site-packages/sql/inspect.py#line=482, in get_columns(name, schema)
    481 def get_columns(name, schema=None):
    482     """Get column names for a given connection"""
--> 483     return Columns(name, schema)

File /opt/envs/env/lib/python3.11/site-packages/sql/inspect.py#line=180, in Columns.__init__(self, name, schema, conn)
    177 # this returns a list of dictionaries. e.g.,
    178 # [{"name": "column_a", "type": "INT"}
    179 #  {"name": "column_b", "type": "FLOAT"}]
    180 if not schema and "." in name:
--> 181     schema, name = name.split(".")
    182 columns = inspector.get_columns(name, schema) or []
    184 self._table = PrettyTable()

ValueError: too many values to unpack (expected 2)

To Reproduce

Connect to a BigQuery project (myproject) containing at least 1 dataset (mydataset) containing at least 1 table (mytable). And run the columns sql command for that table:

%sqlcmd columns --table myproject.mydataset.mytable

OS:

Linux

JupySQL Version:

0.11.1

Full Name:

Adriano Oliveira

Affiliation:

ING