Show HN: Inline SQL in any Python program | Hacker News Reader

Show HN: Inline SQL in any Python program

github.com

26 pointsekzhang13 comments

This is awesome. Is there a way to get it to work with e.g. dataclasses-sql? I am thinking of something such as

  stuff: list[mydataclass]
  sql_val^ "select * from stuff where stuff.field = 2"

over any kind of 'fmappable' container; perhaps eg.

  stuff: dict[int, mydataclass]
  stuff2: dict[int, mydataclass]

  sql_val^ "select (s2.val, s.key) from stuff as s, stuff2 as s2 where s.key > s2.key and s2.value != s.value)"

etc

Hm, you're currently able to do this through Pandas.

    stuff: list[mydataclass]

    stuff_df = pd.DataFrame(stuff)
    results = sql^ "SELECT * FROM stuff_df WHERE field = 2"

My guess is that this would be a lot faster than dataclasses-sql due to less conversion overhead, but we'd have to benchmark it to be sure.

Ah interesting. I was thinking along the lines of if you wanted to, say, package up an algorithm as a class, make a ton of these classes/algorithms, and then affix them in a bunch of ways. E.g. "find the algorithms that take in data and spit out predictions, compose them with the algorithms that have a prediction_input field" etc.

Very cool, I have lots of ideas of how to leverage it for windmill.dev. We were using psycopg2 for all our examples but this is much cleaner. Thanks for sharing!

That's great! There's nothing I would be happier to hear than this experiment making someone think of query languages a little differently. :)

Looks really interesting, but why overload the ^ operator? Is a function call really that much more verbose?

Thanks for the feedback orf! I agree it looks strange at first, this is very experimental so I played a bit with syntax design choices. Sometimes for DSLs it's nice to have a bit of flexibility. You can compare it to the %timeit, %cython "magic" statements in Jupyter.

We could use a standard function application, but that doesn't convey the point that there's some DSL embedding going on here, such as variable substitution from the outside scope. It's also less nice to end multi-line queries with the string: """).

Other reasons: the bitwise XOR operator is almost never used in Python, so it's highly unlikely that people would confuse it as such (this is also why it's formatted without a space before the operator). Open to other proposals for syntax though.

These are good points, especially around the magic substitution. I hadn’t really considered that.

Even so, pandas has the same magic substitution going on with just a standard function call, and it’s somewhat intuitive.

However, regardless of the syntax nitpicking, this is a cool project. Nice one!

Something was not clear to me from the README. When you do string interpolations as in your examples, those are actually prepared sql statements right ? Otherwise it would open the way for sql injections.

Yep, they're prepared SQL statements. Literal string interpolation would be unacceptable in any self-respecting SQL library: it's parameters. Good question though!

This is great. I have a personal-use version of this (also using DuckDB's fantastic Pandas compatibility) and will see if I can just switch over.

Python and Haskell programmers at this point are just bored. All python and Haskell programmers need to start being developers.

Boredom in the right hands leads to works of tremendous beauty

Reply on news.ycombinator.com