Changeset 961 for trunk


Ignore:
Timestamp:
Jan 4, 2019, 2:43:23 PM (9 months ago)
Author:
cito
Message:

Revert to name of prepared query as positional arg

Unnamed prepared queries are not really useful due to their limited
lifetime. To discourage usage of unnamed statements and have a more
consistent API, we switched back from keyword-only to positional arg.

Location:
trunk
Files:
3 edited

Legend:

Unmodified
Added
Removed
  • trunk/docs/contents/pg/db_wrapper.rst

    r958 r961  
    509509----------------------------------------------
    510510
    511 .. method:: DB.query_prepared([arg1, [arg2, ...]], [name=...])
     511.. method:: DB.query_prepared(name, [arg1, [arg2, ...]])
    512512
    513513    Execute a prepared statement
     
    525525
    526526This methods works like the :meth:`DB.query` method, except that instead of
    527 passing the SQL command, you pass the name of a prepared statement via the
    528 keyword-only argument *name*.  If you don't pass a name, the unnamed
    529 statement will be executed, if you created one before.
     527passing the SQL command, you pass the name of a prepared statement.  If you
     528pass an empty name, the unnamed statement will be executed.
    530529
    531530You must have created the corresponding named or unnamed statement with
     
    538537--------------------------------------
    539538
    540 .. method:: DB.prepare(command, [name])
     539.. method:: DB.prepare(name, command)
    541540
    542541    Create a prepared statement
     
    551550This method creates a prepared statement for the given command with the
    552551given name for later execution with the :meth:`DB.query_prepared` method.
    553 The name can be empty or left out to create an unnamed statement, in which
    554 case any pre-existing unnamed statement is automatically replaced;
    555 otherwise a :exc:`pg.ProgrammingError` is raised if the statement name is
    556 already defined in the current database session.
     552The name can be empty to create an unnamed statement, in which case any
     553pre-existing unnamed statement is automatically replaced; otherwise a
     554:exc:`pg.ProgrammingError` is raised if the statement name is already
     555defined in the current database session.
    557556
    558557The SQL command may optionally contain positional parameters of the form
     
    563562Example::
    564563
    565     db.prepare("update employees set phone=$2 where ein=$1",
    566         name='update employees')
     564    db.prepare('change phone',
     565        "update employees set phone=$2 where ein=$1",
    567566    while True:
    568567        ein = input("Employee ID? ")
     
    570569            break
    571570        phone = input("Phone? ")
    572         rows = db.query_prepared(ein, phone,
    573             name='update employees).getresult()[0][0]
     571        db.query_prepared('change phone', ein, phone)
    574572
    575573.. note::
    576574
    577     The DB wrapper sometimes issues parameterized queries behind the scenes
    578     (for instance to find unknown database types) which could replace the
    579     unnamed statement. So we advice to always name prepared statements.
     575     We recommend always using named queries, since unnamed queries have a
     576     limited lifetime and can be automatically replaced or destroyed by
     577     various operations on the database.
    580578
    581579.. versionadded:: 5.1
  • trunk/pg.py

    r960 r961  
    18701870            command, parameters, types, inline))
    18711871
    1872     def query_prepared(self, *args, **kwargs):
     1872    def query_prepared(self, name, *args):
    18731873        """Execute a prepared SQL statement.
    18741874
    18751875        This works like the query() method, except that instead of passing
    1876         the SQL command, you pass the name of a prepared statement via
    1877         the keyword-only argument `name`.  If you don't pass a name, the
    1878         unnamed statement will be executed, if you created one before.
     1876        the SQL command, you pass the name of a prepared statement.  If you
     1877        pass an empty name, the unnamed statement will be executed.
    18791878        """
    18801879        if not self.db:
    18811880            raise _int_error('Connection is not valid')
    1882         # use kwargs because Python 2 does not support keyword-only arguments
    1883         name = kwargs.get('name')
    18841881        if name is None:
    18851882            name = ''
     
    18901887        return self.db.query_prepared(name)
    18911888
    1892     def prepare(self, command, name=None):
     1889    def prepare(self, name, command):
    18931890        """Create a prepared SQL statement.
    18941891
    18951892        This creates a prepared statement for the given command with the
    18961893        the given name for later execution with the query_prepared() method.
    1897         The name can be empty or left out to create an unnamed statement,
    1898         in which case any pre-existing unnamed statement is automatically
    1899         replaced; otherwise it is an error if the statement name is already
    1900         defined in the current database session.
    1901 
    1902         If any parameters are used, they can be referred to in the query as
    1903         numbered parameters of the form $1.
     1894
     1895        The name can be empty to create an unnamed statement, in which case
     1896        any pre-existing unnamed statement is automatically replaced;
     1897        otherwise it is an error if the statement name is already
     1898        defined in the current database session. We recommend always using
     1899        named queries, since unnamed queries have a limited lifetime and
     1900        can be automatically replaced or destroyed by various operations.
    19041901        """
    19051902        if not self.db:
  • trunk/tests/test_classic_dbwrapper.py

    r960 r961  
    972972    def testPrepare(self):
    973973        p = self.db.prepare
    974         self.assertIsNone(p("select 'hello'", 'my query'))
    975         self.assertIsNone(p("select 'world'", 'my other query'))
     974        self.assertIsNone(p('my query', "select 'hello'"))
     975        self.assertIsNone(p('my other query', "select 'world'"))
    976976        self.assertRaises(pg.ProgrammingError,
    977977            p, 'my query', "select 'hello, too'")
     
    979979    def testPrepareUnnamed(self):
    980980        p = self.db.prepare
    981         self.assertIsNone(p("select null"))
    982         self.assertIsNone(p("select null", None))
    983         self.assertIsNone(p("select null", ''))
    984         self.assertIsNone(p("select null", name=None))
    985         self.assertIsNone(p("select null", name=''))
     981        self.assertIsNone(p('', "select null"))
     982        self.assertIsNone(p(None, "select null"))
    986983
    987984    def testQueryPreparedWithoutParams(self):
     
    989986        self.assertRaises(pg.OperationalError, f, 'q')
    990987        p = self.db.prepare
    991         p("select 17", name='q1')
    992         p("select 42", name='q2')
    993         r = f(name='q1').getresult()[0][0]
     988        p('q1', "select 17")
     989        p('q2', "select 42")
     990        r = f('q1').getresult()[0][0]
    994991        self.assertEqual(r, 17)
    995         r = f(name='q2').getresult()[0][0]
     992        r = f('q2').getresult()[0][0]
    996993        self.assertEqual(r, 42)
    997994
    998995    def testQueryPreparedWithParams(self):
    999996        p = self.db.prepare
    1000         p("select 1 + $1 + $2 + $3", name='sum')
    1001         p("select initcap($1) || ', ' || $2 || '!'", name='cat')
     997        p('sum', "select 1 + $1 + $2 + $3")
     998        p('cat', "select initcap($1) || ', ' || $2 || '!'")
    1002999        f = self.db.query_prepared
    1003         r = f(2, 3, 5, name='sum').getresult()[0][0]
     1000        r = f('sum', 2, 3, 5).getresult()[0][0]
    10041001        self.assertEqual(r, 11)
    1005         r = f('hello', 'world', name='cat').getresult()[0][0]
     1002        r = f('cat', 'hello', 'world').getresult()[0][0]
    10061003        self.assertEqual(r, 'Hello, world!')
    10071004
    10081005    def testQueryPreparedUnnamedWithOutParams(self):
    10091006        f = self.db.query_prepared
    1010         self.assertRaises(pg.OperationalError, f)
     1007        self.assertRaises(pg.OperationalError, f, None)
     1008        self.assertRaises(pg.OperationalError, f, '')
    10111009        p = self.db.prepare
    10121010        # make sure all types are known so that we will not
    10131011        # generate other anonymous queries in the background
    1014         p("select 'no name'::varchar")
    1015         r = f().getresult()[0][0]
    1016         self.assertEqual(r, 'no name')
    1017         r = f(name=None).getresult()[0][0]
    1018         self.assertEqual(r, 'no name')
    1019         r = f(name='').getresult()[0][0]
    1020         self.assertEqual(r, 'no name')
     1012        p('', "select 'empty'::varchar")
     1013        r = f(None).getresult()[0][0]
     1014        self.assertEqual(r, 'empty')
     1015        r = f('').getresult()[0][0]
     1016        self.assertEqual(r, 'empty')
     1017        p(None, "select 'none'::varchar")
     1018        r = f(None).getresult()[0][0]
     1019        self.assertEqual(r, 'none')
     1020        r = f('').getresult()[0][0]
     1021        self.assertEqual(r, 'none')
    10211022
    10221023    def testQueryPreparedUnnamedWithParams(self):
    10231024        p = self.db.prepare
    1024         p("select 1 + $1 + $2")
     1025        p('', "select 1 + $1 + $2")
    10251026        f = self.db.query_prepared
    1026         r = f(2, 3).getresult()[0][0]
     1027        r = f('', 2, 3).getresult()[0][0]
    10271028        self.assertEqual(r, 6)
    1028         r = f(2, 3, name=None).getresult()[0][0]
     1029        r = f(None, 2, 3).getresult()[0][0]
    10291030        self.assertEqual(r, 6)
    1030         r = f(2, 3, name='').getresult()[0][0]
    1031         self.assertEqual(r, 6)
     1031        p(None, "select 2 + $1 + $2")
     1032        f = self.db.query_prepared
     1033        r = f('', 3, 4).getresult()[0][0]
     1034        self.assertEqual(r, 9)
     1035        r = f(None, 3, 4).getresult()[0][0]
     1036        self.assertEqual(r, 9)
    10321037
    10331038    def testDescribePrepared(self):
    1034         self.db.prepare("select 1 as first, 2 as second", 'count')
     1039        self.db.prepare('count', "select 1 as first, 2 as second")
    10351040        f = self.db.describe_prepared
    10361041        r = f('count').listfields()
     
    10381043
    10391044    def testDescribePreparedUnnamed(self):
    1040         self.db.prepare("select null as anon")
     1045        self.db.prepare('', "select null as anon")
    10411046        f = self.db.describe_prepared
    10421047        r = f().listfields()
     
    10531058        self.assertRaises(e, f, 'myquery')
    10541059        p = self.db.prepare
    1055         p("select 1", 'q1')
    1056         p("select 2", 'q2')
     1060        p('q1', "select 1")
     1061        p('q2', "select 2")
    10571062        f('q1')
    10581063        f('q2')
    10591064        self.assertRaises(e, f, 'q1')
    10601065        self.assertRaises(e, f, 'q2')
    1061         p("select 1", 'q1')
    1062         p("select 2", 'q2')
     1066        p('q1', "select 1")
     1067        p('q2', "select 2")
    10631068        f()
    10641069        self.assertRaises(e, f, 'q1')
Note: See TracChangeset for help on using the changeset viewer.