sql-tstring 0.4.0

SQL builder via string templates

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.

Rewriting values

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.

Paramstyle (dialect)

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"))

Pre Python 3.14 usage

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,

from sql_tstring import sql

a = 1

query, values = sql(
    """SELECT a, b, c
         FROM tbl
        WHERE a = {a}""",
    locals(),
)

Please note though that only simple variable identifiers can be placed within the braces.