Jupyter Notebook %sql magic throws KeyError: 'DEFAULT' when creating a SQLite table

Data and Databases
1

I’m having trouble with the %sql magic command in Jupyter Notebook. I’m trying to make a new SQLite database and add a table, but I keep getting a KeyError. Here’s what I’ve done:

import sqlite3 as sq
import pandas as pd

%load_ext sql

%sql sqlite:///my_database.db

%%sql sqlite://
CREATE TABLE
  people (
    Name TEXT NOT NULL,
    Surname TEXT NOT NULL,
    PersonID INTEGER NOT NULL UNIQUE,
    PRIMARY KEY (PersonID)
  );

When I run this, I get a long error message ending with:

KeyError: 'DEFAULT'

I’ve looked through the error traceback and the related Python files, but I can’t figure out what’s causing this. Does anyone know what might be going wrong or how to fix it? I’d really appreciate any help or suggestions. Thanks!

2

hey SpinningGalaxy, i’ve run into similar issues before. try separating ur database connection and table creation steps. first use %sql sqlite:///my_database.db to connect, then run ur CREATE TABLE query in a separate cell. that usually fixes weird errors like this for me. lmk if it works!

3

hmm, interesting problem! have u tried using a different SQLite connector? sometimes the built-in sqlite3 can be finicky. maybe give sqlalchemy a shot? it’s more robust and might resolve ur issue. plus, it plays nice with pandas if ur planning on using that later. what do u think?

4

I encountered a similar issue and found that the problem often lies with the SQL magic extension itself. Try updating your SQL magic extension to the latest version using pip: ‘pip install --upgrade ipython-sql’. If that doesn’t resolve the issue, consider using a direct SQLite connection instead of the magic commands. You can create the connection and execute SQL queries using standard Python code, which often bypasses these extension-related errors. This approach gives you more control and can be more reliable in certain environments.