[Answered ]-Django Postgres psycopg2 wrong script generated – quoting of table names

by

2👍

✅

According to the doc, the params and select_params are used to indicate Psycopg2 to quote parameters. It is not for quoting table names (which is done by double-quoting).

Quote the doc of Psycopg2:

Only variable values should be bound via this method: it shouldn’t be
used to set table or field names. For these elements, ordinary string
formatting should be used before running execute().

Furthermore, we normally wouldn’t use identifiers that need to be double-quoted as table names, ref the comment of this answer. Thus its safe to use table names directly in your code:

return self.select_related('item_place').extra(
        select={ 'distance': 'round(earth_distance(ll_to_earth(%s, %s), ll_to_earth({tbl}.latitude, {tbl}.longitude))::numeric, 0)'.format(tbl=ItemPlace._meta.db_table) },
        select_params=[latitude, longitude],
        where=['round(earth_distance(ll_to_earth(%s, %s), ll_to_earth({tbl}.latitude, {tbl}.longitude))::numeric, 0) <= %s'.format(tbl=ItemPlace._meta.db_table)],
        params=[latitude, longitude])
👤okm

Leave a comment