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

Show HN: SQL-tString a t-string SQL builder in Python

github.com

Post reply on HN
Print view View original

21–30 of 43 posts

Re: Show HN: SQL-tString a t-string SQL builder in Python

#21

Post by 1_08iu »

schultzer wrote: post #3

Not really sure what a t string is or if it’s a macro, but feel similar to https://github.com/elixir-dbvisor/sql but less elegant and ergonomic.

t-strings (or template strings) are an upcoming Python 3.14 feature. They have similar syntax to f-strings (which were introduced in 3.6) except that they provide access to the string and the interpolated values (the bits inside the curly brackets) before they have been combined. Previously, something like

  db.query(f"SELECT * FROM table WHERE id={id};")
would have been vulnerable to the classic "bobby tables" SQL injection but t-strings allow for almost the same syntax (which is quite natural for Python programmers) without incurring a security risk.

If you are curious, t-strings have previously been discussed here (https://news.ycombinator.com/item?id=43748512 and https://news.ycombinator.com/item?id=43647716) and you can read the PEP that proposed their addition to the language (https://peps.python.org/pep-0750/).

Re: Show HN: SQL-tString a t-string SQL builder in Python

#23

Post by caturopath »

For any of you also confused by

    with sql_context(columns="x"):
        query, values = sql(t"SELECT {col} FROM y")
I think

1. this is relying on the `col = "x"` in the previous example

2. columns is a set of strings, so it might be sql_context(columns={"foo", "bar", "x"}) to allow those as valid options. It just happens that "x" is a collection supporting the `in` operator so it works much like the set {"x"} would.

2a. (You might hope that something would convert such a string to a singleton set, but I don't think it does, which would have weird results with a multi-letter string.)

Re: Show HN: SQL-tString a t-string SQL builder in Python

#24

Post by pgjones »

caturopath wrote: post #23

For any of you also confused by with sql_context(columns="x"): query, values = sql(t"SELECT {col} FROM y") I think 1. this is relying on the `col = "x"` in the previous example 2. columns is a set of strings, so it might be sql_context(columns={"foo", "bar", "x"}) to allow those as valid options. It just happens that "x" is a collection supporting the `in` operator so it works much like the set {"x"} would. 2a. (You…

Sorry that is a typo, I meant,

    with sql_context(columns={"x"}):

Re: Show HN: SQL-tString a t-string SQL builder in Python

#25

Post by westurner »

pgjones wrote: post #10
schultzer wrote: post #9

Just took a quick look, and it seams like the parser is hand written which is great, but you probably want to build a lexer and parser based on the BNF grammar take a look at how I do it here https://github.com/elixir-dbvisor/sql/tree/main/lib and do conformance testing with https://github.com/elliotchance/sqltest

Thanks, do you have a reference for SQL grammar - I've had no success finding an official source.

Ibis has sqlglot for parsing and rewriting SQL query graphs; and there's sql-to-ibis: https://github.com/ibis-project/ibis/issues/9529

sqlglot: https://github.com/tobymao/sqlglot :

> SQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine [written in Python]. It can be used to format SQL or translate between 24 different dialects like DuckDB, Presto / Trino, Spark / Databricks, Snowflake, and BigQuery. It aims to read a wide variety of SQL inputs and output syntactically and semantically correct SQL in the targeted dialects.

Re: Show HN: SQL-tString a t-string SQL builder in Python

#26

Post by 90s_dev »

90s_dev wrote: post #15
jitl wrote: post #8

Earlier quoted context omitted.

If they’re gonna do that why bother making a new concept? You could already build(normalString, someDict) Like why make me state “A goes here, also the value of A is 1” when I can just say “1 goes here”? When I build an array or map, I just write the expression { key1: value1 } I don’t need to write build({ key1, value1 }, { “key1”: key1, “value1”: value1 }) Why should an sql literal be any different from an array or…

Yeah in retrospect it's identical to what JavaScript does with string literals. I don't know what I was thinking.

Oh wait I know why. It's because the PIP had no specialized syntax highlighting to show that it was getting the variables from scope. So I started reasoning about it differently than I do about JS string literals, rather lazily too, and ended up thinking of something like emacs's dynamic scope or something. Amazing what syntax highlighting does to how we think.

Re: Show HN: SQL-tString a t-string SQL builder in Python

#28

Post by jitl »

pgjones wrote: post #13
jitl wrote: post #7

How does the SQL parsing work for the rewrites like removing expressions? I have a project using some non-standard SQL features and we have quite complex queries going on, so the rewriting makes me a bit nervous. The great thing about tstrings for sql is that it’s a total escape from “magick” creating ineffable and unknown sql replacing with very straightforward what you see is what you get sql right in the source co…

The presence of Absent removes the entire expression, and if that removal results in an empty clause (or group) it will remove that as well. For example if `a = Absent` `WHERE a = {a}` will remove everything, whereas `WHERE a = {a} AND b = {b}` will result in `WHERE b = {b}`. > Do you support templating a sql tstring into an sql tstring for composition? Yep

How do you know what the expression is though? Don’t you need to be parsing the SQL? If I have non standard SQL somewhere upstream in the text how does the parser cope?

Re: Show HN: SQL-tString a t-string SQL builder in Python

#29

Post by williamdclt »

hombre_fatal wrote: post #20

I thought this was just going to be the same ol "where id = {id}" interpolation but dang, those are some crazy examples. I can imagine the behavior takes some trial and error to figure out, but it looks like you can write a search() query that contains fully-loaded sql statement as if all facets were provided, yet you can make each facet optional and those expressions will get removed from the statement. That would b…

> the traditional route of building up a where clause with a bunch of if-statements where it's very hard to understand what the final where clause might look like without print(statement).

Seems similar on this front? You also need to print the final SQL to understand what the query looks like, what conditions have been dropped etc.

What you write still isn’t the sql that’s actually executed, it’s some sort of template.

In general I find that the right approach is to avoid the conditional clauses altogether: instead of repository methods with many options, make several dedicated repository methods. You repeat a good amount of sql, but it’s so much simpler, easier to understand what’s happening, closer to the use-case, easier to optimise…

Re: Show HN: SQL-tString a t-string SQL builder in Python

#30

Post by williamdclt »

90s_dev wrote: post #15
jitl wrote: post #8

Earlier quoted context omitted.

If they’re gonna do that why bother making a new concept? You could already build(normalString, someDict) Like why make me state “A goes here, also the value of A is 1” when I can just say “1 goes here”? When I build an array or map, I just write the expression { key1: value1 } I don’t need to write build({ key1, value1 }, { “key1”: key1, “value1”: value1 }) Why should an sql literal be any different from an array or…

Yeah in retrospect it's identical to what JavaScript does with string literals. I don't know what I was thinking.

No I think your point is valid, and is valid in JavaScript too.

Designing the “right” approach to look like the “wrong” approach (string concatenation) is a bad idea, however cute it is.

It’s annoying that the wrong thing is the more ergonomic one, but at least it jumps out at any dev with any experience, they know what sqli risk looks like. With templated strings, it’s not so obvious anymore.

Post reply on HN