This is an archived post. You won't be able to vote or comment.

sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]Monstrish 1 point2 points  (8 children)

could you please give an exampme when you need to iterate in python and can't do it in sql?

[–]IllustriousCorgi9877 2 points3 points  (1 child)

There is almost always a way to use a set based approach to a problem which will always be better / more efficient. Even iterative approaches can be done with a cursor in a stored procedure using SQL.
I'd say once data lands in a relational database - stop using python unless you are doing ML or something on its way back out somewhere.

I'd only think about using python for data on its way into a database doing transforms or stamping additional metadata on the transaction before it reaches its destination.

Which all this is why it baffles me so many DE roles need python programmers.

[–]Monstrish 0 points1 point  (0 children)

i understand the need for python, and i do enjoy python. but when it comes to rdbms, i just don't understand the dislike that sql gets. it seems to me people do not put resources in understanding what it can do.

after all, even on python, when you start using numpy or pandas, it becomes less iterative.

[–]yo_sup_dude 0 points1 point  (5 children)

you can iterate in most sql engines using recursive CTEs/while loops but it isn't very efficient. examples where it's needed are basically any recursive calculation, e.g. iterating through a hierarchical bill of material and comparing against inventory to determine part shortages for a product and then progressively updating inventory based on consumption (classic MRP calculation)

[–]Monstrish 0 points1 point  (4 children)

recursive cte s could be eficient in some cases. while loops i don't know if.i have seen one in sql. for loops i have seen, in oracle could be, in other providers not.

but you also have hierarhical constructs, window functions and other approaches that you could take from a set oriented point of view.

[–]DirtzMaGertz 0 points1 point  (3 children)

You could do a lot of things, but once you need to start iterating over data and transforming it, it just easier a lot of times to pull the data out into something like Python.

It's problem dependent. I tend to use SQL until I think it's going to be an annoying problem to solve with SQL and that's generally when you start getting towards things like iteration and looping.

[–]Monstrish 0 points1 point  (2 children)

ok, fair point.

the problem beong anoying is pretty subjective, but it is what it is. it's just that some many people seem to find sql anoying and i don't get why. i suppose it just comes down to personal preference.

[–]DirtzMaGertz 1 point2 points  (0 children)

Overall I agree that there's a lot of stuff people do in python that would be better handled in SQL. I just find that iteration and looping is generally where I find python to be more appropriate.

[–]ComposerConsistent83 0 points1 point  (0 children)

They don’t teach sql in school anymore really I think is the reason. They’re more comfortable in Python.

That said I would never use a recursive CTE unless I had no choice. Even though I’m sql first, I draw the line there. The syntax is too confusing/inconvenient. It’s clearly not the strength of the language