t-strings for parameterized quries

[doerwalter]

Are there plans to add support for using t-strings (introduced with PEP 750 in Python 3.14) for parameterized queries?

Currently doing a parameterized queries looks somewhat like this:

per_id = 42
cursor.execute("select * from person where per_id=:per_id", per_id=per_id)

The code contains the part per_id four times. Using t-strings can reduce this to two:

per_id = 42
cursor.execute(t"select * from person where per_id={per_id}")

psycopg supports t-string too: https://www.psycopg.org/psycopg3/docs/basic/tstrings.html

A function that executes Oracle queries specified as t-strings might look like this:

def execute(cursor, template):
	query = []
	args = {}
	for part in template:
		if isinstance(part, str):
			query.append(part)
		else:
			argname = f"p{len(args)+1}"
			query.append(f":{argname}")
			args[argname] = part.value
	return cursor.exexute("".join(query), args)

and can be called like this:

execute((cursor, t"select * from person where per_id={per_id}")

[doerwalter]

I've implemented that feature in ll-xist here:

https://github.com/LivingLogic/LivingLogic.Python.xist/blob/master/src/ll/orasql/__init__.py#L868

with two additions:

• The l format specifier can be used to embed things literally;
• Interpolated values and normal bind parameters can be mixed.

Feel free to copy that implementation.

[anthony-tuininga]

Thanks! I'll take a look.

[doerwalter]

Another enhancement: recursive t-strings, i.e. interpolattions can be t-string themselves and will be embedded recursively:

	def execute(self, statement, parameters=None, **kwargs):
		"""
		Executes the statement ``statement`` against the database.

		This works similar to :meth:`oracledb.Cursor.execute`, but results are
		wrapped in :class:`Record` objects, and t-strings are supported for
		``statement``.

		If ``statement`` is a t-string (i.e. a
		:class:`string.templatelib.Template`), each interpolated value will be
		passed to the database as a bind parameter, i.e.::

			cursor.execute(t"select * from person where firstname = {name}")

		is equivalent to::

			cursor.execute(
				"select * from person where firstname = :p1",
				p1=name
			)

		The generated parameter names are ``p1``, ``p2``, etc. (skipping any
		names that are already used in ``parameters`` or ``kwargs``).

		However, if an interpolation uses the format spec ``l``, its value will
		not be passed as a bind parameter, but will be embedded literally into
		the SQL statement (which of course should only be done with trusted
		values, as this opens the door for SQL injection)::

			tablename = "person"
			cursor.execute(t"select count(*) from {tablename:l}")

		executes::

			select count(*) from person

		A literal value is embedded by converting it to a string via
		:class:`str`, with one exception: ``None`` is embedded as nothing at
		all (i.e. it contributes an empty string to the statement), so
		``t"select 42 from dual{where:l}"`` produces ``select 42 from dual``
		when ``where`` is ``None``. This makes it easy to conditionally include
		parts of a statement. Note that this means ``None`` is *not* embedded
		as the SQL keyword ``null``; to get a SQL ``NULL`` use a normal bind
		parameter interpolation instead, i.e. ``t"select {None} from dual"``
		(which binds ``None`` as ``null``).

		Interpolations can also be t-strings themselves. In this case the nested
		t-string will be embedded into the SQL statement recursively, with its
		own interpolations turned into bind parameters (or literal parts) in the
		same way. As with literal values, this recursive embedding only happens
		when the interpolation uses the format spec ``l`` and no conversion::

			name = "Doe"
			condition = t"lastname = {name}"
			cursor.execute(t"select * from person where {condition:l}")

		is equivalent to::

			cursor.execute(
				"select * from person where lastname = :p1",
				p1=name
			)

		Both types of interpolations can be combined in one statement and can
		be mixed with additional bind parameters passed via ``parameters`` or
		``kwargs``.
		"""
		if isinstance(statement, templatelib.Template):
			real_statement = []
			first_index = 1

			def append(part):
				nonlocal first_index

				if isinstance(part, str):
					real_statement.append(part)
				elif part.conversion is None and part.format_spec == "l":
					if isinstance(part.value, templatelib.Template):
						# Embed template content recursively
						for subpart in part.value:
							append(subpart)
					elif part.value is not None:
						# Embed value as a literal part of the query
						real_statement.append(str(part.value))
				else:
					# Use value as a parameter (with an unused parameter name)
					param_name = f"p{first_index}"
					while param_name in kwargs or (isinstance(parameters, dict) and param_name in parameters):
						first_index += 1
						param_name = f"p{first_index}"
					real_statement.append(f":{param_name}")
					kwargs[param_name] = part.value

			for part in statement:
				append(part)

			statement = "".join(real_statement)
		if parameters is not None:
			result = super().execute(statement, parameters, **kwargs)
		else:
			result = super().execute(statement, **kwargs)
		if self.description is not None:
			self.rowfactory = RecordMaker(self)
		return result

[doerwalter]

OK, I've released XIST 5.87, where ll.orasql now supports the specifier q instead of l for embedding strings and t-strings literally, and l is used for embedding variables as SQL literals (this is supported by pyscopg too).

Here is the docstring of orassql.Cursor.execute():

---

Executes the statement statement against the database.

This works similar to oracledb.Cursor.execute(), but results are wrapped in Record objects, and t-strings are supported for statement.

If statement is a t-string (i.e. a string.templatelib.Template object), each interpolated value will be passed to the database as a bind parameter, i.e.:

name = "Doe"
cursor.execute(t"select * from person where lastname = {name}")

is equivalent to:

name = "Doe"
cursor.execute(
	"select * from person where lastname = :p1",
	p1=name
)

The generated parameter names are p1, p2, etc. (skipping any names that are already used in parameters or kwargs).

However, if an interpolation uses the format spec q, its value will not be passed as a bind parameter, but will be embedded literally into the SQL statement (which of course should only be done with trusted values, as this opens the door for SQL injection):

tablename = "person"
cursor.execute(t"select count(*) from {tablename:q}")

is equivalent to

cursor.execute("select count(*) from person")

A literal value is embedded by converting it to a string via str, with one exception: None is embedded as nothing at all (i.e. it contributes an empty string to the statement), so

where = None
cursor.execute(t"select 42 from dual{where:q}")

is equivalent to

cursor.execute("select 42 from dual")

This makes it easy to conditionally include parts of a statement. Note that this means None is not embedded as the SQL keyword null; to get a SQL NULL use a normal bind parameter interpolation instead, i.e. t"select {None} from dual" (which binds None as null).

The format spec l works similar to q in that the value is embedded literally into the SQL statement instead of being passed as a bind parameter. However, instead of simply converting the value to a string, it is converted to a proper SQL literal via sqlliteral(), so

name = "Doe"
cursor.execute(t"select * from person where lastname = {name:l}")

is equivalent to

cursor.execute("select * from person where lastname = 'Doe'")

In contrast to q, here None is embedded as the SQL keyword null. The supported types are those handled by sqlliteral() (None, int, float, datetime.date, datetime.datetime and str); any other type raises a TypeError. As with q, this embeds the value directly into the statement, so it should only be done with trusted values.

For a normal value comparison in a query you would normally use a bind parameter interpolation (i.e. {name}), which is safer and allows the database to reuse the execution plan. l is useful in those places where Oracle does not allow bind parameters, but the value still has to be embedded into the statement with proper quoting and type conversion. This is most often the case in DDL statements, e.g. when adding a check constraint:

mindate = datetime.date(2000, 1, 1)
cursor.execute(t'''
	alter table person
	add constraint person_birthday_ck
	check (birthday >= {mindate:l})
''')

is equivalent to:

cursor.execute(
	"alter table person "
	"add constraint person_birthday_ck "
	"check (birthday >= to_date('2000-01-01', 'YYYY-MM-DD'))"
)

Using a bind parameter ({mindate}) here would fail, since Oracle doesn't allow bind parameters in DDL, and using q would embed the date as the invalid SQL fragment 2000-01-01. The same applies to other positions that require literals, such as partition bounds or default clauses. l also takes care of quoting strings correctly, so {"O'Brien":l} produces the properly escaped literal 'O''Brien' instead of the broken O'Brien that q would produce.

Interpolations can also be t-strings themselves. In this case the nested t-string will be embedded into the SQL statement recursively, with its own interpolations turned into bind parameters (or literal parts) in the same way. Like the embedding of other literal values, this recursive embedding is triggered by the format spec q (with no conversion):

name = "Doe"
condition = t"lastname = {name}"
cursor.execute(t"select * from person where {condition:q}")

is equivalent to:

name = "Doe"
cursor.execute(
	"select * from person where lastname = :p1",
	p1=name
)

All types of interpolations can be combined in one statement and can be mixed with additional bind parameters passed via parameters or kwargs.

[anthony-tuininga]

Thanks for the update. t-strings will be a great enhancement to take advantage of. :-)

