Changeset 953 for trunk


Ignore:
Timestamp:
Jan 3, 2019, 3:38:57 PM (9 months ago)
Author:
cito
Message:

Add special methods for using prepared statements

Location:
trunk
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • trunk/pg.py

    r951 r953  
    18691869        return self.query(*self.adapter.format_query(
    18701870            command, parameters, types, inline))
     1871
     1872    def query_prepared(self, name, *args):
     1873        """Execute a prepared SQL statement.
     1874
     1875        This works like the query() method, but you need to pass the name of
     1876        a prepared statement that you have already created with prepare().
     1877        """
     1878        if not self.db:
     1879            raise _int_error('Connection is not valid')
     1880        if args:
     1881            self._do_debug('EXECUTE', name, args)
     1882            return self.db.query_prepared(name, args)
     1883        self._do_debug('EXECUTE', name)
     1884        return self.db.query_prepared(name)
     1885
     1886    def prepare(self, name, command):
     1887        """Create a prepared SQL statement.
     1888
     1889        This creates a prepared statement with the given name for the given
     1890        command for later execution with the query_prepared() method.
     1891        The name can be "" to create an unnamed statement, in which case any
     1892        pre-existing unnamed statement is automatically replaced; otherwise
     1893        it is an error if the statement name is already defined in the current
     1894        database session.
     1895
     1896        If any parameters are used, they can be referred to in the query as
     1897        numbered parameters of the form $1.
     1898        """
     1899        if not self.db:
     1900            raise _int_error('Connection is not valid')
     1901        self._do_debug('prepare', name, command)
     1902        return self.db.prepare(name, command)
     1903
     1904    def describe_prepared(self, name):
     1905        """Describe a prepared SQL statement.
     1906
     1907        This method returns a Query object describing the result columns of
     1908        the prepared statement with the given name.
     1909        """
     1910        return self.db.describe_prepared(name)
     1911
     1912    def delete_prepared(self, name=None):
     1913        """Delete a prepared SQL statement
     1914
     1915        This deallocates a previously prepared SQL statement with the given
     1916        name, or deallocates all prepared statements. Prepared statements are
     1917        also deallocated automatically when the current session ends.
     1918        """
     1919        q = "DEALLOCATE %s" % (name or 'ALL',)
     1920        self._do_debug(q)
     1921        return self.db.query(q)
    18711922
    18721923    def pkey(self, table, composite=False, flush=False):
  • trunk/pgmodule.c

    r952 r953  
    21422142
    21432143/* database query */
    2144 static char connQuery__doc__[] =
    2145 "query(sql, [arg]) -- create a new query object for this connection\n\n"
    2146 "You must pass the SQL (string) request and you can optionally pass\n"
    2147 "a tuple with positional parameters.\n";
    2148 
    2149 static PyObject *
    2150 connQuery(connObject *self, PyObject *args)
     2144
     2145/* base method for execution of both unprepared and prepared queries */
     2146static PyObject *
     2147_connQuery(connObject *self, PyObject *args, int prepared)
    21512148{
    21522149        PyObject        *query_obj;
     
    22882285
    22892286                Py_BEGIN_ALLOW_THREADS
    2290                 result = PQexecParams(self->cnx, query, nparms,
    2291                         NULL, parms, NULL, NULL, 0);
     2287                result = prepared ?
     2288                        PQexecPrepared(self->cnx, query, nparms,
     2289                                parms, NULL, NULL, 0) :
     2290                        PQexecParams(self->cnx, query, nparms,
     2291                                NULL, parms, NULL, NULL, 0);
    22922292                Py_END_ALLOW_THREADS
    22932293
     
    22992299        {
    23002300                Py_BEGIN_ALLOW_THREADS
    2301                 result = PQexec(self->cnx, query);
     2301                result = prepared ?
     2302                        PQexecPrepared(self->cnx, query, 0,
     2303                                NULL, NULL, NULL, 0) :
     2304                        PQexec(self->cnx, query);
    23022305                Py_END_ALLOW_THREADS
    23032306        }
     
    23772380}
    23782381
     2382/* database query */
     2383static char connQuery__doc__[] =
     2384"query(sql, [arg]) -- create a new query object for this connection\n\n"
     2385"You must pass the SQL (string) request and you can optionally pass\n"
     2386"a tuple with positional parameters.\n";
     2387
     2388static PyObject *
     2389connQuery(connObject *self, PyObject *args)
     2390{
     2391        return _connQuery(self, args, 0);
     2392}
     2393
     2394/* execute prepared statement */
     2395static char connQueryPrepared__doc__[] =
     2396"query_prepared(name, [arg]) -- execute a prepared statement\n\n"
     2397"You must pass the name (string) of the prepared statement and you can\n"
     2398"optionally pass a tuple with positional parameters.\n";
     2399
     2400static PyObject *
     2401connQueryPrepared(connObject *self, PyObject *args)
     2402{
     2403        return _connQuery(self, args, 1);
     2404}
     2405
     2406/* create prepared statement */
     2407static char connPrepare__doc__[] =
     2408"prepare(name, sql) -- create a prepared statement\n\n"
     2409"You must pass the name (string) of the prepared statement and the\n"
     2410"SQL (string) request for later execution.\n";
     2411
     2412static PyObject *
     2413connPrepare(connObject *self, PyObject *args)
     2414{
     2415        char            *name, *query;
     2416        int             name_length, query_length;
     2417        PGresult        *result;
     2418
     2419        if (!self->cnx)
     2420        {
     2421                PyErr_SetString(PyExc_TypeError, "Connection is not valid");
     2422                return NULL;
     2423        }
     2424
     2425        /* reads args */
     2426        if (!PyArg_ParseTuple(args, "s#s#",
     2427                &name, &name_length, &query, &query_length))
     2428        {
     2429                PyErr_SetString(PyExc_TypeError,
     2430                        "Method prepare() takes two string arguments");
     2431                return NULL;
     2432        }
     2433
     2434        /* create prepared statement */
     2435        Py_BEGIN_ALLOW_THREADS
     2436        result = PQprepare(self->cnx, name, query, 0, NULL);
     2437        Py_END_ALLOW_THREADS
     2438        if (result && PQresultStatus(result) == PGRES_COMMAND_OK)
     2439        {
     2440                PQclear(result);
     2441                Py_INCREF(Py_None);
     2442                return Py_None; /* success */
     2443        }
     2444        set_error(ProgrammingError, "Cannot create prepared statement",
     2445                self->cnx, result);
     2446        if (result)
     2447                PQclear(result);
     2448        return NULL; /* error */
     2449}
     2450
     2451/* describe prepared statement */
     2452static char connDescribePrepared__doc__[] =
     2453"describe_prepared(name, sql) -- describe a prepared statement\n\n"
     2454"You must pass the name (string) of the prepared statement.\n";
     2455
     2456static PyObject *
     2457connDescribePrepared(connObject *self, PyObject *args)
     2458{
     2459        char            *name;
     2460        int             name_length;
     2461        PGresult        *result;
     2462
     2463        if (!self->cnx)
     2464        {
     2465                PyErr_SetString(PyExc_TypeError, "Connection is not valid");
     2466                return NULL;
     2467        }
     2468
     2469        /* reads args */
     2470        if (!PyArg_ParseTuple(args, "s#",
     2471                &name, &name_length))
     2472        {
     2473                PyErr_SetString(PyExc_TypeError,
     2474                        "Method prepare() takes a string argument");
     2475                return NULL;
     2476        }
     2477
     2478        /* describe prepared statement */
     2479        Py_BEGIN_ALLOW_THREADS
     2480        result = PQdescribePrepared(self->cnx, name);
     2481        Py_END_ALLOW_THREADS
     2482        if (result && PQresultStatus(result) == PGRES_COMMAND_OK)
     2483        {
     2484                queryObject *npgobj = PyObject_NEW(queryObject, &queryType);
     2485                if (!npgobj)
     2486                        return PyErr_NoMemory();
     2487                Py_XINCREF(self);
     2488                npgobj->pgcnx = self;
     2489                npgobj->result = result;
     2490                return (PyObject *) npgobj;
     2491        }
     2492        set_error(ProgrammingError, "Cannot describe prepared statement",
     2493                self->cnx, result);
     2494        if (result)
     2495                PQclear(result);
     2496        return NULL; /* error */
     2497}
     2498
    23792499#ifdef DIRECT_ACCESS
    23802500static char connPutLine__doc__[] =
     
    34153535        {"source", (PyCFunction) connSource, METH_NOARGS, connSource__doc__},
    34163536        {"query", (PyCFunction) connQuery, METH_VARARGS, connQuery__doc__},
     3537        {"query_prepared", (PyCFunction) connQueryPrepared, METH_VARARGS,
     3538                        connQueryPrepared__doc__},
     3539        {"prepare", (PyCFunction) connPrepare, METH_VARARGS, connPrepare__doc__},
     3540        {"describe_prepared", (PyCFunction) connDescribePrepared, METH_VARARGS,
     3541                        connDescribePrepared__doc__},
    34173542        {"reset", (PyCFunction) connReset, METH_NOARGS, connReset__doc__},
    34183543        {"cancel", (PyCFunction) connCancel, METH_NOARGS, connCancel__doc__},
  • trunk/tests/test_classic_connection.py

    r928 r953  
    122122
    123123    def testAllConnectMethods(self):
    124         methods = '''cancel close date_format endcopy
     124        methods = '''cancel close date_format describe_prepared endcopy
    125125            escape_bytea escape_identifier escape_literal escape_string
    126126            fileno get_cast_hook get_notice_receiver getline getlo getnotify
    127             inserttable locreate loimport parameter putline query reset
     127            inserttable locreate loimport parameter
     128            prepare putline query query_prepared reset
    128129            set_cast_hook set_notice_receiver source transaction'''.split()
    129130        connection_methods = [a for a in dir(self.connection)
     
    931932        self.assertEqual(self.c.query("select $1::text AS garbage", (garbage,)
    932933            ).dictresult(), [{'garbage': garbage}])
     934
     935
     936class TestPreparedQueries(unittest.TestCase):
     937    """Test prepared queries via a basic pg connection."""
     938
     939    def setUp(self):
     940        self.c = connect()
     941        self.c.query('set client_encoding=utf8')
     942
     943    def tearDown(self):
     944        self.c.close()
     945
     946    def testEmptyPreparedStatement(self):
     947        self.c.prepare('', '')
     948        self.assertRaises(ValueError, self.c.query_prepared, '')
     949
     950    def testInvalidPreparedStatement(self):
     951        self.assertRaises(pg.ProgrammingError, self.c.prepare, '', 'bad')
     952
     953    def testNonExistentPreparedStatement(self):
     954        self.assertRaises(pg.OperationalError,
     955            self.c.query_prepared, 'does-not-exist')
     956
     957    def testAnonymousQueryWithoutParams(self):
     958        self.assertIsNone(self.c.prepare('', "select 'anon'"))
     959        self.assertEqual(self.c.query_prepared('').getresult(), [('anon',)])
     960
     961    def testNamedQueryWithoutParams(self):
     962        self.assertIsNone(self.c.prepare('hello', "select 'world'"))
     963        self.assertEqual(self.c.query_prepared('hello').getresult(),
     964            [('world',)])
     965
     966    def testMultipleNamedQueriesWithoutParams(self):
     967        self.assertIsNone(self.c.prepare('query17', "select 17"))
     968        self.assertIsNone(self.c.prepare('query42', "select 42"))
     969        self.assertEqual(self.c.query_prepared('query17').getresult(), [(17,)])
     970        self.assertEqual(self.c.query_prepared('query42').getresult(), [(42,)])
     971
     972    def testAnonymousQueryWithParams(self):
     973        self.assertIsNone(self.c.prepare('', "select $1 || ', ' || $2"))
     974        self.assertEqual(
     975            self.c.query_prepared('', ['hello', 'world']).getresult(),
     976            [('hello, world',)])
     977        self.assertIsNone(self.c.prepare('', "select 1+ $1 + $2 + $3"))
     978        self.assertEqual(
     979            self.c.query_prepared('', [17, -5, 29]).getresult(), [(42,)])
     980
     981    def testMultipleNamedQueriesWithParams(self):
     982        self.assertIsNone(self.c.prepare('q1', "select $1 || '!'"))
     983        self.assertIsNone(self.c.prepare('q2', "select $1 || '-' || $2"))
     984        self.assertEqual(self.c.query_prepared('q1', ['hello']).getresult(),
     985            [('hello!',)])
     986        self.assertEqual(self.c.query_prepared('q2', ['he', 'lo']).getresult(),
     987            [('he-lo',)])
     988
     989    def testDescribeNonExistentQuery(self):
     990        self.assertRaises(pg.OperationalError,
     991            self.c.describe_prepared, 'does-not-exist')
     992
     993    def testDescribeAnonymousQuery(self):
     994        self.c.prepare('', "select 1::int, 'a'::char")
     995        r = self.c.describe_prepared('')
     996        self.assertEqual(r.listfields(), ('int4', 'bpchar'))
     997
     998    def testDescribeNamedQuery(self):
     999        self.c.prepare('myquery', "select 1 as first, 2 as second")
     1000        r = self.c.describe_prepared('myquery')
     1001        self.assertEqual(r.listfields(), ('first', 'second'))
     1002
     1003    def testDescribeMultipleNamedQueries(self):
     1004        self.c.prepare('query1', "select 1::int")
     1005        self.c.prepare('query2', "select 1::int, 2::int")
     1006        r = self.c.describe_prepared('query1')
     1007        self.assertEqual(r.listfields(), ('int4',))
     1008        r = self.c.describe_prepared('query2')
     1009        self.assertEqual(r.listfields(), ('int4', 'int4'))
    9331010
    9341011
  • trunk/tests/test_classic_dbwrapper.py

    r928 r953  
    199199            'date_format', 'db', 'dbname', 'dbtypes',
    200200            'debug', 'decode_json', 'delete',
     201            'delete_prepared', 'describe_prepared',
    201202            'encode_json', 'end', 'endcopy', 'error',
    202203            'escape_bytea', 'escape_identifier',
     
    214215            'options',
    215216            'parameter', 'pkey', 'port',
    216             'protocol_version', 'putline',
    217             'query', 'query_formatted',
     217            'prepare', 'protocol_version', 'putline',
     218            'query', 'query_formatted', 'query_prepared',
    218219            'release', 'reopen', 'reset', 'rollback',
    219220            'savepoint', 'server_version',
     
    968969        r = f(q, {}).getresult()[0][0]
    969970        self.assertEqual(r, 42)
     971
     972    def testQueryPreparedWithoutParams(self):
     973        p = self.db.prepare
     974        p('q1', "select 17")
     975        p('q2', "select 42")
     976        f = self.db.query_prepared
     977        r = f('q1').getresult()[0][0]
     978        self.assertEqual(r, 17)
     979        r = f('q2').getresult()[0][0]
     980        self.assertEqual(r, 42)
     981
     982    def testQueryPreparedWithParams(self):
     983        p = self.db.prepare
     984        p('sum', "select 1 + $1 + $2 + $3")
     985        p('cat', "select initcap($1) || ', ' || $2 || '!'")
     986        f = self.db.query_prepared
     987        r = f('sum', 2, 3, 5).getresult()[0][0]
     988        self.assertEqual(r, 11)
     989        r = f('cat', 'hello', 'world').getresult()[0][0]
     990        self.assertEqual(r, 'Hello, world!')
     991
     992    def testPrepare(self):
     993        p = self.db.prepare
     994        self.assertIsNone(p('',  "select null"))
     995        self.assertIsNone(p('myquery', "select 'hello'"))
     996        self.assertIsNone(p('myquery2', "select 'world'"))
     997        self.assertRaises(pg.ProgrammingError,
     998            p, 'myquery', "select 'hello, too'")
     999
     1000    def testDescribePrepared(self):
     1001        self.db.prepare('count', 'select 1 as first, 2 as second')
     1002        f = self.db.describe_prepared
     1003        r = f('count').listfields()
     1004        self.assertEqual(r, ('first', 'second'))
     1005
     1006    def testDeletePrepared(self):
     1007        f = self.db.delete_prepared
     1008        f()
     1009        e = pg.OperationalError
     1010        self.assertRaises(e, f, 'myquery')
     1011        p = self.db.prepare
     1012        p('q1', "select 1")
     1013        p('q2', "select 2")
     1014        f('q1')
     1015        f('q2')
     1016        self.assertRaises(e, f, 'q1')
     1017        self.assertRaises(e, f, 'q2')
     1018        p('q1', "select 1")
     1019        p('q2', "select 2")
     1020        f()
     1021        self.assertRaises(e, f, 'q1')
     1022        self.assertRaises(e, f, 'q2')
    9701023
    9711024    def testPkey(self):
Note: See TracChangeset for help on using the changeset viewer.