Cannot connect with DB from Jupyter Notebook

Screen Link: https://app.dataquest.io/m/257/guided-project%3A-analyzing-cia-factbook-data-using-sql/1/introduction

Your Code:

import sqlite3
import pandas as pd
%%capture
%load_ext sql
%sql sqlite:///factbook.db
factbook = pd.read_csv("factbook.db")

What I expected to happen: Able to connect to DB from my computer locally using Jupyter

What actually happened: Hit the following error message UsageError: Line magic function %%capture not found.

1 Like

Hey, Fungsoon.

If you have that block of code in a cell, then the problem is that you’re using a cell magic in the middle of a cell.

Running %magic displays, among other things, the following (emphasis is mine):

IPython’s ‘magic’ functions
===========================

The magic function system provides a series of functions which allow you to
control the behavior of IPython itself, plus a lot of system-type
features. There are two kinds of magics, line-oriented and cell-oriented.

Line magics are prefixed with the % character and work much like OS
command-line calls: they get as an argument the rest of the line, where
arguments are passed without parentheses or quotes. For example, this will
time the given statement::

   %timeit range(1000)

Cell magics are prefixed with a double %%, and they are functions that get as
an argument not only the rest of the line, but also the lines below it in a
separate argument. These magics are called with two arguments: the rest of the
call line and the body of the cell, consisting of the lines below the first.
For example::

   %%timeit x = numpy.random.randn((100, 100))
   numpy.linalg.svd(x)

will time the execution of the numpy svd routine, running the assignment of x
as part of the setup phase, which is not timed.

In a line-oriented client (the terminal or Qt console IPython), starting a new
input with %% will automatically enter cell mode, and IPython will continue
reading input until a blank line is given. In the notebook, simply type the
whole cell as one entity, but keep in mind that the %% escape can only be at
the very start of the cell.

If you’re wondering what is this IPython thing, it’s basically what makes Jupyter Notebook work with Python with all the functionality that we’re used to. IPython is basically the backend, and Jupyter the frontend.

1 Like

Hi Bruno,
thank you for the lengthy explanation. Sorry as I am normally able to look at it during weekends. I have tried it like the below

import sqlite3 as sql
import pandas as pd
%%capture
%load_ext sql
%sql sqlite:///factbook.db
factbook = pd.read_csv("factbook.db")

I get the following error message

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
<ipython-input-4-f5c32c51dc3c> in <module>
----> 1 get_ipython().run_line_magic('load_ext', 'sql')
      2 get_ipython().run_line_magic('sql', 'sqlite:///factbook.db')
      3 factbook = pd.read_csv("factbook.db")

~/anaconda/lib/python3.6/site-packages/IPython/core/interactiveshell.py in run_line_magic(self, magic_name, line, _stack_depth)
   2283                 kwargs['local_ns'] = sys._getframe(stack_depth).f_locals
   2284             with self.builtin_trap:
-> 2285                 result = fn(*args,**kwargs)
   2286             return result
   2287 

<decorator-gen-65> in load_ext(self, module_str)

~/anaconda/lib/python3.6/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):

~/anaconda/lib/python3.6/site-packages/IPython/core/magics/extension.py in load_ext(self, module_str)
     31         if not module_str:
     32             raise UsageError('Missing module name.')
---> 33         res = self.shell.extension_manager.load_extension(module_str)
     34 
     35         if res == 'already loaded':

~/anaconda/lib/python3.6/site-packages/IPython/core/extensions.py in load_extension(self, module_str)
     78             if module_str not in sys.modules:
     79                 with prepended_to_syspath(self.ipython_extension_dir):
---> 80                     mod = import_module(module_str)
     81                     if mod.__file__.startswith(self.ipython_extension_dir):
     82                         print(("Loading extensions from {dir} is deprecated. "

~/anaconda/lib/python3.6/importlib/__init__.py in import_module(name, package)
    124                 break
    125             level += 1
--> 126     return _bootstrap._gcd_import(name[level:], package, level)
    127 
    128 

~/anaconda/lib/python3.6/importlib/_bootstrap.py in _gcd_import(name, package, level)

~/anaconda/lib/python3.6/importlib/_bootstrap.py in _find_and_load(name, import_)

~/anaconda/lib/python3.6/importlib/_bootstrap.py in _find_and_load_unlocked(name, import_)

ModuleNotFoundError: No module named 'sql'

I checked the list of libraries using the command conda list (i haven’t taken the Command Line course yet), but i do have the following libraries installed

sqlalchemy                1.2.12           py36h1de35cc_0  
sqlite                    3.25.2               ha441bb4_0  

i wonder is it because i have screwed up my environment in my machine that’s causing the problem. but i do see sql in in the libraries there

You’re missing ipython-sql. You can install it with the following command:

conda install -c conda-forge ipython-sql
5 Likes

Great thank you so much Bruno. It works now :grinning:

1 Like

Hi Bruno,

I tried the command you provided in Anaconda prompt to install the sql module. I got a message that there is something that need to be updated [y/n]? I entered “y” and I got a message that current user cannot access to this! Could you please help me with this?

Seems like an issue with permissions. Try running Anaconda Prompt as an administrator and repeat.

1 Like

Thank you so much.
Just one more question. Should I first put the ‘factbook.db’ file in the same directory that I open my jupyter notebook on it?

I did this and I run the cell of below code. It worked but I didn’t get the expected message of “‘Connected: [email protected]’”. Could you please what is happening here? I think in the guided project, first part, it has not been clearly explained what is happening here, and how we try to connect our jupyter notebook to the database. Thanks again for your time. I always enjoy the way you explain vague parts in the course.

%%capture
%load_ext sql
%sql sqlite:///factbook.db

That’s one way of doing it.

That message only appears in Jupyter Notebook, if I recall correctly. You’re probably using Jupyter Lab (which I think is vastly superior, so don’t mind that).

1 Like

Hey! I followed these steps exactly but when I run the code to connect with facebook.db I don’t get any output. I don’t understand it :confused:

Hello,

I tried to connect to sqlite database but I am also getting same error “UsageError: Line magic function %%capture not found.”

I have already installed ipython-sql package, and restarted the kernel multiple times.

Below is the screenshot from Jupyter.

Thanks in advance!

1 Like

Hi @itika.sharma10,

Please try moving import sqlite3 and import pandas as pd to the previous cell so that %%capture appears in the first line of the cell.

Best,
Sahil

Thank you, Sahil!

It worked after I moved imports in separate lines. :slightly_smiling_face:

1 Like

I’m having a similar problem on my local Jupyter Notebook, I dont’t get the message (‘Connected: [email protected]’) resulting after running the following block of code:

i already installed the packages, the following image is from the anaconda navigator.

It works fine in the dataquest notebook but not on my local pc. Is there something obvious i’m not seeing?!