On Fri, Dec 21, 2018 at 05:58:43PM -0600, Justin Pryzby wrote:
> On Fri, Dec 14, 2018 at 11:11:03AM -0600, Justin Pryzby wrote:
> > 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.
> I'm sure someone can find something less bad:
I did something slighly less bad, but if there's no agreement on it
I'd advocate for a release with execute_prepared, even without an interface to
converts %s strings to $1 strings.
My solution, since we're not doing string interpretation (same as
inline=False), dict references aren't useful, and %s is literally the only
thing you can write, and then counting it seems to be trivial (if dirty).
Note, execute_prepared is more or less usable from pgdb:
[pryzbyj@telsasoft PyGreSQL]$ PYTHONPATH=build/lib.linux-x86_64-2.7 python2.7
-c "import pgdb; c=pgdb.connect(dbname='ts').cursor(); c.execute(\"PREPARE x AS
SELECT generate_series(1,\$1)\"); print
c._cnx.execute_prepared('x',[3]).getresult()"
[(1,), (2,), (3,)]
I thought this was good reading:
https://www.postgresql.org/docs/10/protocol-flow.html#id-1.10.5.7.4
That's describing the protocol behind the library functions, so somewhat finer
grained than what we need to know.
Please double check that this ons is doing the right thing ?
PYTHONPATH=build/lib.linux-x86_64-2.7 strace -fe sendto -s333 python2.7 -c
"import pg; d=pg.DB('ts'); x=d.prepare_query(\"PREPARE x AS SELECT
'%s',generate_series(1,%s)\"); print d.execute_prepared('x',[1,2]).getresult()"
=>
sendto(4, "\0\0\0\"\0\3\0\0user\0pryzbyj\0database\0ts\0\0", 34, MSG_NOSIGNAL,
NULL, 0) = 34
sendto(4, "Q\0\0\0003PREPARE x AS SELECT '$1',generate_series(1,$2)\0", 52,
MSG_NOSIGNAL, NULL, 0) = 52
...
pg.ProgrammingError: ERROR: could not determine data type of parameter $1
I think that's right since anything sent in the query text will have parameters
substituted..it's within postgres '' quotes, but it hasn't gotten to postgres
yet so that's irrelevant. I think postgres is silly here:
it's trying to check that data type of each of the params is determined, for
example by passing TYPEs, which we don't do, or by INSERTed into a column of
known type, or because it's fine to stay as text. Postgres is dumb for not
first checking that the number of parameters matches the expected number of
arguments.
PYTHONPATH=build/lib.linux-x86_64-2.7 python2.7 -c "import pg; d=pg.DB('ts');
x=d.prepare_query(\"PREPARE x AS SELECT 'foo%%s'\"); print
d.execute_prepared('x', []).getresult()"
[('foo%s',)]
This is an important test case (no params):
[pryzbyj@telsasoft PyGreSQL]$ PYTHONPATH=build/lib.linux-x86_64-2.7 python2.7
-c "import pg; d=pg.DB('ts'); x=d.prepare_query(\"PREPARE x AS SELECT 'foo'\");
print d.execute_prepared('x', []).getresult()"
[('foo',)]
BTW prepared statements allow significantly faster INSERT (that's our use case).
One more point: if you document PREPARE with $1 + EXECUTE + inline=True, you
should also document that SQL EXECUTE needs to be quoted/escaped/sanitized!
int is_prepared could instead be int flags, with
#define F_IS_PREPARED 0x0001
#define F_RESERVED2 0x0002
...
Justin
diff --git a/pg.py b/pg.py
index e830a66..db6b4b0 100644
--- a/pg.py
+++ b/pg.py
@@ -407,6 +407,7 @@ class Adapter:
def __init__(self, db):
self.db = weakref.proxy(db)
+ self.adapt_dict = dict([(a[7:],Adapter.__dict__[a]) for a in Adapter.__dict__ if a.startswith('_adapt_')])
@classmethod
def _adapt_bool(cls, v):
@@ -1853,6 +1854,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 +1877,36 @@ class DB:
return self.query(*self.adapter.format_query(
command, parameters, types, inline))
+ def prepare_query(self, command):
+ """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:
+
+ """
+
+ def count_esses(s):
+ """ Count the number of %s in a string """
+ in_pct = ret = 0
+ for a in s:
+ if not in_pct:
+ in_pct = bool(a=='%')
+ else:
+ in_pct=0
+ if a!='%':
+ #if a!='s': warn
+ ret+=1
+ return ret
+
+ literals = ['$%s'%x for x in range(1,1+count_esses(command))]
+ command %= tuple(literals)
+ return self.query(command)
+
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..8597f8e 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;
@@ -2190,12 +2185,10 @@ connQuery(connObject *self, PyObject *args)
return NULL;
}
- /* If param_obj is passed, ensure it's a non-empty tuple. We want to treat
- * an empty tuple the same as no argument since we'll get that when the
- * caller passes no arguments to db.query(), and historic behaviour was
- * to call PQexec() in that case, which can execute multiple commands. */
+ /* If param_obj is passed, ensure it's a non-empty tuple. */
if (param_obj)
{
+
param_obj = PySequence_Fast(param_obj,
"Method query() expects a sequence as second argument");
if (!param_obj)
@@ -2220,7 +2213,18 @@ connQuery(connObject *self, PyObject *args)
}
/* gets result */
- if (nparms)
+
+ /* We want to treat an empty tuple the same as no argument since we'll
+ * get that when the caller passes no arguments to db.query(), and
+ * historic behaviour was to call PQexec() in that case, which can
+ * execute multiple commands. */
+ if (nparms==0 && is_prepared==0)
+ {
+ Py_BEGIN_ALLOW_THREADS
+ result = PQexec(self->cnx, query);
+ Py_END_ALLOW_THREADS
+ }
+ else /* Do not fail if prepared && nparams==0.. */
{
/* prepare arguments */
PyObject **str, **s;
@@ -2287,20 +2291,23 @@ 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);
while (s != str) { s--; Py_DECREF(*s); }
PyMem_Free(str);
}
- else
- {
- Py_BEGIN_ALLOW_THREADS
- result = PQexec(self->cnx, query);
- Py_END_ALLOW_THREADS
- }
/* we don't need the query and its params any more */
Py_XDECREF(query_obj);
@@ -2376,6 +2383,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 +3444,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
[email protected]
https://mail.vex.net/mailman/listinfo/pygresql
