Python Magic for SQL
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.
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
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 ISNULLb) 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 ISNULLThen 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 ISNULLAlternatively:
#%%
data = "Unknown"
%sql UPDATE Fam SET Profession= :data WHERE Profession ISNULL6- 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!