Error When Creating SQL View in Jupyter - Guided Project: Answering Business Questions Using SQL

Hello DQ community!

I’m working my way through the SQL intermediate guided project in my own Jupyter notebook and I’m getting an unexpected error when creating a view.

My Code:

%%sql
DROP VIEW IF EXISTS customer_spend;

CREATE VIEW customer_spend AS 
SELECT c.*,
       SUM(i.total) AS total
  FROM customer AS c
 INNER JOIN invoice AS i ON i.customer_id = c.customer_id
 GROUP BY c.customer_id;

What I expected to happen:
I expect no output from this code, but for there to be a new VIEW created in the background that I can call as a new SQL table.

What actually happened:
The code does execute and I DO get a new view that I can call in other queries, however I get this error code from Jupyter and my notebooks stops running.

 * sqlite:///chinook.db
Done.
Done.

---------------------------------------------------------------------------
ResourceClosedError                       Traceback (most recent call last)
<ipython-input-15-be4304be6616> in <module>
----> 1 get_ipython().run_cell_magic('sql', '', 'DROP VIEW IF EXISTS customer_spend;\n\nCREATE VIEW customer_spend AS \nSELECT c.*,\n       SUM(i.total) AS total\n  FROM customer AS c\n INNER JOIN invoice AS i ON i.customer_id = c.customer_id\n GROUP BY c.customer_id;\n')

~\anaconda3\lib\site-packages\IPython\core\interactiveshell.py in run_cell_magic(self, magic_name, line, cell)
   2397             with self.builtin_trap:
   2398                 args = (magic_arg_s, cell)
-> 2399                 result = fn(*args, **kwargs)
   2400             return result
   2401 

~\anaconda3\lib\site-packages\decorator.py in fun(*args, **kw)
    229             if not kwsyntax:
    230                 args, kw = fix(args, kw, sig)
--> 231             return caller(func, *(extras + args), **kw)
    232     fun.__name__ = func.__name__
    233     fun.__doc__ = func.__doc__

~\anaconda3\lib\site-packages\IPython\core\magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

~\anaconda3\lib\site-packages\decorator.py in fun(*args, **kw)
    229             if not kwsyntax:
    230                 args, kw = fix(args, kw, sig)
--> 231             return caller(func, *(extras + args), **kw)
    232     fun.__name__ = func.__name__
    233     fun.__doc__ = func.__doc__

~\anaconda3\lib\site-packages\IPython\core\magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

~\anaconda3\lib\site-packages\sql\magic.py in execute(self, line, cell, local_ns)
     93 
     94         try:
---> 95             result = sql.run.run(conn, parsed['sql'], self, user_ns)
     96 
     97             if result is not None and not isinstance(result, str) and self.column_local_vars:

~\anaconda3\lib\site-packages\sql\run.py in run(conn, sql, config, user_namespace)
    342             if result and config.feedback:
    343                 print(interpret_rowcount(result.rowcount))
--> 344         resultset = ResultSet(result, statement, config)
    345         if config.autopandas:
    346             return resultset.DataFrame()

~\anaconda3\lib\site-packages\sql\run.py in __init__(self, sqlaproxy, sql, config)
    105 
    106     def __init__(self, sqlaproxy, sql, config):
--> 107         self.keys = sqlaproxy.keys()
    108         self.sql = sql
    109         self.config = config

~\anaconda3\lib\site-packages\sqlalchemy\engine\result.py in keys(self)
    705 
    706         """
--> 707         return self._metadata.keys
    708 
    709 

~\anaconda3\lib\site-packages\sqlalchemy\engine\cursor.py in keys(self)
   1199     @property
   1200     def keys(self):
-> 1201         self._we_dont_return_rows()
   1202 
   1203 

~\anaconda3\lib\site-packages\sqlalchemy\engine\cursor.py in _we_dont_return_rows(self, err)
   1176 
   1177     def _we_dont_return_rows(self, err=None):
-> 1178         util.raise_(
   1179             exc.ResourceClosedError(
   1180                 "This result object does not return rows. "

~\anaconda3\lib\site-packages\sqlalchemy\util\compat.py in raise_(***failed resolving arguments***)
    209 
    210         try:
--> 211             raise exception
    212         finally:
    213             # credit to

ResourceClosedError: This result object does not return rows. It has been closed automatically.


I’m unsure what to do here. I’d like to just ignore this output, but since the error looks undesirable and it stops the notebook from fully running each cell, I’d like to know what’s going on.

Let me know if I need to provide the full notebook and database.

Thanks for the help!

From what I was able to glean from some google searching, this is an old bug that never was resolved. SQLMagic expects an output whenever there is a SELECT statement, even when like in mine where I’m creating a VIEW with no output. I was able to get around this by adding a brief arbitrary SELECT statement to the bottom of the cell.

Not ideal, but it let’s me move forward. If anyone knows a better way to do this or has more insight, I’d like to know!

Code that works below.

%%sql
DROP VIEW IF EXISTS customer_spend;

CREATE VIEW customer_spend AS 
SELECT c.*,
       SUM(i.total) AS total
  FROM customer AS c
 INNER JOIN invoice AS i ON i.customer_id = c.customer_id
 GROUP BY c.customer_id;

SELECT *
  FROM customer_spend
 LIMIT 1

Thanks,
Kevin

I recommend DB Browser for running your queries and exploring the data.

1 Like