[cjbj]

@doerwalter did you look at using enquote_literal(), enquote_name(), is_qualified_sql_name() and is_simple_sql_name(), as appropriate?

[doerwalter]

@doerwalter did you look at using enquote_literal(), enquote_name(), is_qualified_sql_name() and is_simple_sql_name(), as appropriate?

I didn't know that they exist (since 4.0.0 it seems). Thank for the tip.

I always was wondering whether

"'" + value.replace("'", "''") + "'"

is really enough to format a Python string as a proper SQL string literal and that no other Unicode character can break that. If this is the official Oracle string literal escape function, I guess I can stop wondering now. ;)

I'll use that in to sqlliteral function:

def sqlliteral(value):
	"""
	Convert `value` to an SQL literal.

	The following types are supported: ``None``, :class:`int`, :class:`float`,
	:class:`datetime.date`, :class:`datetime.datetime` and :class:`str`.
	All other types raise a :exc:`TypeError`.

	For example::

		>>> print(orasql.sqlliteral(None))
		null
		>>> print(orasql.sqlliteral('foo'))
		'foo'
		>>> print(orasql.sqlliteral("foo'bar"))
		'foo''bar'
		>>> print(orasql.sqlliteral(datetime.date(2000, 2, 29)))
		to_date('2000-02-29', 'YYYY-MM-DD')
	"""

	if value is None:
		return "null"
	elif isinstance(value, (int, float)):
		return str(value)
	elif isinstance(value, datetime.datetime):
		return f"to_date('{value:%Y-%m-%d %H:%M:%S}', 'YYYY-MM-DD HH24:MI:SS')"
	elif isinstance(value, datetime.date):
		return f"to_date('{value:%Y-%m-%d}', 'YYYY-MM-DD')"
	elif isinstance(value, str):
		return enquote_literal(value)
	else:
		raise TypeError(f"unknown type {type(value)!r}")

