LIMITED TIME OFFER: 50% OFF OF PREMIUM WITH OUR ANNUAL PLAN (THAT'S $294 IN SAVINGS).
GET OFFER

Loading SQL database into Jupyter

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

My Code:

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

What actually happened:

No output at all.

I have installed the following successfully
!conda install -yc conda-forge ipython-sql

Please can someone tell me why this isn’t working?
The database is saved in exactly the same folder as the jupyter notebook that I’m trying to load.

Hello, though no output is generated try running the SQL code to see if the database is working

as you can see mine didn’t show the output: 'Connected: None@factbook.db' but the database is working correctly as expected.

3 Likes

Thanks for your reply.

I tried the code that you suggested and got the following:

 * sqlite:///factbook.db
(sqlite3.OperationalError) no such table: facts
[SQL: SELECT *
FROM facts
LIMIT 5;]
(Background on this error at: http://sqlalche.me/e/e3q8)

That’s challenging, in your terminal, in your project directory:

  1. check if you have ipython-sql
conda list | grep ipython-sql
  1. check if both notebook are in the same directory
ls

Thanks for your reply.

The response to this is as follows:
conda list | grep ipython-sql

Response:
Note: you may need to restart the kernel to use updated packages.

‘grep’ is not recognized as an internal or external command, operable program or batch file.

Both the jupyter notebook and database are saved in the same folder if that’s what you mean by check the directory?

1 Like

Hi! I have got the same problem as yours! How did you solve the problem?

1 Like

Hi @gnev80s, @7933509,

If you are using Windows, then grep command is not available. Please open Anaconda Navigator → Environments → Select Installed from the dropdown menu → Search for ipython-sql like this

If ipython-sql is not found, you have to install ipython-sql by running:
conda install -c conda-forge ipython-sql

Best,
Sahil

2 Likes

Hello, I have the same issue, ipython-sql is correctly installed on my machine, but still no output. I’ve tried running the test code, but it’s giving me the same error.

* sqlite:///factbook.db (sqlite3.OperationalError) no such table: facts [SQL: SELECT * FROM facts LIMIT 5;] (Background on this error at: http://sqlalche.me/e/e3q8)

Thank you for your help :slight_smile:

1 Like

Hello everyone,

I use a Mac computer and was having the exact same problem being described in this thread. After doing some research about this problem, I found this video, which solved the problem on my side.
My steps where as follows: I changed the type of shell (as described in the video), I opened a new notebook, entered the ipython-sql install command (provided in the DQ mission) and restarted the kernel. After doing this, my SQL commands are working just fine :slight_smile:

I don’t know how this would work on a windows computer, but I can imagine that there are other videos available that explain the same procedure for that use case.

Hope this is helpful!

2 Likes

Hey everyone,

I was struggling with this too, but I managed to figure it out! Here’s all the info you’ll need:

  1. Make sure you’ve installed ipython-sql. This only needs to be done once like others have mentioned above. Enter this into jupyter: !conda install -yc conda-forge ipython-sql
  2. Make sure your jupyter notebook and factbook.db are in the same location. If you downloaded from the mission they should be in a folder together, so just leave them there.
  3. Here is the code you will need: import sqlalchemy

sqlalchemy.create_engine(‘sqlite:///factbook.db’)
%load_ext sql
%sql sqlite:///factbook.db

and the TowardsDataScience article I found to figure this out: https://towardsdatascience.com/heres-how-to-run-sql-in-jupyter-notebooks-f26eb90f3259

Good luck!

6 Likes

Thanks, Ryan! This worked for me.

One question: once I close the notebook and come back to work on it, I will only have to re-import sqlalchemy right? ipython-sql should be there already and I don’t have to execute the !pip install ipython-sql code block again?

Hi @atikah.mohamad, yes, that is correct! You only need to run !pip install once and then just import the library whenever you want to use it again…no need to install it again.

1 Like

Thanks Victor! Helped me out.

Thanks that worked!
I had to do a minor change though: change ‘sqlite:///factbook.db’ to double quotas “sqlite:///factbook.db”

Wow to install and run SQL in jupyter note book

→ Run this below command to install sql:

!conda install -yc conda-forge ipython-sql

→ Run command below to connect your DB to jupyter NB(It may of may not provide any output for confirmation):

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

→ Now to run SQL queries follow this syntax:

%%sql
your_sql_queries

What if? None of the above worked?

Connected to factbook but all I get is a few empty rows…

I’m stuck at the same place. Did you figure it out?

don’t think so, ended up doing it on DQ but it’s all pretty basic anyway. don’t think it’s worth the Jupyter hassle, ended up using docker & SQLPad (different course on a different platform)