Author: cito
Date: Sun Jan 3 09:11:21 2016
New Revision: 692
Log:
Implement the callproc() cursor method
The implementation has been a bit simplified in that output and
input/output parameters are not changed in the return value, i.e.
they can only be retrieved using the fetch methods. To implement
this, it would be necessary to determine (through a query to the
database catalog) which parameters are output parameters and
fetch the return values for these, which would be very costly.
Modified:
branches/4.x/docs/changelog.rst
trunk/docs/changelog.rst
trunk/docs/pgdb.rst
trunk/module/pgdb.py
trunk/module/tests/test_dbapi20.py
Modified: branches/4.x/docs/changelog.rst
==============================================================================
--- branches/4.x/docs/changelog.rst Sat Jan 2 18:35:15 2016 (r691)
+++ branches/4.x/docs/changelog.rst Sun Jan 3 09:11:21 2016 (r692)
@@ -4,6 +4,8 @@
Version 4.2
-----------
- Set a better default for the user option "escaping-funcs".
+- The supported Python versions are 2.4 to 2.7.
+- PostgreSQL is supported in all versions from 8.3 to 9.4.
- Force build to compile with no errors.
- Fix decimal point handling.
- Add option to return boolean values as bool objects.
Modified: trunk/docs/changelog.rst
==============================================================================
--- trunk/docs/changelog.rst Sat Jan 2 18:35:15 2016 (r691)
+++ trunk/docs/changelog.rst Sun Jan 3 09:11:21 2016 (r692)
@@ -4,8 +4,8 @@
Version 5.0
-----------
- This version runs on both Python 2 and Python 3.
-- The supported versions are Python 2.6, 2.7, 3.3, 3.4 and 3.5.
-- The supported PostgreSQL versions are 9.0, 9.1, 9.2, 9.3 and 9.4.
+- The supported versions are Python 2.6 to 2.7, and 3.3 to 3.5.
+- PostgreSQL is supported in all versions from 9.0 to 9.4.
- The DB-API 2 module now always returns result rows as named tuples
instead of simply lists as before. The documentation explains how
you can restore the old behavior or use custom row objects instead.
@@ -14,14 +14,18 @@
line with the names used in the DB-API 2 documentation.
Since the API provides objects of these types only by the use of
constructor functions, this should not cause any incompatibilities.
-- The tty parameter and attribute of database connections has been
- removed since it is not supported any more since PostgreSQL 7.4.
+- The DB-API 2 module now supports the callproc() cursor method. Note
+ that output parameters are currently not replaced in the return value.
- The 7-tuples returned by the description attribute of a pgdb cursor
are now named tuples, i.e. their elements can be also accessed by name.
+- The tty parameter and attribute of database connections has been
+ removed since it is not supported any more since PostgreSQL 7.4.
Version 4.2
-----------
- Set a better default for the user option "escaping-funcs".
+- The supported Python versions are 2.4 to 2.7.
+- PostgreSQL is supported in all versions from 8.3 to 9.4.
- Force build to compile with no errors.
- Fix decimal point handling.
- Add option to return boolean values as bool objects.
Modified: trunk/docs/pgdb.rst
==============================================================================
--- trunk/docs/pgdb.rst Sat Jan 2 18:35:15 2016 (r691)
+++ trunk/docs/pgdb.rst Sun Jan 3 09:11:21 2016 (r692)
@@ -325,6 +325,26 @@
Parameters are bounded to the query using Python extended format codes,
e.g. ``" ... WHERE name=%(name)s"``.
+callproc -- Call a stored procedure
+-----------------------------------
+
+.. method:: Cursor.callproc(self, procname, [parameters]):
+
+ Call a stored database procedure with the given name
+
+ :param str procname: the name of the database function
+ :param parameters: a sequence of parameters (can be empty or omitted)
+
+This method calls a stored procedure (function) in the PostgreSQL database.
+
+The sequence of parameters must contain one entry for each input argument
+that the function expects. The result of the call is the same as this input
+sequence; replacement of output and input/output parameters in the return
+value is currently not supported.
+
+The function may also provide a result set as output. These can be requested
+through the standard fetch methods of the cursor.
+
fetchone -- fetch next row of the query result
----------------------------------------------
Modified: trunk/module/pgdb.py
==============================================================================
--- trunk/module/pgdb.py Sat Jan 2 18:35:15 2016 (r691)
+++ trunk/module/pgdb.py Sun Jan 3 09:11:21 2016 (r692)
@@ -291,18 +291,18 @@
'do not know how to handle type %s' % type(val))
return val
- def _quoteparams(self, string, params):
+ def _quoteparams(self, string, parameters):
"""Quote parameters.
This function works for both mappings and sequences.
"""
- if isinstance(params, dict):
- params = _quotedict(params)
- params.quote = self._quote
+ if isinstance(parameters, dict):
+ parameters = _quotedict(parameters)
+ parameters.quote = self._quote
else:
- params = tuple(map(self._quote, params))
- return string % params
+ parameters = tuple(map(self._quote, parameters))
+ return string % parameters
def close(self):
"""Close the cursor object."""
@@ -312,22 +312,22 @@
self.rowcount = -1
self.lastrowid = None
- def execute(self, operation, params=None):
+ def execute(self, operation, parameters=None):
"""Prepare and execute a database operation (query or command)."""
# The parameters may also be specified as list of
# tuples to e.g. insert multiple rows in a single
# operation, but this kind of usage is deprecated:
- if (params and isinstance(params, list)
- and isinstance(params[0], tuple)):
- return self.executemany(operation, params)
+ if (parameters and isinstance(parameters, list) and
+ isinstance(parameters[0], tuple)):
+ return self.executemany(operation, parameters)
else:
# not a list of tuples
- return self.executemany(operation, [params])
+ return self.executemany(operation, [parameters])
- def executemany(self, operation, param_seq):
+ def executemany(self, operation, seq_of_parameters):
"""Prepare operation and execute it against a parameter sequence."""
- if not param_seq:
+ if not seq_of_parameters:
# don't do anything without parameters
return
self.description = None
@@ -345,9 +345,9 @@
except Exception:
raise _op_error("can't start transaction")
self._dbcnx._tnx = True
- for params in param_seq:
- if params:
- sql = self._quoteparams(operation, params)
+ for parameters in seq_of_parameters:
+ if parameters:
+ sql = self._quoteparams(operation, parameters)
else:
sql = operation
rows = self._src.execute(sql)
@@ -415,6 +415,23 @@
return [self.row_factory([typecast(typ, value)
for typ, value in zip(self.coltypes, row)]) for row in result]
+ def callproc(self, procname, parameters=None):
+ """Call a stored database procedure with the given name.
+
+ The sequence of parameters must contain one entry for each input
+ argument that the procedure expects. The result of the call is the
+ same as this input sequence; replacement of output and input/output
+ parameters in the return value is currently not supported.
+
+ The procedure may also provide a result set as output. These can be
+ requested through the standard fetch methods of the cursor.
+
+ """
+ n = parameters and len(parameters) or 0
+ query = 'select * from "%s"(%s)' % (procname, ','.join(n * ['%s']))
+ self.execute(query, parameters)
+ return parameters
+
def __next__(self):
"""Return the next row (support for the iteration protocol)."""
res = self.fetchone()
Modified: trunk/module/tests/test_dbapi20.py
==============================================================================
--- trunk/module/tests/test_dbapi20.py Sat Jan 2 18:35:15 2016 (r691)
+++ trunk/module/tests/test_dbapi20.py Sun Jan 3 09:11:21 2016 (r692)
@@ -66,6 +66,41 @@
def tearDown(self):
dbapi20.DatabaseAPI20Test.tearDown(self)
+ def test_callproc_no_params(self):
+ con = self._connect()
+ cur = con.cursor()
+ # note that now() does not change within a transaction
+ cur.execute('select now()')
+ now = cur.fetchone()[0]
+ res = cur.callproc('now')
+ self.assertIsNone(res)
+ res = cur.fetchone()[0]
+ self.assertEqual(res, now)
+
+ def test_callproc_bad_params(self):
+ con = self._connect()
+ cur = con.cursor()
+ self.assertRaises(TypeError, cur.callproc, 'lower', 42)
+ self.assertRaises(pgdb.ProgrammingError, cur.callproc, 'lower', (42,))
+
+ def test_callproc_one_param(self):
+ con = self._connect()
+ cur = con.cursor()
+ params = (42.4382,)
+ res = cur.callproc("round", params)
+ self.assertIs(res, params)
+ res = cur.fetchone()[0]
+ self.assertEqual(res, 42)
+
+ def test_callproc_two_params(self):
+ con = self._connect()
+ cur = con.cursor()
+ params = (9, 4)
+ res = cur.callproc("div", params)
+ self.assertIs(res, params)
+ res = cur.fetchone()[0]
+ self.assertEqual(res, 2)
+
def test_cursor_type(self):
class TestCursor(pgdb.Cursor):
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
