Connecting to database in SQLite shell connection info needed in SQLAlchemy format

Screen Link: https://app.dataquest.io/m/192/table-relations-and-normalization/1/the-sqlite-shell

I am trying to follow the course using JupyterLab but am unable to connect to the chinook database:

%sql sqlite:///Users/william/Devs/datasets/chinook.db

What actually happened:

(sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/e3q8)
Connection info needed in SQLAlchemy format, example:
               postgresql://username:[email protected]/dbname
               or an existing connection: dict_keys(['sqlite://'])

Why is there a need for username and password since i can connect to it directly in my Jupyter notebook?

I also tried the following

sqlite3 chinook.db

I only get the following error

File "<ipython-input-25-4257ca30c1a7>", line 1
    sqlite3 chinook.db
                  ^
SyntaxError: invalid syntax

Should i use ipython sql or alchemy? i presume magics no longer applies here as i am not using notebook for this part of the course right?

hi @willx

I tried with the following change in my project:

  • added drive
  • added double slashes in between folders

%sql sqlite:///C://Users//Dell//Desktop//factbook.db

This worked for me.

1 Like

Hi @rucha
saw your shared project on answering Business Question on SQL. nice. if only i understand SQL that deep.

i am using Mac, and it still doesn’t work for me

%sql sqlite:///Users/william/Devs/datasets/chinook.db
(sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/e3q8)
Connection info needed in SQLAlchemy format, example:
               postgresql://username:[email protected]/dbname
               or an existing connection: dict_keys(['sqlite://'])

I am unable to reproduce your error. In your notebook, can you please add the following lines of code at the top, each in its own cell, and then run your notebook up to the error?

!ls
!pwd
!find / -name chinook.db 2>/dev/null

What’s the output? Can you also share your notebook?

This is because sqlite3 chinook.db is a shell command and you’re running it in Python. Here’s a more blatant display of this behavior:

image

Hi @willx

I definitely need to work on my jumping the gun habit. :stuck_out_tongue:

I was wondering the same about windows and other OS differences, I tried without “drive” as well, then completely forgot to ask you for your os or etc. details.

please do follow @bruno’s suggestion, even I want to know how to work around this error. have seen it on StackOverflow.com as well.

Hi Bruno
i tried running 3 lines together, it goes quite fast. however if i run it individually it runs quite slowly.

Also am not sure how to export notebook as i am trying to use console in Jupyterlab to follow the lessons. are you able to see the attachment?

The screenshot you shared isn’t aligned with what you asked in the question at all:

  • You’re connecting to a different database.
  • You don’t get the same error (because you didn’t run %%capture and %load_ext sql).

Can you please either edit the question or share a screenshot that is in conformity with what was asked in the question?

oops sorry wrong screen. try this :grinning:

Thanks. Next time please include an image file inline, instead of sharing a screenshot as a PDF. Also, note that you didn’t reproduce what you mentioned in the first post (you’re not trying to connect to the same database file). I’ll focus on the last screenshot.

On cell run 8, we see that the working directory is /Users/william/Devs/Dataquest DS/Step 4.

When you run %sql sqlite:///Users/william/Devs/datasets/chinook.db, you’re telling ipython-sql to connect to the database in the relative path Users/william/Devs/datasets/chinook.db.

This means that it is trying to connect to /Users/william/Devs/Dataquest DS/Step 4/Users/william/Devs/Dataquest DS/Step 4/chinook.db; this doesn’t exist.

I got that it is using a relative path from SQLAlchemy’s documentation:

# sqlite://<nohostname>/<path>
# where <path> is relative:
engine = create_engine('sqlite:///foo.db')

So what you’ll want to do, assuming you’re running IPython from the same directory, is simply %sql sqlite:///chinook.db.

If you want to connect to /Users/william/Devs/datasets/chinook.db, then you need to add an extra slash: sqlite:////Users/william/Devs/datasets/chinook.db.

Except it won’t work because at least one of the intervenient components doesn’t have access to the full filesystem in the way that it needs to (probably the magic itself); it can only access whatever is in the working directory.

This doesn’t contradict the documentation, by the way, because the documentation is talking about running a Python script, not using magics.

oh wow, it works now. thank you so much Bruno for your help and patience to explain :grinning:

Yup you are right - the sqlite:////Users/william/Devs/datasets/chinook.db doesn’t work.

1 Like

Hey,

When I try to run the same thing, instead of getting an error I get nothing. No output.
I tried looking on the console and couldn’t figure out what the error seems to be.
I’m uploading here my python notebook where I did the steps you told willx to do. I always get extremely confused when it comes to working directories and folder structure and how to link documents to each other. Can you please help?

Basics.ipynb (2.5 KB)

Click here to view the jupyter notebook file in a new tab

You mean on the first cell? That’s expected if you’re on Jupyter Lab (instead of Jupyter Notebook).

Try running something like what’s below in the second cell.

%%sql 

SELECT *
  FROM customers;

I’m running jupyter notebook and when I try to do any query it just says I am not connected to any database :confused: with pandas it works though

What do you mean with it working with pandas? Whatever it is, can you show it in a notebook, please?

If I use this code to connect to the database, somehow it works, then I can use the suggested %%sql magic to run my queries:

import sqlite3
import pandas as pd
conn= sqlite3.connect(‘chinook.db’)

Basics.ipynb (26.9 KB)

The last notebook mixes lots of things together, it’s hard to tell what’s causing what. Please run a new notebook with the following cells, and share the notebook back with the outputs.

!ls -hl
!pwd
!find / -name chinook.db 2>/dev/null
%%capture
%load_ext sql
%sql sqlite:///chinook.db
%%sql
SELECT name, type
  FROM sqlite_master
 WHERE type IN ("table","view");

Sorry for the messy notebook, it had the history of my trying to first solve the problem following your steps and then deciding to follow on with the “pandas” code.

I ran the codes you told me to and now it works. I don’t understand why it didnt work on my first try though (in both projects) I guess it had something to do with my folder structure.

What does this do? --> !ls -hl

Possibly, but also with the exact steps that you took. That’s why I asked for a fresh notebook.

It’s completely unnecessary. That’s just diagnosis information for me — the first three lines of code are; you can remove them.

Anyway, what it does is list the contents of the working directory in a special format. You can learn more about it in the command-line missions, specifically on this screen.

@Rucha
Your answer worked for me, Just want to know what to do if there are spaces in folder name

  %sql sqlite:///C://Users//Dell//De sk top//factbook.db

like a space between De sk top ,
I have tried inserting %20 in place of space but still wasn’t able to connect

Thanks

Please how did u get through the error message @ Rucha