Google Colab is a valuable tool for data scientists and machine learning experts. It is a free, collaborative, and cloud-based environment that enables users to write and execute Python code. However, some users are unaware that they can use SQL, the standard 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.
To use SQL in Google Colab, there are two approaches: using Python libraries like SQLite3 or using SQL magic commands. The first approach involves importing SQLite3 libraries, creating a connection to a database, and executing SQL queries using pandas’ read_sql_query() function.
The second approach involves using SQL magic commands, which are similar to Jupyter Notebook’s magic commands. To use SQL magic commands, you need to install the ipython-sql library and load the SQL extension using the %load_ext sql command. Once the extension is loaded, you can connect to a database using the %sql command and execute SQL queries using the %%sql cell magic command.
Method 1: Using SQLite Database
SQLite is a serverless, self-contained, and zero-configuration database engine that requires no additional installation steps. Python comes with built-in support for SQLite.
Let’s begin by importing the SQLite3 module and establishing a connection to the database in your Google Colab notebook. We’ll use an in-memory database for this example, which is denoted by <strong>:memory:</strong>
SQLite in-memory databases are stored in memory rather than on a disc.
# Import library
import sqlite3
# Connect to an SQLite database; use ':memory:' for an in-memory database
conn = sqlite3.connect(':memory:')We used the SQLite database library to create a table called “Students” with columns for “name,” “age,” and “weight.”
# Execute a SQL command to create a new table
c = conn.cursor()
c.execute('''
CREATE TABLE Students
(name text, age real, weight real)
''')We can insert data into our students’ table using standard SQL INSERT syntax.
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 Students VALUES ('John',28,70)")
# Commit the transaction to save changes to the database
conn.commit()Now that we have a database, we can query it using SQL. We’ll select all records from the students’ table and then fetch all results from the execution to print the output.
# Execute a SQL SELECT statement to query the database
c.execute("SELECT * FROM Students")
# Fetch all rows from the result of the query
print(c.fetchall())# Execute a SQL SELECT statement to query the database
c.execute("SELECT * FROM Students")
# Fetch all rows from the result of the query
print(c.fetchall())
Approach 2: Using Magic Commands
Magic commands in IPython are a useful set of commands that help solve common problems while working with data. One such command is the SQL magic command that allows writing SQL queries within a 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-sqlNext, 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 commands. % is for single-line commands, and %% is for multi-line commands that run the entire 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;* sqlite:// Done. 1 rows affected. Done. name age Alice 24
