Your library looks great. But a tangential rant about t-strings, using lexical scope for placeholder lookup is just a terrible, terrible design. It should be explicitly passed in a dictionary. I'm not sure why they made this decision.
replies(2):
from sql_tstring import sql
val = 2
query, values = sql(t"SELECT x FROM y WHERE x = {val}")
assert query == "SELECT x FROM y WHERE x = ?"
assert values == [2]
db.execute(query, values) # Most DB engines support this
col = "x"
sql(t"SELECT {col} FROM y") # Raises ValueError
from sql_tstring import sql_context
with sql_context(columns="x"):
query, values = sql(t"SELECT {col} FROM y")
assert query == "SELECT x FROM y"
assert values == []
As t-strings are format strings you can safely format the literals you'd like to pass as variables,
text = "world"
query, values = sql(t"SELECT x FROM y WHERE x LIKE '%{text}'")
assert query == "SELECT x FROM y WHERE x LIKE ?"
assert values == ["%world"]
SQL-tString is a SQL builder and as such you can use special RewritingValues to alter and build the query you want at runtime. This is best shown by considering a query you sometimes want to search by one column a, sometimes by b, and sometimes both,
def search(
*,
a: str | AbsentType = Absent,
b: str | AbsentType = Absent
) -> tuple[str, list[str]]:
return sql(t"SELECT x FROM y WHERE a = {a} AND b = {b}")
assert search() == "SELECT x FROM y", []
assert search(a="hello") == "SELECT x FROM y WHERE a = ?", ["hello"]
assert search(b="world") == "SELECT x FROM y WHERE b = ?", ["world"]
assert search(a="hello", b="world") == (
"SELECT x FROM y WHERE a = ? AND b = ?", ["hello", "world"]
)
The other rewriting values I've included are handle the frustrating case of comparing to NULL, for example the following is valid but won't work as you'd likely expect,
optional = None
sql(t"SELECT x FROM y WHERE x = {optional}")
from sql_tstring import IsNull
optional = IsNull
query, values = sql(t"SELECT x FROM y WHERE x = {optional}")
assert query == "SELECT x FROM y WHERE x IS NULL"
assert values == []
The final feature allows for complex query building by nesting a t-string within the existing,
inner = t"x = 'a'"
query, _ = sql(t"SELECT x FROM y WHERE {inner}")
assert query == "SELECT x FROM y WHERE x = 'a'"
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 dictionary literal?
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.
What I’m saying is that, regardless of how it works, I don’t think string templating for SQL is a good idea because it looks almost exactly like string concatenation. It makes more difficult to distinguish beteeen the right approach and the wrong approach (or learn about it)