I've attached a patch against SVN head that implements positional parameters
in the classic API's query method. It has the following characteristics:
- parameters are specified using postgres's syntax. $1, $2, etc.
- in the connection object, parameters values can be specified either as
a tuple or a list
- in the DB class, parameters can be specified as either a tuple, a list,
or as individual arguments
- unicode strings are converted to UTF-8 before passing to the database
- other types are converted to strings before passing to the database
- when positional parameters are used, only one SQL statement can be
entered per query
- if either no optional arguments are passed to the function or an empty
tuple is passed, it behaves exactly as it did in the past.
- the DB class's .insert(), .update(), and .delete() methods have been
changed to use positional parameters rather than quoted strings
I've updated the doc and added some tests.
I haven't implemented the following:
- "executemany" functionality, where a list of tuples results in multiple
executions of the statement with different parameters. I felt it
was better to have a simpler change to start with
- prepared statements. This seems like a natural extension and in fact
is what's in the todo list, but again I'd prefer to keep this change
small and simple. It is possible to use explicit PREPARE and EXECUTE
statements to get the same effect
- I haven't touched the dbapi code
I elected to convert all the arguments to strings and let the DB figure out
what to do with them rather than trying to match arguments to type OIDs. This
appears to work well except in select lists, where it's necessary to cast the
parameters to the required return type. I don't see this as a big problem,
since I'd expect parameters to be used mainly in where clauses and insert or
update statements, where the DB seems to do a good job of inferring the
correct type.
In terms of API requirements, this change uses PQexecParams, which has been
around since pgsql 7.4. This is not a new limitation. It uses a few of the
PySequence calls and PyUnicode_AsUTF8String(). These have been around since
at least Python 2.3, which I think was already a requirement.
These are valid calls under this change, assuming all the variables have
values, of course:
db.query("INSERT INTO x VALUES ($1,$2,$3)", alpha, bravo, charlie) *
db.query("INSERT INTO x VALUES ($1,$2,$3)", (alpha, bravo, charlie))
db.query("INSERT INTO x VALUES ($2,$1,$3)", [bravo, alpha, charlie])
db.query("SELECT bravo,charlie FROM x WHERE alpha = $1", alpha) *
db.query("SELECT bravo,charlie FROM x WHERE alpha = $1", (alpha,))
db.query("SELECT bravo,charlie FROM x WHERE alpha = $1", [alpha,])
The calls marked * will not work with the pg.connect() interface -- it
requires either a tuple or list.
--
Patrick TJ McPhee <[email protected]>
Index: module/test_pg.py
===================================================================
--- module/test_pg.py (revision 445)
+++ module/test_pg.py (working copy)
@@ -418,7 +418,14 @@
self.assertNotEqual(user, no_user)
def testMethodQuery(self):
- self.connection.query("select 1+1")
+ self.assertEqual(self.connection.query("select 1+1").getresult()[0][0], 2)
+ self.assertEqual(self.connection.query("select 1+$1", (1,)).getresult()[0][0], 2)
+ self.assertEqual(self.connection.query("select 1+$1", [1,]).getresult()[0][0], 2)
+ self.assertEqual(self.connection.query("select 1+$1::numeric", [1,]).getresult()[0][0], Decimal('2'))
+ self.assertEqual(self.connection.query("select $1::int+$2", (1,1)).getresult()[0][0], 2)
+ self.assertEqual(self.connection.query("create temp table x (a varchar(20));"
+ "insert into x values ('alpha');"
+ "select a from x").getresult()[0][0], "alpha")
def testMethodEndcopy(self):
try:
@@ -925,7 +932,14 @@
r"\x4f007073ff21"), 'O\x00ps\xff!')
def testMethodQuery(self):
- self.db.query("select 1+1")
+ self.assertEqual(self.db.query("select 1+1").getresult()[0][0], 2)
+ self.assertEqual(self.db.query("select 1+$1", (1,)).getresult()[0][0], 2)
+ self.assertEqual(self.db.query("select 1+$1", [1,]).getresult()[0][0], 2)
+ self.assertEqual(self.db.query("select 1+$1::numeric", [1,]).getresult()[0][0], Decimal('2'))
+ self.assertEqual(self.db.query("select $1::int+$2", (1,1)).getresult()[0][0], 2)
+ self.assertEqual(self.db.query("select $1::int+$2", 1,1).getresult()[0][0], 2)
+ s = r"'\{}+()-#[]oo324"
+ self.assertEqual(self.db.query("select $1::text AS dennis", s).dictresult()[0]['dennis'], s)
def testMethodQueryProgrammingError(self):
try:
Index: module/pgmodule.c
===================================================================
--- module/pgmodule.c (revision 445)
+++ module/pgmodule.c (working copy)
@@ -2434,16 +2434,23 @@
/* database query */
static char pg_query__doc__[] =
-"query(sql) -- creates a new query object for this connection,"
-" using sql (string) request.";
+"query(sql, [args]) -- creates a new query object for this connection,"
+" using sql (string) request and optionally a tuple with positional"
+" parameters.";
static PyObject *
pg_query(pgobject *self, PyObject *args)
{
- char *query;
+ char *query,
+ **parms;
+ PyObject *oargs = NULL,
+ **str;
PGresult *result;
pgqueryobject *npgobj;
- int status;
+ int status,
+ nparms = 0,
+ *lparms;
+ register int i;
if (!self->cnx)
{
@@ -2452,17 +2459,99 @@
}
/* get query args */
- if (!PyArg_ParseTuple(args, "s", &query))
+ if (!PyArg_ParseTuple(args, "s|O", &query, &oargs))
{
- PyErr_SetString(PyExc_TypeError, "query(sql), with sql (string).");
+ PyErr_SetString(PyExc_TypeError, "query(sql, [args]), with sql (string).");
return NULL;
}
+ /* If oargs 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(). */
+ if (oargs)
+ {
+ if (!PyTuple_Check(oargs) && !PyList_Check(oargs))
+ {
+ PyErr_SetString(PyExc_TypeError, "query parameters must be a tuple or list.");
+ return NULL;
+ }
+
+ nparms = PySequence_Size(oargs);
+ }
+
+
/* gets result */
- Py_BEGIN_ALLOW_THREADS
- result = PQexec(self->cnx, query);
- Py_END_ALLOW_THREADS
+ if (!nparms)
+ {
+ Py_BEGIN_ALLOW_THREADS
+ result = PQexec(self->cnx, query);
+ Py_END_ALLOW_THREADS
+ }
+
+ else
+ {
+ /* prepare arguments */
+ PyObject * obj = PySequence_GetItem(oargs, 0);
+
+ /* if there's a single argument and it's a list or tuple, it
+ * contains the positional aguments. */
+ if (nparms == 1 && (PyList_Check(obj) ||
+ PyTuple_Check(obj)))
+ {
+ oargs = obj;
+ nparms = PySequence_Size(oargs);
+ }
+ parms = (char **)alloca(nparms * sizeof(*parms));
+ lparms = (int *)alloca(nparms * sizeof(*lparms));
+ str = (PyObject **)alloca(nparms * sizeof(*str));
+
+ /* convert optional args to a list of strings -- this allows
+ * the caller to pass whatever they like, and prevents us
+ * from having to map types to OIDs */
+ for (i = 0; i < nparms; i++)
+ {
+ obj = PySequence_GetItem(oargs, i);
+
+ if (obj == Py_None)
+ {
+ str[i] = NULL;
+ lparms[i] = 0;
+ parms[i] = NULL;
+ }
+ else if (PyUnicode_Check(obj))
+ {
+ /* most objects can be converted to strings. Unicodes might
+ * not work with the default encoding, so encode as UTF-8 */
+ str[i] = PyUnicode_AsUTF8String(obj);
+ lparms[i] = PyString_Size(str[i]);
+ parms[i] = PyString_AsString(str[i]);
+ }
+ else
+ {
+ str[i] = PyObject_Str(obj);
+ lparms[i] = PyString_Size(str[i]);
+ parms[i] = PyString_AsString(str[i]);
+ }
+ }
+
+
+ Py_BEGIN_ALLOW_THREADS
+ result = PQexecParams(self->cnx, query, nparms,
+ NULL, parms, lparms, NULL, 0);
+ Py_END_ALLOW_THREADS
+
+ for (i = 0; i < nparms; i++)
+ {
+ if (str[i])
+ {
+ Py_DECREF(str[i]);
+ }
+ }
+ }
+
+
/* checks result validity */
if (!result)
{
Index: module/pg.py
===================================================================
--- module/pg.py (revision 445)
+++ module/pg.py (working copy)
@@ -318,7 +318,7 @@
self.db.close()
self.db = db
- def query(self, qstr):
+ def query(self, qstr, *args):
"""Executes a SQL command string.
This method simply sends a SQL query to the database. If the query is
@@ -332,12 +332,16 @@
a pgqueryobject that can be accessed via getresult() or dictresult()
or simply printed. Otherwise, it returns `None`.
+ The query can contain numbered parameters of the form $1 in place
+ of any data constant. Arguments given after the query string will
+ be substituted for the corresponding numbered parameter. Parameter
+ values can also be given as a single list or tuple argument.
"""
# Wraps shared library function for debugging.
if not self.db:
raise _int_error('Connection is not valid')
self._do_debug(qstr)
- return self.db.query(qstr)
+ return self.db.query(qstr, args)
def pkey(self, cl, newpkey=None):
"""This method gets or sets the primary key of a class.
@@ -548,7 +552,8 @@
raise _db_error('%s not in arg' % qoid)
else:
arg = {qoid: arg}
- where = 'oid = %s' % arg[qoid]
+ where = 'oid = $1'
+ params = (arg[qoid],)
attnames = '*'
else:
attnames = self.get_attnames(qcl)
@@ -558,14 +563,15 @@
if len(keyname) > 1:
raise _prg_error('Composite key needs dict as arg')
arg = dict([(k, arg) for k in keyname])
- where = ' AND '.join(['%s = %s'
- % (k, self._quote(arg[k], attnames[k])) for k in keyname])
+ where = ' AND '.join(['%s = $%s'
+ % (k, i+1) for i,k in enumerate(keyname)])
+ params = tuple(arg[k] for k in keyname)
attnames = ', '.join(attnames)
q = 'SELECT %s FROM %s WHERE %s LIMIT 1' % (attnames, qcl, where)
- self._do_debug(q)
- res = self.db.query(q).dictresult()
+ self._do_debug(q + ('%% %r' % (params,)))
+ res = self.db.query(q, params).dictresult()
if not res:
- raise _db_error('No such record in %s where %s' % (qcl, where))
+ raise _db_error('No such record in %s where %s %% %r' % (qcl, where, params))
for att, value in res[0].iteritems():
arg[att == 'oid' and qoid or att] = value
return arg
@@ -590,11 +596,14 @@
d = {}
d.update(kw)
attnames = self.get_attnames(qcl)
- names, values = [], []
+ names, values, params = [], [], []
+ i = 1
for n in attnames:
if n != 'oid' and n in d:
names.append('"%s"' % n)
- values.append(self._quote(d[n], attnames[n]))
+ values.append('$%s' % (i,))
+ params.append(d[n])
+ i += 1
names, values = ', '.join(names), ', '.join(values)
selectable = self.has_table_privilege(qcl)
if selectable and self.server_version >= 80200:
@@ -602,8 +611,8 @@
else:
ret = ''
q = 'INSERT INTO %s (%s) VALUES (%s)%s' % (qcl, names, values, ret)
- self._do_debug(q)
- res = self.db.query(q)
+ self._do_debug(q + ("%% %r" % (params,)))
+ res = self.db.query(q, params)
if ret:
res = res.dictresult()
for att, value in res[0].iteritems():
@@ -645,7 +654,8 @@
d.update(kw)
attnames = self.get_attnames(qcl)
if qoid in d:
- where = 'oid = %s' % d[qoid]
+ where = 'oid = $1'
+ params = [d[qoid]]
keyname = ()
else:
try:
@@ -655,14 +665,18 @@
if isinstance(keyname, basestring):
keyname = (keyname,)
try:
- where = ' AND '.join(['%s = %s'
- % (k, self._quote(d[k], attnames[k])) for k in keyname])
+ where = ' AND '.join(['%s = $%s'
+ % (k, i+1) for i,k in enumerate(keyname)])
+ params = [d[k] for k in keyname]
except KeyError:
raise _prg_error('Update needs primary key or oid.')
values = []
+ i = len(params)
for n in attnames:
if n in d and n not in keyname:
- values.append('%s = %s' % (n, self._quote(d[n], attnames[n])))
+ i += 1
+ values.append('%s = $%s' % (n, i))
+ params.append(d[n])
if not values:
return d
values = ', '.join(values)
@@ -673,7 +687,7 @@
ret = ''
q = 'UPDATE %s SET %s WHERE %s%s' % (qcl, values, where, ret)
self._do_debug(q)
- res = self.db.query(q)
+ res = self.db.query(q, params)
if ret:
res = res.dictresult()[0]
for att, value in res.iteritems():
@@ -735,7 +749,8 @@
d = {}
d.update(kw)
if qoid in d:
- where = 'oid = %s' % d[qoid]
+ where = 'oid = $1'
+ params = (d[qoid],)
else:
try:
keyname = self.pkey(qcl)
@@ -745,13 +760,14 @@
keyname = (keyname,)
attnames = self.get_attnames(qcl)
try:
- where = ' AND '.join(['%s = %s'
- % (k, self._quote(d[k], attnames[k])) for k in keyname])
+ where = ' AND '.join(['%s = $%s'
+ % (k, i+1) for i,k in enumerate(keyname)])
+ params = tuple(d[k] for k in keyname)
except KeyError:
raise _prg_error('Delete needs primary key or oid.')
q = 'DELETE FROM %s WHERE %s' % (qcl, where)
- self._do_debug(q)
- return int(self.db.query(q))
+ self._do_debug(q + ("%% %r" % (params,)))
+ return int(self.db.query(q, params))
# if run as script, print some information
Index: docs/pg.txt
===================================================================
--- docs/pg.txt (revision 445)
+++ docs/pg.txt (working copy)
@@ -423,10 +423,11 @@
-------------------------------------
Syntax::
- query(command)
+ query(command, [args])
Parameters:
:command: SQL command (string)
+ :args: optional positional arguments
Return type:
:pgqueryobject, None: result values
@@ -449,10 +450,22 @@
method returns a `pgqueryobject` that can be accessed via the `getresult()`
or `dictresult()` method or simply printed. Otherwise, it returns `None`.
+ The query may optionally contain positional parameters of the form `$1`,
+ `$2`, etc instead of literal data, and the values supplied as a tuple.
+ The values are substituted by the database in such a way that they don't
+ need to be escaped, making this an effective way to pass arbitrary or
+ unknown data without worrying about SQL injection or syntax errors.
+
When the database could not process the query, a `pg.ProgrammingError` or
a `pg.InternalError` is raised. You can check the "SQLSTATE" code of this
error by reading its `sqlstate` attribute.
+Example::
+
+ name = raw_input("Name? ")
+ phone = con.query("select phone from employees"
+ " where name=$1", (name, )).getresult()
+
reset - resets the connection
-----------------------------
Syntax::
@@ -1003,6 +1016,41 @@
either in the dictionary where the OID must be munged, or in the keywords
where it can be simply the string "oid".
+query - executes a SQL command string
+-------------------------------------
+Syntax::
+
+ query(command, [arg1, [arg2, ...]])
+
+Parameters:
+ :command: SQL command (string)
+ :arg*: optional positional arguments
+
+Return type:
+ :pgqueryobject, None: result values
+
+Exceptions raised:
+ :TypeError: bad argument type, or too many arguments
+ :TypeError: invalid connection
+ :ValueError: empty SQL query or lost connection
+ :pg.ProgrammingError: error in query
+ :pg.InternalError: error during query processing
+
+Description:
+ Similar to the pgobject function with the same name, except that positional
+ arguments can be passed either as a single list or tuple, or as individual
+ positional arguments
+
+Example::
+
+ name = raw_input("Name? ")
+ phone = raw_input("Phone? "
+ rows = db.query("update employees set phone=$2"
+ " where name=$1", (name, phone)).getresult()[0][0]
+ # or
+ rows = db.query("update employees set phone=$2"
+ " where name=$1", name, phone).getresult()[0][0]
+
clear - clears row values in memory
-----------------------------------
Syntax::
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
