Cursor: How to quote column name in cursor for mysql AND postgres

Hi,

I have SQL query (inside a migration) that works fine on MySQL:

cursor.execute('UPDATE dojo_finding SET mitigated = CASE WHEN last_reviewed is not NULL THEN last_reviewed ELSE %s END WHERE is_Mitigated = 1 AND mitigated IS NULL', [timezone.now()])

But it fails on Postgres because Postgres converts all unquoted column names to lowercase:

django.db.utils.ProgrammingError: column "is_mitigated" does not exist
LINE 1: ...04T18:58:45.581502+00:00'::timestamptz END WHERE `is_Mitigat...
                                                             ^
HINT:  Perhaps you meant to reference the column "dojo_finding.is_Mitigated".

Quoting the column, and changing 1 to True, makes the query work on Postgres:

cursor.execute('UPDATE dojo_finding SET mitigated = CASE WHEN last_reviewed is not NULL THEN last_reviewed ELSE %s END WHERE "is_Mitigated" = True AND mitigated IS NULL', [timezone.now()])

But this query fails on MySQL:

django.db.utils.OperationalError: (1292, "Truncated incorrect DOUBLE value: 'is_Mitigated'")

I tried backquotes, `is_Mitigated`. This works in MySQL, but not in Postgres.

Is there a way to quote the column name which works both in MySQL and Postgres?

Valentijn

Use a RunPython operation to execute different SQL strings on each backend.

Also, why are you writing a raw SQL migration that runs on two backends?

Yeah I can check ‘connection.vendor’ and use an appropriate query, but that feels dirty.

The migration is part of a product that supports both MySQL and Postgres.