2 Ways of Using SQL in Google Colab

Data Manipulation with SQLite and Magic Commands in Google Colab

Google Colab has become an indispensable tool for data scientists and machine learning practitioners. It provides a free, collaborative, and cloud-based environment to write and execute Python code. Yet, many users don’t realize that they can also use SQL, the go-to language for data manipulation and querying, directly within Colab.

In this guide, we’ll explore two approaches to using SQL within Google Colab: using Python’s SQLite library and using magic commands. By the end, you’ll have a solid understanding of how to create, populate, and query a SQL database within your Colab environment.

If you are new to Google Colab, please check out my tutorial Google Colab Tutorial For Beginners.

Resources for this post:

• Video tutorial for this post on YouTube
• Click here for the Colab notebook
• More video tutorials on NLP
• More blog posts on NLP

Let’s get started!

Approach 1: Using SQLite Database Cursor

SQLite is a self-contained, serverless, and zero-configuration database engine. Python comes with built-in support for SQLite, meaning no additional installation steps are needed.

Let’s start by creating a database and a table. In your Google Colab notebook, import the sqlite3 module and establish a connection to the database. For this example, we’ll use an in-memory database, denoted by :memory:.

# Import library
import sqlite3

# Connect to an SQLite database; use ':memory:' for an in-memory database
conn = sqlite3.connect(':memory:')

With the connection established, we can now create a table. Let’s create a table named stocks with five columns (date, trans, symbol, qty, price).

# Execute a SQL command to create a new table
c = conn.cursor()
c.execute('''
          CREATE TABLE stocks
          (date text, trans text, symbol text, qty real, price real)
          ''')

We can insert data into our stocks table using standard SQL INSERT syntax. As an example, we inserted the date of January 5, 2006, the transaction action of buying, the stock name of ABC, the quantity of 100, and the price of 35.14 dollars.

Then, we commit the transaction to save changes to the database.

# Execute a SQL command to insert data into the table
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','ABC',100,35.14)")

# Commit the transaction to save changes to the database
conn.commit()

Now that we have a populated database, we can start running SQL queries on it. Let’s select all BUY transactions from our stocks table and print the output by fetching all the results from the execution.

# Execute a SQL SELECT statement to query the database
c.execute("SELECT * FROM stocks WHERE trans='BUY'")

# Fetch all rows from the result of the query
print(c.fetchall())

Output:

[('2006-01-05', 'BUY', 'ABC', 100.0, 35.14)]

Approach 2: Using Magic Commands

Magic commands are a feature of IPython that provides a set of commands to solve common problems while working with data. The SQL magic command is one such command that lets you write SQL queries in your notebook.

First, we need to install the ipython-sql extension. This can be done directly in a Colab cell:

# Install ipython-sql
!pip install ipython-sql

Next, load the SQL extension and create a SQLite database:

# Load the SQL extension
%load_ext sql

# Create a SQLite database
%sql sqlite://

Now you can write SQL queries using the %sql or %%sql magic command. % is for single-line commands, and %% is for multi-line commands that run the whole cell as SQL. Here’s an example of executing SQL commands to create a table, insert data, and run a query using %%sql.

# Execute SQL commands to create a table, insert data, and run a query
%%sql
CREATE TABLE test_table(name, age);
INSERT INTO test_table VALUES('Alice', 24);
SELECT * FROM test_table;

Output:

*  sqlite://
Done.
1 rows affected.
Done.
name age
Alice 24

Conclusion

Google Colab is a powerful tool that’s not just limited to Python. With its support for SQL through Python libraries like SQLite and magic commands, it becomes an even more versatile platform for data analysis and machine learning tasks.

If you are interested in using Google Colab with R, please check out my previous tutorial How to Use R with Google Colab Notebook.

More tutorials are available on GrabNGoInfo YouTube Channel, GrabNGoInfo.com, and LinkedIn.

Should you find this article helpful, show your appreciation on Medium with claps. Don’t forget to hit the follow button for more content like this.

Unlock unlimited access to a world of knowledge — become a Medium member today through my referral link and empower your learning journey!

Recommended Tutorials

• GrabNGoInfo Machine Learning Tutorials Inventory
• Google Colab Tutorial For Beginners
• Hierarchical Topic Model for Airbnb Reviews
• How to Use R with Google Colab Notebook
• 3 Ways for Multiple Time Series Forecasting Using Prophet in Python
• Time Series Anomaly Detection Using Prophet in Python
• Time Series Causal Impact Analysis in Python
• Hyperparameter Tuning For XGBoost
• Four Oversampling And Under-Sampling Methods For Imbalanced Classification Using Python
• Five Ways To Create Tables In Databricks

References

• SQLite website
• Jupysql documentation