Metis
Running SQL Commands Directly from Jupyter: The Power of %%sql

Running SQL Commands Directly from Jupyter: The Power of %%sql

Itay Braun's photo
Itay Braun
·

3 min read

If you're a data scientist, analyst, or anyone who works with data, you've probably spent considerable time using Jupyter notebooks. These versatile tools are great for exploring, analyzing, and visualizing data, typically using Python or R.

With %%sql, it's possible to execute SQL code within your notebooks, allowing for seamless integration between SQL-based data manipulation and Python-based data analysis. In this blog post, we'll explore how to set up and use %%sql in Jupyter.

What Is %%sql?

%%sql is a cell magic command in Jupyter that allows you to run SQL code within a Jupyter notebook. This feature is part of the ipython-sql extension, which integrates SQL databases into Jupyter. With %%sql, you can connect to various databases, execute queries, and even use the results within your Python code. This can be incredibly useful for data analysis, machine learning workflows, and more.

Setting Up %%sql in Jupyter

To use %%sql, you need to install the ipython-sql package in your Jupyter environment. You can do this using pip:

!pip install ipython-sql

Or use the %%bash command...

Once installed, you need to load the extension within your Jupyter notebook:

%load_ext sql

With this done, you're ready to connect to a database and start running SQL commands.

Connecting to a Database

To use %%sql, you need to establish a connection to your database. The connection string format varies depending on your database type. Here's an example of how to connect to a PostgreSQL server.

%sql postgresql://user:password@host:port/database

For ex.

%sql spostgresql://postgres_user:your_password@org-metis-inst-dev1.data-1.use1.tembo.io:5432/postgres

If you work with more than one connections, the notebooks shows all confiured connections and mark with a "*" the current one.

Running SQL Commands with %%sql

Once connected, you can start running SQL commands using the %%sql magic. Here's an example that creates a table and inserts a few records:

%%sql
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    department TEXT
);

INSERT INTO employees (name, age, department) VALUES
('Alice', 30, 'HR'),
('Bob', 25, 'Engineering'),
('Charlie', 35, 'Marketing');

To query the data, you can use %%sql as well:

%%sql
SELECT * FROM employees;

The result is displayed directly in the Jupyter notebook, allowing you to visualize and analyze the data without switching contexts.

For ex.

Parameter substitution is a useful feature that lets you insert values into SQL queries at runtime. It not only improves code flexibility but also enhances readability. To use parameter substitution, define the variable in your local scope and prefix it with a colon, like :parameter.

db_name='demo'
%sql SELECT * from pg_stat_database where datname = :db_name;

Integrating SQL Results with Pandas

One of the benefits of using %%sql is the ability to integrate SQL results with Python code. You can assign the results of an SQL query to a variable and then manipulate it with Python libraries like Pandas:

result = %sql SELECT * FROM employees;
dataframe = result.DataFrame()

Now you have a Pandas DataFrame that you can work with using Python's rich data analysis ecosystem.

Jupyter Notebook SQL