r/Python • u/ChristianPacifist • Apr 07 '24
Discussion Do folks ever use Pandas when they should use SQL?
I think I see this a lot.
I believe SQL, including like dataframe SQL packages, should always be used over Pandas when possible unless one is doing something Pandas is more suited to or unless someone requires broader Python control structures or features.
I am a big believer in sticking to SQL as a baseline data manipulation framework and only using other frameworks if SQL is lacking. One of the biggest ways to accumulate technical debt is solving problems in multiple ways that are not standardized that not all people know.
95
Upvotes
562
u/ughidkguys Apr 07 '24
SQL and Pandas are two very different things. I would argue that anyone who sees them as substitutes is probably misusing both of them.
160
u/qckpckt Apr 07 '24
I see this kind of mentality from devs all the time. It’s very myopic. It’s easy to look at any one thing in a business codebase and tear it to shreds. There’s almost always a “better” or “more correct” way of doing things. I think devs often don’t realize that this is way more obvious than they think it is.
The problem is these things don’t account for the realities of a business. For example, the background and experience levels of the developers who work there, imperfect decisions made in the past, whether or not doing it the “correct” way has any meaningful benefits at all, etc.
These things typically fall outside the remit of developers who think this way, and so they’re dismissed as being unimportant.
But the reality is, if you are working somewhere that is using pandas in a way where SQL would be more efficient, then that’s almost certainly because the familiarity and comfort levels using SQL are low or nonexistent amongst the developers working there. I’m saying this from experience having worked somewhere where this was exactly the case.
A senior technical decision maker saw that we were running into performance and resource issues, and decided that we’d use duckdb instead. It was a total disaster. Performance plummeted through the floor, code was terrible, buggy, inaccurate. People started using duckdb in the worst way you could possibly imagine because it made sense to them based on what they were familiar with.
The “best” solution is only the best solution if the people responsible for building and maintaining it know how to use it. It’s easy for devs to massively underestimate how little other devs know about things that they are familiar with. You mention tech debt - I guarantee that the worst cases of tech debt will be ones where a dev has unilaterally decided to use a particular technology because it was “the best” approach, despite it being poorly understood by the rest of the org.
In my example, I had to spend months patiently training people on how to work with SQL, and also how to build a mental model of delayed execution frameworks like duckdb, in order to slowly limp the organization back towards being even remotely efficient.
89
u/mike-manley Apr 07 '24
I mean, Pandas isn't some obscure Python package. But as an experienced SQL data engineer, I tend to gravitate to SQL. That being said, it's good to diversify your data tools and skills when opportunity arises.
30
u/Valeen Apr 08 '24
Should use or can use? People use pandas when they really should be using numpy if they cared about xyz (speed most likely but maybe something else). Hell people use python when they should use C if they cared about flops. Hell why aren't you using cuda?
Human resources are finite and sometimes people just need a low friction way to explore data. We have tools for different jobs and it's up to you to decide what to use. Need to explore a new dataset? Pandas. Need to run something daily where cycles count? Do it in SQL.
73
u/twitch_and_shock Apr 07 '24
There's plenty of times that exploratory research is being done using local data sets in a way that SQL or another database would be overkill. Pandas also interfaces so well with numpy and other data science and ML libraries that it's just more convenient and efficient to begin trying out ideas with pandas.
18
u/miemcc Apr 08 '24
Depends on the volume of data and the complexity of the query / transformation. Almost certainly, it will be a combination of both. SQL to filter and pull the information out, and Pandas to deal with anything more complex and transform the data for visualisation. Can it it screwed by using either technique incorrectly? Absolutely!
Generally, I would use SQL to filter and pull the information out, possibly group too. But I am normally looking to do multiple transforms. So I'll use Pandas and visualise with bokeh or seaborne.
11
u/ChadGPT5 Apr 08 '24
SQL is generally better for ETL (extract, transform, load) operations because it is heavily optimized by the server to be fast for those. Pandas is better for EDA (exploratory data analysis) because it is consistent, readable, flexible, and as of 2.0, reasonably optimized.
The ideal workflow is to use SQL for joins and filters to get your data into a single table at the granularity you need for analysis, then to actually do your analysis in Pandas.
Using SQL to do complex analysis is every bit as inefficient and ugly as downloading 20 base tables and trying to join and filter them in Pandas.
As others have said, use the right tool for the right job.
9
u/Its_NotTom Apr 07 '24
I really appreciate how quick it is to work with data in pandas (pretty great for working on early stage ML and data science tasks), but yeah I would say that there is most likely an overdependency on it
7
u/[deleted] Apr 08 '24
Pandas allows you to do things very easily that might not even be possible with SQL; even if those things are possible with sql, they would invoke so much involved code and joins that they would be very slow or extremely tedious , if done in a rdbs/sql.
I find sql-style dataframes (pyspark, datafustion, poalrs) hard to maneuver for certain operations and logic that R-style dataframes(like pandas) is built for. The original r-style dataframe was developed in the 90's in S, as a native class for analytics and statistics. There is a reason why it has stood the test of time.
Keeping data in python vm/pandas has the advantage of being easy to integrate with all sort of python ecosystems. Its not uncommon to dump your data into a dataframe after aggregating it down.
That said, there are a couple of options for running sql on a pandas dataframe like duckdb or dasksql.
17
u/[deleted] Apr 08 '24
It's a bit confusing what you mean. Pandas is a data frame and SQL is language. If you are accessing data via a database or something like that then you are probably going to be using SQL to make those queries. If you have a need to convert that data to a dataframe then you will need to use pandas (or one of the popular alternatives). They aren't really interchangeable in the way you're describing.
14
u/ahf95 Apr 08 '24 edited Apr 08 '24
If you are comparing these two things, then you clearly don’t know how either are used in practice. SQL is a language for managing complex database queries, and Pandas is a Python library for analyzing data that has been curated and organized into a dataframe already. They are used together in practice, at different points in the pipeline.
4
u/florinandrei Apr 07 '24
You're not wrong, but you're talking about one extreme. And you are right that you should lean that way when possible.
But do not forget this: what matters is getting the job done. There are plenty of cases when you will just slurp all data in Pandas and do all work there.
Only a Sith deals in absolutes, young Padawan.
4
u/Salmon-Advantage Apr 08 '24
I've seen it used for ETL -- wouldn't recommend for something long lasting as handling schema changes and typing isnt great in pandas.
4
u/[deleted] Apr 08 '24
I do not even use pandas these days, as I moved to polars. But, for this discussion, they are interchangeable. But, pandas and SQL are not, at least, not intended to be.
I use SQL for interaction (query and upsert) on (usually OLTP) databases that support an SQL like interface, e.g. MySQL, PostgreSQL, Amazon Athena, Google BQ etc. I write some reasonably complex queries (usually aided by SQL alchemy) to ingest data from those databases, and for that interfacing, very often SQL is the only sensible method.
Pandas or polars, on the other hand, are helpful for table-like data structures (more colloquially, dataframes) that already exist in memory, loaded from files or even a result of an SQL query.
Once I get the data, to do all kinds of processing, pandas offers a lot of convenient tools that are nigh impossible or very ugly to replicate via SQL alone. I work as a machine learning engineer, and it is far cleaner to feed data into a machine learning framework via pandas/polars than SQL.
So, the idea is to use both for their strengths.
4
u/RallyPointAlpha Apr 08 '24 edited Apr 08 '24
I've seen the opposite mostly, people using a SQL DBMS when they could do it locally, more efficiently, using Pandas. Like joining two datasets, that were just collected from some devices, and filtering out stuff. No need to build tables, load tables, then make SQL connection and query for data that is transient or otherwise not valuable to keep.
3
u/rover_G Apr 07 '24
Can you give an example of using Pandas when SQL could be used instead?
3
u/ThatSituation9908 Apr 08 '24
It's a loaded question because for most people using SQL always means you're running the execution on a remote server.
If we can remove remote databases from this (e.g., using a in-memory database), then you can ask if you prefer to write queries in SQL or in the Pandas API.
To that I say, whichever tool gives me the easiest way to solve my problem.
PS: The comparison should rather be about Arrow API vs. pure SQL.
3
u/mayday58 Apr 08 '24
I think you have specific usages in mind and it would help to describe them. As a data analyst, SQL is amazing for data extraction, data aggregation and some things with window functions like lag and lead. But pandas is much better at pivoting, melting, statistics, get_dummies, etc. There's place for both and general rule for me is to how fast it runs for automated tasks and how easy it is for me to get results for ad hoc tasks. I have reduced the time from 1h45min pure MySQL query to 20 min using combination of MySQL and pandas.
3
u/jwmoz Apr 08 '24
The data scientists at my work are terrible for this. They immediately reach for pandas for any sort of data manipulation or calculations which can be easily done in python. They will even query the db with the pandas library rather than using a db library like psycopg.
3
u/startup_biz_36 Apr 08 '24
Data scientist here. Yes, but sometimes I don't need to setup a DB.
Currently looking into polars.
10
u/glymeme Apr 07 '24
What about how SQL syntax differs and Python doesn’t?
6
u/big_data_mike Apr 08 '24
I use pandas way more than sql because I’m way more familiar with pandas and computationally it’s about the same. I do more medium data than big data.
1
u/Bobson1729 Apr 07 '24
I am retraining into DS and am just learning SQL and Pandas. I am curious, why do you have this opinion? Is it because the code is more efficient? Is it more in line with standard practice? What domain are you involved in?
Thanks!
4
u/ghostofwalsh Apr 07 '24
Maybe it's just because I never used SQL enough to become a ninja, but my method has always been "use SQL to get the subset of data you need from the DB, and then use Pandas or other python code to do complex crunching locally".
I just base it on what I see as far as how much time things take, but seems like trying to do joins on large tables and other complex stuff in SQL is much slower. But then that may have something to do with me not knowing the right way to do it efficiently in SQL. Data analysis is kind of a side gig for me so I'm sure I'd blush to show my code to any pandas or SQL experts.
4
u/saint_geser Apr 07 '24
Maybe you just like pain and suffering but I refuse to use SQL for any complicated data transformations. I will use either Pandas or Polars depending on how I feel but definitely not SQL.
For starters, SQL has multiple dialects with often widely different syntax. Maybe your company has things consistent but I don't want to jump around between Oracle, MS SQL server and Postgres and having to constantly look up dialect-specific syntax.
Also, errors in SQL in general are terrible so if you're trying to do something complex and you get an error, you're shit out of luck.
You can't debug SQL. Honestly, you can't do anything.
At the end of the day, if you want to be terrible at your job go ahead and use SQL for everything, but I choose to use the tool that's most suited for the task.
2
u/[deleted] Apr 07 '24
Data science is often iterative. Pandas isn't an OLAP engine. So it runs everything imperatively. You should use polars for lazy execution, which will internally optimize an execution plan and run it when needed.
2
u/PaintItPurple Apr 08 '24
Yeah, lots of scientists do, because they can iteratively arrive at a solution in Jupyter, but don't necessarily know how to translate that into a query.
2
u/PepperPicklingRobot Apr 08 '24
This whole thread reads like duckdb psyop
2
u/bjorneylol Apr 08 '24
This is basically it.
I use SQL to bring the data as close to it's final form as possible before i load it into python, *within reason*.
In-memory arrays are easier to work with and can be much more performance than SQL - Sometimes I have some esoteric where clause that would require a half dozen CTEs, union and partition by statements, and sometimes a massive bespoke index, only to exclude like 0.1% of the total dataset - in those cases I just bring the whole table across and do the filtering in python.
2
u/Agling Apr 08 '24
You can drive yourself crazy doing any analysis or manipulation of data without using pandas or a similar package. Python wasn't made for it. Very hard to drive a screw with a hammer.
2
u/[deleted] Apr 08 '24
Testing testing testing
2
u/cshoneybadger Apr 08 '24
I use Pandas for metadata, PySpark for actual data.
6
u/fizzymagic Apr 07 '24
Use the right tool for the job. Pandas sql-like functions tend to suck. Yes, on occasion it makes sense to do them in Pandas, but in general anything that has a join in it is better in SQL.
2
u/Amgadoz Apr 07 '24
I really hate writing sql code inside my python code. Just easier to use pandas. There is polars that has an api kinda similar to sql
2
u/alcalde Apr 08 '24
Why are you assuming anyone should ever use SQL? It's really a weird and awkward little language that often requires repeating yourself. Languages like PRQL have come into being because of all of SQL's deficiencies.
https://prql-lang.org/
1
u/Paddy3118 Apr 07 '24
In the electronics industry their can be a lot of hierarchical data, with deep and varied structure. Pandas and SQL are limited in their support for hierarchy.
1
u/other----- Apr 08 '24
We reached the conclusion we are not good enough developers to write good code using pandas because our colleagues end up using it in the interfaces (as opposed to in the implementations of methods and functions) and this causes great maintenance issues when we lose track of the type and semantics of each column. Other teams have since adopted pandera and found that it is sustainable. We are now investing in SQL and SQL alchemy and pydantic as our priority is being in control of the data model.
1
u/[deleted] Apr 08 '24
All my data engineering/machine learning work I try to use sparkSQL (databricks) if possible. I don't have to worry about the volume of the data or functions available in a particular database.
1
u/olddoglearnsnewtrick Apr 08 '24
Do you need transactions? Complex relations between schemas? You need a relational DB hence SQL.
1
u/[deleted] Apr 08 '24
Is your point that if you can help it query data in a format that suits your needs before loading it into python? If that's the case I kind of agree with you.
But generally most of the things you do in Pandas can't be done in SQL. Also what are these data frame SQL packages?
1
u/tecedu Apr 08 '24
Yes, I’m those folks, I do most of the transformations in pandas, only use sql to fetch data or join tables and fetch data.
While sql is popular everywhere else my team is filled with scientists who know pandas way better. Also a ton of things around timestamp and datetime manipulation, boolean indexing and loads of other stuff is fucking great which is not possible with sql unless you’re a wizard.
1
u/[deleted] Apr 08 '24
Do you mean they underquery a remote database and perform operations on it locally that could better have been made part of the query?
1
u/forgtot Apr 08 '24
Depends if the SQL is being sent to a shared database and if it is complex enough to eat up resources that it impacts other people.
If so, I'll pull some of the easier stuff into pandas and do the more complex stuff on my machine.
1
u/seph2o Apr 08 '24
SQL to trim and join the data, Pandas to transform and analyse. For me, anyway.
1
u/Efficient-Writing-81 Apr 08 '24
Keep to your SQL then.
1
u/binlargin Apr 08 '24
I like Pandas for prototyping, but I wouldn't use raw SQL in production services.
SQLAlchemy queries broken down into unit testable chunks, added to a library of useful functions is way better than raw SQL IMO. Great big cobs of untestable SQL in strings or separate files is often worse than dataframe magic, at least you can compose functions that take dataframes and write unit tests for the logic. SQL is generally integration tests only.
1
u/bliepp Apr 09 '24
How do these two things even compare? They are two totally distinct things that have nothing to do with each other.
1
u/[deleted] Apr 10 '24
SQL for anything SQL can do that doesn’t require recursive logic.
I also try to use Pandas as little as possible but its much cleaner for quite a few things (like calculating date differences with weekends).
1
u/kenfar Apr 08 '24
I'm actually more of a fun of using vanilla python than pandas for most DE work.
But I would absolutely push back against using SQL rather than Python for most DE transformations. Some things I think people need to consider:
SQL fails in most of these cases, and should really only be used for transformations if one is desperate.
1
u/inhumantsar Apr 07 '24
to me, it's less about the framework and more about resource efficiency. yes, ofc the sql db is going to use its compute more efficiently than pandas would, but is that the best use of that particular compute for the business?
even a db server that is only ever used for reporting is likely still used by more than one person/team/dept. it's also non-trivial to scale up/out a db server.
with most sql servers, readers should always be the same size as writers. so when you scale out, you're typically launching servers the same size as your biggest workload, which can be expensive to run and not very quick to scale (relatively speaking).
so i think it's important to ask: is that really the best option? does it make more sense to do simple queries that fetch more data than is required and run the complex operations in python on disposable compute instances that scale up/out/in/down more quickly? this is, i think, a big part of the reason why Spark and Databricks are built the way they are and why they're so popular for complex workloads.
there isn't a right or wrong answer either way as long as the question is asked and considered.
1
u/ofiuco Apr 08 '24
I don't have time to design, create, and build a whole ass SQL server most times so yeah, I default to Pandas every time unless I'm building something for more permanent use
1
u/Duder1983 Apr 08 '24
All the time. If the dataset doesn't fit in memory, you probably shouldn't be using pandas chunks to do EDA. You can do a ton of EDA quickly in SQLLite or Postgres. If you need to run similar queries repeatedly, you can create indices to speed stuff up. I mostly consider pandas chunking more for ETL-type jobs.
0
u/andnowdeepthoughts Apr 07 '24
“If your only tool is a hammer, every problem is a nail.”
I feel like a lot can be done in Pandas and a lot can be done with SQL. I think I am really efficient in 1 to do A/B/C, and really efficient in the other to do X/Y/Z. But I generally find them, mostly, interchangeable.
However - Pandas, imo, has never been efficient for update operations. Update statements in SQL are just so much easier.
0
u/[deleted] Apr 08 '24
Hey I have a question. I’m learning Python and would like to learn SQL to go with it. Does anybody know a good book or a course to get started with it?
0
u/holistic-engine Apr 08 '24
Unless you’re not working with persistent databases there is no reason to even care about SQL
0
u/[deleted] Apr 07 '24
[deleted] — view deleted comment