Changeset 692


Ignore:
Timestamp:
Jan 3, 2016, 9:11:21 AM (4 years ago)
Author:
cito
Message:

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.

Files:
5 edited

Legend:

Unmodified
Added
Removed
  • branches/4.x/docs/changelog.rst

    r679 r692  
    55-----------
    66- Set a better default for the user option "escaping-funcs".
     7- The supported Python versions are 2.4 to 2.7.
     8- PostgreSQL is supported in all versions from 8.3 to 9.4.
    79- Force build to compile with no errors.
    810- Fix decimal point handling.
  • trunk/docs/changelog.rst

    r683 r692  
    55-----------
    66- This version runs on both Python 2 and Python 3.
    7 - The supported versions are Python 2.6, 2.7, 3.3, 3.4 and 3.5.
    8 - The supported PostgreSQL versions are 9.0, 9.1, 9.2, 9.3 and 9.4.
     7- The supported versions are Python 2.6 to 2.7, and 3.3 to 3.5.
     8- PostgreSQL is supported in all versions from 9.0 to 9.4.
    99- The DB-API 2 module now always returns result rows as named tuples
    1010  instead of simply lists as before. The documentation explains how
     
    1515  Since the API provides objects of these types only by the use of
    1616  constructor functions, this should not cause any incompatibilities.
     17- The DB-API 2 module now supports the callproc() cursor method. Note
     18  that output parameters are currently not replaced in the return value.
     19- The 7-tuples returned by the description attribute of a pgdb cursor
     20  are now named tuples, i.e. their elements can be also accessed by name.
    1721- The tty parameter and attribute of database connections has been
    1822  removed since it is not supported any more since PostgreSQL 7.4.
    19 - The 7-tuples returned by the description attribute of a pgdb cursor
    20   are now named tuples, i.e. their elements can be also accessed by name.
    2123
    2224Version 4.2
    2325-----------
    2426- Set a better default for the user option "escaping-funcs".
     27- The supported Python versions are 2.4 to 2.7.
     28- PostgreSQL is supported in all versions from 8.3 to 9.4.
    2529- Force build to compile with no errors.
    2630- Fix decimal point handling.
  • trunk/docs/pgdb.rst

    r684 r692  
    326326e.g. ``" ... WHERE name=%(name)s"``.
    327327
     328callproc -- Call a stored procedure
     329-----------------------------------
     330
     331.. method:: Cursor.callproc(self, procname, [parameters]):
     332
     333    Call a stored database procedure with the given name
     334
     335    :param str procname: the name of the database function
     336    :param parameters: a sequence of parameters (can be empty or omitted)
     337
     338This method calls a stored procedure (function) in the PostgreSQL database.
     339
     340The sequence of parameters must contain one entry for each input argument
     341that the function expects. The result of the call is the same as this input
     342sequence; replacement of output and input/output parameters in the return
     343value is currently not supported.
     344
     345The function may also provide a result set as output. These can be requested
     346through the standard fetch methods of the cursor.
     347
    328348fetchone -- fetch next row of the query result
    329349----------------------------------------------
  • trunk/module/pgdb.py

    r683 r692  
    292292        return val
    293293
    294     def _quoteparams(self, string, params):
     294    def _quoteparams(self, string, parameters):
    295295        """Quote parameters.
    296296
     
    298298
    299299        """
    300         if isinstance(params, dict):
    301             params = _quotedict(params)
    302             params.quote = self._quote
    303         else:
    304             params = tuple(map(self._quote, params))
    305         return string % params
     300        if isinstance(parameters, dict):
     301            parameters = _quotedict(parameters)
     302            parameters.quote = self._quote
     303        else:
     304            parameters = tuple(map(self._quote, parameters))
     305        return string % parameters
    306306
    307307    def close(self):
     
    313313        self.lastrowid = None
    314314
    315     def execute(self, operation, params=None):
     315    def execute(self, operation, parameters=None):
    316316        """Prepare and execute a database operation (query or command)."""
    317317
     
    319319        # tuples to e.g. insert multiple rows in a single
    320320        # operation, but this kind of usage is deprecated:
    321         if (params and isinstance(params, list)
    322                 and isinstance(params[0], tuple)):
    323             return self.executemany(operation, params)
     321        if (parameters and isinstance(parameters, list) and
     322                isinstance(parameters[0], tuple)):
     323            return self.executemany(operation, parameters)
    324324        else:
    325325            # not a list of tuples
    326             return self.executemany(operation, [params])
    327 
    328     def executemany(self, operation, param_seq):
     326            return self.executemany(operation, [parameters])
     327
     328    def executemany(self, operation, seq_of_parameters):
    329329        """Prepare operation and execute it against a parameter sequence."""
    330         if not param_seq:
     330        if not seq_of_parameters:
    331331            # don't do anything without parameters
    332332            return
     
    346346                    raise _op_error("can't start transaction")
    347347                self._dbcnx._tnx = True
    348             for params in param_seq:
    349                 if params:
    350                     sql = self._quoteparams(operation, params)
     348            for parameters in seq_of_parameters:
     349                if parameters:
     350                    sql = self._quoteparams(operation, parameters)
    351351                else:
    352352                    sql = operation
     
    416416            for typ, value in zip(self.coltypes, row)]) for row in result]
    417417
     418    def callproc(self, procname, parameters=None):
     419        """Call a stored database procedure with the given name.
     420
     421        The sequence of parameters must contain one entry for each input
     422        argument that the procedure expects. The result of the call is the
     423        same as this input sequence; replacement of output and input/output
     424        parameters in the return value is currently not supported.
     425
     426        The procedure may also provide a result set as output. These can be
     427        requested through the standard fetch methods of the cursor.
     428
     429        """
     430        n = parameters and len(parameters) or 0
     431        query = 'select * from "%s"(%s)' % (procname, ','.join(n * ['%s']))
     432        self.execute(query, parameters)
     433        return parameters
     434
    418435    def __next__(self):
    419436        """Return the next row (support for the iteration protocol)."""
  • trunk/module/tests/test_dbapi20.py

    r690 r692  
    6666    def tearDown(self):
    6767        dbapi20.DatabaseAPI20Test.tearDown(self)
     68
     69    def test_callproc_no_params(self):
     70        con = self._connect()
     71        cur = con.cursor()
     72        # note that now() does not change within a transaction
     73        cur.execute('select now()')
     74        now = cur.fetchone()[0]
     75        res = cur.callproc('now')
     76        self.assertIsNone(res)
     77        res = cur.fetchone()[0]
     78        self.assertEqual(res, now)
     79
     80    def test_callproc_bad_params(self):
     81        con = self._connect()
     82        cur = con.cursor()
     83        self.assertRaises(TypeError, cur.callproc, 'lower', 42)
     84        self.assertRaises(pgdb.ProgrammingError, cur.callproc, 'lower', (42,))
     85
     86    def test_callproc_one_param(self):
     87        con = self._connect()
     88        cur = con.cursor()
     89        params = (42.4382,)
     90        res = cur.callproc("round", params)
     91        self.assertIs(res, params)
     92        res = cur.fetchone()[0]
     93        self.assertEqual(res, 42)
     94
     95    def test_callproc_two_params(self):
     96        con = self._connect()
     97        cur = con.cursor()
     98        params = (9, 4)
     99        res = cur.callproc("div", params)
     100        self.assertIs(res, params)
     101        res = cur.fetchone()[0]
     102        self.assertEqual(res, 2)
    68103
    69104    def test_cursor_type(self):
Note: See TracChangeset for help on using the changeset viewer.