search

iqmo-org / magic_duckdb

Jupyter Cell / Line Magics for DuckDB
https://pypi.org/project/magic-duckdb/
BSD 3-Clause "New" or "Revised" License
37 stars 1 forks source link

In %dql, double quote preceded by space not handled if inside brackets #24

Open Alex-Monahan opened 3 months ago

Alex-Monahan commented 3 months ago

Howdy! Thanks for the great library - I am a big fan and recommend it to folks!

I found an odd edge case while doing some odd SQL queries. If my SQL query that I am passing to %dql has a list of strings and the first string contains a double quote with a space immediately prior to it, then it errors out. %%dql works correctly, as do may other slight variations.

This is not a blocker for me at all, but I wanted to pass the bug along!

These fail:

%dql SELECT ['a "with a space before'] as my_column
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[121], [line 1](vscode-notebook-cell:?execution_count=121&line=1)
----> [1](vscode-notebook-cell:?execution_count=121&line=1) get_ipython().run_line_magic('dql', 'SELECT [\'a "with a space before\', \'a\'] as my_column')

File ~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/interactiveshell.py:2480, in InteractiveShell.run_line_magic(self, magic_name, line, _stack_depth)
   [2478](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/interactiveshell.py:2478)     kwargs['local_ns'] = self.get_local_scope(stack_depth)
   [2479](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/interactiveshell.py:2479) with self.builtin_trap:
-> [2480](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/interactiveshell.py:2480)     result = fn(*args, **kwargs)
   [2482](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/interactiveshell.py:2482) # The code below prevents the output from being displayed
   [2483](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/interactiveshell.py:2483) # when using magics with decorator @output_can_be_silenced
   [2484](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/interactiveshell.py:2484) # when the last Python token in the expression is a ';'.
   [2485](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/interactiveshell.py:2485) if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File ~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/magic_duckdb/magic.py:157, in DuckDbMagic.execute(self, line, cell, local_ns)
    [154](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/magic_duckdb/magic.py:154) line = "" if line is None else line
    [155](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/magic_duckdb/magic.py:155) user_ns: Dict[str, object] = self.shell.user_ns  # type: ignore
--> [157](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/magic_duckdb/magic.py:157) args = parse_argstring(self.execute, line)
    [159](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/magic_duckdb/magic.py:159) rest = " ".join(args.rest)
    [160](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/magic_duckdb/magic.py:160) query = f"{rest}\n{cell}".strip()

File ~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:196, in parse_argstring(magic_func, argstring)
    [193](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:193) def parse_argstring(magic_func, argstring):
    [194](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:194)     """ Parse the string of arguments for the given magic function.
    [195](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:195)     """
--> [196](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:196)     return magic_func.parser.parse_argstring(argstring)

File ~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:167, in MagicArgumentParser.parse_argstring(self, argstring)
    [164](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:164) def parse_argstring(self, argstring):
    [165](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:165)     """ Split a string into an argument list and parse that argument list.
    [166](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:166)     """
--> [167](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:167)     argv = arg_split(argstring)
    [168](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:168)     return self.parse_args(argv)

File ~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/utils/_process_common.py:200, in arg_split(s, posix, strict)
    [198](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/utils/_process_common.py:198) while True:
    [199](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/utils/_process_common.py:199)     try:
--> [200](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/utils/_process_common.py:200)         tokens.append(next(lex))
    [201](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/utils/_process_common.py:201)     except StopIteration:
    [202](https://file+.vscode-resource.vscode-cdn.net/Users/alex/Documents/DuckDB/labs/~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/utils/_process_common.py:202)         break

File /opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:300, in shlex.__next__(self)
    [299](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:299) def __next__(self):
--> [300](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:300)     token = self.get_token()
    [301](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:301)     if token == self.eof:
    [302](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:302)         raise StopIteration

File /opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:109, in shlex.get_token(self)
    [107](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:107)     return tok
    [108](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:108) # No pushback.  Get a token.
--> [109](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:109) raw = self.read_token()
    [110](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:110) # Handle inclusions
    [111](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:111) if self.source is not None:

File /opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:191, in shlex.read_token(self)
    [189](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:189)         print("shlex: I see EOF in quotes state")
    [190](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:190)     # XXX what error should be raised here?
--> [191](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:191)     raise ValueError("No closing quotation")
    [192](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:192) if nextchar == self.state:
    [193](https://file+.vscode-resource.vscode-cdn.net/opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:193)     if not self.posix:

ValueError: No closing quotation
%dql SELECT ['a "with a space before', 'a'] as my_column
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[122], line 1
----> 1 get_ipython().run_line_magic('dql', 'SELECT [\'a "with a space before\', \'a\'] as my_column')

File ~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/interactiveshell.py:2480, in InteractiveShell.run_line_magic(self, magic_name, line, _stack_depth)
   2478     kwargs['local_ns'] = self.get_local_scope(stack_depth)
   2479 with self.builtin_trap:
-> 2480     result = fn(*args, **kwargs)
   2482 # The code below prevents the output from being displayed
   2483 # when using magics with decorator @output_can_be_silenced
   2484 # when the last Python token in the expression is a ';'.
   2485 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File ~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/magic_duckdb/magic.py:157, in DuckDbMagic.execute(self, line, cell, local_ns)
    154 line = "" if line is None else line
    155 user_ns: Dict[str, object] = self.shell.user_ns  # type: ignore
--> 157 args = parse_argstring(self.execute, line)
    159 rest = " ".join(args.rest)
    160 query = f"{rest}\n{cell}".strip()

File ~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:196, in parse_argstring(magic_func, argstring)
    193 def parse_argstring(magic_func, argstring):
    194     """ Parse the string of arguments for the given magic function.
    195     """
--> 196     return magic_func.parser.parse_argstring(argstring)

File ~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/core/magic_arguments.py:167, in MagicArgumentParser.parse_argstring(self, argstring)
    164 def parse_argstring(self, argstring):
    165     """ Split a string into an argument list and parse that argument list.
    166     """
--> 167     argv = arg_split(argstring)
    168     return self.parse_args(argv)

File ~/Documents/DuckDB/labs/dynamic_sql_venv/lib/python3.12/site-packages/IPython/utils/_process_common.py:200, in arg_split(s, posix, strict)
    198 while True:
    199     try:
--> 200         tokens.append(next(lex))
    201     except StopIteration:
    202         break

File /opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:300, in shlex.__next__(self)
    299 def __next__(self):
--> 300     token = self.get_token()
    301     if token == self.eof:
    302         raise StopIteration

File /opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:109, in shlex.get_token(self)
    107     return tok
    108 # No pushback.  Get a token.
--> 109 raw = self.read_token()
    110 # Handle inclusions
    111 if self.source is not None:

File /opt/homebrew/Cellar/python@3.12/3.12.2_1/Frameworks/Python.framework/Versions/3.12/lib/python3.12/shlex.py:191, in shlex.read_token(self)
    189         print("shlex: I see EOF in quotes state")
    190     # XXX what error should be raised here?
--> 191     raise ValueError("No closing quotation")
    192 if nextchar == self.state:
    193     if not self.posix:

ValueError: No closing quotation

These succeed:

%dql SELECT ['a" with no space before'] as my_column
%dql SELECT 'a "with a space before not in a list' as my_column
%dql SELECT ['a', 'a "with a space before but the second item in the list'] as my_column
%%dql 
SELECT ['a "with a space before'] as my_column
paultiq commented 3 months ago

Odd, I can't trivially reproduce. I'll look a little more closely, but in VScode:

image

paultiq commented 3 months ago

Mind trying on a clean venv? And share the pip freeze?