Show HN: Csql – Python lib for composeable SQL queries
1–10 of 41 posts
Re: Show HN: Csql – Python lib for composeable SQL queries
#2As 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 - is this useful for you? is it at the right level of abstraction?
Re: Show HN: Csql – Python lib for composeable SQL queries
#3Cool! Thinking so many times in something like this, but sql it's not easy composable...
Re: Show HN: Csql – Python lib for composeable SQL queries
#4I 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.
Re: Show HN: Csql – Python lib for composeable SQL queries
#5Looks 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
Re: Show HN: Csql – Python lib for composeable SQL queries
#6Very 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!
Re: Show HN: Csql – Python lib for composeable SQL queries
#7As 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!
Re: Show HN: Csql – Python lib for composeable SQL queries
#8Very 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!
> aren't CTEs optimization fences in postgres?
Apparently "not all the time with PG12".
https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
Re: Show HN: Csql – Python lib for composeable SQL queries
#9For those of us who didn't know what a CTE was standing for. Here is a link to the PostgreSQL documentation on Common Table Expressions (CTEs)[1], and an article on Wikipedia on Hierarchical and recursive queries, that explains the concept [2].
[1] https://www.postgresql.org/docs/12/queries-with.html
[2] https://en.wikipedia.org/wiki/Hierarchical_and_recursive_que...
edit: formatting
Re: Show HN: Csql – Python lib for composeable SQL queries
#10Very 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!
> aren't CTEs optimization fences in postgres? Apparently "not all the time with PG12". https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
Interesting! I didn't know that, thanks!