Cant use sql in the guided project

I followed the instruction to select data from the database. however, I can’t use SQL in Jupyter, even though i do %%sqlat the beginning.

Screen Link: https://app.dataquest.io/m/469/guided-project%3A-popular-data-science-questions/4/getting-the-data

@candiceliu93

Put the db file you want to query in the same folder as your Jupyter Notebook .ipynb file.

Run this code:

dbname is the name of the .db file.

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

Run this to see the tables in your db file

%%sql
SELECT *
    FROM sqlite_master
    WHERE type='table';

However, I think you write your query here for this exercise.

Thank you!!
Do I need to do it every time when I use SQL in jupyter? If I use my own jupyter, I dont have a database, just the file saved in the drive. what should I do?

These steps are used when you are using Jupyter on your own machine.

You can download the the chinook.db file from here:
image

Click on the cloud symbol with the arrow pointing downwards.

For a guided project:
image

Click on download, you will get a .tar file, that you can unzip with winrar etc. The datafile is inside.

I prefer working on my machine.

I mean if i want to use SQL in juypter on my own machine on certain excel file, not the chinook.db that dq is using. how to make it happen?

You may need to create a database and post the csv/excel file into it.

where can I create a database and how to post the csv and excel file into so that I can use SQL on juypter? I appreciate your answers so much!

Thank you!!

I created a new database and a table with sqlite3 using command line.

Working with sqlite in shell one of the missions in the Data Analyst in Python path.

image

For creating tables in a database using csv files, you may have to take the Data Engineer path. This guided project (Building a Database for Crime Reports) explains how it is done.

1 Like

Thank you for showing it to me. I think I might need to spend some time doing some research on it.

2 Likes