How to do multi-line f strings?

Python Help
24

It sounds like you have a database backend that does not support named parameters, in which case you can create a helper function that converts an execute method that accepts only positional parameters into a wrapper function that accepts named parameters. Use a regex pattern that grabs the name after a colon but ignores quoted strings and comments:

import re
import sys

def to_positional(func, _pattern=re.compile(r"'(?:''|[^'])*'|--.*|:(\w+)")):
    def execute(operation, namespace=sys._getframe(1).f_locals):
        def replacer(match):
            if name := match[1]:
                params.append(namespace[name])
                return '?'
            return match[0]
        params = []
        return func(_pattern.sub(replacer, operation), params)
    return execute

so that you can call the wrapped execute method with a query with named parameters. The wrapper uses names and values from the current local namespace by default so it behaves more like an f-string:

sql = """\
UPDATE products
SET
    name=:name,
    price=:price,
    description=':description ''' || :description || ''''
WHERE id=:id; -- :comment
"""
id, name, price, description = 123, 'foo', 99.99, 'foo description'
to_positional(cursor.execute)(sql)

But you can also call the wrapper with your own namespace:

to_positional(cursor.execute)(sql,
    {'id': 123, 'name': 'foo', 'price': 99.99, 'description': 'foo description'})

To more easily test the transfromed query without involving the database, you can make it wrap print instead of cursor.execute:

to_positional(print)(sql)

so it would output:

UPDATE products
SET
    name=?,
    price=?,
    description=':description ''' || ? || ''''
WHERE id=?; -- :comment
 ['foo', 99.99, 'foo description', 123]
1 Like