From colab directly manipulate sqlite3 format data in Google drive

Question

From colaboratory, is it possible to directly manipulate sqlite 3 format data in google drive? It is possible if you upload it, but it is convenient to use it in google drive.

Answer 1

You can do load files directory from Drive by mounting your Google Drive as a FUSE filesystem.

Here's an example: https://colab.research.google.com/notebook#fileId=1srw_HFWQ2SMgmWIawucXfusGzrj1_U0q

There's no official Google Drive FUSE filesystem. But, several open-source FUSE + Drive libraries have been written by third parties. The example notebook above uses google-drive-ocamlfuse. The notebook shows three things:

1. Installing the Drive FUSE wrapper in the Colab VM.
2. Authenticating to Drive and mounting your Drive using the FUSE wrapper.
3. Listing and creating files in the newly mounted Drive-backed filesystem.

Answer 2

First and foremost, upload the database.sqlite and the desired csv file(Reviews.csv in my case) to Google Drive.

Then, you need to mount your drive in Google Colab using the following command:

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

This will result in the following output:

Go to this URL in a browser: https://accounts.google.com/o/oauth2/auth?client_id=947318989803-6bn6qk8qdgf4n4g3pfee6491hc0brc4i.apps.googleusercontent.com&redirect_uri=urn%3aietf%3awg%3aoauth%3a2.0%3aoob&response_type=code&scope=email%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdocs.test%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdrive%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdrive.photos.readonly%20https%3a%2f%2fwww.googleapis.com%2fauth%2fpeopleapi.readonly

Enter your authorization code:

Click on the URL that will lead to a new tab that shows your Google account to provide certain privileges to Google Drive File Stream:

Choose an account
to continue to Google Drive File Stream

You must select the same Google account with which you logged in to the Google Colab. Then click on the 'Allow' button. This will lead to another page that shows the alphanumeric code. Copy the code and paste it in the text area(Enter your authorization code: ).

Consequently, the message about the drive being mounted will be displayed:

··········
Mounted at /content/gdrive

Now click on the folder icon(below 'show code snippet pane' and is located on the left side of the code cell), you can see the gdrive folder. Your Google Drive - My Drive will be located inside gdrive. Now click on the desired folder in which you stored the database.sqlite file. Right-click on it and select 'copy path'.

The path you copied should be pasted in the path link of the following command:

con = sqlite3.connect('paste the path you copied')

For example, if the database.sqlite resides in '/content/gdrive/My Drive/Colab Notebooks/database.sqlite', then the command will be as follows:

con = sqlite3.connect('/content/gdrive/My Drive/Colab Notebooks/database.sqlite')

Now you may run some SQL queries to check whether all is well:

filtered_data = pd.read_sql_query("""
SELECT * 
FROM Reviews
WHERE Score != 3
""", con)
print(filtered_data.head())