On Fri, Dec 14, 2018 at 11:11:03AM -0600, Justin Pryzby wrote:
> Find attached patch with proof of concept for minimal implementation of
> prepared statements.
I was looking at what it would take to allow prepared statements in pgdb
module, in addition to pg. I guess that's most of what you were thinking of in
trac#16. I assume you'd want to allow that with the DBAPI2 interface like
executemany, and not added, nonstandard functions.
It turns out pg's query object calls PQexecParams, but pgdb's source object
only ever calls PQexec, and the params have already been interpretted into a
pythong string (like pg.query's inline=True).
I don't know if there's any or good reason why it's like that (?), or why it's
done differently. To implement for pgdb, you'd have to change to PQexecParams
(as you said). You'd suck into pgdb a significant fraction of pg, specifically
the Adapter class.
I don't know why you'd continue to support the current "inline" queries.
...Except if you need it for SQL "execute"... which means you'd NEED an
additional function or parameter or attribute controlling whether it's inlined
or not.
Without adding nonstandard functions, there's a limited amount you can do
within pgdb/DBAPI2. How would you choose between calling PQexecPrepared or
PQexecParams?
If you only called PQexecParams, then queries would be prepared by running
"PREPARE q AS....", same as I proposed for pg. You'd still have to implement
something to change %s to $1 (that could be a module level function or
nonstandard interface?), and send params like inline=False.
Then you'd call PQexecParams again, with SQL "EXECUTE(...)", which needs
parameters "inline".
I think that's where I give up.
However here's the hackaround for pgdb:
import pgdb
c=pgdb.connect(dbname='ts').cursor()
c.execute('prepare c AS SELECT * FROM generate_series(1,%s)',
[pgdb.Literal('$1')])
c.execute('EXECUTE c(3)')
print c.fetchall()
|sendto(4, "Q\0\0\0005prepare c AS SELECT * FROM generate_series(1,$1)\0", 54,
MSG_NOSIGNAL, NULL, 0) = 54
|sendto(4, "Q\0\0\0\21EXECUTE c(3)\0", 18, MSG_NOSIGNAL, NULL, 0) = 18
|[Row(generate_series=1), Row(generate_series=2), Row(generate_series=3)]
I think it'd be good to document a sanctioned way to do that..
..but I wonder if we can do any better than that before documenting it.
Justin
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo/pygresql
