Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What SQL Analysts Need to Know About Python (2016)

segment.com

Post reply on HN
Print view View original

11–20 of 115 posts

Re: What SQL Analysts Need to Know About Python (2016)

#11

Post by phumbe »

oarabbus_ wrote: post #7
drej wrote: post #6

I'm worried about the inverse - what Python data analysts should know about SQL. Because I've met tons of analysts who wouldn't be able to even run a basic select. I've seen tons of (often non-reproducible) code written in place of a simple SQL query. I really wish bootcamps and other learning platforms focused on SQL a bit more. (I am a Python data analyst who properly learned SQL only after several years in the ind…

Or worse, they use cursors. Cursors should never be used in SQL. Ever. That's my philosophy.

What is a better approach? I have Python that directly connects to an Oracle database, and the Oracle blog tutorial[0] for using their Python package always uses a cursor.

[0] https://blogs.oracle.com/oraclemagazine/perform-basic-crud-o...

Re: What SQL Analysts Need to Know About Python (2016)

#12

Post by geebee »

I've found that SQL and data frames are pretty exceptional together in combination. I'm a big fan of a Python module called PandaSQL https://pypi.org/project/pandasql. What makes this work so well (for me, at least) is that I can combine pandas operations that transform data frames with SQL operations that transform data frames in the same pipeline, and if I really need to, I can just break it all apart with python and reassemble it back into a dataframe later. I don't need to recreate Boolean algebra with complex and potentially buggy dataframe operations[1], and I don't need to recreate loops, conditionals, stats operations and so forth with complex and potentially buggy SQL.

For clustered work, I've found that Spark sql data frames give a lot of the same functionality (not quite all, though I think that's because there are some pandas operations that require a full in-memory dataframe and don't lend themselves to distributed solutions).

[1] there have been so many attempts to replace SQL with a different relational-like language. the end result is a new syntax that doesn't work if you try to pull your queries out and run them against a database independently. I'm going on a tangent in a footnote here, but I remember reading "your data will outlast your application." I personally strive for a usable database outside the context of the application it was originally created to support. Trust me, eventually someone will want a set of reports that would (in many cases) be fair easier to write as queries if you'd made sure your back end database was a properly designed relational database.

Re: What SQL Analysts Need to Know About Python (2016)

#13

Post by andybak »

overcast wrote: post #4
sheepstrat wrote: post #3

A few years ago I joined a Rails shop, and one thing that always struck me was how many of the engineers didn't know SQL. Most of them had learned to code on Rails, and had always had SQL abstracted away via ActiveRecord. I know this is not the point of this article, but as data analyst/scientist roles continue to climb in popularity, I'm curious if there won't be a similar trend with Python.

Pretty easy to see how, just look at the Python equivalent in Django. You're so far abstracted away from what is actually going on that it's no wonder no one understands it. There is a lot of gotchas, that most probably never investigate, particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs(they aren't).

> particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs (they aren't).

No need to check the SQL logs directly.

First thing I learned about optimising Django was to check django-debug-toolbar to see how many queries were being generated per page. This is fairly common knowledge.

However. I don't often bother because SQL calls aren't the most common bottleneck. It's nearly always a better use of my time to look at page weight or javascript blockage.

Re: What SQL Analysts Need to Know About Python (2016)

#15

Post by bshipp »

andybak wrote: post #13
overcast wrote: post #4

Earlier quoted context omitted.

Pretty easy to see how, just look at the Python equivalent in Django. You're so far abstracted away from what is actually going on that it's no wonder no one understands it. There is a lot of gotchas, that most probably never investigate, particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs(they aren't).

> particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs (they aren't). No need to check the SQL logs directly. First thing I learned about optimising Django was to check django-debug-toolbar to see how many queries were being generated per page. This is fairly common knowledge. However. I don't often bother because SQL calls ar…

django-debug-toolbar is excellent, and I use it on my django sites as well.

In addition, the documentation does a pretty good job of highlighting some of the common gotchas. Unlike your environment, my page/js weight is very low but I'm querying against a few hundred million records joined across many tables. Even using materialized views to eliminate the impact of joins in postgres, it's required a fairly delicate touch to make the delay for page loads tolerable.

In that respect I would likely redo the project in flask and sqlalchemy, if only because then I wouldn't have to remember the syntax nuances of two separate ORMs. They're similar, but not identical, and it's infuriating at times. Plus I'm very comfortable dipping down into raw SQL in sqlalchemy, and it hasn't been as intuitive for me with Django.

Re: What SQL Analysts Need to Know About Python (2016)

#16

Post by bkovacev »

oarabbus_ wrote: post #7
drej wrote: post #6

I'm worried about the inverse - what Python data analysts should know about SQL. Because I've met tons of analysts who wouldn't be able to even run a basic select. I've seen tons of (often non-reproducible) code written in place of a simple SQL query. I really wish bootcamps and other learning platforms focused on SQL a bit more. (I am a Python data analyst who properly learned SQL only after several years in the ind…

Or worse, they use cursors. Cursors should never be used in SQL. Ever. That's my philosophy.

Could you elaborate further?

Re: What SQL Analysts Need to Know About Python (2016)

#17

Post by n4r9 »

oarabbus_ wrote: post #2

SQL+Python is extremely powerful, and the author makes some good points (multivariate regression should be done in python and not SQL for example), but the query example in the blog is not a good one. Every modern DB has aggregate and statistical functions like ntile, percent_Rank, median, min, max, etc. I’d honestly rather run these functions against the database than do it in python. Especially if you're working wi…

SQL Server doesn't make it easy to calculate median values. There are a number of slightly convoluted ways to do it but it's far from straightforward and usually I end up calculating it in C# if I can.

Re: What SQL Analysts Need to Know About Python (2016)

#18

Post by bshipp »

oarabbus_ wrote: post #2

SQL+Python is extremely powerful, and the author makes some good points (multivariate regression should be done in python and not SQL for example), but the query example in the blog is not a good one. Every modern DB has aggregate and statistical functions like ntile, percent_Rank, median, min, max, etc. I’d honestly rather run these functions against the database than do it in python. Especially if you're working wi…

I wonder if people do timings for the variances between a function in the DB vs Python? Unless your queries are so small (like individual records) that a round-trip to the DB is impacted by network lag, I find it's almost always faster to ask the DB to do as much of the lifting as possible.

The downside, of course, is that your code potentially becomes unreadable, harder to migrate between database backends, and more difficult to debug.

Re: What SQL Analysts Need to Know About Python (2016)

#19

Post by hobs »

oarabbus_ wrote: post #7
drej wrote: post #6

I'm worried about the inverse - what Python data analysts should know about SQL. Because I've met tons of analysts who wouldn't be able to even run a basic select. I've seen tons of (often non-reproducible) code written in place of a simple SQL query. I really wish bootcamps and other learning platforms focused on SQL a bit more. (I am a Python data analyst who properly learned SQL only after several years in the ind…

Or worse, they use cursors. Cursors should never be used in SQL. Ever. That's my philosophy.

Generally you'll find at some point a mix of set based and loop based logic wins the day in most SQL's - but shy away from those cursors until you absolutely must use them.

Re: What SQL Analysts Need to Know About Python (2016)

#20

Post by overcast »

andybak wrote: post #13
overcast wrote: post #4

Earlier quoted context omitted.

Pretty easy to see how, just look at the Python equivalent in Django. You're so far abstracted away from what is actually going on that it's no wonder no one understands it. There is a lot of gotchas, that most probably never investigate, particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs(they aren't).

> particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs (they aren't). No need to check the SQL logs directly. First thing I learned about optimising Django was to check django-debug-toolbar to see how many queries were being generated per page. This is fairly common knowledge. However. I don't often bother because SQL calls ar…

When you have millions or billions of rows, SQL calls absolutely become a huge bottleneck. I deal with this all the time from shortsighted developers at the office in other ORM environments. Most recent being a SELECT...NOT IN('a','b','c'), causing an INDEX SCAN on 200 million rows, and then complaining it takes 6 minutes to run.

Look at Django's select_related. It's one of those if you don't understand what's happening under the hood, then you're probably querying way more than you should be.

Post reply on HN