I am attempting to create a new database and create a table using magic line %sql in Jupyter Notebook but I've been getting a KeyError and I'm struggling to work out why, my code is as follows.
import sqlite3 as sql
import pandas as pd
%load_ext sql
%sql sqlite:///dataProgramming.db
%%sql sqlite://
CREATE TABLE
users (
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
USERID INT NOT NULL UNIQUE,
PRIMARY KEY (USERID)
);
When I run this I get the following traceback.
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
Cell In[3], line 1
----> 1 get_ipython().run_cell_magic('sql', 'sqlite://', 'CREATE TABLE\n users (\n FirstName VARCHAR(30) NOT NULL,\n LastName VARCHAR(30) NOT NULL,\n USERID INT NOT NULL UNIQUE,\n PRIMARY KEY (USERID)\n );\n')
File ~\anaconda3\Lib\site-packages\IPython\core\interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
2539 with self.builtin_trap:
2540 args = (magic_arg_s, cell)
-> 2541 result = fn(*args, **kwargs)
2543 # The code below prevents the output from being displayed
2544 # when using magics with decorator @output_can_be_silenced
2545 # when the last Python token in the expression is a ';'.
2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):
File ~\anaconda3\Lib\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 ~\anaconda3\Lib\site-packages\ploomber_core\exceptions.py:128, in modify_exceptions.<locals>.wrapper(*args, **kwargs)
125 @wraps(fn)
126 def wrapper(*args, **kwargs):
127 try:
--> 128 return fn(*args, **kwargs)
129 except (ValueError, TypeError) as e:
130 _add_community_link(e)
File ~\anaconda3\Lib\site-packages\sql\magic.py:624, in SqlMagic._execute(self, line, cell, local_ns, is_interactive_mode)
621 handle_exception(e, command.sql, self.short_errors)
622 except Exception as e:
623 # Handle non SQLAlchemy errors
--> 624 handle_exception(e, command.sql, self.short_errors)
File ~\anaconda3\Lib\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 ~\anaconda3\Lib\site-packages\sql\magic.py:578, in SqlMagic._execute(self, line, cell, local_ns, is_interactive_mode)
575 parameters = user_ns
577 try:
--> 578 result = run_statements(conn, command.sql, self, parameters=parameters)
580 if (
581 result is not None
582 and not isinstance(result, str)
(...)
585 # Instead of returning values, set variables directly in the
586 # users namespace. Variable names given by column names
588 if self.autopandas or self.autopolars:
File ~\anaconda3\Lib\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)
File ~\anaconda3\Lib\site-packages\sql\run\resultset.py:39, in ResultSet.__init__(self, sqlaproxy, config, statement, conn)
36 self._is_dbapi_results = hasattr(sqlaproxy, "description")
38 # note that calling this will fetch the keys
---> 39 self._pretty_table = self._init_table()
41 self._mark_fetching_as_done = False
43 if self._config.autolimit == 1:
44 # if autolimit is 1, we only want to fetch one row
File ~\anaconda3\Lib\site-packages\sql\run\resultset.py:466, in ResultSet._init_table(self)
463 pretty = CustomPrettyTable(self.field_names)
465 if isinstance(self._config.style, str):
--> 466 _style = prettytable.__dict__[self._config.style.upper()]
467 pretty.set_style(_style)
469 return pretty
KeyError: 'DEFAULT'
Any guidance would be greatly appreciated, thank you
I've attempted following the traceback through the .py files in the dependencies but I can't work out the issues
This issue reported here looks to be the same with very related pointers to similar code in the last parts and the same error.
Following the advice given there:
Try running a cell like %config SqlMagic.style = '_DEPRECATED_DEFAULT' before running the cell magic one in your post.