Mounting Google Drives and Saving .db Files

This guide is essential for anyone using Google Colab to manage databases, as it provides a step-by-step process for mounting Google Drive to ensure data persistence. It highlights the importance of saving work to a permanent location, preventing data loss from temporary sessions.

Tasha Penwell
38 steps
3 minutes

Step 1: Mount to Your Google Drive

### Why To Mount Your Database to Your Drive When you work in Colab, you are using a **Temporary Virtual Machine (VM)**. Any files you create (such as the database files) or upload directly to the sidebar will be deleted as soon as you close your browser or the session times out. Mounting your drive ensures your work is saved to Google's permanent servers. ###

Add the code ```notebook-python from google.colab import drive drive.mount ('/content/drive') ``` This code is the "bridge" between the temporary Google Colab environment and your permanent storage. ### What happens when you run this code: - **The Request:** Colab will ask for permission to access your Google Drive files. - **The Link:** Once you click "Allow," your entire Google Drive becomes accessible as a folder inside Colab. - **The Path:** Your files will now be located under the directory `/content/drive/MyDrive/`. If you don't run this code at the start of every session, your notebook won't be able to find your `.db` file, and you will get a `FileNotFoundError`.

If you don't run this code at the start of every session, your notebook won't be able to find your `.db` file, and you will get a `FileNotFoundError`.

Complete the prompts for permission settings.

Click here

We see the message that the it was mounted successfully.

Importing the OS

Click the [[+Code]] button to add a code block

Make sure you have already run the `drive.mount` (Step 1) code before this step. If the drive isn't mounted, Python won't be able to find the `/content/drive/` path and will show an error!

Add the below code and run it ```notebook-python import os folder_path = '/content/drive/MyDrive/ScoutingProject' if not os.path.exists(folder_path): os.makedirs(folder_path) print("Folder created!") ```

This code snippet is used to organize your workspace and ensure your project has a permanent home on Google Drive. ### What this code does: - `import os` - This imports the "Operating System" module, which allows Python to interact with the file system of the computer (the VM). - **Defining the Path:** It sets a variable `folder_path` that points to a specific location in your "My Drive". - **The Safety Check:** The `if not os.path.exists` line checks if the folder already exists. This prevents the code from crashing or trying to overwrite a folder you’ve already created in a previous session. - **Creating the Folder:** If the folder isn't found, `os.makedirs(folder_path)` creates it automatically for you.

We see the folder has been created!

### Why we use this: By creating a dedicated folder like `ScoutingProject`, you ensure that your database (`.db`) and other project files aren't scattered randomly in your main Drive. It makes it much easier to find your data when you return to the exercise later.

Step 3: Establishing the Database Connection

