Pass dynamic field name to query

[anselal]

Hey,

is it possible to have an insert/update query with dynamic field names like:

async with pool.acquire() as connection:
    async with connection.cursor() as cursor:
        await cursor.execute(
           "INSERT INTO logs (id, %s) VALUES (%s, %s)",
                    ('field_name', value1, value2),
        )

or

async with pool.acquire() as connection:
    async with connection.cursor() as cursor:
        await cursor.execute(
            "update logs set %s=%s", ('field_name', value)
        )

I tried it but I get a

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ....

[Nothing4You]

values are escaped before being passed to the server as strings:

cursor.mogrify("INSERT INTO logs (id, %s) VALUES (%s, %s)", ('field_name', 'foo', 'bar'))

INSERT INTO logs (id, 'field_name') VALUES ('foo', 'bar')

therefore you can't pass column names as args.

[anselal]

I think I asked a stupid question. I think it might work if I pass the field as a f-string variable.
Will try it out and let you know

[Nothing4You]

as long as you format the string before passing it into e.g. cursor.execute() (which f-strings do) that should be fine.
you obviously lose the input sanitization aspect but i hope you don't accept column names from untrusted sources anyway.

[anselal]

No ofc not. I just want to create a short function to call queries to avoid the double nested async with statement.