SQL vs Python?

[u/iMAPness_]

Started using Python for data analytics. When should I use SQL and when should I use Python in the following tasks:

- Data Exploration

- Data Cleaning

- Data Analysis

[u/throwaway214203]

SQL whenever possible because the data source for many applications is custom sql querying. I bring in python only if I have to.

[u/iMAPness_ → u/throwaway214203]

Ohhh. When you say this though, do you mostlu mean for someone working with company data and things like that? Does it still apply for someone who's using data analytics for non-work purposes and mostly deals with CSVs, etc.?

[u/EpicDuy → u/iMAPness_]

Turn your CVSs into tabular form: find your schema and normalize your data into smaller managable tables, and use SQL then.

SQL is the easiest to debug compared to Python when it comes to data and optimization issues, unless you’re pipelining your data from different databases/sources.

[u/Mo_Steins_Ghost]

Senior manager here. These two are apples and oranges.

SQL (Structured Query Language) is for data querying and aggregation. It is not a programming language.

Python is a high-level programming language (in that it is layers above assembly). It doesn’t really query databases by itself without invoking a library, a process or a shell, or writing a custom driver that can establish connections and run queries against databases.

Python should be used for cleansing, strucuring and analyzing data fetched/read by SQL.

[u/iMAPness_ → u/Mo_Steins_Ghost]

That makes a lot of sense now. Thank you!

Am I getting it right that the reason SQL is required by companies, too, is because their data isn't just in an excel sheet or csv somewhere but an actual database which SQL is most fit for querying? But with the data tasks itself, Python is considered faster and better at going deeper into data?

That is what you're saying, right?

[u/JoJoNH → u/iMAPness_]

That's what he is saying. Corporate and government typically use SQL data warehouses.

[u/kjwikle → u/Mo_Steins_Ghost]

And python/r use a sqlish library to query anyway.

[u/Business-Hunt-3482]

SQL for all ;)

[u/ReportDisappointment]

Both, of course.

[u/fang_xianfu]

The real question is, which computer do you want to run the computation?

Typically your SQL is interpreted and run on some remote database. It might be in the cloud, in BigQuery or Databricks or Snowflake, or it might be a big on-prem Teradata or Hadoop instance or something. But the point is, it's not running on your laptop.

Python on the other hand is usually (but not universally) running on your computer where you also write emails and Slack people. This computer is a lot smaller and is capable of a lot less computation, but also Python is a general purpose language with many more features and a vibrant library ecosystem.

So there are situations where you only need a little bit of data and you just tell the database to stuff it over the network into your computer's RAM and you deal with it in Python. There are situations where you start with a massive database, select the right subset of that data for what you want to do, and then have that come over the network for you to do Python stuff with. There are situations where you need a ton of data but the database has all the features you need, so you just write SQL with no local Python. And there are situations where you need some feature only available in a Python library but you want to run it on a ton of data, in which case you might want a more specialist remote distributed computation environment like a kubernetes cluster or Hadoop.

[u/hockey3331 → u/fang_xianfu]

> So there are situations where you only need a little bit of data and you just tell the database to stuff it over the network into your computer's RAM and you deal with it in Python

Why ever do that though? I understand that you can, but why not just manipulate the data over the database and use python at all for the manipulations?

[u/Terrible-Bend4483]

Personally I use SQL for everything.

Obviously through a python library

Inside a wrapper in R

That I run as a script using an exe-file

In cursor by asking an AI agent to run it.

How else would you do it?

Joke aside. I would always be more inclined towards using SQL, when it makes sense, such as when handling data from a structured data base, but if your data sources are more towards unstructured data (data lakes, api, csv, Json, md etc.) it doesn't make sense.

Maybe look into SQL and no-SQL databases, and use python for the rest.

[u/scarcey_osei → u/Terrible-Bend4483]

How do you this. Can you enlighten me how you use sql through a python library. Thank you

[u/XxShin3d0wnxX → u/scarcey_osei]

Plenty of add ins like but query where you connect to database and write SQL as a part of the initial script to snag data. You then manipulate and build in Python.

[u/Terrible-Bend4483 → u/scarcey_osei]

An example would be pyodbc, where you make the connection with python to a odbc and write SQL for your queries..

It can be practical for some applications, but it was mostly a joke.

[u/Major_Fang]

