Pandasql -The Best Way to Run SQL Queries in Python

Nilabh Nishchhal 22 Dec, 2023 • 7 min read

Introduction

Pandas have evolved remarkably in data handling, yet some still swear by SQL’s magic. Good news! With Pandassql, you can use SQL-like tricks right in Python, especially in Jupyter Notebooks. Picture querying pandas DataFrames using just SQL syntax. Ready for the adventure of blending SQL with Pandas? And guess what? No SQL servers needed! 😎

This article was published as a part of the Data Science Blogathon.

What is Pandasql?

The saviour is python’s library, pandasql.

As the libraries’ documentation mentions:

pandasql allows you to query pandas DataFrames using SQL syntax. It works similarly to sqldf in Rpandasql seeks to provide a more familiar way of manipulating and cleaning data for people new to Python or pandas.

How to Install Panadsql?

You need to install the Python’s Library, pandasql first. It’s very simple to install. Use any of the below two methods, both use PIP installation.

  • Open the terminal and run
    pip install -U pandasql
  • Open your Jupyter Notebook and in any cell run
    !pip install -U pandasql

How to Use PandaSQL?

1.Install Pandas and pandasql:

pip install pandas pandasql

2. Import Libraries:

import pandas as pd
from pandasql import sqldf

3. Create DataFrame:

df = pd.DataFrame({'Name': ['Alice', 'Bob', 'Charlie'],
                   'Age': [25, 30, 22],
                   'City': ['New York', 'SF', 'LA']})

4. Use SQL-like Queries:

pysql = sqldf("SELECT * FROM df WHERE Age > 25")

5. Print or Use Resulting DataFrame:

print(pysql)

Why Should One Use SQL in Pandas?

Using SQL in Pandas can be advantageous for various data manipulation and analysis tasks, especially when you are already familiar with SQL or when your data processing requirements align with SQL’s capabilities. Let’s explore some examples to illustrate why you might want to use SQL in Pandas:

Example 1: Filtering Data

Suppose you have a Pandas DataFrame containing sales data, and you want to filter it to select only the rows where the sales amount is greater than $1,000:

pythonCopy codeimport pandas as pd
from pandasql import sqldf

# Create a sample DataFrame
data = {'Product': ['A', 'B', 'C', 'D'],
        'Sales': [800, 1200, 950, 1400]}
df = pd.DataFrame(data)

# Using SQL in Pandas
pysqldf = sqldf("SELECT * FROM df WHERE Sales > 1000")

print(pysqldf)

In this example, using SQL-like syntax makes the filtering condition clear and concise.

Example 2: Aggregation

Suppose you have a dataset of customer orders, and you want to calculate the total sales amount for each customer using Pandas:

pythonCopy codeimport pandas as pd
from pandasql import sqldf

# Create a sample DataFrame
data = {'Customer': ['Alice', 'Bob', 'Alice', 'David'],
        'OrderAmount': [100, 150, 200, 120]}
df = pd.DataFrame(data)

# Using SQL in Pandas to calculate total sales per customer
pysqldf = sqldf("SELECT Customer, SUM(OrderAmount) AS TotalSales FROM df GROUP BY Customer")

print(pysqldf)

SQL-like syntax simplifies the aggregation task, allowing you to calculate sums, averages, and other aggregations easily.

Example 3: Joining DataFrames

Suppose you have two DataFrames, one containing customer information and the other containing order information. You want to perform an inner join to combine them based on a common column, such as customer ID:

pythonCopy codeimport pandas as pd
from pandasql import sqldf

# Create sample DataFrames
customers = pd.DataFrame({'CustomerID': [1, 2, 3],
                          'CustomerName': ['Alice', 'Bob', 'Charlie']})

orders = pd.DataFrame({'OrderID': [101, 102, 103],
                       'CustomerID': [1, 2, 1],
                       'OrderAmount': [100, 150, 200]})

# Using SQL in Pandas to perform an inner join
pysqldf = sqldf("SELECT c.CustomerName, o.OrderAmount FROM customers c INNER JOIN orders o ON c.CustomerID = o.CustomerID")

print(pysqldf)

SQL-like syntax provides a clear and familiar way to express join operations.

Example 4: Complex Filtering and Subsetting

In scenarios where you need to apply complex filtering conditions or subsetting operations, SQL can simplify the task. For example, you can use SQL’s WHERE clause with logical operators:

pythonCopy codeimport pandas as pd
from pandasql import sqldf

# Create a sample DataFrame
data = {'Product': ['A', 'B', 'C', 'D'],
        'Sales': [800, 1200, 950, 1400],
        'Region': ['North', 'South', 'North', 'West']}
df = pd.DataFrame(data)

# Using SQL in Pandas for complex filtering
pysqldf = sqldf("SELECT * FROM df WHERE Sales > 1000 AND Region = 'North'")

print(pysqldf)

This approach is concise and readable when dealing with intricate conditions.

Basics

