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

9 hours ago 2

SQL-tString allows for t-string based construction of sql queries without allowing for SQL injection. The basic usage is as follows,

from sql_tstring import sql a = 1 query, values = sql( t"""SELECT a, b, c FROM tbl WHERE a = {a}""", )

The query is a str and values a list[Any], both are then typically passed to a DB connection. Note the parameters can only be identifiers that identify variables (in the above example in the locals()) e.g. {a - 1} is not valid.

SQL-tString will convert parameters to SQL placeholders where appropriate. In other locations SQL-tString will allow pre defined column or table names to be used,

from sql_tstring import sql, sql_context col = "a" table = "tbl" with sql_context(columns={"a"}, tables={"tbl"}): query, values = sql( t"SELECT {col} FROM {table}", )

If the value of col or table does not match the valid values given to the sql_context function an error will be raised.

SQL-tString will also remove parameters if they are set to the special value of Absent (or RewritingValue.Absent). This is most useful for optional updates, or conditionals,

from sql_tstring import Absent, sql a = Absent b = Absent query, values = sql( t"""UPDATE tbl SET a = {a}, b = 1 WHERE b = {b}""", )

As both a and b are Absent the above query will be UPDATE tbl SET b =1.

In addition for conditionals the values IsNull (or RewritingValue.IS_NULL) and IsNotNull (or RewritingValue.IS_NOT_NULL) can be used to rewrite the conditional as expected. This is useful as x = NULL is always false in SQL.

By default SQL-tString uses the qmark paramstyle (dialect) but also supports the $ paramstyle or asyncpg dialect. This is best changed globally via,

from sql_tstring import Context, set_context set_context(Context(dialect="asyncpg"))

t-strings were introduced in Python 3.14 via, PEP 750, however this library can be used with Python 3.12 and 3.13 as follows,