Combining both SQL and Python 💽 x 🐍

DataBites #15

Hey everyone!

It's Josep, back with you for another week! 👋🏻

I'm writing from Amsterdam, down in the Zuid area, where I'm helping my brother get his new place ready to call home!

Today, stay with me for 6 minutes —trust me, it’ll be worth your time!

In one of my finest moments… covered in old clothes and painting walls like a pro 🤡

The most important new of the week?

This weekend, I’m finally moving into my new apartment in Rotterdam! I'll be back to my regular routine of sending one (or maybe even more) updates each week. Get ready!

Now that we’re caught up, let’s get into the important stuff! 👨🏻‍💻

After emphasizing the importance of learning SQL—and recently covering you should get started with Python—today, I want to share a quick tip on a library that brings the best of both worlds: PandasQL.

This might be just what some of you need!

Before we dive in, I recommend checking out my previous posts if you haven’t already. They’ll give you a solid foundation!

You should start learning Python NOW🐍

Getting started with SQL 🔢

Combining both SQL and Python

SQL, or Structured Query Language, has long been the go-to for data management, yet sometimes it falls short and you need the power and flexibility of Python.

Python, on the other hand, shines when it comes to accessing, extracting, wrangling, and exploring data from relational databases. One of Python’s most popular open-source libraries, Pandas, is specifically built for data manipulation and analysis.

In this brief issue, we’ll dive into how and when SQL functionality can be seamlessly integrated within the Pandas framework—and where it has its limitations.

So… the main question you might be wondering right now is…

WHY USING BOTH?

The reason lies in readability and familiarity. In certain cases, especially within complex workflows, SQL queries can be much clearer and easier to read than the equivalent Pandas code. This is especially true for those who started their data journey with SQL before transitioning to Pandas.

Furthermore, since most data originates from databases, SQL—being the native language of these systems—has a natural advantage. This is why many data professionals, especially data scientists, choose to integrate both SQL and Python (specifically, Pandas) within their data pipelines to leverage the strengths of each.

To see this readability in action, let’s work with a Pokémon Gen 1 Pokédex CSV file as an example.

Imagine we want to sort the DataFrame by the "Total" column in ascending order and display the top 5 entries. Let’s compare how to perform this action in both Pandas and SQL.

Using Python Pandas: 

data[["#", "Name", "Total"]].sort_values(by="Total", ascending=True).head(5)

Using SQL:

SELECT 
     "#", 
     Name, 
     Total
FROM data
ORDER  BY Total
LIMIT 5

You see how different both are right?

But… how can we combine both languages within our working environment with Python?

The solution is using PandaSQL!

USING PANDASQL

Pandas is a powerful open-source data analysis and manipulation python library. pandasql allows the use of SQL syntax to query Pandas DataFrames. 

For people new to Pandas, pandasql tries to make data manipulation and cleanup more familiar. You can use pandasql to query Panda data frames using SQL syntax. 

So let’s try to get started…

#1. SETTING UP PANDASQL

First, we need to install pandasql:

! pip install pandasql

Then (and just as always!!), we import the required packages

from pandasql import sqldf

Here, we directly imported the sqldf function from pandasql, which is essentially the library's core feature. As the name suggests, sqldf allows you to query DataFrames using SQL syntax. 

sqldf(query_string, env=None)

In this context, query_string is a required parameter that accepts a SQL query in string format. The env parameter, optional and seldom used, can be set to either locals() or globals(), enabling sqldf to access variables from the specified scope in your Python environment.

Beyond this function, pandasql also includes two basic built-in datasets that can be loaded with the straightforward functions load_births() and load_meat(). So you can have some dummy data to play with!

So now, if we want to execute the previous SQL query within our python Jupyter Notebook, it would be something like follows: 

from pandasql import sqldf
import pandas as pd

sqldf('''
     SELECT "#", Name, Total
     FROM data
     ORDER  BY Total
     LIMIT 5''')

The sqldf function returns the result of a query as a pandas dataframe.

#2. WHEN SHOULD WE USE IT

The pandasql library enables data manipulation using SQL's Data Query Language (DQL), providing a familiar, SQL-based approach to interact with data in Pandas DataFrames. 

With pandasql, you can execute queries directly on your dataset, allowing for efficient data retrieval, filtering, sorting, grouping, joining, and aggregation. 

Additionally, it supports performing mathematical and logical operations, making it a powerful tool for SQL-savvy users working with data in Python.

#3. WHEN SHOULD WE NOT USE IT

pandasql is limited to SQL’s Data Query Language (DQL) subset, meaning it does not support modifying tables or data—actions like UPDATE, INSERT, or DELETE are not available. 

Additionally, since pandasql relies on SQL syntax, specifically SQLite, it’s essential to be mindful of SQLite-specific quirks that may affect query behavior.

Final Conclusions

By comparing these approaches, you can see that pandasql is helpful for SQL-native users or scenarios with complex queries, while native Pandas code can be more Pythonic and integrated for those accustomed to working in Python.

You can check some comparisons of using both libraries in Python in the following Jupyter Notebook.

Still with me? 🧐

As fellow data enthusiasts, I’m sure you’d be eager to help me shape some impactful KPIs and take this newsletter to the next level!

So here’s how you can help:

👉🏻 I want this newsletter to be truly valuable for you, so please share your feedback!

Additionally, you can let me know any preference for future content or any idea you think might add more value to you!

---

Before you go, tap the 💚 button at the bottom of this email to show your support—it really helps and means a lot!

---

My latest articles 📝

• Top 5 Free Machine Learning Courses to Level Up Your Skills in KDnuggets.

• The Transformers Architecture - What’s the magic behind LLMs? in AIgents.

---

Nice articles (my weekly favs)! ♥

• Python Packages for Data Cleaning by

  Cornellius Yudha Wijaya

• Optimize RAG with Reranking by

  David Andrés

---

My Favorite Resources! 👨🏻‍💻

• I strongly recommend following AIgents roadmaps if you want to become a full Data Scientist, ML Engineer, or Data Engineer,

• Want to enhance your coding workflow? You better check pieces for developers.

• Understand ML models following my MLBasics series!

• You can follow the Data Engineer Zoomcap on GitHub to become a fully proficient Data Engineer.

• Want to learn GCP? You can follow The Cloud Girl and learn using her intuitive illustrations!

---

Want to get more of my content? 🙋🏻‍♂️

Reach me on:

• LinkedIn, X (Twitter), or Threads to get daily posts about Data Science.

• My Medium Blog to learn more about Data Science, Machine Learning, and AI.

• Just email me at rfeers@gmail.com for any inquiries or to ask for help! 🤓

---

Remember now that DataBites has an official X (Twitter) account and LinkedIn page. Follow us there to stay updated and help spread the word! 🙌🏻