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

91–100 of 115 posts

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

#91

Post by arminiusreturns »

DannyB2 wrote: post #76
arminiusreturns wrote: post #73

Earlier quoted context omitted.

So, I don't know about setting rules beforehand that work very well universally, but what I personally think is the better method is to just slowly build up your ruleset as you use it every day. I don't think lots of people know how to do this so here it is: 1) Upon using a website you frequent, enable one root at a time until it functions properly. (or you can do the more granular per grid block enable) 2) Click uma…

When I visit a site that doesn't work. Usually I carefully whitelist things that I am willing to whitelist until it does work. If I can't get it working, then in most cases, I don't consider the content of that site to be valuable enough to whitelist things that seem sketchy. In the case of this site, uMatrix just flat out blocked the entire site. it's not that the side page didn't load, or that nothing happened beca…

Yes some sites will be completely blacklisted, and you have to manually whitelist them if you trust them enough to try them. I usually just skip them if they do that though.

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

#92

Post by danso »

noobiemcfoob wrote: post #82
danso wrote: post #70

Tangential question: I'm curious how many people (here on HN, or in general) learned SQL before they learned more traditional programming (e.g. Python, Java, C)? I learned traditional programming (through college) and only stumbled upon SQL years later (someone left a "How to use Microsft Access" book around at work). I absolutely love SQL, and am in the middle of writing a book on how to use just SQLite for large, c…

I learned SQL in high school as my first "programming" class, though that's more of a quirk of how I moved through the curriculum. This was prior to learning C++ and then transitioning to Python some years later.

That's really fascinating...what the class focused on SQL language and syntax and working with data? Or did you get into database theory as well?

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

#93

Post by mrbungie »

SJetKaran wrote: post #67
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!

From what I encountered, this is generally the case when someone is in the "analysis/reports" mode. Rather than get summary statistics on each column, find number of nulls, etc by writing a sql query, they instead get the data into the Python/R instance, and use general purpose functions, utilities, etc. "Programmers are expensive" statement probably applies here as well. I'm not trying to be defensive here, just say…

If you believe "Programmers are expensive", then you should do as much as you can do with a declarative data manipulation language (usually SQL, you can also consider sequences of text manipulations tools using pipes) and leave that last 15-5% of high-value work to a more powerful but also verbose imperative lenguage (usually Python, but any).

Asking for what you want is considerably faster than saying how you want it done.

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

#94

Post by airstrike »

alexhutcheson wrote: post #47
starpilot wrote: post #37

Earlier quoted context omitted.

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/

tidyverse is just an mind-boggingly amazing ecosystem of packages

Forget Da Vinci, the first man to be cloned should be Hadley Wickham

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

#95

Post by noobiemcfoob »

danso wrote: post #92
noobiemcfoob wrote: post #82

Earlier quoted context omitted.

I learned SQL in high school as my first "programming" class, though that's more of a quirk of how I moved through the curriculum. This was prior to learning C++ and then transitioning to Python some years later.

That's really fascinating...what the class focused on SQL language and syntax and working with data? Or did you get into database theory as well?

It was both language and database theory, all focused on Oracle. IIRC, it was titled "Database Administration", but I remember having to draw out schema diagrams and learn all the particular arrow types (one-to-one, one-to-many, etc) and what different block shapes meant in the diagram. All that only to get into industry 5 years later and find no one goes beyond basic squares and maybe double-ended arrows in practice >.>

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

#96

Post by starpilot »

faizshah wrote: post #69
starpilot wrote: post #37

Earlier quoted context omitted.

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

Ibis: https://docs.ibis-project.org/ https://docs.ibis-project.org/notebooks/tutorial/2-Basics-Ag...

Dang, Ibis doesn't support Redshift or SQL Server. I'm also having trouble understanding what it really is - it's an entire framework for big data it seems and not just a translator. What I'd really like is just that, something that turns pandas dataframe operation into ANSI SQL. So input pandas2sql('tablename["col"]') -> "select col from tablename". Something really simple to use.

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

#97

Post by kumarvvr »

geebee wrote: post #12

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…

PandaSQL was last updated 3 years ago. Do you know any other alternatives?

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

#98

Post by Dowwie »

danso wrote: post #70

Tangential question: I'm curious how many people (here on HN, or in general) learned SQL before they learned more traditional programming (e.g. Python, Java, C)? I learned traditional programming (through college) and only stumbled upon SQL years later (someone left a "How to use Microsft Access" book around at work). I absolutely love SQL, and am in the middle of writing a book on how to use just SQLite for large, c…

When I graduated University in 2003, I took a front desk job doing business reporting and running adhoc queries. I did this work for many years in a variety of places, always working with a different type of database in each company. There's so much depth to relational data modeling and SQL. Many, many ways to design queries. I didn't get into hardcore application development until many years later.

I've forgotten some of the more advanced SQL that I've written over the years. I look back at SQL I've written as if I'm reading someone else's work.

I enjoy working with postgres but miss the temporary table workflow in sybase.

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

#99

Post by oarabbus_ »

n4r9 wrote: post #17
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.

This legitimately sounds like a great reason not to use SQL Server, to be honest.

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

#100

Post by avmich »

bshipp wrote: post #22
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? Exc…

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

I'd recommend trying J. Download here - https://code.jsoftware.com/wiki/System/Installation#Installa... . There is, of course, the famous barrier to entry, but the result, unsurprisingly, is also respectable.

Post reply on HN