Supercharge Your Jupyter Notebook: SQL Command Magic for IPython

Find the executable notebook here. Jupyter Notebooks are widely used for data analysis and scientific computing, but working with databases inside them has always been somewhat cumbersome. While libraries like sqlite3 or pymssql provide connectivity, they require extra Python boilerplate for managing connections, executing queries, and formatting results. Wouldn’t it be better if we could directly run SQL queries inside a Jupyter Notebook, just like in SQL Server Management Studio (SSMS)? The Problem: SQL in Jupyter Notebooks Many data professionals need to execute SQL queries within a Jupyter Notebook. However, the existing approaches often come with drawbacks: Complex Setup: Managing database connections, cursors, and transactions manually. Verbosity: Writing additional Python code to fetch and display query results. Limited Integration: Difficult to run multi-statement SQL batches using GO commands. Instead of spending time writing extra Python code, what if we could just run SQL commands directly inside a cell, as if we were in SSMS? The Solution: SQL Command Magic SQL Command Magic for IPython is an IPython extension that integrates Microsoft’s sqlcmd utility into Jupyter Notebooks. It allows users to execute native SQL queries inside Jupyter, without any extra Python code. Key Features ✅ Seamless SQL Execution - Write SQL directly in notebook cells without additional Python code. ✅ Built-in Connection Management - Connect to Microsoft SQL Server dynamically. ✅ Multi-Statement Execution - Supports GO statements for executing multiple queries at once. ✅ Variable Substitution - Pass Python variables directly into SQL queries. ✅ Debugging Support - Use --debug to analyze query execution details. Installation and Setup Find the executable notebook here. Step 1: Install the Extension First, install the required package: pip install ipython-sqlcmd python-dotenv Step 2: Load the Extension In your Jupyter Notebook, load the extension using: %load_ext sqlcmd This enables the %sqlcmd magic command inside Jupyter. Step 3: Connect to SQL Server To connect to a SQL Server instance, use: %sqlcmd master --server=localhost --username=sa --password={os.getenv('SSMS_PASSWORD')} --encrypt --trust-certificate You can replace localhost and credentials with your own connection details. Running SQL Queries in Jupyter Simple Query Once connected, you can execute SQL commands inside a notebook cell: %%sqlcmd SELECT TOP 10 * FROM sys.tables ORDER BY name This fetches the top 10 tables from the system catalog, just like in SSMS. Creating and Populating Tables Creating and inserting data is straightforward. Let’s create a table and insert some values: %%sqlcmd CREATE TABLE TestSpaces ( ID int, Description varchar(100), Code varchar(20) ); INSERT INTO TestSpaces (ID, Description, Code) VALUES (1, 'This has spaces', 'A1'), (2, 'Another spaced value', 'B2'), (3, 'No spaces', 'C3'); SELECT * FROM TestSpaces; This will create the table, insert some values, and return the data in a single execution. Using Python Variables Inside Queries You can use Python variables to dynamically modify your SQL queries: table_name = "sys.tables" limit = 5 %%sqlcmd SELECT TOP $limit * FROM $table_name ORDER BY name The $limit and $table_name placeholders are automatically replaced with the Python variables before execution. Executing External SQL Scripts SQL Command Magic also supports executing external SQL files, making it useful for database migrations or schema setup: %%sqlcmd EXECUTE_SQL_FILE '../src/tests/empty.sql' This will run all SQL commands inside empty.sql. Debugging Queries To troubleshoot execution issues, enable debug mode: %%sqlcmd --debug SELECT @@VERSION AS SQLServerVersion This outputs detailed execution logs, showing how the query was processed. Running Multiple SQL Batches Unlike standard SQL execution in Jupyter, SQL Command Magic fully supports multi-statement execution using GO: %%sqlcmd SELECT DB_NAME() AS CurrentDatabase GO SELECT @@SERVERNAME AS ServerName Each query batch executes separately, just like in SSMS. Conclusion SQL Command Magic for IPython is a simple yet powerful tool for running SQL queries inside Jupyter Notebooks. It removes unnecessary Python boilerplate, enables multi-statement execution, and integrates seamlessly with Microsoft SQL Server. Key Benefits ✅ Reduces Boilerplate – No need to write extra Python code for database connections. ✅ More Natural SQL Workflow – Execute queries just like in SSMS. ✅ Advanced Features – Supports GO statements, variable substitution, and script execution. If you frequently run SQL queries in Jupyter, this extension is a gam

Mar 18, 2025 - 23:36

Supercharge Your Jupyter Notebook: SQL Command Magic for IPython

Find the executable notebook here.

