How to connect / read sqlite database in jupyter notebook on google drive with google colab? Guided Project 257

Code:

from google.colab import drive
drive.mount('/content/drive')

my_path = '/content/drive/My Drive/mypath'
%%capture
%load_ext sql
%sql sqlite:///factbook.db

What I expected to happen: Clearly, the path is not correct and queries won’t work.

Other details: I am trying to use jupyter notebook with google colab on google drive. I uploaded factbook.db in the same folder as the notebook. And mounted the drive. I even try to use:

import sqlite3
db = sqlite3.connect(my_path + 'factbook.db')

But it didn’t work.
How do I use jupyter notebook with google colab on google drive for the project #257

I may test it with my drive later, but right now, out of the head I can see two options to test:

  1. Remove content/drive from the my_path, just leave /My Drive/mypath AND add last / there! So complete
my_path = '/My Drive/mypath/'
  1. Escape the space in the path:
my_path = '/My\ Drive/mypath/'

Let me know if this didn’t help.

Hey ranklord! It doesn’t work. I tried your solution. Thank you for your time though.

The real path in the google drive is:
/content/drive/My Drive/DataQuest/Mission_Datasets_and_Codes/Working_with_Data_Sources/SQL_Fundamentals_1/Analyzing_CIA_Factbook_Data_Using SQL_1/

In the folder Analyzing_CIA_Factbook_Data_Using SQL_1, I have both notebook and factbook.db files.

So, how do I load the db file in the jupyter notebook on google drive?
Thanks.

By any chance, have you checked this StackOverflow question?

And here’s a full example notebook as well.

I couldn’t get the database to load from Google Drive but it worked fine loading the database from Colab’s local runtime storage. Just be aware that anything in the runtime storage will be deleted when the session ends.

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

If anyone knows how to load a database file from Google Drive please post!

1 Like

This worked for me in collab:

import sqlite3

conn = sqlite3.connect('/content/drive/My Drive/Colab Notebooks/DQ_projects/cia-database/factbook.db')

cur = conn.cursor()

cur.execute("SELECT * FROM sqlite_master WHERE type='table'")

rows = cur.fetchall()

for row in rows:

print(row)

conn.close()

Haven’t found solution to %sql yet

Another working solution for pandas

import sqlite3
import pandas as pd

con = sqlite3.connect('/content/drive/My Drive/Colab Notebooks/DQ_projects/cia-database/factbook.db')
filtered_data = pd.read_sql_query("SELECT name FROM sqlite_master WHERE type='table'",con)
filtered_data.head()

After fighting with it for two hours I can state that issue is with the spaces in the path to the file in drive. I’ve tried all escaping sequences that I know, nothing helped.

the way I tested was that I’ve created a folder w/o spaces in runtime and I can load the database from there. as soon as I added a space into directory name - I can’t access it anymore…