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

21–30 of 115 posts

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

#21

Post by ansgri »

phumbe wrote: post #11
oarabbus_ wrote: post #7

Earlier quoted context omitted.

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...

They talk about client-side cursors, these are part of the Python DB API. What you shouldn't use is server-side cursor, which you create with DECLARE in PosgreSQL. These keep their state on the server and are intended for optimizations for special cases like streaming data processing or realtime updates. Basically, for deep internals of realtime systems, and not regular queries.

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

#22

Post by bshipp »

mongol wrote: post #14

Python + SQL seems like a good match for many analysis problems, but Excel + SQL is not bad either. I like the ability to combine complex SQL views or SQL functions with Excel pivot functionality, querying the database directly from Excel

Even after learning python, pandas, and SQL I still find myself firing up Excel first when exploring a new problem. The interface is incredibly simple for pasting, manipulating, and visualizing data. The warts really only start appearing when you try to build business applications on top of it and shortcomings with data integrity (amongst other issues) begin creeping in.

But as a sketchpad for handling a problem? Excel is pretty hard to beat.

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

#23

Post by marcosdumay »

phumbe wrote: post #11
oarabbus_ wrote: post #7

Earlier quoted context omitted.

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...

I imagine none of you or the GP is talking about in-database cursors, that you open in SQL, use on the same SQL script, close at the end of the script and move along. There isn't really a problem inherent to those, and they stay non-problematic if you are writing your database scripts on Python, C, or whatever.

For a server reading and writing from/on a database, you paginate your queries. On Postgres that would be using "limit" and doing small bulk inserts, but under Oracle your options for inserting are limited, so there is still value on cursors.

There is the odd occasion where you'll will iterate through the entire results set, do something fast for each and every row, and only get something useful on the end. Those are classic problems where cursors are more efficient than the alternatives. But even on those, once you add error handling and recovering may yield better results with pagination.

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

#24

Post by tstrimple »

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.

Isn't that one of the selling points of Rails? Time to market is king. Optimize your SQL query performance after you've released and proven that it's an actual bottleneck. Why spend more time and money on an optimized product that might never see the light of day?

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

#25

Post by bshipp »

overcast wrote: post #20
andybak wrote: post #13

Earlier quoted context omitted.

> 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 happen…

select_related caught me when I first started using Django. It's a sneaky one because the queries worked fine when I first wrote the program. Then I started populating the database and, over the course of months, the queries got slower and slower and slower.

Eventually I was forced to pop open the hood and horrified to find this spaghetti bowl of nested, duplicate queries that took a fair bit of work to simplify and optimize. I was not so lucky as to have a DBA I could dump my problems on and was forced to learn that lesson the hard way.

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

#26

Post by overcast »

bshipp wrote: post #25
overcast wrote: post #20

Earlier quoted context omitted.

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 happen…

select_related caught me when I first started using Django. It's a sneaky one because the queries worked fine when I first wrote the program. Then I started populating the database and, over the course of months, the queries got slower and slower and slower. Eventually I was forced to pop open the hood and horrified to find this spaghetti bowl of nested, duplicate queries that took a fair bit of work to simplify and…

PRECISELY my point. Everything is easy to setup, and works amazing, when nothing is actually in the database.

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

#27

Post by goatinaboat »

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…

I’ve seen devs just run select * from table then filter it and sort it in their own code. Then they complain “the database is slow” when it’s spending all its time shipping gigabytes of data they don’t need to them!

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

#28

Post by goatinaboat »

ansgri wrote: post #21
phumbe wrote: post #11

Earlier quoted context omitted.

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...

They talk about client-side cursors, these are part of the Python DB API. What you shouldn't use is server-side cursor, which you create with DECLARE in PosgreSQL. These keep their state on the server and are intended for optimizations for special cases like streaming data processing or realtime updates. Basically, for deep internals of realtime systems, and not regular queries.

What you shouldn't use is server-side cursor, which you create with DECLARE in PosgreSQL.

That entirely depends on the DB. Oracle for example always executes with a server-side cursor; all declaring it does is give you a handle to what it was doing anyway.

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

#29

Post by danzig13 »

mongol wrote: post #14

Python + SQL seems like a good match for many analysis problems, but Excel + SQL is not bad either. I like the ability to combine complex SQL views or SQL functions with Excel pivot functionality, querying the database directly from Excel

PowerQuery/Get&Transform makes it go an even longer way.

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

#30

Post by hombre_fatal »

overcast wrote: post #20
andybak wrote: post #13

Earlier quoted context omitted.

> 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 happen…

I like having some sort of db:seed task during early development that seeds 1MM+ rows in all of your tables to help you experience performance issues as they're created.

It's even more important if your production application has millions of rows. Too easy to create a system that runs perfectly on 10 rows but will crash your production server as soon as you deploy it. Forgetting to create an index on the FKs is a classic one.

Post reply on HN