Can't Connect to Database on Jupyter

I’m trying to reproduce the results of the factbook guided project on jupyter notebook, but I can’t connect to the database (actually, it connects, but I can’t run any sql commands.

That’s what I’m typing.

To make shure there was no problem with the file I downloaded, I tried to open it with the SQLite Shell and succedeed.

I expected to connect to the database and be able to type SQL commands on jupyter, but it returns this OperationalError.

I have sqlite and the factbook.db file saved on the same directory in my computer. One thing I can point out is that when I run the same code on DQ’s platform, the “connect” output looks slightly different.

There is this “none” before the message saying the connection was stablished.

Can anyone point me out the solution of the problem ?

Thanks in advance!

Hey.

Can you please add three cells at the top of your notebook, each their own line of code given respectively below, and run the whole thing?

!dir
!echo %cd%
!where /R C:\ /T *.db

What is the output? Please share actual text in addition to screenshots.

Hey, Bruno. Thanks for helping me.

What do you mean by “actual text” ?

I ran what you told me, but the end result didn’t end at all.

This is the output I got from the commands you told me to run (the lists are huge, so those are just the top results):

At the end, the output remained the same of my first message though.

I’m not sure if this helps in anyway. Also, if you could clarify what you mean by “actual text”. I’d gladly send you more information.

Can you also tell me which commands you told me to run ? Thanks!

Text as in something I can copy and paste, and manipulate, not screenshots.

Please run these commands instead, then. You can put it all together in the same cell, at the top of the notebook. You can remove the previous cells.

!dir >output_dir.txt
!where /R C:\ /T *.db output_dbs.txt

The first command lists the files in the current directory and sends the output to output_dir.txt that should be located in C:\users\celio\My Projects\Guided Projects.

The second command finds all .db files in C and sents the output to output_dbs.txt that should be located in C:\users\celio\My Projects\Guided Projects.

Please share these files in your reply.

The first and third ones I explained above, the second one prints the current directory. I realized that the current directory is printed with the dir command, so I dropped the second command now.

Hey, Bruno. Thanks for the explanation.

As you told to, I typed the following code in the first box of the jupyter interface:

!dir >output_dir.txt
!where /R C:\ /T *.db output_dbs.txt

The output is again a huge list, but now I got the following message at the end that says the file output_dbs.txt couldn’t be located:

I tried to access the database, but it didn’t work. I hope my reply better suits your requirements from your last post.

Thanks again for the help and sorry for the trouble.

My bad, the second command was missing a character. Please run the code below.

!dir >output_dir.txt
!where /R C:\ /T *.db >output_dbs.txt

Attach the files output_dir.txt and output_dbs.txt to your reply.

Hey, Bruno.

I hope you find what you’re looking for in the files below. I really appreciate you taking the time to help me with this issue!.

output_dbs.txt (44.2 KB) output_dir.txt (2.4 KB)

Beleza.

What’s happening is that you’re actually not connecting to the database that you downloaded from the app.

In output_dbs.txt, we can find the following three lines:

     67584   16.04.2020      14:26:46  C:\Users\celio\Downloads\factbook.db
         0   23.04.2020      19:58:49  C:\Users\celio\My Projects\Guided Projects\factbook.db
     67584   24.04.2020      19:44:44  C:\Users\celio\sqlite-tools-win32-x86-3310100\factbook.db

These are all the files named factbook.db in C:.

Furthermore, you’re running Jupyter from C:\Users\celio\My Projects\Guided Projects. I got this information from the other file, from which I display a few of the lines below.

 O volume na unidade C é OS
 O Número de Série do Volume é 5250-51BA

 Pasta de C:\Users\celio\My Projects\Guided Projects

27.04.2020  09:39    <DIR>          .
27.04.2020  09:39    <DIR>          ..
24.04.2020  19:16    <DIR>          .ipynb_checkpoints
23.04.2020  19:58                 0 factbook.db

Note the size of factbook.db. This is empty, there’s nothing there.

One way to get it to work is to copy the file to the directory C:\Users\celio\My Projects\Guided Projects.

You may be wondering where the empty factbook.db came from. You created it when you tried to connect to it when it didn’t exist.

Well, unfortunately I have to say it didn’t yield the results I expected.

I saved the file in the folder you suggested and tried to run the whole thing again. However, it still can’t connect to the database.

What I find odd, is that whenever I try to access the databse with sqlite3, it works (just like it did before).

I really don’t know what the problem could be. However, thanks again for your help!

This is bizarre. Are you getting those results with sqlite3 without replacing the file?

Can you please put the content of the first two cells into just one cell and rerun that notebook? This is just to humor me, I do not understand this.

Hey, Bruno. I hope this output satisfies your curiosity.

As for your question, besides typing the code you asked, I accessed the database file saved in three different folders in my computer with sqlite3. The fact that I can specify the file path makes it really practical.

Sorry, I didn’t make myself clear.

I meant for the first cell to be as follows:

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

If you could share a similar screenshot, only replacing the contents of the first cell with the three lines of code above, that would be great. don’t forget to restart the kernel before running it again!

Thanks!