
Show HN: Easy placeholders for SQL queries when using psycopg2 in Python - rovyko
https://github.com/pjdon/templatequery
======
rovyko
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!