Jupyter Notebooks are widely used for data analysis and scientific computing, but working with databases inside them has always been somewhat cumbersome. While libraries like sqlite3 or pymssql provide connectivity, they require extra Python boilerplate for managing connections, executing queries, and formatting results.

Wouldn’t it be better if we could directly run SQL queries inside a Jupyter Notebook, just like in SQL Server Management Studio (SSMS)?

The Problem: SQL in Jupyter Notebooks

Many data professionals need to execute SQL queries within a Jupyter Notebook. However, the existing approaches often come with drawbacks:

Complex Setup: Managing database connections, cursors, and transactions manually.
Verbosity: Writing additional Python code to fetch and display query results.
Limited Integration: Difficult to run multi-statement SQL batches using GO commands.

Instead of spending time writing extra Python code, what if we could just run SQL commands directly inside a cell, as if we were in SSMS?

The Solution: SQL Command Magic

SQL Command Magic for IPython is an IPython extension that integrates Microsoft’s sqlcmd utility into Jupyter Notebooks. It allows users to execute native SQL queries inside Jupyter, without any extra Python code.

Key Features

✅ Seamless SQL Execution - Write SQL directly in notebook cells without additional Python code.

✅ Built-in Connection Management - Connect to Microsoft SQL Server dynamically.

✅ Multi-Statement Execution - Supports GO statements for executing multiple queries at once.

✅ Variable Substitution - Pass Python variables directly into SQL queries.

✅ Debugging Support - Use --debug to analyze query execution details.

Installation and Setup

Find the executable notebook here.

Step 1: Install the Extension

First, install the required package:

pip install ipython-sqlcmd python-dotenv

Step 2: Load the Extension

In your Jupyter Notebook, load the extension using:

%load_ext sqlcmd

This enables the %sqlcmd magic command inside Jupyter.

Step 3: Connect to SQL Server

To connect to a SQL Server instance, use:

%sqlcmd master --server=localhost --username=sa --password={os.getenv('SSMS_PASSWORD')} --encrypt --trust-certificate

You can replace localhost and credentials with your own connection details.

Running SQL Queries in Jupyter

Simple Query

Once connected, you can execute SQL commands inside a notebook cell:

%%sqlcmd
SELECT TOP 10 * 
FROM sys.tables 
ORDER BY name

This fetches the top 10 tables from the system catalog, just like in SSMS.

Creating and Populating Tables

Creating and inserting data is straightforward. Let’s create a table and insert some values:

%%sqlcmd
CREATE TABLE TestSpaces (
    ID int,
    Description varchar(100),
    Code varchar(20)
);

INSERT INTO TestSpaces (ID, Description, Code) 
VALUES 
    (1, 'This has spaces', 'A1'),
    (2, 'Another spaced value', 'B2'),
    (3, 'No spaces', 'C3');

SELECT * FROM TestSpaces;

This will create the table, insert some values, and return the data in a single execution.

Using Python Variables Inside Queries

You can use Python variables to dynamically modify your SQL queries:

table_name = "sys.tables"
limit = 5

%%sqlcmd
SELECT TOP $limit * 
FROM $table_name 
ORDER BY name

The $limit and $table_name placeholders are automatically replaced with the Python variables before execution.

Executing External SQL Scripts

SQL Command Magic also supports executing external SQL files, making it useful for database migrations or schema setup:

%%sqlcmd
EXECUTE_SQL_FILE '../src/tests/empty.sql'

This will run all SQL commands inside empty.sql.

Debugging Queries

To troubleshoot execution issues, enable debug mode:

%%sqlcmd --debug
SELECT @@VERSION AS SQLServerVersion

This outputs detailed execution logs, showing how the query was processed.

Running Multiple SQL Batches

Unlike standard SQL execution in Jupyter, SQL Command Magic fully supports multi-statement execution using GO:

%%sqlcmd
SELECT DB_NAME() AS CurrentDatabase
GO
SELECT @@SERVERNAME AS ServerName

Each query batch executes separately, just like in SSMS.

Conclusion

SQL Command Magic for IPython is a simple yet powerful tool for running SQL queries inside Jupyter Notebooks. It removes unnecessary Python boilerplate, enables multi-statement execution, and integrates seamlessly with Microsoft SQL Server.

Key Benefits

✅ Reduces Boilerplate – No need to write extra Python code for database connections.

✅ More Natural SQL Workflow – Execute queries just like in SSMS.

✅ Advanced Features – Supports GO statements, variable substitution, and script execution.

If you frequently run SQL queries in Jupyter, this extension is a game changer.

Get Started

pip install ipython-sqlcmd python-dotenv

Try it out and let me know your thoughts!