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

Show HN: Csql – Python lib for composeable SQL queries

github.com

Post reply on HN
Print view View original

11–20 of 41 posts

Re: Show HN: Csql – Python lib for composeable SQL queries

#11

Post by akdor1154 »

ramraj07 wrote: post #7
akdor1154 wrote: post #2

As an analyst, I often am faced with a choice between - write a giant unmaintainable SQL query, or - pull everything to my PC and use pandas, to take advantage of its ability to build up results piece by piece. I wrote this library to try and enable that piece-by-piece development approach with SQL queries, without resorting to the mental overhead of full on query builders like SQLAlchemy or Linq. Seeking feedback -…

Have you tried dbt? Even if you don't want to adapt their "all in SQL" idea, I definitely took inspiration from their Jinja templating idea and have similar libraries as you, except it uses Jinja templating to parameterize the SQL. This allows more logic to be put in the queries than just variable names!

Huh, no I haven't come across dbt. Looks interesting! Jinja'd sql scares me a bit, but to be fair the string interpolation in my lib here scares me as well :)

Re: Show HN: Csql – Python lib for composeable SQL queries

#12

Post by chaz6 »

akdor1154 wrote: post #2

As an analyst, I often am faced with a choice between - write a giant unmaintainable SQL query, or - pull everything to my PC and use pandas, to take advantage of its ability to build up results piece by piece. I wrote this library to try and enable that piece-by-piece development approach with SQL queries, without resorting to the mental overhead of full on query builders like SQLAlchemy or Linq. Seeking feedback -…

As someone in a similar position, my first impression is that this is going to be very useful in saving time coming up with big sql monstrosities. I will be following your project with interest and hope to be able to use it in the daily grind.

Re: Show HN: Csql – Python lib for composeable SQL queries

#14

Post by akdor1154 »

isoprophlex wrote: post #6

Very cool and useful stuff, but aren't CTEs optimization fences in postgres? If you're dealing with largeish tables and several linked CTEs this might get too slow, and you're still stuck on optimizing your queries manually. AFAIK subqueries do allow predicate pushdown, etc. Maybe for postgres, composability can be achieved through subqueries. Nevertheless very cool stuff!

Hmm, interesting and good to know, thanks. (and damn, subqueries are awful to read :( ) If there was appetite, it would be straightforward to make `csql` work by generating subqueries instead, might be something to add to the TODO list!

Re: Show HN: Csql – Python lib for composeable SQL queries

#16

Post by throwaway_pdp09 »

lbj wrote: post #5

Looks like theyre headed in the right direction. ORMs are tricky to get right, when I wrote ClojureQL I looked more towards combinatorics than SQL: https://clojureql.sabrecms.com/en/examples

Your example of multiple sorts is problematic.

> SELECT * FROM (SELECT users.* FROM users ORDER BY users.id asc) ORDER BY users.id desc

A sort should never (for some value of never) appear in a subquery because it's meaningless; it can't affect the result. In tsql it's explicitly checked for and reported.

Not having a go, just fyi.

Edit: now I'm more confused by that. The bracketed subquery doesn't have a name but it's apparnetly called 'users' because the last clause is 'ORDER BY users.id desc' - but that's illegal. Again tsql correctly rejects that (once I comment out the illegal inner order by).

Edit 2: sorry about this but FYI, I'd expect the inner sort order to be disregarded anyway, and assuming this is mysql it explicitly is.

"If ORDER BY occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future MySQL version"

https://dev.mysql.com/doc/refman/5.7/en/select.html

(Actually, what the heck is it saying? An outer order by in the presence of an inner order by is undefined overall??)

Re: Show HN: Csql – Python lib for composeable SQL queries

#17

Post by acidbaseextract »

akdor1154 wrote: post #2

As an analyst, I often am faced with a choice between - write a giant unmaintainable SQL query, or - pull everything to my PC and use pandas, to take advantage of its ability to build up results piece by piece. I wrote this library to try and enable that piece-by-piece development approach with SQL queries, without resorting to the mental overhead of full on query builders like SQLAlchemy or Linq. Seeking feedback -…

If you're an analyst, I second the recommendation for dbt. Here's a podcast interview with the CEO of the company behind dbt that explains a lot of the philosophy, and I think will help you even if you don't end up using dbt: https://softwareengineeringdaily.com/2020/03/09/dbt-data-bui...

Re: Show HN: Csql – Python lib for composeable SQL queries

#18

Post by dkubb »

throwaway_pdp09 wrote: post #16
lbj wrote: post #5

Looks like theyre headed in the right direction. ORMs are tricky to get right, when I wrote ClojureQL I looked more towards combinatorics than SQL: https://clojureql.sabrecms.com/en/examples

Your example of multiple sorts is problematic. > SELECT * FROM (SELECT users.* FROM users ORDER BY users.id asc) ORDER BY users.id desc A sort should never (for some value of never) appear in a subquery because it's meaningless; it can't affect the result. In tsql it's explicitly checked for and reported. Not having a go, just fyi. Edit: now I'm more confused by that. The bracketed subquery doesn't have a name but it…

I agree in this specific example the inner sort is meaningless. There are valid cases where a subquery can use an ORDER BY, such as when a LIMIT or OFFSET is specified.

I'd go to far to say that unless a query has an ORDER BY it is likely a bug to use LIMIT or OFFSET. In the absence of an ORDER BY the database is free to sort the rows any way it likes, and this could change between versions or based on any number of implementation details. If it appears to sort determinsitically with no ORDER BY it should not be relied upon.

Re: Show HN: Csql – Python lib for composeable SQL queries

#19

Post by BiteCode_dev »

dfee wrote: post #4

I think this is really neat. It’s seems a bit more flexible than the composition approach of SQLAlchemy, too. I also like that the unit of composition is the CTE.

Can you say more about that?

I though SQLAlchemy core goal was exactly that. When does it fails, compared to Csql?

Re: Show HN: Csql – Python lib for composeable SQL queries

#20

Post by throwaway_pdp09 »

dkubb wrote: post #18
throwaway_pdp09 wrote: post #16

Earlier quoted context omitted.

Your example of multiple sorts is problematic. > SELECT * FROM (SELECT users.* FROM users ORDER BY users.id asc) ORDER BY users.id desc A sort should never (for some value of never) appear in a subquery because it's meaningless; it can't affect the result. In tsql it's explicitly checked for and reported. Not having a go, just fyi. Edit: now I'm more confused by that. The bracketed subquery doesn't have a name but it…

I agree in this specific example the inner sort is meaningless. There are valid cases where a subquery can use an ORDER BY, such as when a LIMIT or OFFSET is specified. I'd go to far to say that unless a query has an ORDER BY it is likely a bug to use LIMIT or OFFSET. In the absence of an ORDER BY the database is free to sort the rows any way it likes, and this could change between versions or based on any number of…

> valid cases where a subquery can use an ORDER BY, such as when a LIMIT or OFFSET is specified.

Oh, quite true! However the output of that subquery, despite having an order by, will not have a guaranteed order. Ordering is lost as the result set leaves the subquery. This is in the sql standard and in most if not all implementations.

> I'd go to far to say that unless a query has an ORDER BY it is likely a bug to use LIMIT or OFFSET

Wholly agreed.

Post reply on HN