Find attached patch with proof of concept for minimal implementation of
prepared statements.
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.
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.
[pryzbyj@telsasoft PyGreSQL]$ PYTHONPATH=build/lib.linux-x86_64-2.7/ command
time -v python2.7 -c "import pg; d=pg.DB('ts'); d.query_formatted('prepare
prepq AS SELECT * FROM generate_series(1,%s)', range(1), prepare=True); print
d.query('prepq', [99]).getresult()"
[(1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,), (10,), (11,), (12,),
(13,), (14,), (15,), (16,), (17,), (18,), (19,), (20,), (21,), (22,), (23,),
(24,), (25,), (26,), (27,), (28,), (29,), (30,), (31,), (32,), (33,), (34,),
(35,), (36,), (37,), (38,), (39,), (40,), (41,), (42,), (43,), (44,), (45,),
(46,), (47,), (48,), (49,), (50,), (51,), (52,), (53,), (54,), (55,), (56,),
(57,), (58,), (59,), (60,), (61,), (62,), (63,), (64,), (65,), (66,), (67,),
(68,), (69,), (70,), (71,), (72,), (73,), (74,), (75,), (76,), (77,), (78,),
(79,), (80,), (81,), (82,), (83,), (84,), (85,), (86,), (87,), (88,), (89,),
(90,), (91,), (92,), (93,), (94,), (95,), (96,), (97,), (98,), (99,)]
Justin
diff --git a/pg.py b/pg.py
index e830a66..ec15631 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]
@@ -1854,7 +1863,7 @@ class DB:
return self.db.query(command)
def query_formatted(self, command,
- parameters=None, types=None, inline=False):
+ parameters=None, types=None, inline=False, prepare=False):
"""Execute a formatted SQL command string.
Similar to query, but using Python format placeholders of the form
@@ -1867,7 +1876,7 @@ class DB:
embedded in the SQL command, otherwise they will be sent separately.
"""
return self.query(*self.adapter.format_query(
- command, parameters, types, inline))
+ command, parameters, types, inline, prepare))
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..ff2d90d 100644
--- a/pgmodule.c
+++ b/pgmodule.c
@@ -2287,8 +2287,17 @@ connQuery(connObject *self, PyObject *args)
}
Py_BEGIN_ALLOW_THREADS
- result = PQexecParams(self->cnx, query, nparms,
- NULL, parms, NULL, NULL, 0);
+ if (strstr(query, "prep")==query) {
+ // 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);
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo/pygresql
