Changeset 957 for trunk


Ignore:
Timestamp:
Jan 4, 2019, 8:28:58 AM (9 months ago)
Author:
cito
Message:

Add documentation for prepared statements

Location:
trunk
Files:
4 edited

Legend:

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

    r814 r957  
    3434
    3535    :param str command: SQL command
    36     :param args: optional positional arguments
     36    :param args: optional parameter values
    3737    :returns: result values
    3838    :rtype: :class:`Query`, None
     
    5555Otherwise, it returns ``None``.
    5656
    57 The query may optionally contain positional parameters of the form ``$1``,
    58 ``$2``, etc instead of literal data, and the values supplied as a tuple.
    59 The values are substituted by the database in such a way that they don't
    60 need to be escaped, making this an effective way to pass arbitrary or
    61 unknown data without worrying about SQL injection or syntax errors.
     57The SQL command may optionally contain positional parameters of the form
     58``$1``, ``$2``, etc instead of literal data, in which case the values
     59have to be supplied separately as a tuple.  The values are substituted by
     60the database in such a way that they don't need to be escaped, making this
     61an effective way to pass arbitrary or unknown data without worrying about
     62SQL injection or syntax errors.
    6263
    6364When the database could not process the query, a :exc:`pg.ProgrammingError` or
     
    7172        (name,)).getresult()
    7273
     74query_prepared -- execute a prepared statement
     75----------------------------------------------
     76
     77.. method:: Connection.query_prepared(name, [args])
     78
     79    Execute a prepared statement
     80
     81    :param str name: name of the prepared statement
     82    :param args: optional parameter values
     83    :returns: result values
     84    :rtype: :class:`Query`, None
     85    :raises TypeError: bad argument type, or too many arguments
     86    :raises TypeError: invalid connection
     87    :raises ValueError: empty SQL query or lost connection
     88    :raises pg.ProgrammingError: error in query
     89    :raises pg.InternalError: error during query processing
     90    :raises pg.OperationalError: prepared statement does not exist
     91
     92This method works exactly like :meth:`Connection.query` except that instead
     93of passing the command itself, you pass the name of a prepared statement.
     94An empty name corresponds to the unnamed statement.  You must have created
     95the corresponding named or unnamed statement with :meth:`Connection.prepare`
     96before, or an :exc:`pg.OperationalError` will be raised.
     97
     98.. versionadded:: 5.1
     99
     100prepare -- create a prepared statement
     101--------------------------------------
     102
     103.. method:: Connection.prepare(name, command)
     104
     105    Create a prepared statement
     106
     107    :param str name: name of the prepared statement
     108    :param str command: SQL command
     109    :rtype: None
     110    :raises TypeError: bad argument types, or wrong number of arguments
     111    :raises TypeError: invalid connection
     112    :raises pg.ProgrammingError: error in query or duplicate query
     113
     114This method creates a prepared statement for the given command with the
     115given name for later execution with the :meth:`Connection.query_prepared`
     116method. The name can be empty to create an unnamed statement, in which case
     117any pre-existing unnamed statement is automatically replaced; otherwise a
     118:exc:`pg.ProgrammingError` is raised if the statement name is already defined
     119in the current database session.
     120
     121The SQL command may optionally contain positional parameters of the form
     122``$1``, ``$2``, etc instead of literal data.  The corresponding values
     123must then later be passed to the :meth:`Connection.query_prepared` method
     124separately as a tuple.
     125
     126.. versionadded:: 5.1
     127
     128describe_prepared -- describe a prepared statement
     129--------------------------------------------------
     130
     131.. method:: Connection.describe_prepared(name)
     132
     133    Describe a prepared statement
     134
     135    :param str name: name of the prepared statement
     136    :rtype: :class:`Query`
     137    :raises TypeError: bad argument type, or too many arguments
     138    :raises TypeError: invalid connection
     139    :raises pg.OperationalError: prepared statement does not exist
     140
     141This method returns a :class:`Query` object describing the prepared
     142statement with the given name.  You can also pass an empty name in order
     143to describe the unnamed statement.  Information on the fields of the
     144corresponding query can be obtained through the :meth:`Query.listfields`,
     145:meth:`Query.fieldname` and :meth:`Query.fieldnum` methods.
     146
     147.. versionadded:: 5.1
     148
    73149reset -- reset the connection
    74150-----------------------------
     
    77153
    78154    Reset the :mod:`pg` connection
    79    
     155
    80156    :rtype: None
    81157    :raises TypeError: too many (any) arguments
     
    102178
    103179    Close the :mod:`pg` connection
    104    
     180
    105181    :rtype: None
    106182    :raises TypeError: too many (any) arguments
  • trunk/docs/contents/pg/db_wrapper.rst

    r900 r957  
    452452Similar to the :class:`Connection` function with the same name, except that
    453453positional arguments can be passed either as a single list or tuple, or as
    454 individual positional arguments.
     454individual positional arguments.  These arguments will then be used as
     455parameter values of parameterized queries.
    455456
    456457Example::
     
    459460    phone = input("Phone? ")
    460461    rows = db.query("update employees set phone=$2 where name=$1",
    461         (name, phone)).getresult()[0][0]
     462        name, phone).getresult()[0][0]
    462463    # or
    463464    rows = db.query("update employees set phone=$2 where name=$1",
    464          name, phone).getresult()[0][0]
     465        (name, phone)).getresult()[0][0]
    465466
    466467query_formatted -- execute a formatted SQL command string
     
    504505        "update employees set phone=%(phone)s where name=%(name)s",
    505506        dict(name=name, phone=phone)).getresult()[0][0]
     507
     508query_prepared -- execute a prepared statement
     509----------------------------------------------
     510
     511.. method:: DB.query_prepared([arg1, [arg2, ...]], [name=...])
     512
     513    Execute a prepared statement
     514
     515    :param str name: name of the prepared statement
     516    :param arg*: optional positional arguments
     517    :returns: result values
     518    :rtype: :class:`Query`, None
     519    :raises TypeError: bad argument type, or too many arguments
     520    :raises TypeError: invalid connection
     521    :raises ValueError: empty SQL query or lost connection
     522    :raises pg.ProgrammingError: error in query
     523    :raises pg.InternalError: error during query processing
     524    :raises pg.OperationalError: prepared statement does not exist
     525
     526This methods works like the :meth:`DB.query` method, except that instead of
     527passing the SQL command, you pass the name of a prepared statement via the
     528keyword-only argument *name*.  If you don't pass a name, the unnamed
     529statement will be executed, if you created one before.
     530
     531You must have created the corresponding named or unnamed statement with
     532the :meth:`DB.prepare` method before, otherwise an :exc:`pg.OperationalError`
     533will be raised.
     534
     535.. versionadded:: 5.1
     536
     537prepare -- create a prepared statement
     538--------------------------------------
     539
     540.. method:: DB.prepare(command, [name])
     541
     542    Create a prepared statement
     543
     544    :param str command: SQL command
     545    :param str name: name of the prepared statement
     546    :rtype: None
     547    :raises TypeError: bad argument types, or wrong number of arguments
     548    :raises TypeError: invalid connection
     549    :raises pg.ProgrammingError: error in query or duplicate query
     550
     551This method creates a prepared statement for the given command with the
     552given name for later execution with the :meth:`DB.query_prepared` method.
     553The name can be empty or left out to create an unnamed statement, in which
     554case any pre-existing unnamed statement is automatically replaced;
     555otherwise a :exc:`pg.ProgrammingError` is raised if the statement name is
     556already defined in the current database session.
     557
     558The SQL command may optionally contain positional parameters of the form
     559``$1``, ``$2``, etc instead of literal data.  The corresponding values
     560must then later be passed to the :meth:`Connection.query_prepared` method
     561as positional arguments.
     562
     563Example::
     564
     565    db.prepare("update employees set phone=$2 where ein=$1",
     566        name='update employees')
     567    while True:
     568        ein = input("Employee ID? ")
     569        if not ein:
     570            break
     571        phone = input("Phone? ")
     572        rows = db.query_prepared(ein, phone,
     573            name='update employees).getresult()[0][0]
     574
     575.. versionadded:: 5.1
     576
     577describe_prepared -- describe a prepared statement
     578--------------------------------------------------
     579
     580.. method:: DB.describe_prepared([name])
     581
     582    Describe a prepared statement
     583
     584    :param str name: name of the prepared statement
     585    :rtype: :class:`Query`
     586    :raises TypeError: bad argument type, or too many arguments
     587    :raises TypeError: invalid connection
     588    :raises pg.OperationalError: prepared statement does not exist
     589
     590This method returns a :class:`Query` object describing the prepared
     591statement with the given name.  You can also pass an empty name in order
     592to describe the unnamed statement.  Information on the fields of the
     593corresponding query can be obtained through the :meth:`Query.listfields`,
     594:meth:`Query.fieldname` and :meth:`Query.fieldnum` methods.
     595
     596.. versionadded:: 5.1
     597
     598delete_prepared -- delete a prepared statement
     599----------------------------------------------
     600
     601.. method:: DB.delete_prepared([name])
     602
     603    Delete a prepared statement
     604
     605    :param str name: name of the prepared statement
     606    :rtype: None
     607    :raises TypeError: bad argument type, or too many arguments
     608    :raises TypeError: invalid connection
     609    :raises pg.OperationalError: prepared statement does not exist
     610
     611This method deallocates a previously prepared SQL statement with the given
     612name, or deallocates all prepared statements if you do not specify a name.
     613Note that prepared statements are also deallocated automatically when the
     614current session ends.
     615
     616.. versionadded:: 5.1
    506617
    507618clear -- clear row values in memory
  • trunk/pgmodule.c

    r953 r957  
    24512451/* describe prepared statement */
    24522452static char connDescribePrepared__doc__[] =
    2453 "describe_prepared(name, sql) -- describe a prepared statement\n\n"
     2453"describe_prepared(name) -- describe a prepared statement\n\n"
    24542454"You must pass the name (string) of the prepared statement.\n";
    24552455
  • trunk/tests/test_classic_connection.py

    r953 r957  
    950950    def testInvalidPreparedStatement(self):
    951951        self.assertRaises(pg.ProgrammingError, self.c.prepare, '', 'bad')
     952
     953    def testDuplicatePreparedStatement(self):
     954        self.assertIsNone(self.c.prepare('q', 'select 1'))
     955        self.assertRaises(pg.ProgrammingError, self.c.prepare, 'q', 'select 2')
    952956
    953957    def testNonExistentPreparedStatement(self):
Note: See TracChangeset for help on using the changeset viewer.