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.
> And for PQexecPrepared, I added a conditional in query(), since that entire
> function appears to be exactly what's needed for the prepared case, too.
> Here, the hack is that I determine wether to call exec or execPrepared by
> running strstr(sql, "prep"). I'll solicit suggestions for a better way to do
> that now.
I addressed this by refactoring connQuery in the most blindingly simple way.
> I hacked query_formatted to accept an prepare=False.
> The difficulty is in replacing %s with $1, which needs to either have a list
> of
> parameters over which to iterate, or at least an integer determining the
> number
> of dollar parameters in literals. Since PREPARE doesn't send parameters
> (that's quite the point), I passed a range() list as a hack to show that it
> could work.
Any suggestion how to address this ?
The interface currently takes a list which is only used for its length.
It'd be fine to add a different interface for query_prepared, taking just an
int, but that's just as ugly.
All I can think to do is to subclass tuple() and override getitem() and save
the max element which is accessed. It's gross and it's probably for the best
that I couldn't make it work.
So an even grosser idea is to pass tuples of length varying by factors of 2
doing a binary search through except TypeError as e:
untested because I'm sure someone can find something less bad:
tuplen=1
tup=tuple('' for a in range(tuplen))
try:
s%tup
except TypeError as e
if 'not enough arguments' in e.args then
tuplen*=2
elif 'not all arguments converted' in e.args then
tuplen=3*tuplen//4
else:
raise
return tuplen
Finally, do you think it's reasonable to consider releasing an update with
prepared statements in pg but not pgdb ? I don't see a clear path for pgdb, as
I wrote earlier.
[pryzbyj@telsasoft PyGreSQL]$ PYTHONPATH=build/lib.linux-x86_64-2.7 python2.7
-c "import pg; d=pg.DB('ts'); x=d.query_prepared(\"PREPARE x AS SELECT
generate_series(1,%s)\", [1]); print d.execute_prepared('x',3).getresult()"
[(1,), (2,), (3,)]
Justin
diff --git a/pg.py b/pg.py
index e830a66..d480931 100644
--- a/pg.py
+++ b/pg.py
@@ -678,14 +678,23 @@ class Adapter:
params.adapt = self.adapt
return params
- def format_query(self, command, values=None, types=None, inline=False):
+ def format_query(self, command, values=None, types=None, inline=False, prepare=False):
"""Format a database query using the given values and types."""
- if not values:
+ if not values and not prepare:
return command, []
if inline and types:
raise ValueError('Typed parameters must be sent separately')
params = self.parameter_list()
- if isinstance(values, (list, tuple)):
+ if prepare:
+ # In this case, "inline" doesn't make any sense ?
+ 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)):
if inline:
adapt = self.adapt_inline
literals = [adapt(value) for value in values]
@@ -1853,6 +1862,13 @@ class DB:
self._do_debug(command)
return self.db.query(command)
+ def execute_prepared(self, command, *args):
+ # Wraps shared library function for debugging.
+ if not self.db:
+ raise _int_error('Connection is not valid')
+ self._do_debug(command, args)
+ return self.db.execute_prepared(command, args)
+
def query_formatted(self, command,
parameters=None, types=None, inline=False):
"""Execute a formatted SQL command string.
@@ -1869,6 +1885,22 @@ class DB:
return self.query(*self.adapter.format_query(
command, parameters, types, inline))
+ def query_prepared(self, command, parameters=None):
+ """Prepare a statement for execution
+
+ command is a statement of the form "PREPARE name AS ..."
+ which may include parameters using Python format placeholders like as
+ query_formatted.
+
+ The statment can be later executed in two ways:
+ 1. Using SQL calls: query("EXECUTE name [(parameter [, ...])]", inline=True)
+ 2. Using library call to PQexecPrepared:
+
+ """
+ # XXX: check if no parameters sent ?
+ return self.query(*self.adapter.format_query(
+ command, parameters, prepare=True))
+
def pkey(self, table, composite=False, flush=False):
"""Get or set the primary key of a table.
diff --git a/pgmodule.c b/pgmodule.c
index 08ed188..2781b49 100644
--- a/pgmodule.c
+++ b/pgmodule.c
@@ -2140,14 +2140,9 @@ connSource(connObject *self, PyObject *noargs)
return (PyObject *) npgobj;
}
-/* database query */
-static char connQuery__doc__[] =
-"query(sql, [arg]) -- create a new query object for this connection\n\n"
-"You must pass the SQL (string) request and you can optionally pass\n"
-"a tuple with positional parameters.\n";
-
+/* Execute a query or previously prepared statement */
static PyObject *
-connQuery(connObject *self, PyObject *args)
+ connQueryOrPrepared(connObject *self, PyObject *args, int is_prepared)
{
PyObject *query_obj;
PyObject *param_obj = NULL;
@@ -2219,6 +2214,8 @@ connQuery(connObject *self, PyObject *args)
}
}
+ /* TODO: fail if prepared && !param_obj */
+
/* gets result */
if (nparms)
{
@@ -2287,8 +2284,17 @@ connQuery(connObject *self, PyObject *args)
}
Py_BEGIN_ALLOW_THREADS
- result = PQexecParams(self->cnx, query, nparms,
- NULL, parms, NULL, NULL, 0);
+ if (is_prepared) {
+ // In this case, the "query" is just the name of the
+ // prepared statement to execute.
+ // Note, no argument is passed for paramTypes.
+ result = PQexecPrepared(self->cnx, query, nparms,
+ parms, NULL, NULL, 0);
+ } else {
+ result = PQexecParams(self->cnx, query, nparms,
+ NULL/*paramTypes*/, parms, NULL, NULL, 0);
+ }
+
Py_END_ALLOW_THREADS
PyMem_Free((void *)parms);
@@ -2376,6 +2382,29 @@ connQuery(connObject *self, PyObject *args)
return (PyObject *) npgobj;
}
+/* database query */
+static char connQuery__doc__[] =
+"query(sql, [arg]) -- create a new query object for this connection\n\n"
+"You must pass the SQL (string) request and you can optionally pass\n"
+"a tuple with positional parameters.\n";
+static PyObject *
+connQuery(connObject *self, PyObject *args)
+{
+ return connQueryOrPrepared(self, args, 0);
+}
+
+/* execute prepared statement */
+static char connExecPrepared__doc__[] =
+"query(sql, arg) -- create a new query object for this connection\n\n"
+"You must pass the name (string) of previously-prepared statement request and\n"
+"a tuple of parameters.\n";
+// XXX: should allow an empty typle or no params?
+static PyObject *
+connExecPrepared(connObject *self, PyObject *args)
+{
+ return connQueryOrPrepared(self, args, 1);
+}
+
#ifdef DIRECT_ACCESS
static char connPutLine__doc__[] =
"putline(line) -- send a line directly to the backend";
@@ -3414,6 +3443,7 @@ static struct PyMethodDef connMethods[] = {
{"source", (PyCFunction) connSource, METH_NOARGS, connSource__doc__},
{"query", (PyCFunction) connQuery, METH_VARARGS, connQuery__doc__},
+ {"execute_prepared", (PyCFunction) connExecPrepared, METH_VARARGS, connExecPrepared__doc__},
{"reset", (PyCFunction) connReset, METH_NOARGS, connReset__doc__},
{"cancel", (PyCFunction) connCancel, METH_NOARGS, connCancel__doc__},
{"close", (PyCFunction) connClose, METH_NOARGS, connClose__doc__},
_______________________________________________
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql
