On Sun, Dec 09, 2018 at 02:23:48PM -0600, Justin Pryzby wrote:
> >>> d.query('prepare c AS INSERT INTO t VALUES($1)') -- note postgres $1
> >>> parameter
>
> But this fails:
>
> >>> d.query_formatted('EXECUTE c(%s)', [1])
On Thu, Dec 13, 2018 at 10:46:15PM +0100, Christoph Zwerschke wrote:
> Hi Justin,
>
> unfortunately PyGres does not yet support prepared statements with parameter
> passing. I think this can only be solved by adding support for
> PQExecPrepared in the C module.
Maybe it's easier than that..
I saw two issues. First, EXECUTE %s wasn't "adapted" to $1 (since there were
no parameters passed).
Second, execute failed with inline=False.
But actually, SQL EXECUTE doesn't accept parameters, they MUST be inline.
https://www.postgresql.org/docs/11/sql-execute.html
So I think all that's needed for minimal support is a handful lines in DB class
to make PREPARE adapt %s without params:
- if isinstance(values, (list, tuple)):
+ if prepare:
+ literals = []
+ for _ in values:
+ literals.append(params.add(None))
+ params=[] # Do not send params during PREPARE
+ # Or just do this:
+ literals = ['$%s'%x for x in range(1,1+len(values))]
+ command %= tuple(literals)
+ elif isinstance(values, (list, tuple)):
[pryzbyj@telsasoft PyGreSQL]$ PYTHONPATH=`pwd` strace -fe sendto python -c
"import pg; d=pg.DB('ts'); d.query_formatted('prepare x AS SELECT %s',
range(1), prepare=True); d.query_formatted('execute x(%s)', [1], inline=True)"
Process 92304 attached
[pid 92304] +++ exited with 0 +++
--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=92304, si_status=0,
si_utime=0, si_stime=0} ---
sendto(4, "\0\0\0\"\0\3\0\0user\0pryzbyj\0database\0ts"..., 34, MSG_NOSIGNAL,
NULL, 0) = 34
sendto(4, "Q\0\0\0\33prepare x AS SELECT $1\0", 28, MSG_NOSIGNAL, NULL, 0) = 28
sendto(4, "Q\0\0\0\21execute x(1)\0", 18, MSG_NOSIGNAL, NULL, 0) = 18
I suspect maybe you have some deeper goal than just running db.query('prepare
...'), but that would be sufficient for me.
The other mechanism to prepare a statement is PQexecPrepared. It *may* be
that's useful for performance, since that sends an array of params, rather than
comma-separated values which need to be parsed. And that's what I had in mind
when I tried to pass inline=False.
Justin
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo/pygresql
