run.py logic with comments and/or zero or several statements is flawed #1066
Description
What happens?
Sometimes I leave to my students "empty" %%sql cells so that they can complete, something like:
%%sql
-- DELETE FROM XX WHERE ...
This cell fails under jupysql, because first it removes the comments, and then tries to execute an empty cell. The logic in run.py is flawed at least for 0 statements (variables result and statement not bounded if no statements). I will provide a patch so that jupysql does not fails, but the logic must be changed, because now only the last result and statement are available as result (for instance as a dataframe, I don't recall if this is specified in the documentation). The error:
---------------------------------------------------------------------------
UnboundLocalError Traceback (most recent call last)
Cell In[53], line 1
----> 1 get_ipython().run_cell_magic('sql', '', '-- DELETE FROM Votes WHERE ...\n')
File /opt/conda/lib/python3.12/site-packages/IPython/core/interactiveshell.py:2565, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
2563 with self.builtin_trap:
2564 args = (magic_arg_s, cell)
-> 2565 result = fn(*args, **kwargs)
2567 # The code below prevents the output from being displayed
2568 # when using magics with decorator @output_can_be_silenced
2569 # when the last Python token in the expression is a ';'.
2570 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):
File /opt/conda/lib/python3.12/site-packages/sql/magic.py:365, in SqlMagic.execute(self, line, cell, local_ns)
257 @no_var_expand
258 @needs_local_scope
259 @line_magic("sql")
(...) 337 )
338 def execute(self, line="", cell="", local_ns=None):
339 """
340 Runs SQL statement against a database, specified by
341 SQLAlchemy connect string.
(...) 363
364 """
--> 365 return self._execute(
366 line=line, cell=cell, local_ns=local_ns, is_interactive_mode=False
367 )
File /opt/conda/lib/python3.12/site-packages/sql/magic.py:623, in SqlMagic._execute(self, line, cell, local_ns, is_interactive_mode)
620 handle_exception(e, command.sql, self.short_errors)
621 except Exception as e:
622 # Handle non SQLAlchemy errors
--> 623 handle_exception(e, command.sql, self.short_errors)
File /opt/conda/lib/python3.12/site-packages/sql/error_handler.py:115, in handle_exception(error, query, short_error)
113 _display_error_msg_with_trace(error, detailed_message)
114 else:
--> 115 raise error
File /opt/conda/lib/python3.12/site-packages/sql/magic.py:577, in SqlMagic._execute(self, line, cell, local_ns, is_interactive_mode)
574 parameters = user_ns
576 try:
--> 577 result = run_statements(conn, command.sql, self, parameters=parameters)
579 if (
580 result is not None
581 and not isinstance(result, str)
(...) 584 # Instead of returning values, set variables directly in the
585 # users namespace. Variable names given by column names
587 if self.autopandas or self.autopolars:
File /opt/conda/lib/python3.12/site-packages/sql/run/run.py:65, in run_statements(conn, sql, config, parameters)
58 if (
59 config.feedback >= 1
60 and hasattr(result, "rowcount")
61 and result.rowcount > 0
62 ):
63 display.message_success(f"{result.rowcount} rows affected.")
---> 65 result_set = ResultSet(result, config, statement, conn)
66 return select_df_type(result_set, config)
UnboundLocalError: cannot access local variable 'result' where it is not associated with a value
To Reproduce
In a cell, just put a comment. It will lead to an empty cell, thus raising the error:
%%sql
-- Comment
OS:
Linux, macOS
JupySQL Version:
0.11.1
Full Name:
Diego Sevilla Ruiz
Affiliation:
University of Murcia