4 ms·
Show HN: Inline SQL in any Python program
- rubenfiszel 4y agoVery 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!
- ekzhang 4y agoThat's great! There's nothing I would be happier to hear than this experiment making someone think of query languages a little differently. :)
- orf 4y agoLooks really interesting, but why overload the ^ operator? Is a function call really that much more verbose?
- ekzhang 4y agoThanks 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.
- orf 4y agoThese 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!
- biggerChris 4y agoPython and Haskell programmers at this point are just bored. All python and Haskell programmers need to start being developers.
- pizza 4y agoBoredom in the right hands leads to works of tremendous beauty
- rubenfiszel 4y agoSomething 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.
- ekzhang 4y agoYep, they're prepared SQL statements. Literal string interpolation would be unacceptable in any self-respecting SQL library: it's parameters. Good question though!
- lazzlazzlazz 4y agoThis 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.
- pizza 4y agoThis 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
- ekzhang 4y agoHm, 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.
- pizza 4y agoAh 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.