There is this one function that is used the most from this library. Its the main function sqldf. sqldf takes two parameters.

A SQL query in string format
A set of session/environment variables (globals() or locals())
It becomes tedious to specify globals() or locals(), hence whenever you import the library, run the following helper function along with. This will make things simple going forward.

from pandasql import sqldf
mysql = lambda q: sqldf(q, globals())

Syntax

Many variants of SQL are in use, and their syntaxes vary a little. Here pandasql uses the SQLite syntax. Most of the standard SQL language SQLite understands. However, it adds a few features of its own while at the same time it does omit some features. Click Here to read the document that attempts to describe what parts of the SQL language SQLite do and does not support.

pandasql automatically detects any pandas DataFrame. You can call them or query them by their name like you would have done with a SQL table.

We are going to use any one of these two basic code samples.

from pandasql import sqldf

or

from pandasql import sqldf

Import libraries and Data

For this article, we will use the data from the pandasql library itself. Let us import the dependencies and the data.

In [1]:

Python Code:

Read Data using SQL Query

We will read the first 5 rows of data, for the meat and births data frames using SQL. The result shall be similar to what we get from using .head()

In [4]:

# specify globals() or locals() using the following helper function
​mysql = lambda q: sqldf(q, globals())
mysql("SELECT * FROM meat LIMIT 5;")

Out[4]:

 datebeefvealporklamb_and_muttonbroilersother_chickenturkey
01944-01-01 00:00:00.000000751.085.01280.089.0NoneNoneNone
11944-02-01 00:00:00.000000713.077.01169.072.0NoneNoneNone
21944-03-01 00:00:00.000000741.090.01128.075.0NoneNoneNone
31944-04-01 00:00:00.000000650.089.0978.066.0NoneNoneNone
41944-05-01 00:00:00.000000681.0106.01029.078.0NoneNoneNone

In [5]:

mysql("SELECT * FROM births LIMIT 5;")

Out[5]:

 datebirths
01975-01-01 00:00:00.000000265775
11975-02-01 00:00:00.000000241045
21975-03-01 00:00:00.000000268849
31975-04-01 00:00:00.000000247455
41975-05-01 00:00:00.000000254545

Join (or merge) DataFrames using SQL Queries

Joining tables is one of the most common tasks being performed by SQL. Understandably so, as the relational databases have data segregated in separate tables. Hence, SQL users are pretty used to using join() tables in SQL. We can use the power of SQL JOIN here with pandas DataFrame.

In [6]:

query = '''
SELECT m.date, m.beef, m.veal, m.pork, b.births
FROM meat AS m
INNER JOIN
births AS b
ON m.date = b.date;
'''
​
mysql(query)

Out[6]:

 datebeefvealporkbirths
01975-01-01 00:00:00.0000002106.059.01114.0265775
11975-02-01 00:00:00.0000001845.050.0954.0241045
21975-03-01 00:00:00.0000001891.057.0976.0268849
31975-04-01 00:00:00.0000001895.060.01100.0247455
41975-05-01 00:00:00.0000001849.059.0934.0254545
4022012-07-01 00:00:00.0000002200.89.51721.8368450
4032012-08-01 00:00:00.0000002367.510.11997.9359554
4042012-09-01 00:00:00.0000002016.08.81911.0361922
4052012-10-01 00:00:00.0000002343.710.32210.4347625
4062012-11-01 00:00:00.0000002206.610.12078.7320195

407 rows × 5 columns

GROUP BY using SQL

The data of meat production is month-wise. We want to see the beef production per year. For that we need to groupby() and aggregate. We can do this using the SQL GROUP BY function.

In [7]:

query = '''SELECT
            strftime('%Y', date) as year
           , SUM(beef) as beef_total
           FROM
              meat
           GROUP BY
              year
              LIMIT 5;
    '''
​
mysql(query)

Out[7]:

 yearbeef_total
019448801.0
119459936.0
219469010.0
3194710096.0
419488766.0

In the above code, we used SQL query to limit the number of rows for the grouped and aggregated table to 5 rows. But the output and the input, both are not SQL tables. They are pandas DataFrames. And this gives us the liberty to use Pandas functions and methods on the same.

Let us do the same operation, and this time the output shall be the first 10 rows. But the SQL query will give a full table and we will use pandas head() function to get the final output truncated to 10 rows.

In [8]:

query = '''SELECT
            strftime('%Y', date) as year
           , SUM(beef) as beef_total
           FROM
              meat
           GROUP BY
              year;
    '''
​
mysql(query).head(10)

Out[8]:

 yearbeef_total
019448801.0
119459936.0
219469010.0
3194710096.0
419488766.0
519499142.0
619509248.0
719518549.0
819529337.0
9195312055.0

UNION ALL to club multiple variables in SQL

We have beef, pork, and veal as meat types, in separate columns. Here we want all the production values in one column and the identifier in another column. We can use UNION ALL function from SQL to achieve this easily.

