Skip to content

Python Magic for SQL

Python Tutorials

Introduction

In this SQL tutorial we will explain and demonstrate a method to run sql scripts from a Python IDE like Spyder or Jupyter Notebook using Python magic.

Holy Python is reader-supported. When you buy through links on our site, we may earn an affiliate commission.

Editor’s Pick

Special Offer up to 75% off

Udacity SQL Course

You can now enroll in Udacity SQL Nanodegree program with 75% off and enable lucrative career opportunities.

5-star course rating
5/5

*Financial aid available

1- Preparation

To ensure sql magic works properly we need to take a couple of steps first.

a) ipython-sql Installation

ipython-sql Installation

To be able to use Python magic with sql and execute sql queries from a Python IDE we need to install ipython-sql library first. ipython-sql is the library that allows sql magic.

You can simply installing it running this code from Anaconda Command Prompt or another command console you are using.

pip install ipython-sql

You can see this article if you need more help installing python libraries:

b) ipython Update

Also, you may have to upgrade your ipython library, otherwise sql magic won’t run properly. Ipython is a library with rich interactive features that Jupyter Notebook uses by deafult. Although you may not observe it normally, Ipython also runs with Spyder IDE. The script to update ipython is as below:

pip install --upgrade ipython

c) creating code cells in Spyder IDE

You can create code cells using  #%% in Spyder IDE. It’s a lesser known trick that works really well if you’d like to do data science or development from your Spyder console instead of Jupyter.

You can run each code cell with Ctrl+Enter

Or you can use Shift+Enter which will run the current cell and jump to the next cell.

So, it really helps emulate a Jupyter environment and brings extra functionality to Spyder IDE.

2- Loading External SQL Module

Now, we can load sql module using Python magic and load_ext like below:

%load_ext sql

You only have to run this once and not in each cell. It will ensure external sql module is loaded and ready for use.

3- Connecting to Database

At this point we’re almost ready to run sql scripts from Python code cells in either Spyder or Jupyter but we need to establish a connection to the database first. This can be done in several ways depending on your database choice and file or server location. Here are some examples:

sqlite:// –> This will establish a temp connection with sqlite.
sqlite:////Users/ABC/Desktop/yourdatabase.sqlite –> This can be used to specify a database in a specific location. Also if the database file doesn’t exist it will be initiated.
sqlite:///yourdatabase.sqlite –> This can be used to connect to a database file in the same working directory.

Alternative Connection Options:

IBM DB2 Cloud Database Connection Example:

‘%sql ibm_db_sa://etd48682:S5d4Wg6s3%6211n@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/BLUDB

MySQL Database Connection Example:

mysql://user:pwd@localhost/database_name

Oracle Database Connection Example:
oracle://user:pwd@127.0.0.1:1962/database_name

PostgreSQL Database Connection Examples:

postgres://name:pwd@localhost:6259/database_name
postgres://localhost:6259/database_name

 

at this point sql magic is connected to the database and ready to use. It’s an impressive solution to using sql from Ipython console in Jupyter Notebook. What most people might not know is that Ipython also runs in Spyder IDE. Spyder is a great IDE that’s used by scientists, researchers and data scientists as well.


In this tutorial we will demonstrate python magic for sql in Spyder but you can do the exact same thing in Jupyter as well.

4- SQL Magic

SQL Magic can be done in two ways:

Cell magic: this magic will turn your whole code cell to an sql script

Line magic: this magic will turn only one line of your code cell to an sql script.

a) SQL Cell Magic

Cell magic applies to the whole cell. It’s also utilized by double percentage signs as below. All you have to type is %%sql and the rest of the cell will be treated as an sql script.

#%%
%%sql

CREATE TABLE Fam(Name TEXT, Age INTEGER, Profession TEXT, Major TEXT);
INSERT INTO Fam (Name, Age) VALUES('Pam', 50);
INSERT INTO Fam (Name, Age) VALUES('Miranda', 32);
INSERT INTO Fam (Name, Age) VALUES('Pascal', 45);
INSERT INTO Fam (Name, Age) VALUES('Dave', 12);
INSERT INTO Fam (Name, Age) VALUES('Emmy', 23);

UPDATE Fam SET Profession='Unknown' WHERE Profession ISNULL

b) SQL Line Magic

Line magic is achieved by a single percentage sign. All you have to do is type %sql before your sql query and the rest of the line will be interpreted as an sql script. 

For example we can run this line magic to update column Profession with “Unknown” to replace it with NULL values.

#%%
%sql UPDATE Fam SET Profession='Unknown' WHERE Profession ISNULL

Then similarly, we can use one line magic %sql to retrieve data from the database with an sql query like below:

