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