Click on [[+Text]] to add headings in your folder. Type [[# Establish the Database Connection]]

The heading is added. While this is not required to run the code, it can be helpful with organization.

Add this code and run it ```notebook-python #connect to SQLite3 import sqlite3 db_path = '/content/drive/MyDrive/ScoutingProject/scouting_data.db' conn = sqlite3.connect(db_path) c = conn.cursor() print("Permanent database connection established") ```

This code creates a "pipeline" between your Python script and your permanent database file. ### What this code does: - `import sqlite3`: Loads the library that allows Python to talk to SQL databases. - **Defining the** `db_path`: This is the most important part. It tells Python exactly where to find (or create) your file within your **ScoutingProject** folder. - `sqlite3.connect(db_path)`: This opens the file. If the file doesn't exist yet, SQLite will automatically create a brand-new, empty database file for you. - **The Cursor (**`c`**)**: Think of the cursor as a "blinking pointer" or a "messenger." You use it to send your SQL commands to the database and fetch the results back.

Message indicate connection has been established!

### If you see an error saying `sqlite3.OperationalError: unable to open database file`, double-check that you spelled your folder name (`ScoutingProject`) exactly the same way you did in the previous step.

### Why we use the `/content/drive/` path: By using this specific path, you ensure that every time you add a player or update a score, the change is saved directly to **Google Drive**. If you omit this path and just use `'scouting_data.db'`, your data will be saved to the **Temporary VM** and will vanish when you close Colab.

Step 4: Create the Table Structure

Type # Create the Table Structure to create the heading. (Remember this is a Text block)

Add this code ```notebook-python #define the schema c.execute(''' CREATE TABLE IF NOT EXISTS quarterbacks ( name TEXT, team TEXT, passing_yards INTEGER, touchdowns INTEGER ) ''') #Save by commiting conn.commit() print("table Quarterbacks is ready") ```

This step defines the **blueprint** of your database—telling SQLite exactly what kind of data you plan to store. ### What this code does: - **The "Schema"**: This is a technical term for the structure of your database. It defines the table name (`quarterbacks`) and the specific categories of information (columns) you will track. - `IF NOT EXISTS`: This is a safety feature. It tells Python to only create the table if it isn't already there. This prevents errors if you run this cell multiple times. - **Data Types**: Notice the labels `TEXT` (for words) and `INTEGER` (for whole numbers). These ensure the data stays organized and searchable. - `conn.commit()`: This is the "Save" button. In SQL, changes aren't permanent until you "commit" them to the disk. Without this line, your table might disappear when you close the connection.

The message states our table is ready!

### Why this is important: A database is more rigid than a spreadsheet. You must define your columns before you can add any data. By setting this up correctly once, you ensure that every player you add later fits perfectly into the structure you've built.

If you decide to change a column name later (for example, changing `touchdowns` to `TDs`), you will need to either delete the old table or use a special "Alter Table" command. ***It's best to plan your columns carefully before running this cell!***

Step 5: Add Data and Save

Type # Populate Data and Save for the next heading.

Add this code ```notebook-python qb_list = ('Josh Allen', 'Buffalo Bills', 4306, 29) c.execute('INSERT INTO quarterbacks VALUES (?, ?, ?, ?)', qb_list) conn.commit() print("Successfully added qb Josh Allen") ```

This step shows you how to take a specific piece of information and store it inside the table you just built. ### What this code does: - **The Data Tuple (**`qb_list`**)**: We group the player's information (Name, Team, Yards, TDs) into a "tuple". The order of the items must match the order of the columns you created in your schema. - **The Question Marks (**`?`**)**: These are called **Placeholders**. They act as security guards that safely pass your data into the database. Using `?` prevents "SQL Injection," a common way hackers try to break into databases. - `c.execute()`: This sends the specific command to the database engine: "Insert these four values into the quarterbacks table". - `conn.commit()`: Remember, `execute` only prepares the change. Running `commit()` is what officially "writes" Josh Allen's stats into your permanent `.db` file on Google Drive.

We see that data has been added to our table.

### Why we use this method: While you could type the name "Josh Allen" directly into the SQL command, using a variable like `qb_list` and placeholders makes your code much more flexible. This allows you to easily swap out data for different players without having to rewrite your SQL query every time. ### *Tip: If you have a long list of players to add, you don't have to run this code 50 times. You can use a similar command called* `c.executemany()` *to upload a whole list of tuples at once!*

Step 6: Run the Report

Type # Run the Scouting Report to create a new heading

Add this code and run it ```notebook-python #Querying the data c.execute('SELECT * FROM quarterbacks') results = c.fetchall() for row in results: print(row) ```

This step allows you to reach into your "filing cabinet" and pull out the information you have stored. ### What this code does: - `SELECT *`: In SQL, the asterisk (`*`) means "everything." This command tells the database to find every column for every player in the `quarterbacks` table. - `c.execute()`: This sends your request to the database engine. - `c.fetchall()`: This is the "collection" step. It takes all the rows found by the database and saves them into a Python list called `results`. - **The** `for` **loop**: Since `results` is a list of rows, we loop through them one by one to print them to the screen.

Record is displayed

### Why we use this: Retrieving data is the primary goal of any database. The `print(row)` command shows you the raw data. ### **Python: Our SQL "Delivery Truck"** In this exercise, we are using **Python code** to execute our SQL commands. This is why you see SQL syntax (like `SELECT` or `INSERT`) wrapped inside Python functions like `c.execute()`. - **Python's Role**: Right now, Python is acting as the intermediary. We use the `sqlite3` library to send "strings" of SQL text to the database engine. - **The "Wrapper"**: Because we are in a standard Colab Python cell, we cannot type SQL commands directly into the cell yet; Python must "hand-deliver" them for us. - **A Preview of What’s Next**: In future lessons, we will learn how to bypass the Python code using **SQL Magic Commands** (syntax like `%%sql`). This will allow you to write "pure" SQL directly in Colab cells without any Python code around it at all.

By learning to use Python to run SQL first, you are learning how real-world applications (like a mobile app or a scouting website) actually work. Most software uses a programming language like Python to talk to a database behind the scenes.

Step 7: Shutting it Down

Type # Proper Shutdown - Important!

Run this code ```notebook-python conn.close() print("Database safely closed. You can now see this .db file in your Google Drive folder.") ```

This final step is the "clean-up" phase of your database session. It ensures that all your hard work is properly tucked away and that the file is ready for other programs to use. ### What this code does: - `conn.close()`: This command breaks the active connection between your Colab notebook and the database file on Google Drive.

### **Python: The Responsible Manager** Because we are using Python code to manage our database, we have to be responsible for opening *and* closing the door. - **Why we close it**: If you leave the connection open and simply close your browser tab, you risk a "Database is locked" error the next time you try to run your code. - **A Lesson in Persistence**: By using Python to run the `close()` command, you are ensuring that your data moves from the computer's memory (RAM) and is fully written into the physical storage of your Google Drive.

Step 8: Find the .db File in Google Drive

Go do your Google Drive folder

Find the folder created (ScoutingProject)

You should see your scouting_data.db file

Download the file to your computer.

It is a common misconception that "the cloud" is an invincible backup on its own. While cloud services like Google Drive are incredibly stable, they are not a substitute for a backup strategy. ### 1. Cloud Syncing is a "Double-Edged Sword" Cloud storage works by **syncing**—it tries to make your Drive look exactly like your current workspace. - **The Problem:** If you accidentally delete a table or corrupt a database file the cloud will sync that mistake. - **The Result:** The corrupted version overwrites the good version on the server instantly. Without a separate backup (like a downloaded copy), the "good" version is gone forever. ### ### 2. Database-Specific Risks (The "Half-Written" File) Databases can be sensitive to "network flickers." - If your internet cuts out for a split second while Python is writing to your cloud-mounted Drive, the file might save as a partial, unreadable mess. - This creates a **Logical Corruption** where the file exists, but the "index" inside it is broken. A backup from yesterday is the only way to recover without starting from scratch.

### **Blueprint vs House Analogy** Python code is the **Blueprint**, and your `.db` file is the **House**. If the house burns down (file corruption), you can use the blueprint to build it again. But if you have 1,000 players in your database, building it again takes a long time. A **Backup** is like having a second house already built in a different neighborhood. It’s the fastest way to get back to work.

Submission

Create a new Colab file and name it LastName_IC1. Upload

Open the folder panel and click the upload button.

Select the .db file you downloaded from Step 8.

Double-click on the file to show the content (as seen on the right side panel). Take a screenshot (be sure your Colab file is named appropriately and visible). Submit screenshot into ISMS.