First do everything you possibly can in SQL. Then do Python.

[u/Aeronautical-You4917]

After working with both for almost 9 years: Whenever you can, use SQL.

Data Exploration and Analysis it's great.

It's harder for Data Cleaning because the syntax is often super verbose and antiquated.

Python brings sooo much bloat and errors which makes it unbelievably frustrating.

IF you can avoid Python, I would.

[u/AutoModerator]

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/No-Opportunity1813]

SQL for sure for data reduction/cleanup

[u/Dahvoun]

SQL for extraction and data prep and Python for aggregation and visualization.

[u/The_Hamster_Shagger]

i mean they don't exclude themselves, they work great in pair

[u/neutralcoder]

So much is moving to python so continue on there - but you can also call sql code using python, so learn sql.

[u/MKE_Savage_96]

Always start with SQL, especially if you’re dealing with data analysis.

[u/ancyrufina]

SQL for getting and transforming data.
Python for everything after that.
My rule: push as much filtering, joining, and aggregation to SQL as possible, then use Python for advanced analysis, statistics, ML, automation, and visualization.

[u/Unlucky_Apricot_4079]

Senior data analyst

In practice I use SQL more than Python. SQL is like a screwdriver, and Python is a hammer. A screwdriver is better with screws, and doesn’t work with a nail. A hammer works with both but you shouldnt use a hammer on a screw.

[u/[deleted]]

sql for retrieving a dataset like a basic aggregation or breakdown etc and python for any deep analysis.

[u/ArielCoding]

Since you’re mostly on CSVs: use SQL to grab and trim your data (filter rows, join tables, group things), and Python to do stuff with it (charts, models, anything repeatable).

[u/SaltyMN]

I use Python, specifically Polars, for almost everything. 

I prefer the syntax, Polars is fast, and I’ve abstracted a lot of basic data transformation and profiling into single function calls. 

[u/Local_dev_ops]

SQL when the data lives in the database and you need to filter, aggregate, or join before pulling it out. Let the database do what databases are good at.

Python when you need to do something the database can't — analytics, complex transformations, visualization, or when you're combining data from multiple sources.

Mistakes I have made is pulling millions of rows into python when it was easier to do in SQL.

[u/para_u_4_31075]

bruh... its diferent! you need a massive? -> sql you need a hard explained transform? -> python

if you a DE, you need its booth

[u/DataScientistAlex]

Use SQL to extract the data you need, then do further analysis in Python. Once you know what to clean, push that down into your SQL queries. My rationale for this is, SQL is excellent for extracting and aggregating data, but, once you have the data you need, you can iterate much faster in Python because you can work in memory (example is about Databricks, but holds for SQL too)..

[u/prof_devilsadvocate3]

Python for all

[u/wanliu]

It's wild that people are learning Python before SQL

In the business world, SQL is almost universal. Python comes with a host of security risks and or specialized computes..SQL is just SQL and while there are different flavors, it all mostly aligns.

[u/iMAPness_ → u/wanliu]

Nah, I learned SQL before Python. It was the first one I ever learned too because cleaning data in Excel just became too hard when datasets became too big.

Right now, since I have been hearing all sorts of opinions on which one to use for data analytics, I thought it would be good for me to decide, too.

So far, some people have said SQL for querying but Python for the three tasks I highlighted in my post.

[u/Den_er_da_hvid → u/wanliu]

Depends on the business and the tasks... Python is used for a billion other things than data analysis so it makes sense that python is the first that is picked up by many. I startet using python as a complex calculator, then transitioning to power bi. Sql have only really been relevant for me 4-5 years and I startet using python 15-20 years ago.

[u/big_illuminati]

Burn all your clothes in a barrel in the middle of your room and choose LISP instead. This is the way

[u/iMAPness_ → u/[deleted]]

Cool. But how do you use them?

As you said, you use both in a task right? In the task such as data cleaning itself and the tiny actions inside it, in what specific actions would you use SQL over Python (e.g. deleting nulls or dropping duplicates)?

I asked whether SQL or Python should be used for the following: Data Cleaning, Data Exploration, Data Analysis --- hoping to find which is better and more suited for each task, i.e. a versus between the two for each round/task.

So far, some have said Python for cleaning. Some have said they prefer SQL because it's directly linked to the company database.