Hello Readers, Are you a programmer like me, who have who have mastered SQL over the years and then ventured into the world of Python?? As per the TIOBE [The Importance Of Being Earnest ] and PYPL [PopularitY of Programming Language Index], Python is ranked as #1 most popular programming language based on the number of skilled engineers world-wide, courses and third party vendors and how often language tutorials are searched on Google.
While I have been a VBA/SQL guy 🧰 for over a decade and I used to resist learning Python, the industry trends and data science boom pulled me like a black hole. Once you get in, there is no way out! In the end, I fell in love 💖 with Python but at the same time I could never underestimate the power of VBA and SQL for data analytics and data science. One of the initial challenges programmers like me face are why should I relearn techniques in Python when I already know how to do the same by SQL. Some practitioners may get some help from Python libraries like sqlite or pandasql but honestly, you will see that at times they are hard to implement and increases the complexity level. As a result, you realize if there was an easy way out to just learn and convert your SQL commands to Python.
Relax, I have got you covered! Here is a Top 25 SQL commands to Python Cheat Sheet ✍️which has been guaranteed and tested OK by me. You can easily learn and convert your most common SQL commands [JOINS, UPDATE, REPLACE] to Python by leveraging this cheat sheet. If you wish, you can also download the sample raw data files and scripts used for the learning, validation and demonstration purposes.
Press Ctrl + D to Bookmark this page” for references”
Top 25 SQL commands to Python Cheat Sheet by Jayant Kodwani
| # | Type of Action | SQL | Python using Pandas |
| 1 | Select all rows from Table | SELECT * FROM [Table1] | print[df] |
| 2 | Select Top 3 rows from Table | SELECT TOP 3 * FROM [Table1] | print[df.head[3]] |
| 3 | Select all rows and sort by Age in Ascending order [ensure null values appear first] | SELECT * FROM [Table1] ORDER BY Age | print[df.sort_values[by=[‘Age’], ascending=True, na_position=’first’]] |
| 4 | Select all rows and sort by Age in Descending order [ensure null values appear last] | SELECT * FROM [Table1] ORDER BY Age DESC | print[df.sort_values[by=[‘Age’], ascending=False, na_position=’last’]] |
| 5 | Select DISTINCT Column values | SELECT DISTINCT [Department] FROM [Table1] | print[df.Department.unique[]] |
| 6 | Select count of rows | SELECT Count[*] FROM [Table1] | print[len[df]] |
| 7 | Select SUM of any column | SELECT sum[Salary] FROM [Table1] | print[df[‘Salary’].sum[]] |
| 8 | Select all rows where specific column values are NULL | SELECT * FROM [Table1] WHERE [Age] IS NULL | print[df[df[‘Age’].isnull[]]] |
| 9 | Select all rows where specific column values are NOT NULL | SELECT * FROM [Table1] WHERE [Age] IS NOT NULL | print[df[~df[‘Age’].isnull[]]] |
| 10 | Select all rows where specific column values > numeric value i.e. 35 | SELECT * FROM [Table1] WHERE [Age] > 35 | print[df[df.Age >= 35]] |
| 11 | Select all rows where specific column values is conditioned by AND operator | SELECT * FROM [Table1] WHERE [Age] >=30 AND [Age] = 30] & [df.Age 0 AND Salary 20000 AND Salary 40000 THEN ‘High’ ELSE ‘None’ END AS ‘Category’ FROM Table1 | df[‘Category’] = df.apply[lambda row: ‘High’ if row[‘Salary’] > 40000 else [‘Medium’ if row[‘Salary’] > 20000 and row[‘Salary’] 0 and row[‘Salary’] Chủ Đề |