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

41–50 of 115 posts

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

#41

Post by chrishynes »

goatinaboat wrote: post #28
ansgri wrote: post #21

Earlier quoted context omitted.

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.

The problem isn't the cursor itself, but that it is usually a symptom of procedural thinking vs set based thinking. In an RDBMS it's typically far faster to puzzle out the joins, CTE's, and set based expressions and functions to use to winnow down a dataset vs a cursor based procedural logic on a row by row basis.

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

#42

Post by DannyB2 »

eximius wrote: post #9
DannyB2 wrote: post #8

uMatrix prevents it from loading. Against my better judgement, I turned off uMatrix and loaded the page. Then had a look at uMatrix. Wow, I think we have a winner for a site with the most scripts, most 3rd party domains, and the sheer number of XHRs. Sorry for this being off topic.

Do you have a sane setup you can share with uMatrix? Last time I looked at it, I was a bit overwhelmed and shelved it for later.

I'm not sure I understand the question?

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

#43

Post by burk96 »

eximius wrote: post #9
DannyB2 wrote: post #8

uMatrix prevents it from loading. Against my better judgement, I turned off uMatrix and loaded the page. Then had a look at uMatrix. Wow, I think we have a winner for a site with the most scripts, most 3rd party domains, and the sheer number of XHRs. Sorry for this being off topic.

Do you have a sane setup you can share with uMatrix? Last time I looked at it, I was a bit overwhelmed and shelved it for later.

I've found one of the best compromises is using setting uBlock Origin to advanced user mode, globally block 3rd party frames, and enabling a few extra filters that I don't know off hand.

Most sites will still mostly work, and the few that don't usually just need a CDN to be enabled (you can do this per-site).

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

#44

Post by teej »

bshipp wrote: post #18
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…

Pushing computation back into the database has been a major trend in the last few years. Tools like DBT have made that transition easy in terms of clean code and testable SQL.

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

#45

Post by 0xffff2 »

eximius wrote: post #9
DannyB2 wrote: post #8

uMatrix prevents it from loading. Against my better judgement, I turned off uMatrix and loaded the page. Then had a look at uMatrix. Wow, I think we have a winner for a site with the most scripts, most 3rd party domains, and the sheer number of XHRs. Sorry for this being off topic.

Do you have a sane setup you can share with uMatrix? Last time I looked at it, I was a bit overwhelmed and shelved it for later.

uMatrix is not a "set and forget" thing like uBlock is. Using uMatrix really requires that you understand the (admittedly overwhelming) UI and make decisions for yourself about what you want to allow/block. Personally, I deny nearly everything third-party and make individual exceptions on a per-site basis, which I save for sites I visit regularly.

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

#46

Post by iagovar »

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

Do you use powerquery? for that?

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

#47

Post by alexhutcheson »

starpilot wrote: post #37
goatinaboat wrote: post #27

Earlier quoted context omitted.

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!

Is anyone working on a translator for pandas dataframe syntax to SQL?

In the R world, dbplyr[1] does this amazingly well.

[1] https://dbplyr.tidyverse.org/

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

#48

Post by alexhutcheson »

bshipp wrote: post #32

One thing that hasn't been mentioned yet, that I found when I started using Python and SQL together, is the importance of well-considered indices. ORM's aren't always too clever when they generate an index, and often (for repeated queries) you can dramatically cut down the processing time by thoughtfully generating an index. If I know I'm going to be asking the database a certain question a bunch of times I'll even g…

I recommend "Use the Index, Luke"[1] for people who are relatively new to SQL performance issues and want to learn how to make their queries fast.

[1] https://use-the-index-luke.com/

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

#49

Post by bshipp »

chrishynes wrote: post #41
goatinaboat wrote: post #28

Earlier quoted context omitted.

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.

The problem isn't the cursor itself, but that it is usually a symptom of procedural thinking vs set based thinking. In an RDBMS it's typically far faster to puzzle out the joins, CTE's, and set based expressions and functions to use to winnow down a dataset vs a cursor based procedural logic on a row by row basis.

Thank you for clarifying that; I was wondering where the concern was coming from.

Without disagreeing with any of the above, one important consideration is what you're going to do with the query. If all you want to know is a column's mean or some other simplified statistical value, there's really no sense in pulling all the data into Python just to calculate it. Do it inside the DB itself with SQL.

On the other hand, if you need that data to do other work (i.e. populate the table in a webpage, or generate a new descriptive data set or whatever), then the trade-off for pulling it into Python/pandas and running a mean in addition to the other work becomes much smaller.

My approach is usually to do as much data filtering and parsing as possible inside SQL, but things like complex parsing and string manipulation (especially!) I'll do with Python. I can do some simple string work in SQL, but I can almost always do it faster and cleaner in Python.

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

#50

Post by pojzon »

goatinaboat wrote: post #27
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!

Im surprised ppl dont use ORM libs for this instead..
Post reply on HN