Re: Pass parameters to cursor.execute('Listen ')?

On Tue, May 24, 2016 at 10:52 AM, Dumitru Melenteanu
<d(dot)melenteanu(at)dekart(dot)com> wrote:
> When performing LISTEN on a channel, passing parameters results in a
> syntax error
>
> >>> cursor.execute('Listen %s', ('hello',))`
>
> ProgrammingError: syntax error at or near "'hello'"
> LINE 1: Listen 'hello'
>
> The only way I could get it to work is to % format it, which seems to be
> very frowned upon in the documentation, with good reason, and would
> prevent using `executemany` for multiple channels.
>
> >>> cursor.execute('Listen %s'% ('hello',))
>
> Is there any way to pass the channel as a parameter?

Currently no. On master there is a quote_ident feature that is what
you need to sanitize your query. If I manage to find some time to wrap
psycopg 2.7 it should be included in that release.

https://github.com/psycopg/psycopg2/pull/359

Alternatively you may write a plpgsql function listen(channel text)
taking the argument as a string and calling "execute(format('LISTEN
%I', channel))" (untested) and call that function instead, using
normal psycopg arguments. The %I in the format() function should
escape bad strings as valid identifiers: see
<http://www.postgresql.org/docs/9.3/static/functions-string.html#FUNCTIONS-STRING-FORMAT>.
This would work with executemany too (the quote_ident() thing wouldn't
work for that).

-- Daniele