Django Postgres psycopg2 wrong script generated - quoting of table names

Question

Django 1.5
PostgreSQL 9.2
psycopg2 2.4.6

I'm using extra feature from QuerySet API to be able to employ functions from cube extension for Postgres - I know that extra is not very good for portability reasons, but I'm not going to use another DB anyway (not after Postgres, no!). So the problem is that I'm getting wrong SQL query from this code:

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

It seems that psycopg2 surrounds table names with single quotes which is not correct for Postgres, in script being executed I can see this:

round(earth_distance(ll_to_earth(%s, %s), ll_to_earth('item_place'.latitude, 'item_place'.longitude))::numeric, 0)

I should use table name, because I have latitude and longitude in another table and without it I will get "ambigous column" error. Now I don't know, perhaps I'm doing smth totally wrong and that's why I'm getting this error or maybe it's a bug in psycopg2? Any ideas?

score 2 · Accepted Answer · edited May 23 '17 at 11:49

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])

I admit that I was really wrong. Thank you very much for pointing that out - you've just saved my day! — Ivan, Mar 28 '13 at 22:38

Django Postgres psycopg2 wrong script generated - quoting of table names

1 Answers1