Setting up Google Colab with SQLite

This guide provides a step-by-step approach to setting up Google Colab with SQLite, making it an essential resource for anyone looking to leverage lightweight database management in their projects. It highlights the benefits of SQLite, such as its serverless nature and ease of integration with Python, which is particularly useful for business intelligence applications. By following this guide, users can efficiently create and manage databases without complex configurations, enhancing their data analysis capabilities.

Tasha Penwell
19 steps
4 minutes

Creating a Google Colab File

Open up Google Drive and create a new Google Colab file. You see that after you add Google Colab (as completed from Prep1), it remains available just like other applications such as Google Docs.

Name the file LastName_HW1 [[Return]]

Add a Text Box

Click on the [[Text]] button to add a text box.

Add the below text as markdown. [[# Homework 1: Business Intelligence Setup]] [[## Student Name: Tasha Sloan]] [[### Why SQLite?]]

Markdown headings are the simplest way to give your document structure and hierarchy. They use the hash symbol (`#`) followed by a space to define the importance of the text. ### How they work - **Hierarchy:** The number of `#` symbols determines the heading level (1 through 6). - **Visual Styling:** Most editors will automatically bold and resize the text based on the level. ### Examples from your text: - `#` is a **Heading 1**: Used for the main title of the document. Usually, there is only one of these per page. - `##` is a **Heading 2**: Used for major sections (like "Student Name"). - `###` is a **Heading 3**: Used for sub-sections within a main topic **Pro Tip:** Always remember to include a space between the `#` and your text (e.g., `# Heading`), otherwise, some platforms might just render it as plain text.

Add the below text describing the benefits of SQLite and then click [[Close]]. ```markdown **Serverless:** No need to login to a separate database server **Zero-Configuration:** We don't have to "install" it. It's ready to go by importing it. **Portability:** The entire database is stored in a single .db file ```

This snippet uses a combination of **Bold Markdown** and **HTML Line Breaks** (` `) to create a clean list of definitions. Here is a breakdown of what’s happening and how you could optimize it: ### The Elements Used - **Bold Syntax (**`**...**`**):** This highlights the key terms (**Serverless**, **Zero-Config**, etc.) to make them stand out as labels. - **HTML Line Breaks (**` `**):** These are used to force the text onto a new line.

Your results should be similar to this.

Add Code Cell

Click on the [[Code]] button

Importing SQLITE3

### What is SQLite? **SQLite** is a lightweight, high-performance database engine that is used everywhere—from your smartphone and web browser to professional data analysis scripts. Unlike traditional databases (like MySQL or SQL Server), SQLite is **embedded**. This means the database doesn't run as a separate background service; instead, the entire database is just a single file on your hard drive. --- ### Key Characteristics - **Self-Contained:** It is a single library that fits inside your application. You don't need to "install" a database server on your computer to use it. - **File-Based:** The entire database (tables, indexes, and data) is stored in one ordinary disk file (usually ending in `.db` or `.sqlite`). - **Zero Configuration:** There are no user accounts, permissions, or complex network settings to manage. You simply point your code to a file and start working. - **Transactional (ACID):** Despite its small size, it is incredibly reliable. Even if your computer crashes or loses power, SQLite is designed to prevent data corruption. --- ### Why use it for Business Intelligence (BI)? 1. **Portability:** You can move your database from your laptop to a flash drive just by copying a file. 2. **Standard SQL:** It uses the same SQL language (SELECT, INSERT, UPDATE) used by giant corporations, making your skills highly transferable. 3. **Python Integration:** It comes built-in with Python, allowing you to bridge the gap between raw data storage and data visualization libraries like Pandas or Matplotlib.

Type [[import sqlite3]] and then Enter

Type [[print ("SQLite library loaded successfully")]]

In Python, the `sqlite3` module is a standard library, meaning you don't need to install anything via the terminal to use it. It is the bridge that allows Python to "talk" to an SQLite database file. ### Breakdown of the Code - `import sqlite3`: This line pulls the SQLite engine into your current Python script. It allows you to use functions like `connect()` to open database files. - `print(...)`: This is a "sanity check." If you run the script and see this message in your console, it confirms that your Python environment is configured correctly and the library is ready for use.

Click Run

You should see the message printed to the screen.

Create the Database File

When working in **Google Colab**, creating a database with SQLite is incredibly efficient because the environment comes pre-installed with Python and all the necessary libraries.

Click on the [[Code]] button

Type in the below text and Run the code by clicking on the play button ```notebook-python #This creates the database file and establishes the 'conn' (connection) object conn = sqlite3.connect('MIS2800_HOMEWORK.db') print("Database created and connected") ```

### Breaking Down the Code - `sqlite3.connect('MIS2800_HOMEWORK.db')`: This function does two things at once. It looks for a file named `MIS2800_HOMEWORK.db` in your current folder. - If the file **exists**, it opens it - if it **does not exist**, SQLite creates it automatically. - `conn`: This is a variable that stores the Connection Object. Think of this as the "open phone line" between your Python script and the database file. You will use this variable later to save (commit) your changes or close the file. - The `.db` Extension: While you can name the file anything, using `.db` or `.sqlite` is a standard practice that helps other programs (and your computer) recognize it as a database file.

### In a Business Intelligence environment, the connection object is your primary gatekeeper. - **One File, One Database**: Your connection is tied directly to that specific `.db` file.

Folders in Google Colab

Confirm that the file was created by clicking on the Folder *Note: These folders will be deleted when you close this file because Colab uses an ephemeral virtual machine. We'll be reviewing more on this in class.*

We see the file has been created

Submitting the Assignment

Take a screenshot showing your folder. Be sure that your file name is visible and it's been named appropriately.

Setting up Google Colab with SQLite

Tasha Penwell
19 steps
4 minutes

Creating a Google Colab File

Open up Google Drive and create a new Google Colab file. You see that after you add Google Colab (as completed from Prep1), it remains available just like other applications such as Google Docs.

Name the file LastName_HW1 [[Return]]

Add a Text Box

Click on the [[Text]] button to add a text box.

Add the below text as markdown. [[# Homework 1: Business Intelligence Setup]] [[## Student Name: Tasha Sloan]] [[### Why SQLite?]]

Your results should be similar to this.

Add Code Cell

Click on the [[Code]] button

Importing SQLITE3

Type [[import sqlite3]] and then Enter

Type [[print ("SQLite library loaded successfully")]]

Click Run

You should see the message printed to the screen.

Create the Database File

When working in **Google Colab**, creating a database with SQLite is incredibly efficient because the environment comes pre-installed with Python and all the necessary libraries.

Click on the [[Code]] button

### In a Business Intelligence environment, the connection object is your primary gatekeeper. - **One File, One Database**: Your connection is tied directly to that specific `.db` file.

Folders in Google Colab

We see the file has been created

Submitting the Assignment

Take a screenshot showing your folder. Be sure that your file name is visible and it's been named appropriately.