Diff
Modified: trunk/docs/contents/pg/connection.rst (956 => 957)
--- trunk/docs/contents/pg/connection.rst 2019-01-04 11:29:11 UTC (rev 956)
+++ trunk/docs/contents/pg/connection.rst 2019-01-04 13:28:58 UTC (rev 957)
@@ -33,7 +33,7 @@
Execute a SQL command string
:param str command: SQL command
- :param args: optional positional arguments
+ :param args: optional parameter values
:returns: result values
:rtype: :class:`Query`, None
:raises TypeError: bad argument type, or too many arguments
@@ -54,11 +54,12 @@
:meth:`Query.namedresult` methods or simply printed.
Otherwise, it returns ``None``.
-The query may optionally contain positional parameters of the form ``$1``,
-``$2``, etc instead of literal data, and the values supplied as a tuple.
-The values are substituted by the database in such a way that they don't
-need to be escaped, making this an effective way to pass arbitrary or
-unknown data without worrying about SQL injection or syntax errors.
+The SQL command may optionally contain positional parameters of the form
+``$1``, ``$2``, etc instead of literal data, in which case the values
+have to be supplied separately as a tuple. The values are substituted by
+the database in such a way that they don't need to be escaped, making this
+an effective way to pass arbitrary or unknown data without worrying about
+SQL injection or syntax errors.
When the database could not process the query, a :exc:`pg.ProgrammingError` or
a :exc:`pg.InternalError` is raised. You can check the ``SQLSTATE`` error code
@@ -70,6 +71,81 @@
phone = con.query("select phone from employees where name=$1",
(name,)).getresult()
+query_prepared -- execute a prepared statement
+----------------------------------------------
+
+.. method:: Connection.query_prepared(name, [args])
+
+ Execute a prepared statement
+
+ :param str name: name of the prepared statement
+ :param args: optional parameter values
+ :returns: result values
+ :rtype: :class:`Query`, None
+ :raises TypeError: bad argument type, or too many arguments
+ :raises TypeError: invalid connection
+ :raises ValueError: empty SQL query or lost connection
+ :raises pg.ProgrammingError: error in query
+ :raises pg.InternalError: error during query processing
+ :raises pg.OperationalError: prepared statement does not exist
+
+This method works exactly like :meth:`Connection.query` except that instead
+of passing the command itself, you pass the name of a prepared statement.
+An empty name corresponds to the unnamed statement. You must have created
+the corresponding named or unnamed statement with :meth:`Connection.prepare`
+before, or an :exc:`pg.OperationalError` will be raised.
+
+.. versionadded:: 5.1
+
+prepare -- create a prepared statement
+--------------------------------------
+
+.. method:: Connection.prepare(name, command)
+
+ Create a prepared statement
+
+ :param str name: name of the prepared statement
+ :param str command: SQL command
+ :rtype: None
+ :raises TypeError: bad argument types, or wrong number of arguments
+ :raises TypeError: invalid connection
+ :raises pg.ProgrammingError: error in query or duplicate query
+
+This method creates a prepared statement for the given command with the
+given name for later execution with the :meth:`Connection.query_prepared`
+method. The name can be empty to create an unnamed statement, in which case
+any pre-existing unnamed statement is automatically replaced; otherwise a
+:exc:`pg.ProgrammingError` is raised if the statement name is already defined
+in the current database session.
+
+The SQL command may optionally contain positional parameters of the form
+``$1``, ``$2``, etc instead of literal data. The corresponding values
+must then later be passed to the :meth:`Connection.query_prepared` method
+separately as a tuple.
+
+.. versionadded:: 5.1
+
+describe_prepared -- describe a prepared statement
+--------------------------------------------------
+
+.. method:: Connection.describe_prepared(name)
+
+ Describe a prepared statement
+
+ :param str name: name of the prepared statement
+ :rtype: :class:`Query`
+ :raises TypeError: bad argument type, or too many arguments
+ :raises TypeError: invalid connection
+ :raises pg.OperationalError: prepared statement does not exist
+
+This method returns a :class:`Query` object describing the prepared
+statement with the given name. You can also pass an empty name in order
+to describe the unnamed statement. Information on the fields of the
+corresponding query can be obtained through the :meth:`Query.listfields`,
+:meth:`Query.fieldname` and :meth:`Query.fieldnum` methods.
+
+.. versionadded:: 5.1
+
reset -- reset the connection
-----------------------------
@@ -76,7 +152,7 @@
.. method:: Connection.reset()
Reset the :mod:`pg` connection
-
+
:rtype: None
:raises TypeError: too many (any) arguments
:raises TypeError: invalid connection
@@ -101,7 +177,7 @@
.. method:: Connection.close()
Close the :mod:`pg` connection
-
+
:rtype: None
:raises TypeError: too many (any) arguments
Modified: trunk/docs/contents/pg/db_wrapper.rst (956 => 957)
--- trunk/docs/contents/pg/db_wrapper.rst 2019-01-04 11:29:11 UTC (rev 956)
+++ trunk/docs/contents/pg/db_wrapper.rst 2019-01-04 13:28:58 UTC (rev 957)
@@ -451,7 +451,8 @@
Similar to the :class:`Connection` function with the same name, except that
positional arguments can be passed either as a single list or tuple, or as
-individual positional arguments.
+individual positional arguments. These arguments will then be used as
+parameter values of parameterized queries.
Example::
@@ -458,10 +459,10 @@
name = input("Name? ")
phone = input("Phone? ")
rows = db.query("update employees set phone=$2 where name=$1",
- (name, phone)).getresult()[0][0]
+ name, phone).getresult()[0][0]
# or
rows = db.query("update employees set phone=$2 where name=$1",
- name, phone).getresult()[0][0]
+ (name, phone)).getresult()[0][0]
query_formatted -- execute a formatted SQL command string
---------------------------------------------------------
@@ -504,6 +505,116 @@
"update employees set phone=%(phone)s where name=%(name)s",
dict(name=name, phone=phone)).getresult()[0][0]
+query_prepared -- execute a prepared statement
+----------------------------------------------
+
+.. method:: DB.query_prepared([arg1, [arg2, ...]], [name=...])
+
+ Execute a prepared statement
+
+ :param str name: name of the prepared statement
+ :param arg*: optional positional arguments
+ :returns: result values
+ :rtype: :class:`Query`, None
+ :raises TypeError: bad argument type, or too many arguments
+ :raises TypeError: invalid connection
+ :raises ValueError: empty SQL query or lost connection
+ :raises pg.ProgrammingError: error in query
+ :raises pg.InternalError: error during query processing
+ :raises pg.OperationalError: prepared statement does not exist
+
+This methods works like the :meth:`DB.query` method, except that instead of
+passing the SQL command, you pass the name of a prepared statement via the
+keyword-only argument *name*. If you don't pass a name, the unnamed
+statement will be executed, if you created one before.
+
+You must have created the corresponding named or unnamed statement with
+the :meth:`DB.prepare` method before, otherwise an :exc:`pg.OperationalError`
+will be raised.
+
+.. versionadded:: 5.1
+
+prepare -- create a prepared statement
+--------------------------------------
+
+.. method:: DB.prepare(command, [name])
+
+ Create a prepared statement
+
+ :param str command: SQL command
+ :param str name: name of the prepared statement
+ :rtype: None
+ :raises TypeError: bad argument types, or wrong number of arguments
+ :raises TypeError: invalid connection
+ :raises pg.ProgrammingError: error in query or duplicate query
+
+This method creates a prepared statement for the given command with the
+given name for later execution with the :meth:`DB.query_prepared` method.
+The name can be empty or left out to create an unnamed statement, in which
+case any pre-existing unnamed statement is automatically replaced;
+otherwise a :exc:`pg.ProgrammingError` is raised if the statement name is
+already defined in the current database session.
+
+The SQL command may optionally contain positional parameters of the form
+``$1``, ``$2``, etc instead of literal data. The corresponding values
+must then later be passed to the :meth:`Connection.query_prepared` method
+as positional arguments.
+
+Example::
+
+ db.prepare("update employees set phone=$2 where ein=$1",
+ name='update employees')
+ while True:
+ ein = input("Employee ID? ")
+ if not ein:
+ break
+ phone = input("Phone? ")
+ rows = db.query_prepared(ein, phone,
+ name='update employees).getresult()[0][0]
+
+.. versionadded:: 5.1
+
+describe_prepared -- describe a prepared statement
+--------------------------------------------------
+
+.. method:: DB.describe_prepared([name])
+
+ Describe a prepared statement
+
+ :param str name: name of the prepared statement
+ :rtype: :class:`Query`
+ :raises TypeError: bad argument type, or too many arguments
+ :raises TypeError: invalid connection
+ :raises pg.OperationalError: prepared statement does not exist
+
+This method returns a :class:`Query` object describing the prepared
+statement with the given name. You can also pass an empty name in order
+to describe the unnamed statement. Information on the fields of the
+corresponding query can be obtained through the :meth:`Query.listfields`,
+:meth:`Query.fieldname` and :meth:`Query.fieldnum` methods.
+
+.. versionadded:: 5.1
+
+delete_prepared -- delete a prepared statement
+----------------------------------------------
+
+.. method:: DB.delete_prepared([name])
+
+ Delete a prepared statement
+
+ :param str name: name of the prepared statement
+ :rtype: None
+ :raises TypeError: bad argument type, or too many arguments
+ :raises TypeError: invalid connection
+ :raises pg.OperationalError: prepared statement does not exist
+
+This method deallocates a previously prepared SQL statement with the given
+name, or deallocates all prepared statements if you do not specify a name.
+Note that prepared statements are also deallocated automatically when the
+current session ends.
+
+.. versionadded:: 5.1
+
clear -- clear row values in memory
-----------------------------------
Modified: trunk/pgmodule.c (956 => 957)
--- trunk/pgmodule.c 2019-01-04 11:29:11 UTC (rev 956)
+++ trunk/pgmodule.c 2019-01-04 13:28:58 UTC (rev 957)
@@ -2450,7 +2450,7 @@
/* describe prepared statement */
static char connDescribePrepared__doc__[] =
-"describe_prepared(name, sql) -- describe a prepared statement\n\n"
+"describe_prepared(name) -- describe a prepared statement\n\n"
"You must pass the name (string) of the prepared statement.\n";
static PyObject *
Modified: trunk/tests/test_classic_connection.py (956 => 957)
--- trunk/tests/test_classic_connection.py 2019-01-04 11:29:11 UTC (rev 956)
+++ trunk/tests/test_classic_connection.py 2019-01-04 13:28:58 UTC (rev 957)
@@ -950,6 +950,10 @@
def testInvalidPreparedStatement(self):
self.assertRaises(pg.ProgrammingError, self.c.prepare, '', 'bad')
+ def testDuplicatePreparedStatement(self):
+ self.assertIsNone(self.c.prepare('q', 'select 1'))
+ self.assertRaises(pg.ProgrammingError, self.c.prepare, 'q', 'select 2')
+
def testNonExistentPreparedStatement(self):
self.assertRaises(pg.OperationalError,
self.c.query_prepared, 'does-not-exist')