#%%
a = %sql SELECT * FROM Fam

print (a)

Here is how the output of that sql query looks like in Python Spyder console:

Done.
+---------+-----+------------+------+
|   Name  | Age | Profession | Major|
+---------+-----+------------+------+
|   Pam   |  50 |  Unknown   | NULL |
| Miranda |  32 |  Unknown   | NULL |
|  Pascal |  45 |  Unknown   | NULL |
|   Dave  |  12 |  Unknown   | NULL |
|   Emmy  |  23 |  Unknown   | NULL |
+---------+-----+------------+------+

Querying text based on starting characters

Internet Usage for countries that start with ‘be‘:

a = cur.execute('''SELECT * FROM Intuse WHERE country like "be%" 
                order by percentage desc limit 5''')
                
print(a)
('Bermuda', 60349.0, 61349, 197, '98.37%', 3)
('Belgium', 10021242.0, 11429336, 79, '87.68%', 30)
('Belarus', 7048231.0, 9468338, 92, '74.44%', 71)
('Belize', 176400.0, 374681, 174, '47.08%', 135)
('Benin', 1578008.0, 11175692, 80, '14.12%', 192)

5- Using Python Variables in SQL Script

You can also use a variable from Python in your SQL script using :var or {var} syntax. Code example below will use data variable which is defined in Python code and use it in the SQL query.

#%%

data = "Unknown"
%sql UPDATE Fam SET Profession= {data} WHERE Profession ISNULL

Alternatively:

#%%

data = "Unknown"
%sql UPDATE Fam SET Profession= :data WHERE Profession ISNULL

6- Assign multiple SQL lines to a Python variable

Using << operator, you can assign the results of a multi-line SQL query to a Python variable.

#%%
%%sql result << SELECT Date, transaction, price, no FROM Book
Order BY price DESC 
LIMIT 10
Done.
+------------+-------------+-------+-----+
| date | transaction | price | no |
+------------+-------------+-------+-----+
| 2020-09-30 | 39548529 | 75 | 296 |
| 2020-11-09 | 39548529 | 77 | 265 |
| 2020-11-25 | 39548529 | 62 | 259 |
| 2020-11-17 | 39548529 | 62 | 292 |
| 2020-11-12 | 39548529 | 60 | 249 |
| 2020-11-16 | 39548529 | 95 | 254 |
| 2020-11-10 | 39548529 | 80 | 256 |
| 2020-12-03 | 39548529 | 41 | 214 |
| 2020-11-02 | 39548529 | 56 | 244 |
| 2020-09-28 | 39548529 | 55 | 201 |
+------------+-------------+-------+-----+

7- Saving SQL Result as CSV File

Quite conveniently you can save the results of your query to an csv file with simply using .csv() method.

In the example below this is done in two consecutive cells in Spyder IDE. First cell retrieves data from sqlite database executing an sql query while the second cell exports the result to a csv file.

#%%
%%sql result << SELECT Date, transaction, price, no FROM Book
Order BY price DESC 
LIMIT 10
    
#%%
result.csv('Desktop/mydata.csv')

8- Listing Database Connections

You might also want to list the current database connections and their properties. 

l / –connections  will return a list of all the current database connections in Python IDE or Jupyter Notebook. Results will be a dictionary where keys will be the database locations and values will be their object names and memory addresses.

#%%
conn = %sql l / --connections

print(conn)
{'sqlite:////Users/ABC/Desktop/Book.sqlite': 
"sql.connection.Connection object at 0x0000022B78290848"}

9- Closing Database Connections

If you’d like to close any of these connections you can run the code below:

#%%
%sql -x / --close "connection name"

This option will be valid for MySQL connections, SQL Server Connections, PostgreSQL Connections or some Cloud Database Connections such as Amazon RDS, Google Cloud or IBM DB2. SQLite runs in a local file and it can’t be closed. To be able to end the connection you can restart your kernel.

Python SQL Magic Summary

If you are using sql queries a lot this method can be so useful to do that from the comfort of your Python IDE or Jupyter Notebook in a very practical way.

In this tutorial we have shared sql magic tips and showed how sql magic can be used from a Python IDE like Spyder or from Jupyter Notebook.

We have seen most needed knowledge regarding Python SQL magic such as: settings of ipython-sql library, how to make a database connection with sql magic, how to create code cells in Spyder IDE, how to turn Python code cells (or lines) to SQL scripts, how to list database connections with sql magic, how to close database connections with sql magic and how to export sql query results to csv files.

You can also use SQL with Python through more traditional methods explained in this Python SQLite tutorial:

SQL is a very useful skill that compliments Python programming and we hope you find our SQLite Tutorial Series useful.

Thanks for visiting!