I guess these are all types that have a simple conversion to Oracle literals without additional types defined in the database.

Or are there any other? What about lists, sets, tuples or dicts?

[anthony-tuininga]

I am looking into this now and I think the following makes sense:

• when using a template, no additional parameters are permitted; mixing and matching (some bind variables specified directly and some coming from the template) is going to be a source of confusion at best! I see your code allows for it but doesn't cover every possible scenario. Do you see a need for this and the extra complexity that is required to support it?
• format specifier "i" for identifiers and "l" for literals makes sense (even though lowercase L looks like the number one in some fonts)
• format specifier "q" for nested templates makes sense; all other uses should be strongly warned against!
• use positional binding instead of named binding since the Oracle Database uses positional binding anyway

[anthony-tuininga]

I have pushed a commit that should implement this enhancement and have initated a build from which you can download pre-built development wheels once it completes. You can also build from source if you prefer. If you can test your scenario and confirm the commit works as expected, that would be appreciated!

[doerwalter]

I am looking into this now and I think the following makes sense:

* when using a template, no additional parameters are permitted; mixing and matching (some bind variables specified directly and some coming from the template) is going to be a source of confusion at best! I see your code allows for it but doesn't cover every possible scenario. Do you see a need for this and the extra complexity that is required to support it?

It might be useful to support a mixture of t-strings and keyword arguments when there are highly dynamic queries where passing keyword arguments might still be simpler that t-strings (especially since t-strings don't support some useful string methods (yet?). For examples I can't do:

t" and ".join(t"{k:q} = {v}" for (k, v) in foo.items())

Also when a bind parameter is used multiple times in the SQL, I'm not sure whether passing it only once via a keyword argument has any performance or other advantages compared to t-strings where it has to be interpolated multiple times.

• format specifier "i" for identifiers and "l" for literals makes sense (even though lowercase L looks like the number one in some fonts)

• format specifier "q" for nested templates makes sense; all other uses should be strongly warned against!

Just to be clear we're talking about the same things here:

• Identifiers would be table and column names that might need to be quoted if they contain special characters.
• Literals would be Python objects (strings, dates etc.) where corresponding types exist in the Oracle database. i would format those objects as the matching SQL literals, i.e. t"{"foo":l}" would be equivalent to 'foo', t"{datetime.date(2000, 2, 29):l}" would be equivalent to date '2000-02-29' and t"{None:l}" would be equivalent to null (and maybe stuff like t"{datetime.datetime(2000, 2, 29, 12, 34, 56):l}" to to_date('2000-02-29 12:34:56', 'YYYY-MM-DD HH24:MI:SS').
• q would be used for constructing queries from parts like

  t"select {fields:q} from {tables:q} where {where:q} order by {orderby:q}"

I don't know if only other t-strings should be supported for the q format specifier, or if normal strings should be allowed as well.

There's not much difference between:

t"select foo from bar order by baz {'asc' if asc else 'desc':q}"

and

t"select foo from bar order by baz {t'asc' if asc else t'desc':q}"

so disallowing normal strings would probably be OK. But then again: Why should be disallow normal strings? For security reasons?

Using l shouldn't be a problem, since it is visually distinct from all other valid format specifiers, since t"{foo:1}" is invalid anyway.

• use positional binding instead of named binding since the Oracle Database uses positional binding anyway

I didn't know that, but it kind of makes sense since such a parameter can't appear in a second position anyway.

[doerwalter]

I have pushed a commit that should implement this enhancement and have initated a build from which you can download pre-built development wheels once it completes. ...

I haven't tested that extensively, but what I've tested worked.

[doerwalter]

I discovered a typo in 1bdc7a0

parmaeters in src/oracledb/errors.py:

    ERR_TEMPLATE_WITH_DIRECT_PARAMETERS: (
        "directly specified parmaeters cannot be used with templates"
    ),