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

81–90 of 115 posts

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

#81

Post by gamesbrainiac »

whalesalad wrote: post #80
gamesbrainiac wrote: post #78

If you are working a lot with databases, and would like to automate some of the workflow in python, I'd highly recommend Pony ORM, which really feels like LINQ for python, and feels very close to the original SQL.

Pony is the LAST product I’d ever recommend to anyone, ever. It’s probably the worst open source software I’ve ever used. That being said, thanks to Pony I was called in to fix a shitshow and it likely earned me an additional 25% in consulting fees... so maybe I do like it in that regard. But in all seriousness, stay far away.

I suggest pony because it would be quite good for people who have a deep understanding of SQL, because the translation is quite nice.

The alternative - SA is really overkill for most things.

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

#82

Post by noobiemcfoob »

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.

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

#83

Post by danso »

Whut wrote: post #74
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'm a data engineer and I learned SQL before I learned python. I started out as a marketing specialist (mostly working in Google AdWords), then I graduated up to BI Analyst, and eventually to data engineer. I've picked up 100% of my python in my current job.

What did you think of Python, and was it difficult to grasp the differences between the two languages/paradigms? I ask because, for myself, any general language I pick up (Ruby,Python,JS,R), I have a good idea of underlying concepts like memory pointers and garbage collection, even if it's all abstracted by the language. With SQL, I have an incredible ignorance of the most basic programmatic concepts, like how to define variables or custom functions. The deepest underlying concept I have of SQL is that it has a query planner that does all the thinking for me.

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

#84

Post by noobiemcfoob »

teej wrote: post #40
fsloth wrote: post #34

Is 'analyst' an actual job title or just one skill required in a job? What does an 'analyst' do?

This article is talking about a role typically called “data analyst”. They help product managers, marketing, operations, etc by running reports, building dashboards, (business intelligence), building data models, running an exploratory analysis, analyzing A/B tests (product analytics) or things like building marketing attribution models or timeseries forecasting. It’s the job that’s 80% of what a data scientist does…

A Data Analyst is a Data Scientist who can't prove Bayes Theorem.

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

#85

Post by whalesalad »

gamesbrainiac wrote: post #81
whalesalad wrote: post #80

Earlier quoted context omitted.

Pony is the LAST product I’d ever recommend to anyone, ever. It’s probably the worst open source software I’ve ever used. That being said, thanks to Pony I was called in to fix a shitshow and it likely earned me an additional 25% in consulting fees... so maybe I do like it in that regard. But in all seriousness, stay far away.

I suggest pony because it would be quite good for people who have a deep understanding of SQL, because the translation is quite nice. The alternative - SA is really overkill for most things.

SQLAlchemy is a buzzkill but it’s not as heinous as Pony which is a straight up ticking time bomb in your project. Read the source code for 2 minutes and it should be enough to shy you away.

As far as lightweight Python ORMs, I really enjoy Peewee and it’s usually my first choice.

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

#86

Post by gamesbrainiac »

whalesalad wrote: post #85
gamesbrainiac wrote: post #81

Earlier quoted context omitted.

I suggest pony because it would be quite good for people who have a deep understanding of SQL, because the translation is quite nice. The alternative - SA is really overkill for most things.

SQLAlchemy is a buzzkill but it’s not as heinous as Pony which is a straight up ticking time bomb in your project. Read the source code for 2 minutes and it should be enough to shy you away. As far as lightweight Python ORMs, I really enjoy Peewee and it’s usually my first choice.

I tried PeeWee, but it does not have support for SQLServer, which I need to work with on a daily basis.

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

#87

Post by mongol »

merlincorey wrote: post #53
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

Maybe I've not fully understood pivot tables (quite possible), but this example[0] seems to support my understanding... aren't pivot tables basically just aggregate functions such as `sum` and `avg` applied to a window of the table data (pivoted by rows -> columns)? Some of the executives I work with like to relate all their work in Excel and they just love pivot tables. I showed one of them the output of a table of…

If you know SQL well, you have a strong toolbox for sure. But pivot tables are strong too, especially in exploratory "slicing and dicing" of data. It is slightly faster for some purposes and that makes it more convenient. I tend to use SQL for the heavy lifting and then explore further with pivot tables.

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

#88

Post by iagovar »

bshipp wrote: post #56
merlincorey wrote: post #53

Earlier quoted context omitted.

Maybe I've not fully understood pivot tables (quite possible), but this example[0] seems to support my understanding... aren't pivot tables basically just aggregate functions such as `sum` and `avg` applied to a window of the table data (pivoted by rows -> columns)? Some of the executives I work with like to relate all their work in Excel and they just love pivot tables. I showed one of them the output of a table of…

You're absolutely right. As someone coming into Python and SQL from a purely Excel background, the first realization I had was that I had been, in essence, programming Excel. Not in a rational VBA kind of way, but in how I was linking all of my cells (and sheets and books) and then using pivot tables, etc., to extend Excel beyond a single column/row perspective. Microsoft PowerPivot (or whatever it used to be called)…

Well, because Excel is quick. I know R and SQL, and I still use Excel a lot.

If I want a quick overview of a problem, query db from Excel -> PowerQuery -> PivotTables. It's easy to use, it's very easy to find solutions in google for PowerQuery, and it just feels slick.

If I need in depth analysis then is when I switch to programming.

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

#90

Post by PeterisP »

merlincorey wrote: post #53
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

Maybe I've not fully understood pivot tables (quite possible), but this example[0] seems to support my understanding... aren't pivot tables basically just aggregate functions such as `sum` and `avg` applied to a window of the table data (pivoted by rows -> columns)? Some of the executives I work with like to relate all their work in Excel and they just love pivot tables. I showed one of them the output of a table of…

Pivot tables are quite useful for quick ad-hoc exploration of various different group-by structures with immediate feedback, empowering end-users who wouldn't be able to make the queries themselves, and waiting for someone else to do it would make it not worth it for exploration.

If the data allows that, then a data export (usually requiring a bunch of aggregation and preprocessing) to a format suitable for excel pivot tables or some of the many business analytics tools often is quite useful to users who want to analyze the data.

However, it should be expected that a side-effect of such ad-hoc data analysis often is specific reports that become understood to be useful, and which can then be re-implemented "properly" i.e. as a sql query/report that gets run in an automated, tested, reliable way and delivered where needed without that manual analysis step.

Post reply on HN