In
psycopg2, variables can be inserted into queries using
%s placeholders and supplying arguments to
cursor.execute, but this does not allow for identifier arguments such as table or columns names. The alternative is to use
psycopg2.sql.SQL.format, but this requires arguments to be converted into
Composable objects such as
Literal or
Identifier.
With TemplateQuery, instead of wrapping arguments with psycopg2.sql classes (e.g. Literal, Identifier) the expected class can be written inside the query:
>>> TemplateQuery('SELECT * FROM {table@Q} WHERE {@I} {@S} {value@L}').format(
... 'column_name', '>=', table='public.my_table', value=100
... ).as_string(conn)
'SELECT * FROM "public"."my_table" WHERE "column_name" >= 100'
The character after the at sign
@ in the placeholder key is the transformation that is applied to the argument before inserting it into the placeholder (
S for raw SQL,
I for Identifiers and
L for literals, among others).
I wrote this small library while working on my master's thesis to help run similar analysis queries on different tables and schema configurations, without the mental overhead of wrapping arguments in the proper classes. I'm not sure if it will be useful to anyone, but I thought it was worth releasing. Would appreciate any feedback at all!
