Loading chinook database on Jupyter

[otavios.s]

Hello!

I just finished the guided project Answering Business Questions using SQL and now I’d like view this project on my Jupyter notebook. However, I do not know how to load the database. This is what I’m running:

My Code:

import sqlalchemy
engine = sqlalchemy.create_engine('sqlite:///chinook.db')-

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

This code does not return any errors and the data database seems to be loaded, but when I write any query it says the tables do not exist, for example:

 * sqlite:///chinook.db
(sqlite3.OperationalError) no such table: track

Also, when I run a query to see all the tables I get this:

* sqlite:///chinook.db 
 Done.

Out[30]:

name type

Like there’s no tables at all.

Can anyone help?
Thanks in advance.

[Bruno]

Eae? Beleza?

You seem to be mixing two different ways of handling SQLite databases. One is using the Python module sqlalchemy, the other is using Jupyter magics.

To use Jupyter magics, please mimic what is done here (with the necessary adjustments).

I am anticipating a certain issue, so let me ask you this in the mean time: What operating system are you on?

Let me know if you run into any new issues.

[otavios.s]

Fala Bruno, tudo bem? Que bom que tem mais falantes de português por aqui!

I tried to use both sqlalchemy and Jupyter magics because that’s how I got the World Factbook project to work. You can see it here. But now I tried to run it (the factbook project) with only the Jupyter magics and it also worked. There’s a long time that I finished that project so I do not remember what was was going on exactly

But for the Chinook project, I tried now only with the Jupyter magics and the same thing is happening. I do not understand why one project works an the other doesn’t.

I’m on Windows 10.

[Bruno]

@otavios.s

I tried to use both sqlalchemy and Jupyter magics because that’s how I got the World Factbook project to work. You can see it here.

The first code cell shouldn’t be necessary for it to work.

@otavios.s

I’m on Windows 10.

Please run the following lines of code in two seperate cells and share the output.

!dir
!where /R C:\ /T chinook.db 

[otavios.s]

This is what I got:

!dir

 O volume na unidade C ‚ OS
 O N£mero de S‚rie do Volume ‚ 8EA7-72B0

 Pasta de C:\Users\otavi\Documents\Data Science\DataQuest\12 - Intermediate SQL for Data Analysis

12/05/2020  17:23    <DIR>          .
12/05/2020  17:23    <DIR>          ..
11/05/2020  21:58    <DIR>          .ipynb_checkpoints
08/05/2020  17:07           704.282 1 - Joining-data-in-sql.pdf
09/05/2020  11:25           723.735 2 - Intermediate-joins-in-sql.pdf
11/05/2020  10:31           995.885 3 - Building-and-organizing-complex-queries.pdf
12/05/2020  15:10           394.267 3255539.jpg
12/05/2020  17:23            21.061 Basics.ipynb
11/05/2020  16:39            80.301 Capturar.PNG
11/05/2020  21:58                 0 chinook.db
               7 arquivo(s)      2.919.531 bytes
               3 pasta(s)   57.269.444.608 bytes dispon¡veis

!where /R C:\ /T chinook.db

0 11/05/2020 21:58:08 C:\Users\otavi\Documents\Data Science\DataQuest\12 - Intermediate SQL for Data Analysis\chinook.db

[Bruno]

It seems that chinook.db is empty. You probably never downloaded the file and the reason why the file even exists is because it is created when you try to connect to it.

Can you please attach your chinook.db just to confirm this?

Downloading the chinook.db file and placing it in that directory should fix your issues.

[otavios.s]

It worked!

I don’t know why but the database was actually empty!

Muito obrigado!

[Bruno]

@otavios.s

I don’t know why but the database was actually empty!

I tried to explain this here:

@Bruno

the reason why the file even exists is because it is created when you try to connect to it.

Your code belows creates that file:

import sqlalchemy
engine = sqlalchemy.create_engine('sqlite:///chinook.db')

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

[otavios.s]

Oh, ok! It makes total sense! Thanks a lot!

[drill_n_bass]

@Bruno

Your code below creates that file:

import sqlalchemy
engine = sqlalchemy.create_engine('sqlite:///chinook.db')

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

what if this code doesn’t work on Anacoda’s Jupyter? I have win 10. My output is:

When I tried:

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

There is no output.

My chinook.db has 0 kb.

I don’t know where to download it. I couldn’t find any info where and how to download it ( firstly thought that this code above - this from the first page of SQL mission “Answering Business Questions Using SQL” - will download it directly from web, but it seems that it does something else).

@Bruno

Downloading the chinook.db file and placing it in that directory should fix your issues.

So, where it is?

[the_doctor]

@drill_n_bass

I don’t know where to download it. I couldn’t find any info where and how to download it ( firstly thought that this code above - this from the first page of SQL mission “Answering Business Questions Using SQL” - will download it directly from web, but it seems that it does something else).

I am assuming by “SQL mission” you are referring to the Guided Project of that name.

There’s a Download button at the top of the Jupyter Notebook in the classroom. It downloads all the project-related files including your Notebook and the dataset as well. If you download and then unzip the file you will get the chinook.db file.

@drill_n_bass

what if this code doesn’t work on Anacoda’s Jupyter? I have win 10

%% (cell magic commands) should apparently be executed first (so before your import statement, or you can split the cells which you seem to have done already) - https://github.com/jupyter/notebook/issues/5269 That should work, I believe.

[drill_n_bass]

Hello, thank you.

@the_doctor

There’s a Download button at the top of the Jupyter Notebook in the classroom. It downloads all the project-related files including your Notebook and the dataset as well.

I forgot about this. In most DQ projects, the download button/link was in the description too. Anyway, now I know all.

Once again, thank you