As the wise men like Vivekanand expressed, there are many paths that men/women take to reach their destinations (be it search of God or Model Accuracy). And all of those who have learned data science and related streams will be able to relate with this, that these different paths start with different languages, different tools, and different expectations. During this long and arduous journey towards excellence, some start from SQL, and others learn Python. But as we all know by now, in long run knowing only one of these will not suffice.

SQL is necessary for talking to the database and Python is the de-facto leader in taking it all to the Machine Learning journey. This is my second article in this series to make people more conversant in both languages. More specifically, those who are better off with SQL and learning Python now, they will be happy to know that almost all the functions and operations performed in SQL can be replicated in Python. Some of them may even be more efficiently done in Python, as compared to SQL.

Before we can start with looking at the equivalent “SQL functions in Python, let us revise how we linked SQL databases to Python. You may go through my previous article, the first one in this series “How-to-Access-use-SQL-Database-with-pyodbc-in-Python” for detailed understanding. The code to start importing SQL Database data from MS SQL is as below.

Photo by Author

Import data from SQL to Python

Basic SQL Queries

We are going to deconstruct the most basic of the SQL queries and see how the same result can be achieved in Python. The queries which we will discuss in this article are

• SELECT column_name(s)
• FROM table_name
• WHERE condition
• GROUP BY column_name(s)
• HAVING condition
• ORDER BY column_name(s)

The methodology we are going to adopt is like this: We will write a SQL query, and then list some possible ways in which the same result can be achieved in Python. We have three tables in the database which we are going to use, and we have imported two of them as DataFrames in Python already. We will use one of these Data Frames (population) to understand these concepts.

The table State_Population is already imported and the DataFrame is named as population.

population.head()

Let us see how to replicate the SQL function in Python and get the same or similar results.

SELECT year FROM State_Population;

This SQL query will fetch the column(year) and all the rows from the state_population table. In Python, it can be achieved in the following ways. One thing to notice here is that when we select only one column, it gets converted to pandas series object from a pandas DataFrame object. We convert it back to DataFrame by using the DataFrame function.

2.  Call the DataFrame.ColumnName

SELECT population, year FROM State_Population;

This query will fetch two columns(population and year) and all the rows from the state_population table. In Python, it can be achieved in the following ways.

3.  Call the DataFrame with column names (Selecting)

Notice the names of the columns as a list, inside the indexing brackets [].

4.  Use the pandas .loc() method

The syntax for loc method is df.loc([row names], [column names]). If instead of the list of names, only “:” is passed, it means to consider all. So df.loc(: , [column names]) means fetch all rows for the given column names.

The DataFrame above is the output from all the above codes. Different methods, same output.

SELECT column_name(s)

FROM table_name

WHERE condition

SELECT * FROM State_Population WHERE year = 2010;

This query will fetch all the columns and only those rows from the state_population table where the year column has a value equal to 2010. In Python, it can be achieved in the following ways.

5.  Use Python’s Slicing method

6.  Use pandas .loc() method

In [8]:

population.loc[population.year == 2010,:]

Out[8]:

7.  Use pandas .query() method

Notice that the input for df.query() is always a string.

8.  Use pandas lambda function

Notice that the apply method is used, to apply the lambda function to every element of the column. Its result is then fed inside indexing brackets to slice the original DataFrame.

The DataFrame above is the output from all the above codes. Different methods, same output.

SELECT state/region, population, year FROM State_Population WHERE year = 2010 or 2012 and ages = under18;

This query will fetch these three columns(state/region, population, year) and only those rows from the state_population table where the year column has a value equal to 2010 or 2012 and the ages columns have a value equal to “under18”. In Python, it can be achieved in the following ways.

9.  Use Pythons indexing and slicing

10.  Use pandas .loc() method

11.  Use pandas .query() method

Notice that the input for df.query() is always a string.

12.  Use lambda function

The DataFrame above is the output from all the above codes. Different methods, same output.

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

HAVING condition

SELECT * FROM State_Population WHERE ages = total GROUP BY state/region HAVING AVG(population) > 10000000;

The Group By functions of SQL and Pandas look the same on the surface, but pandas groupby is far more capable and efficient, especially for more complex operations. To implement the above operation from SQL in python, let us see the pandas groupby function up close.

It is possible to group by using one or more columns. For one column, just pass the column name, and for more than one column, pass the names as a list.

The output is a groupby object. The output shows that the grouping has been done, and the groupby function has done its job. But as we have not been told to aggregate by which function, the output is not in form of DataFrame. So let’s do that now.

We can assign this groupby object to a variable, and then use that variable for further operations.

Now let’s replicate the SQL Query. To add the HAVING function as well, we need to use the groupby and then filtering on the condition. The python implementation of the above SQL code is as below.

13.  groupby and aggregate in Pandas

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

HAVING condition

ORDER BY column_name(s)

SELECT * FROM State_Population WHERE ages = total GROUP BY state/region HAVING AVG(population) > 10000000 ORDER BY population;

The order by in SQL is used to sort the table in the given order. In the above SQL code, the table needs to be ordered in ascending order (default). This task can be accomplished by using the pandas sort_values() method.

14.  Order by using sort_values() in Python

The sort is done in ascending order by default. To change that, ascending = False shall be used.

Bonus: Sort by Multiple columns

Pandas give the functionality to sort by multiple columns. Not only that, You can choose which columns to sort in ascending order and which one in descending order. Let us groupby and sort our population data set on. We will group them under state and year, and sort them on year and population.

15.  sort_values() on more than one columns

# Grouping by and the grouped table
grouped = population.groupby(by = ['state/region', 'year']).mean()
grouped

16 Conclusion:

You must have wondered how organizations manage their huge databases. They most certainly do not keep it in Excel or other spreadsheet formats. The real-life business databases are maintained in a relational database system, which is created and accessed most commonly using SQL. Hence knowing SQL is a necessary tool for any data scientist. But SQL is more powerful than just a data picking tool. It is capable of many data wrangling and data manipulation tasks. But so is Python.

Now no single language is sufficient to complete all the tasks, with operational efficiency. Hence a deep understanding of both SQL and Python will help you chose which one to use for which task.

If you want to just select, filter, and basic operations on data, you can do that efficiently in SQL. But if there’s a need for complex grouping operations, and more data manipulation, Pandas in Python would be a more apt choice.

There are many benefits in using multiple data analysis languages, as you can customize and use a hybrid approach well suited for your ever-evolving needs.

To see in detail how to connect Python with SQL or SQL server, read How-to-Access-use-SQL-Database-with-pyodbc-in-Python.

The implied learning in this article was, that you can use Python to do things that you thought were only possible using SQL. There may or may not be straight forward solution to things, but if you are inclined to find it, there are enough resources at your disposal to find a way out. You can look at the mix and match the learning from my book, PYTHON MADE EASY – Step by Step Guide to Programming and Data Analysis using Python for Beginners and Intermediate Level.

About the Author: I am Nilabh Nishchhal. I like making seemingly difficult topics easy and write about them. Check out more at https://www.authornilabh.com/. My attempt to make Python easy and Accessible to all is “Python Made Easy”.

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