SQL Magic with SQLite

What is SQL magic?

SQL magic is a technique that enables you to execute SQL queries directly within the Python environment. It provides a convenient way to interact with databases without switching between different tools or environments.

Syntax
To communicate with SQL Databases from within a JupyterLab notebook, you can use the SQL "magic" provided by the ipython-sql extension. "Magic" commands in JupyterLab start with "%". Below, we'll use the %load_ext sql magic to load the ipython-sql extension. In the lab environment provided in the course, the ipython-sql extension and the ibm_db_sa driver are already installed.

%load_ext sql

Here, we'll create and connect to a new SQLite database called SQLiteMagic. The syntax for connecting to SQL using SQLite is %sql sqlite://DatabaseName, where DatabaseName is your .db file.

%sql sqlite:///SQLiteMagic.db

For this blog, we'll consider the SQLite3 database.

import csv, sqlite3

con = sqlite3.connect("SQLiteMagic.db")
cur = con.cursor()

%load_ext sql
%sql sqlite:///SQLiteMagic.db

For convenience, %%sql (two %'s instead of one) at the top of a cell indicates that the entire cell should be treated as SQL. Let's use this to create a table and fill it with some test data.

%%sql

CREATE TABLE SPORT_ATHLETES (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    nationality VARCHAR(50),
    sport VARCHAR(50)
);

INSERT INTO SPORT_ATHLETES (first_name, last_name, nationality, sport)
VALUES
('Michael', 'Phelps', 'USA', 'Swimming'),
('Usain', 'Bolt', 'Jamaica', 'Athletics'),
... (additional sample data)

Using Python Variables in SQL Statements

You can use Python variables in SQL statements by adding a ":" prefix to your Python variable names. For example, if you have a Python variable country with a value of "Canada", you can use it in a SQL query.

nationality = "Canada"
%sql SELECT * FROM SPORTS_ATHLETES WHERE nationality = :nationality

Assigning Results to Python Variables

You can use the normal Python assignment syntax to assign the results of your queries to Python variables.

Converting Query Results to DataFrames

Easily convert a SQL query result to a pandas DataFrame using the DataFrame() method. DataFrame objects are more versatile than SQL query result objects.

athletes_number = %sql SELECT COUNT(*) AS "Frequency" FROM SPORTS_ATHLETES GROUP BY nationality;
dataframe = athletes_number.DataFrame()

Benefits of Using SQL Magic

Using SQL magic offers several advantages:

• Integrated environment: Work with SQL and Python within the same environment, eliminating the need to switch between tools.
• Simplified workflow: It simplifies your data analysis workflow by making it easier to execute SQL queries directly within your Python code.
• Enhanced productivity: It can enhance your productivity by reducing the time and effort spent managing different tools and environments.