[DB-SIG] query - prepared statment

Carsten Haese carsten at uniqsys.com
Sat Feb 11 03:48:41 CET 2006

On Fri, 10 Feb 2006 16:38:09 -0800 (PST), David Rushby wrote
> --- "M.-A. Lemburg" <mal at egenix.com> wrote:
> > Perhaps we should something like this to the list of standard
> > DB-API extensions ?!
> > 
> > This is what we have in mxODBC 2.1:
> > 
> > cursor.prepare(operation)
> > 
> >    Prepare a database operation (query or command) statement for
> >    later execution and set cursor.command. To execute a prepared
> >    statement, pass cursor.statement to one of the .executeXXX()
> >    methods. Return values are not defined.
> > 
> > cursor.command
> >    Provides access to the last prepared or executed SQL command
> >    available through the cursor. If no such command is available,
> >    None is returned.
> > 
> > Looks very similar to what cx_Oracle implements.
> kinterbasdb implements similar functionality this way:
> ---
>   cursor.prep(sql_string)
>     Prepare a SQL statement for later execution.  Return a
> PreparedStatement object that can later be passed as the first
> parameter to cursor.executeXXX() instead of a SQL string.
> ---
> I'd definitely suggest the method name "prepare" as the standard
> instead of "prep"--I chose "prep" specifically so it wouldn't clash
> with the anticipated addition of "prepare" to the standard.
> However, I would find the cx_Oracle-style interface that Carsten
> described too constraining.  Why not let the user create and manipulate
> as many PreparedStatement objects as desired, and let
> PreparedStatements expose whatever properties and methods are
> appropriate for the database engine, instead of confining the whole
> prepared statement concept to "I'm about to execute this statement a
> bunch of times, so don't re-prepare it every time".
> kinterbasdb.PreparedStatement objects expose properties that contain:
>   - the SQL string on which the PreparedStatement is based
>   - a code indicating the statement type (insert, update, delete,
> select, execute procedure, etc.)
>   - the plan that the server will use to execute the statement
>   - the number of input parameters
>   - the number of output parameters
> This information could be crucial to client programs such as database
> administrative GUIs, where the Python programmer using the database
> module knows nothing about the SQL statements the user will submit, but
> needs to examine them programmatically.
> How would the cx_Oracle-style interface even expose this functionality?
>  cx_Oracle currently exposes a cursor.statement property which contains
> the SQL string that was most recently "prepared with prepare() or
> executed with execute()".  But the SQL string on which a prepared
> statement is based is only one of several potentially useful
> properties, so that interface is not rich enough to be blessed as
> standard.

cursor.description already gives the programmer information about the output
parameters. Input parameters and the query plan may be hard to standardize in
a meaningful way, but they could be exposed as additional attributes on the
cursor without having to resort to a PreparedStatement object. The cursor is
the basic DB-API unit that provides the facility for executing statements, and
it is in line with the existing standard to expose properties of the most
recently executed statements as cursor attributes (description and lastrowid,
for example).

I wouldn't mind coming up with a list of optional cursor attributes that
expose deeper diagnostics for the most recently executed/prepared command. My
suggestions would be: statement_type, input_description, and query_plan. The
semantics of query_plan and statement_type will probably have to be
implementation dependent.

I would suggest that a module that implements statement_type should also
define constants for basic statement types that a statement_type can be
compared to, similar to how type constants work already.

I'm -1 on introducing a whole new class to encapsulate these deeper diagnostics.

> Also, isn't it advantageous to allow the client programmer to create
> and manipulate any number of prepared statements, rather than just "the
> most recent one", as the cx_Oracle-style interface does?  Any
> sophisticated database module will cache and reuse multiple prepared
> statements internally, so why constrain the public interface to 
> expose just a single prepared statement at a time?

If the programmer needs more than one prepared statement, nothing is stopping
them from creating more than one cursor.

Best regards,


More information about the DB-SIG mailing list