In [9]:

#executing union all statements
query = """
        SELECT
            date
            , 'beef' AS meat_type
            , beef AS value
        FROM meat
        UNION ALL
        SELECT
            date
            , 'veal' AS meat_type
            , veal AS value
        FROM meat
        UNION ALL
        SELECT
            date
            , 'pork' AS meat_type
            , pork AS value
        FROM meat
        UNION ALL
        SELECT
            date
            , 'lamb_and_mutton' AS meat_type
            , lamb_and_mutton AS value
        FROM meat
        ORDER BY 1
    """
​
mysql(query).head(10)

Out[9]:

 datemeat_typevalue
01944-01-01 00:00:00.000000beef751.0
11944-01-01 00:00:00.000000veal85.0
21944-01-01 00:00:00.000000pork1280.0
31944-01-01 00:00:00.000000lamb_and_mutton89.0
41944-02-01 00:00:00.000000beef713.0
51944-02-01 00:00:00.000000veal77.0
61944-02-01 00:00:00.000000pork1169.0
71944-02-01 00:00:00.000000lamb_and_mutton72.0
81944-03-01 00:00:00.000000beef741.0
91944-03-01 00:00:00.000000veal90.0

Nested Queries of SQL

In SQL, writing queries within another query is commonplace. The same kind of nesting of queries is possible here as well. We will create one table (or say DataFrame) and without assigning it any variable (or name), we will use that to create another table.

In [10]:

# use queries within queries
query = """
    SELECT
        m1.date
        , m1.beef
    FROM
        meat m1
    WHERE m1.date IN
        (SELECT
            date
        FROM meat
        WHERE
            beef >= broilers
        ORDER BY date)
"""
​
mysql(query)

Out[10]:

 datebeef
01960-01-01 00:00:00.0000001196.0
11960-02-01 00:00:00.0000001089.0
21960-03-01 00:00:00.0000001201.0
31960-04-01 00:00:00.0000001066.0
41960-05-01 00:00:00.0000001202.0
4161995-08-01 00:00:00.0000002316.0
4171995-09-01 00:00:00.0000002220.0
4181995-11-01 00:00:00.0000002098.0
4191996-05-01 00:00:00.0000002302.0
4201996-06-01 00:00:00.0000002186.0
421 rows × 2 columns

Conclusion

In this article, we saw that how easily we can use SQL queries to operate upon the DataFrames. This gives us a unique opportunity. This weapon can be a potent one in any Data Scientist’s arsenal, who knows SQL and Python, both.

They both are powerful languages and have their respective strengths and weaknesses. Using the method shown in this article, or in other words, using the pandasql library and sqldf function, we can use the best and most efficient method to manipulate data, well within the python environment and even Jupyter Notebook. This is music to my ears. I hope you enjoyed the song too 🤓.

In this article, you saw how to use SQL queries inside python. But if you want to connect the two most powerful workhorses of the Data Science world, SQL and Python. This is not the end, but only the first step towards getting the “Best of Both Worlds”.

Resources

Now you can start using Python to work upon your data which rests in SQL Databases. In able to connect to your SQL databases, go thru my article How to Access & Use SQL Database with pyodbc in Python. Once you brought it as DataFrame, then all the operations are usual Pandas operations or SQL queries being operated on Pandas DataFrame as you saw in this article.

Apart from the function of SQL shown in this article, many other popular SQL functions are easily implementable in Python. Read 15 Pandas functions to replicate basic SQL Queries in Python for learning how to do that.

Frequently Asked Questions

Q1. What is PandaSQL?

A. PandaSQL is a Python package that allows SQL-like queries to be performed on pandas DataFrames, enabling seamless data manipulation within the Python environment.

Q2. Is Pandasql faster than pandas?

A. Pandasql might be faster for specific operations that leverage SQL’s optimization, but the speed difference depends on the task and dataset size.

Q3. How to install PandaSQL package in Python?

A. Install Pandasql using pip: pip install pandasql. Then, import it in your Python script or Jupyter Notebook to use SQL-like queries with pandas DataFrames.

Q4. Is pandas better than SQL?

A. Pandas and SQL serve different purposes. Pandas are great for data analysis and manipulation within Python, while SQL is essential for efficiently managing databases and querying large datasets. Both have their strengths.

The media shown in this article are not owned by Analytics Vidhya and are used at the Author’s discretion.

Nilabh Nishchhal 22 Dec 2023

Frequently Asked Questions

Lorem ipsum dolor sit amet, consectetur adipiscing elit,

Responses From Readers

Clear

Manpreet Singh Dhillon
Manpreet Singh Dhillon 22 Jul, 2021

Explained very briefly. A very unique and easy way to approach the topic.

Reyes Ponce
Reyes Ponce 15 Oct, 2022

Pandasql current version is from 2016 and the last commit on github was in 2017. Looks like it's been abandoned.

  • [tta_listen_btn class="listen"]